• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 19
  • Last Modified:

Rewriting a CTE

Can the CTE be rewritten ? The boss does not like CTE?

WITH CTE AS (
 SELECT *
 ,CASE WHEN AGE < 30 THEN 'Age 1-29' WHEN AGE >= 30
   AND AGE < 35 THEN 'Age 30-34' WHEN AGE >= 35
   AND AGE < 39 THEN 'Age 35-39' WHEN AGE >= 39
   AND AGE < 45 THEN 'Age 40-44' WHEN AGE >= 45
   AND AGE < 50 THEN 'Age 45-49' WHEN AGE >= 50
   AND AGE < 55 THEN 'Age 50-54' WHEN AGE >= 55
   AND AGE < 60 THEN 'Age 55-59' WHEN AGE >= 60
   AND AGE <= 64 THEN 'Age 60-64' END A1
  FROM PS_NC_CRIT_XML_DTL )
 SELECT CTE.OPRID
 , CTE.RUN_CNTL_ID
 , CTE.SEX
 , CTE.SMOKER
 , A1 AGEGROUP
 ,COUNT(*) [#Employees]
 ,SUM(CAST(VOLUME AS INT)) VOLUME
 ,(SUM(RATE)*25) RATE
 , SUM(NC_PREMIUM) PREMIUM
 , CTL.FULL_PART_TIME
  FROM CTE CTE INNER JOIN %Table(NC_CRIT_XML_CTL) CTL ON CTE.OPRID = CTL.OPRID
   AND CTE.RUN_CNTL_ID = CTL.RUN_CNTL_ID
  GROUP BY CTE.OPRID
  ,CTE.RUN_CNTL_ID
  ,CTE.SEX
  ,CTE.SMOKER
  ,CTE.A1
  ,CTL.FULL_PART_TIME
0
Brock
Asked:
Brock
  • 2
  • 2
1 Solution
 
ste5anSenior DeveloperCommented:
Sure, but "does not like" is the worst business reason I ever heard of. It's wasting time and money. You can simple substitute it by a ordinary sub-query.

SELECT   CTE.OPRID ,
         CTE.RUN_CNTL_ID ,
         CTE.SEX ,
         CTE.SMOKER ,
         CTE.AGEGROUP ,
         COUNT(*) AS [#Employees] ,
         SUM(CAST(VOLUME AS INT)) AS VOLUME ,
         SUM(RATE) * 25 AS RATE ,
         SUM(NC_PREMIUM) AS PREMIUM ,
         CTL.FULL_PART_TIME
FROM     (   SELECT I.* ,
                    CASE WHEN I.AGE < 30 THEN 'AGE 1-29'
                         WHEN I.AGE >= 30
                              AND I.AGE < 35 THEN 'AGE 30-34'
                         WHEN I.AGE >= 35
                              AND I.AGE < 39 THEN 'AGE 35-39'
                         WHEN I.AGE >= 39
                              AND I.AGE < 45 THEN 'AGE 40-44'
                         WHEN I.AGE >= 45
                              AND I.AGE < 50 THEN 'AGE 45-49'
                         WHEN I.AGE >= 50
                              AND I.AGE < 55 THEN 'AGE 50-54'
                         WHEN I.AGE >= 55
                              AND I.AGE < 60 THEN 'AGE 55-59'
                         WHEN I.AGE >= 60
                              AND I.AGE <= 64 THEN 'AGE 60-64'
                    END AS AGEGROUP
             FROM   PS_NC_CRIT_XML_DTL I ) CTE
         INNER JOIN [%Table(NC_CRIT_XML_CTL)] CTL ON CTE.OPRID = CTL.OPRID
                                                     AND CTE.RUN_CNTL_ID = CTL.RUN_CNTL_ID
GROUP BY CTE.OPRID ,
         CTE.RUN_CNTL_ID ,
         CTE.SEX ,
         CTE.SMOKER ,
         CTE.AGEGROUP ,
         CTL.FULL_PART_TIME;

Open in new window


p.s. use always table alias names. Where are RATE, VOLUME and NC_PREMIUM from?
0
 
BrockAuthor Commented:
Hi Ste5an,

Rate, volume and nc_premium are coming from PS_NC_CRIT_XML_DTL.  I am populating this table  using a program.  The information in this table is correct but my summaries are off in the allinone jpg.  I am trying to condense 4 sep reports (FT smk, FT nonsmk, PT smk, PT nonsmk. ) into one report.  The user can only run 1 rpt either FT or PT and should get both SMK and NonSmk on their report.  

What you have given me will allow me to work with the data.  My females in the age categories are off  but when I compare dtl vw of  the old report : 247 tot individ   and new report dtl vw sourced differently : 246 tot individ.

Thanks for your help.  Yes, it is a waste of money to question how one has arrived at a solution as long as the solution is not causing havoc in the system and is easy to maintain but I pick my battles carefully.  And yes, I believe in standing up for what you believe in :-)

Brock.
allinone.JPG
Smoker_old.JPG
Non_Smoker_old.JPG
0
 
ste5anSenior DeveloperCommented:
What you have given me will allow me to work with the data.  My females in the age categories are off  but when I compare dtl vw of  the old report : 247 tot individ   and new report dtl vw sourced differently : 246 tot individ.

Don't rely on the reports. Compare the data in SSMS. E.g. using the EXCEPT/INTERSECT operators. Symbolic as:

SELECT * FROM QueryOld
INTERSECT
SELECT * FROM QueryNew;

Open in new window


must yield the same number of rows or

SELECT * FROM QueryOld
EXCEPT
SELECT * FROM QueryNew;

SELECT * FROM QueryNew
EXCEPT
SELECT * FROM QueryOld;

Open in new window


must return both no rows.

Just an argument: moving away from it can introduce problems by binding outer scope columns.

WITH Cte
AS ( SELECT name )
SELECT T.name ,
       C.*
FROM   sys.tables T
       CROSS APPLY Cte C;

SELECT T.name ,
       C.*
FROM   sys.tables T
       CROSS APPLY ( SELECT name ) C;

Open in new window

0
 
BrockAuthor Commented:
You are awesome!

Brock.
0

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now