Simple Summary Creation - 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 2008 Forums

Transact-SQL (2008)

Simple Summary Creation

Author

Topic

GordonCopestake
Starting Member

8 Posts

Posted - 2012-02-28 : 20:12:24

Hi all,
I'm a little rusty with (T)SQL and am trying what I thought was initially a very simple thing, but is turning out to be more complex than I thought!

I have 3 tables that contain data:

ItemsTable
ItemID(PK)
ItemName

ProductionTable
ProdID(PK,Identity)
ItemID(FK)
Date
QtyProduced

RejectTable
RejectID(PK,Identity)
ItemID(FK)
Date
QtyRejected

Sample Data:
ItemTable:
A1|Apple
O1|Orange
P1|Pear

ProductionTable
1|A1|01/01/12|10
2|O1|01/01/12|15
3|A1|02/01/12|9
4|P1|02/01/12|11

RejectTable
1|A1|01/01/12|2
2|A1|01/01/12|1
3|A1|02/01/12|2

I'm trying to bring all three tables together for a simple summary:

ItemID
QtyProduced(Sum)
QtyRejected(Sum)
PercentRejected(Calc)

Summary Table
A1|19|3|33.33
O1|15|0|0
P1|11|0|0

I'm trying to do this as a stored procedure so I can pass a @StartDate and @EndDate to it. I'm getting hung up on the GROUP BY though and the sub-queries

Any pointers would be appreciated

Thanks in advance

Gordon Copestake

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-28 : 20:31:10

[code]
SELECT i.itemID,p.QtyProduced,COALESCE(r.QtyRejected,0),COALESCE(r.QtyRejected,0)*100.0/p.QtyProduced AS PercentRejected
FROM Items i
CROSS APPLY (SELECT SUM(QtyProduced) AS QtyProduced
FROM ProductionTable
WHERE ItemID = i.ItemID
)p
OUTER APPLY (SELECT SUM(QtyRejected) AS QtyRejected
FROM RejectTable
WHERE ItemID = i.ItemID
)r
[/code]

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

GordonCopestake
Starting Member

8 Posts

Posted - 2012-02-28 : 20:46:22

Hi visakh16,
Thanks for your swift reply. Your answer is very close, but I get the same data in every row!

Does it need a tweak?

GordonCopestake
Starting Member

8 Posts

Posted - 2012-02-29 : 04:22:02

Cracked it! Thanks for your help, it's appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-29 : 12:08:26

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources