Count day pr mont, year
-
This forum editor converted part of my post into a face graphic as below.
CREATE NONCLUSTERED INDEX IX_#DataRows ON #DataRows
(
MonthGroup, SiOppForsikringFra
ON [PRIMARY]When you look at your stored procedure there is nothing prior to the ON [PRIMARY]. There should be a close parenthesis as in the following:
CREATE NONCLUSTERED INDEX IX_#DataRows ON #DataRows
(
MonthGroup, SiOppForsikringFra
) ON [PRIMARY]
P.S. Your English is MUCH better than my French. No need to apologize.
-
Thanks!
Server: Msg 156, Level 15, State 1, Line 36
Incorrect syntax near the keyword 'Create'.
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
-
I dont get the message if i remove --ALTER TABLE #Months
-
The counts say that the tables are fine. I notice nothing wrong with your SQL related to 2004 being omitted. I have two unrelated comments, though. First, you should avoid SELECT ... INTO due to performance reasons. It is best to go to the trouble of manually creating the table and then coding an INSERT INTO ..... SELECT ...... Second, if you are only comparing one value, it would be best to use ISNULL instead of COALESCE.
What you need to do is focus on the following SELECT statement. Copy and paste it to its own Query Analyzer window and play with it there.
SELECT FKBrAvdeling, a.SiOppForsikringFra, b.dato, a.RegNr, BID, GjelderFra, Aktiv
FROM
marsh_HKL.tblhertzkorttidsleiebiler a
join
(select distinct cast('01' + datename(month, gjelderFra) + cast(year( gjelderFra) as varchar) as datetime) as dato from marsh_HKL.tblhertzkorttidsleiebiler) b
on b.dato between gjelderFra and coalesce(SiOppForsikringFra,'01jan2050')
I am guessing that this is where the 2004 rows are being omitted. Assuming that the above SQL omits the 2004 rows you will need to modify the JOIN condition until you identify the problem. Start by making it a LEFT JOIN so that the 2004 rows show up allowing you to see what the "dato" values are for the 2004 rows.
If the above SQL does NOT omit the 2004 rows then the problem is further down.
-
Sorry about that. The "ALTER TABLE #Months" line was something I started and then decided against. I was supposed to delete it but I forgot to. You should be fine.
-
It is the where statement who go against the "dato" field that generates the date with cars, but i cant see to solve the problem. It is this i must change. I Think......
If i just have to cars in the database
1 22 YT51294 26 234 1 2001 1,0 0 1 5000 187 01.01.2003 10 01.01.2003 JA
6386 22 CE59667 26 234 1 2001 1,0 0 1 5000 187 01.01.2004 10 01.12.2003 JA
Bold is GjelderFra.
The output gives just januar and desember 2003 not the other months between. I should give all months.
-
Good. I think we are almost done now.
I briefly mentioned a solution to this earlier on. I will provide better detail this time (I was in a hurry the last time).Create a table to hold every month you may be concerned with and populate it with the first of that month. Go about 50 years into the future so that you shouldn't have to constantly maintain the table. The table should be a single column of smalldatetime type. The initialization script below assumes the table is called MonthsTable and the column is called TheMonth. When you create the table, make TheMonth the primary key and cluster it. Change the start date to your earliest month.
set nocount on
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MonthsTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[MonthsTable]
GO
CREATE TABLE [dbo].[MonthsTable] (
[TheMonth] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MonthsTable] WITH NOCHECK ADD
CONSTRAINT [PK_MonthsTable] PRIMARY KEY CLUSTERED
(
[TheMonth]
) ON [PRIMARY]
GO
declare @TheMonth smalldatetime
set @TheMonth = '1/1/2000'
while @TheMonth < CONVERT(smalldatetime, '1/1/2050' )
begin
INSERT INTO MonthsTable VALUES ( @TheMonth )
set @TheMonth = DATEADD( month, 1, @TheMonth )
end
GO
Now that you have a table of every month you can use a set based operation to insert those months. The following assumes you are providing @dataFOM and @dataTOM. If not, you should SELECT the MIN month from #Months for @dataFOM and use the current month as @dataTOM.
INSERT INTO #Months
SELECT MonthsTable.TheMonth 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,
, 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 )
-
Hi Aron!
i have created the table.
Where shall i put this code?
INSERT INTO #Months
SELECT MonthsTable.TheMonth 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,
, 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 )
-
Hi again!
error:
Server: Msg 170, Level 15, State 1, Line 22
Line 22: Incorrect syntax near ','.
its her --> , AN.ANAvn
The code look like this now:
-- 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 MonthsTable.TheMonth 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,
, 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) ) )
+ ' 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) ) )
+ ' 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
-
Sorry!
Server: Msg 107, Level 16, State 2, Line 16
The column prefix 'AN' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 16
The column prefix 'AN' does not match with a table name or alias name used in the query.
SET dateformat dmy
Declare @datoFOM datetime
Declare @datoTOM datetime
-- The period
Set @datoFOM = '01.01.2003'
Set @datoTOM = '31.12.2004'
-- 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 MonthsTable.TheMonth 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
, AN.ANAvn
FROM marsh_HKL.tblhertzkorttidsleieAvdeling, 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) ) )
+ ' 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) ) )
+ ' 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
-
The new insert goes after the original insert. It is intended to fill the gaps that are left by the primary insert. The latest error you got was because you left out the table alias.
SET dateformat dmy
Declare @datoFOM datetime
Declare @datoTOM datetime
-- The period
Set @datoFOM = '01.01.2003'
Set @datoTOM = '31.12.2004'
-- 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, 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
, 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) ) )
+ ' 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) ) )
+ ' 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
-
Sorry that im no better than this.
Server: Msg 207, Level 16, State 3, Line 41
Invalid column name 'GjelderFra'.
-
Sorry about that. I made a copy/paste error. In the newest INSERT the "GjelderFra" needed to be "MonthsTable.TheMonth" in the big case statement. The following version should work. Alternatively you could add a MonthName column to the MonthsTable table. I considered that from the beginning. I'm not sure which version will perform better.
--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 )
-
Very close now
. The count of days and cars are extremly high. It is about over 2000 active cars every mont and that isent correct. is about 5 to 300 on each department. Do you know why it is counting so high?Morten
-
The SUM and COUNT are counting all cars for that month for ALL departments. The following two parts need to have another condition added to them.
SELECT SUM( AntDays ) FROM #DataRows DR WHERE DR.MonthGroup = Mth.MonthGroup OR ( DR.MonthGroup < Mth.MonthGroup AND ISNULL( DR.SiOppForsikringFra, GetDate() ) >= Mth.MonthGroup)
SELECT COUNT(*) FROM #DataRows DR WHERE DR.MonthGroup = Mth.MonthGroup OR ( DR.MonthGroup < Mth.MonthGroup AND ISNULL( DR.SiOppForsikringFra, GetDate() ) >= Mth.MonthGroup)
The above both need the following condition added:
AND DR.ANAvn = Mth.ANAvn
This change should give the following:
--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
Viewing 15 posts - 31 through 45 (of 45 total)
You must be logged in to reply to this topic. Login to reply