I have a query that I am pulling the same data from three different times except for the day.
Is it possible to show the results of the dataset for all three days on one row?
I have included an Excel spreadsheet of what the desired results to look like.
Thank you
Database Table Structure
USE [tmdsDatabaseStatistics]
GO
/****** Object: Table [dbo].[tblStatisticsLocationDay] Script Date: 09/08/2022 07:54:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblStatisticsLocationDay](
[ControlPointName] [nvarchar](50) NULL,
[StationNumber] [int] NULL,
[codeline] [int] NULL,
[SubName] [nvarchar](50) NULL,
[Year] [int] NULL,
[Month] [int] NULL,
[Day] [int] NULL,
[OutOfSyncCount] [int] NULL,
[RecallLocalCount] [int] NULL,
[RecallRemoteCount] [int] NULL,
[IndicationCount] [int] NULL,
[ControlFailCount] [int] NULL,
[ControlPointStatusDownCount] [int] NULL,
[ControlSentCount] [int] NULL,
[FailureCount] [int] NULL,
[ResyncCount] [int] NULL,
[ControlDeliveryCount] [int] NULL,
[RecallRetryCount] [int] NULL,
[ResendCount] [int] NULL,
[FowardFlushCount] [int] NULL
) ON [PRIMARY]
GO
The current Query I am using to return data
USE tmdsDatabaseStatistics
DECLARE @year int
DECLARE @month int
DECLARE @year1 int
DECLARE @month1 int
DECLARE @year2 int
DECLARE @month2 int
DECLARE @day2 int
SET @year = 2022
SET @month = 8
SET @year1 = 2022
SET @month1 = 8
SET @year2 = 2022
SET @month2 = 8
SET @day2 = 31
SELECT T1.ControlPointName AS 'CP Name',
T1.codeline AS Codeline,
T1.Type AS Type,
T1.SubName AS 'Sub Division',
T1.Day As Day,
T1.Month AS Month,
T1.Year as Year,
SUM(T1.ControlFailCount) AS 'Control Failures',
SUM(T1.ControlPointStatusDownCount) AS 'Control Point Down',
T1.FailureCount AS 'Failures'
FROM(SELECT ControlPointName, codeline, tmdsDatabaseStatic.dbo.tblCodelines.Legacytype as Type, SubName, Day, Month, Year, ControlFailCount, ControlPointStatusDownCount, FailureCount
FROM tmdsDatabaseStatistics.dbo.tblStatisticsLocationDay WITH (NOLOCK) INNER JOIN tmdsDatabaseStatic.dbo.tblCodeLines ON tmdsDatabaseStatistics.dbo.tblStatisticsLocationDay.codeline = tmdsDatabaseStatic.dbo.tblCodeLines.CodelineNumber
WHERE ControlPointName NOT LIKE 'WD%' AND ControlPointName NOT LIKE 'WM%' and day = '1' and month = @month and year = @year and FailureCount < '500' and FailureCount > '99') AS T1
GROUP BY ControlPointName, Codeline, Type, SubName, Day, Month, Year, FailureCount
UNION ALL
SELECT T2.ControlPointName AS 'CP Name',
T2.codeline AS Codeline,
T2.Type AS Type,
T2.SubName AS 'Sub Division',
T2.Day As Day,
T2.Month AS Month,
T2.Year as Year,
SUM(T2.ControlFailCount) AS 'Control Failures',
SUM(T2.ControlPointStatusDownCount) AS 'Control Point Down',
T2.FailureCount AS 'Failures'
FROM(SELECT ControlPointName, codeline, tmdsDatabaseStatic.dbo.tblCodelines.Legacytype as Type, SubName, Day, Month, Year, ControlFailCount, ControlPointStatusDownCount, FailureCount
FROM tmdsDatabaseStatistics.dbo.tblStatisticsLocationDay WITH (NOLOCK) INNER JOIN tmdsDatabaseStatic.dbo.tblCodeLines ON tmdsDatabaseStatistics.dbo.tblStatisticsLocationDay.codeline = tmdsDatabaseStatic.dbo.tblCodeLines.CodelineNumber
WHERE ControlPointName NOT LIKE 'WD%' AND ControlPointName NOT LIKE 'WM%' and day = '15' and month = @month1 and year = @year1 and FailureCount < '500' and FailureCount > '99') AS T2
GROUP BY ControlPointName, Codeline, Type, SubName, Day, Month, Year, FailureCount
UNION ALL
SELECT T3.ControlPointName AS 'CP Name',
T3.codeline AS Codeline,
T3.Type AS Type,
T3.SubName AS 'Sub Division',
T3.Day As Day,
T3.Month AS Month,
T3.Year as Year,
SUM(T3.ControlFailCount) AS 'Control Failures',
SUM(T3.ControlPointStatusDownCount) AS 'Control Point Down',
T3.FailureCount AS 'Failures'
FROM(SELECT ControlPointName, codeline, tmdsDatabaseStatic.dbo.tblCodelines.Legacytype as Type, SubName, Day, Month, Year, ControlFailCount, ControlPointStatusDownCount, FailureCount
FROM tmdsDatabaseStatistics.dbo.tblStatisticsLocationDay WITH (NOLOCK) INNER JOIN tmdsDatabaseStatic.dbo.tblCodeLines ON tmdsDatabaseStatistics.dbo.tblStatisticsLocationDay.codeline = tmdsDatabaseStatic.dbo.tblCodeLines.CodelineNumber
WHERE ControlPointName NOT LIKE 'WD%' AND ControlPointName NOT LIKE 'WM%' and day = @day2 and month = @month2 and year = @year2 and FailureCount < '500' and FailureCount > '99') AS T3
GROUP BY ControlPointName, Codeline, Type, SubName, Day, Month, Year, FailureCount
Order by 'Failures' desc
And the sample data;
"SELECT ' CHEROKEE','1','4721','SELIGMAN','2022','8,'1','0','0','0','0','0','0','142','0','0','142','0','0','0', UNION ALL"
"SELECT ' CHEROKEE','1','4721','SELIGMAN','2022','8,'2','0','0','0','0','0','0','168','0','0','164','0','0','0', UNION ALL"
"SELECT ' CHEROKEE','1','4721','SELIGMAN','2022','8,'3','0','0','0','0','0','0','165','0','0','163','0','0','0', UNION ALL"
"SELECT ' CHEROKEE','1','4721','SELIGMAN','2022','8,'4','0','0','0','0','0','0','141','0','0','140','0','0','0', UNION ALL"
"SELECT ' CHEROKEE','1','4721','SELIGMAN','2022','8,'5','0','0','0','0','0','0','162','0','0','162','0','0','0', UNION ALL"
"SELECT ' CHEROKEE','1','4721','SELIGMAN','2022','8,'6','0','0','0','0','0','0','175','0','0','173','0','0','0', UNION ALL"
"SELECT ' CHEROKEE','1','4721','SELIGMAN','2022','8,'7','0','0','0','0','0','0','172','0','0','171','0','0','0', UNION ALL"
"SELECT ' CHEROKEE','1','4721','SELIGMAN','2022','8,'8','0','0','0','0','0','0','169','0','0','166','0','0','0', UNION ALL"
"SELECT ' CHEROKEE','1','4721','SELIGMAN','2022','8,'9','0','0','0','0','0','0','166','0','0','163','0','0','0', UNION ALL"
"SELECT ' CHEROKEE','1','4721','SELIGMAN','2022','8,'10','0','0','0','0','0','0','202','0','0','200','0','0','0', UNION ALL"
"SELECT ' CHEROKEE','1','4721','SELIGMAN','2022','8,'11','0','0','0','0','0','0','142','0','0','140','0','0','0', UNION ALL"
"SELECT ' CHEROKEE','1','4721','SELIGMAN','2022','8,'12','0','0','0','0','0','0','185','0','0','181','0','0','0', UNION ALL"
"SELECT ' CHEROKEE','1','4721','SELIGMAN','2022','8,'13','0','0','0','0','0','0','191','0','0','188','0','0','0', UNION ALL"
"SELECT ' CHEROKEE','1','4721','SELIGMAN','2022','8,'14','0','0','0','0','0','0','116','0','0','115','0','0','0', UNION ALL"
"SELECT ' CHEROKEE','1','4721','SELIGMAN','2022','8,'15','0','0','0','0','0','0','215','0','0','208','0','0','0', UNION ALL"
"SELECT ' CHEROKEE','1','4721','SELIGMAN','2022','8,'16','0','0','0','0','0','0','189','0','0','183','0','0','0', UNION ALL"
"SELECT ' CHEROKEE','1','4721','SELIGMAN','2022','8,'17','0','0','0','0','0','0','158','0','0','153','0','0','0', UNION ALL"
"SELECT ' CHEROKEE','1','4721','SELIGMAN','2022','8,'18','0','0','0','0','0','0','141','0','0','139','0','0','0', UNION ALL"
"SELECT ' CHEROKEE','1','4721','SELIGMAN','2022','8,'19','0','0','0','0','0','0','175','0','0','172','0','0','0', UNION ALL"
"SELECT ' CHEROKEE','1','4721','SELIGMAN','2022','8,'20','0','0','0','0','0','0','204','0','0','204','0','0','0', UNION ALL"
"SELECT ' CHEROKEE','1','4721','SELIGMAN','2022','8,'21','0','0','0','0','0','0','150','0','0','146','0','0','0', UNION ALL"
"SELECT ' CHEROKEE','1','4721','SELIGMAN','2022','8,'22','0','0','0','0','0','0','171','0','0','167','0','0','0', UNION ALL"
"SELECT ' CHEROKEE','1','4721','SELIGMAN','2022','8,'23','0','0','0','0','0','0','241','0','0','233','0','0','0', UNION ALL"
"SELECT ' CHEROKEE','1','4721','SELIGMAN','2022','8,'24','0','0','0','0','0','0','156','0','0','156','0','0','0', UNION ALL"
"SELECT ' CHEROKEE','1','4721','SELIGMAN','2022','8,'25','0','0','0','0','0','0','202','0','0','201','0','0','0', UNION ALL"
"SELECT ' CHEROKEE','1','4721','SELIGMAN','2022','8,'26','0','0','0','0','0','0','180','0','0','179','0','0','0', UNION ALL"
"SELECT ' CHEROKEE','1','4721','SELIGMAN','2022','8,'27','0','0','0','0','0','0','174','0','0','170','0','0','0', UNION ALL"
"SELECT ' CHEROKEE','1','4721','SELIGMAN','2022','8,'28','0','0','0','0','0','0','199','0','0','197','0','0','0', UNION ALL"
"SELECT ' CHEROKEE','1','4721','SELIGMAN','2022','8,'29','0','0','0','0','0','0','184','0','0','177','0','0','0', UNION ALL"
"SELECT ' CHEROKEE','1','4721','SELIGMAN','2022','8,'30','0','0','0','0','0','0','137','0','0','133','0','0','0', UNION ALL"
"SELECT ' CHEROKEE','1','4721','SELIGMAN','2022','8,'31','0','0','0','0','0','0','184','0','0','179','0','0','0', UNION ALL"
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
September 8, 2022 at 1:52 pm
In order to get the values on the same row, rather than unioning, you probably need to self-join on the key columns for the three cases.
Are all three always populated? If so, you can inner join.
If only one case is always populated, you can left join that to the others.
You'll probably want to alias the value columns to identify them separately unless you're just depending on position to do that -- e.g. Control Failures 1, Control Failures 2, etc.
September 8, 2022 at 2:04 pm
Hi ratbak. Yes all three are always populated.
Not familiar with a Self-join
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
September 8, 2022 at 2:57 pm
Self-join just means you are joining a table to itself rather than a different table. It works just like any other join. Just as you would in joining different tables, you join on the appropriate key columns, & put appropriate filters in the where clause for each joined table.
A common use case is when a table contains hierarchical data (e.g. employees an supervisors). But it's appropriate here where you're trying to get data for different windows of time from the same table.
September 8, 2022 at 3:46 pm
Is year and Month always the same for all 3 parameters? Are you always looking for StartOfMonth, EndOfMonth and MiddleOfMonth? If so, you can do something like this:
SELECT *
FROM #tblStatisticsLocationDay
WHERE ControlPointName NOT LIKE 'WD%' AND ControlPointName NOT LIKE 'WM%'
and month = @month and year = @year
and day in (1, @day2/2, @day2)
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 8, 2022 at 3:59 pm
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 be the the 16, 22nd, etc. it pick up the value in the C# app or I change it in the SQL script if I am running manually.
So, a follow up question on your suggestion.
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
September 8, 2022 at 4:06 pm
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 LIKE 'WD%' AND ControlPointName NOT LIKE 'WM%' AND SubName = 'SELIGMAN'
and month = @month and year = @year
and day in (1, 15, 31)
--Change the value in the () to the 1st/15th/31st
It returns the overall data I need however, I need to split a couple of columns out and show it like in the Spreadsheet I attached.
This is a much more efficient way to do the Day piece but does not answer how to write the data out.
Much appreciated regardless.
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
September 8, 2022 at 4:13 pm
I think this will do it, but I have not tested it.
SELECT
MAX(CASE WHEN month = @month AND year = @year THEN ControlPointName END) AS ControlPointName1,
MAX(CASE WHEN month = @month AND year = @year THEN Codeline END) AS Codeline1,
MAX(CASE WHEN month = @month AND year = @year THEN Type END) AS Type1,
MAX(CASE WHEN month = @month AND year = @year THEN "Sub Division" END) AS "Sub Division1",
MAX(CASE WHEN month = @month AND year = @year THEN Day END) AS Day1,
MAX(CASE WHEN month = @month AND year = @year THEN Month END) AS Month1,
MAX(CASE WHEN month = @month AND year = @year THEN Year END) AS Year1,
MAX(CASE WHEN month = @month AND year = @year THEN "Control Failures" END) AS "Control Failures1",
MAX(CASE WHEN month = @month AND year = @year THEN "Control Point Down" END) AS "Control Point Down1",
MAX(CASE WHEN month = @month AND year = @year THEN "Failures" END) AS "Failures1",
MAX(CASE WHEN month = @month1 AND year = @year1 THEN ControlPointName END) AS ControlPointName2,
MAX(CASE WHEN month = @month1 AND year = @year1 THEN Codeline END) AS Codeline2,
MAX(CASE WHEN month = @month1 AND year = @year1 THEN Type END) AS Type2,
MAX(CASE WHEN month = @month1 AND year = @year1 THEN "Sub Division" END) AS "Sub Division2",
MAX(CASE WHEN month = @month1 AND year = @year1 THEN Day END) AS Day2,
MAX(CASE WHEN month = @month1 AND year = @year1 THEN Month END) AS Month2,
MAX(CASE WHEN month = @month1 AND year = @year1 THEN Year END) AS Year2,
MAX(CASE WHEN month = @month1 AND year = @year1 THEN "Control Failures" END) AS "Control Failures2",
MAX(CASE WHEN month = @month1 AND year = @year1 THEN "Control Point Down" END) AS "Control Point Down2",
MAX(CASE WHEN month = @month1 AND year = @year1 THEN "Failures" END) AS "Failures2",
MAX(CASE WHEN month = @month2 AND year = @year2 THEN ControlPointName END) AS ControlPointName3,
MAX(CASE WHEN month = @month2 AND year = @year2 THEN Codeline END) AS Codeline3,
MAX(CASE WHEN month = @month2 AND year = @year2 THEN Type END) AS Type3,
MAX(CASE WHEN month = @month2 AND year = @year2 THEN "Sub Division" END) AS "Sub Division3",
MAX(CASE WHEN month = @month2 AND year = @year2 THEN Day END) AS Day3,
MAX(CASE WHEN month = @month2 AND year = @year2 THEN Month END) AS Month3,
MAX(CASE WHEN month = @month2 AND year = @year2 THEN Year END) AS Year3,
MAX(CASE WHEN month = @month2 AND year = @year2 THEN "Control Failures" END) AS "Control Failures3",
MAX(CASE WHEN month = @month2 AND year = @year2 THEN "Control Point Down" END) AS "Control Point Down3",
MAX(CASE WHEN month = @month2 AND year = @year2 THEN "Failures" END) AS "Failures3"
FROM (
SELECT T1.ControlPointName AS 'CP Name',
T1.codeline AS Codeline,
T1.Type AS Type,
T1.SubName AS 'Sub Division',
T1.Day As Day,
T1.Month AS Month,
T1.Year as Year,
SUM(T1.ControlFailCount) AS 'Control Failures',
SUM(T1.ControlPointStatusDownCount) AS 'Control Point Down',
T1.FailureCount AS 'Failures'
FROM(SELECT ControlPointName, codeline, tmdsDatabaseStatic.dbo.tblCodelines.Legacytype as Type, SubName, Day, Month, Year, ControlFailCount, ControlPointStatusDownCount, FailureCount
FROM tmdsDatabaseStatistics.dbo.tblStatisticsLocationDay WITH (NOLOCK) INNER JOIN tmdsDatabaseStatic.dbo.tblCodeLines ON tmdsDatabaseStatistics.dbo.tblStatisticsLocationDay.codeline = tmdsDatabaseStatic.dbo.tblCodeLines.CodelineNumber
WHERE ControlPointName NOT LIKE 'WD%' AND ControlPointName NOT LIKE 'WM%' and day = '1' and
( (month = @month and year = @year) or (month = @month1 and year = @year1) or (month = @month2 and year = @year2) ) and
FailureCount < '500' and FailureCount > '99') AS T
GROUP BY T.ControlPointName, T.Codeline, T.Type, T.SubName, T.Day, T.Month, T.Year, T.FailureCount
) AS TAll
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 8, 2022 at 4:16 pm
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 int
DECLARE @month int
--DECLARE @day2 int
SET @year = 2022
SET @month = 8
--SET @day2 = 31
SELECT T1.ControlPointName AS 'CP Name',
T1.codeline AS Codeline,
T1.Type AS Type,
T1.SubName AS 'Sub Division',
T1.Day As Day,
T1.Month AS Month,
T1.Year as Year,
SUM(T1.ControlFailCount) AS 'Control Failures',
SUM(T1.ControlPointStatusDownCount) AS 'Control Point Down',
T1.FailureCount AS 'Failures'
FROM(SELECT ControlPointName, codeline, tmdsDatabaseStatic.dbo.tblCodelines.Legacytype as Type, SubName, Day, Month, Year, ControlFailCount, ControlPointStatusDownCount, FailureCount
FROM tmdsDatabaseStatistics.dbo.tblStatisticsLocationDay WITH (NOLOCK) INNER JOIN tmdsDatabaseStatic.dbo.tblCodeLines ON tmdsDatabaseStatistics.dbo.tblStatisticsLocationDay.codeline = tmdsDatabaseStatic.dbo.tblCodeLines.CodelineNumber
WHERE ControlPointName NOT LIKE 'WD%' AND ControlPointName NOT LIKE 'WM%' and day in (1, 15, 31) and month = @month and year = @year and FailureCount < '500' and FailureCount > '99') AS T1
GROUP BY ControlPointName, Codeline, Type, SubName, Day, Month, Year, T1.FailureCount
Order by 'Failures' desc
I can modify the Day portion for beginning of month, middle of month and end of month later.
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
September 8, 2022 at 4:27 pm
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. Not sure about that error.
I like the reset. I can understand what you saying to do.
Thanks,
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
September 8, 2022 at 4:33 pm
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. Not sure about that error.
I like the reset. I can understand what you saying to do.
Thanks,
D'OH, sorry, my bad. Should be "CP Name" rather than ControlPointName.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 8, 2022 at 4:48 pm
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 only returning one line of data now.
The Month and Year data will be the same for each query. Only the third date may change as a variable. but for the purpose of solving the riddle, I am using the 31st as the third date.
Thanks
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
September 8, 2022 at 11:38 pm
Change the WHERE conditions to match:
...
WHERE ControlPointName NOT LIKE 'WD%' AND ControlPointName NOT LIKE 'WM%' and --<<-- "day = " removed
( (month = @month and year = @year and day = 1) or (month = @month1 and year = @year1 and day = 1) or
(month = @month2 and year = @year2 and day = @day2) ) and
FailureCount < '500' and FailureCount > '99') AS T
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 9, 2022 at 7:13 pm
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 the month. not 1st or 15th in the line. all three are 31st.
I pulled you query and applied it and then set the variables. that is all I have done with it.
Thanks!
DECLARE @day2 int
DECLARE @month int
DECLARE @month1 int
DECLARE @month2 int
DECLARE @year int
DECLARE @year1 int
DECLARE @year2 int
SET @day = 31
SET @month = 8
SET @month1 = 8
Set @month2 = 8
SET @year = 2022
SET @year1 = 2022
SET @year2 = 2022
SELECT
MAX(CASE WHEN month = @month AND year = @year THEN "CP Name" END) AS ControlPointName1,
MAX(CASE WHEN month = @month AND year = @year THEN Codeline END) AS Codeline1,
MAX(CASE WHEN month = @month AND year = @year THEN Type END) AS Type1,
MAX(CASE WHEN month = @month AND year = @year THEN "Sub Division" END) AS "Sub Division1",
MAX(CASE WHEN month = @month AND year = @year THEN Day END) AS Day1,
MAX(CASE WHEN month = @month AND year = @year THEN Month END) AS Month1,
MAX(CASE WHEN month = @month AND year = @year THEN Year END) AS Year1,
MAX(CASE WHEN month = @month AND year = @year THEN "Control Failures" END) AS "Control Failures1",
MAX(CASE WHEN month = @month AND year = @year THEN "Control Point Down" END) AS "Control Point Down1",
MAX(CASE WHEN month = @month AND year = @year THEN "Failures" END) AS "Failures1",
MAX(CASE WHEN month = @month1 AND year = @year1 THEN "CP Name" END) AS ControlPointName2,
MAX(CASE WHEN month = @month1 AND year = @year1 THEN Codeline END) AS Codeline2,
MAX(CASE WHEN month = @month1 AND year = @year1 THEN Type END) AS Type2,
MAX(CASE WHEN month = @month1 AND year = @year1 THEN "Sub Division" END) AS "Sub Division2",
MAX(CASE WHEN month = @month1 AND year = @year1 THEN Day END) AS Day2,
MAX(CASE WHEN month = @month1 AND year = @year1 THEN Month END) AS Month2,
MAX(CASE WHEN month = @month1 AND year = @year1 THEN Year END) AS Year2,
MAX(CASE WHEN month = @month1 AND year = @year1 THEN "Control Failures" END) AS "Control Failures2",
MAX(CASE WHEN month = @month1 AND year = @year1 THEN "Control Point Down" END) AS "Control Point Down2",
MAX(CASE WHEN month = @month1 AND year = @year1 THEN "Failures" END) AS "Failures2",
MAX(CASE WHEN month = @month2 AND year = @year2 THEN "CP Name" END) AS ControlPointName3,
MAX(CASE WHEN month = @month2 AND year = @year2 THEN Codeline END) AS Codeline3,
MAX(CASE WHEN month = @month2 AND year = @year2 THEN Type END) AS Type3,
MAX(CASE WHEN month = @month2 AND year = @year2 THEN "Sub Division" END) AS "Sub Division3",
MAX(CASE WHEN month = @month2 AND year = @year2 THEN Day END) AS Day3,
MAX(CASE WHEN month = @month2 AND year = @year2 THEN Month END) AS Month3,
MAX(CASE WHEN month = @month2 AND year = @year2 THEN Year END) AS Year3,
MAX(CASE WHEN month = @month2 AND year = @year2 THEN "Control Failures" END) AS "Control Failures3",
MAX(CASE WHEN month = @month2 AND year = @year2 THEN "Control Point Down" END) AS "Control Point Down3",
MAX(CASE WHEN month = @month2 AND year = @year2 THEN "Failures" END) AS "Failures3"
FROM (
SELECT T.ControlPointName AS 'CP Name',
T.codeline AS Codeline,
T.Type AS Type,
T.SubName AS 'Sub Division',
T.Day As Day,
T.Month AS Month,
T.Year as Year,
SUM(T.ControlFailCount) AS 'Control Failures',
SUM(T.ControlPointStatusDownCount) AS 'Control Point Down',
T.FailureCount AS 'Failures'
FROM(SELECT ControlPointName, codeline, tmdsDatabaseStatic.dbo.tblCodelines.Legacytype as Type, SubName, Day, Month, Year, ControlFailCount, ControlPointStatusDownCount, FailureCount
FROM tmdsDatabaseStatistics.dbo.tblStatisticsLocationDay WITH (NOLOCK) INNER JOIN tmdsDatabaseStatic.dbo.tblCodeLines ON tmdsDatabaseStatistics.dbo.tblStatisticsLocationDay.codeline = tmdsDatabaseStatic.dbo.tblCodeLines.CodelineNumber
WHERE ControlPointName NOT LIKE 'WD%' AND ControlPointName NOT LIKE 'WM%' and ( (month = @month and year = @year and day = 1) or (month = @month1 and year = @year1 and day = 15) or (month = @month2 and year = @year2 and day = @day2) ) and FailureCount < '500' and FailureCount > '99') AS T
GROUP BY T.ControlPointName, T.Codeline, T.Type, T.SubName, T.Day, T.Month, T.Year, T.FailureCount
) AS TAll
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
September 14, 2022 at 6:46 pm
Scott, I have been reading on PIVOT. Would this be more appropriate for what I am trying to accomplish?
Thanks,
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
Viewing 15 posts - 1 through 15 (of 51 total)
You must be logged in to reply to this topic. Login to reply