asked on

Data accuracy at two Daily and Monthly Reports (UTC time issue )

Hello everyone,

I am trying to create two reports and I have two query for that and I am using MS Reporting Services 2005. My system time -4 hours behind the SQL Time. ( UTC)
My first query called daily, It is counting rows based on where statement and I get total count
second query is monthly it is counting rows based on where statement and I get total counts per day.

Both Report time is 4/27/2009 12:00:00 AM to 4/27/2009 11:59:00 PM

Problem is I am getting different counts from daily and monthly reports, for example I get 591 total counts from my daily query and I get 512 and it is over flowing to next day. from my monthly query I assume some UTC time issue but I could not figure out. I should get same value from both reports.

Please take a look Reporting Services Results page I attached daily and monthly.

Daily Query;
 
SELECT TimeFromRaisedSeconds / 60 AS Minutes, COUNT(*) AS Counts
FROM Alert.vAlertResolutionState
WHERE (ResolutionState = 255) AND (StateSetDateTime BETWEEN DATEADD(HH, DATEDIFF(HH, GETDATE(), getutcdate()), @startdate) AND DATEADD(HH, DATEDIFF(HH, GETDATE(), getutcdate()), @enddate))
GROUP BY TimeFromRaisedSeconds / 60
ORDER BY Minutes
 
 
Monthly Query;
 
 
SELECT AddDate=dateadd(day,datediff(Day,0,StateSetDateTime),0),
COUNT (CASE WHEN TimeFromRaisedSeconds / 60 = 0 THEN 1 ELSE NULL END) AS [0 Min],
COUNT (CASE WHEN TimeFromRaisedSeconds / 60 = 1 THEN 1 ELSE NULL END) AS [1 Min],
COUNT (CASE WHEN TimeFromRaisedSeconds / 60 = 2 THEN 1 ELSE NULL END) AS [2 Min],
COUNT (CASE WHEN TimeFromRaisedSeconds / 60 = 3 THEN 1 ELSE NULL END) AS [3 Min],
COUNT (CASE WHEN TimeFromRaisedSeconds / 60 > 3 THEN 1 ELSE NULL END) AS [4+ Min],
COUNT (CASE WHEN TimeFromRaisedSeconds / 60 <= 100000 THEN 1 ELSE NULL END) AS [Total]
FROM Alert.vAlertResolutionState
WHERE ResolutionState = 255 AND (StateSetDateTime BETWEEN DATEADD(HH, DATEDIFF(HH, GETDATE(), getutcdate()), @startdate) AND DATEADD(HH, DATEDIFF(HH, GETDATE(), getutcdate()), @enddate))
Group by dateadd(Day,datediff(Day,0,StateSetDateTime),0)

Open in new window

daily-Report.JPG
montly-report.JPG

ezraa

I think the difference is how you are displaying the date.

When you are filtering the records you use the getutcdate() but in the monthly query you::

SELECT AddDate=dateadd(day,datediff(Day,0,StateSetDateTime),0)

which doesn't use the UTC date, and therefor is spliting the results to two lines.

You can possible use this instead, but I can't test this as I don't have your database:

Monthly Query;
 
 
SELECT AddDate=DATEADD(HH, DATEDIFF(HH, GETDATE(), getutcdate()), StateSetDateTime),
COUNT (CASE WHEN TimeFromRaisedSeconds / 60 = 0 THEN 1 ELSE NULL END) AS [0 Min],
COUNT (CASE WHEN TimeFromRaisedSeconds / 60 = 1 THEN 1 ELSE NULL END) AS [1 Min],
COUNT (CASE WHEN TimeFromRaisedSeconds / 60 = 2 THEN 1 ELSE NULL END) AS [2 Min],
COUNT (CASE WHEN TimeFromRaisedSeconds / 60 = 3 THEN 1 ELSE NULL END) AS [3 Min],
COUNT (CASE WHEN TimeFromRaisedSeconds / 60 > 3 THEN 1 ELSE NULL END) AS [4+ Min],
COUNT (CASE WHEN TimeFromRaisedSeconds / 60 <= 100000 THEN 1 ELSE NULL END) AS [Total]
FROM Alert.vAlertResolutionState
WHERE ResolutionState = 255 AND (StateSetDateTime BETWEEN DATEADD(HH, DATEDIFF(HH, GETDATE(), getutcdate()), @startdate) AND DATEADD(HH, DATEDIFF(HH, GETDATE(), getutcdate()), @enddate))
Group by DATEADD(HH, DATEDIFF(HH, GETDATE(), getutcdate()), StateSetDateTime)

Open in new window

Unisys1

ASKER

I have tried this before, but I don`t want to grouping by hourly (HH) in your query you are grouping by hour because your select statement by hour (HH)

ezraa

Sorry I was unclear. The point I was trying to make was that in the monthly query you have these 2 parts:

1) dateadd(Day,datediff(Day,0,StateSetDateTime),0)
2) WHERE ResolutionState = 255 AND (StateSetDateTime BETWEEN DATEADD(HH, DATEDIFF(HH, GETDATE(), getutcdate()), @startdate) AND DATEADD(HH, DATEDIFF(HH, GETDATE(), getutcdate()), @enddate))

the first part (1) is not using UTC and the second part (2) is using UTC, so in the results it appears that the following day is showing up.

Daily Query;
 
SELECT TimeFromRaisedSeconds / 60 AS Minutes, COUNT(*) AS Counts
FROM Alert.vAlertResolutionState
WHERE (ResolutionState = 255) AND (StateSetDateTime BETWEEN DATEADD(HH, DATEDIFF(HH, GETDATE(), getutcdate()), @startdate) AND DATEADD(HH, DATEDIFF(HH, GETDATE(), getutcdate()), @enddate))
GROUP BY TimeFromRaisedSeconds / 60
ORDER BY Minutes
 
 
Monthly Query;
 
 
SELECT AddDate=dateadd(Day,datediff(Day,0,DATEADD(HH, DATEDIFF(HH, GETDATE(), getutcdate()),StateSetDateTime)),0),
COUNT (CASE WHEN TimeFromRaisedSeconds / 60 = 0 THEN 1 ELSE NULL END) AS [0 Min],
COUNT (CASE WHEN TimeFromRaisedSeconds / 60 = 1 THEN 1 ELSE NULL END) AS [1 Min],
COUNT (CASE WHEN TimeFromRaisedSeconds / 60 = 2 THEN 1 ELSE NULL END) AS [2 Min],
COUNT (CASE WHEN TimeFromRaisedSeconds / 60 = 3 THEN 1 ELSE NULL END) AS [3 Min],
COUNT (CASE WHEN TimeFromRaisedSeconds / 60 > 3 THEN 1 ELSE NULL END) AS [4+ Min],
COUNT (CASE WHEN TimeFromRaisedSeconds / 60 <= 100000 THEN 1 ELSE NULL END) AS [Total]
FROM Alert.vAlertResolutionState
WHERE ResolutionState = 255 AND (StateSetDateTime BETWEEN DATEADD(HH, DATEDIFF(HH, GETDATE(), getutcdate()), @startdate) AND DATEADD(HH, DATEDIFF(HH, GETDATE(), getutcdate()), @enddate))
Group by dateadd(Day,datediff(Day,0,DATEADD(HH, DATEDIFF(HH, GETDATE(), getutcdate()),StateSetDateTime)),0)

Open in new window

Unisys1

ASKER

unfortunately this returned less value then previous one unfortunately no luck so far.

Chris Luttrell

You all are on the right track but he missed that you were turning the date time into just the Date portion for AddDate, but you needed to adjust StateSetDateTime first just like you do in the where clause. Try this for monthly and see what the results are.

Monthly Query;
 
 
SELECT AddDate=dateadd(day,datediff(Day,0,DATEADD(HH, DATEDIFF(HH, GETDATE(), getutcdate()), @startdate)),0),
COUNT (CASE WHEN TimeFromRaisedSeconds / 60 = 0 THEN 1 ELSE NULL END) AS [0 Min],
COUNT (CASE WHEN TimeFromRaisedSeconds / 60 = 1 THEN 1 ELSE NULL END) AS [1 Min],
COUNT (CASE WHEN TimeFromRaisedSeconds / 60 = 2 THEN 1 ELSE NULL END) AS [2 Min],
COUNT (CASE WHEN TimeFromRaisedSeconds / 60 = 3 THEN 1 ELSE NULL END) AS [3 Min],
COUNT (CASE WHEN TimeFromRaisedSeconds / 60 > 3 THEN 1 ELSE NULL END) AS [4+ Min],
COUNT (CASE WHEN TimeFromRaisedSeconds / 60 <= 100000 THEN 1 ELSE NULL END) AS [Total]
FROM Alert.vAlertResolutionState
WHERE ResolutionState = 255 AND (StateSetDateTime BETWEEN DATEADD(HH, DATEDIFF(HH, GETDATE(), getutcdate()), @startdate) AND DATEADD(HH, DATEDIFF(HH, GETDATE(), getutcdate()), @enddate))
Group by dateadd(Day,datediff(Day,0,DATEADD(HH, DATEDIFF(HH, GETDATE(), getutcdate()), @startdate)),0)

Open in new window

Unisys1

ASKER

Hi CGLuttrell,

I am getting following error,

There is an error in the query. Implicit conversion from data type sql_variant to datetime is not allowed. Use the CONVERT function to run this query.

Can you please take a look at the query? SQL did not like something.

ASKER CERTIFIED SOLUTION

Unisys1

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial