Join aggregated queries via a date formatted YYYY-MM

Question

Join aggregated queries via a date formatted YYYY-MM

saintor1

Mr or Mrs. 500

Points: 562
More actions
January 17, 2023 at 7:52 pm
Go to Answer
#4138735
I have the following that is supposed to do- what the title says.
```
SELECT [W_SE-MFG].dbo.YearMonthNo(J1.EntryDate) AS Per, SUM(J1.RunTime) AS TReg, J2.TNQ
FROM Db1.dbo.WipLabJnl J1
LEFT JOIN 
(SELECT [W_SE-MFG].dbo.YearMonthNo(EntryDate) AS Per, SUM(RunTime) AS TNQ
FROM Db1.dbo.WipLabJnl 
WHERE WorkCentre LIKE '%NQ'
GROUP BY [W_SE-MFG].dbo.YearMonthNo(EntryDate)) J2 ON J2.Per=Per

WHERE J1.EntryDate>'2019-01-01'
GROUP BY db2.dbo.YearMonthNo(J1.EntryDate), J2.Per, J2.TNQ
```
When I run it, it executes way too long and I stop it after 60s. Run separately, they work fine in an instant.
YearMonthNo is a scalar function that will convert a date to YYYY-MM (MM being from 01 to 12). J2 data is all past 2019-01-01.
What I want to accomplish is from a period YYYY-MM, to get the sum of time in the first dataset and in the second dataset. What am I doing wrong?

kaj

SSCommitted

Points: 1607
More actions
January 19, 2023 at 12:14 am
Answer
#4139265
Michael L John wrote:
kaj wrote:
Not sure if I have got the logic right, but please try this query:
SELECT CONVERT(CHAR(7),EOMONTH(J1.EntryDate)) AS Per, SUM(J1.RunTime) AS TReg, J2.TNQ
FROM Db1.dbo.WipLabJnl J1
OUTER APPLY (
SELECT SUM(RunTime) AS TNQ
FROM Db1.dbo.WipLabJnl j
WHERE WorkCentre LIKE '%NQ'
AND j1.EntryDate BETWEEN DATEADD(d,-DAY(j.EntryDate)+1,j.EntryDate) AND EOMONTH(j.EntryDate)
) j2

WHERE J1.EntryDate>'2019-01-01'
GROUP BY EOMONTH(J1.EntryDate), J2.TNQ
Your query has a non-sargable where clause. The use DATEADD and EOMONTH will almost certainly cause a table scan
I'm aware that it isn't perfect. 🙂
But I don't think it'll perform worse than all the other published queries, although that is hard to prove without some test data.
Like the rest of the published queries, it's effectively an outer self-join on year/month, even if I use an outer apply for it.

Viewing 15 posts - 1 through 14 (of 14 total)

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

ratbak Ten Centuries Points: 1382 More actions · Answer 1

You are using a function in a predicate (e.g., where clause), rendering it non-sargable (can't use index). The following should probably be sargable, & perform better (assuming you have appropriate indexes).

If you really need to format "Per", you could wrap this using the function to format "Per".

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, J1.EntryDate), 0) AS Per, SUM(J1.RunTime) AS TReg, J2.TNQ
FROM Db1.dbo.WipLabJnl J1
LEFT JOIN 
    (SELECT  DATEADD(MONTH, DATEDIFF(MONTH, 0, WipLabJnl.EntryDate), 0) AS Per, SUM(RunTime) AS TNQ
    FROM Db1.dbo.WipLabJnl 
    WHERE WorkCentre LIKE '%NQ'
    GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, WipLabJnl.EntryDate), 0) ) J2 ON J2.Per=Per
WHERE J1.EntryDate>'2019-01-01'
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, J1.EntryDate), 0), J2.Per, J2.TNQ

ScottPletcher SSC Guru Points: 101126 More actions · Answer 2

First try getting rid of extraneous parts of the query (reducing the GROUP BY in the outer query):

SELECT [W_SE-MFG].dbo.YearMonthNo(J1.EntryDate) AS Per, SUM(J1.RunTime) AS TReg, MAX(J2.TNQ) AS TNQ
FROM Db1.dbo.WipLabJnl J1
LEFT JOIN 
(SELECT [W_SE-MFG].dbo.YearMonthNo(EntryDate) AS Per, SUM(RunTime) AS TNQ
FROM Db1.dbo.WipLabJnl 
WHERE WorkCentre LIKE '%NQ'
GROUP BY [W_SE-MFG].dbo.YearMonthNo(EntryDate)) J2 ON J2.Per=J1.Per

WHERE J1.EntryDate>'2019-01-01'
GROUP BY db2.dbo.YearMonthNo(J1.EntryDate)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

saintor1 Mr or Mrs. 500 Points: 562 More actions · Answer 3

I tried both of your suggestion but I still didn't get the desired results. For the first one, I get something like

....

2019-08 3853.480000 30.624526

2019-08 3853.480000 3.246816

...

Maybe adding a CTE level and grouping again?

Michael L John One Orange Chip Points: 27060 More actions · Answer 4

saintor1 wrote:

I tried both of your suggestion but I still didn't get the desired results. For the first one, I get something like
....
2019-08 3853.480000 30.624526 2019-08 3853.480000 3.246816
...
Maybe adding a CTE level and grouping again?

You are guessing. Use the facts that are available to you.

Can you post the ACTUAL execution plan? Are there missing indexes? Are there implicit conversions?

This may be hurting performance WHERE WorkCentre LIKE '%NQ'

Can you post the table structure, and the code within the function?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Michael L John One Orange Chip Points: 27060 More actions · Answer 5

ratbak wrote:

You are using a function in a predicate (e.g., where clause), rendering it non-sargable (can't use index).

Hmmm. Not that I can see.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Steve Jones - SSC Editor SSC Guru Points: 736504 More actions · Answer 6

Michael has some good ideas, but what I'd do is not run this on a huge table. Get 20-30 rows and work out your logic and query. Then look at the query plan and see if you have a large number of logical reads relative to the number of rows. Are indexes being used?

With 20-30 rows you can test whether the results are correct.

kaj SSCommitted Points: 1607 More actions · Answer 7

Not sure if I have got the logic right, but please try this query:

SELECT CONVERT(CHAR(7),EOMONTH(J1.EntryDate)) AS Per, SUM(J1.RunTime) AS TReg, J2.TNQ
FROM Db1.dbo.WipLabJnl J1
OUTER APPLY (
    SELECT SUM(RunTime) AS TNQ
    FROM Db1.dbo.WipLabJnl j
    WHERE WorkCentre LIKE '%NQ' 
    AND j1.EntryDate BETWEEN DATEADD(d,-DAY(j.EntryDate)+1,j.EntryDate) AND EOMONTH(j.EntryDate)
    ) j2

WHERE J1.EntryDate>'2019-01-01'
GROUP BY EOMONTH(J1.EntryDate), J2.TNQ

kaj SSCommitted Points: 1607 More actions · Answer 8

Not sure if I have got the logic right, but please try this query:

SELECT CONVERT(CHAR(7),EOMONTH(J1.EntryDate)) AS Per, SUM(J1.RunTime) AS TReg, J2.TNQ
FROM Db1.dbo.WipLabJnl J1
OUTER APPLY (
    SELECT SUM(RunTime) AS TNQ
    FROM Db1.dbo.WipLabJnl j
    WHERE WorkCentre LIKE '%NQ' 
    AND j1.EntryDate BETWEEN DATEADD(d,-DAY(j.EntryDate)+1,j.EntryDate) AND EOMONTH(j.EntryDate)
    ) j2

WHERE J1.EntryDate>'2019-01-01'
GROUP BY EOMONTH(J1.EntryDate), J2.TNQ

Michael L John One Orange Chip Points: 27060 More actions · Answer 9

kaj wrote:

Not sure if I have got the logic right, but please try this query:

SELECT CONVERT(CHAR(7),EOMONTH(J1.EntryDate)) AS Per, SUM(J1.RunTime) AS TReg, J2.TNQ
FROM Db1.dbo.WipLabJnl J1
OUTER APPLY (
    SELECT SUM(RunTime) AS TNQ
    FROM Db1.dbo.WipLabJnl j
    WHERE WorkCentre LIKE '%NQ' 
    AND j1.EntryDate BETWEEN DATEADD(d,-DAY(j.EntryDate)+1,j.EntryDate) AND EOMONTH(j.EntryDate)
    ) j2

WHERE J1.EntryDate>'2019-01-01'
GROUP BY EOMONTH(J1.EntryDate), J2.TNQ

Your query has a non-sargable where clause. The use DATEADD and EOMONTH will almost certainly cause a table scan

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Jonathan AC Roberts SSCoach Points: 18377 More actions · Answer 10

I think this is a more straight forward way of doing it:

;WITH J1 AS
(
    SELECT [W_SE-MFG].dbo.YearMonthNo(J1.EntryDate) AS Per, 
           SUM(J1.RunTime) AS TReg, 
      FROM Db1.dbo.WipLabJnl J1
     WHERE J1.EntryDate>'2019-01-01'
     GROUP BY [W_SE-MFG].dbo.YearMonthNo(J1.EntryDate)
),
J2 AS
(
    SELECT [W_SE-MFG].dbo.YearMonthNo(J2.EntryDate) AS Per, 
           SUM(J2.RunTime) AS TNQ
      FROM Db1.dbo.WipLabJnl J2
     WHERE J2.WorkCentre LIKE '%NQ'
       AND J2.EntryDate>'2019-01-01'
     GROUP BY [W_SE-MFG].dbo.YearMonthNo(J2.EntryDate)
)
SELECT ISNULL(J1.Per, J2.Per) Per,
       J1.TReg,
       J2.TNQ
  FROM J1
  FULL JOIN J2
         ON J2.Per = J1.Per;

Jonathan AC Roberts SSCoach Points: 18377 More actions · Answer 11

Actually I think this might do it:

SELECT [W_SE-MFG].dbo.YearMonthNo(J.EntryDate) AS Per, 
       SUM(J.RunTime) AS TReg, 
       SUM(CASE WHEN J.WorkCentre LIKE '%NQ' THEN J.RunTime ELSE NULL END) TNQ
  FROM Db1.dbo.WipLabJnl J
 WHERE J.EntryDate > '2019-01-01'
 GROUP BY [W_SE-MFG].dbo.YearMonthNo(J.EntryDate);

saintor1 Mr or Mrs. 500 Points: 562 More actions · Answer 12

Suggestions from kaj and Jonathan AC Roberts (both) work! Thanks to for spending a few minutes to solve this. I didn't know about the EOMONTH() function.

This reply was modified 1 year, 11 months ago by saintor1.

Jeff Moden SSC Guru Points: 1004415 More actions · Answer 13

saintor1 wrote:

Suggestions from kaj and Jonathan AC Roberts (both) work! Thanks to for spending a few minutes to solve this. I didn't know about the EOMONTH() function.

If your dates have times in the future (or now... you never told us what the datatype for the EntryDate column is and I won't assume that it doesn't contain a time), you will learn the hard way to avoid EOMONTH() like the plague. Why Microsoft didn't come out with an FOMONTH() function to do month ranges properly, I'll never know. The finally "fixed" this issue in 2022 with the new DATE_TRUNC function. I've not tried it for performance, yet.

EOMONTH() misses all but the very first instant of the last day of the month if time is inclued.

Also, you have a WHERE clause with a leading wildcard. That means it'll never do an INDEX SEEK for performance.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)