Help ..Group BY Problems 
Author Message
 Help ..Group BY Problems

Using MS NT SQL Server 6.0

I have two tables, PROD_DAY (which contains actual production days)
              and  CONTROL_FILE (which contains numerous date fields I use
                                 for a number of processes)  
I am trying to run the query below.
What this query does is looks at the PROD_DTE field in PROD_DAY,checks the
day of the week and I get a return of a Sunday date for that day ...
for "week ending" reporting. This conversion works fine... BUT
I get an error on the group by ,telling me that PROD_WEEK is an invalid
column name.  

select region,
        PROD_WEEK=
        CASE
        WHEN DATEPART(WEEKDAY,PROD_DTE) =1 THEN DATEADD(WEEKDAY,-0,PROD_DTE)
        WHEN DATEPART(WEEKDAY,PROD_DTE) =2 THEN DATEADD(WEEKDAY,+6,PROD_DTE)
        WHEN DATEPART(WEEKDAY,PROD_DTE) =3 THEN DATEADD(WEEKDAY,+5,PROD_DTE)
        WHEN DATEPART(WEEKDAY,PROD_DTE) =4 THEN DATEADD(WEEKDAY,+4,PROD_DTE)
        WHEN DATEPART(WEEKDAY,PROD_DTE) =5 THEN DATEADD(WEEKDAY,+3,PROD_DTE)
        WHEN DATEPART(WEEKDAY,PROD_DTE) =6 THEN DATEADD(WEEKDAY,+2,PROD_DTE)
        WHEN DATEPART(WEEKDAY,PROD_DTE) =7 THEN DATEADD(WEEKDAY,+1,PROD_DTE)
end
into PROD_week_dates
FROM PROD_DAY,CONTROL_FILE
WHERE PROD_DAY.PROD_DTE>DATEADD(YY,-1,CONTROL_FILE.LAST_DOWNLOAD_DATE)
GROUP BY region,PROD_WEEK

My work around is
1. Changing the group by to PROD_DTE..
   no problem with the group by BUT I get EVERY record (which makes sense)
   from the conversion because I have to do the group by on the PROD_DTE.
2. I pump it into a temp table #PROD_week_dates, then have to do the query
   below to get the group by PROD_WEEK.    

select region,PROD_week
into PROD_WEEK_DATES
from #PROD_WEEK_DATES
GROUP BY REGION,PROD_week

I have to get away from this two step process..Taking way to long and my
boss is leanin' me! Can somebody(s) help me out?

Thanks in advance ,
Mike In CT



Mon, 02 Mar 1998 03:00:00 GMT
 
 [ 1 post ] 

 Relevant Pages 

1. Help -- Grouping qry ---- Sub Grouping -Is this possible?

2. Help with this grouping problem

3. Help with a GROUP BY clause problem

4. PLEASE HELP: Problem with GROUP BY clause...

5. help for a project group problem

6. Empress record groups problem - help!

7. VFP 3.0 - Report group problem...please help

8. Rolling Up a Group (or, Grouping a Group)

9. Group by problem (the real problem)

10. Unix Owner Group Privilege Problem/Oracle utl_file Problem

11. Help Help How Can I get NT Group Name

12. Help Help How can I get NT Group Name


 
Powered by phpBB® Forum Software