Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help with CASE and CAST in a query

Status
Not open for further replies.

swenri1

MIS
Jun 12, 2013
6
US
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top