Tricky SQL/Rapport som skriver ut ant pr mnd

Morten,
Jeg plejer at lave en kalender tabel, med én row per dag. Så er det nemt at tælle antal dage og group'ere per måned.
mvh
Henrik

Kan du hjelpe meg litt igang?

Se http://www.cyren.no/diagram3.gif

lav en calender tabel, med et dato felt; adate
fyld denne tabel med records, een for hver dag i året, for alle de år du har data for.
select bid, year(adate), month(adate), count(distict calendar.adate)
from tblbiler b
inner join calendar c on b.opprettetdato >= c.adate and b.slettetdato <= c.adate
group by bid, year(adate), month(adate)
order by bid, year(adate), month(adate)

Mener du sånn?

Det som er litt tricky er hvis bilen ikke er sagt opp så skal den telle alle dager frem tom dagens dato.

Dvs: Hvis en bil er OppretteDato 01.01.2004 og ikke er sagt opp så er den aktiv
31 dager i januar
30 dagger i februar
31 dager i mars osv........ til å med idag.

***** SCRIPTET *********

SET dateformat dmy

Declare @datoFOM datetime
Declare @datoTOM datetime
Declare @period as datetime

-- Perioden rapporten skal kjøre på
Set @datoFOM = '01jan2004'
Set @datoTOM = '01feb2004'
Set @period = @datoFOM

If(object_id('tempdb.dbo.#calendar')<>1)
drop table #calendar

Select @period as adate
into #calendar
while @Period < @datoTOM
begin
  Select @Period = dateadd(day,1,@period)
  Insert into #calendar
  Select @period
End

SELECT BID, year(adate), month(adate), count(distict calendar.adate)
from tblBiler b
inner join Calendar c on b.opprettetdato >= c.adate and b.SlettetDato <= c.adate
group by bid, year(adate), month(adate)
order by bid, year(adate), month(adate)

Og må jeg ikke ha med noe lignende som dette?


CASE
  WHEN SiOppForsikringFra is null THEN DateDiff(d, GjelderFra, getdate())
  ELSE DateDiff(d, GjelderFra, SioppForsikringFra)
END AS DageSidenOprettelse

så de biler som ei er sagt opp har SioppForsikring som null?
Da må du gøre sånn:
inner join Calendar c on b.opprettetdato >= c.adate and coalesce(b.SlettetDato, getdate()) <= c.adate

Jeg ville ikke lage Calender som en Temporær tabell (#Calender). Den er alt for værdifull til ikke å ha som permanent tabell. Ta alle dager fra 1/1/1970 til 1/1/2070, og det er kun 36500 records. Fylder ingenting, og har mange anvendelser.

Hvordan får jeg opprettet tabellen calender så den legger inn alle data automatisk?

Dette virker ikke:

Declare @datoFOM datetime
Declare @datoTOM datetime
Declare @period as datetime

-- Perioden rapporten skal kjøre på
Set @datoFOM = '01jan1970'
Set @datoTOM = '01feb2070'
Set @period = @datoFOM

Select @period as adate
into calendar
while @Period < @datoTOM
begin
  Select @Period = dateadd(day,1,@period)
  Insert into calendar
  Select @period
End

Sorry det var en tabell som het calendar

Når jeg kjører:

select bid, year(adate), month(adate), count(distict calendar.adate)
from tblbiler b
inner join Calendar c on b.opprettetdato >= c.adate and coalesce(b.SlettetDato, getdate()) <= c.adate
group by bid, year(adate), month(adate)
order by bid, year(adate), month(adate)

Får jeg feilmeldingen:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'calendar'.

Hadde skrevet distinct feil:

select bid, year(adate), month(adate), count(distinct adate)
from tblBiler b
inner join Calendar c on b.opprettetdato >= c.adate and coalesce(b.SlettetDato, getdate()) <= c.adate
group by bid, year(adate), month(adate)
order by bid, year(adate), month(adate)

Får ingen treff på denne spørringen. Det er over 10.000 poster i denne db. Hva kan være feil?

Nå er jeg veldig mye nermere.

Men denne virker ikke: and coalesce(b.SiOppforsikringFra, getdate()) <= c.adate

select
BID,Regnr,ANavn,OpprettetDato,SiOppforsikringFra,
year(adate) as ÅR,
month(adate) as MÅNED,
count(distinct adate) as Antalldager

from tblBiler b
    inner join Calendar c on b.GjelderFra >= c.adate --and coalesce(b.SiOppforsikringFra, getdate()) <= c.adate
    inner join tblAvdeling a on b.FKBrAvdeling = a.AID
group by bid,Regnr,ANavn,OpprettetDato,SiOppforsikringFra, year(adate), month(adate)
order by bid,Regnr,ANavn,OpprettetDato,SiOppforsikringFra, year(adate), month(adate)

Deler point