March 9, 2015 at 10:35 pm
Please help with the following query:
declare @fromDate datetime
declare @toDate datetime
set @fromDate = '2015-3-1 0:0:0'
set @toDate = '2015-3-9 21:43:00'
SELECT SUM(ALERTS) as TOTAL_ALERT
FROM
(SELECT COUNT(MessageId) ALERTS
FROM DeliveryMessage with (nolock)
--WHERE CreatedOn > DATEADD(DAY,@WEEKLY,GETUTCDATE())
WHERE CreatedOn > @fromDate and CreatedOn <= @toDate
UNION
SELECT COUNT(MessageId) ALERTS FROM DeliveryMessageArchive
WHERE CreatedOn > @fromDate and CreatedOn <= @toDate) a
SELECT SUM(NOTIFICATIONS) as TOTAL_NOTIFICATIONS
FROM
(SELECT COUNT(TrackId) NOTIFICATIONS FROM DeliveryTrack with (nolock)
WHERE TrackTime > @fromDate and CreatedOn <= @toDate AND TrackType='SENT'
UNION
SELECT COUNT(TrackId) NOTIFICATIONS FROM DeliveryTrackArchive
WHERE TrackTime > @fromDate and CreatedOn <= @toDate AND TrackType='SENT') a
GO
SELECT SUM(ALERTS) as MONTHLY_TOTAL_ALERT
FROM
(SELECT COUNT(MessageId) ALERTS FROM DeliveryMessage with (nolock)
WHERE MONTH(CreatedOn) =MONTH(GETUTCDATE())
UNION
SELECT COUNT(MessageId) ALERTS FROM DeliveryMessageArchive
WHERE MONTH(CreatedOn) =MONTH(GETUTCDATE())) a
SELECT SUM(NOTIFICATIONS) as MONTHLY_TOTAL_NOTIFICATIONS
FROM
(SELECT COUNT(TrackId) NOTIFICATIONS FROM DeliveryTrack with (nolock)
WHERE MONTH(TrackTime)=MONTH(GETUTCDATE()) AND TrackType='SENT'
UNION
SELECT COUNT(TrackId) NOTIFICATIONS FROM DeliveryTrackArchive
WHERE MONTH(TrackTime)=MONTH(GETUTCDATE()) AND TrackType='SENT') a
March 10, 2015 at 12:37 am
Srl832 (3/9/2015)
Please help with the following query:declare @fromDate datetime
declare @toDate datetime
set @fromDate = '2015-3-1 0:0:0'
set @toDate = '2015-3-9 21:43:00'
SELECT SUM(ALERTS) as TOTAL_ALERT
FROM
(SELECT COUNT(MessageId) ALERTS
FROM DeliveryMessage with (nolock)
--WHERE CreatedOn > DATEADD(DAY,@WEEKLY,GETUTCDATE())
WHERE CreatedOn > @fromDate and CreatedOn <= @toDate
UNION
SELECT COUNT(MessageId) ALERTS FROM DeliveryMessageArchive
WHERE CreatedOn > @fromDate and CreatedOn <= @toDate) a
SELECT SUM(NOTIFICATIONS) as TOTAL_NOTIFICATIONS
FROM
(SELECT COUNT(TrackId) NOTIFICATIONS FROM DeliveryTrack with (nolock)
WHERE TrackTime > @fromDate and CreatedOn <= @toDate AND TrackType='SENT'
UNION
SELECT COUNT(TrackId) NOTIFICATIONS FROM DeliveryTrackArchive
WHERE TrackTime > @fromDate and CreatedOn <= @toDate AND TrackType='SENT') a
GO
SELECT SUM(ALERTS) as MONTHLY_TOTAL_ALERT
FROM
(SELECT COUNT(MessageId) ALERTS FROM DeliveryMessage with (nolock)
WHERE MONTH(CreatedOn) =MONTH(GETUTCDATE())
UNION
SELECT COUNT(MessageId) ALERTS FROM DeliveryMessageArchive
WHERE MONTH(CreatedOn) =MONTH(GETUTCDATE())) a
SELECT SUM(NOTIFICATIONS) as MONTHLY_TOTAL_NOTIFICATIONS
FROM
(SELECT COUNT(TrackId) NOTIFICATIONS FROM DeliveryTrack with (nolock)
WHERE MONTH(TrackTime)=MONTH(GETUTCDATE()) AND TrackType='SENT'
UNION
SELECT COUNT(TrackId) NOTIFICATIONS FROM DeliveryTrackArchive
WHERE MONTH(TrackTime)=MONTH(GETUTCDATE()) AND TrackType='SENT') a
Perhaps you could help us out here a little by first telling us what you think is wrong.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 10, 2015 at 12:51 am
I do not think I am getting right results. I compared the results from the top and bottom half of the query by changing the @fromDate to 3/1/2015 0:0:0 and @toDate to the current date and time. It should be giving me the same result as the bottom query, which counts the monthly total alerts and notifications. However, the results these 2 queries provide are not the same. I am wondering if I missed or overlooked something.
I am beginning to write queries and this was not written by me. It was written by one of my coworkers. The query originally gave out the number of alerts and notifications being fired on a weekly and monthly basis. However, I wanted to to see the number of alerts and notifications for a specific time frame and tried modifying it accordingly, although I am not sure if I got it right.
Could you take a look at it and tell me if this will provide me what I want?
March 10, 2015 at 1:04 am
Srl832 (3/10/2015)
I do not think I am getting right results. I compared the results from the top and bottom half of the query by changing the @fromDate to 3/1/2015 0:0:0 and @toDate to the current date and time. It should be giving me the same result as the bottom query, which counts the monthly total alerts and notifications. However, the results these 2 queries provide are not the same. I am wondering if I missed or overlooked something.I am beginning to write queries and this was not written by me. It was written by one of my coworkers. The query originally gave out the number of alerts and notifications being fired on a weekly and monthly basis. However, I wanted to to see the number of alerts and notifications for a specific time frame and tried modifying it accordingly, although I am not sure if I got it right.
Could you take a look at it and tell me if this will provide me what I want?
That's a start. Now how about some DDL to create the referenced tables and some consumable sample data to populate them with. Then the helpers donating their time to those in need have something to work with in diagnosing what the issue is.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 10, 2015 at 3:07 am
Srl832 (3/10/2015)
I do not think I am getting right results.
Well you can start by removing the nolock hints, since they allow SQL to return incorrect results.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 10, 2015 at 3:18 am
Just on the first look I noticed that you have
MONTH(CreatedOn) =MONTH(GETUTCDATE())
which will return all rows created in March in any year, not only in 2015 as you wanted (according to the input date parameters).
But even if this would be answer to your question please read carefully previous comments and for the future request provide us some details and DDL statements with sample data. Also take very seriously comment about NOLOCK. Your query is an example where wrong results caused by NOLOCK would not be a surprise.
___________________________
Do Not Optimize for Exceptions!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply