SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Adding count to query without duplicating original select query


Adding count to query without duplicating original select query

Author
Message
ue_cakar
ue_cakar
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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.
CodeMuddler
CodeMuddler
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 244
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
ue_cakar
ue_cakar
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search