Count days per month between to dates

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Count days per month between to dates

Author

Topic

mortency
Starting Member

4 Posts

Posted - 2006-01-16 : 07:05:00

Hi!

I have a problem with my sql. I have a table with to dates and i would like to make a count per months.

The data is
Regnr RegNo of the car
ANavn is department name
GjelderFra is the date the car will be active from
SiOppForsikringFra is the date the cars shouldent be active more from
Aktiv is if the car is active YES/NO

You may see the tabels her --> http://www.cyren.no/diagram3.gif

I want the output like this:

Department(ANavn)
JANUAR 2003
VF54423(RegNr) 21.01.2003(GjelderFra) 23.01.2003(SiOppforsikringFra) 3(AntDays)
VF54422(RegNr) 21.01.2003(GjelderFra) 23.03.2003(SiOppforsikringFra) 9(AntDays)
SUM JANUAR 12 days 2 cars

FEBRUARY 2003
VF54422(RegNr) 21.01.2003(GjelderFra) 23.03.2003(SiOppforsikringFra) 30(AntDays)
SUM JANUAR 30 days 1 car

MARS 2003
VF54422(RegNr) 21.01.2003(GjelderFra) 23.03.2003(SiOppforsikringFra) 23(AntDays)
SUM JANUAR 23 days 1 car

I have also made a sp but i get it wrong if it is a car with same regnr is it the same month, Can disable a car and reg it again..

CREATE PROCEDURE sp_aktive
(
@datoFOM datetime,
@datoTOM datetime,
@avdeling int
)
AS

Declare @period as datetime
Set @period = @datoFOM

if(object_id('tempdb.dbo.#temp')<>1)
drop table #temp

if(object_id('tempdb.dbo.#temp2')<>1)
drop table #temp2

--SET dateformat dmy

If(object_id('tempdb.dbo.#periods')<>1)
drop table #periods
Select @period as dato
into #periods
while @Period < @datoTOM
begin
Select @Period = dateadd(month,1,@period)
Insert into #periods
Select @period
End

SELECT fkbravdeling, a.SiOppForsikringFra,
case when year(b.dato) = year(gjelderFra) and month(b.dato) = month(gjelderFra) then
gjelderfra
else
b.dato
end as gjelderfra, a.Regnr
into #temp
FROM
cyren.tblbiler a
join
(select distinct cast('01' + datename(month, gjelderFra) + cast(year( gjelderFra) as varchar) as datetime) as dato from cyren.tblbiler) b
on b.dato between cast('01' + datename(month, gjelderFra) + cast(year( gjelderFra) as varchar) as datetime) and coalesce(SiOppForsikringFra,'01jan2050')
join #periods c
on b.dato = c.dato

select *
into #temp2
from
(
SELECT anavn,
sum(
case when month(SiOppForsikringFra) = month(gjelderfra) and year(SiOppForsikringFra) = year(gjelderfra)then
DateDiff(d, gjelderfra, SiOppForsikringFra) +1
else
DateDiff(d, gjelderfra,
dateadd(day, -1, '01' + datename(month, dateadd(month,1, gjelderfra)) + cast(year(dateadd(month,1, gjelderfra)) as varchar)) +1
)
end
) as AntDays,
'Antall aktive biler denne måned' as regnr, count(*) as AntCars,
year(gjelderfra) as [year], month(gjelderfra) as [month],
datename(month,gjelderfra) + ' ' + cast(year(gjelderfra) as varchar) as dato,
2 as [order]
FROM
#temp a join
cyren.tblAvdeling
on FKBRAvdeling=AID
WHERE fkbravdeling=@avdeling AND gjelderfra between @datoFOM and @datoTOM
group by anavn, month(gjelderfra), year(gjelderfra), datename(month,gjelderfra) + ' ' + cast(year(gjelderfra) as varchar)
) a
union
(
SELECT anavn,
case when month(SiOppForsikringFra) = month(gjelderfra) and year(SiOppForsikringFra) = year(gjelderfra)then
DateDiff(d, gjelderfra, SiOppForsikringFra) +1
else
DateDiff(d, gjelderfra,
dateadd(day, -1, '01' + datename(month, dateadd(month,1, gjelderfra)) + cast(year(dateadd(month,1, gjelderfra)) as varchar)) + 1
)
end AntDays,
Regnr, 1 as AntCars ,year(gjelderfra) as [year], month(gjelderfra) as [month],
datename(month,gjelderfra) + ' ' + cast(year(gjelderfra) as varchar) as dato,
1 as [order]
FROM
#temp a join
cyren.tblAvdeling b
on FKBRAvdeling=AID
WHERE fkbravdeling=@avdeling AND gjelderfra between @datoFOM and @datoTOM
)

SELECT a.*, BID, GjelderFra, SiOppForsikringFra, Aktiv
FROM #Temp2 a
left join cyren.tblbiler b
on a.regnr = b.regnr and b.Fkbravdeling=@avdeling--Denne er kun hvis den skal kjøres på avdeling
order by anavn, [year], [month], [order]
GO

mortency
Starting Member

4 Posts

Posted - 2006-01-16 : 07:17:14

You can see the output her: http://www.cyren.no/ods/ (Take a moment)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-16 : 08:02:30

Something like

Select datename(month,DateCol),count(*) from yourTable group by Datecol

Madhivanan

Failing to plan is Planning to fail

mortency
Starting Member

4 Posts

Posted - 2006-01-16 : 08:59:26

Hi madhivanan!

Thanks for helping me.

Do you mean like this:?

Select
datename(month,gjelderfra),count(*)
from tblbiler
group by gjelderfra

What is this counting of?

I need a count days of every cars in every month in a given period.

//Morten

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-17 : 01:41:55

Is this?

Select
datename(month,gjelderfra),count(*)
from tblbiler Where <condition>
group by gjelderfra

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources