Solved

SQL Query Help partition/pivot data into rows help?

Posted on 2015-01-22
8
80 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 76

Expert Comment

by:arnold
Comment Utility
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
Comment Utility
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
Comment Utility
there is no summing I need the Max(Id) record if that group
0
 

Author Comment

by:sbornstein2
Comment Utility
Need the Max Id of each of those 3 datasources then need to pivot into those 7 columns.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:sbornstein2
Comment Utility
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
Comment Utility
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
Comment Utility
I will right now Lowfat thank you so much trying shortly.
0
 

Author Closing Comment

by:sbornstein2
Comment Utility
Absolutely perfect.   Thanks LowFat huge huge help for me today thanks.
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

771 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

11 Experts available now in Live!

Get 1:1 Help Now