locked
How to use STUFF WITH UNION SELECT statement RRS feed

  • Question

  • Hi

    I wan to add  SELECT STUFF statement along with  my existing sql. I tried  to add STUFF STATEMENT BUT NOT WORKING

    SELECT  ', ' + CONVERT(char(10), [AttendanceDate],126) 
             FROM [dbo].[goAttendancelog] 
             WHERE DATEPART(MM, [AttendanceDate]) = DATEPART(MM, '06-08-2019')
    		 AND DATEPART(YYYY, [AttendanceDate]) = DATEPART(YYYY, '06-08-2019')
    	     AND [DepotNo] = 1
    		 AND [DepartmentID] = 1
    		 AND ([IsHolidayMarked] = 1 OR [IsDayOffMarked] = 1 OR [IsTimeOff]=1  )		
             
    		Union
    		SELECT  ', ' + CONVERT(char(10), [AttendanceDate],126) 
             FROM [dbo].[goEmployeeDayOff] 
             WHERE DATEPART(MM, [AttendanceDate]) = DATEPART(MM, '06-08-2019')
    		 AND DATEPART(YYYY, [AttendanceDate]) = DATEPART(YYYY, '06-08-2019')
    	     AND [DepotNo] = 1
    		 AND [DepartmentID] = 1
    		 AND (DayOffHrs > 0 )		

    The above sql is working fine but I want to change the above sql  WITH STUFF STATEMENT AS GIVEN BELOW. bUT NOT WORKING . PLEASE HELP

    SELECT  
    	 STUFF(
    
    	 SELECT  ', ' + CONVERT(char(10), [AttendanceDate],126) 
             FROM [dbo].[goAttendancelog] 
             WHERE DATEPART(MM, [AttendanceDate]) = DATEPART(MM, '06-08-2019')
             AND DATEPART(YYYY, [AttendanceDate]) = DATEPART(YYYY, '06-08-2019')
    	 AND [DepotNo] = 1
    	 AND [DepartmentID] = 1
    	 AND ([IsHolidayMarked] = 1 OR [IsDayOffMarked] = 1 OR [IsTimeOff]=1  )		
    	 Union
    	 SELECT  ', ' + CONVERT(char(10), [AttendanceDate],126) 
             FROM [dbo].[goEmployeeDayOff] 
             WHERE DATEPART(MM, [AttendanceDate]) = DATEPART(MM, '06-08-2019')
    	 AND DATEPART(YYYY, [AttendanceDate]) = DATEPART(YYYY, '06-08-2019')
    	 AND [DepotNo] = 1
    	 AND [DepartmentID] = 1
    	 AND (DayOffHrs > 0 )	
    )
    
     FOR XML PATH(''), TYPE)
    .value('.','NVARCHAR(MAX)'),1,2,' ') Holidays


    polachan

    Friday, June 7, 2019 11:07 AM

Answers

  • Wrong order of operation. It's: Get data -> to Xml -> Stuff. E.g.

    WITH Data
    AS ( SELECT [AttendanceDate]
         FROM   [dbo].[goAttendancelog]
         WHERE  DATEPART(MM, [AttendanceDate]) = DATEPART(MM, '06-08-2019')
                AND DATEPART(YYYY, [AttendanceDate]) = DATEPART(YYYY, '06-08-2019')
                AND [DepotNo] = 1
                AND [DepartmentID] = 1
                AND (   [IsHolidayMarked] = 1
                        OR [IsDayOffMarked] = 1
                        OR [IsTimeOff] = 1 )
         UNION
         SELECT [AttendanceDate]
         FROM   [dbo].[goEmployeeDayOff]
         WHERE  DATEPART(MM, [AttendanceDate]) = DATEPART(MM, '06-08-2019')
                AND DATEPART(YYYY, [AttendanceDate]) = DATEPART(YYYY, '06-08-2019')
                AND [DepotNo] = 1
                AND [DepartmentID] = 1
                AND DayOffHrs > 0 )
    SELECT STUFF((   SELECT ', ' + CONVERT(CHAR(10), [AttendanceDate], 126)
                     FROM   Data
                     FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,
                 1 ,
                 2 ,
                 ' ') AS Holidays;

    • Marked as answer by polachan Friday, June 7, 2019 3:55 PM
    Friday, June 7, 2019 11:18 AM