Discussion :

[kent69003]

Bonjour à tous,

Actuellement en alternance en 2ème année de DUT, j'effectue des requêtes sous sql server. Je m'en remets à vous car j'ai beau chercher, je ne trouve pas ma réponse et mon tuteur est en vacance donc il ne peut pas m'aider...

En gros j'ai une table sur laquelle j'ai des données, elle s'appelle 'factweeklyinventory'.
Voici sa composition :

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
21
22
23
24
timeId
ProductID
ShipperID
quantity
supplierPurchasePriceValue
PurchasePriceValue
revenueValue
OutOfStock
salesQuantity
salesPeriod
saleQuantity_W_1
lastEntry
firstEntry
firstExpectedQty
firstExpectedDate
firstExpectedDelay
nextExpectedQty
nextExpectedDate
nextExpectedDelay
supplierDelay
shopSalesQuantity
shopNumber
targetRotationDay
rotationClass

Les champs qui m'intéresse sur cette table sont le timeid, productid, shipperid uniquement.

Il se trouve qu'un utilisateur aimerait avoir un nouveau champ, currentshippertype qui serait pareil que le shipperid mais à la dernière date donnée d'un produit.
En effet, les données de la table factweeklyinventory sont historisées de façon hebdomadaire, donc on a le shipper pour un produit chaque samedi.
Cependant, l'utilisateur souhaite obtenir un shipper pour ce meme produit mais à la date à jour pour faire des comparaisons.

Les autres tables dont j'ai besoin pour construire ce nouveau champ sont : bc, bc_prod, shipper

La requête qui permet d'avoir la date avec le shipper correspondant à la date pour le produit :

Code :

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

1
2
3
4
select dat, product_ref, shipper from bc
inner join bc_prod on bc.id=bc_id
inner join shipper on bc.shipper=shipper.name and main=1
order by dat desc

Voici la requête que j'ai essayé de faire mais ça ne donne rien de concluant :

Code :

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

1
2
3
4
5
6
select timeid, productid, dat, shipperid, shipper as currentshippertype
from bc 
inner join bc_prod on bc.id=bc_id
inner join shipper on bc.shipper=shipper.name and main=1
inner join FactWeeklyInventory on FactWeeklyInventory.productid=bc_prod.product_ref and FactWeeklyInventory.timeid=dat
where datepart(weekday,cast(bc.DAT as datetime))=7

J'obtiens en effet peu de lignes par rapport à ce que je suis censé avoir (138 170 lignes).

Ma table factweeklyinventory comporte 966 865 lignes et comme vous l'avez compris il faut que ma jointure permette d'obtenir un currentshippertype pour chaque ligne avec la date à jour.
J'espère avoir été assez clair dans mon problème ^^
Je ne sais pas si la jointure est la meilleur option ? un curseur serait-il plus judicieux ?

Merci à tous ceux qui éclaireront ma lanterne.

Kent69003

[elsuket]

Bonjour,

Si j'ai bien compris, il vous faut le shipper le plus récent pour chaque ligne de la table dbo.FactWeeklyInventory. Donc on a deux solutions :

- Soit on va chercher la dernière date pour le shipper et le product_ref, et on fait la jointure avec dbo.FactWeeklyInventory : c'est le plus proche de l'expression de votre requête :

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
SELECT		FWI.*
		, S.shipper
FROM		dbo.FactWeeklyInventory AS FWI
INNER JOIN	(
			SELECT		shipper
					, bc_prod.product_ref
					, MAX(dat) AS max_dat
			FROM		bc
			INNER JOIN	bc_prod ON bc.id=bc_id
			INNER JOIN	shipper ON bc.shipper=shipper.name
			WHERE		main=1
			AND		DATEPART(weekday, CAST(bc.DAT AS datetime))=7
			GROUP BY	shipper, bc_prod.product_ref
		) AS S
			ON S.max_dat = FWI.timeid
			AND FWI.productid = S.product_ref

Ceci implique que la sous-requête est calculée d'abord, pour ensuite réaliser la jointure; soit on fait faire le calcul pour chaque ligne de la table dbo.FactWeeklyInventory, à l'aide de l'opérateur APPLY :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT		FWI.*
		, S.shipper
FROM		dbo.FactWeeklyInventory AS FWI
CROSS APPLY	(
			SELECT		TOP 1 shipper
			FROM		bc
			INNER JOIN	bc_prod ON bc.id=bc_id
			INNER JOIN	shipper ON bc.shipper=shipper.name
			WHERE		main=1
			AND		FWI.productid = bc_prod.product_ref
			AND		DATEPART(weekday, CAST(bc.DAT AS datetime))=7
			ORDER BY		dat DESC
		) AS S

Il faut ensuite considérer les performances de l'une et de l'autre. Essayez et dites-nous ce qu'il en est

Petites remarques :

- Il est utile d'aliaser tous les objets (tables, vues, ou fonctions de table), de façon à pouvoir réutiliser les alias dans toute la requête.
Ceci permet de savoir de quel objet la colonne est extraite, et de réutiliser les mêmes objets dans la même requête, au besoin.

- Le filtre DATEPART(weekday, CAST(bc.DAT AS datetime))=7 forcera le moteur à lire toute la table ou l'index portant sur cette colonne.
Le moteur est renseigné sur les dates qui sont dans la colonne DAT, mais pas de quel jour de la semaine il s'agit.
En effet, stocker tous les résultats possibles de l'application de fonctions sur les colonnes prendrait un temps de calcul et d'espace de stockage conséquent.

L'idéal est donc d'avoir une table utilitaire de calendrier sur laquelle on peut réaliser une jointure.
Une telle table précise, entre autres, le jour de la semaine (mais aussi, par exemple, l'année, le semestre, le trimestre, le mois, la semaine, ... au besoin)

- CAST(bc.DAT AS datetime) implique que le type de la colonne DAT n'est pas datetime; espérons que ce ne soit pas varchar ou nvarchar ou int

@++

[kent69003]

Salut,

Merci pour ce retour. Ca m'a bien aidé.