SQL very ugly GROUP BY

Question

Daylight savings calculation. We do have a very ugly working model below.

Have seen this - SQL - Ugly combination of GROUP BY and COALESCE

If we can set variables.. This does not work - also, have removed the count(*) to illustrate the issue we are seeing..

Declare @subtractor int;
    select top 10  case 
                  when LOGGED_TIME between  
                        case DATEPART(dw,CAST('03/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))
                              when 1 then dateadd(hh,-4,CAST('03/08/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))--2am minus 6 hours for conversion from UST going to DST
                              else        dateadd(hh,-4,DATEADD(d,15-datepart(dw,CAST('03/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)),CAST('03/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)))
                        end
                        and
                        case DATEPART(dw,CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))
                              when 1 then dateadd(hh,-3,CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))  --2am minus 5 hours for conversion from UST going from DST
                              else        dateadd(hh,-3,DATEADD(d,8-datepart(dw,CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)),CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)))
                        end   
                  then 
                  Set @subtractor = 5;
                  else 
                  Set @subtractor = 6;
            end 
    CAST(DATEADD(HH, -@subtractor,LOGGED_TIME) AS date) AS ForDate
       , txtW=DATENAME(WEEKDAY, DATEADD(HH, -@subtractor,LOGGED_TIME))
       , intW=DATEPART(WEEKDAY, DATEADD(HH, -@subtractor,LOGGED_TIME))
       , intH=DATEPART(HOUR, DATEADD(HH, -@subtractor,LOGGED_TIME))
from SUPPORT_DATABASE.dbo.LOG (nolock)

This does work.. However, it is a very, very ugly GROUP BY.. Is there a way to clean this up?

select  top 10
 case 
            when LOGGED_TIME between  
                case DATEPART(dw,CAST('03/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))
                    when 1 then dateadd(hh,-4,CAST('03/08/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))--2am minus 6 hours for conversion from UST going to DST
                    else        dateadd(hh,-4,DATEADD(d,15-datepart(dw,CAST('03/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)),CAST('03/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)))
                end
                and
                case DATEPART(dw,CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))
                    when 1 then dateadd(hh,-3,CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))  --2am minus 5 hours for conversion from UST going from DST
                    else        dateadd(hh,-3,DATEADD(d,8-datepart(dw,CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)),CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)))
                end 
            then  CAST(dateadd(hh,-5,LOGGED_TIME) AS date) -- DST
            else  CAST(dateadd(hh,-6,LOGGED_TIME) AS date) -- DST
        end as ForDate,
 case 
            when LOGGED_TIME between  
                case DATEPART(dw,CAST('03/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))
                    when 1 then dateadd(hh,-4,CAST('03/08/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))--2am minus 6 hours for conversion from UST going to DST
                    else        dateadd(hh,-4,DATEADD(d,15-datepart(dw,CAST('03/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)),CAST('03/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)))
                end
                and
                case DATEPART(dw,CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))
                    when 1 then dateadd(hh,-3,CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))  --2am minus 5 hours for conversion from UST going from DST
                    else        dateadd(hh,-3,DATEADD(d,8-datepart(dw,CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)),CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)))
                end 
            then  DATENAME(WEEKDAY,dateadd(hh,-5,LOGGED_TIME)) -- DST
            else  DATENAME(WEEKDAY,dateadd(hh,-6,LOGGED_TIME)) -- DST
        end as txtW,
 case 
            when LOGGED_TIME between  
                case DATEPART(dw,CAST('03/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))
                    when 1 then dateadd(hh,-4,CAST('03/08/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))--2am minus 6 hours for conversion from UST going to DST
                    else        dateadd(hh,-4,DATEADD(d,15-datepart(dw,CAST('03/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)),CAST('03/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)))
                end
                and
                case DATEPART(dw,CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))
                    when 1 then dateadd(hh,-3,CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))  --2am minus 5 hours for conversion from UST going from DST
                    else        dateadd(hh,-3,DATEADD(d,8-datepart(dw,CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)),CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)))
                end 
            then  DATENAME(WEEKDAY,dateadd(hh,-5,LOGGED_TIME)) -- DST
            else  DATENAME(WEEKDAY,dateadd(hh,-6,LOGGED_TIME)) -- DST
        end as intW,
 case 
            when LOGGED_TIME between  
                case DATEPART(dw,CAST('03/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))
                    when 1 then dateadd(hh,-4,CAST('03/08/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))--2am minus 6 hours for conversion from UST going to DST
                    else        dateadd(hh,-4,DATEADD(d,15-datepart(dw,CAST('03/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)),CAST('03/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)))
                end
                and
                case DATEPART(dw,CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))
                    when 1 then dateadd(hh,-3,CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))  --2am minus 5 hours for conversion from UST going from DST
                    else        dateadd(hh,-3,DATEADD(d,8-datepart(dw,CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)),CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)))
                end 
            then  DATEPART(HOUR,dateadd(hh,-5,LOGGED_TIME)) -- DST
            else  DATEPART(HOUR,dateadd(hh,-6,LOGGED_TIME)) -- DST
        end as intH
        , COUNT(*) AS Totals
FROM         SUPPORT_DATABASE.dbo.LOG WITH (nolock)
 GROUP BY case 
            when LOGGED_TIME between  
                case DATEPART(dw,CAST('03/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))
                    when 1 then dateadd(hh,-4,CAST('03/08/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))--2am minus 6 hours for conversion from UST going to DST
                    else        dateadd(hh,-4,DATEADD(d,15-datepart(dw,CAST('03/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)),CAST('03/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)))
                end
                and
                case DATEPART(dw,CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))
                    when 1 then dateadd(hh,-3,CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))  --2am minus 5 hours for conversion from UST going from DST
                    else        dateadd(hh,-3,DATEADD(d,8-datepart(dw,CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)),CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)))
                end 
            then  CAST(dateadd(hh,-5,LOGGED_TIME) AS date) -- DST
            else  CAST(dateadd(hh,-6,LOGGED_TIME) AS date) -- DST
        end,
 case 
            when LOGGED_TIME between  
                case DATEPART(dw,CAST('03/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))
                    when 1 then dateadd(hh,-4,CAST('03/08/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))--2am minus 6 hours for conversion from UST going to DST
                    else        dateadd(hh,-4,DATEADD(d,15-datepart(dw,CAST('03/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)),CAST('03/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)))
                end
                and
                case DATEPART(dw,CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))
                    when 1 then dateadd(hh,-3,CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))  --2am minus 5 hours for conversion from UST going from DST
                    else        dateadd(hh,-3,DATEADD(d,8-datepart(dw,CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)),CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)))
                end 
            then  DATENAME(WEEKDAY,dateadd(hh,-5,LOGGED_TIME)) -- DST
            else  DATENAME(WEEKDAY,dateadd(hh,-6,LOGGED_TIME)) -- DST
        end
,
 case 
            when LOGGED_TIME between  
                case DATEPART(dw,CAST('03/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))
                    when 1 then dateadd(hh,-4,CAST('03/08/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))--2am minus 6 hours for conversion from UST going to DST
                    else        dateadd(hh,-4,DATEADD(d,15-datepart(dw,CAST('03/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)),CAST('03/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)))
                end
                and
                case DATEPART(dw,CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))
                    when 1 then dateadd(hh,-3,CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))  --2am minus 5 hours for conversion from UST going from DST
                    else        dateadd(hh,-3,DATEADD(d,8-datepart(dw,CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)),CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)))
                end 
            then  DATEPART(WEEKDAY,dateadd(hh,-5,LOGGED_TIME)) -- DST
            else  DATEPART(WEEKDAY,dateadd(hh,-6,LOGGED_TIME)) -- DST
        end,
 case 
            when LOGGED_TIME between  
                case DATEPART(dw,CAST('03/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))
                    when 1 then dateadd(hh,-4,CAST('03/08/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))--2am minus 6 hours for conversion from UST going to DST
                    else        dateadd(hh,-4,DATEADD(d,15-datepart(dw,CAST('03/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)),CAST('03/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)))
                end
                and
                case DATEPART(dw,CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))
                    when 1 then dateadd(hh,-3,CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))  --2am minus 5 hours for conversion from UST going from DST
                    else        dateadd(hh,-3,DATEADD(d,8-datepart(dw,CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)),CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)))
                end 
            then  DATEPART(HOUR,dateadd(hh,-5,LOGGED_TIME)) -- DST
            else  DATEPART(HOUR,dateadd(hh,-6,LOGGED_TIME)) -- DST
        end

For code that works but needs refactoring - http://codereview.stackexchange.com/ — EkoostikMartin, Feb 25 '14 at 17:50

score 2 · Accepted Answer · answered Feb 25 '14 at 17:51

You could wrap it in a cte, so you can just GROUP BY the aliases:

 ;WITH cte AS ( select
                 case 
                            when LOGGED_TIME between  
                                case DATEPART(dw,CAST('03/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))
                                    when 1 then dateadd(hh,-4,CAST('03/08/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))--2am minus 6 hours for conversion from UST going to DST
                                    else        dateadd(hh,-4,DATEADD(d,15-datepart(dw,CAST('03/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)),CAST('03/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)))
                                end
                                and
                                case DATEPART(dw,CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))
                                    when 1 then dateadd(hh,-3,CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))  --2am minus 5 hours for conversion from UST going from DST
                                    else        dateadd(hh,-3,DATEADD(d,8-datepart(dw,CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)),CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)))
                                end 
                            then  CAST(dateadd(hh,-5,LOGGED_TIME) AS date) -- DST
                            else  CAST(dateadd(hh,-6,LOGGED_TIME) AS date) -- DST
                        end as ForDate,
                 case 
                            when LOGGED_TIME between  
                                case DATEPART(dw,CAST('03/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))
                                    when 1 then dateadd(hh,-4,CAST('03/08/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))--2am minus 6 hours for conversion from UST going to DST
                                    else        dateadd(hh,-4,DATEADD(d,15-datepart(dw,CAST('03/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)),CAST('03/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)))
                                end
                                and
                                case DATEPART(dw,CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))
                                    when 1 then dateadd(hh,-3,CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))  --2am minus 5 hours for conversion from UST going from DST
                                    else        dateadd(hh,-3,DATEADD(d,8-datepart(dw,CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)),CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)))
                                end 
                            then  DATENAME(WEEKDAY,dateadd(hh,-5,LOGGED_TIME)) -- DST
                            else  DATENAME(WEEKDAY,dateadd(hh,-6,LOGGED_TIME)) -- DST
                        end as txtW,
                 case 
                            when LOGGED_TIME between  
                                case DATEPART(dw,CAST('03/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))
                                    when 1 then dateadd(hh,-4,CAST('03/08/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))--2am minus 6 hours for conversion from UST going to DST
                                    else        dateadd(hh,-4,DATEADD(d,15-datepart(dw,CAST('03/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)),CAST('03/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)))
                                end
                                and
                                case DATEPART(dw,CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))
                                    when 1 then dateadd(hh,-3,CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))  --2am minus 5 hours for conversion from UST going from DST
                                    else        dateadd(hh,-3,DATEADD(d,8-datepart(dw,CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)),CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)))
                                end 
                            then  DATENAME(WEEKDAY,dateadd(hh,-5,LOGGED_TIME)) -- DST
                            else  DATENAME(WEEKDAY,dateadd(hh,-6,LOGGED_TIME)) -- DST
                        end as intW,
                 case 
                            when LOGGED_TIME between  
                                case DATEPART(dw,CAST('03/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))
                                    when 1 then dateadd(hh,-4,CAST('03/08/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))--2am minus 6 hours for conversion from UST going to DST
                                    else        dateadd(hh,-4,DATEADD(d,15-datepart(dw,CAST('03/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)),CAST('03/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)))
                                end
                                and
                                case DATEPART(dw,CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))
                                    when 1 then dateadd(hh,-3,CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))  --2am minus 5 hours for conversion from UST going from DST
                                    else        dateadd(hh,-3,DATEADD(d,8-datepart(dw,CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)),CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)))
                                end 
                            then  DATEPART(HOUR,dateadd(hh,-5,LOGGED_TIME)) -- DST
                            else  DATEPART(HOUR,dateadd(hh,-6,LOGGED_TIME)) -- DST
                        end as intH
                FROM         SUPPORT_DATABASE.dbo.LOG WITH (nolock))
SELECT *,COUNT(*) CT
FROM cte
GROUP BY ForDate, txtW,  intW,  intH

That's a simple way to clean things up a little bit, sure it needs refactoring as well.

Understand about refactoring. However, we are pretty constrained by the fact that we cannot do much to a vendor database. — Leptonator, Feb 25 '14 at 19:26

score 1 · Answer 2 · answered Feb 25 '14 at 17:58

You could just create a UDF:

CREATE FUNCTION dbo.YourFunctionName (@DateTime DATETIME)
RETURNS DATE
AS
BEGIN
    RETURN
    (
        SELECT   case 
                when @DateTime between  
                    case DATEPART(dw,CAST('03/01/'+CAST (YEAR(@DateTime) as varchar(4)) as datetime))
                        when 1 then dateadd(hh,-4,CAST('03/08/'+CAST (YEAR(@DateTime) as varchar(4)) as datetime))--2am minus 6 hours for conversion from UST going to DST
                        else        dateadd(hh,-4,DATEADD(d,15-datepart(dw,CAST('03/01/'+CAST (YEAR(@DateTime) as varchar(4)) as datetime)),CAST('03/01/'+CAST (YEAR(@DateTime) as varchar(4)) as datetime)))
                    end
                    and
                    case DATEPART(dw,CAST('11/01/'+CAST (YEAR(@DateTime) as varchar(4)) as datetime))
                        when 1 then dateadd(hh,-3,CAST('11/01/'+CAST (YEAR(@DateTime) as varchar(4)) as datetime))  --2am minus 5 hours for conversion from UST going from DST
                        else        dateadd(hh,-3,DATEADD(d,8-datepart(dw,CAST('11/01/'+CAST (YEAR(@DateTime) as varchar(4)) as datetime)),CAST('11/01/'+CAST (YEAR(@DateTime) as varchar(4)) as datetime)))
                    end 
                then  CAST(dateadd(hh,-5,@DateTime) AS date) -- DST
                else  CAST(dateadd(hh,-6,@DateTime) AS date) -- DST
            end
    );
END

Then your query is simply:

SELECT  dbo.YourFunctionName(LOGGED_TIME) AS ForDate,
        DATENAME(WEEKDAY, dbo.YourFunctionName(LOGGED_TIME)) AS txtW
....
GROUP BY dbo.YourFunctionName(LOGGED_TIME);

Or, if you will never re-use this function and don't wnat to create it just for this, you can move your logic to an APPLY, then reference your new column:

SELECT  fd.ForDate,
        DATENAME(WEEKDAY, fd.ForDate) AS txtW
FROM    ...
        CROSS APPLY
        (
            SELECT  case 
                    when LOGGED_TIME between  
                        case DATEPART(dw,CAST('03/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))
                            when 1 then dateadd(hh,-4,CAST('03/08/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))--2am minus 6 hours for conversion from UST going to DST
                            else        dateadd(hh,-4,DATEADD(d,15-datepart(dw,CAST('03/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)),CAST('03/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)))
                        end
                        and
                        case DATEPART(dw,CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))
                            when 1 then dateadd(hh,-3,CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime))  --2am minus 5 hours for conversion from UST going from DST
                            else        dateadd(hh,-3,DATEADD(d,8-datepart(dw,CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)),CAST('11/01/'+CAST (YEAR(LOGGED_TIME) as varchar(4)) as datetime)))
                        end 
                    then  CAST(dateadd(hh,-5,LOGGED_TIME) AS date) -- DST
                    else  CAST(dateadd(hh,-6,LOGGED_TIME) AS date) -- DST
                end as ForDate
        ) AS fd
GROUP BY fd.ForDate;

Or finally you could use a subquery and only group in the outer query.

+1 for the `cross apply` suggestion. I prefer subqueries/CTEs because they are transportable between databases. But I find the `cross apply` to be very clever and potentially quite useful. — Gordon Linoff, Feb 25 '14 at 18:16

SQL very ugly GROUP BY

2 Answers2