COUNT Per Date and Insert Into Another Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

COUNT Per Date and Insert Into Another Table

Author

Topic

anthonywardle
Starting Member

2 Posts

Posted - 2012-12-14 : 11:10:31

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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-14 : 11:22:36

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.

anthonywardle
Starting Member

2 Posts

Posted - 2012-12-14 : 11:38:00

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-14 : 11:47:04

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources