Выберите количество дней, в течение которых запись будет отображаться в течение определенного года / месяца

В sql-сервере у меня есть таблица с датами начала и окончания для публикации вакансий. В течение месяца и года от пользователя мне нужно найти количество дней между датой начала и окончания, которые выпадают в данном году / месяце для этой публикации.

Поэтому, если начальная дата проводки – 2010/11/15, а конечная дата – 2010/12/05, тогда вывод должен быть:

November 16 days December 5 days Total 21 days 

Я бил головой о стену с этим и был свежим из идей.

Это, наверное, самый краткий ответ.

 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) 

Он будет работать на диапазонах до 2048 дней (7-8 лет), но может быть расширен дольше, если вам нужно (только по запросу – это будет выглядеть более сложным).

Единственная причина для convert(char часть convert(char состоит в том, чтобы сделать ноябрь до декабря, а также до января следующего года.

Вы хотите использовать DateDiff

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

d выше будет считать дни.

Это довольно сложно в SQL, но это создаст таблицу с месяцем (в целых числах) и количеством дней.

Я оставлю это вам, чтобы преобразовать целое число в месяц и добавить общее число

 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: Еще одно небольшое исправление или два …

Отредактировано, чтобы дать полный ответ …

 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; 

И вот табличная версия, если вы хотите сделать это со многими сразу:

 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; 

Я немного упростил мои первоначальные запросы.

В следующем примере определяется разница между днями между текущей датой и датой заказа для продуктов в базе данных AdventureWorks.

 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

Давайте будем гением компьютера.