30 November, 2009

Things to remember with Datetime Queries

I wrote some stored procedures to get data for my reports in last week. Most of the queries involve day's summary and cumulative summary till the given date. After running the queries I observed that I am getting wrong results in all the datasets.
After some analysis, I figured out my mistake. I have compared the date given directly with the date field in the table. The problem here is If I give '11/30/2009' as my input date, Database automatically adds the time part to that. So the input becomes '11/30/2009 00:00:00:000'. Whenever I compare the date the query always checks for the exact match. So I need to modify my queries to return correct data.
Here is the modification:
instead of comparing date directly... like 'date = @ReportDate' or 'date < @ReportDate', we should compare with the 'floor' and 'ceil' values for the given date. So the query has modified as 'date between cast(floor(cast(@ReportDate as float))as datetime) and cast(ceiling(cast(@ReportDate as float))as datetime)'. This query works and returns the required datarows. But here one point should be remembered, if we give the parameter with any date picker, It selects only the date and by default it will become 'givenDate 00:00:00:000'. In this case the floor and ceil values for this date are same. So We need to correct our query once again. This modification is very simple and floor value is considered as a benchmark always. After this modification, the query will look like, 'date between cast(floor(cast(@ReportDate as float))as datetime) and dateadd(hh,24,cast(floor(cast(@ReportDate as float))as datetime))'. This last query will always return the correct set of results for a given date.

I think this is known to many of the techies.. But useful to starter bees :)

25 November, 2009

Cumulative totals Query with T-SQL

The following query will be Very useful when one want to calculate the Cumulative total in SQL Server.

For Ex:
Create Table TEST_DATA
Amount Int NOT NULL

if the table data is like

ID  Amount
--- ------
1 10
2 20
3 40
4 50
5 30

select t1.ID, t1.Amount,
(select sum(t2.Amount ) from
TEST_DATA as t2 where t2.ID <= t1.ID) as Total from TEST_DATA as t1

This Query will return the results as

ID Amount Total
--- ------ -----
1 10 10
2 20 30
3 40 70
4 50 120
5 30 150

-Happy Querying :)