Use 2 CTEs in a Single SQL Query

Question

Post reply

Use 2 CTEs in a Single SQL Query

gjoelson 29755

SSC Eights!

Points: 848

August 8, 2022 at 2:09 am

#4073711

Hi ,

I'm trying to union 2 CTE queries into one. but must be missing something. as I get an issue after the union . I tried a few things but no luck. Im trying to select different Meshtypes as per query.

The first CTE is sum_cte and the s2nd is sum_cte2

this is what I have....

;with
sum_cte as (

    select *, sum(tonsperhour) over (order by [datetime]) sum_over_tph
     FROM [dbo].[tons_per_hour]
   where whsecd in ('211','210')
and meshtype =  'Raw Sand A'
 and  datetime >= DATEADD(day,-7, GETDATE())
 )
,sum_cte2 as (

    select *, sum(tonsperhour) over (order by [datetime]) sum_over_tph
     FROM [dbo].[tons_per_hour]
   where whsecd in ('211','210')
and meshtype =  'Raw Sand B'
 and  datetime >= DATEADD(day,-7, GETDATE()) 
 )
select floor(sum_over_tph/20) time_grp, DATEPART (d, datetime) AS Day,
DATEPART (m, datetime) AS Month,
       min([datetime]) min_dt, max([datetime]) max_dt,
       count(*) row_count, sum(tonsperhour) sum_tph,
       datediff(minute, min([datetime]), max([datetime])) [Minutes Down],
      Case when Meshtype = 'Raw Sand A' then 'Dryer A'
   when Meshtype = 'Raw Sand B' then 'Dryer B'
   when Meshtype = 'Washed Sand A' then 'Line A'
   when Meshtype = 'Washed Sand B' then 'Line B'
   when Meshtype = 'Washed Sand C' then 'Line C'
   end as meshtype
from sum_cte
group by floor(sum_over_tph/20), meshtype,DATEPART (d, datetime), 
DATEPART (m, datetime) 
order by 4 desc

union

select floor(sum_over_tph/20) time_grp, DATEPART (d, datetime) AS Day,
DATEPART (m, datetime) AS Month,
       min([datetime]) min_dt, max([datetime]) max_dt,
       count(*) row_count, sum(tonsperhour) sum_tph,
       datediff(minute, min([datetime]), max([datetime])) [Minutes Down],
      Case when Meshtype = 'Raw Sand A' then 'Dryer A'
   when Meshtype = 'Raw Sand B' then 'Dryer B'
   when Meshtype = 'Washed Sand A' then 'Line A'
   when Meshtype = 'Washed Sand B' then 'Line B'
   when Meshtype = 'Washed Sand C' then 'Line C'
   end as meshtype
from sum_cte2
group by floor(sum_over_tph/20), meshtype,DATEPART (d, datetime), 
DATEPART (m, datetime) 
order by 4 desc

August 16, 2022 at 8:41 am

This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63503 More actions · Answer 1

Can't you just use OR in your WHERE clause and then do the aggregation? I'm not convinced you need a CTE at all.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 2

You should only use a single "order by" in your query. ( remove the first ' order by 4 desc' )
If Union partners each have unique results, use UNION ALL ! (meshtype = 'Raw Sand A' vs meshtype = 'Raw Sand B' )
Little use of your CTE as they will both be used/referred only once
fyi I prefer using a column name in the order by clause, especially when someone is using "select * "

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me