Valuable Learnings

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)

The following query will be Very useful when one want to calculate the Cumulative total in SQL Server. For Ex: Create Table TEST_DATA { ID Int NOT NULL, 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 :)