All Forums Analytics
Chuken22 5 posts Joined 07/12
18 Jul 2012
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

 

Chuken22 5 posts Joined 07/12
18 Jul 2012

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!

 

 

ulrich 816 posts Joined 09/09
18 Jul 2012
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

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Chuken22 5 posts Joined 07/12
18 Jul 2012

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

dnoeth 4628 posts Joined 11/04
18 Jul 2012

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

Dieter

Chuken22 5 posts Joined 07/12
19 Jul 2012

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

 

dnoeth 4628 posts Joined 11/04
19 Jul 2012

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

Dieter

You must sign in to leave a comment.