December 7, 2010 at 1:21 pm
My query:
select ISNULL(B.UnitsSold,0) as UnitsSold from(SELECT TOP (100) PERCENT UnitsSold, districtpn, yrm FROM dbo.vwGatesSales WHERE (districtpn LIKE '123') ) as B right outer join yrmonth on B.yrm = yrmonth.yrm order by yrmonth.yrm
this work fine and brings back 0 for all blanks.
However when I try this:
select ISNULL(B.UnitsSold,0) as UnitsSold ,b.districtpn, B.yrm
from(SELECT TOP (100) PERCENT UnitsSold, districtpn, yrm
FROM dbo.vwGatesSales ) as B right outer join yrmonth on B.yrm = yrmonth.yrm
order by b.districtpn,yrmonth.yrm
if fails to bring back data for each key value because it can find a link on the join.
Is there a way to bring back null records for each key value?
December 7, 2010 at 1:29 pm
I'm not entirely sure what a Tally Table has to do with this, but can you post some sample data from the two tables to help illustrate the exact data loss?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 7, 2010 at 1:38 pm
This is the total problem
USE [GamitOlap]
GO
/****** Object: Table [dbo].[testSales] Script Date: 12/07/2010 13:32:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[testSales](
[yrm] [int] NOT NULL,
[part] [varchar](50) NOT NULL,
[sales] [int] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [GamitOlap]
GO
/****** Object: Table [dbo].[testTally] Script Date: 12/07/2010 13:32:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[testTally](
[yrm] [int] NOT NULL
) ON [PRIMARY]
GO
insert into testTally (yrm) values (1)
insert into testTally (yrm) values (2)
insert into testTally (yrm) values (3)
insert into testSales (yrm,part,sales)
values (1,'A',32)
insert into testSales (yrm,part,sales)
values (2,'A',48)
insert into testSales (yrm,part,sales)
values (2,'b',52)
insert into testSales (yrm,part,sales)
values (3,'b',68)
select ISNULL(B.sales,0) as sales
from(SELECT TOP (100) PERCENT sales, part, yrm FROM dbo.testsales WHERE (part = 'A') ) as B right outer join testtally on B.yrm = testtally.yrm order by testtally.yrm
select ISNULL(B.sales,0) as sales
from(SELECT TOP (100) PERCENT sales, part, yrm
FROM dbo.testsales ) as B right outer join testtally on B.yrm = testtally.yrm order by testtally.yrm
December 7, 2010 at 1:44 pm
what I am looking to do is to get 3 rows for each part (6 rows total)
December 7, 2010 at 2:24 pm
So, if I'm correct, you want the output from this:
SELECT
t.yrm,
s.sales,
s.part
FROM
testtally AS t
LEFT JOIN
testsales AS s
ON t.yrm = s.yrm
order by
t.yrm,
s.part
which looks like this:
yrm sales part
----------- ----------- ----------------------
1 32 A
2 48 A
2 52 b
3 68 b
But a Null row for 3/A and a Null row for 1/B?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 7, 2010 at 2:33 pm
Is this what you are looking for? (I don't recommend cartesian products...)
SELECT P.Part, T.YRM, COALESCE(S.Sales,0) Sales
FROM (SELECT DISTINCT Part FROM TestSales) P
CROSS JOIN dbo.TestTally T
LEFT JOIN dbo.TestSales S ON P.Part=S.Part AND T.yrm=S.yrm
Part YRM Sales
A 1 32
A 2 48
A 3 0
b 1 0
b 2 52
b 3 68
(Cleaned up the query)
December 7, 2010 at 2:51 pm
Yes that's what I need, but I don't like cross joins either.
I guess thats what I gotta do tho
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply