Add SQL rows

Question

Add SQL rows

Navigation

#1 by (0 votes)

0

Hi, I have the following problem, I want to add rows of the following query:

           SELECT AreaName,CASE WHEN datename(WEEKDAY,DateOpen)= 'Sunday' THEN DATEPART(MINUTE, CONVERT(Datetime,MTTA)) END Domingo, 
   CASE WHEN datename(WEEKDAY,DateOpen) = 'Monday' THEN  DATEPART(MINUTE, CONVERT(Datetime,MTTA)) END Lunes,
   CASE WHEN datename(WEEKDAY,DateOpen) = 'Tuesday' THEN  DATEPART(MINUTE, CONVERT(Datetime,MTTA))END Martes,
   CASE WHEN datename(WEEKDAY,DateOpen) = 'Wednesday' THEN  DATEPART(MINUTE, CONVERT(Datetime,MTTA)) END Miércoles,
   CASE WHEN datename(WEEKDAY,DateOpen) = 'Thursday' THEN DATEPART(MINUTE, CONVERT(Datetime,MTTA)) END Jueves,
   CASE WHEN datename(WEEKDAY,DateOpen) = 'Friday' THEN  DATEPART(MINUTE, CONVERT(Datetime,MTTA)) END Viernes,
   CASE WHEN datename(WEEKDAY,DateOpen) = 'Saturday' THEN DATEPART(MINUTE, CONVERT(Datetime,MTTA)) END Sábado

     FROM  GeneralReport
 WHERE datename(weekday,DateOpen) in 
('Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday')
AND week = '4' AND AreaName='SMD'
group by GeneralReport.AreaName,GeneralReport.DateOpen,GeneralReport.MTTA
order by GeneralReport.AreaName

sql sql-server query

asked by CarlosR93 26.01.2018 в 17:54

source

1 answer

Error: std: out_of_range Button value within table

score 0 · Answer 1

Your query is almost ready, just need to adjust some things:

SELECT  AreaName,
    SUM(ISNULL(CASE WHEN datename(WEEKDAY,DateOpen) = 'Sunday'    THEN ISNULL(DATEPART(MINUTE, CONVERT(Datetime,MTTA))),0) END Domingo, 
    SUM(ISNULL(CASE WHEN datename(WEEKDAY,DateOpen) = 'Monday'    THEN ISNULL(DATEPART(MINUTE, CONVERT(Datetime,MTTA))),0) END Lunes,
    SUM(ISNULL(CASE WHEN datename(WEEKDAY,DateOpen) = 'Tuesday'   THEN ISNULL(DATEPART(MINUTE, CONVERT(Datetime,MTTA))),0) END Martes,
    SUM(ISNULL(CASE WHEN datename(WEEKDAY,DateOpen) = 'Wednesday' THEN DATEPART(MINUTE, CONVERT(Datetime,MTTA))),0)        END Miércoles,
    SUM(ISNULL(CASE WHEN datename(WEEKDAY,DateOpen) = 'Thursday'  THEN DATEPART(MINUTE, CONVERT(Datetime,MTTA))),0)        END Jueves,
    SUM(ISNULL(CASE WHEN datename(WEEKDAY,DateOpen) = 'Friday'    THEN DATEPART(MINUTE, CONVERT(Datetime,MTTA))),0)        END Viernes,
    SUM(ISNULL(CASE WHEN datename(WEEKDAY,DateOpen) = 'Saturday'  THEN DATEPART(MINUTE, CONVERT(Datetime,MTTA))),0)        END Sábado
        FROM  GeneralReport
    WHERE   datename(weekday,DateOpen) in  ('Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday')
        AND week = '4' 
        AND AreaName='SMD'
    GROUP BY GeneralReport.AreaName
    ORDER BY GeneralReport.AreaName

You want to add but you're not using the SUM() function
You must group only the column AreaName
Add a ISNULL so that eventually the SUM returns 0 instead of NULL