[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2009-05-01
7
Medium Priority
?
382 Views
Last Modified: 2012-05-06
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
0
Comment
Question by:Unisys1
  • 4
  • 2
7 Comments
 
LVL 9

Expert Comment

by:ezraa
ID: 24281475
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

0
 

Author Comment

by:Unisys1
ID: 24281771
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)
0
 
LVL 9

Expert Comment

by:ezraa
ID: 24295210
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

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Unisys1
ID: 24336945
unfortunately this returned less value then previous one unfortunately no luck so far.
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 24341345
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

0
 

Author Comment

by:Unisys1
ID: 24353873
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.
0
 

Accepted Solution

by:
Unisys1 earned 0 total points
ID: 24739690
I set the report time  8:00 PM previous day and 07:59 PM next day. and it worked.

thank you for all your effort.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question