Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Location
    Poland
    Posts
    18

    Unanswered: please help more more time

    hi
    i work with sql server and created a transitory querry , result of which is the set of payable accounts order by Customer i stored in #tableindex1 and that looks like this:

    customer invoice date of payment(A) value difference (03.10 -"A") AS B
    --------- ------- -------------------- ----- ------------------------------
    201100 174 2003-09-19 600 13 days
    201100 230 2003-09-24 500 9 days
    201100 270 2003-09-26 300 7 days
    301000 100 2003-09-26 200 7 days


    Now i want to create a final querry that stores the data in the following way.
    IF #tableindex1.B < 0
    THEN SUM invoice's value is stored in the column " C"
    IF #tableindex1.B > 0 AND < 8
    THEN SUM invoice's value is stored in the column " D"
    IF #tableindex1.B> 8 AND < 14
    THEN SUM invoice's value is stored in the column " E"

    Finally result should look like this

    Customer sum of CDE C D E
    ---------- ------------- --- --- -----
    201100 1370 270 1100
    301000 100 100

    Thanks in advance for every help

  2. #2
    Join Date
    Oct 2003
    Location
    Dallas
    Posts
    76
    The below query should work. Let me know if you have any other questions.


    SELECT

    customer,

    'C' = case when B < 0
    then sum(C) else 0 end,

    'D' = case when B > 0 and B < 8
    then sum(D) else 0 end,

    'E' = case when B > 8 and B < 14
    then sum(E) else 0 end

    FROM #tableindex1

    GROUP BY CUSTOMER, B

  3. #3
    Join Date
    Oct 2003
    Location
    Poland
    Posts
    18

    Thumbs up

    Originally posted by mkkmg
    The below query should work. Let me know if you have any other questions.


    SELECT

    customer,

    'C' = case when B < 0
    then sum(C) else 0 end,

    'D' = case when B > 0 and B < 8
    then sum(D) else 0 end,

    'E' = case when B > 8 and B < 14
    then sum(E) else 0 end

    FROM #tableindex1

    GROUP BY CUSTOMER, B
    thank you MKKMG
    I used your idea making some modification with a little help of other advicers, but your solution was a good inspiration that helped me to move my job forward.

Posting Permissions

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