I am pulling data from our SQL database to show our SAIDI and SAIFI numbers visually for our operations department.
I have a functioning query that can pull in the needed data, and currently I use a labeling expression to perform the math from the basic data to give the numbers I need to show.
This is the query I am using:
SELECT Milsoft.dbo.Outage.Substation, Milsoft.dbo.Outage.Feeder, COUNT(Milsoft.dbo.Outage.OutageName) As Outages, SUM(Milsoft.dbo.Outage.CustomersOutInitially) AS MemOut, SUM((Milsoft.dbo.Outage.InterruptionDuration*Milsoft.dbo.Outage.CustomersOutInitially)) as Time, Milsoft.dbo.CustomerServedElement.Total As Members FROM Milsoft.dbo.Outage, Milsoft.dbo.CustomerServedElement WHERE (Milsoft.dbo.CustomerServedElement.Year='2014' AND (Milsoft.dbo.CustomerServedElement.DayOfYear='181')) AND(Milsoft.dbo.Outage.Feeder=Milsoft.dbo.CustomerServedElement.ElementName) AND (Milsoft.dbo.Outage.OutageStartTime >= '6/1/2014' AND Milsoft.dbo.Outage.OutageStartTime <= '6/30/2014') GROUP BY Milsoft.dbo.Outage.Substation, Milsoft.dbo.Outage.Feeder, Milsoft.dbo.CustomerServedElement.Total
I can probably be cleaned up, I am just starting to learn this (previously used access database and its query wizard instead of building it like this).
I am trying to add the math for SAIDI, SAIFI, and CAIDI. One of our IT guys said that as soon as the column name variable was declared I should be able to use that name in my query which lead to this:
SELECT Milsoft.dbo.Outage.Substation, Milsoft.dbo.Outage.Feeder, COUNT(Milsoft.dbo.Outage.OutageName) As Outages, SUM(Milsoft.dbo.Outage.CustomersOutInitially) AS MemOut, SUM((Milsoft.dbo.Outage.InterruptionDuration*Milsoft.dbo.Outage.CustomersOutInitially)) as Time, Milsoft.dbo.CustomerServedElement.Total As Members, (Time/60/Members) AS SAIDI, (MemOut/Members) AS SAIFI, (SAIDI/SAIDI) AS CAIDI FROM Milsoft.dbo.Outage, Milsoft.dbo.CustomerServedElement WHERE (Milsoft.dbo.CustomerServedElement.Year='2014' AND (Milsoft.dbo.CustomerServedElement.DayOfYear='181')) AND(Milsoft.dbo.Outage.Feeder=Milsoft.dbo.CustomerServedElement.ElementName) AND (Milsoft.dbo.Outage.OutageStartTime >= '6/1/2014' AND Milsoft.dbo.Outage.OutageStartTime <= '6/30/2014') GROUP BY Milsoft.dbo.Outage.Substation, Milsoft.dbo.Outage.Feeder, Milsoft.dbo.CustomerServedElement.Total
This throws the error Time is an invalid column name, and will throw a similar error if I change the name to something else.
This lead to my third query:
SELECT Milsoft.dbo.Outage.Substation, Milsoft.dbo.Outage.Feeder, COUNT(Milsoft.dbo.Outage.OutageName) As Outages, SUM(Milsoft.dbo.Outage.CustomersOutInitially) AS MemOut, SUM((Milsoft.dbo.Outage.InterruptionDuration*Milsoft.dbo.Outage.CustomersOutInitially)) as Time, Milsoft.dbo.CustomerServedElement.Total As Members, (SUM((Milsoft.dbo.Outage.InterruptionDuration*Milsoft.dbo.Outage.CustomersOutInitially))/60/Milsoft.dbo.CustomerServedElement.Total) AS Saidi, (SUM(Milsoft.dbo.Outage.CustomersOutInitially)/Milsoft.dbo.CustomerServedElement.Total) AS Saifi, (((Milsoft.dbo.Outage.InterruptionDuration*Milsoft.dbo.Outage.CustomersOutInitially))/60/Milsoft.dbo.CustomerServedElement.Total)/(SUM(Milsoft.dbo.Outage.CustomersOutInitially)/Milsoft.dbo.CustomerServedElement.Total)) AS Caidi FROM Milsoft.dbo.Outage, Milsoft.dbo.CustomerServedElement WHERE (Milsoft.dbo.CustomerServedElement.Year='2014' AND (Milsoft.dbo.CustomerServedElement.DayOfYear='181')) AND(Milsoft.dbo.Outage.Feeder=Milsoft.dbo.CustomerServedElement.ElementName) AND (Milsoft.dbo.Outage.OutageStartTime >= '6/1/2014' AND Milsoft.dbo.Outage.OutageStartTime <= '6/30/2014') GROUP BY Milsoft.dbo.Outage.Substation, Milsoft.dbo.Outage.Feeder, Milsoft.dbo.CustomerServedElement.Total
This query says I have an error near FROM, unfortunately I have no idea what that error is. Any suggestions on what I am doing wrong?
