我如何获得第一，第二，第三，第四，第五周六 [英] How Do I Get First ,Second,Third ,Fourth,Fifth Saturday
本文介绍了我如何获得第一，第二，第三，第四，第五周六的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
美好的一天
我有一个sql server表ShiftDates，其中有如下文件：
我想要的是我想要获得星期六（第一，第二，第三，第四，第五）从
开始，从日期说'04 -Nov-2013'到'03 -Mar-2014'
ShiftPattern ShiftID日期天数
58 1 2013-11-04 Monday
58 2 2013-11-05周二
58 3 2013-11-06周三
58 4 2013- 11-07周四
58 4 2013-11-08 Friday
58 4 2013-11-09周六
....
...
...
...
...
等等
到目前为止尝试了这个
但它给了我所有星期六
。如果我已经在第一个星期六和第二个星期六检查过了。
但它给我第三个和第四个星期六也是
但输出应该像
09-Nov-2013
16-Nov-2013
30-Dec-2013
它不应该考虑2014年11月23日，但即便如此。
声明 @ Glb_FromDate as date = ' 01-Apr-2014'
声明 @ Glb_ToDate as date = ' 2015年3月31日'
- select * from dbo.amastershiftschedule_trigger
- 其中ssCreatedDate位于@Glb_FromDate和@Glb_ToDate
- 和FK_ShiftRotation = 58
声明 @EmpID as int = 21
CREATE TABLE #tblshiftrota
（
id INT IDENTITY （ 1 ， 1 ），
fkshiftrotassshiftrota INT ，
rotastartdate DATE ，
rotaenddate DATE
）
CREATE TABLE #tbldates
（
id INT IDENTITY （ 1 ， 1 ），
fkshiftrotassshiftrota INT ，
shiftdate DATE
）
DECLARE @ FirstSatweek AS BIT
DECLARE @ SecondtSatweek AS BIT
DECLARE < span class =code-sdkkeyword> @ ThirdSatweek AS BIT
< span class =code-keyword> DECLARE @ FourthSatweek AS BIT
DECLARE @ FifthSatweek AS BIT
DECLARE @ RotaStartDate AS date
DECLARE @ RotaEndDate AS date
- 获取每个班次轮换模式的开始日期和结束日期
INSERT INTO #tblshiftrota
SELECT DISTINCT fkshiftrotassshiftrota，
ssfromdate，
sstodate
FROM dbo.amastershiftschedule_trigger
WHERE fkempid = @EmpID
AND sscreateddate BETWEEN @ Glb_FromDate AND @ Glb_ToDate
ORDER BY fkshiftrotassshiftrota ASC
- 获取行数
DECLARE @ rowcount AS INT =（ SELECT 计数（*）
FROM #tblshiftrota）
DECLARE @Counter AS INT = 1;
- 循环遍历
< span class =code-keyword> WHILE （ @ rowcount > = @ Counter ）
BEGIN
- 获取换档旋转ID
DECLARE @ ShiftRota AS INT =（ SELECT fkshiftrotassshiftrota
FROM #tblshiftrota
WHERE id = @ Counter ）
- 开始日期
set @ RotaStartDate =（选择 rotastartdate 来自 #tblshiftrota
其中 fkshiftrotassshiftrota = @ ShiftRota）
- enddate
set @ RotaEndDate =（选择 rotaenddate 来自 #tblshiftrota
其中 fkshiftrotassshiftrota = @ ShiftRota）
- 检查是否检查了第一，第二，第三，第四，第五周六
SELECT @ FirstSatweek = isnull（srfirstsat， 0 ），
@ SecondtSatweek = isnull（srsecondsat， 0 ），
@ThirdSatweek = isnull（srthirdsat， 0 ），
@ FourthSatweek = isnull（srfourthsat， 0 ），
@ FifthSatweek = isnull（srfifthsat， 0 ）
FROM dbo.amastershiftrotation
WHERE apkshiftrotaid = @ ShiftRota
- 如果它被检查，那么插入记录的日期为星期六1星期六
IF @ FirstSatweek = 1
BEGIN
INSERT INTO #tbldates
SELECT fk_shiftrotation，
sd_date
FROM dbo.ashiftrotationdates
WHERE sd_weekno = 1
和 fk_shiftrotation = 58
和 SD_Date @ RotaStartDate 和 @ RotaEndDate
和 SD_Day = ' 星期六'
END
IF < span class =code-sdkkeyword> @ SecondtSatweek = 1
BEGIN
INSERT INTO #tbldates
SELECT fk_shiftrotation，
sd_date
FROM dbo.ashiftrotationdates
WHERE sd_weekno = 2
AND fk_shiftrotation = @ ShiftRota
和 SD_Date @ RotaStartDate 和 @ RotaEndDate
和 SD_Day = ' 星期六'
END
IF @ ThirdSatweek = 1
BEGIN
INSERT INTO #tbldates
SELECT fk_shiftrotation，
sd_date
FROM dbo.ashiftrotationdates
WHERE sd_weekno = 3
AND fk_shiftrotation = @ ShiftRota
和 SD_Date @ RotaStartDate 和 @ RotaEndDate
和 SD_Day = ' 星期六'
END
IF @ FourthSatweek = 1
BEGIN
INSERT INTO #tbldates
SELECT fk_shiftrotation，
sd_date
FROM dbo.ashiftrotationdates
W HERE sd_weekno = 4
AND fk_shiftrotation = @ ShiftRota
和 SD_Date @ RotaStartDate 和 @ RotaEndDate
和 SD_Day = ' 星期六'
END
IF @ FifthSatweek = 1
BEGIN
INSERT INTO #tbldates
SELECT fk_shiftrotation，
sd_date
FROM dbo.ashiftrotationdates
WHERE sd_weekno = 5
AND fk_shiftrotation = @ ShiftRota
和 SD_Date @ RotaStartDate 和 @ RotaEndDate
和 SD_Day = ' 星期六'
结束
SET @ Counter = @ Counter + 1 ;
END
选择 * 来自 #tbldates
drop table #tbldates
drop table #tblshiftrota
解决方案
如果您想只获得第一个和第二个星期六，并且在周六到指定日期之前跳过3,4。和5.试试这个：
DECLARE @ sd DATETIME = ' 2013-11-04'
DECLARE @ ed DATETIME = ' 2014-05-24'
- 查找第一个星期六
WHILE DATEPART（dw， @ sd ）<> 7
BEGIN
SET @ sd = DATEADD（dd， 1 ， @ sd ）
END
- 下周六获得
; WITH 星期六 AS
（
- 初始值
SELECT @ sd AS MyDate， 1 AS SatNo
UNION ALL
- 递归部分
SELECT DATEADD（dd， 7 ，MyDate） AS MyDate， CASE WHEN SatNo + 1 = 6 那么 1 ELSE SatNo + 1 END AS SatNo
FROM 星期六
WHERE DATEADD（dd， 7 ，MyDate）< = @ ed
）
SELECT *
< span class =code-keyword> FROM 星期六
WHERE SatNo IN （ 1 ， 2 ）
OPTION （MAXRECURSION 0 ）
结果：
2013-11-09 00：00：00.000 1
2013-11-16 00：00：00.000 2
2013-12-14 00： 00：00.000 1
2013-12-21 00：00：00.000 2
2014-01-18 00：00：00.000 1
2014-01-25 00：00：00.000 2
2014-02-22 00：00：00.000 1
2014-03-01 00：00：00.000 2
2014-03-29 00：00：00.000 1
2014- 04-05 00：00：00.000 2
2014-05-03 00：00：00.000 1
2014-05-10 00：00：00.000 2
根据需要更改代码;）
如果您需要一些细节，请提出问题...
我不确定你的期望，但这将是解决方案之一
DECLARE @ shift AS TABLE （shiftid INT ，日期 DATETIME ）
INSERT INTO @ shift
（shiftid ，日期）
SELECT TOP 100 10 ，DATEADD（DAY，ROW_NUMBER（） OVER （ ORDER BY c。[object_id]），GETDATE（））
FROM sys。[columns] AS c
; WITH cte AS （
SELECT *，DATENAME（dw，日期）[Dayname]，DENSE_RANK（） OVER （ ORDER BY DATEPART（ wk，dates））[Week_No]
FROM @shift
）
SELECT * FROM cte
WHERE cte.dayname = ' 星期六' AND week_no in （ 1 ， 2 ， 3 ， 4 ， 5 ）
会这样做吗？
开始
选择 row_number（） over （ order 按日期） as Rownumber，* int o #temp 来自 table_1
其中 days = ' 星期六'
和日期> = ' 04-Nov-2013'
和日期< ' 03-Mar-2014'
选择 * 来自 #temp 其中 Rownumber in （ 1 ， 2 ， 3 ， 4 ， 5 ）
end
更好地使用表变量但
Good Day
I have a sql server table ShiftDates in which there fileds like:
what i want is i want to get saturdays (first,second,third,fourth,fifth) starting from
from date say '04-Nov-2013' till '03-Mar-2014'
ShiftPattern ShiftID Dates Days
58 1 2013-11-04 Monday
58 2 2013-11-05 Tuesday
58 3 2013-11-06 Wednesday
58 4 2013-11-07 Thursday
58 4 2013-11-08 Friday
58 4 2013-11-09 Saturday
....
...
...
...
...
so on
so far tried this
but it is giving me all Saturday
.If i have checked first saturday and second saturday only.
But its giving me third and fourth fifith saturday also
but the output should be like
09-Nov-2013
16-Nov-2013
30-Dec-2013
it should not consider 23-Nov-2014 but even that is coming.
declare @Glb_FromDate as date='01-Apr-2014'
declare @Glb_ToDate as date='31-Mar-2015'
--select * from dbo.amastershiftschedule_trigger
--where ssCreatedDate between @Glb_FromDate and @Glb_ToDate
--and FK_ShiftRotation=58
declare @EmpID as int =21
CREATE TABLE #tblshiftrota
(
id INT IDENTITY(1, 1),
fkshiftrotassshiftrota INT,
rotastartdate DATE,
rotaenddate DATE
)
CREATE TABLE #tbldates
(
id INT IDENTITY(1, 1),
fkshiftrotassshiftrota INT,
shiftdate DATE
)
DECLARE @FirstSatweek AS BIT
DECLARE @SecondtSatweek AS BIT
DECLARE @ThirdSatweek AS BIT
DECLARE @FourthSatweek AS BIT
DECLARE @FifthSatweek AS BIT
DECLARE @RotaStartDate AS date
DECLARE @RotaEndDate AS date
--get the start date and end date for each shift rotation pattern
INSERT INTO #tblshiftrota
SELECT DISTINCT fkshiftrotassshiftrota,
ssfromdate,
sstodate
FROM dbo.amastershiftschedule_trigger
WHERE fkempid = @EmpID
AND sscreateddate BETWEEN @Glb_FromDate AND @Glb_ToDate
ORDER BY fkshiftrotassshiftrota ASC
--get row count
DECLARE @rowcount AS INT=(SELECT Count(*)
FROM #tblshiftrota)
DECLARE @Counter AS INT=1;
--loop over it
WHILE( @rowcount >= @Counter )
BEGIN
--GET THE SHIFT ROTATION ID
DECLARE @ShiftRota AS INT =(SELECT fkshiftrotassshiftrota
FROM #tblshiftrota
WHERE id = @Counter)
--start date
set @RotaStartDate =(select rotastartdate from #tblshiftrota
where fkshiftrotassshiftrota=@ShiftRota)
--enddate
set @RotaEndDate = (select rotaenddate from #tblshiftrota
where fkshiftrotassshiftrota=@ShiftRota)
--check whether first ,second, third fourth ,fifth saturday are checked
SELECT @FirstSatweek =isnull( srfirstsat,0),
@SecondtSatweek = isnull( srsecondsat,0),
@ThirdSatweek = isnull( srthirdsat,0),
@FourthSatweek = isnull( srfourthsat,0),
@FifthSatweek = isnull( srfifthsat,0)
FROM dbo.amastershiftrotation
WHERE apkshiftrotaid = @ShiftRota
--IF IT IS CHECKED THEN INSERT RECORDS HAVING DAY AS SATURDAY 1 SATURDAY
IF @FirstSatweek = 1
BEGIN
INSERT INTO #tbldates
SELECT fk_shiftrotation,
sd_date
FROM dbo.ashiftrotationdates
WHERE sd_weekno = 1
AND fk_shiftrotation = 58
and SD_Date between @RotaStartDate and @RotaEndDate
and SD_Day ='Saturday'
END
IF @SecondtSatweek = 1
BEGIN
INSERT INTO #tbldates
SELECT fk_shiftrotation,
sd_date
FROM dbo.ashiftrotationdates
WHERE sd_weekno = 2
AND fk_shiftrotation = @ShiftRota
and SD_Date between @RotaStartDate and @RotaEndDate
and SD_Day ='Saturday'
END
IF @ThirdSatweek = 1
BEGIN
INSERT INTO #tbldates
SELECT fk_shiftrotation,
sd_date
FROM dbo.ashiftrotationdates
WHERE sd_weekno = 3
AND fk_shiftrotation = @ShiftRota
and SD_Date between @RotaStartDate and @RotaEndDate
and SD_Day ='Saturday'
END
IF @FourthSatweek = 1
BEGIN
INSERT INTO #tbldates
SELECT fk_shiftrotation,
sd_date
FROM dbo.ashiftrotationdates
WHERE sd_weekno = 4
AND fk_shiftrotation = @ShiftRota
and SD_Date between @RotaStartDate and @RotaEndDate
and SD_Day ='Saturday'
END
IF @FifthSatweek = 1
BEGIN
INSERT INTO #tbldates
SELECT fk_shiftrotation,
sd_date
FROM dbo.ashiftrotationdates
WHERE sd_weekno = 5
AND fk_shiftrotation = @ShiftRota
and SD_Date between @RotaStartDate and @RotaEndDate
and SD_Day ='Saturday'
END
SET @Counter =@Counter + 1;
END
select * from #tbldates
drop table #tbldates
drop table #tblshiftrota 解决方案
If you would like to get only first and second saturday and skip 3., 4. and 5. saturday till the specified day, please try this:
DECLARE @sd DATETIME = '2013-11-04' DECLARE @ed DATETIME = '2014-05-24' --find first saturday WHILE DATEPART(dw, @sd)<>7 BEGIN SET @sd = DATEADD(dd,1,@sd) END --get next saturdays ;WITH Saturdays AS ( --initial value SELECT @sd AS MyDate, 1 AS SatNo UNION ALL --recursive part SELECT DATEADD(dd,7,MyDate) AS MyDate, CASE WHEN SatNo + 1 =6 THEN 1 ELSE SatNo+1 END AS SatNo FROM Saturdays WHERE DATEADD(dd,7,MyDate)<=@ed ) SELECT * FROM Saturdays WHERE SatNo IN (1,2) OPTION(MAXRECURSION 0)
Result:
2013-11-09 00:00:00.000 1 2013-11-16 00:00:00.000 2 2013-12-14 00:00:00.000 1 2013-12-21 00:00:00.000 2 2014-01-18 00:00:00.000 1 2014-01-25 00:00:00.000 2 2014-02-22 00:00:00.000 1 2014-03-01 00:00:00.000 2 2014-03-29 00:00:00.000 1 2014-04-05 00:00:00.000 2 2014-05-03 00:00:00.000 1 2014-05-10 00:00:00.000 2
Change the code to your needs ;)
If you want some details, ask a question...
i am not sure what you are expecting but this will be one of the solution
DECLARE @shift AS TABLE (shiftid INT, dates DATETIME) INSERT INTO @shift (shiftid, dates) SELECT TOP 100 10, DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY c.[object_id]),GETDATE() ) FROM sys.[columns] AS c ;WITH cte AS( SELECT *,DATENAME(dw,dates)[Dayname],DENSE_RANK() OVER (ORDER BY DATEPART(wk,dates)) [Week_No] FROM @shift ) SELECT * FROM cte WHERE cte.dayname ='Saturday' AND week_no in(1,2,3,4,5)
Would this do it?
begin select row_number() over(order by Dates) as Rownumber, * into #temp from table_1 where days = 'Saturday' and Dates >= '04-Nov-2013' and Dates < '03-Mar-2014' select * from #temp where Rownumber in (1,2,3,4,5) end
better to use a table variable though
这篇关于我如何获得第一，第二，第三，第四，第五周六的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
