CODEWITHSUNDEEP
sql-serversql-server-2005
user2527359
user2527359

Reputation: 43

Count of rows for 2 days

My data is in below format:

employee    order id   date   
a           123         01/06/2013
b           124         02/06/2013 
a           125         02/06/2013 
a           129         02/06/2013

I need the data in below format:

employee   day 1    day 2
a           1        2
b           0        1

Upvotes: 2

Views: 66

Answers (3)

Devart
Devart

Reputation: 121932

Try this one -

DECLARE @temp TABLE
(
      dtStart DATETIME
    , employees CHAR(1)
)

INSERT INTO @temp (employees, dtStart) VALUES('a','01/06/2013')
INSERT INTO @temp (employees, dtStart) VALUES('a','01/06/2013')
INSERT INTO @temp (employees, dtStart) VALUES('b','02/06/2013')

SELECT 
       employees
     , day1 = COUNT(CASE WHEN DAY(dtStart) = 1 THEN 1 END)
     , day2 = COUNT(CASE WHEN DAY(dtStart) = 2 THEN 1 END) 
FROM @temp
--WHERE dtStart BETWEEN '01/06/2013' AND '30/06/2013'
GROUP BY employees

Upvotes: 1

Dinup Kandel
Dinup Kandel

Reputation: 2505

select distinct employees, 
SUM(CASE WHEN dtStart = '01/06/2013' THEN 1 ELSE 0 END) as day1,
SUM(CASE WHEN dtStart = '02/06/2013' THEN 1 ELSE 0 END) as day2 
from yourTable
group by dtStart,employees

see your demo

Upvotes: 0

Sam
Sam

Reputation: 1366

Something along these lines should work (depending on whether you have a fixed amount of days or not):

select employee,
       SUM(CASE WHEN date = '01/06/2013' THEN 1 ELSE 0 END) as day1,
       SUM(CASE WHEN date = '02/06/2013' THEN 1 ELSE 0 END) as day2
from table
group by employee

Upvotes: 0

Related Questions