July 30, 2016 at 3:09 pm
Hi All,
I am trying to understand the below query but finding it very hard to grasp the design of the query.
From my knowledge I can understand that it is trying to pull records by comparing a temp table and two other tables.
SELECT c1.CodingNumber,c1.Suffix,c1.TransactionEffDate,c1.CreationDateTime,f1.AmountTypeCode, sum(coalesce(f1.Amount,0)) AS Amount
FROM #tmpAbstract AS A,CodeChangesdata AS c1
LEFT OUTER JOIN FinancialChangesData AS f1
ON c1.CodingNumber = f1.CodingNumberAND
c1.Suffix = f1.Suffix AND
f1.TransactionEffDate <= '2016-07-01 00:00:00.000' AND
isnull(f1.TransactionExpDate, dateadd(day, 1, '2016-07-01 00:00:00.000')) > '2016-07-01 00:00:00.000'
WHERE c1.AccountNumber = '0105672985' AND
c1.PolicyNumber = '0007412326' AND
c1.AccidentDate >= '1990-01-01 00:00:00.000' AND
c1.AccidentDate < '1991-01-01 00:00:00.000'
AND isnull(c1.AdjustmentStatus, 'O') != 'S'
AND c1.AccountNumber = A.AccountNumber AND
c1.PolicyNumber = A.PolicyNumberAND
c1.PolicyEffDate = A.PolicyEffectiveDate AND
c1.LOBId = A.LOBId AND c1.LOCId = A.LOCId AND
c1.InterestedState = A.StateCode AND
c1.AdjustmentType = ( SELECT min(a2.AdjustmentType)
FROM CodeChangesdata AS a2
WHERE a2.CodingNumber = c1.CodingNumberAND
a2.Suffix = c1.Suffix AND
a2.CreationDateTime <= '2016-07-29 18:29:00' AND
a2.TransactionEffDate <= '2016-07-01 00:00:00.000'AND
isnull(a2.TransactionExpDate, dateadd(day, 1, '2016-07-01 00:00:00.000')) > '2016-07-01 00:00:00.000' )AND
c1.TransactionEffDate =( SELECT max(a2.TransactionEffDate) FROM CodeChangesdata AS a2
WHERE a2.CodingNumber = c1.CodingNumber AND
a2.Suffix = c1.Suffix AND
a2.CreationDateTime <= '2016-07-29 18:29:00' AND
a2.TransactionEffDate <= '2016-07-01 00:00:00.000' AND
isnull(a2.TransactionExpDate, dateadd(day, 1, '2016-07-01 00:00:00.000')) > '2016-07-01 00:00:00.000' AND
a2.AdjustmentType = c1.AdjustmentType ) AND
c1.CreationDateTime = ( SELECT max(a2.CreationDateTime)
FROM CodeChangesdata AS a2
WHERE a2.CodingNumber = c1.CodingNumber AND
a2.Suffix = c1.Suffix AND
a2.CreationDateTime <= '2016-07-29 18:29:00' AND
a2.TransactionEffDate = c1.TransactionEffDate AND
isnull(a2.TransactionExpDate, dateadd(day, 1, '2016-07-01 00:00:00.000')) > '2016-07-01 00:00:00.000' AND
a2.AdjustmentType = c1.AdjustmentType )
GROUP BY c1.CodingNumber,c1.Suffix,c1.TransactionEffDate,c1.CreationDateTime,f1.AmountTypeCode
But the part i could not understand anything is where it selects the below three columns using
select (min) and select (Max) function.
1. transaction effective date
2. creation date and time
3. adjustment type.
I dont understand what is the value passed in to the query from the subquery Select (Max) or Select (Min).
And also in there where clause for selecting the above three columns it is using the same
CodeChangesdate table on the right hand side and left hand side !!!!
a2.Suffix = c1.Suffix . Both a2 and c1 are CodeChangesdata table.
I tried running those sub queries separately to understand how it selects the transaction eff date as below
SELECT max(TransactionEffDate) FROM CodeChangesdata
WHERE a2.CodingNumber = c1.CodingNumber AND
a2.Suffix = c1.Suffix AND
a2.CreationDateTime <= '2016-07-29 18:29:00' AND
a2.TransactionEffDate <= '2016-07-01 00:00:00.000' AND
isnull(a2.TransactionExpDate, dateadd(day, 1, '2016-07-01 00:00:00.000')) > '2016-07-01 00:00:00.000' AND
a2.AdjustmentType = c1.AdjustmentType )
But this query runs forever.
Could you please help me in understanding at a high level on how this query works and what it is trying to pull.
Thanks in Advance !!!!
July 30, 2016 at 4:54 pm
The subquery is a correlated subquery. It's attempting to get the min, or max, value for the AdjustmentType and using that to limit the results. It looks like you are trying to get the earliest adjustmenttype (in value), the latest transaction date, and the most recent transaction created.
This is easier to understand with a small table. If I have a little table like this:
CREATE TABLE Schedule
(
myid INT
, CREATEdate DATETIME2
, updatedate DATETIME2
, customerid int
, status int
)
GO
INSERT dbo.Schedule
( myid
, CREATEdate
, updatedate
, customerid
, status
)
VALUES ( 1, '20160101', '20160201', 1, 22)
, ( 2, '20160102', '20160202', 1, 50)
, ( 3, '20160103', '20160203', 2, 86)
GO
When I run this query, what I'm doing is making two instances of the Schedule table (in essence). The first one is where I'm selecting data from a. However, I can't do that, because I need to find the rows in a that match a particular row in b. In my case, I'm ensuring the customer in a matches the customer in b, and that I'm taking the earliest (in time) date with MIN().
SELECT
*
FROM dbo.Schedule a
WHERE customerid = 1
AND a.CREATEdate = (SELECT MIN(b.CREATEdate)
FROM schedule b
WHERE a.customerid = b.customerid
)
I'm linking these two tables together, even though they're the same table, they're two separate, identical copies, for the purposes of this query.
Also, the query you have is badly formed. This part
FROM #tmpAbstract AS A,CodeChangesdata AS c1
Should not be constructed with this syntax. Really you should have this:
FROM #tmpAbstract AS A
inner join CodeChangesdata AS c1
on c1.AccountNumber = A.AccountNumber AND
c1.PolicyNumber = A.PolicyNumberAND
c1.PolicyEffDate = A.PolicyEffectiveDate
To check the subqueries, you'd need to replace the a.xx in the subquery with a particular value to see how the subquery returns data.
July 30, 2016 at 6:12 pm
Hi Steve ,
Thanks very much for detailed and clear explanation 🙂
To check the subqueries, you'd need to replace the a.xx in the subquery with a particular value to see how the subquery returns data.
So Just to be sure that i understood properly, I need to write like below right ?
SELECT MIN(b.CREATEdate)
FROM schedule b
WHERE b.customerid=1
)
Am i right Steve ?
And also for the below quote
Also, the query you have is badly formed. This part
FROM #tmpAbstract AS A,CodeChangesdata AS c1
Could you please tell me why you are saying it is badly formed, I mean will it cause any performance issues
or query itself is wrong because this query is running in my application for a long time .
Thanks in Advance !!!
July 30, 2016 at 6:52 pm
yes, your query is correct. Take out the subquery, like this:
( SELECT max(a2.TransactionEffDate)
FROM CodeChangesdata AS a2
WHERE a2.CodingNumber = 5
AND a2.Suffix = c1.Suffix
AND a2.CreationDateTime <= '2016-07-29 18:29:00'
AND a2.TransactionEffDate <= '2016-07-01 00:00:00.000'
AND isnull(a2.TransactionExpDate, dateadd(day, 1, '2016-07-01 00:00:00.000')) > '2016-07-01 00:00:00.000'
AND a2.AdjustmentType = 1
)
Note, I reformatted. Having consistent formatting is good. You can put the ANDs at the end, but I find that harder to read and easy to assume you have ANDs, when you might have an OR in there.
As far as the JOIN construct. The old way of joining was with table, table, and then something in the WHERE clause. However, this is easy to forget a join you need between tables, especially when you may have 2. Instead, the better way, which may be required at some point in SQL Server, is to ALWAYS specify the join, and use the ON clause to include the criteria needed for the join.
Good luck.
Keep posting if you're stuck
July 30, 2016 at 7:41 pm
Thanks Very Much Steve !!!
Understood 🙂
August 1, 2016 at 8:13 am
You are welcome. Hope you solve it. Let us know what works.
August 1, 2016 at 8:41 am
Often simply formatting a query nicely is sufficient to be able to figure out what's going on. The Simple Talk prettifier will get you most of the way there. I've followed Steve's suggestion of using JOIN syntax for table joins:
SELECT
c1.CodingNumber,
c1.Suffix,
c1.TransactionEffDate,
c1.CreationDateTime,
f1.AmountTypeCode,
SUM(COALESCE(f1.Amount,0)) AS Amount
FROM #tmpAbstract AS A
INNER JOIN CodeChangesdata AS c1
ON c1.AccountNumber = A.AccountNumber
AND c1.PolicyNumber = A.PolicyNumber
AND c1.PolicyEffDate = A.PolicyEffectiveDate
AND c1.LOBId = A.LOBId
AND c1.LOCId = A.LOCId
AND c1.InterestedState = A.StateCode
LEFT OUTER JOIN FinancialChangesData AS f1
ON c1.CodingNumber = f1.CodingNumber
AND c1.Suffix = f1.Suffix
AND f1.TransactionEffDate <= '2016-07-01 00:00:00.000'
AND ISNULL(f1.TransactionExpDate, DATEADD(DAY, 1, '2016-07-01 00:00:00.000')) > '2016-07-01 00:00:00.000'
WHERE c1.AccountNumber = '0105672985'
AND c1.PolicyNumber = '0007412326'
AND c1.AccidentDate >= '1990-01-01 00:00:00.000'
AND c1.AccidentDate < '1991-01-01 00:00:00.000'
AND ISNULL(c1.AdjustmentStatus, 'O') != 'S'
AND c1.AdjustmentType = (
SELECT MIN(a2.AdjustmentType)
FROM CodeChangesdata AS a2
WHERE a2.CodingNumber = c1.CodingNumber
AND a2.Suffix = c1.Suffix
AND a2.CreationDateTime <= '2016-07-29 18:29:00'
AND a2.TransactionEffDate <= '2016-07-01 00:00:00.000'
AND ISNULL(a2.TransactionExpDate, DATEADD(DAY, 1, '2016-07-01 00:00:00.000')) > '2016-07-01 00:00:00.000'
)
AND c1.TransactionEffDate = (
SELECT MAX(a2.TransactionEffDate)
FROM CodeChangesdata AS a2
WHERE a2.CodingNumber = c1.CodingNumber
AND a2.Suffix = c1.Suffix
AND a2.CreationDateTime <= '2016-07-29 18:29:00'
AND a2.TransactionEffDate <= '2016-07-01 00:00:00.000'
AND ISNULL(a2.TransactionExpDate, DATEADD(DAY, 1, '2016-07-01 00:00:00.000')) > '2016-07-01 00:00:00.000'
AND a2.AdjustmentType = c1.AdjustmentType
)
AND c1.CreationDateTime = (
SELECT MAX(a2.CreationDateTime)
FROM CodeChangesdata AS a2
WHERE a2.CodingNumber = c1.CodingNumber
AND a2.Suffix = c1.Suffix
AND a2.CreationDateTime <= '2016-07-29 18:29:00'
AND a2.TransactionEffDate = c1.TransactionEffDate
AND ISNULL(a2.TransactionExpDate, DATEADD(DAY, 1, '2016-07-01 00:00:00.000')) > '2016-07-01 00:00:00.000'
AND a2.AdjustmentType = c1.AdjustmentType
)
GROUP BY c1.CodingNumber,c1.Suffix,c1.TransactionEffDate,c1.CreationDateTime,f1.AmountTypeCode
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply