Use 2 CTEs in a Single SQL Query

  • 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

     

     

     

     

     

     

  • 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.

    1. You should only use a single "order by" in your query. ( remove the first ' order by 4 desc' )
    2. If Union partners each have unique results, use UNION ALL ! (meshtype = 'Raw Sand A'  vs meshtype = 'Raw Sand B' )
    3. Little use of your CTE as they will both be used/referred only once
    4. 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

  • This was removed by the editor as SPAM

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

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