How show only first friday of every month

  • Hi,

    I am having one requirement where I want to show only first Friday of every month of 2014,2015 and 2016 year.

    Please suggest me the logic

    Regards,

    Vipin jha

  • vipin_jha123 (7/2/2015)


    Hi,

    I am having one requirement where I want to show only first Friday of every month of 2014,2015 and 2016 year.

    Please suggest me the logic

    Regards,

    Vipin jha

    Show it where?

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Using SQL Qury.

  • fetch the first day of the every month then check for its day (as in.. monday,sunday etc..) then on the basis of the outcome .. add 0-6 days to the firstday of the month .. this should give you the expected result set.

    There might be a better way than this logic but this is what striked to me after reading problem statement

    You might want to run DBCC useroptions to verify the datefirst on your SQL server.

  • A calendar table would be good here, although I think the code below should also do the job

    DECLARE@StartDayDATETIME = '20140101',

    @EndDayDATETIME = '20161231';

    WITH DateBase (DT,RN)

    AS

    (

    SELECTA.DT,

    ROW_NUMBER() OVER (PARTITION BY YEAR(A.DT),MONTH(A.DT) ORDER BY A.DT)

    FROM

    (

    SELECTDATEADD(DAY,N,@StartDay)

    FROMdbo.GetNums(0,DATEDIFF(DAY,@StartDay,@EndDay))

    ) AS A(DT)

    WHEREDATEDIFF(DAY,0,A.DT)%7 = 4

    )

    SELECTDB.DT

    FROMDateBase AS DB

    WHEREDB.RN = 1;

    I only checked the first few months in 2014 though. It uses a number table, If you don't have a function for this you can look it up on these forums.

  • vipin_jha123 (7/2/2015)


    Using SQL Qury.

    Here's a query that converts from numeric values, although if you want to just generate this data independent of other table values, then you could change the CTEs to add single quotes and leading zeros for months and days, and just add single quotes for the years, and then take out all the CAST and RIGHT stuff except for the final CAST to a date value.

    WITH DAY_NUMS AS (

    SELECT 1 AS DAY_NUM UNION ALL

    SELECT 2 UNION ALL

    SELECT 3 UNION ALL

    SELECT 4 UNION ALL

    SELECT 5 UNION ALL

    SELECT 6 UNION ALL

    SELECT 7

    ),

    YEARS AS (

    SELECT 2014 AS THE_YEAR UNION ALL

    SELECT 2015 UNION ALL

    SELECT 2016

    ),

    MONTHS AS (

    SELECT DAY_NUM AS MONTH_NUM

    FROM DAY_NUMS

    UNION ALL

    SELECT 8 UNION ALL

    SELECT 9 UNION ALL

    SELECT 10 UNION ALL

    SELECT 11 UNION ALL

    SELECT 12

    )

    SELECT CAST(CAST(Y.THE_YEAR AS char(4)) + RIGHT('0' + CAST(M.MONTH_NUM AS varchar(2)), 2) + RIGHT('0' + CAST(D.DAY_NUM AS varchar(2)), 2) AS date) AS THE_DATE

    FROM YEARS AS Y, MONTHS AS M, DAY_NUMS AS D

    WHERE DATENAME(dw, CAST(Y.THE_YEAR AS char(4)) + RIGHT('0' + CAST(M.MONTH_NUM AS varchar(2)), 2) + RIGHT('0' + CAST(D.DAY_NUM AS varchar(2)), 2)) = 'Friday'

    ORDER BY CAST(CAST(Y.THE_YEAR AS char(4)) + RIGHT('0' + CAST(M.MONTH_NUM AS varchar(2)), 2) + RIGHT('0' + CAST(D.DAY_NUM AS varchar(2)), 2) AS date)

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • sgmunson (7/2/2015)


    vipin_jha123 (7/2/2015)


    Using SQL Qury.

    Here's a query that converts from numeric values, although if you want to just generate this data independent of other table values, then you could change the CTEs to add single quotes and leading zeros for months and days, and just add single quotes for the years, and then take out all the CAST and RIGHT stuff except for the final CAST to a date value.

    WITH DAY_NUMS AS (

    SELECT 1 AS DAY_NUM UNION ALL

    SELECT 2 UNION ALL

    SELECT 3 UNION ALL

    SELECT 4 UNION ALL

    SELECT 5 UNION ALL

    SELECT 6 UNION ALL

    SELECT 7

    ),

    YEARS AS (

    SELECT 2014 AS THE_YEAR UNION ALL

    SELECT 2015 UNION ALL

    SELECT 2016

    ),

    MONTHS AS (

    SELECT DAY_NUM AS MONTH_NUM

    FROM DAY_NUMS

    UNION ALL

    SELECT 8 UNION ALL

    SELECT 9 UNION ALL

    SELECT 10 UNION ALL

    SELECT 11 UNION ALL

    SELECT 12

    )

    SELECT CAST(CAST(Y.THE_YEAR AS char(4)) + RIGHT('0' + CAST(M.MONTH_NUM AS varchar(2)), 2) + RIGHT('0' + CAST(D.DAY_NUM AS varchar(2)), 2) AS date) AS THE_DATE

    FROM YEARS AS Y, MONTHS AS M, DAY_NUMS AS D

    WHERE DATENAME(dw, CAST(Y.THE_YEAR AS char(4)) + RIGHT('0' + CAST(M.MONTH_NUM AS varchar(2)), 2) + RIGHT('0' + CAST(D.DAY_NUM AS varchar(2)), 2)) = 'Friday'

    ORDER BY CAST(CAST(Y.THE_YEAR AS char(4)) + RIGHT('0' + CAST(M.MONTH_NUM AS varchar(2)), 2) + RIGHT('0' + CAST(D.DAY_NUM AS varchar(2)), 2) AS date)

    Interesting Approach! My method generates full set of dates and this way the initial set is about 1/4 of the rows.

    I'm assuming if you ever needed 2nd, 3rd you would just change the days

  • Dohsan (7/2/2015)


    Interesting Stuff! My method generates full set of dates and this way the initial set is about 1/4 of the rows.

    I'm assuming if you ever needed 2nd, 3rd you would just change the days

    My objective was to make the code independent of DATEFIRST, and that way, there's no need to worry about it. If it became a different day of the week, they just change the weekday name specified, and if it has to be the 2nd, 3rd, or 4th such day in a given month, then the DAY_NUM values just get changed to the proper range. Note that February will never have a 5th occurrence of any given day, except in a leap year, and this technique would require adjustment to provide for the LAST given weekday for a given month.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • vipin_jha123 (7/2/2015)


    Hi,

    I am having one requirement where I want to show only first Friday of every month of 2014,2015 and 2016 year.

    Please suggest me the logic

    Regards,

    Vipin jha

    Try this in your where clause:

    WHERE DATEPART(YEAR, [yourDateColumn]) IN (2014, 2015, 2016)

    AND DATEPART(DAY, [yourDateColumn]) <= 7

    AND DATENAME(WEEKDAY, [yourDateColumn]) = 'Friday'



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Yeah I like the last approach, I'd probably have used datepart(dw,yourdatecolumn) =6 but that is dependant on the day offset, better using Datename, and it is easier to read.

    But it does highlight the lack of clarity in the question:

    Do you want a query that will give you the dates of all the first fridays of the month for 2014 to 2015

    OR

    do you have records that you need to filter to just those with a yourdatecolumn that falls on the first friday of the month for 2014 to 2015 ?

    Hence you get two quite different answers.

  • Alvin Ramard (7/2/2015)


    vipin_jha123 (7/2/2015)


    Hi,

    I am having one requirement where I want to show only first Friday of every month of 2014,2015 and 2016 year.

    Please suggest me the logic

    Regards,

    Vipin jha

    Try this in your where clause:

    WHERE DATEPART(YEAR, [yourDateColumn]) IN (2014, 2015, 2016)

    AND DATEPART(DAY, [yourDateColumn]) <= 7

    AND DATENAME(WEEKDAY, [yourDateColumn]) = 'Friday'

    Gave it another go to see if i could modify my code and make it work any better, this should work for finding the first day of the month from any range (although limited to first week). Wasn't sure if using MIN over ROW_NUMBER improved it much as I still needed to sort the final result set. At least using ROW_NUMBER you could then add in the variable of which week.

    DECLARE@StartDateDATETIME = '20140101',

    @EndDateDATETIME = '20161231',

    @DayNameVARCHAR(10) = 'Friday',

    @DateNumINT;

    SELECT@DateNum = CASE @DayName

    WHEN 'Monday' THEN 0

    WHEN 'Tuesday' THEN 1

    WHEN 'Wednesday' THEN 2

    WHEN 'Thursday' THEN 3

    WHEN 'Friday' THEN 4

    WHEN 'Saturday' THEN 5

    WHEN 'Sunday' THEN 6

    END;

    WITH DateBase(DT)

    AS

    (

    --From StartDate work out next day that is the required day to use as start base

    SELECTDATEADD(DAY,CA1.DTAdjust,DATEDIFF(DAY,0,@StartDate))

    FROM(

    SELECT DATEDIFF(DAY,0,@StartDate)%7

    ) AS A(DTMod)

    CROSS

    APPLY(

    SELECTCASE

    WHEN A.DTMod < @DateNum THEN @DateNum - A.DTMod

    WHEN A.DTMod > @DateNum THEN @DateNum + 7 - A.DTMod

    ELSE A.DTMod

    END

    ) AS CA1(DTAdjust)

    )

    SELECTMIN(B.DT)

    FROM(

    SELECTDATEADD(DAY,N*7,B.DT)

    FROMDateBase AS B

    CROSS

    APPLYdbo.GetNums(0,DATEDIFF(DAY,@StartDate,@EndDate)/7)

    ) AS B(DT)

    GROUPBY YEAR(B.DT),

    MONTH(B.DT)

    ORDERBY YEAR(B.DT),

    MONTH(B.DT);

    Row_Number

    DECLARE@StartDateDATETIME = '20140101',

    @EndDateDATETIME = '20161231',

    @DayNameVARCHAR(10) = 'Friday',

    @DateNumINT,

    @WeekNumINT = 1;

    SELECT@DateNum = CASE @DayName

    WHEN 'Monday' THEN 0

    WHEN 'Tuesday' THEN 1

    WHEN 'Wednesday' THEN 2

    WHEN 'Thursday' THEN 3

    WHEN 'Friday' THEN 4

    WHEN 'Saturday' THEN 5

    WHEN 'Sunday' THEN 6

    END;

    WITH DateBase(DT)

    AS

    (

    --From StartDate work out next day thatis the required day to use as start base

    SELECTDATEADD(DAY,CA1.DTAdjust,DATEDIFF(DAY,0,@StartDate))

    FROM(

    SELECT DATEDIFF(DAY,0,@StartDate)%7

    ) AS A(DTMod)

    CROSS

    APPLY(

    SELECTCASE

    WHEN A.DTMod < @DateNum THEN @DateNum - A.DTMod

    WHEN A.DTMod > @DateNum THEN @DateNum + 7 - A.DTMod

    ELSE A.DTMod

    END

    ) AS CA1(DTAdjust)

    )

    SELECTBS1.DT

    FROM(

    SELECTBS.DT,

    RN = ROW_NUMBER() OVER (PARTITION BY YEAR(BS.DT),MONTH(BS.DT) ORDER BY BS.DT)

    FROM(

    SELECTDATEADD(DAY,N*7,B.DT)

    FROMDateBase AS B

    CROSS

    APPLYdbo.GetNums(0,DATEDIFF(DAY,@StartDate,@EndDate)/7)

    ) AS BS(DT)

    ) AS BS1(DT,RN)

    WHEREBS1.RN = @WeekNum;

  • Dohsan (7/3/2015)


    Alvin Ramard (7/2/2015)


    vipin_jha123 (7/2/2015)


    Hi,

    I am having one requirement where I want to show only first Friday of every month of 2014,2015 and 2016 year.

    Please suggest me the logic

    Regards,

    Vipin jha

    Try this in your where clause:

    WHERE DATEPART(YEAR, [yourDateColumn]) IN (2014, 2015, 2016)

    AND DATEPART(DAY, [yourDateColumn]) <= 7

    AND DATENAME(WEEKDAY, [yourDateColumn]) = 'Friday'

    Gave it another go to see if i could modify my code and make it work any better, this should work for finding the first day of the month from any range (although limited to first week). Wasn't sure if using MIN over ROW_NUMBER improved it much as I still needed to sort the final result set. At least using ROW_NUMBER you could then add in the variable of which week.

    DECLARE@StartDateDATETIME = '20140101',

    @EndDateDATETIME = '20161231',

    @DayNameVARCHAR(10) = 'Friday',

    @DateNumINT;

    SELECT@DateNum = CASE @DayName

    WHEN 'Monday' THEN 0

    WHEN 'Tuesday' THEN 1

    WHEN 'Wednesday' THEN 2

    WHEN 'Thursday' THEN 3

    WHEN 'Friday' THEN 4

    WHEN 'Saturday' THEN 5

    WHEN 'Sunday' THEN 6

    END;

    WITH DateBase(DT)

    AS

    (

    --From StartDate work out next day that is the required day to use as start base

    SELECTDATEADD(DAY,CA1.DTAdjust,DATEDIFF(DAY,0,@StartDate))

    FROM(

    SELECT DATEDIFF(DAY,0,@StartDate)%7

    ) AS A(DTMod)

    CROSS

    APPLY(

    SELECTCASE

    WHEN A.DTMod < @DateNum THEN @DateNum - A.DTMod

    WHEN A.DTMod > @DateNum THEN @DateNum + 7 - A.DTMod

    ELSE A.DTMod

    END

    ) AS CA1(DTAdjust)

    )

    SELECTMIN(B.DT)

    FROM(

    SELECTDATEADD(DAY,N*7,B.DT)

    FROMDateBase AS B

    CROSS

    APPLYdbo.GetNums(0,DATEDIFF(DAY,@StartDate,@EndDate)/7)

    ) AS B(DT)

    GROUPBY YEAR(B.DT),

    MONTH(B.DT)

    ORDERBY YEAR(B.DT),

    MONTH(B.DT);

    Row_Number

    DECLARE@StartDateDATETIME = '20140101',

    @EndDateDATETIME = '20161231',

    @DayNameVARCHAR(10) = 'Friday',

    @DateNumINT,

    @WeekNumINT = 1;

    SELECT@DateNum = CASE @DayName

    WHEN 'Monday' THEN 0

    WHEN 'Tuesday' THEN 1

    WHEN 'Wednesday' THEN 2

    WHEN 'Thursday' THEN 3

    WHEN 'Friday' THEN 4

    WHEN 'Saturday' THEN 5

    WHEN 'Sunday' THEN 6

    END;

    WITH DateBase(DT)

    AS

    (

    --From StartDate work out next day thatis the required day to use as start base

    SELECTDATEADD(DAY,CA1.DTAdjust,DATEDIFF(DAY,0,@StartDate))

    FROM(

    SELECT DATEDIFF(DAY,0,@StartDate)%7

    ) AS A(DTMod)

    CROSS

    APPLY(

    SELECTCASE

    WHEN A.DTMod < @DateNum THEN @DateNum - A.DTMod

    WHEN A.DTMod > @DateNum THEN @DateNum + 7 - A.DTMod

    ELSE A.DTMod

    END

    ) AS CA1(DTAdjust)

    )

    SELECTBS1.DT

    FROM(

    SELECTBS.DT,

    RN = ROW_NUMBER() OVER (PARTITION BY YEAR(BS.DT),MONTH(BS.DT) ORDER BY BS.DT)

    FROM(

    SELECTDATEADD(DAY,N*7,B.DT)

    FROMDateBase AS B

    CROSS

    APPLYdbo.GetNums(0,DATEDIFF(DAY,@StartDate,@EndDate)/7)

    ) AS BS(DT)

    ) AS BS1(DT,RN)

    WHEREBS1.RN = @WeekNum;

    Why are you trying to make everything so complicated?



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Alvin Ramard (7/3/2015)


    Why are you trying to make everything so complicated?

    Avril Lavigne?

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Phil Parkin (7/3/2015)


    Alvin Ramard (7/3/2015)


    Why are you trying to make everything so complicated?

    Avril Lavigne?

    She would sound better right now. 😛



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Alvin Ramard (7/3/2015)


    Phil Parkin (7/3/2015)


    Alvin Ramard (7/3/2015)


    Why are you trying to make everything so complicated?

    Avril Lavigne?

    She would sound better right now. 😛

    Why? Boredom! Not sure if Avril sings about that though.

Viewing 15 posts - 1 through 15 (of 61 total)

You must be logged in to reply to this topic. Login to reply