SQLTeam.com | Weblogs | Forums

Global field distributed on individual fields


#1

Hi,

I have a sql view that shows Me all active lots on stock, my problem is that I must distribute the QtyAllowed from the oldest to newest lot Is this possible?
PS: The Qtyallowed Is a globe data no has lot assigned thats wy I have to distribute it.

Thanks in advance

Example :

Original Info :
ItemCode | LotNr| LotDate |QtyIn|QtyOut|QtyAllocated |QtyAvailable| QtyReserved |
Item1 | Lot01| 2018-10-26 | 233 |216 | | 17 | 50
Item1 | Lot02| 2018-10-29 | 163 | 1 | | 162 | 50
Item1 | Lot03| 2018-10-31 | 100 | 16 | | 84 | 50
Item1 | Lot04| 2018-11-27 | 80 | 0 | | 80 | 50

Final Info :
ItemCode | LotNr| LotDate |QtyIn|QtyOut|QtyAllocated |QtyAvailable| QtyReserved |
Item1 | Lot01| 2018-10-26 | 233 |216 | 17 | 0 | 50
Item1 | Lot02| 2018-10-29 | 163 | 1 | 33 | 129 | 50
Item1 | Lot03| 2018-10-31 | 100 | 16 | | 84 | 50
Item1 | Lot04| 2018-11-27 | 80 | 0 | | 80 | 50

My code is this :

SELECT TOP (100) PERCENT ItemCode, LotNr, LotDate, QtyReceived, QtyUsed, QtyReserved, QtyAvailable, QtyAllocated
FROM dbo.ViewVM_LotesActivosFull
ORDER BY ItemCode, LotDate


#2

QtyAllowed??? Where do you see a QtyAllowed in column in the any of that? And what do you mean by "must be distributed"? If your example of "Final Info:" is what you want for an output, then you need to explain because, except for Lot2, it would appear that all you've done is shuffle a couple of columns around.


#3

Sorry, its qtyreserved


#4

I apologize. I can't tell from your original and your final examples what you need to have done.