Thanks for the SP... But I need to output: Day 1 55 Day 2 130 Day 3 110 So I have to group the totals by day.
________________________________ From: Robertson-Ravo, Neil (RX) [mailto:[EMAIL PROTECTED] Sent: Tue 8/9/2005 10:25 AM To: CF-Talk Subject: RE: Totals from Last 7 days, multiple tables Could cheat and just do this ;-) DECLARE @count1 INT DECLARE @count2 INT DECLARE @count3 INT DECLARE @total INT select @count1 = count(*) as totalcalls from cases_notes WHERE note_date BETWEEN #createodbcdate(startdat)# AND #DateAdd('D', -7, now())#) Select @count2 = count(*) as totalcalls from customer_info WHERE logdate BETWEEN #createodbcdate(startdat)# AND #DateAdd('D', -7, now())#) select @count3 = count(*) FROM customer_log WHERE customer_log.logdate BETWEEN #createodbcdate(dateformat(Dateadd('D', -7, now()), 'mm/dd/yyyy'))# AND #DateAdd('D', 1, now())#) set @total = @count1 + @count2 + @count3 SELECT @total -----Original Message----- From: Eric J. Hoffman [mailto:[EMAIL PROTECTED] Sent: 09 August 2005 16:26 To: CF-Talk Subject: Totals from Last 7 days, multiple tables Quick question...this query is wrong, but I am in the middle of spitballing how to get this information. I am sure an old SQL hand will want to slap me and say duh..but what I am trying to get is the total of calls for each day for the last seven days...the logs of these calls exist over three separate tables. I don't know how to group the calcs in the subqueries. So maybe it should be rewritten? Here's what I have: SELECT COUNT (*) + (Select count(*) as totalcalls from cases_notes WHERE note_date BETWEEN #createodbcdate(startdat)# AND #DateAdd('D', -7, now())#) + (Select count(*) as totalcalls from customer_info WHERE logdate BETWEEN #createodbcdate(startdat)# AND #DateAdd('D', -7, now())#) as contacts, DATEPART(dd, customer_log.logdate) as dow FROM customer_log WHERE customer_log.logdate BETWEEN #createodbcdate(dateformat(Dateadd('D', -7, now()), 'mm/dd/yyyy'))# AND #DateAdd('D', 1, now())# GROUP BY DATEPART(dd, customer_log.logdate) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:214190 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

