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 |
rnelsch
Starting Member
31 Posts |
Posted - 2013-01-29 : 09:43:51
|
Is it possible to use an alias to create a new calculated column. For example, I have the following query:SELECT COUNT(DISTINCT JOB.JobID) AS JobsRun, COUNT (DISTINCT CASE WHEN Job.SaleIsSold = 'True' THEN JOB.JobId END) as JobsSoldFROM JOBWHERE JOB.PrimaryEmp = '294' AND JOB.SchedDate between '12/27/2012' and '1/23/2013'And I'd like to ad a calculated column to get the difference between JobsRun and Jobs Sold so I tried the following but it doesn't work:SELECT COUNT(DISTINCT JOB.JobID) AS JobsRun, COUNT (DISTINCT CASE WHEN Job.SaleIsSold = 'True' THEN JOB.JobId END) as JobsSold,JobsRun - JobsSold as NotSoldFROM JOBWHERE JOB.PrimaryEmp = '294' AND JOB.SchedDate between '12/27/2012' and '1/23/2013'Ryan A Nelsch |
|
rnelsch
Starting Member
31 Posts |
Posted - 2013-01-29 : 09:46:27
|
Nevermind I fixed this with the following in case anyone was interested:SELECT COUNT(DISTINCT JOB.JobID) AS JobsRun, COUNT (DISTINCT CASE WHEN Job.SaleIsSold = 'True' THEN JOB.JobId END) as JobsSold, COUNT (DISTINCT CASE WHEN Job.SaleIsSold = 'False' THEN JOB.JobId END) as NotSoldFROM JOBWHERE JOB.PrimaryEmp = '294' AND JOB.SchedDate between '12/27/2012' and '1/23/2013'Ryan A Nelsch |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-29 : 09:46:54
|
you cant use aliases created in same level like this. it should beSELECT *,JobsRun - JobsSold as NotSoldFROM(SELECT COUNT(DISTINCT JOB.JobID) AS JobsRun,COUNT (DISTINCT CASE WHEN Job.SaleIsSold = 'True' THEN JOB.JobId END) as JobsSoldFROM JOBWHERE JOB.PrimaryEmp = '294'AND JOB.SchedDate between '12/27/2012' and '1/23/2013')t ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|