4

This is a lengthy query, it polls one LIVE database for certain sales information, then a UNION runs the same exact query on the ARCHIVE database for the same information. The results show each store's sales from the two queries on two lines each, one for each DB polled.

The ideal result would merge the results down to ONE line per store, something I'm currently doing in Excel instead.

The QUERY:

DECLARE @FROM DATETIME, @TO DATETIME

    SET @FROM = '12-30-2013 03:00:00'
    SET @TO   = '12-28-2014 03:00:00'

SELECT d.DivisionName, (SUM(PaymentAmount) - SUM(TipAmount))[CC w/o Tips], SUM(TipAmount) [Tips], SUM(PaymentAmount) [Receipts]
  FROM LFLive.POS.OrderPayments  op WITH(NOLOCK)
  JOIN LFLive.POS.Orders          o WITH(NOLOCK) ON o.OrderId=op.OrderId  
  JOIN LFLive.POS.Payments        p WITH(NOLOCK) ON p.PaymentId=op.PaymentId 
  JOIN LFLive.Directory.Divisions d WITH(NOLOCK) ON d.DivisionId=o.DivisionId  
 WHERE op.IsVoided=0 AND op.IsDeleted=0 AND o.IsVoided=0 AND o.IsClosed=1
   AND o.OrderTypeId = 2 AND p.PaymentTypeId = 6 
   AND o.DateClosed BETWEEN @FROM AND @TO
 GROUP BY d.DivisionName

 UNION

SELECT d.DivisionName, (SUM(PaymentAmount) - SUM(TipAmount))[CC w/o Tips], SUM(TipAmount) [Tips], SUM(PaymentAmount) [Receipts]
  FROM LFArchive.POS.OrderPayments op WITH(NOLOCK)
  JOIN LFArchive.POS.Orders         o WITH(NOLOCK) ON o.OrderId=op.OrderId  
  JOIN LFLive.POS.Payments                 p WITH(NOLOCK) ON P.PaymentId=op.PaymentId 
  JOIN LFLive.Directory.Divisions          d WITH(NOLOCK) ON d.DivisionId=o.DivisionId  
 WHERE op.IsVoided=0 AND op.IsDeleted=0 AND o.IsVoided=0 AND o.IsClosed=1
   AND o.OrderTypeId = 2 AND p.PaymentTypeId = 6 
   AND o.DateClosed BETWEEN @FROM AND @TO
 GROUP BY d.DivisionName
 ORDER BY d.DivisionName

Notice the LFArchive in two lines of the second half? That's the only difference.

The RESULTS:

DivisionName    CC w/o Tips     Tips            Receipts
------------    -----------     ----            ---------
Store1          96794.67        18875.97        115670.64
Store1         342488.77        67588.71        410077.48
Store2          90863.25        20353.98        111217.23
Store2         315051.32        69489.22        384540.54
Store3          82561.83        15213.77        97775.60
Store3         302811.03        54493.71        357304.74

What I'd rather have is ONE line per store... never tried to merge down a UNION results output so not sure exactly how to ask the question.

DESIRED RESULT:

DivisionName    CC w/o Tips     Tips            Receipts
------------    -----------     ----            ---------
Store1          439283.44       86464.68        525748.12
Store2          405914.57       89843.20        495757.77
Store3          385372.86       69707.48        455080.34
3
  • Do you want the CC, Tips and Receipts result added or in separate columns? (so, 1+3+3 columns) Jan 14, 2015 at 0:41
  • In the example above, the output would look exactly the same, but Store1's 3 columns would have the existing data added together for one big set of answers. So instead of 6 lines (as above) there would only be 3. Jan 14, 2015 at 0:54
  • I've updated the question above to show the desired results. Jan 14, 2015 at 1:14

1 Answer 1

4

It seems you just need to use a derived table or common table expression with a new grouping level:

DECLARE 
    @FROM datetime = CONVERT(datetime, '12-30-2013 03:00:00', 101), 
    @TO datetime = CONVERT(datetime, '12-28-2014 03:00:00', 101);

WITH Combined AS
(
    SELECT 
        DivisionName = d.DivisionName, 
        [CC w/o Tips] = (SUM(PaymentAmount) - SUM(TipAmount)), 
        [Tips] = SUM(TipAmount),
        [Receipts] = SUM(PaymentAmount)
    FROM LFLive.POS.OrderPayments AS op
    JOIN LFLive.POS.Orders AS o
        ON o.OrderId=op.OrderId
    JOIN LFLive.POS.Payments AS p
        ON p.PaymentId=op.PaymentId 
    JOIN LFLive.Directory.Divisions AS d 
        ON d.DivisionId=o.DivisionId  
    WHERE 
        op.IsVoided=0 
        AND op.IsDeleted=0 
        AND o.IsVoided=0 
        AND o.IsClosed=1
        AND o.OrderTypeId = 2 
        AND p.PaymentTypeId = 6 
        AND o.DateClosed BETWEEN @FROM AND @TO
    GROUP BY 
        d.DivisionName

    UNION ALL -- Not UNION!

    SELECT 
        DivisionName = d.DivisionName, 
        [CC w/o Tips] = (SUM(PaymentAmount) - SUM(TipAmount)), 
        [Tips] = SUM(TipAmount),
        [Receipts] = SUM(PaymentAmount)
    FROM LFArchive.POS.OrderPayments AS op
    JOIN LFArchive.POS.Orders AS o
        ON o.OrderId=op.OrderId
    JOIN LFLive.POS.Payments AS p
        ON p.PaymentId=op.PaymentId 
    JOIN LFLive.Directory.Divisions AS d 
        ON d.DivisionId=o.DivisionId  
    WHERE 
        op.IsVoided=0 
        AND op.IsDeleted=0 
        AND o.IsVoided=0 
        AND o.IsClosed=1
        AND o.OrderTypeId = 2 
        AND p.PaymentTypeId = 6 
        AND o.DateClosed BETWEEN @FROM AND @TO
    GROUP BY 
        d.DivisionName
)
SELECT
    C.DivisionName,
    [CC w/o Tips] = SUM(C.[CC w/o Tips]),
    [Tips] = SUM(C.Tips),
    [Receipts] = SUM(C.Receipts)
FROM Combined AS C
GROUP BY
    C.DivisionName
ORDER BY 
    C.DivisionName;

I replaced the UNION with UNION ALL because you don't want duplicate removal.

1
  • Well that worked perfectly! Thanks for the new syntax, I'll be pulling this apart over the next few days, love learning new methods. Thanks so much! Jan 14, 2015 at 3:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.