[SQL SERVER]: Stored procedure per recuperare quantità di lotti in magazzino

[valeria75]

Scusate, forse sono OT ma non sapevo dove postare vorrei creare una stored procedure per gestire l'utilizzo dei lotti di produzione, mi spiego: partendo da una quantità richiesta in un ordine, io devo andare a recupeare le quantità dai lotti presenti a magazzino.

Quindi, procedo per steps:

Recupero tutte le mie disponibilità di lotti
SELECT LottoProduzione, (Qta-QtaConsumataLotto) AS Qta FROM TabLotti WHERE (IDArt = XXXX) ORDER BY Data ASC

Faccio (Qta-QtaConsumataLotto) AS Qta perchè per ogni lotto caricato a magazzino indico anche le quantità già utilizzate nell'evasione di altri ordini (QtaConsumataLotto)


In funzione di una variabile che passerò alla Stored procedure

@QtRichiesta int

conosco la quantità richiesta per uno specifico articolo (dato che eseguirò questa SP per ogni articolo dell'ordine)

Devo fare un insert (in una seconda tabella) di tutte le righe, ad esaurimento, che raggiungono la mia @QtRichiesta

Quindi praticamente:

se la prima select mi ritorna dei record di questo tipo

Lotto - Qt
00012006 4
00052006 2

vuol dire che per l'articolo indicato ho 6 disponibilità di 2 lotti differenti


se la mia @QtRichiesta è 5

devo inserire nella mia tabella di appoggio
00012006 4
00052006 1

partendo ovviamente dal record (lotto) piu vecchio (00012006) fino al piu recente (infatti ho ordinato la select in questo modo)

tutto questo per sapere quali lotti utilizzare e le quantità da utilizzare per ogni lotto


Spero di essere stata chiara... io ci ho provato ma mi perdo in quanto non saprei come "iterare" nei risultati della mia select per recuperare SOLO le quantità che soddisfano la mia quantità richiesta!

Grazie per l'aiuto

[alka]

Originariamente inviato da valeria75
Scusate, forse sono OT ma non sapevo dove postare vorrei creare una stored procedure per gestire l'utilizzo dei lotti di produzione, mi spiego: partendo da una quantità richiesta in un ordine, io devo andare a recupeare le quantità dai lotti presenti a magazzino.

Più che OT, mi sembra strano titolare una discussione su un database con VB.NET/C# quando si sta parlando di un database che, comunque, è un argomento affrontato in generale qui... omettendo però in compenso di quale database stiamo parlando.

Fornisci i dati mancanti e provvedo a correggere il titolo e a spostare la discussione, se necessario, nell'area appropriata.

Ciao!

[valeria75]

Oppsssss il DB è SQL Server (hai ragione )... per quanto riguarda il titolo non posso piu modificarlo!!!

[alka]

Originariamente inviato da valeria75
Oppsssss il DB è SQL Server (hai ragione )... per quanto riguarda il titolo non posso piu modificarlo!!!

Ho spostato e modificato la discussione, correggendo anche il titolo.

Ciao!

[valeria75]

Nessun aiutino ???

[sspintux]

Originariamente inviato da valeria75
....
vorrei creare una stored procedure per gestire l'utilizzo dei lotti di produzione, mi spiego: partendo da una quantità richiesta in un ordine, io devo andare a recupeare le quantità dai lotti presenti a magazzino.

Quindi, procedo per steps:

Recupero tutte le mie disponibilità di lotti
SELECT LottoProduzione, (Qta-QtaConsumataLotto) AS Qta FROM TabLotti WHERE (IDArt = XXXX) ORDER BY Data ASC

Faccio (Qta-QtaConsumataLotto) AS Qta perchè per ogni lotto caricato a magazzino indico anche le quantità già utilizzate nell'evasione di altri ordini (QtaConsumataLotto)


In funzione di una variabile che passerò alla Stored procedure

@QtRichiesta int

conosco la quantità richiesta per uno specifico articolo (dato che eseguirò questa SP per ogni articolo dell'ordine)

Devo fare un insert (in una seconda tabella) di tutte le righe, ad esaurimento, che raggiungono la mia @QtRichiesta

Quindi praticamente:

se la prima select mi ritorna dei record di questo tipo

Lotto - Qt
00012006 4
00052006 2

vuol dire che per l'articolo indicato ho 6 disponibilità di 2 lotti differenti

se la mia @QtRichiesta è 5

devo inserire nella mia tabella di appoggio
00012006 4
00052006 1

partendo ovviamente dal record (lotto) piu vecchio (00012006) fino al piu recente (infatti ho ordinato la select in questo modo)

tutto questo per sapere quali lotti utilizzare e le quantità da utilizzare per ogni lotto
.....
Grazie per l'aiuto

Ciao valeria75,

Puoi usare un cursore (cerca DECLARE CURSOR nei BOL e troverai molti esempi esaustivi)
oppure puoi provare con un approccio più 'set based' come quello che segue
(da provare nel Query Analizer);

...vedi tu quale sia il migliore e come adattarlo al meglio nel tuo caso

Use TempDb

go

-- creazione e popolamento tabella di test
create table dbo.TabLotti
(
 Lotto varchar(10), 
 Articolo varchar(10), 
 Data datetime, 
 QtaProdotta int,  
 QtaImpegnata int
)

go

insert dbo.TabLotti values('Lotto01','art1', '20060101',5,2) 
insert dbo.TabLotti values('Lotto01','art1', '20060102',2,2)
insert dbo.TabLotti values('Lotto02', 'art1','20060101',4,3)
insert dbo.TabLotti values('Lotto02', 'art1','20060102',6,2)
insert dbo.TabLotti values('Lotto05', 'art1','20060101',3,2)
insert dbo.TabLotti values('Lotto05', 'art1','20060102',3,2)

go

Create Function dbo.fnProgQtaDisponibile
--Funzione di utilità per il calcolo Progressivo disponibilità 
(
 @Art varchar(10),
 @Lotto varchar(10),
 @Data DateTime,
 @QtaDispGiornoLotto int
)
Returns int
AS
Begin 
 Return
 (
       (
        select IsNull(sum(T2.QtaProdotta-T2.QtaImpegnata),0) 
        from tabLotti T2 
        where T2.Articolo=@Art
          and ((T2.Data<=@Data and T2.Lotto<@Lotto) or (T2.Data<@Data))
       ) + @QtaDispGiornoLotto 
  )
End

go

-- corpo sp principale da creare
-- N.B. non verifica se la disponibiltà copre interamente la richiesta 

declare  @Art varchar(10) -- articolo
declare  @QtaRic int      -- qta richiesta

set @Art='art1'

--set @QtaRic= 1
--set @QtaRic= 4
--set @QtaRic= 5
set @QtaRic= 6


select T1.Data,
       T1.Lotto,
       T1.QtaProdotta,
       T1.QtaImpegnata,
       (T1.QtaProdotta-T1.QtaImpegnata) as QtaDisponibile,
       --dbo.fnProgQtaDisponibile(@Art,T1.Lotto,T1.Data,(T1.QtaProdotta-T1.QtaImpegnata)) as ProgressivoQtaDisponibile,
       (case when 
         dbo.fnProgQtaDisponibile(@Art,T1.Lotto,T1.Data,(T1.QtaProdotta-T1.QtaImpegnata))<=@QtaRic
        then 
         (T1.QtaProdotta-T1.QtaImpegnata)
       else 
	 @QtaRic - dbo.fnProgQtaDisponibile(@Art,T1.Lotto,T1.Data,0)
        end) as QtaDaImpegnare
from tabLotti T1
where (T1.Articolo=@Art) 
  and (T1.QtaProdotta > T1.QtaImpegnata)
  and (case when 
         dbo.fnProgQtaDisponibile(@Art,T1.Lotto,T1.Data,(T1.QtaProdotta-T1.QtaImpegnata))<=@QtaRic
       then 
         (T1.QtaProdotta-T1.QtaImpegnata)
       else 
	 @QtaRic - dbo.fnProgQtaDisponibile(@Art,T1.Lotto,T1.Data,0)
       end)> 0  
order by T1.Data,T1.Lotto

go

-- pulizia
drop Function dbo.fnProgQtaDisponibile
drop table dbo.TabLotti

Ciao sspintux

[valeria75]

Grazie infinite per l'aiuto... provo subito ad applicare la tua soluzione!

solo un dubbio, cosa intendi con :

non verifica se la disponibiltà copre interamente la richiesta

[valeria75]

Ho verificato la logica e c'è qualche cosa che non mi torna

nella funzione non credo che sia necessario passare il lotto (anche perchè il lotto devo cercarlo)

io devo cercare tutti i lotti "disponibili" (non esauriti) per l'articolo considerato (ordinati per data)


Nella SP invece dovrei fare un insert degli X lotti utilizzati per soddisfare la mia richiesta in una seconda tabella (tabella2) del tipo:
QtaUtilizzata
Lotto

dove, per ogni lotto, indico la quantità utilizzata


come già indicato il lotto potrebbe essere uno solo (se riesce a soddisfare tutta la mia richiesta) oppure piu lotti ...

Grazie ancora

[sspintux]

Originariamente inviato da valeria75
Ho verificato la logica e c'è qualche cosa che non mi torna

nella funzione non credo che sia necessario passare il lotto (anche perchè il lotto devo cercarlo)

io devo cercare tutti i lotti "disponibili" (non esauriti) per l'articolo considerato (ordinati per data)


Nella SP invece dovrei fare un insert degli X lotti utilizzati per soddisfare la mia richiesta in una seconda tabella (tabella2) del tipo:
QtaUtilizzata
Lotto

dove, per ogni lotto, indico la quantità utilizzata


come già indicato il lotto potrebbe essere uno solo (se riesce a soddisfare tutta la mia richiesta) oppure piu lotti ...

Grazie ancora

...non so se ho capito bene qual'è il problema ,
ma come puoi notare dai dati di esempio che ho fatto in precedenza
ho ipotizzato che ad una stessa data possano esserci più
Lotti diversi con lo stesso articolo e che lo stesso Lotto
si possa ripetere in date diverse;

infatti nel caso in cui la Qta Richietsa (@QtaRic) sia 6 l'output che vedrai
nel query analizer - senza bisogno di sapere quale lotto passare alla funzione - è :

(non ho fatto l'insert nella tabella perchè mi sembra banale modificarlo in tal senso)

Data               Lotto       Qta da impegnare 
-----------     ------- ... ----------------
2006-01-01    Lotto01		3
2006-01-01    Lotto02		1
2006-01-01    Lotto05		1
2006-01-02    Lotto02		1

invece con questi altri dati in cui c'è un solo lotto per data
(e che forse rispecchiano meglio il problema)
e quantità richiesta pari sempre a 6

insert dbo.TabLotti values('Lotto01','art1', '20060101',5,2) 
insert dbo.TabLotti values('Lotto02','art1', '20060102',2,2)
insert dbo.TabLotti values('Lotto03', 'art1','20060103',4,3)
insert dbo.TabLotti values('Lotto04', 'art1','20060104',6,2)
insert dbo.TabLotti values('Lotto05', 'art1','20060105',3,2)
insert dbo.TabLotti values('Lotto06', 'art1','20060106',3,2)

l'output sarà:

Data               Lotto       Qta da impegnare 
-----------     ------- ... ----------------
2006-01-01     Lotto01	    3
2006-01-03     Lotto03	    1
2006-01-04     Lotto04	    2

...se il caso è il secondo tra quelli fatti sopra direi che le cose si semplificano
e, anche se mi sembra che già funzioni così com'è, si può migliorare;

....come già detto prova ad adattarla al tuo caso e se ci sono problemi
, tempo permettendo, vediamo il da farsi;

(ti ricordo anche che volendo puoi usare un cursore)

[valeria75]

Prima di tutto ti ringrazio per l'aiuto e per le risposte dettagliate.

La tua soluzione va benissimo (preferirei non usare i cursori) ma ho due dubbi (porta pazienza)

Come fai ad ottenere un output di questo tipo dalla funzione??

Data Lotto Qta da impegnare
----------- ------- ... ----------------
2006-01-01 Lotto01 3
2006-01-01 Lotto02 1
2006-01-01 Lotto05 1
2006-01-02 Lotto02 1

Io ottengo un solo numero come output..... e, in ogni caso, non capisco perchè passare il lotto alla funzione, io non posso saperlo a priori (devo ottenerlo in base alle disponibilità)

Create Function dbo.fnProgQtaDisponibile
(
@Art varchar(10),
@Lotto varchar(10), <- ****** ????
@Data DateTime,
@QtaDispGiornoLotto int
)

Returns .....

La funzione dovrebbe ritornarmi i lotti da utilizzare (quelli liberi, verificando: QtaProdotta - QtaImpegnata) e per questi lotti dovrei fare l'insert, e questo è il mio secondo problema:

una volta ottenuto l'elenco (uno o piu) dei lotti disponibili, come posso ciclare tra i risultati della funzione per effettuare l'insert in una seconda tabella (Tabella2)

Grazie ancora per l'aiuto