Finding the total count of ACTIVE Customers and their remaining TOTAL POINTS for each distinct groups of CATEGORY.

Question

Finding the total count of ACTIVE Customers and their remaining TOTAL POINTS for each distinct groups of CATEGORY.

sql1411

Ten Centuries

Points: 1390
More actions
May 20, 2013 at 9:53 am

#272830

Hi
I’ve a CustomerActivity table that records all the activities of the consumers.
I've create a simplyfied version of the table with the activities of just two members as below:
USE [Sample]
GO
ANSI_PADDING ON
GO
-- Create a ConsumerActivityTable
CREATE TABLE [dbo].[ConsumerActivity](
[ConsumerID] [varchar](10) NOT NULL,
[ActivityDate] [datetime] NULL,
[Status] [varchar](10) NULL,
[Category] [varchar](10) NULL,
[PointsEarned] [int] NULL,
[PointsUsed] [int] NULL,
[Description] [varchar](100) NULL
) ON [PRIMARY]
GO
-- Insert some sample data into this activity table
GO
INSERT INTO dbo.ConsumerActivity (ConsumerID, ActivityDate, [Status], Category, PointsEarned, PointsUsed,[Description])
SELECT '101', '2013-05-10 00:00:00.000','ACTIVE','PREMIUM',NULL,'500','Upgrage to PREMIUM class'
UNION ALL
SELECT '101', '2013-05-07 00:00:00.000','ACTIVE','GENERAL',NULL,'-200','Points used'
UNION ALL
SELECT '101', '2013-02-10 00:00:00.000','ACTIVE','GENERAL','200',NULL,'General purchase'
UNION ALL
SELECT '101', '2012-10-12 00:00:00.000','ACTIVE','GENERAL','200','100','First time purchase with bonus points'
UNION ALL
SELECT '102', '2013-05-10 00:00:00.000','ACTIVE','GENERAL','600',NULL,'Account Reactivated'
UNION ALL
SELECT '102','2013-01-10 00:00:00.000','INACTIVE','GENERAL','600','-600','Account DEACTIVATED and the remaining points are adjusted'
UNION ALL
SELECT '102','2012-10-20 00:00:00.000','ACTIVE','GENERAL','300',NULL,'General Purchase'
UNION ALL
SELECT '102','2012-11-20 00:00:00.000','ACTIVE','GENERAL',NULL,'-200','Points used'
UNION ALL
SELECT '102','2012-09-20 00:00:00.000','ACTIVE','GENERAL','400','100','New purchase with bonus points'
GO
-- SELECT * from this table to see how the data looks currently
GO
SELECT * FROM dbo.ConsumerActivity
ORDER BY ConsumerID, ActivityDate DESC
GO
Now, I need to write a query that gives me the “TOTAL COUNT” of ACTIVE customers and their “TOTAL Remaining Points (Rewards + Bonus points)”, for each DISTINCT group of “Category” as of @ReportDate(parameter). All these should be based on the latest STATUS and latest CATEGORY on a specified @ReportDate parameter.
Here are the two scenarios of how I want to see the result from the given sample:
•If @ReportDate = ‘05/15/2013’
Category TotalActiveConsumerCountTotalRemainingPoints
Premium 1 800
General 1 600
Here, for all the consumers whose accounts has been REACTIVATED, we only use the points that they’ve gathered after they becomes ACTIVE again. The latest CATEGORY of consumerID 101 on the specified date is PREMIMUM.
•If @ReportDate = ‘04/15/2013’
ConsumerTypeTotalActiveConsumerCountTotalRemainingPoints
Premium 0 0
General 1 500
Here, the latest Category of consumerID 101 on the specified date is GENERAL. The latest status of ConsumerID 102 on the specified date is INACTIVE and thus we don’t need to count this consumer at all.
Thanks for your help in advance
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Excellent job posting ddl and sample data. I can't figure out the logic behind what you say you want as desired output. What is the significance of the date parameter? Should it be looking at rows before that date, after, on that date? I tried a dozen or so ways to come up with the output you stated you wanted and it just doesn't make sense to me. Can you try to explain more clearly the business rules behind how you get the output?

_______________________________________________________________

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/

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 2

Hi

where do these two columns come from

"TOTAL Remaining Points (Rewards + Bonus points)"

you have pointsearned and pointsused

??

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

sql1411 Ten Centuries Points: 1390 More actions · Answer 3

Hi Sean,

Thanks for the response

Here are some business rules for the desired output,

•The significance of @Reportdate parameter something that I want to use it in the report as a report date. So, business users will be selecting the date as they wish. It can be today, yesterday, last week, last month and whenever they want. We should be looking at the data on that day and before that day, whichever is latest it identify the STATUS and CATEGORY. However, we still have to consider all previous rows of activities for the points.

•Users should be able to know how many ACTIVE consumers are there in PREMIUM and GENERAL category on that specified date. And for each Active customers, how much points do they have.

•There are some consumers whose accounts are deactivated and reactivated again after some point of time. For these consumers, we’ll only need to use the points from the day their account is reactivated.

The desired output in terms of columns would look something like this:

SELECT DISTINCT Category, COUNT(ConsumerID) as TotalConsumerCounts, SUM(PointsEarned+PointsUsed) as RewardsPoints

FROM dbo.ConsumerActivity

WHERE ActivityDate = @ReportDate

GROUP BY Category;

This query is just the simplest form of how the desired output should be. I need to apply all the above business logic in it.

Let me know if I need to clarify further.

Thanks.

sql1411 Ten Centuries Points: 1390 More actions · Answer 4

Sorry for the mistake. It should be Total points = (PointsEarned+PointsUsed)

Positive values in the PointsUsed indicates the bonus points. It's weired but that's how the data is right now.

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

sql1411 (5/20/2013)
Hi Sean,
Thanks for the response
Here are some business rules for the desired output,
•The significance of @Reportdate parameter something that I want to use it in the report as a report date. So, business users will be selecting the date as they wish. It can be today, yesterday, last week, last month and whenever they want. We should be looking at the data on that day and before that day, whichever is latest it identify the STATUS and CATEGORY. However, we still have to consider all previous rows of activities for the points.

This is easy enough. Just add a where clause.

ActivityDate <= '5/15/2013'

•There are some consumers whose accounts are deactivated and reactivated again after some point of time. For these consumers, we’ll only need to use the points from the day their account is reactivated.

This is a nightmare with the way your data is. When an account is deactivated you should have some adjusting entries to zero out the balance. For account 102 the sum of PointsEarned and PointsUsed make no sense. Why do they earn points for deactivating your account? And why do you spend all of them in the same row? Seems to me the points earned for that should -700 and the PointsUsed should be 800? Why in the world do you have both positive and negative amounts in the pointsUsed column?

The desired output in terms of columns would look something like this:
SELECT DISTINCT Category, COUNT(ConsumerID) as TotalConsumerCounts, SUM(PointsEarned+PointsUsed) as RewardsPoints
FROM dbo.ConsumerActivity
WHERE ActivityDate = @ReportDate
GROUP BY Category;

You should probably add Status = 'Active' to the above? Not sure why you have a distinct in that query, all the other columns are aggregates so it will not return any duplicates. What you might want is to COUNT(Distinct ConsumerID) so it will give you the number of ConsumerID values found instead of the number of rows. Aside from the nightmarish points situation this is pretty straight forward.

I have to head to a meeting for a couple hours or so. I think you could probably deal with points situation with a cte and then use the cte as the basis for your query. Maybe somebody else will come along and put some cycles into this while I am gone. If not, I will try to help you either later today or tomorrow.

_______________________________________________________________

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/

sql1411 Ten Centuries Points: 1390 More actions · Answer 6

[/quote]

This is a nightmare with the way your data is. When an account is deactivated you should have some adjusting entries to zero out the balance. For account 102 the sum of PointsEarned and PointsUsed make no sense. Why do they earn points for deactivating your account? And why do you spend all of them in the same row? Seems to me the points earned for that should -700 and the PointsUsed should be 800? Why in the world do you have both positive and negative amounts in the pointsUsed column?

I understand the frustration of the data being very messy. This is one of the database from our so called "most economic vendor". Anyway, lets change the name of PointsEarned and PointsUsed as "Points1" and "Points2" so that we can avoid any confusion. This is how I tried to understand this for myself.

Now for CustomerID 102, that day this member is DEACTIVATED, it has the remaining TotalPoints of 600 (400+100+300-200) and they seems to Zero out this with the same negative value.

Thanks

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 7

Hi..just to be sure and possibly save time later on...does your sample data provide all the possible situations you have in the real data.

if it doesn't...then please provide all the scenarios.

thanks

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

sql1411 Ten Centuries Points: 1390 More actions · Answer 8

Everything that I understood in last two days, I tried to put it in these scenarios. So yes I believe these scenarios covered everything so far.

sql1411 Ten Centuries Points: 1390 More actions · Answer 9

I believe this query gives me the count of ACTIVE customers for each CATEGORY.

SELECT Category as ConsumerCategory, COUNT(DISTINCT ConsumerID)as TotalConsumerCount

FROM (

SELECT *, ROW_NUMBER() OVER (PARTITION BY ConsumerID ORDER BY ActivityDate DESC) AS RowNum

FROM dbo.ConsumerActivity

WHERE ActivityDate <= @ReportDate

) as Sub

WHERE Sub.RowNum = 1

AND Status = 'ACTIVE'

GROUP BY Category;

However, I'm still not sure how I can calculate the third column (TotalRemaimingPoints) in this query based on the business logic that I've explained above.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 10

so....heres some dev code to get you started...have left it all broken out into tables so that you can see what is going on....

not sure how performant it will be..maybe you can play with this to improve.

hope it gives you some ideas...and maybe someone else will pick up and improve

good luck

use [tempdb]

go

/* delete "temp" tables...these are permanent for debug use */

IF OBJECT_ID('tempdb..tempstatus', 'U') IS NOT NULL DROP TABLE tempdb..tempstatus ;

IF OBJECT_ID('tempdb..tempIAdate', 'U') IS NOT NULL DROP TABLE tempdb..tempIAdate ;

IF OBJECT_ID('tempdb..tempAdate', 'U') IS NOT NULL DROP TABLE tempdb..tempAdate ;

IF OBJECT_ID('tempdb..tempresults', 'U') IS NOT NULL DROP TABLE tempdb..tempresults ;

IF OBJECT_ID('tempdb..tempCat', 'U') IS NOT NULL DROP TABLE tempdb..tempCat ;

/* set date variable for report*/

declare @Reportdate as datetime

set @Reportdate = '2013-05-15'

/* get latest status at report date */

select consumerId,category

into tempstatus

from

(

SELECT consumerid,category, ROW_NUMBER() OVER (PARTITION BY ConsumerID ORDER BY ActivityDate DESC) AS rn

FROM dbo.ConsumerActivity

WHERE ActivityDate <= @ReportDate

) as sub

where rn=1

/* select distinct categories */

SELECT DISTINCT Category

into tempCat

FROM ConsumerActivity

/* find last date account was inactive before report date */

SELECT ConsumerID, MAX(ActivityDate) AS maxIAdate

into tempIAdate

FROM ConsumerActivity

WHERE (Status = 'INACTIVE')

GROUP BY ConsumerID

HAVING (MAX(ActivityDate) <= @Reportdate)

/* select all consumerId not in tempIAdate above*/

SELECT DISTINCT CA.ConsumerID

INTO tempADate

FROM

ConsumerActivity AS CA INNER JOIN tempIAdate AS CTE

ON CA.ConsumerID <> CTE.ConsumerID;

/* put results by consumer into table for detailed analysis if required */

select ConsumerID, SUM(ISNULL(PointsEarned,0) + ISNULL (PointsUsed,0)) as results

into tempresults

from

(

SELECT CA.ConsumerID

, CA.PointsEarned

, CA.PointsUsed

FROM

ConsumerActivity AS CA INNER JOIN tempIAdate AS CTE

ON CA.ConsumerID = CTE.ConsumerID

AND CA.ActivityDate > CTE.maxIAdate

WHERE (CA.ActivityDate <= @Reportdate)

UNION ALL

SELECT CA.ConsumerID

, CA.PointsEarned

, CA.PointsUsed

FROM

ConsumerActivity AS CA INNER JOIN tempAdate AS CTE

ON CA.ConsumerID = CTE.ConsumerID

WHERE (CA.ActivityDate <= @Reportdate)

) x

group by ConsumerID

/* this what you want?? */

SELECT tempCat.Category, COUNT(tempresults.ConsumerID) AS cnt, SUM(tempresults.results) AS points

FROM tempresults INNER JOIN

tempstatus ON tempresults.ConsumerID = tempstatus.consumerId RIGHT OUTER JOIN

tempCat ON tempstatus.category = tempCat.Category

GROUP BY tempCat.Category

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

sql1411 Ten Centuries Points: 1390 More actions · Answer 11

Hi J,

Thanks for posting this. It seems to be working only for certain recent dates. For instance, If I choose the @ReportDate = '2013-04-08 00:00:00.000' , it returns the correct counts and points.

Now, if I choose @ReportDate = '2013-01-08 00:00:00.000' , it returns 0 counts and NULL points for both the CATEGORY. For this date, I should be getting

PREMIUM = 0

GENERAL = 2

TotalPoints = 900 (200+100-200+300+400+100)

Thanks again

mister.magoo SSC-Forever Points: 47174 More actions · Answer 12

Try this

DECLARE @ReportDate DATE;

SET @ReportDate='8 jan 2013';

WITH data AS

(

SELECT * ,ROW_NUMBER() OVER(PARTITION BY ConsumerID ORDER BY ActivityDate DESC) AS rn

FROM dbo.ConsumerActivity

WHERE ActivityDate <= @ReportDate

), cons AS

(

SELECT ConsumerId, MAX(CASE WHEN rn=1 THEN Status ELSE '' END) AS Status, MAX(CASE WHEN rn=1 THEN Category ELSE '' END) AS Category,SUM(COALESCE(CASE Status WHEN 'ACTIVE' THEN PointsEarned ELSE NULL END,0)+COALESCE(PointsUsed,0)) AS TPR

FROM data

GROUP BY ConsumerID

)

SELECT cats.Category, SUM(CASE cons.status WHEN 'ACTIVE' THEN 1 ELSE 0 END) AS TACC, COALESCE(SUM(TPR),0) AS TPR

FROM (SELECT 'GENERAL' UNION ALL SELECT 'PREMIUM') cats(category)

LEFT OUTER JOIN cons

ON cats.category = cons.Category

GROUP BY cats.Category;

The first CTE (data) just adds a row_number so we can tell which is the most recent status/category etc for a ConsumerID.

The second CTE (cons) groups by ConsumerId and totals the ACTIVE points, using the row_number to isolate the status and category from the latest row for each ConsumerId.

The final select counts the ACTIVE ConsumerID(s) and uses a static set of Categories to ensure you get a result for each category every time.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

sql1411 Ten Centuries Points: 1390 More actions · Answer 13

Looks like this is working so far :-). Thanks Magoo and thank you all for spending your valuable time in helping me to figure this out. You guys are great.