Get the Latest records fro each month

Question

Post reply

Get the Latest records fro each month

patr

SSC Eights!

Points: 892
More actions
September 8, 2008 at 2:51 am

#123354

HI
I need to get the latest purchase records of each customer in each month.the follwing scenario is furnished below.
TABLE
------------------------------------------------
DAY WEEK MONTH CUSTOMER PURCHASECOUNT
------------------------------------------------
1 1 1 ABC 10
7 2 1 DEF 12
3 4 1 ABC 13
7 4 1 ABC 14
----------------------------------------------
I want only to extract the latest data for each CUSTOMER as
DAY WEEK MONTH CUSTOMER PURCHASECOUNT
7 2 1 DEF 12
7 4 1 ABC 14
THANKS IN ADVANCE

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

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Here's my first pass in T-SQL:

[font="Courier New"]DECLARE @table TABLE(DAY INT, Week INT, MONTH INT, Customer CHAR(3), PurchaseCount INT)

INSERT INTO @table

SELECT

1, 1, 1, 'ABC', 10

UNION ALL

SELECT

7, 2, 1, 'DEF', 12

UNION ALL

SELECT

3, 4, 1, 'ABC', 13

UNION ALL

SELECT

7, 4, 1, 'ABC', 14

;WITH cteRows AS

(

SELECT

Row_Number() OVER(Partition BY Customer ORDER BY MONTH DESC, Week DESC, DAY DESC) AS row_id,

DAY,

Week,

MONTH,

Customer,

PurchaseCount

FROM

@table

)

SELECT

*

FROM

cteRows

WHERE

row_id = 1[/font]

Also note how I posted the data you supplied. It makes it easier for others to try some solutions and verify that they produce the results you want.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

patr SSC Eights! Points: 892 More actions · Answer 2

HI JACK

first of all i would like to say thanks for giving me a gud solution.but I need the output for each customer for each month (i need to get the latest updated records for each month)

suppose there are 10 customers ,if i query the table it should fetch 12 records(for each month january,feb etc.,) for each customers

so therefore it should give 10*12=120 records at the end of december.

the present query u have given retrieves the output as latest record for each customer for the last month.

thanks in advance

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

You should be able to modify the code provided to return the results you need. And I actually answered your original post exactly as asked. Here is what you posted as the desired result based on the test data you provided:

DAY WEEK MONTH CUSTOMER PURCHASECOUNT
7 2 1 DEF 12
7 4 1 ABC 14

and this is exactly the outcome of my solution.

If you add MONTH to the PARTITION BY I think the query will work as you now desire, but the test data you provided only included 1 month. I leave that to you to determine.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

hi jack
I am really thankful to u for sending ur valuable time for my query....... [Smile]
i need the output like this i have tried by adding one more partition by fro the month.but i could nt get THE SOLUTION.
here is the exact output i need
FOR EG:
CUSTOMER MONTH WEEK DAYNO PURCHASE COUNT
ABC 1 4 7 45
ABC 2 3 6 25
DEF 1 2 1 19
DEF 12 3 4 20
WHICH SHOULD SHOW LATEST RECORD FOR EACH CUSTOMER FOR EACH MONTH

Patr,

I am putting your PM in the thread so that others can potentially benefit from the full thread.

I don't mind helping out, but you need to provide full information. In your PM you only provide your desired outcome, not the full set of data, so how can anyone provide a solution when we don't know what the source data is?

Please read the articles linked in my signature line and post a full set of test data and your desired outcome and either myself or someone else will likely provide a working solution.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Lloyd Bond-383166 Old Hand Points: 343 More actions · Answer 5

Am I missing something or is this nothing to do with SSIS?