February 7, 2013 at 4:50 pm
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
February 7, 2013 at 4:55 pm
Based on the sample data provided, what would be the expected results.
Please show, not just describe.
February 7, 2013 at 5:20 pm
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 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
February 7, 2013 at 5:22 pm
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
February 7, 2013 at 6:26 pm
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 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
February 8, 2013 at 9:38 am
Thanks. This is just what I needed 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply