Help with date range subquery

Question

Post reply

Help with date range subquery

ThePhoenix

SSC Veteran

Points: 285
More actions
March 20, 2024 at 5:19 pm
Go to Answer
#4398975
I have two large tables. One table(Table A) contains trouble tickets that are created when applications fail. The other table(Table B) contains warning notifications that are emailed whenever there may be an issue with an application. I need to write an efficient query that will select fields from Table A where a NotificationDate (Table B) is not between the TicketOpenDate(Table A) minus an hour and TicketOpenDate (Table A) plus an hour. Basically trying to see if there wasn't any notification messages sent an hour before or after the trouble ticket was created. The tables are joined using the AppID column. Can someone assist with creating an efficient way to pull the data from two large tables?

Table A
Ticket
TicketID
AppID
AppName
TicketOpenDate
Table B
WarningNotifications
NotificationID
NotificationDate
AppID
- This topic was modified 8 months, 1 week ago by ThePhoenix.
- This topic was modified 8 months, 1 week ago by ThePhoenix.

Jonathan AC Roberts

SSCoach

Points: 18354

March 20, 2024 at 7:12 pm

Answer

#4399025

SELECT *
  FROM TableA A
 WHERE NOT EXISTS(SELECT *
                    FROM TableB B
                   WHERE B.AppId = A.AppId
                     AND B.NotificationDate BETWEEN DATEADD(hh, -1, A.TicketOpenDate) AND DATEADD(hh, 1, A.TicketOpenDate))
;

Viewing 9 posts - 1 through 8 (of 8 total)

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Is there any additional way of limiting TicketOpenDate?

Eg, are you looking for only those open in the last week or month, rather than going back over all time?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ScottPletcher SSC Guru Points: 101098 More actions · Answer 2

Agree with Phil, that's a critical q.

Also, efficiency will depend on the indexes on the tables. You'll need to be able to review indexes.

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".

ThePhoenix SSC Veteran Points: 285 More actions · Answer 3

No, I won't be limiting the TicketOpenDate for the query. I may add a month filter to limit TicketOpenDate at a later time. The initial query will look at all data to see how many notifications have not been sent for all tickets. Also, I'm not sure if Not in, not exists, or joins would be the best approach for this type of query.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

ThePhoenix wrote:

No, I won't be limiting the TicketOpenDate for the query. I may add a month filter to limit TicketOpenDate at a later time. The initial query will look at all data to see how many notifications have not been sent for all tickets. Also, I'm not sure if Not in, not exists, or joins would be the best approach for this type of query.

Since the history in this table won't be changing, I recommend that you keep the aggregates for the old data in a separate table so that you only have to calculate aggregates for the newer stuff.

--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)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

p.s. Read the article at the first link in my signature line below for how to post some readily consumable test data. It'll help you get a coded answer instead of just rhetorical suggestions.

--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)

ScottPletcher SSC Guru Points: 101098 More actions · Answer 6

Typically EXISTS / NOT EXISTS is most efficient when it's a viable option.

You'll want to have a (nonclus) index on B ( AppId, NotificationDate ). If the table already has a $IDENTITY column, you could add that last to the index above -- i.e. ( AppId, NotificationDate, $IDENTITY ) -- so that you can CREATE UNIQUE NONCLUSTERED INDEX rather than forcing SQL itself to make the keys unique.

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".

ThePhoenix SSC Veteran Points: 285 More actions · Answer 7

Thanks for the example. The NOT EXISTS query is much faster than what I was trying with NOT IN.