query on date function 1

[tirf]

I am writing a stored procedure whcih accepts 2 date input parameters
@fromDate DATEIME
@toDate DATEIME
and returns a table of 3 columns

DispayDate, fromDate, toDAte

for example if

@fromDate = '01/20/2010
@toDate = '03/15/2010
then the return table should look like

DispayDate fromDate toDAte
Jan-10 01/20/2010 01/31/2010
Feb-10 02/01/2010 02/28/2010
Mar-10 03/01/2010 03/15/2010


The idea is we divid the days between start and end date based on month

hope my question is clear

Thanks

 

[simian336]

Are we always talking about 3 months or could it be 1 month or 6 months?

Simi

 

[markros]

Try

declare @FromDate date, @toDate date

select @fromDate = '01/20/2010', @toDate = '03/15/2010'
--then the return table should look like

;with cte as (select @FromDate as FromDate, 

cast(dateadd(day,-1,DATEADD(month, 1+datediff(month,'20100101', @FromDate),'20100101')) as date) as ToDate
union all
select cast(DATEADD(day,1, ToDate) as date) as FromDate,
cast(DATEADD(month, 1, ToDate)    as date)
as ToDate from cte where ToDate  <= @toDate 
)


select  LEFT(datename(month,FromDate),3) +'-' +
 RIGHT(cast(year(FromDate) as CHAR(4)),2) as DisplayDate, FromDate, 
case when ToDate > @toDate then @toDate else ToDate end as ToDate
 from cte --where ToDate < @toDate 

--DispayDate  fromDate      toDAte
--Jan-10      01/20/2010    01/31/2010
--Feb-10      02/01/2010    02/28/2010
--Mar-10      03/01/2010    03/15/2010

PluralSight Learning Library

 

[tirf]

Are we always talking about 3 months or could it be 1 month or 6 months?

it could be any month 1 0r 3 or any

 

[markros]

The original code was incorrect. I fixed it:

declare @FromDate date, @toDate date

select @fromDate = '01/20/2010', @toDate = '10/15/2010'
--then the return table should look like

;with cte as (select @FromDate as FromDate, 

cast(dateadd(day,-1,DATEADD(month, 1+datediff(month,'20100101', @FromDate),'20100101')) as date) as ToDate
union all
select cast(DATEADD(day,1, ToDate) as date) as FromDate,
cast(dateadd(day,-1,DATEADD(month, 1, cast(DATEADD(day,1, ToDate) as date)))    as date)
as ToDate from cte where ToDate  <= @toDate 
)


select  LEFT(datename(month,FromDate),3) +'-' +
 RIGHT(cast(year(FromDate) as CHAR(4)),2) as DisplayDate, FromDate, 
case when ToDate > @toDate then @toDate else ToDate end as ToDate
 from cte --where ToDate < @toDate 

--DispayDate  fromDate      toDAte
--Jan-10      01/20/2010    01/31/2010
--Feb-10      02/01/2010    02/28/2010
--Mar-10      03/01/2010    03/15/2010

PluralSight Learning Library

 

[tirf]

Thank you Markros, it works for me

 

[tirf]

Hello all,
I have a follow up question if you guys don't mind

I am trying to creat a report for a client

I have this Resultset

SubmissionType DisplayDate SubmissionCount

Initial JAN-10 64
Initial FEB-10 47
Amendment JAN-10 32
Amendment FEB-10 63


The report outcome should look like

Report Month1 Month2

SubmissionType JAN-10 FEB-10
Initial 64 47
Amendment 32 63

Thanks,

 

[markros]

Take a look at the PIVOT command. If the Types are not known in advance, then you need to use Dynamic Pivot.

Let me know if you'll have troubles completing it based on this suggestion, then I write the exact code.

PluralSight Learning Library

 

[tirf]

I tryed the dynamic pivot, my problem was how can I include the column names as resultset as well

please look at what the report should look like

Report Month1 Month2

SubmissionType JAN-10 FEB-10
Initial 64 47
Amendment 32 63


Thanks for your help

 

[markros]

If you only have 2 months always and you want your columns named Month1 and Month2, then you don't need dynamic PIVOT.

Try

;with cte as (Select SubmissionType, DisplayDate, SubmissionCount,
row_number() over (partition by SubmissionType order by [DateColumn]) as Row from Results) -- we need to order by the real date column, not display date

select 'SubmissionType' as SubmissionType,

min(case when Row = 1 then DisplayDate end) as Month1,
min(case when Row = 2 then DisplayDate end) as Month2

UNION ALL

select SubmissionType, cast(sum(case when Row = 1 then SubmissionCount else 0 end) as varchar(10)) as [Month1],
cast(sum(case when Row = 2 then SubmissionCount else 0 end)  as varchar(10)) as [Month2] from cte
group by SubmissionType

from the top of my head.

PluralSight Learning Library

 

[tirf]

The thing is The Number of months varies, it could be 1 or 2 or 5 or 10 but max is 60, our report is for max 5 years

how can we dynamically change base on the columns

Thanks again

 

[markros]

declare @SQL nvarchar(max), @ColsName nvarchar(max), @Cols
if object_ID('tempDb..#Temp','U') IS NOT NULL drop table #Temp
select distinct DisplayDate,
dense_rank() over (order by RealDate) as Row
into #Temp
from Results

select @ColsName = stuff((select ', ' + quotename(DisplayDate) + 
' as Month' + cast(Row as varchar(10))
from #Temp ORDER BY Row for XML PATH('')),1,2,'')

select @Cols = stuff((select ', ' + quotename(DisplayDate) + 
from #Temp ORDER BY Row for XML PATH('')),1,2,'')

set @SQL = 'select ''Sumbission Type '' as [SumbissionType],
' + @ColsName
+
'
UNION ALL 
select SubmissionType, ' + @ColsName + '
from (select SubmissionType, DisplayDate, SubmissionCount
from Results) src

 PIVOT (sum(SubmissionCount) FOR DisplayDate IN (' + @Cols + ')) pvt'

This is from the top of my head - may need some tweaking.



PluralSight Learning Library

 

[tirf]

Thanks, it is perfect