December 22, 2010 at 12:55 pm
I have the following 2 tables
table: ReportDate
it contains the following data:
rptdte
8/6/2010
8/7/2010
8/8/2010
8/9/2010
8/10/2010
8/11/2010
8/12/2010
table: CustomerInfo
it containt the following data:
ID Stat StatDte
1 A 8/1/2010
1 X 8/9/2010
1 A 8/11/2010
2 A 7/16/2010
2 X 8/7/2010
3 A 8/10/2010
3 X 8/12/2010
desired results:
Report Date Customer Count (have an A status)
8/6/2010 2 (customers 1 and 2)
8/7/2010 1 (customer 1)
8/8/2010 1 (customer 1)
8/9/2010 0 (no customers)
8/10/2010 1 (customer 3)
8/11/2010 2 (customer 1 and 3)
8/12/2010 1 (customer 1)
So what I'm needing is to create a report that will show specific report dates (8/6, 8/7, 8/8, etc...) for each of those dates, I need to know how many customers have an "A" status where the status date is before the specific report date.
December 22, 2010 at 1:15 pm
Doesn't sound too bad. Now if there were some table create scripts and some sample data people would be easily able to help you. If you help us help you, you are much more likely to get some help. Most people, myself included, are not likely to generate tables based on loose definitions and then generate some test data.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 22, 2010 at 1:38 pm
Sean, sorry here is some table scripts - hope this helps, if you need more info I will gladly supply.
CREATE TABLE dbo.ReportDate
(
dt SMALLDATETIME NOT NULL
PRIMARY KEY CLUSTERED
)
**********
SET NOCOUNT ON
DECLARE @dt SMALLDATETIME
SET @dt = '20100806'
WHILE @dt < '20100813'
BEGIN
INSERT dbo.ReportDate(dt) SELECT @dt
END
**********
CREATE TABLE dbo.CustomerInfo
(
ID int NOT NULL
PRIMARY KEY CLUSTERED,
CustomerStatus varchar(1),
StatusDate SMALLDATETIME NOT NULL
)
***********
INSERT INTO dbo.CustomerInfo (ID, CustomerStatus, StatusDate) VALUES (1, 'A', 8/1/2010)
INSERT INTO dbo.CustomerInfo (ID, CustomerStatus, StatusDate) VALUES (1, 'X', 8/9/2010)
INSERT INTO dbo.CustomerInfo (ID, CustomerStatus, StatusDate) VALUES (1, 'A', 8/11/2010)
INSERT INTO dbo.CustomerInfo (ID, CustomerStatus, StatusDate) VALUES (2, 'A', 7/16/2010)
INSERT INTO dbo.CustomerInfo (ID, CustomerStatus, StatusDate) VALUES (2, 'X', 8/7/2010)
INSERT INTO dbo.CustomerInfo (ID, CustomerStatus, StatusDate) VALUES (3, 'A', 8/10/2010)
INSERT INTO dbo.CustomerInfo (ID, CustomerStatus, StatusDate) VALUES (3, 'X', 8/12/2010)
***********
Desired Results:
Report Date Customer Count (have an A status)
8/6/2010 2 (customers 1 and 2)
8/7/2010 1 (customer 1)
8/8/2010 1 (customer 1)
8/9/2010 0 (no customers)
8/10/2010 1 (customer 3)
8/11/2010 2 (customer 1 and 3)
8/12/2010 1 (customer 1)
So what I'm needing is to create a report that will show specific report dates (8/6, 8/7, 8/8, etc...) for each of those dates, I need to know how many customers have an "A" status where the status date is before the specific report date.
December 22, 2010 at 1:47 pm
The insert statements above should be:
INSERT INTO dbo.CustomerInfo (ID, CustomerStatus, StatusDate) VALUES (1, 'A', '20100801')
INSERT INTO dbo.CustomerInfo (ID, CustomerStatus, StatusDate) VALUES (1, 'X', '20100809')
INSERT INTO dbo.CustomerInfo (ID, CustomerStatus, StatusDate) VALUES (1, 'A', '20100811')
INSERT INTO dbo.CustomerInfo (ID, CustomerStatus, StatusDate) VALUES (2, 'A', '20100716')
INSERT INTO dbo.CustomerInfo (ID, CustomerStatus, StatusDate) VALUES (2, 'X', '20100807')
INSERT INTO dbo.CustomerInfo (ID, CustomerStatus, StatusDate) VALUES (3, 'A', '20100810')
INSERT INTO dbo.CustomerInfo (ID, CustomerStatus, StatusDate) VALUES (3, 'X', '20100812')
December 22, 2010 at 2:04 pm
Your insert are bit of a problem because you are inserting the field ID which you have marked as the primary key and there are duplicates.
If I understand the requirements correctly you want the count of all customers that has a record with a status where the newest entry is > the reportdate (even if the status for that date is not 'A')???? The more I look at the data and your desired output I am more and more confused. Why is customer 1 counted on 8/12? The most recent entry is from the 11th.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 22, 2010 at 2:12 pm
Sorry about the primary key issues on the CustomerInfo table - for my scaled down data example, the primary key doesn't need to be on this.
I will try to explain my desired results in some more detail:
On 8/6/2010 - Customer 1 and 2 should be considered "Active" - both have a status date that is before 8/6/2010.
On 8/7/2010 - only Customer 1 should be considered "Active" - customer 2 got an inactivated status on this day, so we don't want to count him, but will still count Customer 1 because he still has an active status.
On 8/8/2010 - only Customer 1 still has an active status
On 8/9/2010 - none of the customers have an active status (customer 1 switched to inactive on this day)
On 8/10/2010 - only Customer 3 is Active
On 8/11/2010 - Customer 3 and Customer 1 both have an active status
On 8/12/2010 - Customer 1 is the only person left with an active statu - customer 3 is now inactive
Hope this is making a little more sense...???
December 22, 2010 at 4:01 pm
Here's my approach:
; WITH cte AS
(
SELECT dt,id,MAX(statusdate) AS max_statusdate
FROM ReportDate rd
LEFT OUTER JOIN CustomerInfo ci ON ci.statusdate<=rd.dt
GROUP BY dt,id
)
SELECT
dt AS [Report DATE] ,
SUM(CASE WHEN ci.id IS NOT NULL THEN 1 ELSE 0 END) AS [Customer COUNT (have an A status)]
FROM cte
LEFT OUTER JOIN CustomerInfo ci
ON cte.id=ci.id
AND cte.max_statusdate=ci.statusdate
AND (ci.CustomerStatus='A' OR ci.CustomerStatus IS NULL)
GROUP BY dt ORDER BY dt
Unfortunately, it'll use a triangular join to get all the required. But I simply can't figure a way to do it more efficient (except using a calendar table and the quirky update method...)
December 29, 2010 at 6:30 am
Thanks - this is what I needed....great work!!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply