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

  • 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

  • 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/

  • 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

  • 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.

  • 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.

  • 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/

  • [/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

  • 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

  • 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.

  • 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.

  • 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

  • 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

  • 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);

  • 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]

  • 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.

  • Viewing 14 posts - 1 through 13 (of 13 total)

    You must be logged in to reply to this topic. Login to reply