Monday, October 7, 2013

[SQL Server] How to collapse rows but take a specific value?

[SQL Server] How to collapse rows but take a specific value?

How to collapse rows but take a specific value?

Posted: 07 Oct 2013 02:57 AM PDT

Hi,I'm struggling as to how to collapse rows in a view based on different values in a column, but take one specific value. Here's the sample data with four columns:AccountNumber ------MaturityDate ------ResetFreq ------ Amount123 ------ 20140331 ------ 1D ------- 100.00123 ------ 20140331 ------ 1M ------- 2400.00123 ------ 20140331 ------ '' ------- 8700.00123 ------ 20140331 ------ 1Y ------- -99.00123 ------ 20140331 ------ 1M ------- 299.00I want to collapse these four rows into one row:AccountNumber ------MaturityDate ------ResetFreq ------ Sum(Amount)123 ------ 20140331 ------ 1Y ------- 11400.00For ResetFrequency, I need to take the highest value among the distinct values listed for that account.The hierarchy is: '' (blank) < 1D (1 day) < 1M (1 month) < 3M (3 months) < 1Y (1 year) < 3Y < 5Y < 10YSo, in this case, the distinct values are: '', 1D, 1M, 1Y -- so I take 1Y as it is the highest.Any help will be appreciated.Amit

Duplicate Data Reports Issue

Posted: 07 Oct 2013 05:42 AM PDT

We have a database named NorthStar (NS) that pushes data into a CRM Application (Salesforce) through an SSIS via a sql agent job. Lately, NS has been pushing duplicate data and Salesforce users are complaining of receiving duplicate data/records. Average amount received daily should be 156. Today 270 records were received. How do I solve this problem? Here is the path to the package. --- J:\Projects\NS to SF\NS Contacts Update with Org\Weekly NS Push\Package.dtsxThanks!

Site for good SQL learnings

Posted: 06 Oct 2013 07:35 PM PDT

Hi to all give me a site that has a good content about SQLthanks:-)
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog