SQL joins trouble

Go To StackoverFlow.com

1

Using Microsoft SQL server 2008 I have a query that is in need of some attention. I'm confused about the joins needed in order to perform my query correctly.

USE [ShaftData]
GO
/****** Object:  StoredProcedure [dbo].[MyPareto]    Script Date: 04/03/2012 19:32:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[MyPareto]
as

SELECT i.pg,
   dbo.OldParetoAnalysis.Pareto,
   i.part,
   i.sumofqty,
   a.sumofqty,
   dbo.NewParetoAnalysis.Pareto

FROM
   OPENQUERY(SACBAUTO, 'SELECT dbo.iLines.Part,
                            dbo.iLines.Pg,
                            SUM(dbo.iLines.Qty) as sumofqty,
                            dbo.iLines.Prefix 
                     FROM Autopart.dbo.iLines 
                     where prefix = ''i''
                     and [datetime] > dateadd(month, -6, getdate())
                     group by 
                     dbo.ilines.pg,
                     dbo.ilines.part,
                     dbo.ilines.prefix
                     order by sumofqty desc') i
RIGHT JOIN
dbo.OldParetoAnalysis
on
i.part collate SQL_Latin1_General_CP1_CI_AS = dbo.OldParetoAnalysis.Part

LEFT JOIN
   OPENQUERY(SACBAUTO, 'SELECT dbo.aLines.Part,
                            dbo.aLines.Pg,
                            SUM(dbo.aLines.Qty) as sumofqty,
                            dbo.aLines.Prefix 
                     FROM Autopart.dbo.aLines 
                     where prefix = ''d''
                     and [datetime] > dateadd(month, -6, getdate())
                     group by 
                     dbo.alines.pg,
                     dbo.alines.part,
                     dbo.alines.prefix
                     order by sumofqty desc') a
ON
dbo.OldParetoAnalysis.Part collate SQL_Latin1_General_CP1_CI_AS = a.part
FULL JOIN
dbo.NewParetoAnalysis
ON
a.part collate SQL_Latin1_General_CP1_CI_AS = dbo.NewParetoAnalysis.Part
WHERE
i.pg = '31'
GROUP BY
   i.pg,
   dbo.OldParetoAnalysis.Pareto,
   i.part,
   i.sumofqty,
   a.sumofqty,
   dbo.NewParetoAnalysis.Pareto
ORDER BY
dbo.OldParetoAnalysis.Pareto desc

Ok the explanation, I need to show those fields. Currently I need all the rows from old pareto to show. new pareto is a clone of the old one that will be used for updating (thats the next part thats planned).

The problem have is that currently my pareto is showing correclty 1-5780. but the new pareto column that should match row for row is showing blanks, this suggests that not all rows are being shown (possibly something to do with Nulls).

I hope its something to do with my joins, as you can see its probably an overcomplicated query, but it works for what I need, problem is the time, its rushed because I have 24 hrs to complete this query with no prior planning (yes I face palmed as well).

Oh and Pareto is just a number assigned to the best selling parts, like a league.

If you require any more info just ask and ill edit.

Many thanks

EDIT: Ok solved the problem, the answer will be updated.

I have got very confused, can someone show me how I would now Update my old and new pareto tables? since theres a ton of joins

2012-04-03 21:51
by lemunk
Yikes... maybe consider changing that OPENQUERY to a view that references your linked server? That would probably go a long way towards simplifying your query so it's easier to get your head around - Michael Fredrickson 2012-04-03 21:54
yup after I went for a smoke I finally cracked this sucker. require me to change the layout of the query and joins. Ill post the edited version if anyone is intereste - lemunk 2012-04-03 22:04
If you still have questions, I'd update your question. If your updated query solved your problems, I'd post it as an answer to your own question then mark it as the accepted answer - Michael Fredrickson 2012-04-03 22:10
Just make sure you post it as an answer. Don't edit your original question, ok - Robert Koritnik 2012-04-03 22:11
yeh will do, just cant do it yet :( didnt think ied be able to crack it. but had a moment of clarity, amazing what nicotine can do haha - lemunk 2012-04-03 22:21
update to the question, edited my post, or should I post a new question - lemunk 2012-04-04 08:44


1

heres the Code:

USE [ShaftData]
GO
/****** Object:  StoredProcedure [dbo].[MyPareto]    Script Date: 04/04/2012 08:50:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[MyPareto]
@pgParam varchar(255)
AS
SELECT i.pg,
   dbo.OldParetoAnalysis.Pareto,
   i.part,
   i.sales6months,
   a.LostSales6Months,
   dbo.NewParetoAnalysis.Pareto

FROM
OPENQUERY(SACBAUTO, 'SELECT dbo.iLines.Part,
                            dbo.iLines.Pg,
                            SUM(dbo.iLines.Qty) as sales6months,
                            dbo.iLines.Prefix 
                     FROM Autopart.dbo.iLines 
                     where prefix = ''i''
                     and [datetime] > dateadd(month, -6, getdate())
                     group by 
                     dbo.ilines.pg,
                     dbo.ilines.part,
                     dbo.ilines.prefix
                     order by sales6months desc') i
RIGHT JOIN
dbo.OldParetoAnalysis
on
i.part collate SQL_Latin1_General_CP1_CI_AS = dbo.OldParetoAnalysis.Part
INNER JOIN
dbo.NewParetoAnalysis
ON
dbo.OldParetoAnalysis.Part collate SQL_Latin1_General_CP1_CI_AS = dbo.NewParetoAnalysis.Part

LEFT JOIN
OPENQUERY(SACBAUTO, 'SELECT dbo.aLines.Part,
                            dbo.aLines.Pg,
                            SUM(dbo.aLines.Qty) as LostSales6Months,
                            dbo.aLines.Prefix 
                     FROM Autopart.dbo.aLines 
                     where prefix = ''d''
                     and [datetime] > dateadd(month, -6,   getdate())
                     group by 
                     dbo.alines.pg,
                     dbo.alines.part,
                     dbo.alines.prefix
                     order by LostSales6Months desc') a
ON
dbo.NewParetoAnalysis.Part collate SQL_Latin1_General_CP1_CI_AS = a.part
/*FULL OUTER JOIN
dbo.NewParetoAnalysis
ON
a.part collate SQL_Latin1_General_CP1_CI_AS = dbo.NewParetoAnalysis.Part*/
WHERE
i.pg = @pgParam
GROUP BY
   i.pg,
   dbo.OldParetoAnalysis.Pareto,
   i.part,
   i.sales6months,
   a.LostSales6Months,
   dbo.NewParetoAnalysis.Pareto
ORDER BY
dbo.OldParetoAnalysis.Pareto asc

Now i have a new question. Using c sharp, how would i update the Old and New pareto tables?

2012-04-04 08:57
by lemunk