Multiple Rows Into 1 Question

Analytics
Enthusiast

Multiple Rows Into 1 Question

Hi All,

I was wondering if i could get some expert advise on the best way to receive the following output.












POSTDATE TRANS_TYPE BILLING_CRCY_CDE CC_TYP_CDE TKT_NUM CC_WIP FARE_WIP TAX_WIP Sum(RADF_AMT_PD)
2012/06/28 CCD32D          ? CA 142052078142 620.38 475.76 144.62 475.76
2012/06/28 CCD32D          ? CA 142052237467 743.82 604 139.82 604

My Query now is..

select a.postdate, a.trans_type, a.billing_crcy_cde, a.cc_typ_cde, a.tkt_num, (case when c.dr_gl_acct_num = '123401' then c.gl_amt else 0 end) as CC_WIP, (case when c.cr_gl_acct_num = '237200' then c.gl_amt else 0 end) as FARE_WIP, (case when c.cr_gl_acct_num = '237300' then c.gl_amt else 0 end) as TAX_WIP, sum(b.radf_amt_pd) from pedw_tmp.tickets_ccb_tmp a, pedw_tmp.radf_stg b, pedw_tmp.gory_stg c where a.tkt_num = b.ff_tkt_num and a.tkt_num = c.tkt_num and b.ff_tkt_num = c.tkt_num and c.Acct_event = 'RSR' and a.trans_type = 'ccd32d' and a.cc_typ_cde in ('ca','vi') and a.postdate between '2012-06-27' and '2012-07-02' and b.postdate < '2012-07-02' and b.cpn_usg_cde = 'L' group by 5,1,2,3,4,5,6,7,8 order by 1,2,3,4,5,6,7,8;

And i receive the following with my query...












POSTDATE TRANS_TYPE BILLING_CRCY_CDE CC_TYP_CDE TKT_NUM CC_WIP FARE_WIP TAX_WIP Sum(RADF_AMT_PD)
2012/06/28 CCD32D          ? CA 142052078142 0 0 144.62 475.76
2012/06/28 CCD32D          ? CA 142052078142 0 475.76 0 475.76
2012/06/28 CCD32D          ? CA 142052078142 620.38 0 0 475.76
2012/06/28 CCD32D          ? CA 142052237467 0 0 139.82 604
2012/06/28 CCD32D          ? CA 142052237467 0 604 0 604
2012/06/28 CCD32D          ? CA 142052237467 743.82 0 0 604

I'm pretty new at this and haven't had any luck finding the correct/ best solution. And i'm think i've made my query alot more difficult than necessary.

Thanks for your help!!

Kenny

6 REPLIES
Enthusiast

Re: Multiple Rows Into 1 Question

Hi Again,

I've attempted a Qualify Row.

My Query:

select a.postdate, a.trans_type, a.billing_crcy_cde, a.cc_typ_cde, a.tkt_num, (case when c.dr_gl_acct_num = '123401' then c.gl_amt else 0 end) as CC_WIP, (case when c.cr_gl_acct_num = '237200' then c.gl_amt else 0 end) as FARE_WIP, (case when c.cr_gl_acct_num = '237300' then c.gl_amt else 0 end) as TAX_WIP, sum(b.radf_amt_pd) from pedw_tmp.tickets_ccb_tmp a, pedw_tmp.radf_stg b, pedw_tmp.gory_stg c where a.tkt_num = b.ff_tkt_num and a.tkt_num = c.tkt_num and b.ff_tkt_num = c.tkt_num and c.Acct_event = 'RSR' and a.trans_type = 'ccd32d' and a.cc_typ_cde in ('ca','vi') and a.postdate between '2012-06-27' and '2012-07-02' and b.postdate < '2012-07-02' and b.cpn_usg_cde = 'L'  QUALIFY ROW_NUMBER() OVER(PARTITION BY a.tkt_num ORDER BY a.postdate) = 1 group by 1,2,3,4,5,6,7,8 order by 1,2,3,4,5,6,7,8;

My Result:












POSTDATE TRANS_TYPE BILLING_CRCY_CDE CC_TYP_CDE TKT_NUM CC_WIP FARE_WIP TAX_WIP Sum(RADF_AMT_PD)
2012/06/28 CCD32D          ? CA 142052078142 0 0 144.62 475.76
2012/06/28 CCD32D          ? CA 142052237467 743.82 0 0 604

But it's only picking up 1 of the 3 vaules from CC_WIP, FARE_WIP, TAX_WIP.

I'm having difficulty researching as my company has Blocked 'Computer/Internet/Blogs/Personal Pages' sites. As such I would really appreciate anyone's help at your earliest convenience as this data is high priority for me. I'll even mail a box of chocolates to whomever assists me first.

Thanks!

Supporter

Re: Multiple Rows Into 1 Question

select a.postdate, a.trans_type, a.billing_crcy_cde, a.cc_typ_cde, a.tkt_num, 
max((case when c.dr_gl_acct_num = '123401' then c.gl_amt else null end)) as CC_WIP,
max( (case when c.cr_gl_acct_num = '237200' then c.gl_amt else null end)) as FARE_WIP, max((case when c.cr_gl_acct_num = '237300' then c.gl_amt else null end)) as TAX_WIP, sum(b.radf_amt_pd) from pedw_tmp.tickets_ccb_tmp a, pedw_tmp.radf_stg b, pedw_tmp.gory_stg c where a.tkt_num = b.ff_tkt_num and a.tkt_num = c.tkt_num and b.ff_tkt_num = c.tkt_num and c.Acct_event = 'RSR' and a.trans_type = 'ccd32d' and a.cc_typ_cde in ('ca','vi') and a.postdate between '2012-06-27' and '2012-07-02' and b.postdate < '2012-07-02' and b.cpn_usg_cde = 'L' group by 1,2,3,4,5 order by 1,2,3,4,5,6,7,8;

might work

Enthusiast

Re: Multiple Rows Into 1 Question

Hi & thanks Ulrich!

Just about there, now my Sum(Radf_amt_pd) column is overstated. (3x)

1247.28 should be 475.76

1812 should be 604

Do you have a recommended solution to fix this? And please send me your mailing address as i would like send you some chocolates as a huge thank you!

select a.postdate, a.trans_type, a.billing_crcy_cde, a.sale_crcy_cde, a.cc_typ_cde, a.tkt_num,  max((case when c.dr_gl_acct_num = '123401' then c.gl_amt else null end)) as CC_WIP, max( (case when c.cr_gl_acct_num = '237200' then c.gl_amt else null end)) as FARE_WIP, max((case when c.cr_gl_acct_num = '237300' then c.gl_amt else null end)) as TAX_WIP, sum(b.radf_amt_pd) from pedw_tmp.tickets_ccb_tmp a, pedw_tmp.radf_stg b, pedw_tmp.gory_stg c where a.tkt_num = b.ff_tkt_num and a.tkt_num = c.tkt_num and b.ff_tkt_num = c.tkt_num and c.Acct_event = 'RSR' and a.trans_type = 'ccd32d' and a.cc_typ_cde in ('ca','vi') and a.postdate between '2012-06-27' and '2012-07-02' and b.postdate < '2012-07-02' and b.cpn_usg_cde = 'L' group by 1,2,3,4,5 order by 1,2,3,4,5,6,7,8;












POSTDATE TRANS_TYPE BILLING_CRCY_CDE CC_TYP_CDE TKT_NUM CC_WIP FARE_WIP TAX_WIP Sum(RADF_AMT_PD)
2012/06/28 CCD32D          ? CA 142052078142 620.38 475.76 144.62 1427.28
2012/06/28 CCD32D          ? CA 142052237467 743.82 604 139.82

1812

Junior Contributor

Re: Multiple Rows Into 1 Question

Hi Kenny,

a solution depends on your data, according to your example Sum(RADF_AMT_PD) is the same for each of the three rows. If this is guaranteed it's simply "Sum(RADF_AMT_PD)/3" or if it's not always three rows "Sum(RADF_AMT_PD)/count(*)".

Otherwise you have to decide how to weight hse different values.

Btw, Sum(RADF_AMT_PD) and FARE_WIP return exactly the same value, is this accidentally?

Dieter

Enthusiast

Re: Multiple Rows Into 1 Question

No it's not always 3 rows. From the Radf it can be multiple rows as each tkt can range from 1 to say 4 coupons or so.









Current output is now          
TKT_NUM CC_WIP FARE_WIP TAX_WIP (Sum(RADF_AMT_PD)/Count(*))    
351966 $753.80 $607.48 $146.32 $303.74 $607.48 2 cpn used so it shud be 607.48

select a.postdate, a.trans_type, a.billing_crcy_cde, a.cc_typ_cde, a.tkt_num,  max((case when c.dr_gl_acct_num = '123401' then c.gl_amt else null end)) as CC_WIP, max( (case when c.cr_gl_acct_num = '237200' then c.gl_amt else null end)) as FARE_WIP, max((case when c.cr_gl_acct_num = '237300' then c.gl_amt else null end)) as TAX_WIP, sum(b.RADF_AMT_PD)/count(*) from pedw_tmp.tickets_ccb_tmp a, pedw_tmp.radf_stg b, pedw_tmp.gory_stg c where a.tkt_num = b.ff_tkt_num and a.tkt_num = c.tkt_num and b.ff_tkt_num = c.tkt_num and c.Acct_event = 'RSR' and a.trans_type = 'ccd32d' and a.cc_typ_cde in ('ca','vi') and a.postdate between '2012-06-27' and '2012-07-02' and b.postdate < '2012-07-02' and b.cpn_usg_cde = 'L' group by 1,2,3,4,5 order by 1,2,3,4,5,6,7,8;

Below is an example of the 2 coupons used on the radf that equal 'L'







RADF DETAIL      
FF_TKT_NUM FF_CPN_NUM F_LEG_FARE_AMT CPN_USG_CDE
142052351966 1 303.74 L
142052351966 2 303.74 L
    607.48
Junior Contributor

Re: Multiple Rows Into 1 Question

Hi Kenny,

this is a typical problem - you probably have two 1-to-many relationships and when you simply join the three tables you aggregate the same row multiple times.

Rearranging the aggregations into Derived Table will avoid this problem, hopefully this returns the correct result:

SELECT
a.postdate,
a.trans_type,
a.billing_crcy_cde,
a.sale_crcy_cde,
a.cc_typ_cde,
a.tkt_num,
c.CC_WIP,
c.FARE_WIP,
c.TAX_WIP,
b.radf_amt_pd
FROM pedw_tmp.tickets_ccb_tmp a
JOIN
(SELECT
b.ff_tkt_num,
SUM(b.radf_amt_pd) AS radf_amt_pd
FROM pedw_tmp.radf_stg
WHERE b.postdate < '2012-07-02'
AND b.cpn_usg_cde = 'L'
GROUP BY 1
) b
ON a.tkt_num = b.ff_tkt_num
JOIN
(SELECT
c.tkt_num,
MAX((CASE WHEN c.dr_gl_acct_num = '123401' THEN c.gl_amt ELSE NULL END)) AS CC_WIP,
MAX((CASE WHEN c.cr_gl_acct_num = '237200' THEN c.gl_amt ELSE NULL END)) AS FARE_WIP,
MAX((CASE WHEN c.cr_gl_acct_num = '237300' THEN c.gl_amt ELSE NULL END)) AS TAX_WIP
FROM pedw_tmp.gory_stg c
WHERE c.Acct_event = 'RSR'
GROUP BY 1
) c
ON a.tkt_num = c.tkt_num
WHERE a.trans_type = 'ccd32d'
AND a.cc_typ_cde IN ('ca','vi')
AND a.postdate BETWEEN '2012-06-27' AND '2012-07-02'
ORDER BY 1,2,3,4,5;

I formatted your source code to make it more readable :-)

Dieter