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
BrockAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BrockAuthor Commented:
You are awesome!

Brock.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.