Incorrect syntax near the keyword 'AS'.
Feilmelding..Server: Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'AS'.
Server: Msg 156, Level 15, State 1, Line 47
Incorrect syntax near the keyword 'AS'.
Hva er feil?
-- Get a listing of every month to be summarized
declare @Months table ( MonthGroup smalldatetime, MonthName varchar(25) )
INSERT INTO @Months
SELECT DISTINCT
CONVERT( smalldatetime, '01.'
+ CONVERT( varchar, DATEPART( month, GjelderFra ) )
+ '.'
+ CONVERT( varchar, DATEPART( year, GjelderFra AS) )
AS MonthGroup
case when DATEPART( month, GjelderFra ) = 1 then 'January'
when DATEPART( month, GjelderFra ) = 2 then 'February'
when DATEPART( month, GjelderFra ) = 3 then 'March'
when DATEPART( month, GjelderFra ) = 4 then 'April'
when DATEPART( month, GjelderFra ) = 5 then 'May'
when DATEPART( month, GjelderFra ) = 6 then 'June'
when DATEPART( month, GjelderFra ) = 7 then 'July'
when DATEPART( month, GjelderFra ) = 8 then 'August'
when DATEPART( month, GjelderFra ) = 9 then 'September'
when DATEPART( month, GjelderFra ) = 10 then 'October'
when DATEPART( month, GjelderFra ) = 11 then 'November'
else 'December'
end AS MonthName
FROM tblbiler,
tblAvdeling
WHERE FKBRAvdeling=AID
--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.
declare @DataRows table ( 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 tblbiler,
tblAvdeling
WHERE FKBRAvdeling=AID
--Get the 1st logical row (the month header)
SELECT CONVERT(varchar(100), MonthName + ' ' + CONVERT( varchar, DATEPART( year, GjelderFra ) ) ) AS RowLabel
, CONVERT( int, 0 ) AS BID
, CONVERT( varchar(7), '' ) AS Regnr
, CONVERT( varchar(50), '' ) AS 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), 'SUM ' + MonthName
+ ' '
+ CONVERT( varchar, ( SELECT SUM( AntDays ) FROM @DataRows DR WHERE DR.MonthGroup = Mth.MonthGroup ) )
+ ' days '
+ CONVERT( varchar, ( SELECT COUNT(*) FROM @DataRows DR WHERE DR.MonthGroup = Mth.MonthGroup ) )
+ ' cars '
) AS RowLabel
, CONVERT( int, 0 ) AS BID
, CONVERT( varchar(7), '' ) AS Regnr
, CONVERT( varchar(50), '' ) AS 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 MonthGroup, RowType, ANavn ASC
