May 28, 2014 at 11:05 am
I have 5 tbls:
tbl1: Person table contains all person information (name, address)
tbl2: Dept (contains list of Dept the person worked at: Finance, payroll, HumanResource, etc)
tbl3: Year (Contains column Years, which contains year values: 2010, 2011, 2012,etc)
Tbl4: Current Year (this table contains only one column, current year value is 2013)
tbl5: Dept_Year is a reference table to join Year and Dept
I was tasked to extract data from person table for year 2014 to list person's current department, and also list person’s previous year Department name if Department is different. For example, the person can belong to Dept1 in 2014, but possibility has worked in Dept2 for year 2013. The query has to return both values.
Initially I joined all tables as following
select Per.name, Dep.DepAbbr, Depnameforcurrentyear, depnameforlastyear
from Person Per
left join ADDRESS ADDR on per.ADDR_PK= ADDR.ADDR.PK
left join Dept Dep on Per.Dep_PK = Dep.Dep_PK
left join Dept_Year DY on Dep.YearPK = DY.YearPK
left join YEAR YR ON YR.YEAR_PK = DY.Year_PK
Left join CurrentYear CY on CY.CurrentYEAR + 1 = Dept.CSCHOOL_YEAR = YR.CurrentYear
I am able to list dept name for 2014, but need help to list dept name in 2013. BTW, we might not need to use currentYear table since that table only contains 2013 value (that is why I am adding by 1), instead you can use Year table, take max of Year tbl (which will be 2014 to list current dept name), and take max Year - 1 to get 2013 and display dept name for 2013.
How would you query it?
May 28, 2014 at 11:47 am
maybe be easier for us to give you a tried and tested answer if you can please provide set up scripts for create table / insert sample data and the expected results from the sample data.
thanks
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 28, 2014 at 11:50 am
The 5 tables I gave were just an example, the system I am working on is very normalized. I will create a test script and send it shortly. Thank you.
June 1, 2014 at 9:27 pm
Sorry for the late reply, I got busy with other things... Attached is a test file which includes a sample script. The goal is to list person information such as department for the current year, 2014, in addition to previous year, 2013. The Year tbl list all years, 2012, 2013, 2014. The current year shows the current year which is 2014. The dept lists all depts, and the person tbl lists the person. Sorry, I had to make up the information but the actual database is more complex.
For example lets say you have person A, we need person's dept name for 2014, and person previous dept name for 2013. The output needs to be like following
PersonName CurrentDept PreviousDept
Test Person HR Finance
Thank you so much.
June 2, 2014 at 2:34 am
lsalih (6/1/2014)
Sorry for the late reply, I got busy with other things... Attached is a test file which includes a sample script. The goal is to list person information such as department for the current year, 2014, in addition to previous year, 2013. The Year tbl list all years, 2012, 2013, 2014. The current year shows the current year which is 2014. The dept lists all depts, and the person tbl lists the person. Sorry, I had to make up the information but the actual database is more complex.For example lets say you have person A, we need person's dept name for 2014, and person previous dept name for 2013. The output needs to be like following
PersonName CurrentDept PreviousDept
Test Person HR Finance
Thank you so much.
thanks for the script.....sample data seems a little sparse, but I'll take a first guess as something along these lines...maybe what you are after, or maybe not:-)
;WITH CTE as (
SELECT Person.Per_PK
, Person.Fname
, Person.Lname
, Dept.Dept_name
, Years.D_Year
, ROW_NUMBER() OVER (PARTITION BY Person.Per_PK ORDER BY Years.D_Year DESC ) rn
FROM Person
INNER JOIN PerDeptYear
ON Person.Per_PK = PerDeptYear.Per_PK
INNER JOIN Dept
ON PerDeptYear.Dept_PK = Dept.Dept_PK
INNER JOIN Years
ON PerDeptYear.Year_PK = Years.Year_PK
)
SELECT Per_PK
, Fname
, Lname
, ISNULL(MAX(case when rn=1 then dept_name end),'') as currentdept
, ISNULL(MAX(case when rn=2 then dept_name end),'') as previousdept
FROM cte
GROUP BY Per_PK
, Fname
, Lname
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 2, 2014 at 7:45 am
Many thanks to you, it worked. Great help!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply