Count day pr mont, year
-
Super!
Now i see some other small thing. It is stopping after year 2003 it should continue to the date today.
Thanks, Master Aaron
//Morten
-
That is somehow related to the following FROM/WHERE you provided.
FROM marsh_HKL.tblhertzkorttidsleiebiler,
marsh_HKL.tblhertzkorttidsleieAvdeling
WHERE FKBRAvdeling=AID
Add a "SELECT DISTINCT GjelderFra" in front of it. You should notice the same symptoms (stopping after 2003). My first guess is that the 2004 items do not yet have rows in the marsh_HKL.tblhertzkorttidsleieAvdeling table. Because you are doing a full join rows must exist in both tables for either to be reported. Converting to a LEFT JOIN may resolve the issue.
FROM marsh_HKL.tblhertzkorttidsleiebiler,
LEFT JOIN marsh_HKL.tblhertzkorttidsleieAvdeling
ON FKBRAvdeling=AID
With the above approach there isn't even a WHERE clause needed at all anymore.
Since ANAvn seems rather important to me and not somthing that can be allowed to be NULL, should the above return the 2004 rows you likely have a data integrity problem in your tables that needs to be resolved.
-
ANavn cannot be null see http://www.cyren.no/diagram3.gif
Shall i only put the
SELECT DISTINCT GjelderFra FROM marsh_HKL.tblhertzkorttidsleiebiler,
LEFT JOIN marsh_HKL.tblhertzkorttidsleieAvdeling
ON FKBRAvdeling=AID --AND GjelderFra BETWEEN @datoFOM and @dataTOM
in the first select or both?
With this i get error:
*******************************
Incorrect syntax near the keyword 'LEFT'
*******************************
-
I didn`t see the ,
But where shall i put the SELECT DISTINCT GjelderFra?
Morten
-
SELECT DISTINCT GjelderFra
FROM marsh_HKL.tblhertzkorttidsleiebiler
LEFT JOIN marsh_HKL.tblhertzkorttidsleieAvdeling
ON FKBRAvdeling=AID
It is exactly what you had minus the comma you noticed. When you look at the results (DISTINCT will implicitely sort it for you) I am guessing that the above version will have 2004 in it, but the previous version (using a full join and a WHERE clause) will not have 2004.
As for ANAvn never being NULL, it WILL be null if there is a FKBRAvdeling value that does not have a matching AID value. Since anything compared to NULL is always false, the WHERE condition filtered them out. However, with a LEFT JOIN, the non-matching rows will survive with each tblhertzkorttidsleieAvdeling column being SELECTed out as NULL. You could effectively turn the LEFT JOIN into a full join by adding a "WHERE AID IS NOT NULL" condition to the end.
-
When i have this i get many errors:
Server: Msg 207, Level 16, State 3, Line 16
Invalid column name 'GjelderFra'.
Server: Msg 207, Level 16, State 1, Line 16
Invalid column name 'GjelderFra'.
Server: Msg 207, Level 16, State 1, Line 16
Invalid column name 'GjelderFra'.
Server: Msg 207, Level 16, State 1, Line 16
Invalid column name 'GjelderFra'.
Server: Msg 207, Level 16, State 1, Line 16
Invalid column name 'GjelderFra'.
Server: Msg 207, Level 16, State 1, Line 16
Invalid column name 'GjelderFra'.
Server: Msg 207, Level 16, State 1, Line 16
Invalid column name 'GjelderFra'.
Server: Msg 207, Level 16, State 1, Line 16
Invalid column name 'GjelderFra'.
Server: Msg 207, Level 16, State 1, Line 16
Invalid column name 'GjelderFra'.
Server: Msg 207, Level 16, State 1, Line 16
Invalid column name 'GjelderFra'.
Server: Msg 207, Level 16, State 1, Line 16
Invalid column name 'GjelderFra'.
Server: Msg 207, Level 16, State 1, Line 16
Invalid column name 'GjelderFra'.
Server: Msg 207, Level 16, State 1, Line 16
Invalid column name 'GjelderFra'.
Server: Msg 207, Level 16, State 1, Line 16
Invalid column name 'ANAvn'.
--CREATE PROCEDURE sp_ActiveCarsPrMonth
--SET dateformat dmy
--(
--Declare @datoFOM datetime
--Declare @datoTOM datetime
--)
--AS
-- 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
SELECT DISTINCT GjelderFra FROM marsh_HKL.tblhertzkorttidsleiebiler
LEFT JOIN marsh_HKL.tblhertzkorttidsleieAvdeling
ON FKBRAvdeling=AID --AND GjelderFra between @datoFOM and @datoTOM
--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
--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 ASC
--drop table #Months
--drop table #DataRows
GO
-
I was talking about running that SELECT by itself from Query Analyzer strictly for the purpose of problem identification. Should that reveal the missing dates then your tblhertzkorttidsleieAvdeling table is probably out of balance. To help confirm it run it again with the origin FROM/WHERE clause. If it does NOT reveal the missing dates then I simply have to assume that they don't exist within tblhertzkorttidsleiebiler.
The good news is, I now understand why you didn't know where to put the SELECT line.
So, from an empty Query Analyzer window start with the following two COUNTs. The COUNTs SHOULD BE the same. If not then the tables are out of balance.
SELECT COUNT (DISTINCT GjelderFra)
FROM marsh_HKL.tblhertzkorttidsleiebiler
LEFT JOIN marsh_HKL.tblhertzkorttidsleieAvdeling
ON FKBRAvdeling=AID
SELECT COUNT (DISTINCT GjelderFra)
FROM marsh_HKL.tblhertzkorttidsleiebiler, marsh_HKL.tblhertzkorttidsleieAvdeling
WHERE FKBRAvdeling=AID
If the counts are different you can identify the rows within tblhertzkorttidsleiebiler which have a FKBRAvdeling that does not have a corresponding AID within tblhertzkorttidsleieAvdeling using the following statement.
SELECT *
FROM marsh_HKL.tblhertzkorttidsleiebiler
WHERE BID NOT IN ( SELECT BID
FROM marsh_HKL.tblhertzkorttidsleiebiler, marsh_HKL.tblhertzkorttidsleieAvdeling
WHERE FKBRAvdeling=AID )
If the counts are the same then I'll be a little confused. Make sure there ARE 2004 dates within the tblhertzkorttidsleiebiler table.
-
The error now is
Server: Msg 207, Level 16, State 1, Line 16
Invalid column name 'GjelderFra'.
Server: Msg 207, Level 16, State 1, Line 16
Invalid column name 'ANAvn'.
-
Somehow you are not running the last SQL I gave you. I know that because ANAvn isn't in it anywhere. Do not put this SQL into the stored procedure at all. It is only for problem analysis and should be run all by itself. Leave the SP alone for now.
-
Hi!
I run this i query an...
-- 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
SELECT DISTINCT GjelderFra FROM marsh_HKL.tblhertzkorttidsleiebiler
LEFT JOIN marsh_HKL.tblhertzkorttidsleieAvdeling
ON FKBRAvdeling=AID --AND GjelderFra between @datoFOM and @datoTOM
--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
--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 ASC
Then i get
Server: Msg 207, Level 16, State 3, Line 22
Invalid column name 'GjelderFra'.
Server: Msg 207, Level 16, State 1, Line 22
Invalid column name 'GjelderFra'.
Server: Msg 207, Level 16, State 1, Line 22
Invalid column name 'GjelderFra'.
Server: Msg 207, Level 16, State 1, Line 22
Invalid column name 'GjelderFra'.
Server: Msg 207, Level 16, State 1, Line 22
Invalid column name 'GjelderFra'.
Server: Msg 207, Level 16, State 1, Line 22
Invalid column name 'GjelderFra'.
Server: Msg 207, Level 16, State 1, Line 22
Invalid column name 'GjelderFra'.
Server: Msg 207, Level 16, State 1, Line 22
Invalid column name 'GjelderFra'.
Server: Msg 207, Level 16, State 1, Line 22
Invalid column name 'GjelderFra'.
Server: Msg 207, Level 16, State 1, Line 22
Invalid column name 'GjelderFra'.
Server: Msg 207, Level 16, State 1, Line 22
Invalid column name 'GjelderFra'.
Server: Msg 207, Level 16, State 1, Line 22
Invalid column name 'GjelderFra'.
Server: Msg 207, Level 16, State 1, Line 22
Invalid column name 'GjelderFra'.
Server: Msg 207, Level 16, State 1, Line 22
Invalid column name 'ANAvn'.
when i run the three other querys i get
13, 13 an null posts
-
I dont get the error if i change
SELECT DISTINCT GjelderFra FROM marsh_HKL.tblhertzkorttidsleiebiler
LEFT JOIN marsh_HKL.tblhertzkorttidsleieAvdeling
ON FKBRAvdeling=AID --AND GjelderFra between @datoFOM and @datoTOM
to
FROM marsh_HKL.tblhertzkorttidsleiebiler
LEFT JOIN marsh_HKL.tblhertzkorttidsleieAvdeling
ON FKBRAvdeling=AID --AND GjelderFra between @datoFOM and @datoTOM
-
stop changing the stored procedure. Do nothing with the stored procedure. If you wish the SP to be functional at all for now, change the SP back to:
FROM marsh_HKL.tblhertzkorttidsleiebiler,
marsh_HKL.tblhertzkorttidsleieAvdeling
WHERE FKBRAvdeling=AID --AND GjelderFra between @datoFOM and @datoTOM
But more importantly, run the SQL I gave you ALL BY ITSELF. NOT inside the stored procedure at all. Just get the counts and compare them.
-
I didn't notice you reported the counts at the end of your post a couple posts back. 13 seems really small considering you mentioned there are over 15000 cars. Were those numbers with the between code added in? I was attempting to detect year 2004 rows since you reported earlier that they were being omitted. I've corrected the SQL below by getting rid of the SELECT DISTINCT line and changing the FROM/WHERE clause back to the original. I also added a CREATE INDEX statement to help with performance. I also copied the DROP statements to the bottom since you should always DROP temp tables immediately when done with them.
-- 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
ALTER TABLE #Months
--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
-
An new error become:
Server: Msg 156, Level 15, State 1, Line 36
Incorrect syntax near the keyword 'Create'.
Server: Msg 156, Level 15, State 1, Line 62
Incorrect syntax near the keyword 'ON'.
Morten
-
The posts when i run...
SELECT COUNT (DISTINCT GjelderFra)
FROM marsh_HKL.tblhertzkorttidsleiebiler
LEFT JOIN marsh_HKL.tblhertzkorttidsleieAvdeling
ON FKBRAvdeling=AID
THIS IS 383
SELECT COUNT (DISTINCT GjelderFra)
FROM marsh_HKL.tblhertzkorttidsleiebiler, marsh_HKL.tblhertzkorttidsleieAvdeling
WHERE FKBRAvdeling=AID
THIS IS 383
SELECT *
FROM marsh_HKL.tblhertzkorttidsleiebiler
WHERE BID NOT IN ( SELECT BID
FROM marsh_HKL.tblhertzkorttidsleiebiler, marsh_HKL.tblhertzkorttidsleieAvdeling
WHERE FKBRAvdeling=AID )
AND THIS IS EMPTY
I have an other script that is counting everything right but it is not taking all the years with it(only 2003). is this script bad or not? Could you check this? I think i have to change the where statment but im not shure?
Sorry my bad english.....
Script as follow..
SET dateformat dmy
Declare @datoFOM datetime
Declare @datoTOM datetime
Set @datoFOM = '01.01.2003'
Set @datoTOM = '08.10.2004'
if(object_id('tempdb.dbo.#temp')<>1)
drop table #temp
SELECT FKBrAvdeling, a.SiOppForsikringFra, b.dato, a.RegNr, BID, GjelderFra, Aktiv
into #temp
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')
select * from
(
SELECT ANavn,SiOppForsikringFra, Aktiv, GjelderFra,
sum(
case when month(SiOppForsikringFra) = month(dato) and year(SiOppForsikringFra) = year(dato)then
DateDiff(d, dato, SiOppForsikringFra)
else
DateDiff(d, dato,
dateadd(day, -1, '01' + datename(month, dateadd(month,1, dato)) + cast(year(dateadd(month,1, dato)) as varchar))
)
end
) as AntDays,
'Totalt' as RegNr, count(*) as AntCars,
year(dato) as [Year], month(dato) as [Month],
datename(month,dato) + ' ' + cast(year(dato) as varchar) as Dato,
2 as [Order]
FROM
#temp a join
marsh_HKL.tblhertzkorttidsleieAvdeling
on FKBRAvdeling=AID
--This is so i can make an report on a spesified time.
--WHERE dato between @datoFOM and @datoTOM
--This is so i can make it on a spesified department
--WHERE FKBrAvdeling = 181
group by ANavn,SiOppForsikringFra, Aktiv, GjelderFra, month(dato), year(dato), datename(month,dato) + ' ' + cast(year(dato) as varchar)
) a
union
(
SELECT anavn,SiOppForsikringFra, aktiv, gjelderFra,
case when month(SiOppForsikringFra) = month(dato) and year(SiOppForsikringFra) = year(dato)then
DateDiff(d, dato, SiOppForsikringFra)
else
DateDiff(d, dato,
dateadd(day, -1, '01' + datename(month, dateadd(month,1, dato)) + cast(year(dateadd(month,1, dato)) as varchar))
)
end AntDays,
Regnr, 1 as AntCars ,year(dato) as [Year], month(dato) as [Month],
datename(month,dato) + ' ' + cast(year(dato) as varchar) as Dato,
1 as [order]
FROM
#temp a join
marsh_HKL.tblhertzkorttidsleieAvdeling b
on FKBRAvdeling=AID
--This is so i can make an report on a spesified time.
--WHERE dato between @datoFOM and @datoTOM
--This is so i can make it on a spesified department
--WHERE FKBrAvdeling = 181
)
order by a.anavn, a.year, a.month, [order]
if(object_id('tempdb.dbo.#temp')<>1)
drop table #temp
Viewing 15 posts - 16 through 30 (of 45 total)
You must be logged in to reply to this topic. Login to reply