Count day pr mont, year
-
i have made this query:
SELECT DISTINCT
BID,Regnr,ANavn,GjelderFra,SiOppForsikringFra,Aktiv,
CASE WHEN SiOppForsikringFra is null THEN
DateDiff(d, GjelderFra, getdate())
ELSE
DateDiff(d, GjelderFra, SioppForsikringFra)
END AS AntDays
FROM
marsh_HKL.tblhertzkorttidsleiebiler,
marsh_HKL.tblhertzkorttidsleieAvdeling
WHERE
FKBRAvdeling=AID
ORDER BY ANAVN ASC
The data is
Regnr RegNo of the car
ANavn is department name
GjelderFra is the date the car will be active from
SiOppForsikringFra is the date the cars shouldent be active more from
Aktiv is if the car is active YES/NO
You may see the tabels her --> http://www.cyren.no/diagram3.gif
I want the output like this:
Department(ANavn)
JANUAR 2003
VF54423(RegNr) 21.01.2003(GjelderFra) 23.01.2003(SiOppforsikringFra) 3(AntDays)
VF54422(RegNr) 21.01.2003(GjelderFra) 23.03.2003(SiOppforsikringFra) 9(AntDays)
SUM JANUAR 12 days 2 cars
FEBRUARY 2003
VF54422(RegNr) 21.01.2003(GjelderFra) 23.03.2003(SiOppforsikringFra) 30(AntDays)
SUM JANUAR 30 days 1 car
MARS 2003
VF54422(RegNr) 21.01.2003(GjelderFra) 23.03.2003(SiOppforsikringFra) 23(AntDays)
SUM JANUAR 23 days 1 car
Is this possible?
//Newbie Morten
-
This was removed by the editor as SPAM
-
Yes and no. If you want to keep different resultset structures you can use a cursor with one select for the month header, one select for the core data, and another select for the month summary. Your example would ultimately produce 9 data SELECTs. I don't think this is what you are after since it will be tricky to use application side and not perform all that well SQL Server side.
What you need to consider is if you can accept a consistent columnar results for all 3 logical row types. For example:
RowLabel: varchar(100)
RegNr: varchar(7)
GjelderFra: datetime
SiOppforsikringFra: datetime
AntDays: int
With the above, the first and last logical rows would provide a RowLabel and have NULL in most of the other fields. The middle logical row would have a NULL RowLabel (unless you wanted one) and the appropriate values in the other fields. You would then produce 3 SELECT statements. One to get each month label (first logical row), one to get each data row (middle logical row and basically the SELECT statement you posted), and one to get each summary row (last logical row). These 3 SELECTs would then be UNIONed together to form a single resultset (thus the need for a uniform column structure). The trick is then to get the results ordered the way you want them. Probably the easiest way to do this is to add 2 more columns to the resultset. 1 for the month to be stored as a smalldatetime as the 1st of the month for whatever month the row represents. The second is for a rowtype indicator. A tinyint should do fine for this and it can be provided as a constant in each of the three selects such that the 1st logical row SELECT always returns a 1, the 2nd logical row SELECT always returns a 2, and the 3rd logical row SELECT always returns a 3. You can then sort by Month, RowType, ANavn. Due to the union, I believe each ORDER BY object needs to be in the SELECT list meaning in addition to those columns I listed above the following 3 columns would need to be SELECTed out:
Month: smalldatetime
RowType: tinyint
AnAvn: varchar(50)
I hope this addresses your question.
-
The problem is that there is over 15000 cars in the database. I dont want to make any changes. But make an query out of the data there is in the database now.
Regards,
Morten
-
The volume shouldn't be a problem with the solution I provided. I'll try to put the SQL together for you here. Try it out.
-- 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 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 marsh_HKL.tblhertzkorttidsleiebiler,
marsh_HKL.tblhertzkorttidsleieAvdeling
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 marsh_HKL.tblhertzkorttidsleiebiler,
marsh_HKL.tblhertzkorttidsleieAvdeling
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
-
Thanks for helping me. I get an error!
Server: Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'AS'.
Server: Msg 156, Level 15, State 1, Line 47
Incorrect syntax near the keyword 'AS'.
What can this be?
//Morten
-
Hi again!
Server: Msg 207, Level 16, State 3, Line 54
Invalid column name 'GjelderFra'.
-- 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 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 marsh_HKL.tblhertzkorttidsleiebiler,
marsh_HKL.tblhertzkorttidsleieAvdeling
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 marsh_HKL.tblhertzkorttidsleiebiler,
marsh_HKL.tblhertzkorttidsleieAvdeling
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
-
Sorry. Copy/paste error. This is the downside to not being able to test your own code.
--Get the 1st logical row (the month header)
SELECT CONVERT(varchar(100), MonthName + ' ' + CONVERT( varchar, DATEPART( year, GjelderFra ) ) ) AS RowLabel
Changes to:
--Get the 1st logical row (the month header)
SELECT CONVERT(varchar(100), MonthName + ' ' + CONVERT( varchar, DATEPART( year, MonthGroup ) ) ) AS RowLabel
I think that is the source of your current error. Good luck. Also, it occurred to be later that converting @DataRows to a #DataRows temp table and adding an index on MonthGroup and AntDays should help with performance (don't forget the DROP TABLE #DataRows at the end). I'd get the existing @DataRows version going first, though. Prove the concept before performance tweaking it.
-
Hi Aaron!
I still have som small problems with the query. The total sum are correct! Thanks!
It dosen't make the sum pr department(ANavn) It take alle togetheter pr month. Also if it isen`t any cars that have been created(gjelderFra) or deleted(SiOppforsikringFra) one mont it dosen`t make the sum for that month it jumps over it.
Thanks for the help so far!
Regards,
Morten
-
I hadn't noticed the department line in your example results. That will involve a fourth logical resultset. Man this is getting to be some ugly SQL. Add ANAvn to the @Months table, the select that populates that table, and change both the 1st and 3rd logical row SELECTs to use the newly added value instead of an empty string. Then change the ORDER BY to place ANAvn as the first condition instead of the last one. The SUM and COUNT subselects need to have ANAvn added to their WHERE clauses. Finally, you need to add the fourth (a new first) logical row just prior to the existing 1st logical row for the Month headers.
--Get the 0th logical row (the department header)
SELECT CONVERT(varchar(100), 'Department ' + 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
That should add Department. Once you get the above changes applied and tested you can fill in the gaps within the @Months table. Ultimately you will need to add another INSERT statement after the existing INSERT INTO @Months SELECT DISTINCT .... statement. There are two basic approaches. One involves a cursor and carefull tracking of previous MonthGroup with an inner while loop to generate required INSERTs. A better approach would be to create a permanent table that lists every month you may be concerned with. Populate it beginning with the first month you are tracking (or earlier) and ending with something a few years down the road. You can use a while loop to automate populating the table. Then you use an INSERT INTO @Months SELECT FROM TheMonthsTables WHERE <<the month is not in the list of Months for a given department>>. That is obviously incomplete. I have to run to a meeting and other stuff. If you need help finishing post back with what you have so far. Good luck.
-
Hi again!
I still have some problems, i`m not very good at this. Glad you are!!!! Thanks
Server: Msg 207, Level 16, State 3, Line 54
Invalid column name 'ANAvn'.
Server: Msg 207, Level 16, State 1, Line 54
Invalid column name 'ANavn'.
-- 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 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
FROM marsh_HKL.tblhertzkorttidsleiebiler,
marsh_HKL.tblhertzkorttidsleieAvdeling
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 marsh_HKL.tblhertzkorttidsleiebiler,
marsh_HKL.tblhertzkorttidsleieAvdeling
WHERE FKBRAvdeling=AID
--Get the 0th logical row (the department header)
SELECT CONVERT(varchar(100), 'Department ' + 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 ) )
+ ' dager '
+ CONVERT( varchar, ( SELECT COUNT(*) FROM @DataRows DR WHERE DR.MonthGroup = 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
Thanks again!
Morten
-
Thanks for helping me i get an error, and im not very good at this somthing you are.
Server: Msg 207, Level 16, State 3, Line 54
Invalid column name 'ANAvn'.
Server: Msg 207, Level 16, State 1, Line 54
Invalid column name 'ANavn'.
-- 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 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
FROM marsh_HKL.tblhertzkorttidsleiebiler,
marsh_HKL.tblhertzkorttidsleieAvdeling
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 marsh_HKL.tblhertzkorttidsleiebiler,
marsh_HKL.tblhertzkorttidsleieAvdeling
WHERE FKBRAvdeling=AID
--Get the 0th logical row (the department header)
SELECT CONVERT(varchar(100), 'Department ' + 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 ) )
+ ' dager '
+ CONVERT( varchar, ( SELECT COUNT(*) FROM @DataRows DR WHERE DR.MonthGroup = 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
//Morten
-
You need to add it to the @Months table. And you're doing fine. If I were better I might come up with a simpler approach.
-- Get a listing of every month to be summarized
declare @Months table ( 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
-
Hi again Aaron!
Sorry that i have to ask you so many questions!
Everything looks good now except one thing. If you insert a car for each month the sum are pr month, the query should put the sum for the next month also so you can se how many cars the department have pr month.
Se sample:
RowLabel BID RegNr Anavn GjelderFra SiOppForsikringFra AKTIV AntDays MonthGroup RowType Department A 0 Department A 2003-01-01 00:00:00.000 2003-01-01 00:00:00.000 0 1753-01-01 00:00:00.000 1 Department A 0 Department A 2003-01-02 00:00:00.000 2003-01-02 00:00:00.000 0 1753-01-01 00:00:00.000 1 Department A 0 Department A 2003-01-03 00:00:00.000 2003-01-03 00:00:00.000 0 1753-01-01 00:00:00.000 1 Department A 0 Department A 2003-01-04 00:00:00.000 2003-01-04 00:00:00.000 0 1753-01-01 00:00:00.000 1 Department A 0 Department A 2003-01-05 00:00:00.000 2003-01-05 00:00:00.000 0 1753-01-01 00:00:00.000 1 Department A 0 Department A 2003-01-06 00:00:00.000 2003-01-06 00:00:00.000 0 1753-01-01 00:00:00.000 1 Department A 0 Department A 2003-01-07 00:00:00.000 2003-01-07 00:00:00.000 0 1753-01-01 00:00:00.000 1 Department A 0 Department A 2003-01-08 00:00:00.000 2003-01-08 00:00:00.000 0 1753-01-01 00:00:00.000 1 Department A 0 Department A 2003-01-09 00:00:00.000 2003-01-09 00:00:00.000 0 1753-01-01 00:00:00.000 1 Department A 0 Department A 2003-01-10 00:00:00.000 2003-01-10 00:00:00.000 0 1753-01-01 00:00:00.000 1 Department A 0 Department A 2003-01-11 00:00:00.000 2003-01-11 00:00:00.000 0 1753-01-01 00:00:00.000 1 Department A 0 Department A 2003-01-12 00:00:00.000 2003-01-12 00:00:00.000 0 1753-01-01 00:00:00.000 1 jan.03 0 Department A 2003-01-01 00:00:00.000 2003-01-01 00:00:00.000 0 2003-01-01 00:00:00.000 1 Total Januar 904 days 2 cars 0 Department A 2003-01-01 00:00:00.000 2003-01-01 00:00:00.000 0 2003-01-01 00:00:00.000 1 1 YT51294 Department A 2003-01-01 00:00:00.000 2003-09-17 00:00:00.000 NEI 259 2003-01-01 00:00:00.000 2 6385 CE59667 Department A 2003-01-01 00:00:00.000 NULL JA 645 2003-01-01 00:00:00.000 2 feb.03 0 Department A 2003-01-02 00:00:00.000 2003-01-02 00:00:00.000 0 2003-01-02 00:00:00.000 1 Total Februar 587 days 1 cars 0 Department A 2003-01-02 00:00:00.000 2003-01-02 00:00:00.000 0 2003-01-02 00:00:00.000 1 2 YU34711 Department A 2003-02-01 00:00:00.000 2004-09-10 00:00:00.000 NEI 587 2003-01-02 00:00:00.000 2 mar.03 0 Department A 2003-01-03 00:00:00.000 2003-01-03 00:00:00.000 0 2003-01-03 00:00:00.000 1 Total Mars 200 days 1 cars 0 Department A 2003-01-03 00:00:00.000 2003-01-03 00:00:00.000 0 2003-01-03 00:00:00.000 1 3 YT35373 Department A 2003-03-01 00:00:00.000 2003-09-17 00:00:00.000 NEI 200 2003-01-03 00:00:00.000 2 apr.03 0 Department A 2003-01-04 00:00:00.000 2003-01-04 00:00:00.000 0 2003-01-04 00:00:00.000 1 Total April 527 days 1 cars 0 Department A 2003-01-04 00:00:00.000 2003-01-04 00:00:00.000 0 2003-01-04 00:00:00.000 1 4 YU34835 Department A 2003-04-01 00:00:00.000 2004-09-09 00:00:00.000 NEI 527 2003-01-04 00:00:00.000 2 mai.03 0 Department A 2003-01-05 00:00:00.000 2003-01-05 00:00:00.000 0 2003-01-05 00:00:00.000 1 Total Mai 181 days 1 cars 0 Department A 2003-01-05 00:00:00.000 2003-01-05 00:00:00.000 0 2003-01-05 00:00:00.000 1 5 YT51228 Department A 2003-05-01 00:00:00.000 2003-10-29 00:00:00.000 NEI 181 2003-01-05 00:00:00.000 2 jun.03 0 Department A 2003-01-06 00:00:00.000 2003-01-06 00:00:00.000 0 2003-01-06 00:00:00.000 1 Total Juni 32 days 1 cars 0 Department A 2003-01-06 00:00:00.000 2003-01-06 00:00:00.000 0 2003-01-06 00:00:00.000 1 6 YT50853 Department A 2003-06-01 00:00:00.000 2003-07-03 00:00:00.000 NEI 32 2003-01-06 00:00:00.000 2 jul.03 0 Department A 2003-01-07 00:00:00.000 2003-01-07 00:00:00.000 0 2003-01-07 00:00:00.000 1 Total Juli 464 days 1 cars 0 Department A 2003-01-07 00:00:00.000 2003-01-07 00:00:00.000 0 2003-01-07 00:00:00.000 1 7 KH18239 Department A 2003-07-01 00:00:00.000 NULL JA 464 2003-01-07 00:00:00.000 2 aug.03 0 Department A 2003-01-08 00:00:00.000 2003-01-08 00:00:00.000 0 2003-01-08 00:00:00.000 1 Total August 433 days 1 cars 0 Department A 2003-01-08 00:00:00.000 2003-01-08 00:00:00.000 0 2003-01-08 00:00:00.000 1 8 KH18240 Department A 2003-08-01 00:00:00.000 NULL JA 433 2003-01-08 00:00:00.000 2 sep.03 0 Department A 2003-01-09 00:00:00.000 2003-01-09 00:00:00.000 0 2003-01-09 00:00:00.000 1 Total September 402 days 1 cars 0 Department A 2003-01-09 00:00:00.000 2003-01-09 00:00:00.000 0 2003-01-09 00:00:00.000 1 9 KH18241 Department A 2003-09-01 00:00:00.000 NULL JA 402 2003-01-09 00:00:00.000 2 okt.03 0 Department A 2003-01-10 00:00:00.000 2003-01-10 00:00:00.000 0 2003-01-10 00:00:00.000 1 Total Oktober 372 days 1 cars 0 Department A 2003-01-10 00:00:00.000 2003-01-10 00:00:00.000 0 2003-01-10 00:00:00.000 1 10 KH18242 Department A 2003-10-01 00:00:00.000 NULL JA 372 2003-01-10 00:00:00.000 2 nov.03 0 Department A 2003-01-11 00:00:00.000 2003-01-11 00:00:00.000 0 2003-01-11 00:00:00.000 1 Total November 341 days 1 cars 0 Department A 2003-01-11 00:00:00.000 2003-01-11 00:00:00.000 0 2003-01-11 00:00:00.000 1 11 KH18243 Department A 2003-11-01 00:00:00.000 NULL JA 341 2003-01-11 00:00:00.000 2 des.03 0 Department A 2003-01-12 00:00:00.000 2003-01-12 00:00:00.000 0 2003-01-12 00:00:00.000 1 Total Desember 311 days 1 cars 0 Department A 2003-01-12 00:00:00.000 2003-01-12 00:00:00.000 0 2003-01-12 00:00:00.000 1 12 KH18558 Department A 2003-12-01 00:00:00.000 NULL JA 311 2003-01-12 00:00:00.000 2 It should be like this:
RowLabel BID RegNr Anavn GjelderFra SiOppForsikringFra AKTIV AntDays MonthGroup RowType Department A 0 Department A 2003-01-01 00:00:00.000 2003-01-01 00:00:00.000 0 1753-01-01 00:00:00.000 1 Department A 0 Department A 2003-01-02 00:00:00.000 2003-01-02 00:00:00.000 0 1753-01-01 00:00:00.000 1 Department A 0 Department A 2003-01-03 00:00:00.000 2003-01-03 00:00:00.000 0 1753-01-01 00:00:00.000 1 Department A 0 Department A 2003-01-04 00:00:00.000 2003-01-04 00:00:00.000 0 1753-01-01 00:00:00.000 1 Department A 0 Department A 2003-01-05 00:00:00.000 2003-01-05 00:00:00.000 0 1753-01-01 00:00:00.000 1 Department A 0 Department A 2003-01-06 00:00:00.000 2003-01-06 00:00:00.000 0 1753-01-01 00:00:00.000 1 Department A 0 Department A 2003-01-07 00:00:00.000 2003-01-07 00:00:00.000 0 1753-01-01 00:00:00.000 1 Department A 0 Department A 2003-01-08 00:00:00.000 2003-01-08 00:00:00.000 0 1753-01-01 00:00:00.000 1 Department A 0 Department A 2003-01-09 00:00:00.000 2003-01-09 00:00:00.000 0 1753-01-01 00:00:00.000 1 Department A 0 Department A 2003-01-10 00:00:00.000 2003-01-10 00:00:00.000 0 1753-01-01 00:00:00.000 1 Department A 0 Department A 2003-01-11 00:00:00.000 2003-01-11 00:00:00.000 0 1753-01-01 00:00:00.000 1 Department A 0 Department A 2003-01-12 00:00:00.000 2003-01-12 00:00:00.000 0 1753-01-01 00:00:00.000 1 jan.03 0 Department A 2003-01-01 00:00:00.000 2003-01-01 00:00:00.000 0 2003-01-01 00:00:00.000 1 Total Januar 904 days 2 cars 0 Department A 2003-01-01 00:00:00.000 2003-01-01 00:00:00.000 0 2003-01-01 00:00:00.000 1 1 YT51294 Department A 2003-01-01 00:00:00.000 2003-09-17 00:00:00.000 NEI 259 2003-01-01 00:00:00.000 2 6385 CE59667 Department A 2003-01-01 00:00:00.000 NULL JA 645 2003-01-01 00:00:00.000 2 feb.03 0 Department A 2003-01-02 00:00:00.000 2003-01-02 00:00:00.000 0 2003-01-02 00:00:00.000 1 Total Februar 587 days 3 cars 0 Department A 2003-01-02 00:00:00.000 2003-01-02 00:00:00.000 0 2003-01-02 00:00:00.000 1 2 YU34711 Department A 2003-02-01 00:00:00.000 2004-09-10 00:00:00.000 NEI 587 2003-01-02 00:00:00.000 2 mar.03 0 Department A 2003-01-03 00:00:00.000 2003-01-03 00:00:00.000 0 2003-01-03 00:00:00.000 1 Total Mars 200 days 4 cars 0 Department A 2003-01-03 00:00:00.000 2003-01-03 00:00:00.000 0 2003-01-03 00:00:00.000 1 3 YT35373 Department A 2003-03-01 00:00:00.000 2003-09-17 00:00:00.000 NEI 200 2003-01-03 00:00:00.000 2 apr.03 0 Department A 2003-01-04 00:00:00.000 2003-01-04 00:00:00.000 0 2003-01-04 00:00:00.000 1 Total April 527 days 5 cars 0 Department A 2003-01-04 00:00:00.000 2003-01-04 00:00:00.000 0 2003-01-04 00:00:00.000 1 4 YU34835 Department A 2003-04-01 00:00:00.000 2004-09-09 00:00:00.000 NEI 527 2003-01-04 00:00:00.000 2 mai.03 0 Department A 2003-01-05 00:00:00.000 2003-01-05 00:00:00.000 0 2003-01-05 00:00:00.000 1 Total Mai 181 days 6 cars 0 Department A 2003-01-05 00:00:00.000 2003-01-05 00:00:00.000 0 2003-01-05 00:00:00.000 1 5 YT51228 Department A 2003-05-01 00:00:00.000 2003-10-29 00:00:00.000 NEI 181 2003-01-05 00:00:00.000 2 jun.03 0 Department A 2003-01-06 00:00:00.000 2003-01-06 00:00:00.000 0 2003-01-06 00:00:00.000 1 Total Juni 32 days 7 cars 0 Department A 2003-01-06 00:00:00.000 2003-01-06 00:00:00.000 0 2003-01-06 00:00:00.000 1 6 YT50853 Department A 2003-06-01 00:00:00.000 2003-07-03 00:00:00.000 NEI 32 2003-01-06 00:00:00.000 2 jul.03 0 Department A 2003-01-07 00:00:00.000 2003-01-07 00:00:00.000 0 2003-01-07 00:00:00.000 1 Total Juli 464 days 8 cars 0 Department A 2003-01-07 00:00:00.000 2003-01-07 00:00:00.000 0 2003-01-07 00:00:00.000 1 7 KH18239 Department A 2003-07-01 00:00:00.000 NULL JA 464 2003-01-07 00:00:00.000 2 aug.03 0 Department A 2003-01-08 00:00:00.000 2003-01-08 00:00:00.000 0 2003-01-08 00:00:00.000 1 Total August 433 days 7 cars 0 Department A 2003-01-08 00:00:00.000 2003-01-08 00:00:00.000 0 2003-01-08 00:00:00.000 1 8 KH18240 Department A 2003-08-01 00:00:00.000 NULL JA 433 2003-01-08 00:00:00.000 2 sep.03 0 Department A 2003-01-09 00:00:00.000 2003-01-09 00:00:00.000 0 2003-01-09 00:00:00.000 1 Total September 402 days 8 cars 0 Department A 2003-01-09 00:00:00.000 2003-01-09 00:00:00.000 0 2003-01-09 00:00:00.000 1 9 KH18241 Department A 2003-09-01 00:00:00.000 NULL JA 402 2003-01-09 00:00:00.000 2 okt.03 0 Department A 2003-01-10 00:00:00.000 2003-01-10 00:00:00.000 0 2003-01-10 00:00:00.000 1 Total Oktober 372 days 6 cars 0 Department A 2003-01-10 00:00:00.000 2003-01-10 00:00:00.000 0 2003-01-10 00:00:00.000 1 10 KH18242 Department A 2003-10-01 00:00:00.000 NULL JA 372 2003-01-10 00:00:00.000 2 nov.03 0 Department A 2003-01-11 00:00:00.000 2003-01-11 00:00:00.000 0 2003-01-11 00:00:00.000 1 Total November 341 days 5 cars 0 Department A 2003-01-11 00:00:00.000 2003-01-11 00:00:00.000 0 2003-01-11 00:00:00.000 1 11 KH18243 Department A 2003-11-01 00:00:00.000 NULL JA 341 2003-01-11 00:00:00.000 2 des.03 0 Department A 2003-01-12 00:00:00.000 2003-01-12 00:00:00.000 0 2003-01-12 00:00:00.000 1 Total Desember 311 days 6 cars 0 Department A 2003-01-12 00:00:00.000 2003-01-12 00:00:00.000 0 2003-01-12 00:00:00.000 1 12 KH18558 Department A 2003-12-01 00:00:00.000 NULL JA 311 2003-01-12 00:00:00.000 2 This is because if one car is gjelderFra is 01.01.2003 (sioppforsikringfra 15.02.2003) and one from gjelderfra 01.01.2003 (sioppforsikringfra 05.03.2003)
Then you have 2 cars in januar, 2 cars in february and 1 car in mars.
Regards,
//Morten
-
I THINK what you are after is to change the SUM and COUNT subselects to check that the current MonthGroup is the same or else the beginning of the current MonthGroup occurrs prior to the end of the duration. This would involve changing the following:
+ CONVERT( varchar, ( SELECT SUM( AntDays ) FROM @DataRows DR WHERE DR.MonthGroup = Mth.MonthGroup ) )
+ ' dager '
+ CONVERT( varchar, ( SELECT COUNT(*) FROM @DataRows DR WHERE DR.MonthGroup = Mth.MonthGroup ) )
to:
+ 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) ) )
Try that out.
Viewing 15 posts - 1 through 15 (of 45 total)
You must be logged in to reply to this topic. Login to reply