How Do I Get First ,Second,Third ,Fourth,Fifth Saturday

Question

1.00/5 (2 votes)

See more:

SQL-Server-2008R2

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'

C#

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.

SQL

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

Posted 23-May-14 1:15am

surajemo

Updated 23-May-14 2:36am

Thanks7872

v2

Add a Solution

Comments

Maciej Los 23-May-14 7:26am

You need to decide... Do you want all saturdays (1., 2., 3. and so on) or only first two saturdays for each month.

surajemo 23-May-14 8:47am

Thank you for replying .
No not for each month if my shift rotation pattern date is starting from 07-May-2014 then my first saturday will be 10-May-2014 second saturday will be 17 may then i have to ignore third fourth and fifth again consider 14 june 2014 so on

Sunasara Imdadhusen 23-May-14 7:36am

Need clarification

Thanks7872 23-May-14 8:34am

use reply button at comment of the member. That will notify the corresponding member about your comment.

Thanks7872 23-May-14 8:40am

This question is not at all clear. First explain the scenario. How much sats(saturdays) do you need. Do you want even/odd sats?

surajemo 23-May-14 8:52am

Suppose i have list of Saturdays as follows now if the user want's only first , second and fifth Saturday only so now how would i get following Saturday's

2013-11-09 1 saturday
2013-11-16 2 saturday
2013-11-23 3 ''
2013-11-30 4 ''
2013-12-07 1 ''
2013-12-14 2 ''
2013-12-21 3 ''
2013-12-28 4 ''
2014-01-04
2014-01-11
2014-01-18
2014-01-25
2014-02-01
2014-02-08
2014-02-15
2014-02-22
2014-03-01
2014-03-08
2014-03-15
2014-03-22
2014-03-29

3 solutions

Solution 1

i am not sure what you are expecting but this will be one of the solution

SQL

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)

Posted 23-May-14 4:41am

Thava Rajan

Solution 2

Would this do it?

SQL

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

Posted 23-May-14 4:46am

Sprint89

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2014-05-23T08:15:00

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:

SQL

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...