March 21, 2011 at 12:06 pm
I am going to be reworking a query soon that joins a single table to itself multiple times. I'm trying to figure out if I need to keep these derived tables / sub-queries or if I can use a CTE structure to optimize the query.
--table code
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [DT].[Detail](
[DetID] [int] IDENTITY(1,1) NOT NULL,
[CycleID] [int] NOT NULL,
[TransactionType] [char](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[TransactionAmount] [decimal](12, 2) NOT NULL,
[AmtSign] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DTConID] [int] NOT NULL,
) ON [PRIMARY]
SET ANSI_PADDING OFF
The data looks something like this:
DetID CycleID TransactionType TransactionAmt AmtSign DTConID
1 182 SALE 14.95 + 33
2 182 CNL 18.99 + 33
3 182 PRE 18.99 + 33
4 182 SALE 129.50 - 84
5 183 PRE 75.00 + 127
5 183 SALE 67.85 - 127
6 183 SALE 18.29 + 33
DTConIDs come from a third party system. DetID is my transaction table ID. Each Cycle can have single or multiple records for each DTConID, but each DTConID can only have 1 of each different transaction type in each cycle. So while 33 can have all three transaction types in Cycle 182 (or 183 or 184, etc), it will not have 2 SALE or 2 PRE in the same cycle. But it doesn't have to have all three transaction types. It could end up with 0-1 in a particular cycle.
--Current query code
SELECT srsf.DTConID, srsf.OrderNum,
CASE WHEN srsf.TransactionDate >= srsf.CycleStartDate AND srsf.TransactionDate <= srsf.CycleEndDate
AND pr1.DTConID IS NOT NULL AND srsf.Status NOT IN ('V','R','C')
THEN
(CASE WHEN (IsNull(srsf.p2,0.00) + IsNull(srsf.p3,0.00)) <> IsNull(pr1.TransactionAmount,0.00)
AND IsNull(srsf.IsReinstate,0) = 0
THEN (IsNull(srsf.p2,0.00) + IsNull(srsf.p3,0.00)) - IsNull(pr1.TransactionAmount,0.00)
WHEN (IsNull(srsf.p2,0.00) + IsNull(srsf.p3,0.00)) <> IsNull(pr1.TransactionAmount,0.00)
AND IsNull(srsf.IsReinstate,0) = 1
THEN (IsNull(srsf.p2,0.00) + IsNull(srsf.p3,0.00)) - IsNull(pr1.TransactionAmount,0.00)
WHEN (IsNull(srsf.p2,0.00) + IsNull(srsf.p3,0.00)) = IsNull(pr1.TransactionAmount,0.00)
AND IsNull(srsf.IsReinstate,0) = 1
THEN (IsNull(srsf.p2,0.00) + IsNull(srsf.p3,0.00)) ELSE 0.00 END)
WHEN srsf.TransactionDate >= srsf.CycleStartDate AND srsf.TransactionDate <= srsf.CycleEndDate
AND pr1.DTConID IS NULL AND srsf.Status IN ('V','R','C') THEN (IsNull(srsf.p2,0.00) + IsNull(srsf.p3,0.00))
ELSE 0.00 END AS SALE,
CASE WHEN srsf.TransactionDate >= srsf.CycleStartDate AND srsf.TransactionDate <= srsf.CycleEndDate
AND pre1.DTConID IS NULL AND srsf.Status NOT IN ('V','R','C')
THEN (IsNull(srsf.p1,0.00) + IsNull(srsf.p3,0.00))
WHEN srsf.TransactionDate >= srsf.CycleStartDate AND srsf.TransactionDate <= srsf.CycleEndDate
AND pre1.DTConID IS NOT NULL AND srsf.Status NOT IN ('V','R','C')
THEN (CASE WHEN (IsNull(srsf.p1,0.00) + IsNull(srsf.p3,0.00)) <> IsNull(pre1.TransactionAmount,0.00)
AND IsNull(srsf.IsReinstate,0) = 0
THEN (IsNull(srsf.p1,0.00) + IsNull(srsf.p3,0.00)) - IsNull(pre1.TransactionAmount,0.00)
WHEN (IsNull(srsf.p1,0.00) + IsNull(srsf.p3,0.00)) <> IsNull(pre1.TransactionAmount,0.00)
AND IsNull(srsf.IsReinstate,0) = 1
THEN (IsNull(srsf.p1,0.00) + IsNull(srsf.p3,0.00)) - IsNull(pre1.TransactionAmount,0.00)
WHEN (IsNull(srsf.p1,0.00) + IsNull(srsf.p3,0.00)) = IsNull(pre1.TransactionAmount,0.00)
AND IsNull(srsf.IsReinstate,0) = 1
THEN (IsNull(srsf.p1,0.00) + IsNull(srsf.p3,0.00)) ELSE 0.00 END)
WHEN srsf.TransactionDate >= srsf.CycleStartDate AND srsf.TransactionDate <= srsf.CycleEndDate
AND pre1.DTConID IS NULL AND srsf.Status IN ('V','R','C') THEN (IsNull(srsf.p1,0.00) + IsNull(srsf.p3,0.00)) ELSE 0.00 END AS pre,
FROM DT.Staging_Feed srsf
LEFT OUTER JOIN (SELECT DISTINCT DTConID, TransactionType,
Sum(Convert(decimal(12,2),CASE WHEN AmtSign = '-' THEN 0 - IsNull(TransactionAmount,0.00)
WHEN AmtSign = '+' THEN IsNull(TransactionAmount,0.00) END)) AS TransactionAmount
FROM DT.Detail
WHERE CycleID < @CycleID AND TransactionType = 'SALE'
GROUP BY DTConID, TransActionType) pr1
ON srsf.DTConID = pr1.DTConID
LEFT OUTER JOIN (SELECT DISTINCT DTConID, TransactionType,
Sum(Convert(decimal(12,2),CASE WHEN AmtSign = '-' THEN 0 - IsNull(TransactionAmount,0.00
WHEN AmtSign = '+' THEN IsNull(TransactionAmount,0.00) END)) AS TransactionAmount
FROM DT.Detail
WHERE CycleID < @CycleID AND TransactionType = 'PRE'
GROUP BY DTConID, TransActionType) pre1
ON srsf.DTConID = pre1.DTConID;
Now, that doesn't look too bad, but I've cut out several repeated joins on different Transaction Types for brevity's sake. The pattern is "Find transaction A / B / C / D from all previous cycles and sum the total for calculating the new totals up in the SELECT statement."
I'm trying to decide if I can do a CTE above the SELECT statement for this code to optimize it, but I'm thinking there are so many different transaction types that I'll end up stuck with doing a CTE with a UNION ALL or that I'll just have to leave the derived tables as they are. Here's what I'm thinking for the CTE:
WITH PrevTransactions
AS (SELECT DISTINCT DTConID, TransactionType,
Sum(Convert(decimal(12,2),CASE WHEN AmtSign = '-' THEN 0 - IsNull(TransactionAmount,0.00
WHEN AmtSign = '+' THEN IsNull(TransactionAmount,0.00) END)) AS TransactionAmount
FROM DT.Detail
WHERE CycleID < @CycleID AND TransactionType = 'PRE'
GROUP BY DTConID, TransActionType
UNION ALL
SELECT DISTINCT DTConID, TransactionType,
Sum(Convert(decimal(12,2),CASE WHEN AmtSign = '-' THEN 0 - IsNull(TransactionAmount,0.00)
WHEN AmtSign = '+' THEN IsNull(TransactionAmount,0.00) END)) AS TransactionAmount
FROM DT.Detail
WHERE CycleID < @CycleID AND TransactionType = 'SALE'
GROUP BY DTConID, TransActionType)
SELECT...
FROM dt.Staging_Feed srsf
JOIN PrevTransactions pt
....
So, should I stick with what I've got, switch to the CTE, or does anyone have a third option I haven't considered?
March 21, 2011 at 12:27 pm
I don't think you need the UNION ALL in your CTE:
SELECT DTConID, TransactionType,
Sum(Convert(decimal(12,2),CASE WHEN AmtSign = '-' THEN 0 - IsNull(TransactionAmount,0.00)
WHEN AmtSign = '+' THEN IsNull(TransactionAmount,0.00) END)) AS TransactionAmount
FROM Detail
WHERE TransactionType IN('PRE','SALE')
GROUP BY DTConID, TransActionType
March 21, 2011 at 12:34 pm
Wow. I am seriously brain-dead today that I didn't see that solution.
Thanks, Lutz. Sometimes it just takes a second set of eyes. I've been looking at this code for too darned long. 😛
March 21, 2011 at 1:01 pm
Glad I could help 😀
A second set of eyes helps a lot from time to time. Especially, if there's no mouth connected to it... 😉
March 25, 2011 at 12:50 pm
DOH. I knew there was a reason I asked this question and I got so caught up in getting my DDL together that I forgot the most important part.
Rephrase: Not all the derived tables have the same setup. I have two derived tables as listed above, then another derived tables as listed below.
LEFT OUTER JOIN (SELECT DISTINCT DTConID, CNum, IsNull(ABCCurrent,0.00) AS ABC
FROM DT.Detail
WHERE CycleID = (@CycleID - 1) AND TransactionType = 'ABC') ABC
ON srsf.DTConID = ABC.DTConID
LEFT OUTER JOIN (SELECT DISTINCT DTConID, CNum, IsNull(EDFCurrent,0.00) AS EDF
FROM DT.Detail
WHERE CycleID = (@CycleID - 1) AND TransactionType = 'EDF') EDF
ON srsf.DTConID = TAB1.DTConID
LEFT OUTER JOIN (SELECT DISTINCT DTConID, CNum, IsNull(TABCurrent,0.00) AS TAB1
FROM DT.Detail
WHERE CycleID = (@CycleID - 1) AND TransactionType = 'TAB1') TAB1
ON srsf.DTConID = TAB1.DTConID
LEFT OUTER JOIN (SELECT DISTINCT DTConID, CNum, IsNull(TABCurrent,0.00) AS 'TAB2'
FROM DT.Detail
WHERE CycleID = (@CycleID - 2) AND TransactionType = 'TAB2') tab2
ON srsf.DTConID = TAB2.DTConID
Notice that the last one uses a slightly different WHERE clause. I use these calculations to get this month's fee for use in calculations in unshown transactions.
There's another derived table, but that one is a single instance and has much more complicated code, so I plan to leave it as such.
The first mentioned derived tables are adding all previous cycles. Three out of these derived tables are using the cycle before, and the very last one is using 2 cycles before.
So, can I still use the CTE route?
March 25, 2011 at 3:36 pm
Would you please post the query in total as well as some more test data (including expected result)?
I'm confident it still can be done using a single CTE but I'd like to verify.
March 30, 2011 at 5:00 am
Sorry it took so long. Had fires to put out.
SELECT srsf.DTConID, srsf.CNum,
CASE WHEN srsf.TransactionDate >= srsf.CycleStartDate AND srsf.TransactionDate <= srsf.CycleEndDate
AND pr1.DTConID IS NULL AND srsf.Status NOT IN ('V','R','C')
THEN (IsNull(srsf.p2,0.00) + IsNull(srsf.p3,0.00))
WHEN srsf.TransactionDate >= srsf.CycleStartDate AND srsf.TransactionDate <= srsf.CycleEndDate
AND pr1.DTConID IS NOT NULL AND srsf.Status NOT IN ('V','R','C')
THEN (CASE WHEN (IsNull(srsf.p2,0.00) + IsNull(srsf.p3,0.00)) <> IsNull(pr1.TransactionAmount,0.00)
AND IsNull(srsf.IsReinstate,0) = 0
THEN (IsNull(srsf.p2,0.00) + IsNull(srsf.p3,0.00)) - IsNull(pr1.TransactionAmount,0.00)
WHEN (IsNull(srsf.p2,0.00) + IsNull(srsf.p3,0.00)) <> IsNull(pr1.TransactionAmount,0.00)
AND IsNull(srsf.IsReinstate,0) = 1
THEN (IsNull(srsf.p2,0.00) + IsNull(srsf.p3,0.00)) - IsNull(pr1.TransactionAmount,0.00)
WHEN (IsNull(srsf.p2,0.00) + IsNull(srsf.p3,0.00)) = IsNull(pr1.TransactionAmount,0.00)
AND IsNull(srsf.IsReinstate,0) = 1
THEN (IsNull(srsf.p2,0.00) + IsNull(srsf.p3,0.00))
ELSE 0.00 END)
WHEN srsf.TransactionDate >= srsf.CycleStartDate AND srsf.TransactionDate <= srsf.CycleEndDate
AND pr1.DTConID IS NULL AND srsf.Status IN ('V','R','C') THEN (IsNull(srsf.p2,0.00) + IsNull(srsf.p3,0.00))
ELSE 0.00 END AS SALE,
CASE WHEN srsf.CnDate >= srsf.CycleStartDate AND srsf.CnDate <= srsf.CycleEndDate
AND srsf.Status IN ('V','R','C')
THEN (IsNull(srsf.Cnp2,0.00) + IsNull(srsf.Cnp3,0.00))
WHEN srsf.Status NOT IN ('V','R','C') AND IsNull(srsf.CnDate,'01/01/1900') >= srsf.CycleStartDate
AND IsNull(srsf.CnDate,'01/01/1900') <= srsf.CycleEndDate
THEN (IsNull(srsf.Cnp2,0.00) + IsNull(srsf.Cnp3,0.00))
ELSE 0.00 END AS CNLSALE, ---Changed
CASE WHEN srsf.Status = 'A'
THEN (((IsNull(srsf.Ernp1,0.00) + IsNull(srsf.Ernp2,0.00) + IsNull(srsf.Ernp3,0.00) )
- IsNull(tab1.tabLM,0.00)) * @tabPercentage)
- ( (IsNull(tab1.tabLM,0.00) - IsNull(tab2.tabMBL,0.00)) * @tabPercentage)
ELSE 0.00 END AS tab,
CASE WHEN srsf.Status = 'C' AND srsf.CnDate >= srsf.CycleStartDate AND srsf.CnDate <= srsf.CycleEndDate
THEN ( ( (IsNull(srsf.p2,0.00) + IsNull(srsf.p3,0.00))
- ( ( IsNull(srsf.Ernp2,0.00) + IsNull(srsf.Ernp3,0.00))
+ (IsNull(srsf.Cnp2,0.00) + IsNull(srsf.Cnp3,0.00)) ) )
- IsNull(chg.ABC,0.00))
WHEN srsf.Status = 'C' AND (srsf.CnDate < srsf.CycleStartDate OR srsf.CnDate > srsf.CycleEndDate) THEN 0.00
WHEN srsf.Status NOT IN ('C')
THEN ( ( (IsNull(srsf.p2,0.00) + IsNull(srsf.p3,0.00))
- ( ( IsNull(srsf.Ernp2,0.00) + IsNull(srsf.Ernp3,0.00))
+ (IsNull(srsf.Cnp2,0.00) + IsNull(srsf.Cnp3,0.00)) ) )
- IsNull(chg.ABC,0.00))
ELSE 0.00 END AS ABC,
CASE WHEN srsf.Status = 'A' THEN (IsNull(srsf.p2,0.00) + IsNull(srsf.p3,0.00)) ELSE 0.00 END AS IFSALE,
CASE WHEN srsf.PaidDate >= srsf.CycleStartDate AND srsf.PaidDate <= srsf.CycleEndDate
THEN IsNull(srsf.PaidL,0.00) ELSE 0.00 END AS PDL,
CASE WHEN srsf.PaidDate >= srsf.CycleStartDate AND srsf.PaidDate <= srsf.CycleEndDate
THEN IsNull(srsf.PaidA,0.00) ELSE 0.00 END AS PDA,
CASE WHEN srsf.GroupStatus = 'O' AND (lsr2.GroupStatus IS NULL OR lsr2.GroupStatus = 'O')
THEN (IsNull(srsf.Lsr,0.00) - IsNull(lsr2.lsr,0.00))
WHEN srsf.GroupStatus = 'O' AND lsr2.GroupStatus = 'P' THEN IsNull(srsf.lsr,0.00)
WHEN srsf.GroupStatus = 'P' AND lsr2.GroupStatus = 'O' THEN (0 - IsNull(lsr2.lsr,0.00))
WHEN srsf.GroupStatus = 'P' AND lsr2.GroupStatus IS NULL THEN 0.00 ELSE 0.00 END AS LS,
CASE WHEN srsf.TransactionDate >= srsf.CycleStartDate AND srsf.TransactionDate <= srsf.CycleEndDate
AND pre1.DTConID IS NULL AND srsf.Status NOT IN ('V','R','C')
THEN (IsNull(srsf.p1,0.00) + IsNull(srsf.p3,0.00))
WHEN srsf.TransactionDate >= srsf.CycleStartDate AND srsf.TransactionDate <= srsf.CycleEndDate
AND pre1.DTConID IS NOT NULL AND srsf.Status NOT IN ('V','R','C')
THEN (CASE WHEN (IsNull(srsf.p1,0.00) + IsNull(srsf.p3,0.00)) <> IsNull(pre1.TransactionAmount,0.00)
AND IsNull(srsf.IsReinstate,0) = 0
THEN (IsNull(srsf.p1,0.00) + IsNull(srsf.p3,0.00)) - IsNull(pre1.TransactionAmount,0.00)
WHEN (IsNull(srsf.p1,0.00) + IsNull(srsf.p3,0.00)) <> IsNull(pre1.TransactionAmount,0.00)
AND IsNull(srsf.IsReinstate,0) = 1
THEN (IsNull(srsf.p1,0.00) + IsNull(srsf.p3,0.00)) - IsNull(pre1.TransactionAmount,0.00)
WHEN (IsNull(srsf.p1,0.00) + IsNull(srsf.p3,0.00)) = IsNull(pre1.TransactionAmount,0.00)
AND IsNull(srsf.IsReinstate,0) = 1
THEN (IsNull(srsf.p1,0.00) + IsNull(srsf.p3,0.00))
ELSE 0.00 END)
WHEN srsf.TransactionDate >= srsf.CycleStartDate AND srsf.TransactionDate <= srsf.CycleEndDate
AND pre1.DTConID IS NULL AND srsf.Status IN ('V','R','C') THEN (IsNull(srsf.p1,0.00) + IsNull(srsf.p3,0.00))
ELSE 0.00 END AS PRE,
CASE WHEN srsf.Status = 'C' AND srsf.CnDate >= srsf.CycleStartDate AND srsf.CnDate <= srsf.CycleEndDate
THEN ( ( (IsNull(srsf.p1,0.00) + IsNull(srsf.p3,0.00))
- ( ( IsNull(srsf.Ernp1,0.00) + IsNull(srsf.Ernp3,0.00))
+ (IsNull(srsf.Cnp1,0.00) + IsNull(srsf.Cnp3,0.00)) ) )
- IsNull(edf.CHUPR,0.00))
WHEN srsf.Status = 'C' AND (srsf.CnDate < srsf.CycleStartDate OR srsf.CnDate > srsf.CycleEndDate) THEN 0.00
WHEN srsf.Status NOT IN ('C')
THEN ( ( (IsNull(srsf.p1,0.00) + IsNull(srsf.p3,0.00))
- ( ( IsNull(srsf.Ernp1,0.00) + IsNull(srsf.Ernp3,0.00))
+ (IsNull(srsf.Cnp1,0.00) + IsNull(srsf.Cnp3,0.00)) ) )
- IsNull(edf.CHUPR,0.00))
ELSE 0.00 END AS CHUPR,
CASE WHEN srsf.CnDate >= srsf.CycleStartDate AND srsf.CnDate <= srsf.CycleEndDate
THEN (IsNull(srsf.Cnp1,0.00) + IsNull(srsf.Cnp3,0.00)) ELSE 0.00 END AS CNPRE
FROM DT.Staging srsf
LEFT OUTER JOIN (SELECT DISTINCT drf.DTConID, drf.CNum, drf.GroupStatus, IsNull(drf.LSCurrent,0.00) AS Lsr
FROM DT.Detail drf
INNER JOIN (SELECT DISTINCT DTConID, CNum, Max(CycleID) as CycleID
FROM DT.Detail
WHERE CycleID < @CycleID AND TransactionType = 'LS'
GROUP BY DTConID, CNum ) lsr1
ON lsr1.CycleID = drf.CycleID
AND lsr1.DTConID = drf.DTConID
AND lsr1.CNum = drf.CNum
WHERE drf.TransactionType = 'LS' AND drf.CycleID = lsr1.CycleID) lsr2
ON srsf.DTConID = lsr2.DTConID
AND srsf.CNum = lsr2.CNum
LEFT OUTER JOIN (SELECT DISTINCT DTConID, TransactionType,
Sum(Convert(decimal(12,2),CASE WHEN AmtSign = '-' THEN 0 - IsNull(TransactionAmount,0.00)
WHEN AmtSign = '+' THEN IsNull(TransactionAmount,0.00) END)) AS TransactionAmount
FROM DT.Detail
WHERE CycleID < @CycleID AND TransactionType = 'SALE'
GROUP BY DTConID, TransActionType) pr1
ON srsf.DTConID = pr1.DTConID
LEFT OUTER JOIN (SELECT DISTINCT DTConID, TransactionType,
Sum(Convert(decimal(12,2),CASE WHEN AmtSign = '-' THEN 0 - IsNull(TransactionAmount,0.00)
WHEN AmtSign = '+' THEN IsNull(TransactionAmount,0.00) END)) AS TransactionAmount
FROM DT.Detail
WHERE CycleID < @CycleID AND TransactionType = 'PRE'
GROUP BY DTConID, TransActionType) pre1
ON srsf.DTConID = pre1.DTConID
LEFT OUTER JOIN (SELECT DISTINCT DTConID, CNum, IsNull(ABCCurrent,0.00) AS ABC
FROM DT.Detail
WHERE CycleID = (@CycleID - 1) AND TransactionType = 'ABC') abc
ON srsf.DTConID = chg.DTConID
LEFT OUTER JOIN (SELECT DISTINCT DTConID, CNum, IsNull(CHUPRCurrent,0.00) AS CHUPR
FROM DT.Detail
WHERE CycleID = (@CycleID - 1) AND TransactionType = 'CHUPR') edf
ON srsf.DTConID = edf.DTConID
LEFT OUTER JOIN (SELECT DISTINCT DTConID, CNum, IsNull(tabCurrent,0.00) AS tabLM
FROM DT.Detail
WHERE CycleID = (@CycleID - 1) AND TransactionType = 'tab') tab1
ON srsf.DTConID = tab1.DTConID
LEFT OUTER JOIN (SELECT DISTINCT DTConID, CNum, IsNull(tabCurrent,0.00) AS tabMBL
FROM DT.Detail
WHERE CycleID = (@CycleID - 2) AND TransactionType = 'tab') tab2
ON srsf.DTConID = tab2.DTConID;
March 30, 2011 at 12:31 pm
Unfortunately, based on the original table DDL you provided the code won't run.
Please provide the DDL for all related tables as well as sample data and values for the parameter involved.
March 31, 2011 at 5:29 am
LutzM (3/30/2011)
Unfortunately, based on the original table DDL you provided the code won't run.
I'll have to see if I'm allowed to do that. I've already had to sanitize my code to heck and back just to be able to post it.
I might just have to bite the bullet and leave everything as it is.
April 1, 2011 at 12:26 pm
Brandie,
My rule of thumb on CTE vs derived table (when they are functionally equivalent) is use whichever is easiest to read in the code. I use both liberally and sometimes one is easier to read than the other.
Of course CTE's can do recursion (which I avoid when I can) and are easy to piggy back, but sometimes the good ol' derived table is just plain easier to read in the code.
Todd Fifield
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply