Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: Pairs of Data

  1. #1
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    152

    Unhappy Unanswered: Pairs of Data

    Hi folks,

    I am trying to write a query to get data in pairs, for example, i have data like this:
    sr_no week_no
    1 24-A
    2 24-B
    3 24-C
    4 25-A
    5 25-B
    6 26-A
    7 26-B

    I want to get data in pairs i.e. data for week_no 24-A and 24-B will come togather? is it possible?

    Any urgent help will be highly appreicated.

    Thanks
    mr_roomi

  2. #2
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    152

    Unhappy problem

    any suggestion on my problem
    mr_roomi

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Your question lack of especification. You want data into GROUPS, but want do you want to do with sr_no after you have done so ?

    Here's an example that will get the count of pairs within each group, and the sum of sr_no by each group as well:

    Code:
    select substr(week_no,1,instr(week_no,'-')-1) pair,
           count(*) cnt,
           sum(sr_no) sum
      from t
     group by substr(week_no,1,instr(week_no,'-')-1)

  4. #4
    Join Date
    Apr 2004
    Posts
    246
    you've given rather vague requirements. what happens to 24-C? do you just want output ordered, or output on same line? if pairs should be created as one row, what is the format? and, most of all - have you even tried it yourself? what SQL do you have so far?
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  5. #5
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    152

    Unhappy Problem

    Thanks for prompt reply. We cann't follow sr_no cuz it is from "squence" Week_No is unique. I am trying to display data
    in-group of bi-weekly but first week must be even. i mean first week should
    be 2 and second week should be 3. Here is real data:
    sr_no week_no amount
    1 24-A 500
    2 24-B 600
    3 24-C 700
    4 25-A 800
    5 25-B 900
    6 26-A 100
    7 26-B 200

    Note: We can assume that we want sum of amount bi-weekly.
    mr_roomi

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    are you relying on the sequence to determine the bi-weeks?
    OR does A, B, C represent something?

    this is a really confusing design
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    mr_roomi, in addition to not bumping your own threads, you should definitely not post the same question in more than one forum

    http://www.dbforums.com/t1022905.html

    sheesh

    what is it, oracle or sql server?

    the answer you get is gonna be different, depending
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    152

    Problem.

    Mr. R937: I am looking for logic to create a SQL Statement.
    A, B, C doesn't represent any thing but multiple entries within the same week. So i am trying to create bi-weekly i.e. week 24 and 25 togather and so on.

    I hope i clear my point this time?

    Thanks for prompt replies.
    mr_roomi

  9. #9
    Join Date
    Apr 2004
    Posts
    246
    1st post - "data for week_no 24-A and 24-B will come togather"
    last post - "week 24 and 25 togather and so on"

    which is it? still no clear requirement.

    Oracle or SQL Server? Pick one, not both. I don't write ANSI sql - I write Oracle SQL, taking full advantage of Oracle constructs.

    and, show at least on simple, incorrect sql that you wrote, just so we believe that you tried.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Mr_roomi
    I hope i clear my point this time?
    nope, sorry, it's even more confusing

    perhaps if you would give some real data, and then show what you want as a result

    i just don't see where the "pairs" comes in

    and which is it, oracle or sql server?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    152

    Pairs

    Here is more clear thing. If I would say there is no such thing like A, B in week. If I should make it more clear, the data is in this format.
    sr_no week_no amount
    1 24 500
    2 25 600
    3 26 700
    4 27 800
    5 28 900
    6 29 100
    7 30 200

    I am trying to get sum of bi-weekly data. so it would be week 24 and 25, 26 and 27 and so on.

    ??
    mr_roomi

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oracle or sql server?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    152

    Oracle

    I want it in oracle and sql server. cuz my online appliction is extracting data froms sql server and desktop appliction is on oracle.
    mr_roomi

  14. #14
    Join Date
    Jul 2003
    Posts
    2,296
    PHP Code:
    select aamount, (case when MOD(a2) = 0 then amount+prev endbi_weekly
    from 
    (
      
    select 
        week_no a

        
    amount
        
    lag(amountover (order by week_noprev
       from tablename

    - The_Duck
    you can lead someone to something but they will never learn anything ...

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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