Perform a Series of Calculations on a Multiple CTE Table

Question

Can't seem to figure out the formula for performing a series of functions on my query. I need to subtract the figures in the DIF column depending on the STAT code. I need to perform the same series of functions in every case. STAT I0099 MINUS E0002, this result will always be the denominator. After this I need to take each of the other lines in the table and divide them by this result.

1st (2811-98), 2nd E0013 (52/(2811-98)) 3rd E0019 (405/(2811-98)) 4th E0004 (2256/(2811-98) End.

I've tried adding rollup and a couple of others things, I saw on here, but none were successful.

WITH i_stat_cte
AS
(
SELECT ROW_NUMBER() OVER(partition by STAT Order by UDATE ) as 
Rn,JCDS_SOGR.OBJNR, JCDS_SOGR.STAT,JCDS_SOGR.UDATE,JCDS_SOGR.CHGNR,JCDS_SOGR.CDTCODE,JCDS_SOGR.CHIND,JCDS_SOGR.INACT,JCDS_SOGR.TCODE,TJ02T.TXT30, EQUI.BAUJJ as "Year", T370K_T.EARTX as Model,
LAG(UDATE) OVER(partition by STAT Order by UDATE ) As PrevUDate,
COUNT(*) OVER(partition by STAT) As [Count]
from JCDS_SOGR
Join TJ02T on JCDS_SOGR.STAT = TJ02T.ISTAT
Join EQUI on JCDS_SOGR.OBJNR = EQUI.OBJNR
Join T370K_T on equi.EQART = T370K_T.EQART
where jcds_sogr.OBJNR = 'IE000000000010003137'
and TJ02T.SPRAS = 'E'
)
,
e_stat_cte
AS
(
SELECT ROW_NUMBER() OVER(partition by STAT Order by UDATE ) as Rn, JCDS_SOGR.OBJNR, JCDS_SOGR.STAT,JCDS_SOGR.UDATE,JCDS_SOGR.CHGNR,JCDS_SOGR.CDTCODE,JCDS_SOGR.CHIND,JCDS_SOGR.INACT,JCDS_SOGR.TCODE,TJ30T.TXT30, EQUI.BAUJJ as "Year", T370K_T.EARTX as Model,
LAG(UDATE) OVER(partition by STAT Order by UDATE ) As PrevUDate,
COUNT(*) OVER(partition by STAT) As [Count], TJ30T.MANDT as Client
from JCDS_SOGR
Join TJ30T on JCDS_SOGR.STAT = TJ30T.ESTAT
Join EQUI on JCDS_SOGR.OBJNR = EQUI.OBJNR
Join T370K_T on equi.EQART = T370K_T.EQART
where jcds_sogr.OBJNR = 'IE000000000010003137'
and TJ30T.SPRAS = 'E'AND TJ30T.MANDT='400'
AND TJ30T.STSMA = 'VEHICLE' AND T370K_T.MANDT = '400')

SELECT Max(rn) As [Count],
OBJNR,Year, Model, STAT,TXT30,
SUM(CASE WHEN rn%2=0 THEN DATEDIFF(d,PrevUDate,UDATE) 
    WHEN rn=[Count] THEN  DATEDIFF(d,UDATE,getDate())   
ELSE 0 END) as DIF
from i_stat_cte
Group BY OBJNR, STAT,TXT30, Year, Model

UNION

SELECT Max(rn) As [Count],
OBJNR,Year, Model, STAT,TXT30,
SUM(CASE WHEN rn%2=0 THEN DATEDIFF(d,PrevUDate,UDATE) 
    WHEN rn=[Count] THEN  DATEDIFF(d,UDATE,getDate())   
ELSE 0 END) as DIF
from e_stat_cte
Group BY OBJNR, STAT,TXT30, Year, Model

EXPECTED RESULTS

Count   OBJNR                  Year Model   STAT    TXT30   DIF   Avail | Calculations
1   IE000000000010003137    2011    Orion  I0099    Avail  2810 
2   IE000000000010003137    2011    Orion  E0002    Await  98   
4   IE000000000010003137    2011    Orion  E0013    Non Op 52     .0191740  = (52/(2810-98))
4   IE000000000010003137    2011    Orion  E0019    OperBk 405    .1493363  = (405/(2810-98))
7   IE000000000010003137    2011    Orion  E0004    Oper   2255   .8314897  = (2255/(2810-98))

Have you considered using a temp table instead so that you can see each table's result prior to using them? CTEs performs pretty slow too mostly due to how people write them without regards to any index. — Earl, Jan 22 '19 at 23:54
Not totally sure how I would make that modification, but I will try and look it up. Thank you for the suggestion. — Erikah, Jan 23 '19 at 00:36

Earl · Answer 1 · 2019-01-28T22:15:51.957

--The code below is a conversion of your code from CTE to temp tables. --Hopefully you can perform better diagnostics on it. Just evaluate each of the data you are producing on your last query. I think the UNION is a bit sloppy. Are you sure it's really a UNION and not a UNION ALL?

DROP TABLE IF EXISTS #i_stat_cte

SELECT ROW_NUMBER() OVER(partition by STAT Order by UDATE ) as 
Rn,JCDS_SOGR.OBJNR, JCDS_SOGR.STAT,JCDS_SOGR.UDATE,JCDS_SOGR.CHGNR,JCDS_SOGR.CDTCODE,JCDS_SOGR.CHIND,JCDS_SOGR.INACT,JCDS_SOGR.TCODE,TJ02T.TXT30, EQUI.BAUJJ as "Year", T370K_T.EARTX as Model,
LAG(UDATE) OVER(partition by STAT Order by UDATE ) As PrevUDate,
COUNT(*) OVER(partition by STAT) As [Count]
INTO #i_stat_cte
from JCDS_SOGR
Join TJ02T on JCDS_SOGR.STAT = TJ02T.ISTAT
Join EQUI on JCDS_SOGR.OBJNR = EQUI.OBJNR
Join T370K_T on equi.EQART = T370K_T.EQART
where jcds_sogr.OBJNR = 'IE000000000010003137'
and TJ02T.SPRAS = 'E'

DROP TABLE IF EXISTS #e_stat_cte

SELECT ROW_NUMBER() OVER(partition by STAT Order by UDATE ) as Rn, JCDS_SOGR.OBJNR, JCDS_SOGR.STAT,JCDS_SOGR.UDATE,JCDS_SOGR.CHGNR,JCDS_SOGR.CDTCODE,JCDS_SOGR.CHIND,JCDS_SOGR.INACT,JCDS_SOGR.TCODE,TJ30T.TXT30, EQUI.BAUJJ as "Year", T370K_T.EARTX as Model,
LAG(UDATE) OVER(partition by STAT Order by UDATE ) As PrevUDate,
COUNT(*) OVER(partition by STAT) As [Count], TJ30T.MANDT as Client
INTO #e_stat_cte
from JCDS_SOGR
Join TJ30T on JCDS_SOGR.STAT = TJ30T.ESTAT
Join EQUI on JCDS_SOGR.OBJNR = EQUI.OBJNR
Join T370K_T on equi.EQART = T370K_T.EQART
where jcds_sogr.OBJNR = 'IE000000000010003137'
and TJ30T.SPRAS = 'E'AND TJ30T.MANDT='400'
AND TJ30T.STSMA = 'VEHICLE' AND T370K_T.MANDT = '400'

 -- VERIFY YOU GET WHAT YOU EXPECT
select * from #i_stat_cte    

 -- VERIFY YOU GET WHAT YOU EXPECT
select * from #e_stat_cte    

 -- VERIFY YOU GET WHAT YOU EXPECT
SELECT Max(rn) As [Count],
OBJNR,Year, Model, STAT,TXT30,
SUM(CASE WHEN rn%2=0 THEN DATEDIFF(d,PrevUDate,UDATE) 
    WHEN rn=[Count] THEN  DATEDIFF(d,UDATE,getDate())   
ELSE 0 END) as DIF
from #i_stat_cte
Group BY OBJNR, STAT,TXT30, Year, Model

--UNION    

 -- VERIFY YOU GET WHAT YOU EXPECT
SELECT Max(rn) As [Count],
OBJNR,Year, Model, STAT,TXT30,
SUM(CASE WHEN rn%2=0 THEN DATEDIFF(d,PrevUDate,UDATE) 
    WHEN rn=[Count] THEN  DATEDIFF(d,UDATE,getDate())   
ELSE 0 END) as DIF
from #e_stat_cte
Group BY OBJNR, STAT,TXT30, Year, Model

Thank you so much Earl, I am trying now, but keep getting a message that "Msg 2714, Level 16, State 6, Line 2 There is already an object named '#i_stat_cte' in the database." Regarding UNION vs UNION ALL there are no duplicates in the table, so that's why we went with Union. — Erikah, Jan 28 '19 at 17:26
Thank you so much Earl, I figured out the error message. The script works perfectly. — Erikah, Jan 28 '19 at 17:39
Now I will try some calculations on this and hopefully I can get closer to adding the calculated field. — Erikah, Jan 28 '19 at 17:40
Earl regarding the OBJNR ='IE000000000010031347' can I create a series of these for this field to equal? — Erikah, Jan 28 '19 at 18:55
Hi. Maybe you mean **jcds_sogr.OBJNR IN ('IE000000000010003137','IE000000000010003138','IE000000000010003139')** — Earl, Jan 28 '19 at 22:18
Hi Earl, I'm not sure why I didn't get a notification for this message. Thank you for the response. I mean this where jcds_sogr.OBJNR = 'IE000000000010003137' Can I say where jcds_sogr.OBJNR = listofOBJNR if I assign a list of object numbers to that name — Erikah, Jan 29 '19 at 23:04
@Erikah if i understand you right, you want to do equality condition for jcds_sogr.OBJNR for a list of values. If that is the case my answer above using the IN statement should be sufficient. Now, you may not want to hardcode the values. If that is the case you can create a query as well jcds_sogr.OBJNR IN (SELECT JCDS_SOGR.OBJNR FROM JCDS_SOGR INNER JOIN.. etc) — Earl, Jan 30 '19 at 00:12

Perform a Series of Calculations on a Multiple CTE Table

1 Answers1