Count day pr mont, year
-
Hi Again Aaron!
I have some problems with this query. Will you still help me?
In the test db i have following data (2 cars)
1 22 YT51294 26 234 1 2001 1,0 0 1 5000 196 2003-01-01 00:00:00.000 10 2003-01-01 00:00:00.000 NEI NULL NULL 2003-01-15 00:00:00.000 NULL NULL
6386 22 CE59667 26 234 1 2001 1,0 0 1 5000 196 2004-01-01 00:00:00.000 10 2003-01-01 00:00:00.000 JA NULL NULL 2003-02-15 00:00:00.000 NULL NULL
The output give this:
158 lines with the same result for each department(ANavn/FKBRAvdeling)
It copies january and February for alle departments.
************** SCRIPT ARE NOW LIKE THIS ***********
SET dateformat dmy
Declare @datoFOM datetime
Declare @datoTOM datetime
-- The period
Set @datoFOM = '01.01.2003'
Set @datoTOM = '28.02.2003'
-- Get a listing of every month to be summarized
if(object_id('tempdb.dbo.#Months')<>1)
drop table #Months
if(object_id('tempdb.dbo.#DataRows')<>1)
drop table #DataRows
Create table #Months ( MonthGroup smalldatetime, MonthName varchar(25), ANAvn varchar(50) )
INSERT INTO #Months
SELECT DISTINCT
CONVERT( smalldatetime, '01.'
+ CONVERT( varchar, DATEPART( month, GjelderFra ) )
+ '.'
+ CONVERT( varchar, DATEPART( year, GjelderFra) ))
AS MonthGroup,
case when DATEPART( month, GjelderFra ) = 1 then 'Januar'
when DATEPART( month, GjelderFra ) = 2 then 'Februar'
when DATEPART( month, GjelderFra ) = 3 then 'Mars'
when DATEPART( month, GjelderFra ) = 4 then 'April'
when DATEPART( month, GjelderFra ) = 5 then 'Mai'
when DATEPART( month, GjelderFra ) = 6 then 'Juni'
when DATEPART( month, GjelderFra ) = 7 then 'Juli'
when DATEPART( month, GjelderFra ) = 8 then 'August'
when DATEPART( month, GjelderFra ) = 9 then 'September'
when DATEPART( month, GjelderFra ) = 10 then 'Oktober'
when DATEPART( month, GjelderFra ) = 11 then 'November'
else 'Desember'
end AS MonthName
, ANAvn
FROM marsh_HKL.tblhertzkorttidsleiebiler, marsh_HKL.tblhertzkorttidsleieAvdeling
WHERE FKBRAvdeling=AID --AND GjelderFra between @datoFOM and @datoTOM
--Now fill in the missing months.
INSERT INTO #Months
SELECT MonthsTable.TheMonth AS MonthGroup,
case when DATEPART( month, MonthsTable.TheMonth ) = 1 then 'Januar'
when DATEPART( month, MonthsTable.TheMonth ) = 2 then 'Februar'
when DATEPART( month, MonthsTable.TheMonth ) = 3 then 'Mars'
when DATEPART( month, MonthsTable.TheMonth ) = 4 then 'April'
when DATEPART( month, MonthsTable.TheMonth ) = 5 then 'Mai'
when DATEPART( month, MonthsTable.TheMonth ) = 6 then 'Juni'
when DATEPART( month, MonthsTable.TheMonth ) = 7 then 'Juli'
when DATEPART( month, MonthsTable.TheMonth ) = 8 then 'August'
when DATEPART( month, MonthsTable.TheMonth ) = 9 then 'September'
when DATEPART( month, MonthsTable.TheMonth ) = 10 then 'Oktober'
when DATEPART( month, MonthsTable.TheMonth ) = 11 then 'November'
else 'Desember'
end AS MonthName
, AN.ANAvn
FROM marsh_HKL.tblhertzkorttidsleieAvdeling AN, MonthsTable
WHERE MonthsTable.TheMonth >= @datoFOM
AND MonthsTable.TheMonth <= @datoTOM
AND MonthsTable.TheMonth NOT IN ( SELECT MonthGroup FROM #Months Mth WHERE Mth.ANAvn = AN.ANAvn )
--Pull the data rows into a temporary table since we will be using the results as the 2nd logical row and then re-using the
--same results in subselects for the 3rd logical row. This is therefore being placed into a temporary table for both
--programming convenience and for performance improvement.
Create table #DataRows ( RowLabel varchar(100), BID int
, Regnr varchar(7), ANavn varchar(50), GjelderFra datetime
, SiOppForsikringFra datetime, Aktiv varchar(3), AntDays int
, MonthGroup datetime, RowType tinyint )
INSERT INTO #DataRows
SELECT DISTINCT
''AS RowLabel
, BID
, Regnr
, ANavn
, GjelderFra
, SiOppForsikringFra
, Aktiv
, CASE WHEN SiOppForsikringFra is null THEN
DateDiff(d, GjelderFra, getdate())
ELSE
DateDiff(d, GjelderFra, SioppForsikringFra)
END AS AntDays
, CONVERT( smalldatetime, '01.' + CONVERT( varchar, DATEPART( month, GjelderFra ) ) + '.' + CONVERT( varchar, DATEPART( year, GjelderFra ) )) AS MonthGroup
, 2 AS RowType
FROM marsh_HKL.tblhertzkorttidsleiebiler,
marsh_HKL.tblhertzkorttidsleieAvdeling
WHERE FKBRAvdeling=AID --AND GjelderFra between @datoFOM and @datoTOM
CREATE NONCLUSTERED INDEX IX_#DataRows ON #DataRows
(
MonthGroup, SiOppForsikringFra
) ON [PRIMARY]
--Get the 0th logical row (the department header)
SELECT CONVERT(varchar(100), 'Avdeling ' + ANAvn ) AS RowLabel
, CONVERT( int, 0 ) AS BID
, CONVERT( varchar(7), '' ) AS Regnr
, ANavn
, CONVERT( datetime, MonthGroup ) AS GjelderFra
, CONVERT( datetime, MonthGroup ) AS SiOppForsikringFra
, CONVERT( varchar(3), '' ) AS Aktiv
, CONVERT( int, 0 ) AS AntDays
, CONVERT( datetime, '1/1/1753' ) AS MonthGroup
, CONVERT( tinyint, 1 ) AS RowType
FROM #Months
UNION
--Get the 1st logical row (the month header)
SELECT CONVERT(varchar(100), MonthName + ' ' + CONVERT( varchar, DATEPART( year, MonthGroup ) ) ) AS RowLabel
, CONVERT( int, 0 ) AS BID
, CONVERT( varchar(7), '' ) AS Regnr
, Anavn
, CONVERT( datetime, MonthGroup ) AS GjelderFra
, CONVERT( datetime, MonthGroup ) AS SiOppForsikringFra
, CONVERT( varchar(3), '' ) AS Aktiv
, CONVERT( int, 0 ) AS AntDays
, MonthGroup AS MonthGroup
, CONVERT( tinyint, 1 ) AS RowType
FROM #Months
UNION
--Get the 2nd logical row ( all the actual data rows)
SELECT * FROM #DataRows
UNION
--Get the 3rd logical row ( summary data )
SELECT CONVERT(varchar(100), 'Totalt ' + MonthName
+ ' '
+ CONVERT( varchar, ( SELECT SUM( AntDays ) FROM #DataRows DR WHERE DR.MonthGroup = Mth.MonthGroup OR ( DR.MonthGroup < Mth.MonthGroup AND ISNULL( DR.SiOppForsikringFra, GetDate() ) >= Mth.MonthGroup) AND DR.ANAvn = Mth.ANAvn ) )
+ ' dager '
+ CONVERT( varchar, ( SELECT COUNT(*) FROM #DataRows DR WHERE DR.MonthGroup = Mth.MonthGroup OR ( DR.MonthGroup < Mth.MonthGroup AND ISNULL( DR.SiOppForsikringFra, GetDate() ) >= Mth.MonthGroup) AND DR.ANAvn = Mth.ANAvn ) )
+ ' biler '
) AS RowLabel
, CONVERT( int, 0 ) AS BID
, CONVERT( varchar(7), '' ) AS Regnr
, Anavn
, CONVERT( datetime, MonthGroup ) AS GjelderFra
, CONVERT( datetime, MonthGroup ) AS SiOppForsikringFra
, CONVERT( varchar(3), '' ) AS Aktiv
, CONVERT( int, 0 ) AS AntDays
, MonthGroup AS MonthGroup
, 1 AS RowType
FROM #Months Mth
ORDER BY ANavn, MonthGroup, RowType
if(object_id('tempdb.dbo.#Months')<>1)
drop table #Months
if(object_id('tempdb.dbo.#DataRows')<>1)
drop table #DataRows
Viewing post 46 (of 45 total)
You must be logged in to reply to this topic. Login to reply