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!
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
Der Beginn aller Wissenschaften ist das Erstaunen, dass die Dinge sind, wie sie sind.-Aristoteles
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
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
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
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
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