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'
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
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 Count(*)
FROM #tblshiftrota)
DECLARE @Counter AS INT=1;
WHILE( @rowcount >= @Counter )
BEGIN
DECLARE @ShiftRota AS INT =(SELECT fkshiftrotassshiftrota
FROM #tblshiftrota
WHERE id = @Counter)
set @RotaStartDate =(select rotastartdate from #tblshiftrota
where fkshiftrotassshiftrota=@ShiftRota)
set @RotaEndDate = (select rotaenddate from #tblshiftrota
where 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
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