AnsweredAssumed Answered

Requesting help designing the query in a Query Layer

Question asked by cdennis on Jul 23, 2014
Latest reply on Jul 23, 2014 by cdennis

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?

Outcomes