Count days between to dates group by month

Hi i have an database that looks like this:
http://www.cyren.no/diagram3.gif
And the output here http://www.cyren.no/ods
(Se picture)
I want to get an query where a user can search in a date range.
The output should be 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
The error is if a car has been made inactive and the make a new post
with the same regnr. Or the sp works fine...
CREATE PROCEDURE sp_aktive
(
@.datoFOM datetime,
@.datoTOM datetime,
@.avdeling int
)
AS
Declare @.period as datetime
Set @.period =3D @.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 =3D dateadd(month,1,@.period)
Insert into #periods
Select @.period
End
SELECT fkbravdeling, a.SiOppForsikringFra,
case when year(b.dato) =3D year(gjelderFra) and month(b.dato) =3D
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 =3D c.dato
select *
into #temp2
from
(
SELECT anavn,
sum(
case when month(SiOppForsikringFra) =3D month(gjelderfra) and
year(SiOppForsikringFra) =3D 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=E5ned' 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=3DAID
WHERE fkbravdeling=3D@.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) =3D month(gjelderfra) and
year(SiOppForsikringFra) =3D 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=3DAID
WHERE fkbravdeling=3D@.avdeling AND gjelderfra between @.datoFOM and
@.datoTOM
)
SELECT a.*, BID, GjelderFra, SiOppForsikringFra, Aktiv
FROM #Temp2 a
left join cyren.tblbiler b
on a.regnr =3D b.regnr and b.Fkbravdeling=3D@.avdeling--Denne er kun hvis
den skal kj=F8res p=E5 avdeling
order by anavn, [year], [month], [order]
GOOn 16 Jan 2006 05:04:07 -0800, web@.cyren.no wrote:

>Hi i have an database that looks like this:
>http://www.cyren.no/diagram3.gif
>And the output here http://www.cyren.no/ods
>(Se picture)
>
>I want to get an query where a user can search in a date range.
>
>The output should be like this:
(snip)
>The error is if a car has been made inactive and the make a new post
>with the same regnr. Or the sp works fine...
>CREATE PROCEDURE sp_aktive
(snip)
Hi web,
First: never use sp_ as the first three characters for your stored
procedure names. This prefixed is resserved for system stored
procedures. SQL Server will look for them in the master database first,
then in the current database. Apart from the performance hit, you'll run
into much more severe problems if MS adds a system stored procedure with
the same name in the next release or service pack...
Second: I don't understand what exactly it is you're asking. I've seen
the picture of the table structure, and the ~20,000 row report you've
posted links to (and I even have a feeling that I understand what some
of the Norwewgian words mean <g> ). But I fail to see what you want. And
the highly complex code in the stored proc you posted doesn't help
either.
A better way to describe your problem would be to post:
* CREATE TABLE statements for the tables involved. You may simplify them
by omitting irrelevant columns (and translating remaining column names
to English would not be a bad idea either), but please do include all
constraints and properties.
* INSERT statements for a few rows of sample data. The amount of data
should be enough to illustrate the problem you're trying to solve, but
not more. Of course, they should run against the CREATE TABLE statements
you post.
* Expected output (based on the posted sample data, of course).
* And a description of why the sample data has to yield the results you
posted - in other words, a description of the actual busniess problem
that you're trying to solve.
Check out www.aspfaq.com/5006 for some useful hints on how to assemble
the info for this follow-up post.
Hugo Kornelis, SQL Server MVP