Solved

SQL Query Help partition/pivot data into rows help?

Posted on 2015-01-22
8
81 Views
Last Modified: 2015-01-23
Hello all,

Okay this is what I am trying to accomplish and this may be confusing so hopefully my same data set here will clarify ok.

I have a table with the following data:

DataSourceID = 6 is considered Cust Group 1
DataSourceID = 8 is considered Cust Group 2
DataSourceID = NULL is considered  Cust Override

ID          CustDate,       CustID,     DataSourceID,     Currency,      Amount
15         1/22/2015      1                6                             USD               55.00
14         1/22/2015      1                6                             USD               75.00
13         1/19/2015      1                6                             AED               69.00
12         1/19/2015      1                8                             USD               99.99
11         1/18/2015      1                6                             USD              111.11
10         1/18/2015      1                6                             USD              222.22
9           1/18/2015      1                NULL                      AUD             888.55
8           1/18/2015      1                NULL                      AUD             547.22
7           1/17/2015      1                8                             BBD              593.55
6           1/17/2015      1                6                             USD              444.44
5           1/17/2015      1                NULL                     USD               333.33


What I need to return is 7 columns in my result set that would be as follows I will pass it a CustID = 1:

CustDate    CustGrp1Currency  CustGrp1Amount   CustGrp2Currency CustGrp2Amount  CustOVCurrency   CustOVAmount

I need the data to be grouped by CustDate,  DataSourceID,   Max(ID) to get all three sets of data for each date.   So my resultset would be in this sample set:
   
CustDate    CustGrp1Currency  CustGrp1Amount   CustGrp2Currency CustGrp2Amount  CustOVCurrency   CustOVAmount

1/22/2015   USD                          55.00                         NULL                         NULL                       NULL                       NULL
1/19/2015   AED                          69.00                         USD                           99.99                       NULL                       NULL
1/18/2015   USD                         111.11                        NULL                         NULL                       AUD                        888.55
1/17/2015   USD                         444.44                        BBD                          593.55                     USD                         333.33

Hope this makes sense but I have to get that data together from this one table based on a CustID I pass at the proc.   Thanks all for any help
0
Comment
Question by:sbornstein2
  • 5
  • 2
8 Comments
 
LVL 77

Expert Comment

by:arnold
ID: 40565936
I think your example is incorrect, 1/22/2015 has two payments in USD 55 and 75. you need to sum(amount)

IS the following acceptable
CustDate,custgrp1currency,custgrp1amount,custgrp2currency,custgrp2amount,custgrp3currency,custgrp3amount,
1/22/2015,usd,130,null,null,null,null


What is your attempt at this query?
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 40565945
try this....

i  think you basically want to pivot the data for a customer taking only the latest row per day for that customer  and datasourceid

;with cte as
 (select custid,custdate,
     case datasourceid when 6 then currency end as custgrp1currency
    ,case datasourceid when 6 then amount end asas custgrp1amount
    ,case datasourceid when 8 then currency end as custgrp2currency
    ,case datasourceid when 8 then amount end as custgrp2amount
    ,case when datasourceid is null then currency end as custovrcurrency
    ,case when datasourceod is null then amount end as custovramount
    ,row_number() over (partition by custid,custdate,datasourceid) order by id desc  as rn
from yourtable
)
select custdate,max(custgrp1currency) as custgrp1currency
      ,sum(custgrp1amount) as custgrp1amount
      ,max(custgrp2currency) as custgrp2currency
      ,sum(custgrp2amount) as custgrp2amount
      ,max(custovrcurrency) as custovrcurrency
      ,sum(custovramount) as custovramount
  from cte
  where rn=1
    and custid = ????
  group by custdate
  order by custdate desc

Open in new window

0
 

Author Comment

by:sbornstein2
ID: 40566239
there is no summing I need the Max(Id) record if that group
0
 

Author Comment

by:sbornstein2
ID: 40566240
Need the Max Id of each of those 3 datasources then need to pivot into those 7 columns.
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:sbornstein2
ID: 40566244
based on the max id there really should never be more than one record if any for that group so summing is not needed
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 40566292
That's what the row number function does

The sum just combines the 3 rows into a single one for the pivot.

Have you tried it
0
 

Author Comment

by:sbornstein2
ID: 40566319
I will right now Lowfat thank you so much trying shortly.
0
 

Author Closing Comment

by:sbornstein2
ID: 40567263
Absolutely perfect.   Thanks LowFat huge huge help for me today thanks.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL find overlapping events (by time) 2 25
Error when loading the database 16 55
grouping logic 6 46
email about the whoisactive result 7 23
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now