SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 COUNT Per Date and Insert Into Another Table		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

anthonywardle
Starting Member

United Kingdom
2 Posts
Posted - 12/14/2012 :  11:10:31  Show Profile  Reply with Quote
Afternoon all

New to this forum so be gentle with me.
I am by no means an expert on SQL but know enough to get me by with the job I do.

I have 2 tables.

TableA has a list of dates in a column named date_field (currently just 01/01/2011 to 31/12/2011), and multiple columns that I want to insert data into, for this example using column named count_for_xyz

TableB has a list of calls that have taken place on that date, a row per call ranging from a single call to hundreds, all labelled per client (client_name) with a datetime stamp (Start_time)

What I am trying to do, is select from TableB, the number of calls by date for client XYZ (this bit is easy), but I want to insert these results in a the column count_for_xyz in TableA.

So far what i have written is (the DATEADD bit is to strip out the time from the datetime value) 

set dateformat dmy
UPDATE TableA
SET count_for_XYZ = 
(
SELECT COUNT(*) FROM TableB
WHERE ClientName = 'XYZ'
AND
DATEADD(day, DATEDIFF(day, 0, Start_time), 0) IN (SELECT date_field from TableA)
)
WHERE date_field = (SELECT DATEADD(day, DATEDIFF(day, 0, Start_time), 0) from TableB)


But this returns a message saying

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Any help much appreciated

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 12/14/2012 :  11:22:36  Show Profile  Reply with Quote
It is the subquery in the WHERE clause that is causing the problem.

First run this query to see if the results look like what you want them to be
SELECT
	DATEADD(DAY,DATEDIFF(DAY,0,Start_time),0) AS Date,
	COUNT(*) AS count_for_XYZ
FROM
	TableB;

If that looks right, you can do the updates by making that into a subquery like shown below:
UPDATE a SET
	a.count_for_XYZ = b.count_for_XYZ
FROM
	TableA a
	INNER JOIN 
	(
		SELECT
			DATEADD(DAY,DATEDIFF(DAY,0,x.Start_time),0) AS Date,
			COUNT(*) AS count_for_XYZ
		FROM
			TableB x
	) AS b ON b.Date = a.Date
That assumes taht the Date column in TableA has no time portion to it.
Go to Top of Page

anthonywardle
Starting Member

United Kingdom
2 Posts
Posted - 12/14/2012 :  11:38:00  Show Profile  Reply with Quote
Thanks sunitabeck,

That's great. Modified slightly as it was also needing a group by in there, but that has helped me acheive what I am after....now to just modify & repeat this for multiple clients over multiple date ranges.

Many Thanks, greatly appreciated
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 12/14/2012 :  11:47:04  Show Profile  Reply with Quote
Great!

And about the GROUP BY - I ALWAYS forget that!!

To do this for multiple clients, you can add the client ID column to the query and group by clauses, and also the join clause in the outer query.
Edited by - sunitabeck on 12/14/2012 11:47:48
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000