CODEWITHSUNDEEP
sqlsql-serverdate
hotseetotsee
hotseetotsee

Reputation: 71

How to get number of days between date in a column

I have an employee schedule table that store the shift group and working day date. The table looks like below:

shift_group    schedule_date
------------------------------
Shift 1         2014-11-22 
Shift 1         2014-11-23 
Shift 1         2014-11-24 
Shift 1         2014-11-25 
Shift 1         2014-11-26 
Shift 1         2014-11-27 
Shift 1         2014-11-28 
Shift 1         2014-11-29 
Shift 1         2014-11-30 
Shift 2         2014-11-25 
Shift 2         2014-11-26 
Shift 2         2014-11-27 
Shift 2         2014-11-28 
Shift 2         2014-11-29 
Shift 2         2014-11-30
Shift 1         2014-12-01
Shift 1         2014-12-02
Shift 1         2014-12-03
Shift 1         2014-12-04
Shift 1         2014-12-05
Shift 1         2014-12-06
Shift 1         2014-12-07

All I found is DATEDIFF between two date columns. Is there any method to calculate the number of days for every shift? For example:

shift_group  month   number of days
------------------------------------
Shift 1        11         9 
Shift 1        12         7
Shift 2        11         6

Upvotes: 0

Views: 53

Answers (1)

Ed Bangga
Ed Bangga

Reputation: 13026

I think you are trying to get the number of shifts per month.

select shift_group  , datepart(month, schedule_date), count(1) from table
group by shift_group  , datepart(month, schedule_date)
order by shift_group

Upvotes: 1

Related Questions