Count days per month and group by month
-
I have a problem with my sp. If a car is inactive you can create a new post withe the same regnr again. If this is happen and i does ofen, my sp get wrong.
The tables can you see here --> http://www.cyren.no/diagram3.gif
Here you can see the output(wrong) http://www.cyren.no/ods/
Can anybody help me?
Regards;
Mario
*************** MY SP ***********************
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
-
This was removed by the editor as SPAM
-
Hello Mario (or Morten?),
Unfortunately I don't understand what precisely the procedure should do and what is wrong with the result. PLease try to be more explicit about the desired result and the problems you encounter.
Diagram of a table is better than nothing, but it does not tell what columns are relevant for the query, what is their meaning and what values there can be. Best would be to post a CREATE statement for the table (including only columns that are necessary) and a few INSERT INTO statements to fill tables with data. Make sure you include such combination that will show the problem. Then, if you post, what your procedure does (result when run with the posted set of data) and what the result should be, we can create the environment on our server and look for a solution.
Also, I'd like to warn you, that the link to "wrong result" either starts the procedure on your server, or transmits data from some HUGE table, or there is some other problem why it hangs. I wouldn't advise anyone to click on this link, since it clutters up the communication channels and runs quite long (I canceled it after some time). It would be better to remove this link and post results based on a small set of data directly here - not as a link.
BTW, just being curious, what language is it? Danish? Swedish? Norwegian?
-
It's Norwegian, Vladan.
(quite similar to Swedish, a bit farther away from Danish)
/Kenneth
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply