SQL Server Get Operational days from Period

Question

I have the table Seasons:

YEAR    DATE_FROM    DATE_TO

2013    2013-04-21   2013-11-14

2014    2014-04-03   2014-12-03

I need a query that gets as parameters the @year,@month,@type and it returns for

@type=

1)the days that are contained within the period for the @month of the @year

2)the days that have passes since the beginning of the period and until the end of the @month of the @year

Examples:

@type=1, @month= 5, @year = 2013 should returns 31 days

@type=2, @month= 5, @year = 2013 should returns 40 days

Thanx in advance!

please, make your sample data synchronized to expected output. what your are asking is too confusing. — Jade, May 24 '14 at 10:03
conditions a & b have the same inputs, but different outputs. please edit your question to clarify — Stuart Ainsworth, May 24 '14 at 10:25

score 1 · Answer 1 · answered May 24 '14 at 12:19

You need to do two things. The first is to find the matching row. The second is to summarize as you want. I think the best way to summarize is by transforming @year and @month to the beginning of the month. Then add one month and subtract a day for the end of the month. I think the following captures the logic:

  select (case when @type = 1
               then datediff(day, thedate,
                             (case when datediff(day, date_from, dateadd(month, 1, thedate)) > date_to
                                   then dateadd(day, -1, datediff(day, date_from, dateadd(month, 1, thedate)) )
                                   else date_to
                              end) 
                             )
               when @type = 2
               then datediff(day, date_from, dateadd(month, 1, thedate)) - 1
          end)
  from seasons s cross join
       (select cast(cast(@year*100000  + @month*100 + 1 as varchar(255)) as date) as thedate
  where @year * 100 + @month between year(s.date_from) * 100 + month(s.date_from) and
                                     year(s.date_to) * 100 + month(s.date_to)

Thanx! The strange is that i get a Incorrect syntax near ')' for the last line although the code seems perfect — PanosPlat, May 24 '14 at 13:19

SQL Server Get Operational days from Period

1 Answers1