November 6, 2013 at 2:34 am
Hi There,
I need distinct customer count for a certain time slab.
Consider the following table,
create table #sample (saledate datetime,custname VARCHAR(100))
Insert into #sample values ('11/1/2013','A')
Insert into #sample values ('11/2/2013','B')
Insert into #sample values ('11/3/2013','A')
Insert into #sample values ('11/4/2013','B')
Insert into #sample values ('11/5/2013','E')
Insert into #sample values ('11/6/2013','W')
Insert into #sample values ('11/7/2013','B')
Insert into #sample values ('11/8/2013','E')
Insert into #sample values ('11/9/2013','C')
Insert into #sample values ('11/10/2013','D')
From the above table I need to find distinct count of custname for the below time slabs,
BETWEEN '2013-11-01 00:00:00.000' AND '2013-11-06 00:00:00.000'
BETWEEN '2013-11-02 00:00:00.000' AND '2013-11-07 00:00:00.000'
BETWEEN '2013-11-03 00:00:00.000' AND '2013-11-08 00:00:00.000'
BETWEEN '2013-11-04 00:00:00.000' AND '2013-11-09 00:00:00.000'
BETWEEN '2013-11-05 00:00:00.000' AND '2013-11-10 00:00:00.000'
To achieve the above i'm using the following query,
select COUNT(DISTINCT CUSTNAME ) FROM #sample WHERE saledate BETWEEN '2013-11-01 00:00:00.000' AND '2013-11-06 00:00:00.000'
select COUNT(DISTINCT CUSTNAME ) FROM #sample WHERE saledate BETWEEN '2013-11-02 00:00:00.000' AND '2013-11-07 00:00:00.000'
select COUNT(DISTINCT CUSTNAME ) FROM #sample WHERE saledate BETWEEN '2013-11-03 00:00:00.000' AND '2013-11-08 00:00:00.000'
select COUNT(DISTINCT CUSTNAME ) FROM #sample WHERE saledate BETWEEN '2013-11-04 00:00:00.000' AND '2013-11-09 00:00:00.000'
select COUNT(DISTINCT CUSTNAME ) FROM #sample WHERE saledate BETWEEN '2013-11-05 00:00:00.000' AND '2013-11-10 00:00:00.000'
Please help me out the best possible way.
.
.
.:crying::crying::crying::crying::crying::crying:
November 6, 2013 at 3:59 am
What do you exactly want to optimize? we coudnt understand your requirement.
Regards
Durai Nagarajan
November 6, 2013 at 3:59 am
You already have queries, so what's the question?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 6, 2013 at 4:27 am
you need 5 different results from 5 different filter(mentioned above) so your queries will work as expected.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
November 6, 2013 at 4:31 am
Cadavre (11/6/2013)
Stab in the dark because you haven't been very clear in what you want to do: -
-- Sample dataIF object_id('tempdb..#sample') IS NOT NULL
BEGIN;
DROP TABLE #sample;
END;
CREATE TABLE #sample (saledate DATETIME, custname VARCHAR(100));
INSERT INTO #sample
VALUES ('11/1/2013','A'),('11/2/2013','B'),('11/3/2013','A'),
('11/4/2013','B'),('11/5/2013','E'),('11/6/2013','W'),
('11/7/2013','B'),('11/8/2013','E'),('11/9/2013','C'),
('11/10/2013','D');
-- Solution
SELECT
COUNT(DISTINCT custname),
'BETWEEN '+CAST(lookupTable.startDate AS VARCHAR(MAX))+' AND '+CAST(lookupTable.endDate AS VARCHAR(MAX))
FROM #sample
INNER JOIN (VALUES('2013-11-01 00:00:00.000','2013-11-06 00:00:00.000'),
('2013-11-02 00:00:00.000','2013-11-07 00:00:00.000'),
('2013-11-03 00:00:00.000','2013-11-08 00:00:00.000'),
('2013-11-04 00:00:00.000','2013-11-09 00:00:00.000'),
('2013-11-05 00:00:00.000','2013-11-10 00:00:00.000')
)lookupTable(startDate, endDate) ON saledate BETWEEN lookupTable.startDate AND lookupTable.endDate
GROUP BY 'BETWEEN '+CAST(lookupTable.startDate AS VARCHAR(MAX))+' AND '+CAST(lookupTable.endDate AS VARCHAR(MAX));
Returns: -
----------- -------------------------------------------------------------4 BETWEEN 2013-11-01 00:00:00.000 AND 2013-11-06 00:00:00.000
4 BETWEEN 2013-11-02 00:00:00.000 AND 2013-11-07 00:00:00.000
4 BETWEEN 2013-11-03 00:00:00.000 AND 2013-11-08 00:00:00.000
4 BETWEEN 2013-11-04 00:00:00.000 AND 2013-11-09 00:00:00.000
5 BETWEEN 2013-11-05 00:00:00.000 AND 2013-11-10 00:00:00.000
--EDIT--Koen Verbeeck (11/6/2013)
Please do not crosspost. It wastes peoples time and fragments replies.Original thread with replies:
http://www.sqlservercentral.com/Forums/Topic1511755-3077-1.aspx
Blergh! Sorry, was typing as you posted that otherwise I'd have replied to the original thread.
And that is the point that Koen was making about fragmenting replies 😉
November 6, 2013 at 5:21 am
Thanks for your replies friends...
* My query works what I'm intended to do.
* My expectation is I dont want to hard code the date.
I tried myself, following is my query
create table #count (Date datetime,count int)
Declare @Strtdate datetime
set @Strtdate = '2013-10-01 00:00:00.000'
Truncate table #count
while (@Strtdate < = '2013-10-31 00:00:00.000')
begin
insert into #count
select @strtdate
,count(distinct custname)
from #Sample
where DATE between convert(varchar(100), @strtdate-30,112) and convert(varchar(100), @strtdate,112)
set @Strtdate = @Strtdate +1
end
It responses me but little slower..
Please suggest me any other good way...
November 6, 2013 at 5:50 am
vignesh.ms (11/6/2013)
Thanks for your replies friends...* My query works what I'm intended to do.
* My expectation is I dont want to hard code the date.
I tried myself, following is my query
create table #count (Date datetime,count int)
Declare @Strtdate datetime
set @Strtdate = '2013-10-01 00:00:00.000'
Truncate table #count
while (@Strtdate < = '2013-10-31 00:00:00.000')
begin
insert into #count
select @strtdate
,count(distinct custname)
from #Sample
where DATE between convert(varchar(100), @strtdate-30,112) and convert(varchar(100), @strtdate,112)
set @Strtdate = @Strtdate +1
end
It responses me but little slower..
Please suggest me any other good way...
That is completely different from your original requirement. If I change your start date and your end date variables to fit with your original sample data, here's the results from your new query: -
Date count
----------------------- -----------
2013-11-01 00:00:00.000 1
2013-11-02 00:00:00.000 2
2013-11-03 00:00:00.000 2
2013-11-04 00:00:00.000 2
2013-11-05 00:00:00.000 3
2013-11-06 00:00:00.000 4
2013-11-07 00:00:00.000 4
2013-11-08 00:00:00.000 4
2013-11-09 00:00:00.000 5
2013-11-10 00:00:00.000 6
2013-11-11 00:00:00.000 6
2013-11-12 00:00:00.000 6
2013-11-13 00:00:00.000 6
2013-11-14 00:00:00.000 6
2013-11-15 00:00:00.000 6
2013-11-16 00:00:00.000 6
2013-11-17 00:00:00.000 6
2013-11-18 00:00:00.000 6
2013-11-19 00:00:00.000 6
2013-11-20 00:00:00.000 6
2013-11-21 00:00:00.000 6
2013-11-22 00:00:00.000 6
2013-11-23 00:00:00.000 6
2013-11-24 00:00:00.000 6
2013-11-25 00:00:00.000 6
2013-11-26 00:00:00.000 6
2013-11-27 00:00:00.000 6
2013-11-28 00:00:00.000 6
2013-11-29 00:00:00.000 6
2013-11-30 00:00:00.000 6
Let's compare that to your previous combination of queries: -
'2013-11-01 00:00:00.000' AND '2013-11-06 00:00:00.000'
-------------------------------------------------------
4
'2013-11-02 00:00:00.000' AND '2013-11-07 00:00:00.000'
-------------------------------------------------------
4
'2013-11-03 00:00:00.000' AND '2013-11-08 00:00:00.000'
-------------------------------------------------------
4
'2013-11-04 00:00:00.000' AND '2013-11-09 00:00:00.000'
-------------------------------------------------------
4
'2013-11-05 00:00:00.000' AND '2013-11-10 00:00:00.000'
-------------------------------------------------------
5
Logically, you're not doing the same thing as you were before. What do you actually want to do here?
November 6, 2013 at 6:05 am
Here's some code that replaces your loop and does the same job: -
-- Sample data
IF object_id('tempdb..#sample') IS NOT NULL
BEGIN;
DROP TABLE #sample;
END;
CREATE TABLE #sample (saledate DATETIME, custname VARCHAR(100));
INSERT INTO #sample
VALUES ('11/1/2013','A'),('11/2/2013','B'),('11/3/2013','A'),
('11/4/2013','B'),('11/5/2013','E'),('11/6/2013','W'),
('11/7/2013','B'),('11/8/2013','E'),('11/9/2013','C'),
('11/10/2013','D');
-- Solution
DECLARE @StartDate DATE = '2013-11-01', @EndDate DATE = '2013-11-30';
WITH cteX(N) AS
(
SELECT 1
FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))a(N)
),
cteC(N) AS
(
SELECT 1
FROM cteX x
CROSS JOIN cteX y
),
cteMMMMMMMMMM(N) AS
(
SELECT 1
FROM cteC x
CROSS JOIN cteC y
),
tally(N) AS
(
SELECT TOP (CASE WHEN @EndDate > @StartDate THEN DATEDIFF(DAY,@StartDate,@EndDate)+1 ELSE 0 END)
DATEADD(DAY,ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1,@StartDate)
FROM cteMMMMMMMMMM x
CROSS JOIN cteMMMMMMMMMM y
),
lookupTable(startDate,endDate) AS
(
SELECT DATEADD(DAY,-30,N), N
FROM tally
)
SELECT
COUNT(DISTINCT custname) AS [Count],
CAST(endDate AS DATETIME) AS [Date]
FROM lookupTable
LEFT OUTER JOIN #sample ON saledate BETWEEN lookupTable.startDate AND lookupTable.endDate
GROUP BY CAST(endDate AS DATETIME);
This returns: -
Count Date
----------- -----------------------
1 2013-11-01 00:00:00.000
2 2013-11-02 00:00:00.000
2 2013-11-03 00:00:00.000
2 2013-11-04 00:00:00.000
3 2013-11-05 00:00:00.000
4 2013-11-06 00:00:00.000
4 2013-11-07 00:00:00.000
4 2013-11-08 00:00:00.000
5 2013-11-09 00:00:00.000
6 2013-11-10 00:00:00.000
6 2013-11-11 00:00:00.000
6 2013-11-12 00:00:00.000
6 2013-11-13 00:00:00.000
6 2013-11-14 00:00:00.000
6 2013-11-15 00:00:00.000
6 2013-11-16 00:00:00.000
6 2013-11-17 00:00:00.000
6 2013-11-18 00:00:00.000
6 2013-11-19 00:00:00.000
6 2013-11-20 00:00:00.000
6 2013-11-21 00:00:00.000
6 2013-11-22 00:00:00.000
6 2013-11-23 00:00:00.000
6 2013-11-24 00:00:00.000
6 2013-11-25 00:00:00.000
6 2013-11-26 00:00:00.000
6 2013-11-27 00:00:00.000
6 2013-11-28 00:00:00.000
6 2013-11-29 00:00:00.000
6 2013-11-30 00:00:00.000
And this is some code that uses the same logic that you were using before (groups of 5 day chunks) but without needing a hard-coded lookup table: -
-- Sample data
IF object_id('tempdb..#sample') IS NOT NULL
BEGIN;
DROP TABLE #sample;
END;
CREATE TABLE #sample (saledate DATETIME, custname VARCHAR(100));
INSERT INTO #sample
VALUES ('11/1/2013','A'),('11/2/2013','B'),('11/3/2013','A'),
('11/4/2013','B'),('11/5/2013','E'),('11/6/2013','W'),
('11/7/2013','B'),('11/8/2013','E'),('11/9/2013','C'),
('11/10/2013','D');
-- Solution
DECLARE @StartDate DATE = '2013-11-01', @EndDate DATE = '2013-11-30';
WITH cteX(N) AS
(
SELECT 1
FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))a(N)
),
cteC(N) AS
(
SELECT 1
FROM cteX x
CROSS JOIN cteX y
),
cteMMMMMMMMMM(N) AS
(
SELECT 1
FROM cteC x
CROSS JOIN cteC y
),
tally(N) AS
(
SELECT TOP (CASE WHEN @EndDate > @StartDate THEN DATEDIFF(DAY,@StartDate,@EndDate)+1 ELSE 0 END)
DATEADD(DAY,ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1,@StartDate)
FROM cteMMMMMMMMMM x
CROSS JOIN cteMMMMMMMMMM y
),
lookupTable(startDate,endDate) AS
(
SELECT N, DATEADD(DAY,5,N)
FROM tally
WHERE DATEADD(DAY,5,N) <= @EndDate
)
SELECT
COUNT(DISTINCT custname) AS [Count],
[Between Dates]
FROM lookupTable
LEFT OUTER JOIN #sample ON saledate BETWEEN lookupTable.startDate AND lookupTable.endDate
CROSS APPLY (SELECT CAST(lookupTable.startDate AS VARCHAR(MAX))+' AND '+CAST(lookupTable.endDate AS VARCHAR(MAX))) ca([Between Dates])
GROUP BY [Between Dates];
Returns: -
Count Between Dates
----------- --------------------------
4 2013-11-01 AND 2013-11-06
4 2013-11-02 AND 2013-11-07
4 2013-11-03 AND 2013-11-08
4 2013-11-04 AND 2013-11-09
5 2013-11-05 AND 2013-11-10
5 2013-11-06 AND 2013-11-11
4 2013-11-07 AND 2013-11-12
3 2013-11-08 AND 2013-11-13
2 2013-11-09 AND 2013-11-14
1 2013-11-10 AND 2013-11-15
0 2013-11-11 AND 2013-11-16
0 2013-11-12 AND 2013-11-17
0 2013-11-13 AND 2013-11-18
0 2013-11-14 AND 2013-11-19
0 2013-11-15 AND 2013-11-20
0 2013-11-16 AND 2013-11-21
0 2013-11-17 AND 2013-11-22
0 2013-11-18 AND 2013-11-23
0 2013-11-19 AND 2013-11-24
0 2013-11-20 AND 2013-11-25
0 2013-11-21 AND 2013-11-26
0 2013-11-22 AND 2013-11-27
0 2013-11-23 AND 2013-11-28
0 2013-11-24 AND 2013-11-29
0 2013-11-25 AND 2013-11-30
November 6, 2013 at 7:32 am
Cadavre (11/6/2013)
Here's some code that replaces your loop and does the same job: -
-- Sample dataIF object_id('tempdb..#sample') IS NOT NULL
BEGIN;
DROP TABLE #sample;
END;
CREATE TABLE #sample (saledate DATETIME, custname VARCHAR(100));
INSERT INTO #sample
VALUES ('11/1/2013','A'),('11/2/2013','B'),('11/3/2013','A'),
('11/4/2013','B'),('11/5/2013','E'),('11/6/2013','W'),
('11/7/2013','B'),('11/8/2013','E'),('11/9/2013','C'),
('11/10/2013','D');
-- Solution
DECLARE @StartDate DATE = '2013-11-01', @EndDate DATE = '2013-11-30';
WITH cteX(N) AS
(
SELECT 1
FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))a(N)
),
cteC(N) AS
(
SELECT 1
FROM cteX x
CROSS JOIN cteX y
),
cteMMMMMMMMMM(N) AS
(
SELECT 1
FROM cteC x
CROSS JOIN cteC y
),
tally(N) AS
(
SELECT TOP (CASE WHEN @EndDate > @StartDate THEN DATEDIFF(DAY,@StartDate,@EndDate)+1 ELSE 0 END)
DATEADD(DAY,ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1,@StartDate)
FROM cteMMMMMMMMMM x
CROSS JOIN cteMMMMMMMMMM y
),
lookupTable(startDate,endDate) AS
(
SELECT DATEADD(DAY,-30,N), N
FROM tally
)
SELECT
COUNT(DISTINCT custname) AS [Count],
CAST(endDate AS DATETIME) AS [Date]
FROM lookupTable
LEFT OUTER JOIN #sample ON saledate BETWEEN lookupTable.startDate AND lookupTable.endDate
GROUP BY CAST(endDate AS DATETIME);
This returns: -
Count Date----------- -----------------------
1 2013-11-01 00:00:00.000
2 2013-11-02 00:00:00.000
2 2013-11-03 00:00:00.000
2 2013-11-04 00:00:00.000
3 2013-11-05 00:00:00.000
4 2013-11-06 00:00:00.000
4 2013-11-07 00:00:00.000
4 2013-11-08 00:00:00.000
5 2013-11-09 00:00:00.000
6 2013-11-10 00:00:00.000
6 2013-11-11 00:00:00.000
6 2013-11-12 00:00:00.000
6 2013-11-13 00:00:00.000
6 2013-11-14 00:00:00.000
6 2013-11-15 00:00:00.000
6 2013-11-16 00:00:00.000
6 2013-11-17 00:00:00.000
6 2013-11-18 00:00:00.000
6 2013-11-19 00:00:00.000
6 2013-11-20 00:00:00.000
6 2013-11-21 00:00:00.000
6 2013-11-22 00:00:00.000
6 2013-11-23 00:00:00.000
6 2013-11-24 00:00:00.000
6 2013-11-25 00:00:00.000
6 2013-11-26 00:00:00.000
6 2013-11-27 00:00:00.000
6 2013-11-28 00:00:00.000
6 2013-11-29 00:00:00.000
6 2013-11-30 00:00:00.000
And this is some code that uses the same logic that you were using before (groups of 5 day chunks) but without needing a hard-coded lookup table: -
-- Sample dataIF object_id('tempdb..#sample') IS NOT NULL
BEGIN;
DROP TABLE #sample;
END;
CREATE TABLE #sample (saledate DATETIME, custname VARCHAR(100));
INSERT INTO #sample
VALUES ('11/1/2013','A'),('11/2/2013','B'),('11/3/2013','A'),
('11/4/2013','B'),('11/5/2013','E'),('11/6/2013','W'),
('11/7/2013','B'),('11/8/2013','E'),('11/9/2013','C'),
('11/10/2013','D');
-- Solution
DECLARE @StartDate DATE = '2013-11-01', @EndDate DATE = '2013-11-30';
WITH cteX(N) AS
(
SELECT 1
FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))a(N)
),
cteC(N) AS
(
SELECT 1
FROM cteX x
CROSS JOIN cteX y
),
cteMMMMMMMMMM(N) AS
(
SELECT 1
FROM cteC x
CROSS JOIN cteC y
),
tally(N) AS
(
SELECT TOP (CASE WHEN @EndDate > @StartDate THEN DATEDIFF(DAY,@StartDate,@EndDate)+1 ELSE 0 END)
DATEADD(DAY,ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1,@StartDate)
FROM cteMMMMMMMMMM x
CROSS JOIN cteMMMMMMMMMM y
),
lookupTable(startDate,endDate) AS
(
SELECT N, DATEADD(DAY,5,N)
FROM tally
WHERE DATEADD(DAY,5,N) <= @EndDate
)
SELECT
COUNT(DISTINCT custname) AS [Count],
[Between Dates]
FROM lookupTable
LEFT OUTER JOIN #sample ON saledate BETWEEN lookupTable.startDate AND lookupTable.endDate
CROSS APPLY (SELECT CAST(lookupTable.startDate AS VARCHAR(MAX))+' AND '+CAST(lookupTable.endDate AS VARCHAR(MAX))) ca([Between Dates])
GROUP BY [Between Dates];
Returns: -
Count Between Dates----------- --------------------------
4 2013-11-01 AND 2013-11-06
4 2013-11-02 AND 2013-11-07
4 2013-11-03 AND 2013-11-08
4 2013-11-04 AND 2013-11-09
5 2013-11-05 AND 2013-11-10
5 2013-11-06 AND 2013-11-11
4 2013-11-07 AND 2013-11-12
3 2013-11-08 AND 2013-11-13
2 2013-11-09 AND 2013-11-14
1 2013-11-10 AND 2013-11-15
0 2013-11-11 AND 2013-11-16
0 2013-11-12 AND 2013-11-17
0 2013-11-13 AND 2013-11-18
0 2013-11-14 AND 2013-11-19
0 2013-11-15 AND 2013-11-20
0 2013-11-16 AND 2013-11-21
0 2013-11-17 AND 2013-11-22
0 2013-11-18 AND 2013-11-23
0 2013-11-19 AND 2013-11-24
0 2013-11-20 AND 2013-11-25
0 2013-11-21 AND 2013-11-26
0 2013-11-22 AND 2013-11-27
0 2013-11-23 AND 2013-11-28
0 2013-11-24 AND 2013-11-29
0 2013-11-25 AND 2013-11-30
Thanks for your understanding
Please explain how it works...?
November 6, 2013 at 7:33 am
@ Cadavre :
Thanks for your understanding
Please explain how it works...?
November 6, 2013 at 9:16 am
vignesh.ms (11/6/2013)
@ Cadavre :Thanks for your understanding
Please explain how it works...?
Note: This is the second version of this explanation as the forums ate the first explanation. No promises that it's as detailed as it should be, as my patience was a little thinner after having my first post eaten.
I will, but first, can you do me a favour? Have a read through this article: - http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]. Jeff Moden explains what a numbers/tally table is and how it can replace a loop far better than I ever could and the solution I have offered is just a join against a numbers table.
Once you've finished the article, come back to this post and have a read through this. I'll explain how I've used cascading common table expressions to generate an inline numbers table to use for creating the dates that we need to join to.
Let's start off with the root common table expression: -
WITH cteX(N) AS
(
SELECT 1
FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))a(N)
)
SELECT *
FROM cteX;
I called this one cteX because it is a common table expression (cte) that holds 10 rows (X) of data. If you create a cartesian product of cteX with itself, you get 10*10 rows (100): -
WITH cteX(N) AS
(
SELECT 1
FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))a(N)
)
SELECT *
FROM cteX x
CROSS JOIN cteX y;
That is the basis of the next one, which I called cteC because it is a common table expression (cte) that holds 100 rows (C) of data.
WITH cteX(N) AS
(
SELECT 1
FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))a(N)
),
cteC(N) AS
(
SELECT 1
FROM cteX x
CROSS JOIN cteX y
)
SELECT *
FROM cteC;
If you create a cartesian product of cteC with itself, yu get 100*100 rows (10,000): -
WITH cteX(N) AS
(
SELECT 1
FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))a(N)
),
cteC(N) AS
(
SELECT 1
FROM cteX x
CROSS JOIN cteX y
)
SELECT *
FROM cteC x
CROSS JOIN cteC y;
That is the basis of the next one, which I called cteMMMMMMMMMM because it is a common table expression (cte) that holds 10,000 rows (MMMMMMMMMM) of data.
WITH cteX(N) AS
(
SELECT 1
FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))a(N)
),
cteC(N) AS
(
SELECT 1
FROM cteX x
CROSS JOIN cteX y
),
cteMMMMMMMMMM(N) AS
(
SELECT 1
FROM cteC x
CROSS JOIN cteC y
)
SELECT *
FROM cteMMMMMMMMMM;
Finally, for the next one, we create a cartesian product of cteMMMMMMMMMM with itself, to get 10,000*10,000 rows (100,000,000). This is more than you could ever need for this task, as there are 2,958,463 days between 1900-01-01 and 9999-12-31, which I'm guessing is outside of the scope of your problem! :p
Of course, we don't actually want to generate 100 million rows, as this will take a long time. So instead, we use a TOP clause to filter it down to the number of rows that we actually require.
DECLARE @StartDate DATE = '2013-11-01', @EndDate DATE = '2013-11-30';
WITH cteX(N) AS
(
SELECT 1
FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))a(N)
),
cteC(N) AS
(
SELECT 1
FROM cteX x
CROSS JOIN cteX y
),
cteMMMMMMMMMM(N) AS
(
SELECT 1
FROM cteC x
CROSS JOIN cteC y
)
SELECT TOP (DATEDIFF(DAY,@StartDate,@EndDate))
1
FROM cteMMMMMMMMMM x
CROSS JOIN cteMMMMMMMMMM y;
Ah, wait. A bit of sanity checking is required there. What if the number returned by the DATEDIFF is a minus number? Shall we take a look?
DECLARE @StartDate DATE = '2013-11-01', @EndDate DATE = '2013-10-30';
WITH cteX(N) AS
(
SELECT 1
FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))a(N)
),
cteC(N) AS
(
SELECT 1
FROM cteX x
CROSS JOIN cteX y
),
cteMMMMMMMMMM(N) AS
(
SELECT 1
FROM cteC x
CROSS JOIN cteC y
)
SELECT TOP (DATEDIFF(DAY,@StartDate,@EndDate))
1
FROM cteMMMMMMMMMM x
CROSS JOIN cteMMMMMMMMMM y;
Msg 127, Level 15, State 1, Line 3
A TOP N value may not be negative.
So negative numbers are bad and we should code around them. For this, I used a case statement.
DECLARE @StartDate DATE = '2013-11-01', @EndDate DATE = '2013-10-30';
WITH cteX(N) AS
(
SELECT 1
FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))a(N)
),
cteC(N) AS
(
SELECT 1
FROM cteX x
CROSS JOIN cteX y
),
cteMMMMMMMMMM(N) AS
(
SELECT 1
FROM cteC x
CROSS JOIN cteC y
)
SELECT TOP (CASE WHEN @EndDate > @StartDate THEN DATEDIFF(DAY,@StartDate,@EndDate) ELSE 0 END)
1
FROM cteMMMMMMMMMM x
CROSS JOIN cteMMMMMMMMMM y;
This now returns an empty result set when a minus figure is passed in, rather than an error.
Let's get back on track.
We want to turn the 29 "1" rows of data into a numbers table, for this we use the row_number ranking function.
DECLARE @StartDate DATE = '2013-11-01', @EndDate DATE = '2013-11-30';
WITH cteX(N) AS
(
SELECT 1
FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))a(N)
),
cteC(N) AS
(
SELECT 1
FROM cteX x
CROSS JOIN cteX y
),
cteMMMMMMMMMM(N) AS
(
SELECT 1
FROM cteC x
CROSS JOIN cteC y
)
SELECT TOP (CASE WHEN @EndDate > @StartDate THEN DATEDIFF(DAY,@StartDate,@EndDate) ELSE 0 END)
ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM cteMMMMMMMMMM x
CROSS JOIN cteMMMMMMMMMM y;
As a point of interest, I've used ROW_NUMBER() OVER(ORDER BY (SELECT NULL)). The reason I have ordered by SELECT NULL is because this row number is unordered. By that I mean that the underlying dataset is unordered and I like to be able to glance at a piece of code and have some understanding of what it is doing. You could have used ROW_NUMBER() OVER(ORDER BY N), but to me this is incorrect as it implies that your row number is ordered by N which it can't be as N only contains the value "1".
OK, so that gives us numbers from 1 to 29. But there are 30 days, so we want to expand the TOP clause by 1. Also, it'd be better to start from 0 as we intend to use these numbers with the DATEADD function.
DECLARE @StartDate DATE = '2013-11-01', @EndDate DATE = '2013-11-30';
WITH cteX(N) AS
(
SELECT 1
FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))a(N)
),
cteC(N) AS
(
SELECT 1
FROM cteX x
CROSS JOIN cteX y
),
cteMMMMMMMMMM(N) AS
(
SELECT 1
FROM cteC x
CROSS JOIN cteC y
)
SELECT TOP (CASE WHEN @EndDate > @StartDate THEN DATEDIFF(DAY,@StartDate,@EndDate)+1 ELSE 0 END)
ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1
FROM cteMMMMMMMMMM x
CROSS JOIN cteMMMMMMMMMM y;
That gives us numbers from 0 to 29.
Now we can use DATEADD to generate the start dates that we require.
DECLARE @StartDate DATE = '2013-11-01', @EndDate DATE = '2013-11-30';
WITH cteX(N) AS
(
SELECT 1
FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))a(N)
),
cteC(N) AS
(
SELECT 1
FROM cteX x
CROSS JOIN cteX y
),
cteMMMMMMMMMM(N) AS
(
SELECT 1
FROM cteC x
CROSS JOIN cteC y
)
SELECT TOP (CASE WHEN @EndDate > @StartDate THEN DATEDIFF(DAY,@StartDate,@EndDate)+1 ELSE 0 END)
DATEADD(DAY,ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1,@StartDate)
FROM cteMMMMMMMMMM x
CROSS JOIN cteMMMMMMMMMM y;
So that returns dates from 2013-11-01 to 2013-11-30. Perfect, we have every day between our start date variable and our end date variable. Let's stick this in another common table expression, this time called tally.
DECLARE @StartDate DATE = '2013-11-01', @EndDate DATE = '2013-11-30';
WITH cteX(N) AS
(
SELECT 1
FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))a(N)
),
cteC(N) AS
(
SELECT 1
FROM cteX x
CROSS JOIN cteX y
),
cteMMMMMMMMMM(N) AS
(
SELECT 1
FROM cteC x
CROSS JOIN cteC y
),
tally(N) AS
(
SELECT TOP (CASE WHEN @EndDate > @StartDate THEN DATEDIFF(DAY,@StartDate,@EndDate)+1 ELSE 0 END)
DATEADD(DAY,ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1,@StartDate)
FROM cteMMMMMMMMMM x
CROSS JOIN cteMMMMMMMMMM y
)
SELECT N
FROM tally;
From this, it's fairly trivial to generate the end dates (5 days + the start date): -
DECLARE @StartDate DATE = '2013-11-01', @EndDate DATE = '2013-11-30';
WITH cteX(N) AS
(
SELECT 1
FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))a(N)
),
cteC(N) AS
(
SELECT 1
FROM cteX x
CROSS JOIN cteX y
),
cteMMMMMMMMMM(N) AS
(
SELECT 1
FROM cteC x
CROSS JOIN cteC y
),
tally(N) AS
(
SELECT TOP (CASE WHEN @EndDate > @StartDate THEN DATEDIFF(DAY,@StartDate,@EndDate)+1 ELSE 0 END)
DATEADD(DAY,ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1,@StartDate)
FROM cteMMMMMMMMMM x
CROSS JOIN cteMMMMMMMMMM y
)
SELECT N, DATEADD(DAY,5,N)
FROM tally;
Wait, this includes time periods such as 2013-11-26 to 2013-12-01, which spills outside of our end date variable. Let's filter it down a bit.
DECLARE @StartDate DATE = '2013-11-01', @EndDate DATE = '2013-11-30';
WITH cteX(N) AS
(
SELECT 1
FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))a(N)
),
cteC(N) AS
(
SELECT 1
FROM cteX x
CROSS JOIN cteX y
),
cteMMMMMMMMMM(N) AS
(
SELECT 1
FROM cteC x
CROSS JOIN cteC y
),
tally(N) AS
(
SELECT TOP (CASE WHEN @EndDate > @StartDate THEN DATEDIFF(DAY,@StartDate,@EndDate)+1 ELSE 0 END)
DATEADD(DAY,ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1,@StartDate)
FROM cteMMMMMMMMMM x
CROSS JOIN cteMMMMMMMMMM y
)
SELECT N, DATEADD(DAY,5,N)
FROM tally
WHERE DATEADD(DAY,5,N) <= @EndDate;
Take a look at what we've produced there, then take another look at the original solution that I offered. What we've done is essentially create the values for the "lookupTable".
Using this sample data: -
-- Sample data
IF object_id('tempdb..#sample') IS NOT NULL
BEGIN;
DROP TABLE #sample;
END;
CREATE TABLE #sample (saledate DATETIME, custname VARCHAR(100));
INSERT INTO #sample
VALUES ('11/1/2013','A'),('11/2/2013','B'),('11/3/2013','A'),
('11/4/2013','B'),('11/5/2013','E'),('11/6/2013','W'),
('11/7/2013','B'),('11/8/2013','E'),('11/9/2013','C'),
('11/10/2013','D');
This: -
SELECT
COUNT(DISTINCT custname) AS [Count],
[Between Dates]
FROM (VALUES('2013-11-01','2013-11-06'),
('2013-11-02','2013-11-07'),
('2013-11-03','2013-11-08'),
('2013-11-04','2013-11-09'),
('2013-11-05','2013-11-10'),
('2013-11-06','2013-11-11'),
('2013-11-07','2013-11-12'),
('2013-11-08','2013-11-13'),
('2013-11-09','2013-11-14'),
('2013-11-10','2013-11-15'),
('2013-11-11','2013-11-16'),
('2013-11-12','2013-11-17'),
('2013-11-13','2013-11-18'),
('2013-11-14','2013-11-19'),
('2013-11-15','2013-11-20'),
('2013-11-16','2013-11-21'),
('2013-11-17','2013-11-22'),
('2013-11-18','2013-11-23'),
('2013-11-19','2013-11-24'),
('2013-11-20','2013-11-25'),
('2013-11-21','2013-11-26'),
('2013-11-22','2013-11-27'),
('2013-11-23','2013-11-28'),
('2013-11-24','2013-11-29'),
('2013-11-25','2013-11-30')
)lookupTable(startDate,endDate)
LEFT OUTER JOIN #sample ON saledate BETWEEN lookupTable.startDate AND lookupTable.endDate
CROSS APPLY (SELECT CAST(lookupTable.startDate AS VARCHAR(MAX))+' AND '+CAST(lookupTable.endDate AS VARCHAR(MAX))) ca([Between Dates])
GROUP BY [Between Dates];
Is the same as: -
DECLARE @StartDate DATE = '2013-11-01', @EndDate DATE = '2013-11-30';
WITH cteX(N) AS
(
SELECT 1
FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))a(N)
),
cteC(N) AS
(
SELECT 1
FROM cteX x
CROSS JOIN cteX y
),
cteMMMMMMMMMM(N) AS
(
SELECT 1
FROM cteC x
CROSS JOIN cteC y
),
tally(N) AS
(
SELECT TOP (CASE WHEN @EndDate > @StartDate THEN DATEDIFF(DAY,@StartDate,@EndDate)+1 ELSE 0 END)
DATEADD(DAY,ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1,@StartDate)
FROM cteMMMMMMMMMM x
CROSS JOIN cteMMMMMMMMMM y
),
lookupTable(startDate,endDate) AS
(
SELECT N, DATEADD(DAY,5,N)
FROM tally
WHERE DATEADD(DAY,5,N) <= @EndDate
)
SELECT
COUNT(DISTINCT custname) AS [Count],
[Between Dates]
FROM lookupTable
LEFT OUTER JOIN #sample ON saledate BETWEEN lookupTable.startDate AND lookupTable.endDate
CROSS APPLY (SELECT CAST(lookupTable.startDate AS VARCHAR(MAX))+' AND '+CAST(lookupTable.endDate AS VARCHAR(MAX))) ca([Between Dates])
GROUP BY [Between Dates];
Do you understand?
I'm hoping that this time the forum doesn't eat my reply. . . here goes.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply