Requesting help designing the query in a Query Layer

ClayDennis — Wed, 23 Jul 2014 14:34:46 GMT

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:

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:

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?

Re: Requesting help designing the query in a Query Layer

ClayDennis — Wed, 23 Jul 2014 16:25:25 GMT

With some help on GIS Stack Exchange I found out what the problem was with the third query, I accidently cut out a SUM command from the CAIDI part when I was copying and pasting.

topic Re: Requesting help designing the query in a Query Layer in Data Management Questions

Requesting help designing the query in a Query Layer

Re: Requesting help designing the query in a Query Layer