• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 49
  • Last Modified:

Update a column in one table with the count of rows in another

Hello,

I'm working in SQL Server 2012 and trying to update a field in one table with a count of rows in another table that meet my criteria.  It will be easier to demonstrate with some faux data so here you go:
CREATE TABLE #TABLE1 (
	TABLE1ID INT,
	USERID INT,
	TABLE1DATE DATE,
	THECOUNT INT
)
GO
CREATE TABLE #TABLE2 (
	USERID INT,
	TABLE2DATE DATE,
	TABLE2CRITERIA INT
)
GO
INSERT INTO #TABLE1 VALUES
	(118380, 116582, '2014-03-14', 0),
	(118380, 126442, '2014-09-12', 0),
	(250546, 377959, '2009-07-26', 0),
	(787156, 69879, '2010-11-27', 0),
	(1430548, 522015, '2015-02-04', 0),
	(877827, 674129, '2013-10-10', 0),
	(67960, 519332, '2014-10-10', 0),
	(67960, 314561, '2011-11-03', 0),
	(1735514, 414294, '2015-02-18', 0),
	(1353983, 565342, '2009-11-14', 0),
	(1810296, 632287, '2009-11-22', 0),
	(298348, 282491, '2012-05-26', 0)
GO
INSERT INTO #TABLE2 VALUES
	(118380, '2012-01-14', 1),
	(118380, '2010-01-11', 1),
	(118380, '2015-01-14', 1),
	(238280, '2000-02-01', 1),
	(1810296, '1999-01-13', 3),
	(298348, '2012-05-23', 2),
	(298348, '2011-03-10', 9),
	(67960, '2000-01-01', 2),
	(67960, '2000-04-10', 1)

Open in new window


What I'm trying to do is update #TABLE1.THECOUNT with the count of rows in #TABLE2 where #TABLE1.USERID=#TABLE2.USERID AND #TABLE2.TABLE2DATE<#TABLE1.TABLE1DATE AND #TABLE2.TABLE2CRITERIA IN (1,2,3).


I thought I would start with a CTE like so:
;WITH CTE_STUFFTOCOUNT AS
(SELECT
	t2.USERID, t2.TABLE2CRITERIA, t2.TABLE2DATE 
	FROM #TABLE2 AS t2
	WHERE t2.TABLE2CRITERIA IN (1,2,3)
)

Open in new window

And then update #TABLE1.THECOUNT with the count of rows where TABLE2DATE<TABLE1DATE but I'm getting hung up on how to handle the multiple instances of USERID in #TABLE1.

Any help will be REALLY REALLY REALLY REALLY appreciated (my brain is currently tied up in knots).  :D
0
ttist25
Asked:
ttist25
  • 5
  • 3
1 Solution
 
Ryan ChongCommented:
are you trying to match #TABLE1.TABLE1ID with #TABLE2.USERID instead?

based on your test data:

SnapShot.png
0
 
ttist25Author Commented:
Ahhhh shoot!  :)

Thanks Ryan.  I screwed up the column names but yes, the join would be on #TABLE1.TABLE1ID=#TABLE2.USERID.

Sorry about that.
0
 
Ryan ChongCommented:
ok, try verify this (not fully tested) and see if it's working?

IF OBJECT_ID('tempdb..#TABLE1') IS NOT NULL DROP TABLE #TABLE1
IF OBJECT_ID('tempdb..#TABLE2') IS NOT NULL DROP TABLE #TABLE2

CREATE TABLE #TABLE1 (
	TABLE1ID INT,
	USERID INT,
	TABLE1DATE DATE,
	THECOUNT INT
)
GO
CREATE TABLE #TABLE2 (
	USERID INT,
	TABLE2DATE DATE,
	TABLE2CRITERIA INT
)
GO
INSERT INTO #TABLE1 VALUES
	(118380, 116582, '2014-03-14', 0),
	(118380, 126442, '2014-09-12', 0),
	(250546, 377959, '2009-07-26', 0),
	(787156, 69879, '2010-11-27', 0),
	(1430548, 522015, '2015-02-04', 0),
	(877827, 674129, '2013-10-10', 0),
	(67960, 519332, '2014-10-10', 0),
	(67960, 314561, '2011-11-03', 0),
	(1735514, 414294, '2015-02-18', 0),
	(1353983, 565342, '2009-11-14', 0),
	(1810296, 632287, '2009-11-22', 0),
	(298348, 282491, '2012-05-26', 0)
GO
INSERT INTO #TABLE2 VALUES
	(118380, '2012-01-14', 1),
	(118380, '2010-01-11', 1),
	(118380, '2015-01-14', 1),
	(238280, '2000-02-01', 1),
	(1810296, '1999-01-13', 3),
	(298348, '2012-05-23', 2),
	(298348, '2011-03-10', 9),
	(67960, '2000-01-01', 2),
	(67960, '2000-04-10', 1)


select * from #TABLE1
select * from #TABLE2 where TABLE2CRITERIA in (1,2,3)

;with cte as
(
	select b.USERID, sum(case when b.TABLE2DATE < a.TABLE1DATE then 1 else 0 end) Total
	from #TABLE2 b inner join #TABLE1 a
	on b.USERID = a.TABLE1ID where b.TABLE2CRITERIA in (1,2,3)
	GROUP BY b.USERID
)
update a
set a.THECOUNT = b.Total
from #TABLE1 a
inner join 
cte b on a.TABLE1ID = b.USERID

select * from #TABLE1

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ttist25Author Commented:
Thanks again Ryan.  I ran the above code and here is the part I was having trouble with:
results
For example, TABLE1ID 118380 should have the value '2' in THECOUNT for both rows because for row 1, the count of rows in the CTE that meet the criteria is 2.  I think it's getting multiplied because there are multiple instances of 118380 in #TABLE1.  Same thing for 67960.  

Is there a way to use the other ID field in #TABLE1 to accomplish this?  

Thanks so much for the help.
0
 
Ryan ChongCommented:
i try to add in additional criteria... and see if this produce correct results?

IF OBJECT_ID('tempdb..#TABLE1') IS NOT NULL DROP TABLE #TABLE1
IF OBJECT_ID('tempdb..#TABLE2') IS NOT NULL DROP TABLE #TABLE2

CREATE TABLE #TABLE1 (
	TABLE1ID INT,
	USERID INT,
	TABLE1DATE DATE,
	THECOUNT INT
)
GO
CREATE TABLE #TABLE2 (
	USERID INT,
	TABLE2DATE DATE,
	TABLE2CRITERIA INT
)
GO
INSERT INTO #TABLE1 VALUES
	(118380, 116582, '2014-03-14', 0),
	(118380, 126442, '2014-09-12', 0),
	(250546, 377959, '2009-07-26', 0),
	(787156, 69879, '2010-11-27', 0),
	(1430548, 522015, '2015-02-04', 0),
	(877827, 674129, '2013-10-10', 0),
	(67960, 519332, '2014-10-10', 0),
	(67960, 314561, '2011-11-03', 0),
	(1735514, 414294, '2015-02-18', 0),
	(1353983, 565342, '2009-11-14', 0),
	(1810296, 632287, '2009-11-22', 0),
	(298348, 282491, '2012-05-26', 0)
GO
INSERT INTO #TABLE2 VALUES
	(118380, '2012-01-14', 1),
	(118380, '2010-01-11', 1),
	(118380, '2015-01-14', 1),
	(238280, '2000-02-01', 1),
	(1810296, '1999-01-13', 3),
	(298348, '2012-05-23', 2),
	(298348, '2011-03-10', 9),
	(67960, '2000-01-01', 2),
	(67960, '2000-04-10', 1)


select * from #TABLE1
select * from #TABLE2

;with cte as
(
	select b.USERID, a.TABLE1DATE, sum(case when b.TABLE2DATE < a.TABLE1DATE then 1 else 0 end) Total
	from #TABLE2 b inner join #TABLE1 a
	on b.USERID = a.TABLE1ID where b.TABLE2CRITERIA in (1,2,3)
	GROUP BY b.USERID, a.TABLE1DATE
)
update a
set a.THECOUNT = b.Total
from #TABLE1 a
inner join 
cte b on a.TABLE1ID = b.USERID and a.TABLE1DATE = a.TABLE1DATE

select * from #TABLE1

Open in new window

0
 
ttist25Author Commented:
Awesome!  Those are the results I would expect!  

How did you make it work?
0
 
ttist25Author Commented:
Oh I think I see!

You are grouping by userid and table1date:
GROUP BY b.USERID, a.TABLE1DATE
0
 
ttist25Author Commented:
Thanks Ryan!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now