July 15, 2009 at 11:51 am
I have 2 tables: a claim table and a corresponding date table. There can be multiple dates for each claim, with a qualifier type to distinguish them, and I'd like to retrieve them all in one row. Here's a snippet of what I have:
SELECT
d.PointerField
, a.ApplianceDate
b.AccidentDate
FROM
( select case when isdate(CLM_StartDate) = 1 then CLM_StartDate else '' end as ApplianceDate, Pointer from claimdetailsDates where Pointer= 2645 and clm_dateQualifier = '439') as a
cross join
( select case when isdate(CLM_StartDate) = 1 then CLM_StartDate else '' end as AccidentDate, Pointer from claimdetailsDates where Pointer= 2645 and clm_dateQualifier = '433') as b
INNER JOIN
TableD as d
-- on a.pointer = d.Pointer and b.Pointer = d.Pointer
where d.ID = 2645
I know I have a few things wrong..but I can't seem to figure it out!
Thanks!
July 15, 2009 at 12:11 pm
To help us help you, it'd really be a lot easier if we could get some sample data and expected results as per http://www.sqlservercentral.com/articles/Best+Practices/61537/.
Thanks,
-Luke.
July 15, 2009 at 12:12 pm
If you need each date in a separate column, and you know exactly how many date values you can have, then you can use a Pivot table to pivot the rows into columns. If you don't need them in separate columns, then you can write a function that will take as an input the claim ID, and return as an output a comma-delimited list of dates, then show those dates in one column.
If you need each date in a separate column, and you have a variable number of dates, then you can still do it - but you'll need to use a pivot table with a dynamic SQL query in order to make the arbitrary number of columns, and you should realize that you have a maximum number of columns that can exist in a query recordset(can't remember the number though)
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply