3 Replies Latest reply on Feb 18, 2009 5:24 AM by Dan Bracuk

    Date groups

    Hagster Level 1
      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
        • 1. Re: Date groups
          Dan Bracuk Level 5
          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
          • 2. Date groups
            Hagster Level 1
            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 =
            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'
            FROM pmastshed
            WHERE pmastsale like 21035 AND pmastpart LIKE '366R4548AAA'
            GROUP By pmastpart,pmastqty,pmastdate


            ---------------------- ------------------------------ --------------

            • 3. Re: Date groups
              Dan Bracuk Level 5
              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.