November 6, 2013 at 2:40 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.
.
.
.
November 6, 2013 at 4:01 am
Please do not crosspost. It wastes peoples time and fragments replies.
Original thread with replies:
http://www.sqlservercentral.com/Forums/Topic1511755-3077-1.aspx
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:11 am
Stab in the dark because you haven't been very clear in what you want to do: -
-- 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
SELECT
COUNT(DISTINCT CASE WHEN saledate BETWEEN lookupTable.startDate AND lookupTable.endDate THEN custname END),
'BETWEEN '+CAST(lookupTable.startDate AS VARCHAR(MAX))+' AND '+CAST(lookupTable.endDate AS VARCHAR(MAX))
FROM #sample
CROSS 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)
GROUP BY 'BETWEEN '+CAST(lookupTable.startDate AS VARCHAR(MAX))+' AND '+CAST(lookupTable.endDate AS VARCHAR(MAX));
-- 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
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
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 😉
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply