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
LVL 1
ttist25Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.