4 queries to produce 1 result - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

4 queries to produce 1 result

Author

Topic

BIGKX001
Starting Member

1 Post

Posted - 2011-09-24 : 15:05:45

How do I do this in one select query

Query one – or Base Table from fields below
SELECT qryASMCur.REGION, qryASMCur.Area, qryASMCur.[Profit Center], qryASMCur.NAME, qryASMCur.AccountManager, qryASMCur.ASMName, qryASMCur.LocalChain, qryASMCur.Customer, qryASMCur.CustAcct, qryASMCur.BillingDocument, qryASMCur.DistType, qryASMCur.[Calendar day], qryASMCur.Period, qryASMCur.Yr, qryASMCur.Qtr, qryASMCur.MO, qryASMCur.Week, qryASMCur.CalDate, qryASMCur.Cases, qryASMCur.NSP, qryASMCur.Margin, qryASMCur.Returns, qryASMCur.Rtn, qryASMCur.U10Cs, qryASMCur.U10, qryASMCur.[No]
FROM qryASMCur;

ASM is same as Salesmen into “qryDaysASM”
Summarized into a query summarizing Days by Profit Center, ASM#, ASMName, Period, Week, Date
SELECT DISTINCT qryASMCur.[Profit Center], qryASMCur.AccountManager, qryASMCur.ASMName, qryASMCur.Period, qryASMCur.Week, qryASMCur.CalDate
FROM qryASMCur
GROUP BY qryASMCur.[Profit Center], qryASMCur.AccountManager, qryASMCur.ASMName, qryASMCur.Period, qryASMCur.Week, qryASMCur.CalDate;

Then is summarized again into “qryASMDaysCT”
SELECT qryDaysASM.[Profit Center], qryDaysASM.AccountManager, qryDaysASM.ASMName, qryDaysASM.Period, qryDaysASM.Week, Count(qryDaysASM.CalDate) AS Days
FROM qryDaysASM
GROUP BY qryDaysASM.[Profit Center], qryDaysASM.AccountManager, qryDaysASM.ASMName, qryDaysASM.Period, qryDaysASM.Week;

Then finally joined into query “zqryBUCurWkv1”
SELECT qryASMCur.REGION, qryASMCur.Area, qryASMCur.[Profit Center], qryASMCur.NAME, qryASMCur.AccountManager, qryASMCur.ASMName, qryASMCur.Period, qryASMCur.Week, Sum(qryASMCur.Cases) AS Cases, Sum(qryASMCur.NSP) AS NSP, Sum(qryASMCur.Margin) AS Margin, Sum(qryASMCur.Returns) AS Returns, Sum(qryASMCur.Rtn) AS Rtn, Sum(qryASMCur.U10Cs) AS U10Cs, Sum(qryASMCur.U10) AS U10, qryASMDaysCT.Days, Sum(qryASMCur.[No]) AS [No] INTO BUWkReview
FROM qryASMDaysCT RIGHT JOIN qryASMCur ON (qryASMDaysCT.[Profit Center] = qryASMCur.[Profit Center]) AND (qryASMDaysCT.AccountManager = qryASMCur.AccountManager) AND (qryASMDaysCT.Period = qryASMCur.Period) AND (qryASMDaysCT.Week = qryASMCur.Week)
GROUP BY qryASMCur.REGION, qryASMCur.Area, qryASMCur.[Profit Center], qryASMCur.NAME, qryASMCur.AccountManager, qryASMCur.ASMName, qryASMCur.Period, qryASMCur.Week, qryASMDaysCT.Days;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-25 : 01:28:23

[code]SELECT t.*,t1.Days
FROM
(
SELECT [Profit Center], AccountManager, ASMName, Period, Week, Count(CalDate) AS Days,
Sum(Cases) AS Cases, Sum(NSP) AS NSP, Sum(Margin) AS Margin, Sum(Returns) AS Returns, Sum(Rtn) AS Rtn, Sum(U10Cs) AS U10Cs, Sum(U10) AS U10,Sum([No]) AS [No]
FROM qryASMCur
GROUP BY [Profit Center], AccountManager, ASMName, Period, Week;
)t
LEFT JOIN qryASMDaysCT t1
ON (t1.[Profit Center] = t.[Profit Center])
AND (t1.AccountManager = t.AccountManager)
AND (t1.Period = t.Period)
AND (t1.Week = t.Week)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources