June 13, 2014 at 5:55 pm
Hi,
I have this task to carry Sum* fields into RDL and I need to make calculation as in below query, is it a way to code SumQ1_Fn in a nice way ? like in SumAll_Fn??
I can't put WHERE MM... in last FROM as I still need to carry all months into my report ??
Thanks much for your help and have a nice wkend
Best
Mario
;; WITH cte AS (
SELECT * FROM (
SELECT 200 CustID, 110 Amt, 1 MM, 'Jan' MMName UNION ALL SELECT 200 CustID, 120 Amt, 2 MM, 'Feb' MMName UNION ALL
SELECT 200 CustID, 130 Amt, 3 MM, 'Mar' MMName UNION ALL SELECT 200 CustID, 240 Amt, 4 MM, 'Apr' MMName UNION ALL
SELECT 200 CustID, 350 Amt, 5 MM, 'May' MMName UNION ALL SELECT 200 CustID, 460 Amt, 6 MM, 'Jun' MMName UNION ALL
---
SELECT 777 CustID, 260 Amt, 1 MM, 'Jan' MMName UNION ALL SELECT 777 CustID, 360 Amt, 2 MM, 'Feb' MMName UNION ALL
SELECT 777 CustID, 460 Amt, 3 MM, 'Mar' MMName UNION ALL SELECT 777 CustID, 500 Amt, 4 MM, 'Apr' )b ) --SELECT * FROM cte
SELECT a.custID,
(select SUM(Amt) FROM cte b WHERE MM BETWEEN 1 AND 3
AND b.CustID = a.CustID) SumQ1 ,
'Sum.. where MM????' SumQ1_Fn, --Help!!!!
(select SUM(Amt) FROM cte b WHERE 1=1
AND b.CustID = a.CustID) SumAll,
SUM(a.amt) OVER (PARTITION BY a.CustID) SumAll_Fn --OK
from CTE a
June 13, 2014 at 6:16 pm
Based on the sample data provided what should the output look like?
June 14, 2014 at 1:43 pm
Hi,
I'm trying to optimize that selection, probably there is better way to do this with any SQL function,
so value for column in question should be = SumQ1. I put my placeholde into SumQ1_Fn for my idea.
Tx
Mario
(select SUM(Amt) FROM cte b WHERE MM BETWEEN 1 AND 3
AND b.CustID = a.CustID) SumQ1 ,
'Sum over b.CustID where MM between 1 and 3' SumQ1_Fn, --Help!!!!
June 14, 2014 at 6:49 pm
I'll ask again.
If the query with sample data in your original post does not give you want you want, what is the expected results you are looking for based on the sample data?
June 14, 2014 at 11:54 pm
Guessing a little bit here but this is what I think:rolleyes: you are after
😎
; WITH cte AS (
SELECT * FROM (
SELECT 200 CustID, 110 Amt, 1 MM, 'Jan' MMName UNION ALL SELECT 200 CustID, 120 Amt, 2 MM, 'Feb' MMName UNION ALL
SELECT 200 CustID, 130 Amt, 3 MM, 'Mar' MMName UNION ALL SELECT 200 CustID, 240 Amt, 4 MM, 'Apr' MMName UNION ALL
SELECT 200 CustID, 350 Amt, 5 MM, 'May' MMName UNION ALL SELECT 200 CustID, 460 Amt, 6 MM, 'Jun' MMName UNION ALL
---
SELECT 777 CustID, 260 Amt, 1 MM, 'Jan' MMName UNION ALL SELECT 777 CustID, 360 Amt, 2 MM, 'Feb' MMName UNION ALL
SELECT 777 CustID, 460 Amt, 3 MM, 'Mar' MMName UNION ALL SELECT 777 CustID, 500 Amt, 4 MM, 'Apr' )b ) --SELECT * FROM cte
,REPORT_BASE AS
(
SELECT
CC.CustID
,ROW_NUMBER() OVER
(
PARTITION BY CC.CustID
ORDER BY (SELECT NULL)
) AS DD_RID
/* Q1 CUSTOMER */
,SUM(
CASE
WHEN CC.MM BETWEEN 1 AND 3 THEN CC.Amt
ELSE 0.0
END
) OVER
(
PARTITION BY CC.CustID
) AS CUST_Q1
/* Q1 ALL */
,SUM(
CASE
WHEN CC.MM BETWEEN 1 AND 3 THEN CC.Amt
ELSE 0.0
END
) OVER
(
PARTITION BY (SELECT NULL)
) AS ALL_Q1
/* Q2 CUSTOMER */
,SUM(
CASE
WHEN CC.MM BETWEEN 4 AND 6 THEN CC.Amt
ELSE 0.0
END
) OVER
(
PARTITION BY CC.CustID
) AS CUST_Q2
/* Q2 ALL */
,SUM(
CASE
WHEN CC.MM BETWEEN 4 AND 6 THEN CC.Amt
ELSE 0.0
END
) OVER
(
PARTITION BY (SELECT NULL)
) AS ALL_Q2
/* Q3 CUSTOMER */
,SUM(
CASE
WHEN CC.MM BETWEEN 7 AND 9 THEN CC.Amt
ELSE 0.0
END
) OVER
(
PARTITION BY CC.CustID
) AS CUST_Q3
/* Q3 ALL */
,SUM(
CASE
WHEN CC.MM BETWEEN 7 AND 9 THEN CC.Amt
ELSE 0.0
END
) OVER
(
PARTITION BY (SELECT NULL)
) AS ALL_Q3
/* Q4 CUSTOMER */
,SUM(
CASE
WHEN CC.MM BETWEEN 10 AND 12 THEN CC.Amt
ELSE 0.0
END
) OVER
(
PARTITION BY CC.CustID
) AS CUST_Q4
/* Q4 ALL */
,SUM(
CASE
WHEN CC.MM BETWEEN 10 AND 12 THEN CC.Amt
ELSE 0.0
END
) OVER
(
PARTITION BY (SELECT NULL)
) AS ALL_Q4
/* ALL FOR ALL */
,SUM(CC.Amt) OVER
(
PARTITION BY (SELECT NULL)
) AS SUM_ALL
FROM cte CC
)
SELECT
RB.CustID
,RB.CUST_Q1
,RB.ALL_Q1
,RB.CUST_Q2
,RB.ALL_Q2
,RB.CUST_Q3
,RB.ALL_Q3
,RB.CUST_Q4
,RB.ALL_Q4
,RB.SUM_ALL
FROM REPORT_BASE RB
WHERE RB.DD_RID = 1
Results
CustID CUST_Q1 ALL_Q1 CUST_Q2 ALL_Q2 CUST_Q3 ALL_Q3 CUST_Q4 ALL_Q4 SUM_ALL
------- -------- ------- -------- ------- -------- ------- -------- ------- --------
200 360.0 1440.0 1050.0 1550.0 0.0 0.0 0.0 0.0 2990
777 1080.0 1440.0 500.0 1550.0 0.0 0.0 0.0 0.0 2990
June 16, 2014 at 6:59 am
Maybe something simple as this:
SELECT a.custID,
SUM(CASE WHEN MM BETWEEN 1 AND 3 THEN Amt END) SumQ1 ,
SUM(a.amt) SumAll
from CTE a
GROUP BY CustID
June 16, 2014 at 10:42 am
Thansk, Luis and all
Yes, making separate Group By solves all problems, I'll check it , I have bunch of other fields in my select which could be max or min with this group by.
Tx
M
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply