i am in one-to-many hell

We may earn a small commission from affiliate links and paid advertisements. Terms

P

posol

RETIRED
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?
 
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.
 
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
 
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
 
:confused:..........................................................................................................................................................................:blink:
 
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)
 
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.
 
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
 
wtf?

ftp is a built in function of windows pretty much... its just a protocol with commands like PUT and so forth...
 
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.
 
You must log in or register to reply here.
Back
Top