Date groups

[Hagster]

Hello All,

Not sure if this sits in an SQL forum or not but in case I have to CFM the problem, also not looking for the detailed solution here just a pointer to which route to go down.

Im grabing data from my SQL server using a query like the one below.

********************************************************
SELECT DATEPART(yy, pmastdate) AS shed_year, DATEPART(ww, pmastdate) AS shed_week, SUM (pmastqty) AS pmastqty
FROM table
GROUP BY pmastpart, pmastref, DATEPART(yy, pmastdate), DATEPART(ww, pmastdate), pmastsale
********************************************************

The SQL server returns data grouped in weeks and it regards a week as Sun - Sat,

Now I need the data to be grouped in weeks but using Mon - Sun not what the SQL server returns.

So my question is:
Will I need to be grabbing the data set ungrouped and write some CFM to group it in Mon - Sun weeks,
or is there a setting or dirrerent SQL on the SQL server (microsoft 2000) that will change what it regards a week
group to be? (been googleing lots for the SQL server side etc with no luck yet)

As I said before not after solutions here unless someone has done this and does not mind, just an opinion to which the best route would be SQL or CFM

Kind Regards Guy

[Dan_Bracuk]

You can group it in sql by using a case construct.

select case when something then 'something'
when something_else then 'something_else'
else 'whatever' end
as some_alias

[Hagster]

Hello Dan,
Thanks for the reply.

Ok not sure if this is what you meant or not but I got this far now im stuck again.
I use : SELECT DATEADD(wk, DATEDIFF(wk, 7, getdate()), 7)
to select me the Monday (date) of my current week, so for today this returns '16-Feb-2009'

Then using a 'CASE' statement I can generate week groups starting from this date and simple adding 7 days etc.
In the CFM id put a loop to run through the WHEN statements, generating 12 weekly groups starting at the Monday date grabbed from above SQL.

My problem is the Group by for the SUM of 'pmastqty' wont work

See SQL below with no loop just three hard coded week groups. and the result set I'm getting as you can see I would like the results to be grouped and 'pmastqty' SUMed.

I'm thinking because the three dates that all fall in wk8 are on different days its treating it as three separate groups,
so how can I use the date field to select but group by the CASE result and not the underlying date behind.

Regards Guy

***********************************************

SELECT pmastpart,SUM (pmastqty) AS pmastqty, wknumber =
CASE
WHEN pmastdate BETWEEN '16-Feb-2009' AND '22-Feb-2009' THEN 'wk8'
WHEN pmastdate BETWEEN '23-Feb-2009' AND '01-Mar-2009' THEN 'wk9'
WHEN pmastdate BETWEEN '02-Mar-2009' AND '08-Mar-2009' THEN 'wk10'
ELSE 'not_valid_week'
END
FROM pmastshed
WHERE pmastsale like 21035 AND pmastpart LIKE '366R4548AAA'
GROUP By pmastpart,pmastqty,pmastdate

**********************************************************************


Results
pmastpart__________pmastqty_____wknumber
---------------------- ------------------------------ --------------
366R4548AAA________1.0________wk8
366R4548AAA________1.0________wk8
366R4548AAA________1.0________wk8
366R4548AAA________1.0________wk9
366R4548AAA________98.0_______wk10
366R4548AAA________98.0_______not_valid_week

[Dan_Bracuk]

Reading your original post, it appears that if the day of the week is Sunday, you subtract 1 from the week number. For every other day, you simply take the week number.

Can you think of any ways to code that in a case construct?

That's the easy part. Once you get it working you can figure out what to do about the 1st few days of January.