March 3, 2014 at 3:49 pm
In a sql server 2008 r2 database, I am trying to take the results of the cte called sProgram and join the results with the Person table in the query. The syntax of the join looks ok, however the results of the join are incorrect. The joined results are null when there is really data that can be joined. Can you tell me how to join the query so the results of the sprogram cte will be joined to the Person table correctly?
1. Here is the query:
;with sProgram as (
SELECT [personID],[attributeID],[value], [date],ROW_NUMBER() OVER(Partition by[personID] ORDER BY [personID],[date] DESC) rn
FROM [dbo].[CustomS]
where [attributeID] in ('562','563','564','565')
)
,programSelect as
(
select [personID],[attributeID],[value],[date]
from sProgram
where rn=1
)
SELECT person.personID
, cs562.personID
, cs562.attributeID
, cs562.value
, cs562.[date]
, cs563.personID
, cs563.attributeID
, cs563.value
, cs563.[date]
, cs564.personID
, cs564.attributeID
, cs564.value
, cs564.[date]
, cs565.personID
, cs565.attributeID
, cs565.value
, cs565.[date]
FROM Person cs562
join programSelect on cs562.personID = Person.personID and cs562.attributeID= '562'
left join programSelect cs563 on cs563.personID = cs562.personID and cs563.[Date] = cs562.[date] and cs563.attributeID ='563'
left join programSelect cs564 on cs564.personID = cs563.personID and cs564.[date] = cs563.[Date] and cs564.attributeID ='564'
left join programSelect cs565 on cs565.personID = cs564.personID and cs565.[Date] = cs564.[date] and cs565.attributeID ='565'
GROUP BY
person.personID
, cs562.personID
, cs562.attributeID
, cs562.value
, cs562.[date]
, cs563.personID
, cs563.attributeID
, cs563.value
, cs563.[date]
, cs564.personID
, cs564.attributeID
, cs564.value
, cs564.[date]
, cs565.personID
, cs565.attributeID
, cs565.value
, cs565.[date]
2. here is the CustomS table with some data.
SELECT [CustomSID]
,[personID]
,[attributeID]
,[value]
,[date]
FROM [dbo].[CustomS]
where personID=7170
and attributeID in (562,563,564,565)
order by date desc
CustomSID personID attributeID value [date]
262490684 7170 562 GA 2013-08-14 07:26:00
262490683 7170 565 05/23/2014 2013-08-14 07:26:00
262490682 7170 563 Acd 2013-08-14 07:26:00
262490681 7170 564 08/14/2013 2013-08-14 07:26:00
251784 7170 564 09/06/2007 2007-09-08 00:00:00
250029 7170 562 MA 2007-09-08 00:00:00
248287 7170 563 asp 2007-09-08 00:00:00
251785 7170 564 09/07/2006 2006-09-08 00:00:00
248286 7170 563 asp 2006-09-08 00:00:00
250028 7170 562 MA 2006-09-08 00:00:00
251783 7170 564 09/06/2006 2006-09-06 00:00:00
249367 7170 562 LA 2006-09-06 00:00:00
248285 7170 563 asp 2006-09-06 00:00:00
3. Here is the table definition:
here is the table definition:
alter TABLE [dbo].[CustomS](
[CustomSID] [int] IDENTITY(1,1) NOT NULL,
[personID] [int] NOT NULL,
[attributeID] [int] NOT NULL,
[value] [varchar](256) NULL,
[date] [smalldatetime] NULL,
March 3, 2014 at 4:37 pm
SELECT person.personID
, cs562.personID
, cs562.attributeID
, cs562.value
, cs562.[date]
, cs563.personID
, cs563.attributeID
, cs563.value
, cs563.[date]
, cs564.personID
, cs564.attributeID
, cs564.value
, cs564.[date]
, cs565.personID
, cs565.attributeID
, cs565.value
, cs565.[date]
FROM Person cs562
INNER JOIN sProgram sp
ON cs562.PersonID = sp.PersonID
join programSelect on cs562.personID = Person.personID and cs562.attributeID= '562'
left join programSelect cs563 on cs563.personID = cs562.personID and cs563.[Date] = cs562.[date] and cs563.attributeID ='563'
left join programSelect cs564 on cs564.personID = cs563.personID and cs564.[date] = cs563.[Date] and cs564.attributeID ='564'
left join programSelect cs565 on cs565.personID = cs564.personID and cs565.[Date] = cs564.[date] and cs565.attributeID ='565'
GROUP BY
person.personID
, cs562.personID
, cs562.attributeID
, cs562.value
, cs562.[date]
, cs563.personID
, cs563.attributeID
, cs563.value
, cs563.[date]
, cs564.personID
, cs564.attributeID
, cs564.value
, cs564.[date]
, cs565.personID
, cs565.attributeID
, cs565.value
, cs565.[date]
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 3, 2014 at 5:26 pm
SQLRNNR (3/3/2014)
Jason is a man of few words and a code-talker to boot! 😛
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 3, 2014 at 5:59 pm
dwain.c (3/3/2014)
SQLRNNR (3/3/2014)
Jason is a man of few words and a code-talker to boot! 😛
LOL
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 3, 2014 at 8:05 pm
Thank you very much for your answer! It works fine.
Can you tell me what is wrong with the cte that I was trying to use?
March 4, 2014 at 7:04 am
Nothing wrong with the CTE that you were using from what I can tell. That said, I didn't do an intrusive code review either.
All that was needed was the addition of the JOIN statement to the CTE. Your code was lacking a JOIN to the CTE. Therefore, I added a JOIN statement as shown with this piece of code.
INNER JOIN sProgram sp
ON cs562.PersonID = sp.PersonID
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply