May 29, 2015 at 5:24 am
I have a date table which I am trying to get the first date of the month and the last day of the month (these dates are fiscal)
I have the start of the week and end of the week but having trouble working out the first day of the month and last day of the month.
To Create the table
CREATE TABLE [dbo].[mhs_month_week](
[startdate] [datetime] NULL,
[enddate] [datetime] NULL,
[year] [int] NULL,
[month] [int] NULL,
[week] [int] NULL,
[financialyear] [varchar](10) NULL,
[monthname] [varchar](20) NULL
) ON [PRIMARY]
Data for the date in 2015 only.
To Insert the data -
INSERT INTO [dbo].[mhs_month_weekxx]
(startdate,enddate,year,month,week,financialyear,monthname)
SELECT 'Apr 6 2015 12:00AM','Apr 12 2015 12:00AM','2015','1','1','2015/2016','April' UNION ALL
SELECT 'Apr 13 2015 12:00AM','Apr 19 2015 12:00AM','2015','1','2','2015/2016','April' UNION ALL
SELECT 'Apr 20 2015 12:00AM','Apr 26 2015 12:00AM','2015','1','3','2015/2016','April' UNION ALL
SELECT 'Apr 27 2015 12:00AM','May 3 2015 12:00AM','2015','1','4','2015/2016','April' UNION ALL
SELECT 'May 4 2015 12:00AM','May 10 2015 12:00AM','2015','2','5','2015/2016','May' UNION ALL
SELECT 'May 11 2015 12:00AM','May 17 2015 12:00AM','2015','2','6','2015/2016','May' UNION ALL
SELECT 'May 18 2015 12:00AM','May 24 2015 12:00AM','2015','2','7','2015/2016','May' UNION ALL
SELECT 'May 25 2015 12:00AM','May 31 2015 12:00AM','2015','2','8','2015/2016','May' UNION ALL
SELECT 'Jun 1 2015 12:00AM','Jun 7 2015 12:00AM','2015','3','9','2015/2016','June' UNION ALL
SELECT 'Jun 8 2015 12:00AM','Jun 14 2015 12:00AM','2015','3','10','2015/2016','June' UNION ALL
SELECT 'Jun 15 2015 12:00AM','Jun 21 2015 12:00AM','2015','3','11','2015/2016','June' UNION ALL
SELECT 'Jun 22 2015 12:00AM','Jun 28 2015 12:00AM','2015','3','12','2015/2016','June' UNION ALL
SELECT 'Jun 29 2015 12:00AM','Jul 5 2015 12:00AM','2015','3','13','2015/2016','June' UNION ALL
SELECT 'Jul 6 2015 12:00AM','Jul 12 2015 12:00AM','2015','4','14','2015/2016','July' UNION ALL
SELECT 'Jul 13 2015 12:00AM','Jul 19 2015 12:00AM','2015','4','15','2015/2016','July' UNION ALL
SELECT 'Jul 20 2015 12:00AM','Jul 26 2015 12:00AM','2015','4','16','2015/2016','July' UNION ALL
SELECT 'Jul 27 2015 12:00AM','Aug 2 2015 12:00AM','2015','4','17','2015/2016','July' UNION ALL
SELECT 'Aug 3 2015 12:00AM','Aug 9 2015 12:00AM','2015','5','18','2015/2016','August' UNION ALL
SELECT 'Aug 10 2015 12:00AM','Aug 16 2015 12:00AM','2015','5','19','2015/2016','August' UNION ALL
SELECT 'Aug 17 2015 12:00AM','Aug 23 2015 12:00AM','2015','5','20','2015/2016','August' UNION ALL
SELECT 'Aug 24 2015 12:00AM','Aug 30 2015 12:00AM','2015','5','21','2015/2016','August' UNION ALL
SELECT 'Aug 31 2015 12:00AM','Sep 6 2015 12:00AM','2015','5','22','2015/2016','August' UNION ALL
SELECT 'Sep 7 2015 12:00AM','Sep 13 2015 12:00AM','2015','6','23','2015/2016','September' UNION ALL
SELECT 'Sep 14 2015 12:00AM','Sep 20 2015 12:00AM','2015','6','24','2015/2016','September' UNION ALL
SELECT 'Sep 21 2015 12:00AM','Sep 27 2015 12:00AM','2015','6','25','2015/2016','September' UNION ALL
SELECT 'Sep 28 2015 12:00AM','Oct 4 2015 12:00AM','2015','6','26','2015/2016','September' UNION ALL
SELECT 'Oct 5 2015 12:00AM','Oct 11 2015 12:00AM','2015','7','27','2015/2016','October' UNION ALL
SELECT 'Oct 12 2015 12:00AM','Oct 18 2015 12:00AM','2015','7','28','2015/2016','October' UNION ALL
SELECT 'Oct 19 2015 12:00AM','Oct 25 2015 12:00AM','2015','7','29','2015/2016','October' UNION ALL
SELECT 'Oct 26 2015 12:00AM','Nov 1 2015 12:00AM','2015','7','30','2015/2016','October' UNION ALL
SELECT 'Nov 2 2015 12:00AM','Nov 8 2015 12:00AM','2015','8','31','2015/2016','November' UNION ALL
SELECT 'Nov 9 2015 12:00AM','Nov 15 2015 12:00AM','2015','8','32','2015/2016','November' UNION ALL
SELECT 'Nov 16 2015 12:00AM','Nov 22 2015 12:00AM','2015','8','33','2015/2016','November' UNION ALL
SELECT 'Nov 23 2015 12:00AM','Nov 29 2015 12:00AM','2015','8','34','2015/2016','November' UNION ALL
SELECT 'Nov 30 2015 12:00AM','Dec 6 2015 12:00AM','2015','8','35','2015/2016','November' UNION ALL
SELECT 'Dec 7 2015 12:00AM','Dec 13 2015 12:00AM','2015','9','36','2015/2016','December' UNION ALL
SELECT 'Dec 14 2015 12:00AM','Dec 20 2015 12:00AM','2015','9','37','2015/2016','December' UNION ALL
SELECT 'Dec 21 2015 12:00AM','Dec 27 2015 12:00AM','2015','9','38','2015/2016','December' UNION ALL
SELECT 'Dec 28 2015 12:00AM','Jan 3 2016 12:00AM','2015','9','39','2015/2016','December' UNION ALL
SELECT 'Jan 4 2016 12:00AM','Jan 10 2016 12:00AM','2015','10','40','2015/2016','January' UNION ALL
SELECT 'Jan 11 2016 12:00AM','Jan 17 2016 12:00AM','2015','10','41','2015/2016','January' UNION ALL
SELECT 'Jan 18 2016 12:00AM','Jan 24 2016 12:00AM','2015','10','42','2015/2016','January' UNION ALL
SELECT 'Jan 25 2016 12:00AM','Jan 31 2016 12:00AM','2015','10','43','2015/2016','January' UNION ALL
SELECT 'Feb 1 2016 12:00AM','Feb 7 2016 12:00AM','2015','11','44','2015/2016','Febuary' UNION ALL
SELECT 'Feb 8 2016 12:00AM','Feb 14 2016 12:00AM','2015','11','45','2015/2016','Febuary' UNION ALL
SELECT 'Feb 15 2016 12:00AM','Feb 21 2016 12:00AM','2015','11','46','2015/2016','Febuary' UNION ALL
SELECT 'Feb 22 2016 12:00AM','Feb 28 2016 12:00AM','2015','11','47','2015/2016','Febuary' UNION ALL
SELECT 'Feb 29 2016 12:00AM','Mar 6 2016 12:00AM','2015','11','48','2015/2016','Febuary' UNION ALL
SELECT 'Mar 7 2016 12:00AM','Mar 13 2016 12:00AM','2015','12','49','2015/2016','March' UNION ALL
SELECT 'Mar 14 2016 12:00AM','Mar 20 2016 12:00AM','2015','12','50','2015/2016','March' UNION ALL
SELECT 'Mar 21 2016 12:00AM','Mar 27 2016 12:00AM','2015','12','51','2015/2016','March' UNION ALL
SELECT 'Mar 28 2016 12:00AM','Apr 3 2016 12:00AM','2015','12','52','2015/2016','March'
So for April of 2015 I would expect 6/4/15 to be the first day and 03/05/15 to be the end date.
Thanks
May 29, 2015 at 5:49 am
Does this do what you want?
SELECT year, monthname, MIN(startdate), MAX(enddate)
FROM [dbo].[mhs_month_week]
GROUP BY year, monthname
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 29, 2015 at 6:53 am
Legend.
Thanks. Does what I need it to do!!!
June 4, 2015 at 5:53 pm
Shifting gears a bit... constantly recalculating that which will not change seems very expensive to me (in this case). Why not make another smaller table with the start and end dates for each month? That would require just one row per month.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 4, 2015 at 6:49 pm
Jeff Moden (6/4/2015)
... constantly recalculating that which will not change seems very expensive...
That's going into my table of Jeff Moden quotes.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 4, 2015 at 6:56 pm
dwain.c (6/4/2015)
Jeff Moden (6/4/2015)
... constantly recalculating that which will not change seems very expensive...That's going into my table of Jeff Moden quotes.
Oh, be careful. I truly meant "in this case" (and edited my original response so no one makes a similar conclusion). There are places where doing the recalculation IS much less expensive than looking it up especially when it comes to memory I/O (logical reads). Itzik's cascading CTE Tally is pretty much proof of that. Yep... it's a tiny bit slower than looking it up in a Tally table in some cases. It's also a whole lot less expensive for logical reads because it doesn't produce any. "It Depends".
"In this case" means that there would be one lookup instead of two and no calculations to it very well might be worth it. It's at least worth the test.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 4, 2015 at 7:32 pm
Jeff Moden (6/4/2015)
dwain.c (6/4/2015)
Jeff Moden (6/4/2015)
... constantly recalculating that which will not change seems very expensive...That's going into my table of Jeff Moden quotes.
Oh, be careful. I truly meant "in this case" (and edited my original response so no one makes a similar conclusion). There are places where doing the recalculation IS much less expensive than looking it up especially when it comes to memory I/O (logical reads). Itzik's cascading CTE Tally is pretty much proof of that. Yep... it's a tiny bit slower than looking it up in a Tally table in some cases. It's also a whole lot less expensive for logical reads because it doesn't produce any. "It Depends".
"In this case" means that there would be one lookup instead of two and no calculations to it very well might be worth it. It's at least worth the test.
Rats! And I was just going to Tweet this new Modenism too!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 5, 2015 at 9:54 am
If they're always Mondays, why waste resources with table I/O at all? Very simple date arithmetic can tell you what the starting and ending dates are.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
June 5, 2015 at 11:13 am
ScottPletcher (6/5/2015)
If they're always Mondays, why waste resources with table I/O at all? Very simple date arithmetic can tell you what the starting and ending dates are.
You mean like this?
WITH cteStart AS (
SELECT CONVERT(datetime, '04/06/2015') first_date
)
SELECT DATEADD(week, t.N - 1, s.first_date) week_start, DATEADD(day, (t.n * 7) - 1, s.first_date) week_end
FROM cteStart s
CROSS APPLY dbo.Tally1K t
WHERE t.N < 53;
You need a tally table, but that's about it. You could also use this approach to form the base of your insert statement to populate the table instead of all that typing.
June 5, 2015 at 11:29 am
Ed Wagner (6/5/2015)
ScottPletcher (6/5/2015)
If they're always Mondays, why waste resources with table I/O at all? Very simple date arithmetic can tell you what the starting and ending dates are.You mean like this?
WITH cteStart AS (SELECT CONVERT(datetime, '04/06/2015') first_date
)
SELECT DATEADD(week, t.N - 1, s.first_date) week_start, DATEADD(day, (t.n * 7) - 1, s.first_date) week_end
FROM cteStart s
CROSS APPLY dbo.Tally1K t
WHERE t.N < 53;
You need a tally table, but that's about it. You could also use this approach to form the base of your insert statement to populate the table instead of all that typing.
Yep, along those exact lines (except with an inline tally to avoid table I/O, since that was the prime intent 🙂 ), and using a generic year:
WITH firstMondayInApril AS
(
SELECT DATEADD(DAY, - DATEDIFF(DAY, 0, CAST(CAST(YEAR(GETDATE()) AS char(4)) + '0407' AS date)) % 7,
CAST(CAST(YEAR(GETDATE()) AS char(4)) + '0407' AS date)) AS start_date
)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply