Select the number of days a record is present within a certain year/month

In sql server I have a table with start and end dates for job postings. Given a month and a year by the user I need to find the amount of days between the start and end date that fall in the given year/month for that posting.

So if the posting Start Date is 2010/11/开发者_JAVA百科15 and End Date is 2010/12/05 Then output should be:

November  16 days
December  5 days
Total     21 days

I've been beating my head against the wall with this one and am fresh out of ideas.

This is probably the most concise answer.

declare @start datetime, @end datetime
select @start = '20101115', @end = '20101205'

select datename(month,@start+number), count(*)
from master..spt_values
where type='P'
  and number between 0 and datediff(d,@start,@end)
group by datename(month,@start+number), convert(char(6),@start+number,112)
order by convert(char(6),@start+number,112)

It will work for ranges up to 2048 days (7-8 years) but can by extended for longer if you need (on request only - that will looks more complicated).

The only reason for the convert(char portion is to make November come up before December, and also before January of the next year.

You want to use DateDiff

DateDiff("d",[StartDate],[EndDate])

the d above will count days.

This is kinda hard in SQL but this will create a table with the month (in integer) and the number of days.

I'll leave it to you to convert the integer to the month and add the total

SET NOCOUNT on

Declare @StartDate datetime
Declare @EndDate datetime
Declare @StartDateNormalized datetime
Declare @EndDateNormalized datetime


SET @StartDate = '2010/11/15' 
SET @EndDate = '2011/2/05'

declare @result table ( month int, days int)


--Normalize the Inputs

SET @StartDateNormalized = cast(Month(@startDate) as varchar) + '/1/' + cast(year(@startDate) as varchar)
SET @EndDateNormalized = cast(Month(@EndDate) as varchar) + '/1/' + cast(year(@EndDate) as varchar)

insert into @result 
values 
(   MONTH(@StartDateNormalized),
    DateDiff(Day, @StartDate, DateAdd(month, 1, @StartDateNormalized ) )
)

SET @StartDateNormalized = DateAdd(month, 1, @StartDateNormalized ) 

WHILE (@StartDateNormalized < @EndDateNormalized)
BEGIN

insert into @result 
values 
(   MONTH(@StartDateNormalized),
   DateDiff(Day, @StartDateNormalized, DateAdd(month, 1, @StartDateNormalized ) )  
)

   SET @StartDateNormalized = DateAdd(month, 1, @StartDateNormalized ) 
END

insert into @result 
values 
(   MONTH(@EndDateNormalized),
   DateDiff(Day, @EndDateNormalized, @EndDate    ) + 1 
)


select * from @result

EDIT: Another minor fix or two...

Edited to give the full answer...

declare @user_start_date datetime
set @user_start_date = '1/1/2011'
declare @user_end_date datetime
set @user_end_date = '1/10/2011'
declare @job_start_date datetime
set @job_start_date = '1/2/2011'
declare @job_end_date datetime
set @job_end_date = '1/11/2011'

declare @nextStartDate datetime;
set @nextStartDate = str(datepart(mm, @user_start_date)) + '/1/' + str(datepart(yyyy, @user_start_date))
declare @nextEndDate datetime;
set @nextEndDate = dateadd(dd,-1,dateadd(mm,1,@nextStartDate))

create table #monthYears(startDate datetime, endDate datetime)
while (@nextStartDate < @user_end_date) begin
    insert into #monthYears values(@nextStartDate, @nextEndDate)
    set @nextStartDate = dateadd(mm,1,@nextStartDate)
    set @nextEndDate = dateadd(dd,-1,dateadd(mm,1,@nextStartDate))
end

-- Print Months
select [month], [year], case when dayCount < 0 then 0 else dayCount end from (
select month(startDate) month, year(startDate) year , datediff(dd, 
    case when startDate > @job_start_date then startDate else @job_start_date end, 
    case when endDate < @job_end_date then endDate else @job_end_date end) dayCount
from #monthYears) temp

select datediff(dd, 
    case when @user_start_date > @job_start_date then @user_start_date else @job_start_date end, 
    case when @user_end_date < @job_end_date then @user_end_date else @job_end_date end)

DECLARE
   @StartDate datetime,
   @EndDate datetime;
SET @StartDate = '20101115';
SET @EndDate = '20101205';

WITH Mos AS (
   SELECT
      Number,
      DateAdd(Month, Number, @StartDate - Day(@StartDate) + 1) MoDate
   FROM master.dbo.spt_values
   WHERE
      Type = 'P'
      AND Number <= DateDiff(Month, @StartDate, @EndDate)
), Dys AS (
   SELECT
      MoDate,
      DateDiff(
         Day,
         CASE WHEN Number = 0 THEN @StartDate ELSE MoDate END,
         CASE WHEN Number = DateDiff(Month, @StartDate, @EndDate) THEN @EndDate ELSE DateAdd(Month, 1, MoDate) - 1 END
      ) + 1 Cnt
   FROM Mos
)
SELECT
   Year(MoDate) Yr,
   Coalesce(DateName(Month, MoDate), 'Total') Mo,
   Convert(varchar(11), Sum(Cnt)) + ' day' + CASE WHEN Sum(Cnt) = 1 THEN '' ELSE 's' END Descr
FROM Dys
GROUP BY MoDate
WITH ROLLUP
ORDER BY Grouping(MoDate), MoDate;

And here's a table version in case you wanted to do it with many at once:

CREATE TABLE AccountDates (
   AccountCode varchar(10) NOT NULL CONSTRAINT PK_AccountDates PRIMARY KEY CLUSTERED,
   StartDate datetime,
   EndDate datetime
);

INSERT AccountDates VALUES ('BLINKEN', '20101115', '20101205');
INSERT AccountDates VALUES ('KRAM', '20101027', '20110118');
INSERT AccountDates VALUES ('NUVU', '20101207', '20101207');

WITH Mos AS (
   SELECT
      AccountCode,
      D.StartDate,
      D.EndDate,
      Number,
      DateAdd(Month, Number, D.StartDate - Day(D.StartDate) + 1) MoDate
   FROM
      AccountDates D
      INNER JOIN master.dbo.spt_values V ON V.Number <= DateDiff(Month, D.StartDate, D.EndDate)
   WHERE
      V.Type = 'P'
), Dys AS (
   SELECT
      AccountCode,
      MoDate,
      DateDiff(
         Day,
         CASE WHEN Number = 0 THEN StartDate ELSE MoDate END,
         CASE WHEN Number = DateDiff(Month, StartDate, EndDate) THEN EndDate ELSE DateAdd(Month, 1, MoDate) - 1 END
      ) + 1 Cnt
   FROM Mos
)
SELECT
   AccountCode,
   Year(MoDate) Yr,
   Coalesce(DateName(Month, MoDate), 'Total') Mo,
   Convert(varchar(11), Sum(Cnt)) + ' day' + CASE WHEN Sum(Cnt) = 1 THEN '' ELSE 's' END Descr
FROM Dys
GROUP BY AccountCode, MoDate
WITH ROLLUP
HAVING Grouping(AccountCode) = 0
ORDER BY
   AccountCode,
   Grouping(MoDate),
   MoDate;

I simplified things a little from my original query.

The following example determines the difference in days between the current date and the order date for products in the AdventureWorks database.

USE AdventureWorks;
GO
SELECT DATEDIFF(day, OrderDate, GETDATE()) AS NumberOfDays
FROM Sales.SalesOrderHeader;
GO

http://msdn.microsoft.com/en-us/library/ms189794%28v=sql.90%29.aspx