Discussion :

[adrien_as]

Bonjour

Voici mon défi :
J'ai une table qui recense tous les items de mon stock et une autre qui mémorise toutes les transactions faites sur chaque item (pour faire simple, 2 mouvements : entrée ou sortie).
Je souhaite écrire une requête qui me ressorte l'évolution du stock en indiquant jour par jour, mouvement par mouvement (auquel on associe une quantité), le solde à la fin de chaque ligne.

Voici la requête actuelle :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
select b.catno,convert(varchar,t.transaction_dt,103) as date,
	case t.transaction_type
		when 'CloseReception' then 'E'
		when 'Consume' then 'S'
	else
		t.transaction_type
	end as mouvement,
	count(it.inventoryid) as quantite
 
from transactions t
join inventory_transactions it on it.transaction_id = t.transaction_id
join inventory_batch ib on ib.inventoryid = it.inventoryid
join batch_info b on b.batchid=ib.batchid
 
where t.transaction_type <> 'AddReception'
and b.catno='GOSSTR95-23'
 
group by b.catno,
	convert(varchar,t.transaction_dt,103),t.transaction_type

où
- TRANSACTIONS est la table des mouvements
- INVENTORY_TRANSACTIONS est le mouvement de chaque item
- INVENTORY_BATCH relie un item à un lot
- BATCH_INFO est le lot
NB : les 2 dernières tables permettent de faire la sélection par rapport à une référence fournisseur, ici : GOSSTR95-23.
NB2 : on enlève les transactions de AddReception qui font doublon avec 'CloseReception'

Cela donne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
catno		date		mouvement	quantite
GOSSTR95-23	09/07/2009	E		8
GOSSTR95-23	16/07/2009	E		2
GOSSTR95-23	17/07/2009	S		1

Il me manque juste une colonne supplémentaire qui indique le solde au fur età mesure, tel que :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
catno		date		mouvement	quantite	solde
GOSSTR95-23	09/07/2009	E		8		8
GOSSTR95-23	16/07/2009	E		2		10
GOSSTR95-23	17/07/2009	S		1		9

J'ai essayé avec les fonction SUM et COUNT cumulées mais cela ne marche pas et surtout je ne sais pas comment réaliser un cumul en récupérant la valeur du solde précédent ! La difficulté est que la quantité est déjà un champ calculé (COUNT des items) et qu'il faut distinguer les entrées et les sorties !

Mes différentes recherches sur ce forum ne m'ont pas apportées de solution non plus, cela ressemble pourtant à un "cas d'école" ? Il doit donc y avoir une réponse simple.

Merci d'avance pour votre aide !

Adrien

PS : SGBD = SQL Server 2005

[Waldar]

SQL Server n'a pas encore implémenté la partie des fonctions analytiques qui permet de faire du cumul, ni les fonctions analytique lead / lag.

Il vous faut faire une autre requête qui le calcule pour vous.

[adrien_as]

Merci de votre réponse.

J'avais effectivement lu des propositions à propos de sujets similaires sur ce forum mais plutôt orientés Oracle. Je n'ai donc jamais pu les exécuter sur ma base SQL Server.

Le souci est qu'il me faut qu'une seule requête à exécuter car je souhaite créer une vue qui sera utilisée dans un rapport Crytal. Avez-vous une idée de ce qu'il est possible de faire dans ce sens ?

[Waldar]

Ce serait peut-être plus simple de faire la somme cumulative dans cristal, car oui des solutions existent mais elles grèvent les performances, après ça dépendra de vos machines et de la volumétrie de vos données.

En gros il faut refaire exactement la même requête que celle que vous avez, faire la jointure exacte sur la clef (cat_no, transaction_type) et faire une jointure type <= sur la date.

D'ailleurs attention, il est préférable de remettre votre case dans le group by également. Peut-être que dans ce cas de figure comme vous ne faites qu'une transcodification ce n'est pas nécessaire mais c'est une bonne habitude à prendre.