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-18 : 16:03:03
|
Gosh I feel like such a newb, but here is what I have so far:Select Count(Distinct Job.JobID) as JobsRun, Count (If (Job.SaleIsSold = 'True')) as JobsSold,From JobWhere Job.PrimaryEmp = '294' and Job.SchedDate between '12/27/12' and '1/16/13'I need to return two columns basically, 1 that shows the total of Jobs Run (JobID) and one that shows how many sold (SaleIsSold = True)This is from a SQL Server 2008 server. Not sure what other information is needed.Ryan A Nelsch |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-18 : 16:34:36
|
The syntax would be like shown below.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/12' AND '1/16/13' Couple of things to note:1. I added a distinct clause in the second count as well - not sure if your business logic calls for that or not.2. Is SaleIsSold a char or varchar type of column? If it is, you are ok, but if it is a bit column, instead of comparing to 'True' you should compare to 1. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-19 : 02:21:21
|
i would have written date range as belowJOB.SchedDate >= '20121227' AND JOB.SchedDate <= '20130116' if SchedDate contains time part it shoulde beJOB.SchedDate >= '20121227' AND JOB.SchedDate < '20130117' ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
subashseo
Starting Member
4 Posts |
Posted - 2013-01-19 : 07:16:08
|
unspammed |
|
|
|
|
|