August 11, 2016 at 2:25 am
Hi all!
Have looked a bit around to find something here, but only found something about how to calculate last day of month.
Given this extremly simple table:
CREATE TABLE [dbo].[a_dates](
[id] [int] NOT NULL,
[Check_date] [datetime] NULL,
CONSTRAINT [PK_a_dates] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
What i need is to SELECT count(*) WHERE [Check_date] is last day in month including leap years etc.
Best regards
Edvard Korsbæk
August 11, 2016 at 2:42 am
Edvard
If you add one day to the last day of the month, you get the first day of the next month. You could therefore try something like this:
WHERE DATEPART(day,DATEADD(day,1,Check_date)) = 1
If you have an index on Check_date then this query isn't going to be able to use it - I'm not sure whether there's a sargable way of doing the above.
John
August 11, 2016 at 3:51 am
SELECT
t.[Date],
cnt = COUNT(*)
FROM Trans t -- 36M rows
CROSS APPLY (SELECT ldom = DATEADD(MONTH,1+DATEDIFF(MONTH,0,t.[Date]),0)-1) x
WHERE DAY([Date]) > 27
AND t.[Date] = x.ldom
GROUP BY t.[Date]
-- (100 row(s) affected) / 00:00:02
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 11, 2016 at 4:34 am
Just a minor adjustment to Chris solution, It will not handle if there is date and time are coming in the check_date column.
Here is Chris updated script:
SELECT
t.[Check_date],
cnt = COUNT(*)
FROM [dbo].[a_dates] t
CROSS APPLY (SELECT ldom = DATEADD(MONTH,1+DATEDIFF(MONTH,0,t.[Check_date]),0)-1, DATEADD(day,DATEDIFF(day,0,t.[Check_date]),0) as checkDate) x
WHERE DAY([Check_date]) > 27
AND x.checkDate = x.ldom
GROUP BY t.[Check_date]
Or You can also use the EOFMONTH Function like this:
SELECT
t.[Check_date],
cnt = COUNT(*)
FROM [dbo].[a_dates] t
CROSS APPLY (SELECT ldom = EOMONTH([Check_date]), Adj_CheckDate = CAST([Check_date] as date) )x
WHERE DAY([Check_date]) > 27
AND ldom = Adj_CheckDate
GROUP BY t.[Check_date]
hope it helps.
August 11, 2016 at 4:48 am
twin.devil (8/11/2016)
Just a minor adjustment to Chris solution, It will not handle if there is date and time are coming in the check_date column.Here is Chris updated script:
SELECT
t.[Check_date],
cnt = COUNT(*)
FROM [dbo].[a_dates] t
CROSS APPLY (SELECT ldom = DATEADD(MONTH,1+DATEDIFF(MONTH,0,t.[Check_date]),0)-1, DATEADD(day,DATEDIFF(day,0,t.[Check_date]),0) as checkDate) x
WHERE DAY([Check_date]) > 27
AND x.checkDate = x.ldom
GROUP BY t.[Check_date]
Did you test it with datetime?
Edit: New version for datetimes with a time component:
SELECT
CAST(t.[Check_date] AS DATE),
cnt = COUNT(*)
FROM [a_dates] t -- 36M rows
CROSS APPLY (SELECT ldom = DATEADD(MONTH,1+DATEDIFF(MONTH,0,t.[Check_date]),0)-1) x
WHERE DAY([Check_date]) > 27
AND CAST(t.[Check_date] AS DATE) = x.ldom
GROUP BY CAST(t.[Check_date] AS DATE)
ORDER BY CAST(t.[Check_date] AS DATE)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 11, 2016 at 5:06 am
Did you test it with datetime?
Yes i did.
August 11, 2016 at 5:20 am
I quickly slapped this together. Can someone do some scalability testing for me to see how it stacks up with the other code? Sadly I have to grab a shower and head to a client for some real (i.e. billable) work!! 😎
SELECT DATEADD(DAY,DATEDIFF(DAY,0,t.d1),0) AS EOM,
cnt = COUNT(*)
FROM #tmp t
WHERE DATEADD(DAY,DATEDIFF(DAY,0,t.d1),0) = DATEADD(MONTH,1+DATEDIFF(MONTH,0,DATEADD(DAY,DATEDIFF(DAY,0,t.d1),0)),0)-1
GROUP BY DATEADD(DAY,DATEDIFF(DAY,0,t.d1),0)
ORDER BY 1
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 11, 2016 at 5:35 am
TheSQLGuru (8/11/2016)
I quickly slapped this together. Can someone do some scalability testing for me to see how it stacks up with the other code? Sadly I have to grab a shower and head to a client for some real (i.e. billable) work!! 😎
SELECT DATEADD(DAY,DATEDIFF(DAY,0,t.d1),0) AS EOM,
cnt = COUNT(*)
FROM #tmp t
WHERE DATEADD(DAY,DATEDIFF(DAY,0,t.d1),0) = DATEADD(MONTH,1+DATEDIFF(MONTH,0,DATEADD(DAY,DATEDIFF(DAY,0,t.d1),0)),0)-1
GROUP BY DATEADD(DAY,DATEDIFF(DAY,0,t.d1),0)
ORDER BY 1
Sure, same 36 million row table as earlier. Script run four or five times, here's a standard result:
Chris v2
SQL Server Execution Times:
CPU time = 8769 ms, elapsed time = 1942 ms.
================================================
Kevin v1
SQL Server Execution Times:
CPU time = 22401 ms, elapsed time = 3982 ms.
SET NOCOUNT ON
PRINT 'Chris v2'
set statistics time on
SELECT
CAST(t.[date] AS DATE),
cnt = COUNT(*)
FROM trans t -- 36M rows
CROSS APPLY (SELECT ldom = DATEADD(MONTH,1+DATEDIFF(MONTH,0,t.[date]),0)-1) x
WHERE DAY([date]) > 27
AND CAST(t.[date] AS DATE) = x.ldom
GROUP BY CAST(t.[date] AS DATE)
ORDER BY CAST(t.[date] AS DATE)
set statistics time off
print '================================================'
PRINT 'Kevin v1'
set statistics time on
SELECT DATEADD(DAY,DATEDIFF(DAY,0,t.[date]),0) AS EOM,
cnt = COUNT(*)
FROM trans t
WHERE DATEADD(DAY,DATEDIFF(DAY,0,t.[date]),0) = DATEADD(MONTH,1+DATEDIFF(MONTH,0,DATEADD(DAY,DATEDIFF(DAY,0,t.[date]),0)),0)-1
GROUP BY DATEADD(DAY,DATEDIFF(DAY,0,t.[date]),0)
ORDER BY 1
set statistics time off
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 11, 2016 at 5:45 am
SQL Server 2014, four processors, 16GB RAM. Test harness:USE tempdb
CREATE TABLE Dates (MyDate datetime);
WITH N10(n) AS (
SELECT CAST(n AS bigint) FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9))m(n)
)
, N100 AS (
SELECT m1.n FROM N10 m1 CROSS JOIN N10 m2
)
, N10000 AS (
SELECT m1.n FROM N100 m1 CROSS JOIN N100 m2
)
, N100000000 AS (
SELECT m1.n FROM N10000 m1 CROSS JOIN N10000 m2
)
INSERT INTO Dates
SELECT TOP 36000000 DATEADD(hour,CAST(RAND(CHECKSUM(NEWID()))*10000000 AS int),'19000101')
FROM N100000000
Four queries tested:-- John
SELECT
CAST(MyDate AS date)
,COUNT(*)
FROM Dates
WHERE DATEPART(day,DATEADD(day,1,MyDate)) = 1
GROUP BY CAST(MyDate AS date)
-- Chris
SELECT
CAST(MyDate AS DATE),
cnt = COUNT(*)
FROM Dates t -- 36M rows
CROSS APPLY (SELECT ldom = DATEADD(MONTH,1+DATEDIFF(MONTH,0,t.MyDate),0)-1) x
WHERE DAY(MyDate) > 27
AND CAST(t.MyDate AS DATE) = x.ldom
GROUP BY CAST(t.MyDate AS DATE)
-- Kevin
SELECT DATEADD(DAY,DATEDIFF(DAY,0,t.MyDate),0) AS EOM,
cnt = COUNT(*)
FROM Dates t
WHERE DATEADD(DAY,DATEDIFF(DAY,0,t.MyDate),0) = DATEADD(MONTH,1+DATEDIFF(MONTH,0,DATEADD(DAY,DATEDIFF(DAY,0,t.MyDate),0)),0)-1
GROUP BY DATEADD(DAY,DATEDIFF(DAY,0,t.MyDate),0)
-- twin.devil's EOMONTH
SELECT
CAST(t.MyDate AS DATE),
cnt = COUNT(*)
FROM Dates t
CROSS APPLY (SELECT ldom = EOMONTH(MyDate), Adj_CheckDate = CAST(MyDate as date) )x
WHERE DAY(MyDate) > 27
AND ldom = Adj_CheckDate
GROUP BY CAST(t.MyDate AS DATE)
Results (each query executed three times):
John
----
CPU Elapsed
--- -------
4242 1170
4245 1176
4135 1255
Chris
-----
CPU Elapsed
--- -------
4712 1381
4679 1289
4650 1287
Kevin
-----
CPU Elapsed
--- -------
9345 2611
9267 2507
9343 2465
twin.devil
----------
CPU Elapsed
--- -------
4258 1313
4274 1184
4291 1193
John
August 11, 2016 at 6:08 am
The results I get are much closer than that, John (except for Kevin's).
This might be the simplest option:
SELECT
Adj_CheckDate,
cnt = COUNT(*)
FROM trans t
CROSS APPLY (SELECT ldom = EOMONTH([date]), Adj_CheckDate = CAST([date] as date) )x
WHERE DAY([date]) > 27
AND ldom = Adj_CheckDate
GROUP BY Adj_CheckDate
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 11, 2016 at 8:05 am
Chris, yes, that's very similar to mine now.CPU Elapsed
--- -------
4303 1185
4260 1184
4273 1255
John
August 11, 2016 at 8:29 pm
IF you can afford a nonclustered index on check_date, this will drastically reduce the i/o.
;with Eomonths (Eom) as (select top(500) EoMonth(DateAdd(MONTH,(N-1),'1/1/2016')) from tally)
select Eom as Check_date, Total
from Eomonths
cross apply (select sum(1) as Total from a_dates where check_date = eom) ca
order by check_date
Also, I have a question. Check_date is a datetime column. Are times ever reflected in any rows in that column?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 11, 2016 at 11:41 pm
The Dixie Flatline (8/11/2016)
IF you can afford a nonclustered index on check_date, this will drastically reduce the i/o.
;with Eomonths (Eom) as (select top(500) EoMonth(DateAdd(MONTH,(N-1),'1/1/2016')) from tally)
select Eom as Check_date, Total
from Eomonths
cross apply (select sum(1) as Total from a_dates where check_date = eom) ca
order by check_date
Also, I have a question. Check_date is a datetime column. Are times ever reflected in any rows in that column?
OP only gave the Table structure, didn't gave us any sample data to work with. So its an assumption that there could be Time involved as the datatype allows it.
August 12, 2016 at 3:53 am
The Dixie Flatline (8/11/2016)
IF you can afford a nonclustered index on check_date, this will drastically reduce the i/o.
;with Eomonths (Eom) as (select top(500) EoMonth(DateAdd(MONTH,(N-1),'1/1/2016')) from tally)
select Eom as Check_date, Total
from Eomonths
cross apply (select sum(1) as Total from a_dates where check_date = eom) ca
order by check_date
Also, I have a question. Check_date is a datetime column. Are times ever reflected in any rows in that column?
I made the following changes to my test harness to make it more like the original poster's table, and to add the suggested non-clustered index.AL TER TABLE Dates ADD ID int NULL;
WITH DatestoUpdate AS (
SELECT
ID
,ROW_NUMBER() OVER (ORDER BY NEWID()) RowNo
FROM Dates
)
UPDATE DatestoUpdate
SET ID = RowNo;
AL TER TABLE Dates ALTER COLUMN ID int NOT NULL;
AL TER TABLE DATES
ADD CONSTRAINT PK_Dates_ID
PRIMARY KEY CLUSTERED (ID);
CREATE NONCLUSTERED INDEX IX_Dates_MyDate
ON Dates(MyDate);
I tweaked your query thus to generate the tally on the fly and to change the column names to fit in with my test harness:with N10(n) AS (
SELECT CAST(n AS bigint) FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9))m(n)
)
, N100 AS (
SELECT m1.n FROM N10 m1 CROSS JOIN N10 m2
)
, N10000 AS (
SELECT m1.n FROM N100 m1 CROSS JOIN N100 m2
)
, tally AS (
SELECT m1.n FROM N10000 m1 CROSS JOIN N10000 m2
)
, Eomonths (Eom) as (select TOP 500 EoMonth(DateAdd(MONTH,(n-1),'1/1/2016')) from tally)
select Eom as Check_date, Total
from Eomonths
cross apply (select sum(1) as Total from Dates where MyDate = eom) ca
order by check_date
Unfortunately, all I got back was 500 lots of 2015-12-31 2
I don't know whether that's down to your code or my tweaks!
In other news, I retested the other contenders on the updated test harness, and here are the results:
John
----
CPU Elapsed
--- -------
3293 964
3321 1046
3324 947
Chris (latest)
--------------
CPU Elapsed
--- -------
3290 921
3278 1113
3262 947
Kevin
-----
CPU Elapsed
--- -------
8783 2882
8595 2822
8657 2734
twin.devil
----------
CPU Elapsed
--- -------
3291 1132
3198 942
3307 1036
Interestingly enough, all four queries have the same execution plan, but the row estimates aren't the same in each. I've attached the plans in case anyone's interested.
John
August 12, 2016 at 4:32 am
I tweaked your query thus to generate the tally on the fly and to change the column names to fit in with my test harness:
with N10(n) AS (SELECT CAST(n AS bigint) FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9))m(n)
)
, N100 AS (
SELECT m1.n FROM N10 m1 CROSS JOIN N10 m2
)
, N10000 AS (
SELECT m1.n FROM N100 m1 CROSS JOIN N100 m2
)
, tally AS (
SELECT m1.n FROM N10000 m1 CROSS JOIN N10000 m2
)
, Eomonths (Eom) as (select TOP 500 EoMonth(DateAdd(MONTH,(n-1),'1/1/2016')) from tally)
select Eom as Check_date, Total
from Eomonths
cross apply (select sum(1) as Total from Dates where MyDate = eom) ca
order by check_date
Unfortunately, all I got back was 500 lots of
2015-12-31 2I don't know whether that's down to your code or my tweaks!
Its happening because the logic is not proper in your tally CTE, It should be something this:
with N10(n) AS (
SELECT CAST(n AS bigint) FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9))m(n)
)
, N100 AS (
SELECT m1.n FROM N10 m1 CROSS JOIN N10 m2
)
, N10000 AS (
SELECT m1.n FROM N100 m1 CROSS JOIN N100 m2
)
, tally AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS n FROM N10000 m1 CROSS JOIN N10000 m2
)
, Eomonths (Eom) as (select TOP 500 EoMonth(DateAdd(MONTH,(n-1),'1/1/2016')) from tally)
select Eom as Check_date, Total
from Eomonths
cross apply (select sum(1) as Total from Dates where MyDate = eom) ca
order by check_date
There are few issues regarding this solution:
1. This logic will not work if MyDate column have TIME data in it, So MyDate should be cast as date.
2. You need to have distinct value in Eom to avoid getting the same result.
3. Its would be ending up like a Sub Query written Select statement ( Old School Style).
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply