Case Statement

Question

Case Statement

sharonsql2013

SSCertifiable

Points: 5908
More actions
September 17, 2013 at 1:05 pm

#298788

I am looking to get a one Prodcode ,Prod Description Instead of multiple (if any) for a combination of prodId , prodmonth, prodyear.
So, it should return
ProdIdProdMonthProdYearProdVolumeProdCodeProdDesc
1 4 20131500 2 Corrected Report received.
1 5 2013 2000 1 Report received
1
so on...
for the following table.
I believe a CASE statement comparing ProdCode should be used , but don't know how?Kindly advise
ProdIdProdMonthProdYearProdVolumeProdCodeProdDesc
1 4 20130 0 Report Not received
1 4 20131000 1 Report received
1 4 20131500 2 Revised report received
1 5 20132000 1 Report Received
1 6 2013 2500 1 Report Received
1 7 20130 0 Report not received
1 7 20134000 1 Report received
1 8 201310000 1 Report received
2 4 2013200 1 Report received

Viewing 4 posts - 1 through 3 (of 3 total)

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

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

sharonsql2013 (9/17/2013)
I am looking to get a one Prodcode ,Prod Description Instead of multiple (if any) for a combination of prodId , prodmonth, prodyear.
So, it should return
ProdIdProdMonthProdYearProdVolumeProdCodeProdDesc
1 4 20131500 2 Corrected Report received.
1 5 2013 2000 1 Report received
1
so on...
for the following table.
I believe a CASE statement comparing ProdCode should be used , but don't know how?Kindly advise
ProdIdProdMonthProdYearProdVolumeProdCodeProdDesc
1 4 20130 0 Report Not received
1 4 20131000 1 Report received
1 4 20131500 2 Revised report received
1 5 20132000 1 Report Received
1 6 2013 2500 1 Report Received
1 7 20130 0 Report not received
1 7 20134000 1 Report received
1 8 201310000 1 Report received
2 4 2013200 1 Report received

The only we can offer from this to provide us enough information to help. In order to help we will need a few things:

1. Sample DDL in the form of CREATE TABLE statements

2. Sample data in the form of INSERT INTO statements

3. Expected results based on the sample data

Please take a few minutes and read the first article in my signature for best practices when posting questions.

_______________________________________________________________

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/

LinksUp SSCertifiable Points: 6526 More actions · Answer 2

As was requested by Mr. Lange, PROPERLY FORMED DLL, insert statements and expected output make it easier to provide a tested solution. So, the following is just air code that needs testing:

;with cte as

(

Select ProdId, ProdMonth, ProdYear, ProdVolume, ProdCode, ProdDesc,

Row_Number() over (Partition by ProdID, ProdMonth, ProdYear Order By ProdCode desc) RowNum

From Product_Table

)

Select *

From cte

Where cte.RowNum = 1

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

sharonsql2013 SSCertifiable Points: 5908 More actions · Answer 3

I was about to post everything. But what you sent actually helped.

Thanks so much