DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Fun with SQL

  1. #1
    Join Date
    Aug 2004
    Posts
    43,023

    Fun with SQL

    [Originally posted by Tom McGeehan]

    This question really doesn't pertain to Visual Basic, but since I've been so impressed in the past with solutions I've received through posting questions on this site, I'll give it a shot...

    I have an Oracle table; lets call it TASK
    the format of the table is:

    proj_id˙ ˙ ˙ ˙ ˙ number(18)
    task_id˙ ˙ ˙ ˙ ˙ number(12)
    user_id˙ ˙ ˙ ˙ ˙ varchar2(10)
    work_date˙ ˙ ˙ ˙ date
    hours_worked˙ ˙  number(10,3)

    Given a sunday date (lets say 7/28/02), I would like to run a query that would return proj_id, task_id, user_id, the hours worked on sunday, the hours worked on monday(7/29/02), the hours worked on tuesday(7/30/02)...the hours worked on saturday.

    I know at least one method of returning rows as columns but the dates have got me thouroghly confused.˙

    Thanks to any SQL gurus who might be able to lend me a hand.

    Tom McGeehan

  2. #2
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Fun with SQL

    [Originally posted by Andon K.]

    What about using the DateAdd function? Here is an example with two workdays:

    SELECT proj_id, task_id, user_id,
    Sum(IIf([work_date]=DateAdd("d",1,#7/28/2002#),[Hours_worked],0)) AS DayMonday
    Sum(IIf([work_date]=DateAdd("d",2,#7/28/2002#),[Hours_worked],0)) AS DayTuesday
    FROM Task
    GROUP BY proj_id, task_id, user_id

    With Access or SQL Server this would be solved using a pivot query, but it is not standard SQL and AFAIK Oracle doesn't support them, so you have to simulate them, usually in a stored procedure.

    HTH

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center