group by date (week)

Question

group by date (week)

GrassHopper

SSCarpal Tunnel

Points: 4473
More actions
May 7, 2010 at 10:42 am

#386882

I need to rollup some data and It needs to be group by Friday thru Thursday. The date field is RCPTDT. I don't know what the syntax for filtering my group by using the date field from friday to thursday.
This is my code so far:
Select RCPTDT, PACK_ITEM, max(UPC), min(UPC), Max(LEGACY_WH), SUM(CASESSHPD), MAX(PACK_SIZE), MIN(PACK_SIZE), MAX(UNIT_COST), MAX(ITMDESC), MIN(ITMDESC)
From pmk_cands_shipments_2008
GROUP BY PACK_ITEM

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

GrassHopper SSCarpal Tunnel Points: 4473 More actions · Answer 1

I just tried something, would this do what i am looking for?

Select top 1000 RCPTDT, PACK_ITEM, max(UPC), min(UPC), Max(LEGACY_WH), SUM(CASESSHPD), MAX(PACK_SIZE), MIN(PACK_SIZE), MAX(UNIT_COST), MAX(ITMDESC), MIN(ITMDESC)

From pmk_cands_shipments_2008

GROUP BY PACK_ITEM, RCPTDT

Having RCPTDT between RCPTDT AND RCPTDT + 7

Maddy...! SSC Eights! Points: 995 More actions · Answer 2

Hi rookie

jst try out this.........

Select

case datepart(dw,RCPTDT)

when 1 then 'Sun'

when 2 then 'Mon'

when 3 then 'Tue'

when 4 then 'Wed'

when 5 then 'Thur'

when 6 then 'Fri'

else 'Sat' end

as RCPTDT

, PACK_ITEM, max(UPC), min(UPC), Max(LEGACY_WH), SUM(CASESSHPD), MAX(PACK_SIZE), MIN(PACK_SIZE), MAX(UNIT_COST), MAX(ITMDESC), MIN(ITMDESC)

From pmk_cands_shipments_2008

GROUP BY PACK_ITEM,datepart(dw,RCPTDT)

-- here you can filter it for week days by writing a small having clause where datepart(dw,RCPTDT) = so n so..... etc