"Phill" <Phill@discussions.microsoft.com> wrote in message
news:489D5EDF-0D15-494C-94A4-FF5E46F1855A@microsoft.com...
>I have a stored procedure that returns outage totals by month for the past
>7
> years. The problem is that if there is no outage for a given month it
> doesn't return all of the previous years. For example, I have 4 outages
> in
> Jan 07, 0 in Jan 06, 1 in Jan 05; My resultset returns 4 Jan 07 and 1 Jan
> 05.
> I want it to also return 0 Jan 06. This is for a graph. Does anyone have
> any ideas? Here is my SP. Thanks.
>
> SP:
> CREATE PROCEDURE dbo.usp_GetTotal_SAIFI
> AS
> DECLARE @maxMonth varchar(5)
> DECLARE @maxYear varchar(5)
> DECLARE @maxDate datetime
> DECLARE @endMonthTitle varchar(50)
>
> --Get the last day of the previous month
> SELECT @maxDate = DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0,
> GETDATE()), 0))
>
> --GetDate()
>
> SELECT @maxYear = Year(@maxDate)
>
> -- Get the value for the most current month in the most current year
> SELECT @maxMonth = Month(@maxDate)
> SET @endMonthTitle=DateName(month, @maxDate) + ' ' +Cast(Year(@maxDate) AS
> varchar(4))
> SELECT Year(OutageDate) AS OutageYear,
> COUNT(OutageID) AS Outages,
> (SELECT CustomerCount FROM tblCustomerCount WHERE YearID =
> Year(OutageDate)) AS CustomerCount,
> (SELECT CustomersPerFeeder FROM tblCustomerCount WHERE YearID =
> Year(OutageDate)) AS CustomersPerFeeder,
> @endMonthTitle AS EndMonth
>
> FROM
> tblOutages
> WHERE
> Month(OutageDate) <= @maxMonth
> AND CAST(Year(OutageDate) AS Int) > @maxYear - 7
> GROUP BY Year(OutageDate)
> GO
>

"Phill" <Phill@discussions.microsoft.com> wrote in message
news:489D5EDF-0D15-494C-94A4-FF5E46F1855A@microsoft.com...
>I have a stored procedure that returns outage totals by month for the past 7
> years. The problem is that if there is no outage for a given month it
> doesn't return all of the previous years. For example, I have 4 outages in
> Jan 07, 0 in Jan 06, 1 in Jan 05; My resultset returns 4 Jan 07 and 1 Jan 05.
> I want it to also return 0 Jan 06. This is for a graph. Does anyone have
> any ideas? Here is my SP. Thanks.
>
> SP:
> CREATE PROCEDURE dbo.usp_GetTotal_SAIFI
> AS
> DECLARE @maxMonth varchar(5)
> DECLARE @maxYear varchar(5)
> DECLARE @maxDate datetime
> DECLARE @endMonthTitle varchar(50)
>
> --Get the last day of the previous month
> SELECT @maxDate = DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0,
> GETDATE()), 0))
>
> --GetDate()
>
> SELECT @maxYear = Year(@maxDate)
>
> -- Get the value for the most current month in the most current year
> SELECT @maxMonth = Month(@maxDate)
> SET @endMonthTitle=DateName(month, @maxDate) + ' ' +Cast(Year(@maxDate) AS
> varchar(4))
> SELECT Year(OutageDate) AS OutageYear,
> COUNT(OutageID) AS Outages,
> (SELECT CustomerCount FROM tblCustomerCount WHERE YearID =
> Year(OutageDate)) AS CustomerCount,
> (SELECT CustomersPerFeeder FROM tblCustomerCount WHERE YearID =
> Year(OutageDate)) AS CustomersPerFeeder,
> @endMonthTitle AS EndMonth
>
> FROM
> tblOutages
> WHERE
> Month(OutageDate) <= @maxMonth
> AND CAST(Year(OutageDate) AS Int) > @maxYear - 7
> GROUP BY Year(OutageDate)
> GO
>