April 3, 2003 at 3:04 pm
I have a table with a date column and a sales made on that date.
eg:
03/03/03 -- 10
03/03/03 -- 20
03/05/03 -- 05 etc
I need a query runs for a date range (parameters) and gives the date and the total sale for that day. So far so good. I should even display the dates within the given date range that don't have any sale made as date and 0 as the sale.
I first grouped the available dates into a table variable. Then created a loop starting at the initial date, checked for exists in the table variable, if yes, next, if not insert the date with zero. Increment the counter till the end date is reached. Finally select all the rows from this table variable.
It works, but I don't like my logic. Want to know any better way of doing this.
April 3, 2003 at 3:47 pm
Could you create a proc that gets kicked off as part of a daily job that inserts a 0 quantity record for yesterday if there's no record for yesterday? If not, your logic is probably as good as anyone elses!
Alternatively:
I created a table, tblSaleQuantity with columns SaleDay(smalldatetime,pk) and SaleQuantity(int). Popped in records for the 1st through 4th of April, sans the 3rd. Code and results:
CREATE TABLE#Results
(
SaleDaysmalldatetime,
SaleQuantityinteger
)
DECLARE@StartDaysmalldatetime,
@EndDaysmalldatetime,
@ThisDaysmalldatetime,
@DayCountinteger,
@Iteratorinteger
SET @StartDay = 'April 1, 2003'
SET @EndDay = 'April 4, 2003'
SET @ThisDay = @StartDay
SET @DayCount = DateDiff(dd,@StartDay,@EndDay) + 1
SET @Iterator = 1
IF (@DayCount < 1) RETURN
WHILE (@Iterator <= @DayCount)
BEGIN
INSERT INTO#Results (
SaleDay,
SaleQuantity
)
SELECTSaleDay,
SaleQuantity
FROMtblSaleQuantity
WHEREDateDiff(dd,@ThisDay,SaleDay) = 0
IF (@@ROWCOUNT = 0)
BEGIN
INSERT INTO#Results
(
SaleDay,
SaleQuantity
)
VALUES(
@ThisDay,
0
)
END
SET @ThisDay = DateAdd(dd,1,@ThisDay)
SET @Iterator = @Iterator + 1
END
SELECT*
FROM#Results
DROP TABLE#Results
.
.
.
SaleDay SaleQuantity
------------------------------------------------------ ------------
2003-04-01 00:00:00 10
2003-04-02 00:00:00 20
2003-04-03 00:00:00 0
2003-04-04 00:00:00 5
Regards,
-SJT
April 3, 2003 at 6:07 pm
If you don't mind having a table of dates, you could use a set based solution.
Create a table containing every date for 20 years or so (a good wide range), then left outer join to it and sum your values. The overhead and time of execution will be a fraction of any procedural one.
April 7, 2003 at 12:46 pm
Thanks a lot for the responses. There are some other reports that need similar queries. So I created a table with the dates and I am left-outer joining with that table. Works good. Thanks for the suggestions.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply