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