1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

i am in one-to-many hell

Discussion in 'Computers, Games, Electronics etc' started by pissedoffsol, Jan 14, 2006.

  1. pissedoffsol

    pissedoffsol RETIRED

    Messages:
    49,693
    Likes Received:
    53
    Joined:
    Sep 28, 2002
    Location:
    Retirement Home
    any relational database experts in here?


    here's the deal:

    I have a main table (one)
    and i have a phone number table (many)

    there can be multiple phone numbers for each order in the mian table, each joined off a confirmation code number.

    I'm trying to set up an out put, so that i get 1 record that includes the counts from the main table.

    for example,

    Joe has 2 phone numbers, each with long distance options.

    so i have:

    Main:
    row1 Joe codeA

    Phones:
    row1 codeA longdistance
    row2 codeA longdistance

    what i need for the output is:

    Joe codeA 2

    i can't figure this out. i'm retarded.

    When i left outer join the tables, i get 2 rows (one for each number) instead of the one result row i want


    of course, this is the over-simplified version :p


    do i need to make a temp table with my counts first, then join to that?
     
  2. reikoshea

    reikoshea HS Troll...And Mod Moderator VIP

    Messages:
    12,633
    Likes Received:
    193
    Joined:
    Apr 27, 2005
    Location:
    San Antonio, TX
    looking for a query just do something like "Expr1: Count (*)" (i think that is it, id have to see what i have written at work to know for sure)

    and then expression and then the name of the phone # fields.

    I know i could do it, but i am not even CLOSE to having a computer with access on it.

    If you still need it on monday, ill post how to do it. I just feel out access for my purposes, i really cant do anything in it off the top of my head.
     
  3. pissedoffsol

    pissedoffsol RETIRED

    Messages:
    49,693
    Likes Received:
    53
    Joined:
    Sep 28, 2002
    Location:
    Retirement Home
    if you use count(), which is what one woudl think
    of first, it won't work, because you need count()s of records with
    different filters applied. You would need a bunch of queries in this case,
    one filtering on longdistance=true, another filtering on dsl=true, and
    another for all the rest of the fields


    this is due monday, so i need to finish it today
     
  4. pissedoffsol

    pissedoffsol RETIRED

    Messages:
    49,693
    Likes Received:
    53
    Joined:
    Sep 28, 2002
    Location:
    Retirement Home
    now i'm getting somewhere....

    Code:
    
    SELECT
    office_code, repid_number, fn_dateonly(iacc_date), btn, 
    cust_first_name + ' ' + cust_last_name, cust_type, loa,
    ( select count(*) 
    from tbellsouthretail_wtn
     where confirmation_code 
    = m.confirmation_code ), '', 
    case directtv when 'Y' then '1' else null end,
    case packages when 'BPP' then '1' else null end,
    case packages when 'CC' 
     then num_cc_lines
    - ( select count(*) 
    from wtn
     where confirmation_code 
    = m.confirmation_code
     and ld_program_type <> 'E')
     else null end,
    
    ( select count(*) 
    from wtn
     where confirmation_code 
    = m.confirmation_code
     AND dsl_plan LIKE 'DSL Extreme%'
    AND dsl_upgrade = 'N'
    ),
    
    ( select count(*) 
    from wtn
     where confirmation_code 
    = m.confirmation_code
     AND dsl_plan = 'DSL Lite'
    AND dsl_upgrade = 'N'
    ),
    
    ( select count(*) 
    from wtn
     where confirmation_code 
    = m.confirmation_code
     AND dsl_plan = 'DSL Ultra'
    AND dsl_upgrade = 'N'
    )
    
    
    FROM Main m
    WHERE dispo = '80'
    AND fn_dateonly(iacc_date) = fn_dateonly(getdate() - 1)
    
    
    very in-efficient, but its working... shut up sys admin guy, buy more ram :p
     
  5. HatchSpeeD

    HatchSpeeD ...Hi...

    Messages:
    1,241
    Likes Received:
    0
    Joined:
    Apr 14, 2003
    Location:
    Beantown
    [​IMG]..........................................................................................................................................................................[​IMG]
     
  6. pissedoffsol

    pissedoffsol RETIRED

    Messages:
    49,693
    Likes Received:
    53
    Joined:
    Sep 28, 2002
    Location:
    Retirement Home
    lol that's nothing...
    here's what i'm up to now :p

    Code:
    SELECT
    office_code, repid_number, fn_dateonly(iacc_date) as 'Sales Date', btn as 'Phone Number', 
    cust_first_name + ' ' + cust_last_name As 'Customer Name', cust_type as 'Type', loa,
    ( select count(*) 
    from _wtn
     where confirmation_code 
    = m.confirmation_code ) as 'Number of lines', 
    'EON' as 'Electronic Order Number', 
    case directtv when 'Y' then '1' else null end AS 'Direct TV',
    case packages when 'BPP' then '1' else null end AS 'BPP',
    case packages when 'CC' 
     then num_cc_lines- (
    select count(*) 
     from _wtn
    where confirmation_code 
    = m.confirmation_code
    and ld_program_type <> 'E'
    ) 
     else null end AS 'Complete Choice',
    
    ( select count(*) 
    from _wtn
     where confirmation_code 
    = m.confirmation_code
     AND dsl_plan = 'DSL Extreme 3.0'
    AND dsl_upgrade = 'N'
    ) AS 'DSL Extreme',
    
    ( select count(*) 
    from _wtn
     where confirmation_code 
    = m.confirmation_code
     AND dsl_plan = 'DSL Lite'
    AND dsl_upgrade = 'N'
    ) AS 'DSL Lite',
    
    ( select count(*) 
    from _wtn
     where confirmation_code 
    = m.confirmation_code
     AND dsl_plan = 'DSL Ultra'
    AND dsl_upgrade = 'N'
    ) AS 'DSL Ultra',
    
    ( select count(*) 
    from _wtn
     where confirmation_code 
    = m.confirmation_code
     AND m.ld_plan = 'Nickel Plan'
    AND ld_program_type = 'N'
    ) AS 'Nickel Plan',
    
    'Primary Link',
    
    ( select count(*) 
    from _wtn
     where confirmation_code 
    = m.confirmation_code
     AND m.ld_plan = 'Unlimited LD'
    AND ld_program_type = 'U'
    ) AS 'U NickelPlan-Unlimit L',
    
    ( select count(*) 
    from _wtn
     where confirmation_code 
    = m.confirmation_code
     AND m.ld_plan = 'Unlimited LD'
    AND ld_program_type = 'N'
    ) AS 'Unlimited LD',
    
    ( select count(*) 
    from _wtn
     where confirmation_code 
    = m.confirmation_code
     AND dsl_plan = 'DSL Lite'
    AND dsl_upgrade = 'Y'
    ) AS 'Dial-up to DSL Lite',
    
    ( select count(*) 
    from _wtn
     where confirmation_code 
    = m.confirmation_code
     AND m.ld_plan = 'Dollar Plan'
    AND ld_program_type = 'N'
    ) AS 'Dollar Plan',
    ( select count(*) 
    from _wtn
     where confirmation_code 
    = m.confirmation_code
     AND dsl_plan = 'DSL Extreme 6.0'
    --AND dsl_upgrade = 'Y'
    ) AS 'Extreme 6.0',
    
    ( select count(*) 
    from _wtn
     where confirmation_code 
    = m.confirmation_code
     AND m.intllongdistance = 'Y'
    AND intl_ld_program_type = 'N'
    ) AS 'International',
    
    ( select count(*) 
    from _wtn
     where confirmation_code 
    = m.confirmation_code
     AND dsl_plan = 'DSL Ultra'
    AND dsl_upgrade = 'Y'
    ) AS 'Up - Dial to Ultra',
    
    ( select count(*) 
    from _wtn
     where confirmation_code 
    = m.confirmation_code
     AND dsl_plan = 'DSL Extreme 3.0'
    AND dsl_upgrade = 'Y'
    ) AS 'Up - Dial to Extreme',
    
    'GRAND TOTAL',
    'Issued Orders',
    'Accepted/Rejected',
    'Order#/Conf#',
    'Due Date',
    'Sales Code',
    'Notes',
    'Comments'
    
    
    FROM _TPV_Main m
    WHERE dispo = '80'
    AND fn_dateonly(iacc_date) = fn_dateonly(getdate() - 1)
    
     
  7. reikoshea

    reikoshea HS Troll...And Mod Moderator VIP

    Messages:
    12,633
    Likes Received:
    193
    Joined:
    Apr 27, 2005
    Location:
    San Antonio, TX
    lol, if i had more than a break to look at this, id help you. But we all know i wouldnt be much help. Your sql skillz OWN ME.
     
  8. pissedoffsol

    pissedoffsol RETIRED

    Messages:
    49,693
    Likes Received:
    53
    Joined:
    Sep 28, 2002
    Location:
    Retirement Home
    query is done.

    now, for the hard part.

    setting up the DTS package to call this correctly on a daily basis for yesterday's data sets, exports to a properly named XLS, and auto-ftp's at 9am to my client :p
     
  9. reikoshea

    reikoshea HS Troll...And Mod Moderator VIP

    Messages:
    12,633
    Likes Received:
    193
    Joined:
    Apr 27, 2005
    Location:
    San Antonio, TX
    lol, good luck. we still use ProComm for up and downloads.
     
  10. pissedoffsol

    pissedoffsol RETIRED

    Messages:
    49,693
    Likes Received:
    53
    Joined:
    Sep 28, 2002
    Location:
    Retirement Home
    wtf?

    ftp is a built in function of windows pretty much... its just a protocol with commands like PUT and so forth...
     
  11. reikoshea

    reikoshea HS Troll...And Mod Moderator VIP

    Messages:
    12,633
    Likes Received:
    193
    Joined:
    Apr 27, 2005
    Location:
    San Antonio, TX
    i wasnt saying good luck like it was gonna be hard. I was just using it as an expression.

    And most of our clients are behind a firewall and they dont want to make changes to their network. So they get a computer with a modem and we dial into them.
     
Verification:
Draft saved Draft deleted

Share This Page