Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 95
  • Last Modified:

SQL Query Help partition/pivot data into rows help?

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
sbornstein2
Asked:
sbornstein2
  • 5
  • 2
1 Solution
 
arnoldCommented:
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
 
LowfatspreadCommented:
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
 
sbornstein2Author Commented:
there is no summing I need the Max(Id) record if that group
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
sbornstein2Author Commented:
Need the Max Id of each of those 3 datasources then need to pivot into those 7 columns.
0
 
sbornstein2Author Commented:
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
 
LowfatspreadCommented:
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
 
sbornstein2Author Commented:
I will right now Lowfat thank you so much trying shortly.
0
 
sbornstein2Author Commented:
Absolutely perfect.   Thanks LowFat huge huge help for me today thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now