Yes, Aaron, that is correct. I need it to display the three dates and the associated data in new columns. Reading up on CrossTab I think you and are correct....

Thanks Scott. Sorry for the delay. Took a few days away!

Scott, I have been reading on PIVOT. Would this  be more appropriate for what I am trying to accomplish?

Thanks,

Scott, I run the query and still is only returning one line of data when it should be 100 or more. And it is only showing for the 31st of...

• This reply was modified 3 years, 8 months ago by brian.cook.

Cool, that solved that error and it returns for Day 1. Do I repeat this for the other two sub queries or am I missing something here, as this is...

Hi Scott, getting an error on Column not found in this piece;

MAX(CASE WHEN month = @month AND year = @year THEN ControlPointName END)

That one is part of the table....

Mike using what you provided , here is how my query looks now. I was able to eliminate the UNIONS, and make the script smaller.

USE tmdsDatabaseStatistics

DECLARE @year...

Mike, I think you made a type-o. If I change it to look like this;

SELECT * 
  FROM [tmdsDatabaseStatistics].[dbo].[tblStatisticsLocationDay]
  WHERE ControlPointName = 'CHEROKEE'and ControlPointName NOT...

Mike, the day value is always the 1st and 15, however the third date is variable. it depends on the date  that the user runs the script. i.e. it could...

Hi ratbak. Yes all three  are always populated.

Not familiar with a Self-join

Thanks,

After I wrote this, I realized I had this one figured out.

Sorry, too many days on the same project and keep getting stuff dumped on me to add or change.

Appreciate...

Scott,

WHERE g.Year = @year and g.Month = @month AND g.FailureCount <> 0

Only returns lines with the High FailureCount values. It does not return any lines with the changed values.

Jeff,

Here is an example of the data for one location for a single days statistics with some explanation;

As for query output for a single day, it generates almost 95000 lines...

I need to show for example that at hour null/zero it was a value of '0' and lets say at hour one it climbs to the positive number and then...

Here is an example where it goes from a NULL value to a Positive number then a Negative number in a three hour period.

WD 1116.0 (HILINE)1413HI LINE2022816386610
WD...