Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2011
    Posts
    44

    Unanswered: Grouping of Fields

    Hi All,


    I have a 3rd Party Table with the following fields
    Code:
    date             Code1    Time1    Code2     Time2   Code3   Time3     Code4    Time4    Code5   Time5.
    01.01.2015    AAA       0:15      CCC        1:00     AAA     0:10                                BBB      0:15
    01.01.2015    BBB       0:10      AAA        1:00     EEE      0:10        BBB       0:30      CCC      0:15
    02.01.2015    CCC       0:15      CCC        1:00     BBB      0:10       DDD       1:00      AAA      0:15
    What I require is to group the Codes and sum the Times for each code based on the text value of the code.
    Code:
    CODE     DATE1              HOURS2   DATE2         HOURS2
    AAA       01.01.2015        1:25       02.01.2015    0:15
    BBB       01.01.2015        0:55       02.01.2015    0:10
    CCC       01.01.2015        1:15       02.01.2015    1:15
    DDD       01.01.2015       0:00       02.01.2015    1:00
    The query will alway be run for a week so DATE1-DATE7 and HOURS1-HOURS7 required.

    AAA BBB CCC etc can be of multiple options so these can't be hard coded. eg there could be a ZZZ or ZAC etc.

    How would I go about this?

    Thanks in advance,
    Conor

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Code:
    select code, sum(hours2), date2, sum(hours2)
    where ????
    group by code, date2
    Dave

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    This is a problem you should tackle with pivotal tables and is best done on the client.

    If you need to do it with SQL, you may find a solution by Googling for "crosstab SQL". This is what I came up with.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Sep 2011
    Posts
    44
    Thanks, I managed to get this with a number of sql queries for each row but your suggestion looks helpful should I require again

Posting Permissions

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