Web Development

Ask something
Discussion Locked

Difficult Transact-SQL Query

0 helpful
7 replies

by bgibilaro

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hey there,

This is a problem I simply have not been able to wrap my brain around. First, the basics, I am

running MS SQL Server 2000 on Windows XP Pro for the client and Windows 2000 Server for the

server.

So here is my problem, I havea table that looks something like this:

------------------------------------
FILE_NO   | DATE_TYPE   |  DATE    |
------------------------------------

123       | typeA       |  10/23/02|

456       | typeA       |  10/21/02|

456    | typeB       |  10/25/02|

789       | typeA       |  10/12/02|

789       | typeB       |  10/24/02|

789       | typeC       |  10/24/02|
------------------------------------

What I want to do is write a select statement that will return the information in this format:

---------------------------------------------
FILE_NO   | typeA   |  typeB    |   typeC   |
---------------------------------------------

123       | 10/23/02|  NULL     |   NULL    |

456       | 10/21/02|  10/25/02 |   NULL    |

789       | 10/12/02|  10/24/02 |   10/26/02|
---------------------------------------------

So basically I want to know the dates of each date type that exist for each file number. Keep in
mind that in reality, my table contains thousands of rows, so performance will be a concern here.

Any help you can offer here would be greatly appreciated.

7 replies

by john_wills

SELECT File_No, typeA, typeB, typeC
FROM
((SELECT File_No, DATE AS typeA
FROM BigFile
WHERE Date_Type="typeA") AS FileA
LEFT JOIN
(SELECT File_No, DATE AS typeB
FROM BigFile
WHERE Date_Type="typeB") AS FileB
ON FileA.File_No=FileB.File_No) AS FileAB
LEFT JOIN
(SELECT File_No, DATE AS typeC
FROM BigFile
WHERE Date_Type="typeC") AS FileC
ON FileAB.File_No=FileC.File_No
)

I haven't tested this, but I'm pretty sure it will work.

by bgibilaro

John,

Thanks for the help. Unfortunately, this seems to be throwing me some errors in query analyzer. Here are the errors I receive:

Server: Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'AS'.
Server: Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'AS'.

And here is the code I am writing...

SELECT FILE_NO, sailDate, poiETA, destETA
FROM
((SELECT FILE_NO, DATE_TRACING_SHIPMENT AS sailDate
FROM SHIPMENT_DATES
WHERE TRACING_DATE_NO = 00001) AS FileA
LEFT JOIN
(SELECT FILE_NO, DATE_TRACING_SHIPMENT AS poiETA
FROM SHIPMENT_DATES
WHERE TRACING_DATE_NO = 00006) AS FileB
ON FileA.FILE_NO = FileB.FILE_NO) AS FileAB
LEFT JOIN
(SELECT FILE_NO, DATE_TRACING_SHIPMENT AS destETA
FROM SHIPMENT_DATES
WHERE TRACING_DATE_NO = 00011) AS FileC
ON FileAB.FILE_NO = FileC.FILE_NO
)

I have tried multiple combinations of the parentheses without any luck. Let me know if I am doing something wrong here.

by john_wills

WHERE Date_Type="typeA"). AS FileA
Try the above with the 3 quasi-tables in parentheses before the aliasing AS: the dot is needed in the Access dialect of SQL, so perhaps also in SQL Server 2000. It may further be necessary to use brackets [] instead of parentheses (); again, this is what happens in the Access dialect.

by medievaldude

I've tried the following, which works, and it's not too bad performance-wise: (in this case I only stored 'A', 'B', and 'C' values in my Date_Type field, but you can substitute in 'typeA', 'typeB', and 'typeC' to make it work for your example)

SELECT File_No,
CASE WHEN Date_Type = 'A' THEN
[Date]
ELSE
NULL
END AS TypeA,
CASE WHEN Date_Type = 'B' THEN
[Date]
ELSE
NULL
END AS TypeB,
CASE WHEN Date_Type = 'C' THEN
[Date]
ELSE
NULL
END AS TypeC
INTO #tmpPreFinal
FROM mytable
GROUP BY File_No, Date_Type, [Date]

SELECT File_No, MAX(TypeA), MAX(TypeB), MAX(TypeC)
FROM #tmpPreFinal
GROUP BY File_No

This discussion is closed to new replies.