Solved

Conditional SQL Statement

Posted on 2013-12-16
5
291 Views
Last Modified: 2014-01-27
I previously posted a question about a SQL Statement that had a CASE statement.  How would I do the following query and be able to not just do the case statement for isnull values but how about values that are not null as  when statement?  Is that possible?  Thanks!

ALTER PROCEDURE [dbo].[Rpt_Comp]  
SELECT DISTINCT 
 EmpCpsPlnDta.CpsGrp
 AS 'Comp Group',
 EmpCpsPlnDta.Fld1
 AS Unit,
 EmpCpsPlnDta.Fld1Name
 AS 'Unit Name',
 (Emp.Lastname + ', ' + Emp.Firstname)
 AS Name,
 Job.JobTitle,
 EmpCpsPlnDta.RecAmt
 AS 'Merit Increase Amount',
 (AnnSal*ExchangeRate)
 AS 'Current Annual Sal USD',
 ((select isnull(sum(round(sc.amt*ExchangeRate,2)),'') from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null))
 AS 'Current Components (USD)',
 ((select isnull(sum(round(sc.Mnthlyamt*ExchangeRate*12,2)),'') from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null) + AnnlSal*ExchangeRate)
 AS 'Current Components and Base (USD)',
 ((select isnull(sum(round(sc.NewmnthlyamtCZ*ExchangeRate,2)),'') from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null))
 AS 'Planned Components (USD)',
 ((eml.AnnlSal+EmpCpsPlnDta.RecAmt)*ExchangeRate)
 AS 'Proposed New Base (Merit) Salary USD',
 ((isnull(RecAmt,'')*ExchangeRate)/((AnnSal*ExchangeRate)+(select isnull(sum(round(sc.amt*ExchangeRate,2)),'') from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null))*100)
 AS 'Merit Increase Pct',
 (promoincreaseamt*ExchangeRate)
 AS 'Promo Increase Amt USD',
 (((isnull(RecAmt,'')*ExchangeRate)+(isnull(promoincreaseamt,'')*ExchangeRate))/((AnnSal*ExchangeRate)+(select isnull(sum(round(sc.amt*ExchangeRate,2)),'') from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null))*100)
 AS 'Total Increase Percent (Merit and Promo)',
 RptLoc.RptLocName
 AS 'Assigned Office',
 Eml.Div
 AS Region,
 (
	case when ((select isnull(sum(round(sc.NewmnthlyamtCZ*ExchangeRate,2)),'') from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null)) > 0 
	then ((select isnull(sum(round(sc.NewmnthlyamtCZ*ExchangeRate,2)),'') from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null))
	else ((select isnull(sum(round(sc.Mnthlyamt*ExchangeRate*12,2)),'') from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null) + AnnlSal*ExchangeRate)
	end
) as NewField 
FROM
 Emp
  LEFT JOIN Eml ON (Emp.Emp = Eml.Emp AND Eml.EmlEfdDt IS NULL AND Eml.EmlEfdDt IS NULL AND Emp.EmpEfdDt IS NULL) AND Eml.EmlEfdDt IS NULL AND Emp.EmpEfdDt IS NULL
  LEFT JOIN Job ON (Job.Job = Eml.Job AND Job.JobEfdDt IS NULL AND Job.JobEfdDt IS NULL) AND Job.JobEfdDt IS NULL
  LEFT JOIN Dpt ON (Dpt.Dpt = Eml.Dpt AND Dpt.Loc = Eml.Loc AND Dpt.DptEfdDt IS NULL AND Dpt.DptEfdDt IS NULL) AND Dpt.DptEfdDt IS NULL
  LEFT JOIN Org ON (Org.Org = Eml.Org AND Org.Dpt = Eml.Dpt AND Org.OrgEfdDt IS NULL AND Org.OrgEfdDt IS NULL) AND Org.OrgEfdDt IS NULL
  RIGHT JOIN Cty ON Cty.Cty = Emp.PrimCty AND Cty.CtyEfdDt IS NULL
  LEFT JOIN EmpCpsPlnDta AS EmpCpsPlnDta ON (EmpCpsPlnDta.Emp = Emp.Emp) AND EmpCpsPlnDta.EmpCpsPlnDtaEfdDt IS NULL
  RIGHT JOIN RptLoc ON (RptLoc.RptLoc = Eml.RptLoc AND RptLoc.Cmp = Eml.Cmp AND RptLoc.RptLocEfdDt IS NULL AND RptLoc.RptLocEfdDt IS NULL) AND RptLoc.RptLocEfdDt IS NULL
  RIGHT JOIN Cur ON Cur.Cur = Eml.Cur AND Cur.CurEfdDt IS NULL
  LEFT JOIN EmpSalCompTmp ON EmpSalCompTmp.Emp = Emp.Emp AND EmpSalCompTmp.EmpSalCompTmpEfdDt IS NULL
WHERE
 ((eml.empsts IN (N'Active',N'On Leave'))
AND
 EmpCpsPlnDta.CpsPlnYr = N'2014'
AND
 (EmpCpsPlnDta.Fld1 IN (N'IT ANZAME',N'IT Bus Servces',N'IT HelpDsk',N'IT Mgmt',N'IT SrvrHelpDsk',N'IT SteSuportUS',N'IT SysApps',N'IT UK')))
AND
 (Emp.EmpEfdDt IS NULL AND Eml.EmlEfdDt IS NULL AND Job.JobEfdDt IS NULL AND Dpt.DptEfdDt IS NULL AND Org.OrgEfdDt IS NULL AND Cty.CtyEfdDt IS NULL AND EmpCpsPlnDta.EmpCpsPlnDtaEfdDt IS NULL AND RptLoc.RptLocEfdDt IS NULL AND Cur.CurEfdDt IS NULL AND EmpSalCompTmp.EmpSalCompTmpEfdDt IS NULL)
ORDER BY
 EmpCpsPlnDta.CpsGrp,
 EmpCpsPlnDta.Fld1,
 (Emp.Lastname + ', ' + Emp.Firstname) 

Open in new window

0
Comment
Question by:VBBRett
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39722678
>How would I do the following query and be able to not just do the case statement for isnull values but how about values that are not null as  when statement?

do the query .. not just do ... how about

Don't know how to say this, but What the heck are you asking us?
0
 

Author Comment

by:VBBRett
ID: 39722705
LOL, sorry.  But in the following query, how would I set a condition as opposed to isnull I want it to be not null in the following line: case when((select isnull(sum(round(sc.NewmnthlyamtCZ*ExchangeRate,2)),'') from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null)) > 0

(
	case when ((select isnull(sum(round(sc.NewmnthlyamtCZ*ExchangeRate,2)),'') from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null)) > 0 
	then ((select isnull(sum(round(sc.NewmnthlyamtCZ*ExchangeRate,2)),'') from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null))
	else ((select isnull(sum(round(sc.Mnthlyamt*ExchangeRate*12,2)),'') from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null) + AnnlSal*ExchangeRate)
	end
) as NewField 

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 39722729
Can you post some sample result from your query and the expected result.
That may help in understanding your question.
0
 

Author Comment

by:VBBRett
ID: 39737765
I was basically asking how to do a case or if statement in my query.
0
 
LVL 41

Accepted Solution

by:
Sharath earned 500 total points
ID: 39760723
try this.
ALTER PROCEDURE [dbo].[Rpt_Comp]  as
SELECT DISTINCT 
 EmpCpsPlnDta.CpsGrp
 AS 'Comp Group',
 EmpCpsPlnDta.Fld1
 AS Unit,
 EmpCpsPlnDta.Fld1Name
 AS 'Unit Name',
 (Emp.Lastname + ', ' + Emp.Firstname)
 AS Name,
 Job.JobTitle,
 EmpCpsPlnDta.RecAmt
 AS 'Merit Increase Amount',
 (AnnSal*ExchangeRate)
 AS 'Current Annual Sal USD',
 isnull(t1.amt_res,0) 
 --((select isnull(sum(round(sc.amt*ExchangeRate,2)),'') from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null)) 
 AS 'Current Components (USD)',
 isnull(t1.Mnthlyamt_res,0) + (AnnlSal*ExchangeRate)
 --((select isnull(sum(round(sc.Mnthlyamt*ExchangeRate*12,2)),'') from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null) + AnnlSal*ExchangeRate)
 AS 'Current Components and Base (USD)',
 isnull(t1.NewmnthlyamtCZ,0) 
 --((select isnull(sum(round(sc.NewmnthlyamtCZ*ExchangeRate,2)),'') from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null))
 AS 'Planned Components (USD)',
 ((eml.AnnlSal+EmpCpsPlnDta.RecAmt)*ExchangeRate)
 AS 'Proposed New Base (Merit) Salary USD',
 ((isnull(RecAmt,'')*ExchangeRate)/((AnnSal*ExchangeRate)+ isnull(t1.amt_res,0))*100) 
 --(select isnull(sum(round(sc.amt*ExchangeRate,2)),'') from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null))*100)
 AS 'Merit Increase Pct',
 (promoincreaseamt*ExchangeRate)
 AS 'Promo Increase Amt USD',
 (((isnull(RecAmt,'')*ExchangeRate)+(isnull(promoincreaseamt,'')*ExchangeRate))/((AnnSal*ExchangeRate)+isnull(t1.amt_res,0))*100)
 --(select isnull(sum(round(sc.amt*ExchangeRate,2)),'') from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null))*100)
 AS 'Total Increase Percent (Merit and Promo)',
 RptLoc.RptLocName
 AS 'Assigned Office',
 Eml.Div
 AS Region,
 case when isnull(NewmnthlyamtCZ,0) > 0 then isnull(NewmnthlyamtCZ,0) else isnull(Mnthlyamt,0) end 
 /*(
	case when ((select isnull(sum(round(sc.NewmnthlyamtCZ*ExchangeRate,2)),'') from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null)) > 0 
	then ((select isnull(sum(round(sc.NewmnthlyamtCZ*ExchangeRate,2)),'') from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null))
	else ((select isnull(sum(round(sc.Mnthlyamt*ExchangeRate*12,2)),'') from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null) + AnnlSal*ExchangeRate)
	end
) */as NewField 
FROM
 Emp
  LEFT JOIN Eml ON (Emp.Emp = Eml.Emp AND Eml.EmlEfdDt IS NULL AND Eml.EmlEfdDt IS NULL AND Emp.EmpEfdDt IS NULL) AND Eml.EmlEfdDt IS NULL AND Emp.EmpEfdDt IS NULL
  LEFT JOIN Job ON (Job.Job = Eml.Job AND Job.JobEfdDt IS NULL AND Job.JobEfdDt IS NULL) AND Job.JobEfdDt IS NULL
  LEFT JOIN Dpt ON (Dpt.Dpt = Eml.Dpt AND Dpt.Loc = Eml.Loc AND Dpt.DptEfdDt IS NULL AND Dpt.DptEfdDt IS NULL) AND Dpt.DptEfdDt IS NULL
  LEFT JOIN Org ON (Org.Org = Eml.Org AND Org.Dpt = Eml.Dpt AND Org.OrgEfdDt IS NULL AND Org.OrgEfdDt IS NULL) AND Org.OrgEfdDt IS NULL
  RIGHT JOIN Cty ON Cty.Cty = Emp.PrimCty AND Cty.CtyEfdDt IS NULL
  LEFT JOIN EmpCpsPlnDta AS EmpCpsPlnDta ON (EmpCpsPlnDta.Emp = Emp.Emp) AND EmpCpsPlnDta.EmpCpsPlnDtaEfdDt IS NULL
  RIGHT JOIN RptLoc ON (RptLoc.RptLoc = Eml.RptLoc AND RptLoc.Cmp = Eml.Cmp AND RptLoc.RptLocEfdDt IS NULL AND RptLoc.RptLocEfdDt IS NULL) AND RptLoc.RptLocEfdDt IS NULL
  RIGHT JOIN Cur ON Cur.Cur = Eml.Cur AND Cur.CurEfdDt IS NULL
  LEFT JOIN EmpSalCompTmp ON EmpSalCompTmp.Emp = Emp.Emp AND EmpSalCompTmp.EmpSalCompTmpEfdDt IS NULL
  left join (select Emp,
       isnull(sum(round(sc.amt*ExchangeRate,2)),'') amt_res,
isnull(sum(round(sc.Mnthlyamt*ExchangeRate*12,2)),'') Mnthlyamt_res,
isnull(sum(round(sc.NewmnthlyamtCZ*ExchangeRate,2)),'')  NewmnthlyamtCZ_res
 from EmpSalCompTmp sc, Cur c 
 where  sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null
 group by  sc.Emp) t1 on  t1.Emp = Emp.Emp
WHERE
 ((eml.empsts IN (N'Active',N'On Leave'))
AND
 EmpCpsPlnDta.CpsPlnYr = N'2014'
AND
 (EmpCpsPlnDta.Fld1 IN (N'IT ANZAME',N'IT Bus Servces',N'IT HelpDsk',N'IT Mgmt',N'IT SrvrHelpDsk',N'IT SteSuportUS',N'IT SysApps',N'IT UK')))
AND
 (Emp.EmpEfdDt IS NULL AND Eml.EmlEfdDt IS NULL AND Job.JobEfdDt IS NULL AND Dpt.DptEfdDt IS NULL AND Org.OrgEfdDt IS NULL AND Cty.CtyEfdDt IS NULL AND EmpCpsPlnDta.EmpCpsPlnDtaEfdDt IS NULL AND RptLoc.RptLocEfdDt IS NULL AND Cur.CurEfdDt IS NULL AND EmpSalCompTmp.EmpSalCompTmpEfdDt IS NULL)
ORDER BY
 EmpCpsPlnDta.CpsGrp,
 EmpCpsPlnDta.Fld1,
 (Emp.Lastname + ', ' + Emp.Firstname) 

Open in new window

0

Featured Post

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question