Results 1 to 4 of 4

Thread: Cross-Tab Query

  1. #1
    Join Date
    Jul 2001
    Location
    Southend-on-Sea/England
    Posts
    8

    Question Unanswered: Cross-Tab Query

    I need to produce a query in cross-tab format that displays the column data as the first day of the week for every week for a given date range.

    i.e. for August
    4 Aug 03 | 11 Aug 03 | 18 Aug 03 | 25 Aug 03

    Row1
    Row2
    etc...

    Any pointer in the right direction would be appreciated.
    Thanks
    Tim

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Cross-Tab Query

    The basic idea is to make a query, which returns the value to be accumulated with the corresponding week indication, like:

    SELECT YourGroupingField,
    case datediff(wk, '2003-01-01', getdate()) < 30 THEN YourSumField ELSE 0 END AS Aug0,
    case datediff(wk, '2003-01-01', getdate()) = 30 THEN YourSumField ELSE 0 END AS Aug1,
    case datediff(wk, '2003-01-01', getdate()) = 31 THEN YourSumField ELSE 0 END AS Aug2,
    case datediff(wk, '2003-01-01', getdate()) > 31 THEN YourSumField ELSE 0 END AS Aug3
    FROM YourTable

    You may use this query as a subquery or as a database view:

    SELECT YourGroupingField, sum(Aug0), sum(Aug1), ....
    FROM (YourQuery)
    GROUP BY YourGroupingField

    Let me know if this helps, or when you don't understand my outline.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Jul 2001
    Location
    Southend-on-Sea/England
    Posts
    8
    Thank you for your quick reply.
    I think I did not explain myself that well. So here goes - the column dates should auto-generate based on a date range. This date range could potentially be several months or years. So if the date range was 1 March 03 - 30 June 03 this would produde 18 columns starting with 3 Mar 03.

    I know how to write a normal cross-tab but it's the auto-column generation that's tricky for me.

  4. #4
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    You want the heavy stuff? Look at this.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •