Help with CTE's

1

Hello Experts,

I have the following query which works just fine in the way I intended.

SELECT
MAX (ch.quote_number) AS OurRef,
sh.orderid AS OrderId,
sd.receipt_number AS ReceiptNo,
sd.item_description AS ItemTag,
ch.quality_check AS InternalPostDate,
sh.received_date AS ReceivedDate,
sh.despatched_date AS DespatchDate,
sh.target_delivery_date AS DeliveryDate,
CASE
WHEN CAST (sh.despatched_date AS Date) <= CAST(sh.target_delivery_date AS Date) THEN 'On Time'
ELSE 'Delivery Failure'
END AS 'Quality Metric'
FROM salesheader sh
INNER JOIN salesdetail sd
ON sh.orderid = sd.orderid
INNER JOIN clientdetails cd
ON cd.clientnumber = sh.clientnumber
INNER JOIN client history ch
ON ch.quote_number = cd.quote_number
INNER JOIN activities a
ON a.activityref = ch.activityref
WHERE sh.despatched_date >= '01/01/18'
AND sd.leadteam IN (CampaignCodeA, CampaignCodeB, CampaignCodeC)
AND sh.order_status = 'Confirmed'

GROUP BY
sh.orderid AS OrderId,
sd.receipt_number,
sd.item_description,
ch.quality_check,
sh.received_date,
sh.despatched_date,
sh.target_delivery_date

I am trying to develop my SQL skills and wondered if it was possible to write the same query using CTE's. This is what I have so far:

WITH CTE1
AS ( SELECT MAX (quote_number), quality_check
FROM clienthistory
GROUP BY quality_check
),
WITH CTE2
AS ( SELECT orderid, received_date, despatched_date, target_delivery_date,
CASE
WHEN CAST (despatched_date AS Date) <= CAST(target_delivery_date AS Date) THEN 'On Time'
ELSE 'Delivery Failure'
END AS 'Quality Metric'
FROM salesheader
),

With CTE3
AS (SELECT receipt_number, item_description
FROM salesdetail
)

I am getting a bit stuck at the point, not sure how to put this together as a CTE.

Does anybody have any suggestions on if/how this query could be written as a CTE?

Thanks for your time

Vinnie

2

I wouldn't worry about changing this specific query to cte.
But for the sake of learning, here goes (mind you, this will be slower than your query)

Creating each table as cte (as it looks like you were trying to do) 
with cte_sales_header
  as (select orderid
            ,received_date
            ,despatched_date
            ,target_delivery_date
            ,clientnumber
        from salesheader
       where despatched_date>=cast('2018-01-01' as date)
         and order_status='Confirmed'
       group by orderid
               ,received_date
               ,despatched_date
               ,target_delivery_date
               ,clientnumber
     )
    ,cte_sales_detail
  as (select orderid
            ,receipt_number
            ,item_description
        from salesdetail as d
       where exists (select 1 from cte_sales_header as h where h.orderid=d.orderid)
         and leadteam in (campaigncodea
                         ,campaigncodeb
                         ,campaigncodec
                         )
     )
    ,cte_client_detail
  as (select clientnumber
            ,max(quote_number) as quote_number
        from clientdetails as d
       where exists (select 1 from cte_sales_header as h where h.clientnumber=d.clientnumber)
       group by clientnumber
     )
    ,cte_client_history
  as (select quote_number
            ,activityref
            ,quality_check
        from clienthistory as h
       where exists (select 1 from cte_client_detail as d where d.quote_number=h.quote_number)
     )
    ,cte_activity
  as (select quote_number
        from activities as a
       where exists (select 1 from cte_client_history as h where h.activityref=a.activityref)
     )
select ch.quote_number as OurRef
      ,sh.orderid as OrderId
      ,sh.receipt_number as ReceiptNo
      ,sd.item_description as ItemTag
      ,ch.quality_check as InternalPostDate
      ,sh.received_date as ReceivedDate
      ,sh.despatched_date as DespatchDate
      ,sh.target_delivery_date as DeliveryDate
      ,case
          when cast(sh.despatched_date as date)<=cast(sh.target_delivery_date as date)
          then 'On Time'
          else 'Delivery Failure"
       end as [Quality Metric]
  from cte_sales_header as sh
       inner join cte_sales_detail as sd
               on sd.orderid=sh.orderid
       inner join cte_client_detail as cd
               on cd.clientnumber=sh.clientnumber
       inner join cte_client_history as ch
               on ch.quote_number=cd.quote_number
       inner join cte_activity as a
               on a.activityref=ch.activityref
;
Creating two cte's - one for sale and one for client 
with cte_sale
  as (select h.orderid
            ,h.received_date
            ,h.despatched_date
            ,h.target_delivery_date
            ,h.clientnumber
            ,d.receipt_number
            ,d.item_description
        from salesheader as h
             inner join salesdetail as d
                     on d.orderid=h.orderid
                    and d.leadteam in (h.campaigncodea
                                      ,h.campaigncodeb
                                      ,h.campaigncodec
                                      )
       where h.despatched_date>=cast('2018-01-01' as date)
         and h.order_status='Confirmed'
       group by h.orderid
               ,h.received_date
               ,h.despatched_date
               ,h.target_delivery_date
               ,h.clientnumber
               ,d.receipt_number
               ,d.item_description
     )
    ,cte_client
  as (select d.clientnumber
            ,h.quality_check
            ,max(d.quote_number) as quote_number
        from clientdetails as d
             inner join clienthistory as h
                     on h.quote_number=d.quote_number
             inner join activities as a
                     on a.activityref=h.activityref
       where exists (select 1 from cte_sale as s where s.clientnumber=d.clientnumber)
       group by d.clientnumber
               ,h.quality_check
     )
select c.quote_number as OurRef
      ,s.orderid as OrderId
      ,s.receipt_number as ReceiptNo
      ,s.item_description as ItemTag
      ,c.quality_check as InternalPostDate
      ,s.received_date as ReceivedDate
      ,s.despatched_date as DespatchDate
      ,s.target_delivery_date as DeliveryDate
      ,case
          when cast(s.despatched_date as date)<=cast(s.target_delivery_date as date)
          then 'On Time'
          else 'Delivery Failure"
       end as [Quality Metric]
  from cte_sale as s
       inner join cte_client as c
               on c.clientnumber=s.clientnumber
;

Again I have to say, I would NOT do this in your particular case!

3

Thank you for taking the time to help me. Just for my understanding - what you have provided is two different ways of writing the CTE which will get the same outcome?

4

They should both produce same result as your query, but I'm not absolute certain, as I didn't have sample data to test it on.

5

Ok, Thanks again for your help