Wednesday, January 29, 2014

FIll in missing months

I am querying a table that includes the rider id, the date of the ride and the distance. Problem is that they may not have ridden I In a given month, so I want to include that month with a distance of 0, so all twelve months are listed. Here s my attempt... however it doesn't pick up the zero moths.



alter PROCEDURE dbo.UM_getmonthlymiles
-- Add the parameters for the stored procedure here
@YEAR INT
, @RIDER INT

AS
BEGIN

declare @months table
(
monthnum int
--, mnthname varchar(3)
)
insert into @months

select MonthInt
from (
select 1 as MonthInt union all select 2 union all select 3 union all select 4 union all
select 5 as MonthInt union all select 6 union all select 7 union all select 8 union all
select 9 as MonthInt union all select 10 union all select 11 union all select 12
)M

select* from @months

declare @mnthmiles table
(
distance float
, riderid int
--, mnthname varchar(10)
, monthnumber int
)
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
insert into @mnthmiles
select isnull(sum(distancemiles),0), @RIDER, isnull(DATEPART(mm,ridedate), M.monthnum)--) --as [num}
from mileagelog
LEFT JOIN @months M
ON M.monthnum = DATEPART(mm,ridedate)
where DATEPART(yyyy,ridedate) = @year
and riderid = @rider
group by DATEPART(mm,ridedate), riderid, DATENAME(mm,ridedate), DATEPART(mm,ridedate), M.monthnum order by DATEPART(mm,ridedate), M.monthnum
select * from @mnthmiles


No comments:

Post a Comment