Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
swenri
Yak Posting Veteran
72 Posts |
Posted - 2013-07-31 : 10:03:49
|
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 |
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2013-07-31 : 10:21:56
|
CASE WHEN ReportID = 1 AND SigStatus = 1 THEN 'YES’ ELSE 'N' END AS AdmnSumSigned |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-31 : 10:23:43
|
quote: CASE ("BVReports"."ReportID" = 1) + ("BVReports"."SigStatus" WHEN 1 ) THEN 'YES’ ELSE 'N' END AS AdmnSumSigned
This syntax is incorrect. What is the logic you are trying to implement? Perhaps this?CASE WHEN "BVReports"."ReportID" = 1 AND "BVReports"."SigStatus" = 1 THEN 'YES' ELSE 'N' END AS AdmnSumSigned |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-07-31 : 10:25:31
|
[code]CASE WHEN BVReports.ReportID = 1 AND BVReports.SigStatus = 1 THEN 'YES' ELSE 'N'END AS AdmnSumSigned[/code] Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2013-07-31 : 10:29:01
|
Hmm, sure I answered this.. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-31 : 10:49:14
|
Hit the submit button before I saw your reply, RickD. I guess it was a case of what they affectionately call sniped |
|
|
swenri
Yak Posting Veteran
72 Posts |
Posted - 2013-07-31 : 11:38:14
|
Thank you very much :-) It worked |
|
|
|
|
|
|
|