September 20, 2007 at 12:24 pm
I'm not sure exactly how to ask this question, so I will just give sample data and my objectives for the query. I'm trying to create a query that will show actual numbers and target numbers. Here's the issue: some customers have actuals that are not targets and vice versa. I want to show all actuals and all targets regardless of a match. Here's a sample of what I'm looking for:
[Target table]
ID, TargetID, ItemID, Target
1,1,ABC,5
2,1,CBA,90
3,1,DAB,5
[Actuals table]
ID, CustomerID, ItemID, Qty
1,1,ABC,10
2,1,EFG,10
[Customer table]
ID, TargetID
1,1
[Item table]
ID,ItemID
1,ABC
2,CBA
3,DAB
4,EFG
Here's the desired output:
Customer, ItemID, Qty, Target
1,ABC,10,5
1,EFG,10,0
1,CBA,0,90
1,DAB,0,5
This seems easy to me, but I can't get it to work. My real query is a lot more complex, but the theory is the same. Any help would be greatly appreciated.
September 21, 2007 at 1:19 am
Not sure what you're after, but is this it?
create table #Target(id int, TargetID int, ItemID varchar(3), Target int)
insert into #Target
select 1,1,'ABC',5 union all
select 2,1,'CBA',90 union all
select 3,1,'DAB',5
create table #Actuals(id int, CustomerID int, ItemID varchar(3), Qty int)
insert into #Actuals
select 1,1,'ABC',10 union all
select 2,1,'EFG',10
create table #Customers(id int, TargetID int)
insert into #Customers
select 1,1
create table #Items(id int, ItemID varchar(3))
insert into #Items
select 1,'ABC' union all
select 2,'CBA' union all
select 3,'DAB' union all
select 4,'EFG'
select c.id, i.itemid, isnull(a.qty, 0) qty, isnull(t.target, 0) target
from
#Customers c inner join
#Items i on 1 = 1 left join
#Actuals a on i.itemid = a.itemid left join
#Target t on i.itemid = t.itemid
September 21, 2007 at 2:10 am
Hi Jeff,
Can u please gice me the columnnames wht u want, like in the output u want ItemID froim which table u want this itemid caz u have itemid field in two tables.
and please post your query that u have made no matters how complex it is,,..???
Thanks,
Prashant
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply