Help with CASE and CAST in a query

[swenri1]

Hi ,

I’ve a report where I would like to use the CASE statement with different columns in one. Is it possible to concatenate both the columns with the same data type integer.

Like for example in the report that I need to create I tried to use CASE statement as

CASE ("BVReports"."ReportID" = 1) + ("BVReports"."SigStatus" WHEN 1 ) THEN 'YES’ ELSE 'N' END AS AdmnSumSigned

**************************************************************************************************************************
Here is the query that I wrote.

SELECT "BLSession_Extended"."MRN",
"BLSession_Extended"."LastName",
"BLSession_Extended"."firstname",
"MO_Times"."ArchiveTime",
CASE "BVReports"."ReportID" WHEN 1 THEN 'YES' ELSE 'N' END AS AdmnSummary,
CASE "BVReports"."ReportID" WHEN 6061 THEN 'YES' ELSE 'N' END AS AnesPreop,
CASE "BVReports"."ReportID" WHEN 123 THEN 'YES' ELSE 'N' END AS AntPartumTest,
CASE "BVReports"."ReportID" WHEN 37 THEN 'YES' ELSE 'N' END AS DischSummary,
CASE ("BVReports"."ReportID" = 1 + ("BVReports"."SigStatus" WHEN 1 ) THEN 'YES' ELSE 'N' END AS AdmnSumSigned

FROM ("DatamartDB2"."dbo"."BLSession_Extended" "BLSession_Extended"
LEFT OUTER JOIN "DatamartDB2"."dbo"."MO_Times" "MO_Times"
ON "BLSession_Extended"."sessionID"="MO_Times"."SessionID")
LEFT OUTER JOIN "DatamartDB2"."dbo"."BVReports" "BVReports"
ON "BLSession_Extended"."sessionID"="BVReports"."Sessionid"
WHERE ("MO_Times"."ArchiveTime">={ts '2013-07-29 00:00:00'}
AND "MO_Times"."ArchiveTime"<{ts '2013-07-30 00:00:01'}
AND "BLSession_Extended"."FacilityID" = 0)

ORDER BY "BLSession_Extended"."MRN" DESC


The output should look as mentioned below.

MRN LastName FirstName Archive Time AdmnSummary AnesPreOp AntePartumTest DischSummary
1111 ABCD EFGH 07/30/213 1:01:55 AM YES YES No Yes
2222 XYZ LMNO 07/30/213 2:01:55 AM NO No Yes Yes


Can anybody help me please ??

Thank you

 

[swenri1]

solved the problem thank you