July 10, 2014 at 12:30 pm
Ok all, here is my setup: I have the following tables -
tblPerson - holds basic person data.
tblPersonHistorical - holds a dated snapshot of the fkPersonId, fkInstitutionId, and fkDepartmentId
tblWebUsers - holds login data specific to a web account, but not every person will have a web account
I want to allow my admins to search for users (persons) with web accounts. They need to be able to search by tblPerson.FirstName, tblPerson.LastName, tblInstitutions.Institution, and tblDepartments.Department. The only way a Person record is joined an Institution or Department record is through many -> many junction table tblPersonHistorical.
People place orders and make decisions in our system. Because people can change institutions and departments, we need an historical snapshot of where they worked at the time they placed an order or made a decision. Of course that means some folks will have multiple historical records. That all works fine.
So when an admin user wants to search for webusers, I only want to return data, if possible, from he most recent/current historical records. This is where I am getting bogged down. When I search for a specific webuser I simply do a TOP 1 and ORDER BY DateCreated DESC. That returns only the current historical record for that person/webuser.
But what if I want to return many different webusers, and only want the TOP 1 historical for each returned?
Straight TOP by itself won't do it.
GROUP BY by itself won't do it.
I am convinced I need something akin to a RBAR solution. Maybe a correlated subquery?
Any thoughts?
July 10, 2014 at 12:41 pm
care to provide some sample scripts of create tables/ insert sample data and expected outcome based on the data you provide....will be easier to provide a tested solution for you.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 10, 2014 at 12:56 pm
kpwimberger (7/10/2014)
Ok all, here is my setup: I have the following tables -tblPerson - holds basic person data.
tblPersonHistorical - holds a dated snapshot of the fkPersonId, fkInstitutionId, and fkDepartmentId
tblWebUsers - holds login data specific to a web account, but not every person will have a web account
I want to allow my admins to search for users (persons) with web accounts. They need to be able to search by tblPerson.FirstName, tblPerson.LastName, tblInstitutions.Institution, and tblDepartments.Department. The only way a Person record is joined an Institution or Department record is through many -> many junction table tblPersonHistorical.
People place orders and make decisions in our system. Because people can change institutions and departments, we need an historical snapshot of where they worked at the time they placed an order or made a decision. Of course that means some folks will have multiple historical records. That all works fine.
So when an admin user wants to search for webusers, I only want to return data, if possible, from he most recent/current historical records. This is where I am getting bogged down. When I search for a specific webuser I simply do a TOP 1 and ORDER BY DateCreated DESC. That returns only the current historical record for that person/webuser.
But what if I want to return many different webusers, and only want the TOP 1 historical for each returned?
Straight TOP by itself won't do it.
GROUP BY by itself won't do it.
I am convinced I need something akin to a RBAR solution. Maybe a correlated subquery?
Any thoughts?
As JLS suggested posting ddl and sample data would be a big help. However, I think what you need is to create a cte with ROW_NUMBER and partition it by UserID and Institution. Then you can select from the cte where your row number = 1.
something like this:
with myCTE as
(
select SomeColumns, ROW_NUMBER() over(PARTITION by UserID, InstitutionID order by DateCreated DESC) as RowNum
from tblPerson p
join tblWebUsers w on w.UserID = p.UserID
join tblPersonHistorical ph on ph.SomeID = p.SomeID
)
select *
from myCTE
where RowNum = 1
/suggestion on
BTW, starting every table name with tbl is not a great practice. This type of naming is commonly referred to as tbling.
I also notice you have columns named fkPersonId. Ideally a data point should not change names based on the usage or the table it is in. It should be PersonID in every table. When you start naming tables or columns by their usage you end up with really strange names and it is a real PITA to work with.
/suggestion off
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 10, 2014 at 1:02 pm
Thanks folks. I will see if I can create some generic example code.
I have not worked with CTEs and partitions, so I'll have to do some research on those. But I *think* I follow what you mean.
I have had the tblWhatever conversation before. I stick with it because I have traditionally done some scrubbing of SQL in code, and since I prefix all tables with tbl, I can use that as a regular expression hook to find malicious intent. I have never had an issue with it.
The pk, fk bit I realize is a bit old. I started out doing that so I could disambiguate the columns quickly when reading through long SQL. Probably time to try some new tricks. 😉
July 10, 2014 at 1:10 pm
kpwimberger (7/10/2014)
Thanks folks. I will see if I can create some generic example code.I have not worked with CTEs and partitions, so I'll have to do some research on those. But I *think* I follow what you mean.
I have had the tblWhatever conversation before. I stick with it because I have traditionally done some scrubbing of SQL in code, and since I prefix all tables with tbl, I can use that as a regular expression hook to find malicious intent. I have never had an issue with it.
The pk, fk bit I realize is a bit old. I started out doing that so I could disambiguate the columns quickly when reading through long SQL. Probably time to try some new tricks. 😉
Glad you were able to at least follow what I was trying to say. Post back with some ddl and sample data if you get stuck and I will be happy to help.
I understand it is difficult to change habits. As for disambiguating you should alias your tables and always use that alias in your column list. It removes all doubt. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 10, 2014 at 2:12 pm
I am still trying to wrap my head around CTE and Partition and OVER. In the meantime, here are the table defs. Table aspnet_Membership was what I referred to as tblWebUsers in an earlier post as that is its function.
CREATE TABLE [dbo].[tblPerson]
(
[pkPersonId] [int] IDENTITY(1,1) NOT NULL,
[fkMembershipUserID] [uniqueidentifier] NULL,
[NetworkId] [varchar](4) NULL,
[IsEnabled] [bit] NULL,
[fkRoleCode] [varchar](6) NULL,
[FirstName] [varchar](20) NULL,
[LastName] [varchar](25) NULL,
[Phone1] [varchar](20) NULL,
[Email] [varchar](60) NULL,
[Comments] [varchar](2000) NULL,
[CreatedOn] [datetime] NULL
)
CREATE TABLE [dbo].[tblPersonInstitutionHistorical]
(
[pkPersonHistoryId] [int] IDENTITY(1,1) NOT NULL,
[fkPersonId] [int] NOT NULL,
[fkInsititutionId] [int] NOT NULL,
[fkDeptId] [int] NULL,
[DateRecordAdded] [datetime] NOT NULL
)
CREATE TABLE [dbo].[aspnet_Membership]
(
[ApplicationId] [uniqueidentifier] NOT NULL,
[UserId] [uniqueidentifier] NOT NULL,
[Password] [nvarchar](128) NOT NULL,
[Email] [nvarchar](256) NULL,
[IsApproved] [bit] NOT NULL,
[IsLockedOut] [bit] NOT NULL,
[CreateDate] [datetime] NOT NULL,
[Comment] [ntext] NULL
}
CREATE TABLE [dbo].[tblInstitutions]
(
[pkInstitutionId] [int] IDENTITY(1,1) NOT NULL,
[Institution] [varchar](75) NOT NULL,
[fkInstTypeId] [int] NULL
)
CREATE TABLE [dbo].[tblDepts]
(
[pkDeptId] [int] IDENTITY(1,1) NOT NULL,
[Dept] [varchar](75) NULL,
[fkInstitutionId] [int] NOT NULL,
[IsMain] [bit] NULL,
[fkCenterId] [int] NULL,
[fkDivisionId] [int] NULL,
[Address1] [varchar](50) NULL,
[Address2] [varchar](50) NULL,
[City] [varchar](25) NULL,
[State] [char](2) NULL,
[Zip] [varchar](9) NULL,
[FedexNumber] [varchar](15) NULL
)
I will post what I come up with as a first attempt.
July 10, 2014 at 2:51 pm
You should also be able to use a CROSS APPLY to a "SELECT TOP (1) ..." to get the most recent row for any given user.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 10, 2014 at 3:29 pm
With the following code, you can at least get the most recent historical record for each personid:
;WITH MOST_RECENT AS (
SELECT PH.fkPersonId, MAX(PH.DateRecordAdded) AS DateRecordAdded
FROM tblPersonInstitutionHistorical AS PH
GROUP BY PH.fkPersonId
)
SELECT H.*
FROM tblPersonInstitutionHistorical AS H
INNER JOIN MOST_RECENT AS MR
ON H.fkPersonId = MR.fkPersonId
AND H.DateRecordAdded = MR.DateRecordAdded
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 10, 2014 at 4:53 pm
Steve, that did it! I am a bit embarrassed that I didn't think of a simple MAX() function. That was slick and spot on. Thanks.
Kurt
July 10, 2014 at 5:05 pm
Glad I could help, and thanks for the update.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply