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
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