query one table three for three different where variables

  • 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"

     

    Attachments:
    You must be logged in to view attached files.
  • 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.

  • Hi ratbak. Yes all three  are always populated.

    Not familiar with a Self-join

     

  • 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.

  • 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/

  • 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.

    1. Can you explain the day in (1, @day2/2, @day2) portion?
    2. May think of another after
  • 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.

  • 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) A socialist is someone who give you the shirt off *someone else's* back.

  • 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.

     

     

  • 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,

  • brian.cook wrote:

    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) A socialist is someone who give you the shirt off *someone else's* back.

  • 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

  • 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) A socialist is someone who give you the shirt off *someone else's* back.

  • 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

    • This reply was modified 2 weeks, 2 days ago by  brian.cook.
  • Scott, I have been reading on PIVOT. Would this  be more appropriate for what I am trying to accomplish?

     

    Thanks,

Viewing 15 posts - 1 through 15 (of 38 total)

You must be logged in to reply to this topic. Login to reply