July 2, 2015 at 2:42 am
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
July 2, 2015 at 2:49 am
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 2, 2015 at 2:52 am
Using SQL Qury.
July 2, 2015 at 3:43 am
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.
July 2, 2015 at 4:44 am
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.
July 2, 2015 at 9:10 am
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)
July 2, 2015 at 9:32 am
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
July 2, 2015 at 9:45 am
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)
July 2, 2015 at 9:58 am
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'
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]
July 2, 2015 at 10:41 pm
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.
July 3, 2015 at 2:14 am
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;
July 3, 2015 at 9:05 am
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?
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]
July 3, 2015 at 9:16 am
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 3, 2015 at 9:18 am
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. 😛
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]
July 3, 2015 at 9:25 am
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