Opgøre antallet af aktive dage, per måned

Her er det sql jeg roder i lige nu (har ryddet det for overflødige ting)
select a.[Advertisement No_], a.[Starting Date], a.[Ending Date], b.[Date] from [Sales Datawarehouse] a inner join [Date view] b ON (a.[Starting Date] >= b.[Date] AND a.[Ending Date] <= b.[Date]) WHERE (a.[Starting Date] >= '2006-01-01' AND a.[Ending Date] >= '2006-01-01' AND a.[Ending Date] <= '2006-01-20' )

Tja, egentlig har jeg vel bare omskrevet din forespørgsel lidt, men da det ikke ser ud til, at der er andre der vil give deres besyv med, er her mit forslag:

SELECT d.[Date], s.[Advertisement No_], s.[Starting Date], s.[Ending Date]
FROM [Date view] d, [Sales Datawarehouse] s
WHERE s.[Ending Date] >= s.[Starting Date]
  AND d.[Date] BETWEEN '2006-01-01' and '2006-01-20'
  AND d.[Date] BETWEEN s.[Starting Date] AND s.[Ending Date]

Hmmm... måske har jeg misforstået spørgsmålet, men man burde herefter kunne foretage en GROUP BY på År/Måned, som giver antallet af dage. Forudsætter naturligvis, at Sales Datawarehouse kun indeholder én række pr. s.[Advertisement No_], s.[Starting Date], s.[Ending Date] - ellers går der kuk i det)...


SELECT YEAR(d.[Date]) AS [Årstal], MONTH(d.[Date]) AS [Måned], s.[Advertisement No_], s.[Starting Date], s.[Ending Date], COUNT(*) AS [Antal dage]
FROM [Date view] d, [Sales Datawarehouse] s
WHERE s.[Ending Date] >= s.[Starting Date]
  AND d.[Date] BETWEEN '2006-01-01' and '2006-01-20'
  AND d.[Date] BETWEEN s.[Starting Date] AND s.[Ending Date]
GROUP BY YEAR(d.[Date], MONTH(d.[Date]), s.[Advertisement No_], s.[Starting Date], s.[Ending Date]

Kan du ikke bruge DateDiff funktionen?

Unskyld, jeg skulle egentlig ikke have sendt ovenstående. Udfordringen bliver at generere en linie pr. måned, så kan du eventuelt bruge datediff, til at regne dage for første og sidste måned.

Hvis du laver en månedstabel, som angiver først og sidste dag i måneden. Derefter linker du din [Sales Datawarehouse] til din månedstabel, hvor første dag i annonceperioden er mindre end sidste dag i måenden, og sidste dag i annonceperioden er større en første dag i afsluttende måned.

Når du har et output som giver dig en række for hver måned i annonceperioden, kan du bruge datediff til at regne på antal dage i 'ikke hele' måneder, og eventuelt hente en værdi med antal dage i måeneden for hele måneder.

Jeg lavet en lille test, hvor jeg oprættet to tabeller, Annonce og Måneder.
Annoncetabellen indeholder et AnnonceID, samt start og slutdato. Dessuden lavet jeg en 'Måneder' tabel, som indeholder alle måneder , med FraDato, TilDato, og AntalDage. AntalDage er forhåndsberegnet, og angiver antal dage i hele måneden,

Derefter lavet jeg følgende forespørgsel, som ser ud til at fungere som jeg håbet:

select a.AnnonceID, m.Måned, a.StartDato, a.SLutDato,
    AntalDage = CASE
                    --Hele måneder
                    WHEN a.StartDato < m.FraDato and a.SlutDato >= m.TilDato
                            Then m.AntalDage
                    -- Start og slutdato indenfor samme måned
                    WHEN a.StartDato > m.FraDato and a.SlutDato <= m.TilDato
                            Then DateDiff(Day, a.StartDato, a.SlutDato)
                    -- Beregner antal dage i første måned
                    WHEN a.StartDato > m.FraDato and a.SlutDato > m.TilDato
                            Then DateDiff(Day, a.StartDato, dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,a.StartDato)+1, 0)))
                    -- Beregner antal dage i sidste måned
                    WHEN a.StartDato < m.FraDato and a.SlutDato <= m.TilDato
                            Then DateDiff(Day, DATEADD(mm, DATEDIFF(mm,0,a.SlutDato), 0), a.SlutDato) + 1
                    ELSE 1
                    END
from dbo.Annonce a ,dbo.MÅneder m
where a.StartDato <= m.TilDato
and    a.SlutDato >= m.FraDato

En lille rettelse med > ændret til >= for 'Start og slutdato indenfor samme måned':

select a.AnnonceID, m.Måned, a.StartDato, a.SLutDato,
    AntalDage = CASE
                    --Hele måneder
                    WHEN a.StartDato < m.FraDato and a.SlutDato >= m.TilDato
                            Then m.AntalDage
                    -- Start og slutdato indenfor samme måned
                    WHEN a.StartDato >= m.FraDato and a.SlutDato <= m.TilDato
                            Then DateDiff(Day, a.StartDato, a.SlutDato)
                    -- Beregner antal dage i første måned
                    WHEN a.StartDato > m.FraDato and a.SlutDato > m.TilDato
                            Then DateDiff(Day, a.StartDato, dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,a.StartDato)+1, 0)))
                    -- Beregner antal dage i sidste måned
                    WHEN a.StartDato < m.FraDato and a.SlutDato <= m.TilDato
                            Then DateDiff(Day, DATEADD(mm, DATEDIFF(mm,0,a.SlutDato), 0), a.SlutDato) + 1
                    ELSE 1
                    END
from dbo.Annonce a ,dbo.MÅneder m
where a.StartDato <= m.TilDato
and    a.SlutDato >= m.FraDato

Hvis dine datoer også indeholder klokkeslet, skal du lave noget SQL for at fjærne klokkeslet. Dette kan f.eks. gøres med:
select DATEADD(day, DATEDIFF(day, '20000101', dinDato), '20000101')

lorentsv: Tak for din kode! Nu har jeg ikke lige mulighed for at lave dine tabeller så jeg har ikke testet det, men det _virker_ umiddelbart som en stor omvej - jeg ved det ikke. Jeg fik det løst ved at joine på en tabel med datoer - så kunne jeg få det nøjagtige antal dage per måned.

kjulius: det var lige din BETWEEN ting jeg manglede - ved ikke hvorfor jeg ikke kunne huske den - det spiller nu, tak for hjælpen :-) (smider du et svar?)

Her er den sql jeg endte med at bruge:
Select a.[Advertisement No_], a.[Number], a.[Salesperson Code], CAST(a.[Net Amount] as int), a.[Advertiser Name], a.[Starting Date], a.[Ending Date],
COUNT(DISTINCT b.[Date]) as numdays, DATENAME(month, b.[Date]), YEAR(b.[Date])
FROM [Sales Datawarehouse] a
INNER JOIN [Date view] b ON (b.[Date] BETWEEN a.[Starting Date] AND a.[Ending Date] )
WHERE a.[Selling Publication] = 'INTDK' AND LEN(a.[Salesperson Code]) > 0 AND
(a.[Starting Date] <= '2006-01-01' AND a.[Ending Date] >= '2006-01-01' AND a.[Ending Date] <= '2006-12-31') AND a.[Net Amount] > 0 AND YEAR(b.[Date]) = '2006'
GROUP BY a.[Advertisement No_], a.[Number], a.[Salesperson Code], a.[Net Amount], a.[Advertiser Name], a.[Starting Date], a.[Ending Date],YEAR(b.[Date]) ,DATENAME(month, b.[Date]) ;

Fint at du har fået det til at virke.

Umiddelbart er jeg ikke enig at den måde jeg viser, er nogen omvej, da jeg vil tro at den vil være hurtigst, ved større mængde data.

Grunden er at Den vil behandle væsentlig færre rækker data, da joinen kun vil returnere antal annoncer * antal måneder. Din sql vil behandle et rækkeantal som antal annoncer * antal dage, og lave en efterfølgende GROUP BY.

Men hvis det ikke er tale om et stort antal annoncer, betyder performance måske ikke så meget.

Tja, der er som bekendt mange veje til Rom. :-)

jeg kan ikke se nogen performance problemer umiddelbart - der er lige over 300.000 rækker og den returnerer ca. 3200 rækker på 2 sekunder så det er fint performance. Jeg ville bare tro at en CASE er langsommere end en join/count men jeg ved det ikke. Jeg værdsætter dit svar, det er ikke det, men egen kode er oftest nemmest at arbejde videre med :-)

Tak for hjælpen begge 2.