The problem is that there are no values for those dates so there are no rows
being returned. In this case isNull won't work. An easy way to do this is if
you query against a table containing the dates then join the orders table. If
you are using SQLServer, you can do the following:
DECLARE @i int,@x int,@startDate smalldatetime, @endDate smalldatetime,
@tempDate smalldatetime
SELECT @startDate = '#varStartDate#',
@endDate = '#varEndDate#',
@x = 0;
SET @i = DateDiff(DAY,@startDate,@endDate)
CREATE TABLE #dates (day smalldatetime)
WHILE (@x < @i)
BEGIN
SET @x = @x + 1
SET @tempDate = DateAdd(DAY,@x,@startDate)
INSERT INTO #dates (day) VALUES (@tempDate)
END
SELECT Datepart(dw,d.Day),
isNull(Count(o.OrderDate),0) AS TotalOrdersPerDay
FROM #dates as d
LEFT OUTER JOIN Orders as o on CONVERT(varchar,o.orderdate,1) =
CONVERT(varchar,d.Day,1)
WHERE o.Status = 'SHIPPED'
GROUP BY
Datepart(dw,d.Day)
ORDER BY
Datepart(dw,d.Day) ASC
DROP TABLE #dates
Hope that helps,
Chris
>>> [EMAIL PROTECTED] 03/07/05 07:47AM >>>
tried isNull(), then did a dump of the query.
i got results for sunday and monday...yet nothing (not even any zeroes)
for the rest of the week. any other ideas?
-----Original Message-----
From: Ali Awan [mailto:[EMAIL PROTECTED]
Sent: Friday, March 04, 2005 5:32 PM
To: CF-Talk
Subject: RE: SQL Case Question?
There is a function in SQL called IsNull which replaces a NULL result with
whatever value you specify.
Try doing this in the first line of your statement:
SELECT IsNull(Count(OrderDate),0) AS TotalOrdersPerDay
That way if the result set is NULL it will output a 0.
Ali
-----Original Message-----
From: Che Vilnonis [mailto:[EMAIL PROTECTED]
Sent: Friday, March 04, 2005 2:23 PM
To: CF-Talk
Subject: OT: SQL Case Question?
I'm trying to output total orders by day of the week.
SELECT Count(OrderDate) AS TotalOrdersPerDay
FROM Orders
WHERE Status = 'SHIPPED'
AND (OrderDate BETWEEN '#VarStartDate# 00:00:01' AND '#VarEndDate#
23:59:59')
GROUP BY Datepart(dw,OrderDate)
ORDER BY Datepart(dw,OrderDate) ASC
This code works great, for the most part. How would I change this code so
that if there were NO orders for a particular day, the query would return a
result of zero [0] and not null or whatever it returns when there is no
result? Does this make sense? I just don't want to have <td> cells with
nothing in them.
Thanks, Che
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:197671
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
