June 25, 2014 at 1:46 pm
I have a pretty complex query that returns three records. For simplicity sake, the results can be simulated with this query:
Select 5 AS InternalAuditTeamEmployeeID, 1 as InternalAuditTeamID
UNION ALL
Select 11, 2
UNION ALL
Select 14, 3;
I want to take this result and update the Flag field to true in my table tblInternalAuditTeamEmployee (CREATE statement below) for any InternalAuditTeamEmployeeID that is less than or equal to the ones in the results above, but by group. My results would look something like this using the data below and the results above.
InternalAuditTeamEmployeeIDInternalAuditTeamIDEmployeeIDFlag
1 1 619 1
218581
316041
425181
517161
639661
711910
819400
92391
1012340
1129541
1228910
1329500
143321
1539450
I was thinking I could somehow use ROW_NUMBER(PARTITION BY InternalAuditTeamID ORDER BY InternalAuditTeamEmployeeID DESC), but not sure how to get the results of "WHERE <= InternalAuditTeamEmployeeID For each particular group".
CREATE TABLE STATEMENT:
CREATE TABLE [tblInternalAuditTeamEmployee](
[InternalAuditTeamEmployeeID] [int] IDENTITY(1,1) NOT NULL,
[InternalAuditTeamID] [int] NOT NULL,
[EmployeeID] [int] NOT NULL,
[Flag] [bit] NOT NULL CONSTRAINT [DF_tblInternalAuditTeamEmployee_Flag] DEFAULT ((0)),
CONSTRAINT [PK_tblInternalAuditEligibleEmployees] PRIMARY KEY CLUSTERED
(
[InternalAuditTeamEmployeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [tblInternalAuditTeamEmployee] ON
GO
INSERT [tblInternalAuditTeamEmployee] ([InternalAuditTeamEmployeeID], [InternalAuditTeamID], [EmployeeID], [Flag]) VALUES (1, 1, 619, 0)
GO
INSERT [tblInternalAuditTeamEmployee] ([InternalAuditTeamEmployeeID], [InternalAuditTeamID], [EmployeeID], [Flag]) VALUES (2, 1, 858, 0)
GO
INSERT [tblInternalAuditTeamEmployee] ([InternalAuditTeamEmployeeID], [InternalAuditTeamID], [EmployeeID], [Flag]) VALUES (3, 1, 604, 0)
GO
INSERT [tblInternalAuditTeamEmployee] ([InternalAuditTeamEmployeeID], [InternalAuditTeamID], [EmployeeID], [Flag]) VALUES (4, 2, 518, 0)
GO
INSERT [tblInternalAuditTeamEmployee] ([InternalAuditTeamEmployeeID], [InternalAuditTeamID], [EmployeeID], [Flag]) VALUES (5, 1, 716, 0)
GO
INSERT [tblInternalAuditTeamEmployee] ([InternalAuditTeamEmployeeID], [InternalAuditTeamID], [EmployeeID], [Flag]) VALUES (6, 3, 966, 0)
GO
INSERT [tblInternalAuditTeamEmployee] ([InternalAuditTeamEmployeeID], [InternalAuditTeamID], [EmployeeID], [Flag]) VALUES (7, 1, 191, 0)
GO
INSERT [tblInternalAuditTeamEmployee] ([InternalAuditTeamEmployeeID], [InternalAuditTeamID], [EmployeeID], [Flag]) VALUES (8, 1, 940, 0)
GO
INSERT [tblInternalAuditTeamEmployee] ([InternalAuditTeamEmployeeID], [InternalAuditTeamID], [EmployeeID], [Flag]) VALUES (9, 2, 39, 0)
GO
INSERT [tblInternalAuditTeamEmployee] ([InternalAuditTeamEmployeeID], [InternalAuditTeamID], [EmployeeID], [Flag]) VALUES (10, 1, 234, 0)
GO
INSERT [tblInternalAuditTeamEmployee] ([InternalAuditTeamEmployeeID], [InternalAuditTeamID], [EmployeeID], [Flag]) VALUES (11, 2, 954, 0)
GO
INSERT [tblInternalAuditTeamEmployee] ([InternalAuditTeamEmployeeID], [InternalAuditTeamID], [EmployeeID], [Flag]) VALUES (12, 2, 891, 0)
GO
INSERT [tblInternalAuditTeamEmployee] ([InternalAuditTeamEmployeeID], [InternalAuditTeamID], [EmployeeID], [Flag]) VALUES (13, 2, 950, 0)
GO
INSERT [tblInternalAuditTeamEmployee] ([InternalAuditTeamEmployeeID], [InternalAuditTeamID], [EmployeeID], [Flag]) VALUES (14, 3, 32, 0)
GO
INSERT [tblInternalAuditTeamEmployee] ([InternalAuditTeamEmployeeID], [InternalAuditTeamID], [EmployeeID], [Flag]) VALUES (15, 3, 945, 0)
GO
SET IDENTITY_INSERT [tblInternalAuditTeamEmployee] OFF
GO
Thanks for any insight.
June 25, 2014 at 2:34 pm
I'm a little confused to your request. What do you mean 'by group'?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 25, 2014 at 3:36 pm
Assuming by "group" you're referring to the AuditTeamID, I think this gets you what you need.
;with qry as
(
Select 5 AS InternalAuditTeamEmployeeID, 1 as InternalAuditTeamID
UNION ALL
Select 11, 2
UNION ALL
Select 14, 3
)
update a
set flag = 1
from tblInternalAuditTeamEmployee a
inner join qry b
on a.InternalAuditTeamID = b.InternalAuditTeamID
and a.InternalAuditTeamEmployeeID <= b.InternalAuditTeamEmployeeID
June 25, 2014 at 4:19 pm
Sorry about leaving the specifics of the group out. Before submitting, I read my post and it made sense to me - of course. Sorry I missed the details. JeeTee's solution is spot on to what I was trying to achieve. I've never used anything on an inner join other than the = operator and didn't even realize I could (sometimes I completely miss the simple things :-)). Thank you for the solution, and for helping me learn something new today!
Thanks,
Kevin
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply