CODEWITHSUNDEEP
t-sql
NoWar
NoWar

Reputation: 37633

UNION ALL and WITH statements

I need to use two filters over one table. I try UNION ALL but it does not work.

Is there a way to rework TSQL in order to merge both filters to get merged result data set?

Thank you!

DECLARE @TimeStart datetime
DECLARE @TimeEnd datetime
SET @TimeStart = '20180123'
SET @TimeEnd = '20180124'

    ;WITH A ([Machine], [TimeStart], [TimeEnd])  
    AS  
    (
    SELECT machine, [СИЗА: Calib Start] as [TimeStart],[СИЗА: Calib Stop] as [TimeEnd]
    FROM
    (
      SELECT  Machine,  [AtTime], [ValueString]
      FROM [STC_DB].[dbo].[DATA_ACTION]
      WHERE AtTime BETWEEN @TimeStart AND @TimeEnd
        AND ([ValueInt] IN (16,17))
        AND (ValueString LIKE '%СИЗА: Calib Start%' OR ValueString LIKE '%СИЗА: Calib Stop%' )
    ) d
    PIVOT
    (
      MAX(AtTime)
      FOR [ValueString] in ([СИЗА: Calib Start] , [СИЗА: Calib Stop])
    ) piv
    )
    SELECT [Machine], (SELECT FullName FROM MachineModelShortView MM WHERE MM.MachineID = A.[Machine]) AS FullName, [TimeStart], [TimeEnd]  FROM A 


    UNION ALL--- It does not work with ;WITH coz of ;


    ;WITH A ([Machine], [TimeStart], [TimeEnd])  
    AS  
    (
    SELECT machine, [СИЗ: Calib Start] as [TimeStart],[СИЗ: Calib Stop] as [TimeEnd]
    FROM
    (
      SELECT  Machine,  [AtTime], [ValueString] FROM [STC_DB].[dbo].[DATA_ACTION]
      WHERE AtTime BETWEEN @TimeStart AND @TimeEnd  AND ([ValueInt] IN (15, 16))  AND (ValueString LIKE '%СИЗ: Calib Start%' OR ValueString LIKE '%СИЗ: Calib Stop%' ) AND [Action] = 80
    ) d
    PIVOT
    (
      MAX(AtTime)
      FOR [ValueString] in ([СИЗ: Calib Start] , [СИЗ: Calib Stop])
    ) piv
    )
    SELECT [Machine], (SELECT FullName FROM MachineModelShortView MM WHERE MM.MachineID = A.[Machine]) AS FullName, [TimeStart], [TimeEnd]  FROM A

Upvotes: 1

Views: 40

Answers (1)

John Woo
John Woo

Reputation: 263733

how about combining the two condition with OR and change the ValueString to just Calib Start and Calib End? Will it still give you the same result?

;WITH A ([Machine], [TimeStart], [TimeEnd])  
AS  
(
    SELECT machine, [Calib Start] as [TimeStart],[Calib Stop] as [TimeEnd]
    FROM
    (
        SELECT  Machine,  
                [AtTime], 
                CASE WHEN ValueString LIKE '%Calib Start%' THEN 'Calib Start' ELSE 'Calib Stop' END [ValueString]
        FROM    [STC_DB].[dbo].[DATA_ACTION]
        WHERE   (
                    AtTime BETWEEN @TimeStart AND @TimeEnd
                    AND ([ValueInt] IN (16,17))
                    AND (ValueString LIKE '%Calib Start%' OR ValueString LIKE '%Calib Stop%' )
                )
                OR
                (
                    AtTime BETWEEN @TimeStart AND @TimeEnd  
                    AND ([ValueInt] IN (15, 16))  
                    AND (ValueString LIKE '%СИЗ: Calib Start%' OR ValueString LIKE '%СИЗ: Calib Stop%' ) 
                    AND [Action] = 80
                )
    ) d
    PIVOT
    (
      MAX(AtTime)
      FOR [ValueString] in ([Calib Start] , [Calib Stop])
    ) piv
)
SELECT  [Machine], 
        (SELECT FullName FROM MachineModelShortView MM WHERE MM.MachineID = A.[Machine]) AS FullName, 
        [TimeStart], 
        [TimeEnd]  
FROM    A

Upvotes: 2

Related Questions