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
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?
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