CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Somashekhar
Somashekhar

Reputation: 513

How to get distinct values for the given date range in SQL Server

I have a table

employeeid WorkedDate Hours
   1        1/1/2013  8
   1        1/2/2013  8
   1        1/3/2013  8
   2        1/4/2013  8
   2        1/5/2013  8
   2        1/6/2013  8
   3        1/7/2013  8
   3        1/8/2013  8
   3        1/9/2013  8

I need output like below

   EmployeeCount    WorkedDate    TotalHours 
        1           1/1/2013           72
        0           1/2/2013           0
        0           1/3/2013           0
        1           1/4/2013           0
        0           1/5/2013           0
        0           1/6/2013           0
         1          1/7/2013           0
         0          1/8/2013           0
         0          1/9/2013           0

I don't want to group by Month or Year here. Is it possible to get output like above?

Upvotes: 0

Views: 1392

Answers (2)

Ravi Singh
Ravi Singh

Reputation: 2080

Try this. However you may need to generalize this. I could not as requirement is not clear.

;with cte1 as
(select min(workeddate) MIN_date from test group by employeeid),

cte2 as 
(select min_date  min_date from cte1
 except
 select min(min_date)from cte1)


select * from 
(select 1 EID,min(min_date) WORKDATE ,(select sum(hours) from test)Hours from cte1

union 

select 1,min_date , 0 from cte2
union
select 0,workeddate,0 from test where workeddate not in
    (select min_date from cte1))a
order by workdate

SQL Fiddle

Upvotes: 0

peterm
peterm

Reputation: 92795

It's hard to understand what you really want, but deducing from desired output, IMHO you just need to GROUP BY date

SELECT COUNT(employeeid) EmployeeCount,
       CAST(WorkedDate AS DATE) WorkedDate,
       SUM(Hours) Hours
  FROM Table1
 WHERE WorkedDate BETWEEN '2013-01-02' AND '2013-01-03'
 GROUP BY CAST(WorkedDate AS DATE)

Assuming that you have sample data like this

| EMPLOYEEID | WORKEDDATE | HOURS |
-----------------------------------
|          1 | 2013-01-01 |     8 |
|          1 | 2013-01-02 |     8 |
|          1 | 2013-01-03 |     8 |
|          2 | 2013-01-01 |     8 |
|          2 | 2013-01-02 |     8 |
|          2 | 2013-01-03 |     8 |
|          3 | 2013-01-01 |     8 |
|          3 | 2013-01-02 |     8 |
|          3 | 2013-01-03 |     8 |

This query will give you following output

| EMPLOYEECOUNT | WORKEDDATE | HOURS |
--------------------------------------
|             3 | 2013-01-02 |    24 |
|             3 | 2013-01-03 |    24 |

Here is a sqlfiddle example

Upvotes: 1

Related Questions