Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Adding count to query without duplicating original select query Expand / Collapse
Author
Message
Posted Tuesday, August 5, 2014 8:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 11, 2014 10:55 PM
Points: 2, Visits: 8
Hello ladies and gentlemen,
I have the following code.
SELECT     _bvSerialMasterFull.SerialNumber, _bvSerialMasterFull.SNStockLink, _bvSerialMasterFull.SNDateLMove, _bvSerialMasterFull.CurrentLoc, 
_bvSerialMasterFull.CurrentAccLink, _bvSerialMasterFull.StockCode, _bvSerialMasterFull.CurrentAccount, _bvSerialMasterFull.CurrentLocationDesc,
_bvSerialNumbersFull.SNTxDate, _bvSerialNumbersFull.SNTxReference, _bvSerialNumbersFull.SNTrCodeID, _bvSerialNumbersFull.SNTransType,
_bvSerialNumbersFull.SNWarehouseID, _bvSerialNumbersFull.TransAccount, _bvSerialNumbersFull.TransTypeDesc,
_bvSerialNumbersFull.SerialNumber AS Expr1, _bvSerialNumbersFull.SNStockLink AS Expr2, _bvSerialNumbersFull.WarehouseCode,
_bvSerialNumbersFull.TrCode, _bvSerialNumbersFull.CurrentLocationDesc AS Expr3, _bvSerialNumbersFull.CurrentAccount AS Expr5,
WhseMst.Name, _btblInvoiceLineSN.cSerialNumber, _btblInvoiceLines.fUnitPriceExcl, StkItem.Code, StkItem.AveUCst, StkItem.ItemGroup
FROM _btblInvoiceLineSN INNER JOIN
_btblInvoiceLines ON _btblInvoiceLineSN.iSerialInvoiceLineID = _btblInvoiceLines.idInvoiceLines INNER JOIN
_bvSerialMasterFull INNER JOIN
_bvSerialNumbersFull ON _bvSerialMasterFull.SerialCounter = _bvSerialNumbersFull.SNLink INNER JOIN
WhseMst ON _bvSerialNumbersFull.SNWarehouseID = WhseMst.WhseLink ON
_btblInvoiceLines.iStockCodeID = _bvSerialMasterFull.SNStockLink AND
_btblInvoiceLineSN.cSerialNumber = _bvSerialMasterFull.SerialNumber INNER JOIN
StkItem ON _bvSerialMasterFull.SNStockLink = StkItem.StockLink
WHERE (_bvSerialNumbersFull.SNTransType = 8) AND (_bvSerialMasterFull.CurrentLoc = 1) AND (StkItem.ItemGroup IN ('010', '020', '030', '040', '050', '060',
'070', '080', '100', '150', '300', '400'))
ORDER BY StkItem.ItemGroup, StkItem.Code, _bvSerialNumbersFull.SNTxDate

What this does is basically get's an inventory of certain items and does an aging analysis on them. However, the way the system works connected to this database there are some issues that need to be sorted out. This solution requires that a count on the serial number be made on the resultant dataset of this query and that count column be added in for the respective serial records.

Now the count bit is easy, and one solution I wrote is:
SELECT     _bvSerialMasterFull.SerialNumber, COUNT(_bvSerialMasterFull.SerialNumber) AS SerialCount
FROM _btblInvoiceLineSN INNER JOIN
_btblInvoiceLines ON _btblInvoiceLineSN.iSerialInvoiceLineID = _btblInvoiceLines.idInvoiceLines INNER JOIN
_bvSerialMasterFull INNER JOIN
_bvSerialNumbersFull ON _bvSerialMasterFull.SerialCounter = _bvSerialNumbersFull.SNLink INNER JOIN
WhseMst ON _bvSerialNumbersFull.SNWarehouseID = WhseMst.WhseLink ON
_btblInvoiceLines.iStockCodeID = _bvSerialMasterFull.SNStockLink AND
_btblInvoiceLineSN.cSerialNumber = _bvSerialMasterFull.SerialNumber INNER JOIN
StkItem ON _bvSerialMasterFull.SNStockLink = StkItem.StockLink
WHERE (_bvSerialNumbersFull.SNTransType = 8) AND (_bvSerialMasterFull.CurrentLoc = 1) AND (StkItem.ItemGroup IN ('010', '020', '030', '040', '050', '060',
'070', '080', '100', '150', '300', '400'))
GROUP BY _bvSerialMasterFull.SerialNumber

Thus, using an inner join with the original query we can have:
SELECT     Cnt.SerialCount, _bvSerialMasterFull.SerialNumber, _bvSerialMasterFull.SNStockLink, _bvSerialMasterFull.SNDateLMove, _bvSerialMasterFull.CurrentLoc, 
_bvSerialMasterFull.CurrentAccLink, _bvSerialMasterFull.StockCode, _bvSerialMasterFull.CurrentAccount, _bvSerialMasterFull.CurrentLocationDesc,
_bvSerialNumbersFull.SNTxDate, _bvSerialNumbersFull.SNTxReference, _bvSerialNumbersFull.SNTrCodeID, _bvSerialNumbersFull.SNTransType,
_bvSerialNumbersFull.SNWarehouseID, _bvSerialNumbersFull.TransAccount, _bvSerialNumbersFull.TransTypeDesc,
_bvSerialNumbersFull.SerialNumber AS Expr1, _bvSerialNumbersFull.SNStockLink AS Expr2, _bvSerialNumbersFull.WarehouseCode,
_bvSerialNumbersFull.TrCode, _bvSerialNumbersFull.CurrentLocationDesc AS Expr3, _bvSerialNumbersFull.CurrentAccount AS Expr5,
WhseMst.Name, _btblInvoiceLineSN.cSerialNumber, _btblInvoiceLines.fUnitPriceExcl, StkItem.Code, StkItem.AveUCst, StkItem.ItemGroup
FROM _btblInvoiceLineSN INNER JOIN
_btblInvoiceLines ON _btblInvoiceLineSN.iSerialInvoiceLineID = _btblInvoiceLines.idInvoiceLines INNER JOIN
_bvSerialMasterFull INNER JOIN
_bvSerialNumbersFull ON _bvSerialMasterFull.SerialCounter = _bvSerialNumbersFull.SNLink INNER JOIN
WhseMst ON _bvSerialNumbersFull.SNWarehouseID = WhseMst.WhseLink ON
_btblInvoiceLines.iStockCodeID = _bvSerialMasterFull.SNStockLink AND
_btblInvoiceLineSN.cSerialNumber = _bvSerialMasterFull.SerialNumber INNER JOIN
StkItem ON _bvSerialMasterFull.SNStockLink = StkItem.StockLink
INNER JOIN (
SELECT _bvSerialMasterFull.SerialNumber, COUNT(_bvSerialMasterFull.SerialNumber) AS SerialCount
FROM _btblInvoiceLineSN INNER JOIN
_btblInvoiceLines ON _btblInvoiceLineSN.iSerialInvoiceLineID = _btblInvoiceLines.idInvoiceLines INNER JOIN
_bvSerialMasterFull INNER JOIN
_bvSerialNumbersFull ON _bvSerialMasterFull.SerialCounter = _bvSerialNumbersFull.SNLink INNER JOIN
WhseMst ON _bvSerialNumbersFull.SNWarehouseID = WhseMst.WhseLink ON
_btblInvoiceLines.iStockCodeID = _bvSerialMasterFull.SNStockLink AND
_btblInvoiceLineSN.cSerialNumber = _bvSerialMasterFull.SerialNumber INNER JOIN
StkItem ON _bvSerialMasterFull.SNStockLink = StkItem.StockLink
WHERE (_bvSerialNumbersFull.SNTransType = 8) AND (_bvSerialMasterFull.CurrentLoc = 1) AND (StkItem.ItemGroup IN ('010', '020', '030', '040', '050', '060',
'070', '080', '100', '150', '300', '400'))
GROUP BY _bvSerialMasterFull.SerialNumber
) Cnt ON Cnt.SerialNumber=_bvSerialMasterFull.SerialNumber
WHERE (_bvSerialNumbersFull.SNTransType = 8) AND (_bvSerialMasterFull.CurrentLoc = 1) AND (StkItem.ItemGroup IN ('010', '020', '030', '040', '050', '060',
'070', '080', '100', '150', '300', '400'))

However, as you can see, the original select query is run twice and joined together.

What I was hoping for is this to be done in the original query without the need to duplicate the original query. Can this be done? If so, your ideas, inputs and code is much appreciated.

Thanks in advance.
Post #1600011
Posted Tuesday, August 5, 2014 8:50 PM This worked for the OP Answer marked as solution
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 10:19 AM
Points: 36, Visits: 238
I think this may do what you want?


SELECT					COUNT(*) OVER (PARTITION BY _bvSerialMasterFull.SerialNumber) AS SerialCount,
_bvSerialMasterFull.SerialNumber, _bvSerialMasterFull.SNStockLink, _bvSerialMasterFull.SNDateLMove, _bvSerialMasterFull.CurrentLoc,
_bvSerialMasterFull.CurrentAccLink, _bvSerialMasterFull.StockCode, _bvSerialMasterFull.CurrentAccount, _bvSerialMasterFull.CurrentLocationDesc,
_bvSerialNumbersFull.SNTxDate, _bvSerialNumbersFull.SNTxReference, _bvSerialNumbersFull.SNTrCodeID, _bvSerialNumbersFull.SNTransType,
_bvSerialNumbersFull.SNWarehouseID, _bvSerialNumbersFull.TransAccount, _bvSerialNumbersFull.TransTypeDesc,
_bvSerialNumbersFull.SerialNumber AS Expr1, _bvSerialNumbersFull.SNStockLink AS Expr2, _bvSerialNumbersFull.WarehouseCode,
_bvSerialNumbersFull.TrCode, _bvSerialNumbersFull.CurrentLocationDesc AS Expr3, _bvSerialNumbersFull.CurrentAccount AS Expr5,
WhseMst.Name, _btblInvoiceLineSN.cSerialNumber, _btblInvoiceLines.fUnitPriceExcl, StkItem.Code, StkItem.AveUCst, StkItem.ItemGroup
FROM _btblInvoiceLineSN INNER JOIN
_btblInvoiceLines ON _btblInvoiceLineSN.iSerialInvoiceLineID = _btblInvoiceLines.idInvoiceLines INNER JOIN
_bvSerialMasterFull INNER JOIN
_bvSerialNumbersFull ON _bvSerialMasterFull.SerialCounter = _bvSerialNumbersFull.SNLink INNER JOIN
WhseMst ON _bvSerialNumbersFull.SNWarehouseID = WhseMst.WhseLink ON
_btblInvoiceLines.iStockCodeID = _bvSerialMasterFull.SNStockLink AND
_btblInvoiceLineSN.cSerialNumber = _bvSerialMasterFull.SerialNumber INNER JOIN
StkItem ON _bvSerialMasterFull.SNStockLink = StkItem.StockLink
WHERE (_bvSerialNumbersFull.SNTransType = 8) AND (_bvSerialMasterFull.CurrentLoc = 1) AND (StkItem.ItemGroup IN ('010', '020', '030', '040', '050', '060',
'070', '080', '100', '150', '300', '400'))
ORDER BY StkItem.ItemGroup, StkItem.Code, _bvSerialNumbersFull.SNTxDate



blog: http://www.codemuddler.com
twitter: http://www.twitter.com/CodeMuddler
Post #1600016
Posted Tuesday, August 5, 2014 10:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 11, 2014 10:55 PM
Points: 2, Visits: 8
Brilliant!

Does exactly as I was hoping for. Great, Thank you very much for your response. Now I guess one needs to reap up on Partition BY to understand how it works.
Post #1600036
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse