April 3, 2014 at 12:17 pm
the table in my subquery has a person in there multiple times becuase its the issue table for items. I am trying to get 1 record that has the strunit on it. The only thing I can join on is the intpersID which is the issued ID in the table. Problem is personnel have assinged multiple strUnits to themselves on differerent records in there. I could care less which strUnit I grab I just want to grab 1.
Here is my query
Selectdistinct a.intPersId
,mn.strFullname Name
,a.strSignature
,CONVERT(Varchar(10), a.dtSigned, 111) dtSigned
,CONVERT(Varchar(10), a.dtnotified, 111) dtNotified
,CASE WHEN a.dtSigned IS NOT NULL THEN '0' ELSE datediff(DAY, a.dtNotified, getdate()) END DaysNotified
,i.strUnit
fromDrat_AnnualHR a
LEFT JOIN tblPersonnel mn on mn.intPersonnelId = a.intPersID
LEFT JOIN (select intIssuedTo, strUnit from Drat_Issued) i on i.intIssuedTo = a.intPersId
However because a person can have multiple strUnit attached to the sub query record, it returns mutiple records, I just want it to match and return 1 record with the strUnit. tried Top, Order by, Groupby, but nothing seems to work.
so for a return instead of 1 record it returns 2 or more depending on how many strUnits they have on records.
intPersId name strSignature dtsigned dtNotified DaysNotified strunit
101 John Does
April 3, 2014 at 12:20 pm
Sorry hit return accidentally before I could post a result example.
134 John Doe John Doe.123245 2013/12/16 2013/12/16 0 8AEAA
134 John Doe John Doe.123245 2013/12/16 2013/12/16 0 PJPT0
134 John Doe John Doe.123245 2013/12/16 2013/12/16 0 JJTY0
I only want 1 record to show, but becuase this person has multiple strUNists assinged to them in the Issued table it pulls multiple records.
April 3, 2014 at 12:26 pm
My first question is why are you using a subquery here? This is just another left join.
Select distinct a.intPersId
,mn.strFullname Name
,a.strSignature
,CONVERT(Varchar(10), a.dtSigned, 111) dtSigned
,CONVERT(Varchar(10), a.dtnotified, 111) dtNotified
,CASE WHEN a.dtSigned IS NOT NULL THEN '0' ELSE datediff(DAY, a.dtNotified, getdate()) END DaysNotified
,i.strUnit
from Drat_AnnualHR a
LEFT JOIN tblPersonnel mn on mn.intPersonnelId = a.intPersID
LEFT JOIN Drat_Issued i on i.intIssuedTo = a.intPersId
I am more than happy to help but in order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
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/
April 3, 2014 at 12:32 pm
First, do you really need the DISTINCT in the select statement?
Second, try this:
Select distinct
a.intPersId
,mn.strFullname Name
,a.strSignature
,CONVERT(Varchar(10), a.dtSigned, 111) dtSigned
,CONVERT(Varchar(10), a.dtnotified, 111) dtNotified
,CASE WHEN a.dtSigned IS NOT NULL THEN '0' ELSE datediff(DAY, a.dtNotified, getdate()) END DaysNotified
,i.strUnit
from
Drat_AnnualHR a
LEFT JOIN tblPersonnel mn on
mn.intPersonnelId = a.intPersID
OUTER APPLY (select top 1 intIssuedTo, strUnit
from Drat_Issued di
where di.intIssuedTo = a.intPersId
order by strUnit) i;
April 3, 2014 at 12:36 pm
HI, I was only using it to try to get to 1 record, knew it did not work since I was still getting mutiple. Your change works to only get one record. Have to look this up cause I have never seen a Outer apply. thanks for the help.
April 3, 2014 at 12:49 pm
I would probably use APPLY similar to Lynn but here is another approach that should accomplish the same thing.
Select a.intPersId
,mn.strFullname Name
,a.strSignature
,CONVERT(Varchar(10), a.dtSigned, 111) dtSigned
,CONVERT(Varchar(10), a.dtnotified, 111) dtNotified
,CASE WHEN a.dtSigned IS NOT NULL THEN '0' ELSE datediff(DAY, a.dtNotified, getdate()) END DaysNotified
, MIN(i.strUnit) --Since you don't care which value you get you could use ANY aggregate function here.
from Drat_AnnualHR a
LEFT JOIN tblPersonnel mn on mn.intPersonnelId = a.intPersID
LEFT JOIN Drat_Issued i on i.intIssuedTo = a.intPersId
group by a.intPersId
,mn.strFullname
,a.strSignature
,CONVERT(Varchar(10), a.dtSigned, 111)
,CONVERT(Varchar(10), a.dtnotified, 111)
,CASE WHEN a.dtSigned IS NOT NULL THEN '0' ELSE datediff(DAY, a.dtNotified, getdate()) END
_______________________________________________________________
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/
April 3, 2014 at 12:51 pm
First, glad to help. Second, since you don't need the DISTINCT, drop it from my code.
April 3, 2014 at 12:53 pm
Stubby Bunny (4/3/2014)
HI, I was only using it to try to get to 1 record, knew it did not work since I was still getting mutiple. Your change works to only get one record. Have to look this up cause I have never seen a Outer apply. thanks for the help.
Take a look at the links in my signature about APPLY. It is a very cool feature.
_______________________________________________________________
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/
April 3, 2014 at 1:00 pm
Sean,
thanks for the link to applys, wish I had know about these before could have saved me a lot of headaches. thank you
April 3, 2014 at 1:03 pm
Stubby Bunny (4/3/2014)
Sean,thanks for the link to applys, wish I had know about these before could have saved me a lot of headaches. thank you
You are quite welcome. Just think of all the headaches you can prevent now. :hehe:
_______________________________________________________________
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/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply