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

Reply via email to