get the latest records for each group

Question

Post reply

get the latest records for each group

sql1411

Ten Centuries

Points: 1390
More actions
February 7, 2013 at 4:50 pm

#269911

Hi,

I'm trying to create a report from the given database. I just need to see the latest records of all the members whose latest status is "ACTIVE" and I need to show their first ActivityDate in one single row.

Below are my sample queries and scenarios.

USE [Sample]

GO

-- Create Table

CREATE TABLE [dbo].[Consumer](

[ConsumerID] [varchar](10) NOT NULL,

[OrderCode] [varchar](100) NOT NULL,

[ConsumerType] [varchar](20) NULL,

[ConsumerStatus] [varchar](10) NULL,

[ActivityDate] [datetime] NULL,

[DutyCode_1] [varchar](20)NULL,

[DutyCode_2] [varchar](20)NULL

) ON [PRIMARY]

GO

-- Insert multiple records into the Consumer table

GO

INSERT INTO dbo.Consumer (ConsumerID, OrderCode, ConsumerType, ConsumerStatus, ActivityDate, DutyCode_1, DutyCode_2)

SELECT 'FX1' ,'abc',NULL,'ACTIVE','2012-12-30 00:00:00.000','Y12','67cuos'

UNION ALL

SELECT 'FX1' ,'abc',NULL,'ACTIVE','2012-12-25 00:00:00.000','Y18','32K'

UNION ALL

SELECT 'FX1' ,'abc',NULL,'ACTIVE','2011-09-10 00:00:00.000','0','0'

UNION ALL

SELECT 'FX1' ,'abc',NULL,'ACTIVE','2010-12-25 00:00:00.000','0','32K'

UNION ALL

SELECT 'AX3' ,'xyz',NULL,'INACTIVE','2012-12-29 00:00:00.000','0','0'

UNION ALL

SELECT 'AX3' ,'xyz',NULL,'ACTIVE','2011-09-10 00:00:00.000','0','12p'

UNION ALL

SELECT 'AX3' ,'xyz',NULL,'ACTIVE','2009-12-25 00:00:00.000','0','32erts'

GO

SELECT * FROM dbo.Consumer

From the given sample, I need the just latest record (one row) for each GROUP(CustomerID & OrderCode), based on ActivityDate. And, I need only the Customers whose latest 'ConsumerStatus' = 'ACTIVE'.

However, I also need a new column "FirstEnrollmentDate" which is the first ActivityDate for each Customer. From the above sample, I should just see one latest record for customer "FX1" and I need a new "FirstActivityDate" column as well.

Let me know if I need to clarify further.

Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 1

Lynn Pettis

SSC Guru

Points: 442447

February 7, 2013 at 4:55 pm

#1585695

Based on the sample data provided, what would be the expected results.

Please show, not just describe.

Dwain Camps SSC Guru Points: 86913 More actions · Answer 2

I agree with Lynn that seeing the expected results would be far more helpful than a description, but nonetheless I'll give it a shot:



;WITH AllCustomers AS (

    SELECT *

        ,n=ROW_NUMBER() OVER (PARTITION BY ConsumerID ORDER BY ActivityDate DESC)

        ,FirstActivity=MIN(ActivityDate) OVER (PARTITION BY ConsumerID, [OrderCode])

        ,LatestActivity=MAX(ActivityDate) OVER (PARTITION BY ConsumerID, [OrderCode])

    FROM dbo.Consumer)

SELECT ConsumerID, OrderCode, FirstActivity, LastActivity

FROM AllCustomers

WHERE n = 1 AND ConsumerStatus = 'ACTIVE'

ORDER BY ConsumerID, ActivityDate

Please let us know if this is close to your need.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

sql1411 Ten Centuries Points: 1390 More actions · Answer 3

Here is what I should see. Sorry, I had to put this vertically

consumerID - FX1

Ordercode - abc

Consumertype - NULL

ConsumerStatus - ACTIVE

LatestActivityDate - 2012-12-30 00:00:00.000

DutyCode_1 - Y12

DutyCode_2 - 67cuos

FirstActivityDate - 2010-12-25 00:00:00.000

FirstActivityDate is should be a new column that I need

Dwain Camps SSC Guru Points: 86913 More actions · Answer 4

You just need to add a few columns to the query I provided:



;WITH AllCustomers AS (

    SELECT *

        ,n=ROW_NUMBER() OVER (PARTITION BY ConsumerID ORDER BY ActivityDate DESC)

        ,FirstActivity=MIN(ActivityDate) OVER (PARTITION BY ConsumerID, [OrderCode])

        ,LatestActivity=MAX(ActivityDate) OVER (PARTITION BY ConsumerID, [OrderCode])

    FROM dbo.Consumer)

SELECT ConsumerID, OrderCode, ConsumerType, LatestActivity

    ,ConsumerStatus, DutyCode_1, DutyCode_2

    ,FirstActivity

FROM AllCustomers

WHERE n = 1 AND ConsumerStatus = 'ACTIVE'

ORDER BY ConsumerID, ActivityDate

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

sql1411 Ten Centuries Points: 1390 More actions · Answer 5

sql1411

Ten Centuries

Points: 1390

February 8, 2013 at 9:38 am

#1586042

Thanks. This is just what I needed 🙂