Created
February 18, 2022 14:09
-
-
Save fourgeotf/16137c738660701b2772119d3d92853e to your computer and use it in GitHub Desktop.
[SHOPPING - Situation de stock 2021] Création de la situation de stock au 1er janvier 2022 #sql #sage #shopping
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- A lancer en premier afin de créer la table CIMSHOPPING2021.dbo.SituationStock2021 | |
-- ATTENTION : La jointure avec Repository et Site se fait en LEFT pour la situation de stock sur la base SHOPPING | |
-- alors que l'on utilisera INNER pour les autres bases afin de contraindre le résultat. Sur SHOPPING | |
-- nous voulons récupérer l'ensemble des lignes du fichier F_ARTSTOCK alors que pour les autres bases | |
-- nous ne voulons que les lignes qui correspondent aux différents magasins. | |
USE SHOPPING | |
GO | |
WITH dl | |
AS | |
(SELECT | |
AR_Ref | |
,SUM(DL_Qte) AS [QteTotaleAchat] | |
,COUNT(AR_Ref) AS [NbAchat] | |
,MAX(DO_Date) AS MaxDate | |
,MAX(CASE | |
WHEN seqnum = 1 THEN DL_PrixUnitaire | |
END) AS [LastPrice] | |
FROM (SELECT | |
dl.* | |
,ROW_NUMBER() OVER (PARTITION BY AR_Ref ORDER BY DO_Date DESC) AS seqnum | |
FROM SHOPPING..F_DOCLIGNE dl | |
WHERE DO_Type = 17) dl | |
GROUP BY AR_Ref), | |
t0 AS | |
(SELECT ac.AR_Ref, ac.AC_PrixVen FROM F_ARTCLIENT ac WHERE ac.AC_Categorie = 1), | |
t1 AS | |
(SELECT ac.AR_Ref, ac.AC_PrixVen FROM F_ARTCLIENT ac WHERE ac.AC_Categorie = 2) | |
--INSERT INTO CIMSHOPPING2021.dbo.SituationStock2021 -- Pour insérer dans la table déjà créée | |
SELECT | |
DB_NAME() AS Societe | |
,rp.Name AS Magasin | |
,ar.AR_Ref | |
,ar.AR_Design | |
,fa.FA_CodeFamille | |
,fa.FA_Intitule | |
,af.CT_Num | |
,af.AF_RefFourniss | |
,af.AF_PrixAch | |
,ar.AR_PrixAch | |
,ar.AR_PUNet | |
,dl.LastPrice | |
,de.DE_No | |
,de.DE_Intitule | |
,rp.DE_No_Master AS DE_No_Dest | |
,de.DE_Intitule AS DE_Intitule_Dest | |
,st.AS_QteSto | |
,st.AS_MontSto | |
,IIF(ISNULL(t0.AC_PrixVen, 0) = 0, ar.AR_PrixVen, t0.AC_PrixVen) AS AC_PrixVen_T0 | |
,t1.AC_PrixVen AS AC_PrixVen_T1 | |
,ar.AR_Sommeil | |
,ISNULL(rp.RepositoryId, 0) AS RepositoryId | |
,IIF(DB_NAME() = 'SHOPPING', IIF(st.AS_QteSto = 0 AND st.AS_MontSto = 0, 0, 1), 0) AS Action_RAZ_Stock | |
,IIF(rp.RepositoryId IS NULL, 0, IIF(st.AS_QteSto > 0, 1, 0)) AS Action_Import_Stock | |
INTO CIMSHOPPING2021.dbo.SituationStock2021 -- Uniquement en création de la table | |
FROM SHOPPING..F_ARTICLE ar | |
INNER JOIN SHOPPING..F_FAMILLE fa | |
ON ar.FA_CodeFamille = fa.FA_CodeFamille | |
INNER JOIN F_ARTSTOCK st | |
ON ar.AR_Ref = st.AR_Ref | |
INNER JOIN F_DEPOT de | |
ON st.DE_No = de.DE_No | |
LEFT JOIN SHOPPING..F_ARTFOURNISS af | |
ON ar.AR_Ref = af.AR_Ref | |
AND af.AF_Principal = 1 | |
LEFT JOIN (SELECT -- INNER JOIN dans le cas des autres bases, LEFT dans le cas de SHOPPING | |
r.RepositoryId | |
,r.SiteID | |
,r.DE_No_Master | |
,r.DE_No_Site | |
,r.Name | |
FROM CIMSHOPPING2021..Repository r | |
INNER JOIN CIMSHOPPING2021..Site s | |
ON s.SiteID = r.SiteID | |
AND s.DatabaseName = DB_NAME()) AS rp | |
ON rp.DE_No_Site = de.DE_No | |
LEFT JOIN dl | |
ON dl.AR_Ref = ar.AR_Ref | |
LEFT JOIN t0 | |
ON ar.AR_Ref = t0.AR_Ref | |
LEFT JOIN t1 | |
ON ar.AR_Ref = t1.AR_Ref | |
ORDER BY ar.AR_Ref, de.DE_No |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- A lancer en deuxième afin de complèter la table CIMSHOPPING2021.dbo.SituationStock2021 | |
-- Pour chacune des bases suivantes : ASALB, CHTY, EPITHON et SABO | |
-- ATTENTION : La jointure avec Repository et Site se fait en LEFT pour la situation de stock sur la base SHOPPING | |
-- alors que l'on utilisera INNER pour les autres bases afin de contraindre le résultat. Sur SHOPPING | |
-- nous voulons récupérer l'ensemble des lignes du fichier F_ARTSTOCK alors que pour les autres bases | |
-- nous ne voulons que les lignes qui correspondent aux différents magasins. | |
USE ASALB -- CHTY EPITHON SABO | |
GO | |
WITH dl | |
AS | |
(SELECT | |
AR_Ref | |
,SUM(DL_Qte) AS [QteTotaleAchat] | |
,COUNT(AR_Ref) AS [NbAchat] | |
,MAX(DO_Date) AS MaxDate | |
,MAX(CASE | |
WHEN seqnum = 1 THEN DL_PrixUnitaire | |
END) AS [LastPrice] | |
FROM (SELECT | |
dl.* | |
,ROW_NUMBER() OVER (PARTITION BY AR_Ref ORDER BY DO_Date DESC) AS seqnum | |
FROM SHOPPING..F_DOCLIGNE dl | |
WHERE DO_Type = 17) dl | |
GROUP BY AR_Ref), | |
t0 AS | |
(SELECT ac.AR_Ref, ac.AC_PrixVen FROM F_ARTCLIENT ac WHERE ac.AC_Categorie = 1), | |
t1 AS | |
(SELECT ac.AR_Ref, ac.AC_PrixVen FROM F_ARTCLIENT ac WHERE ac.AC_Categorie = 2) | |
INSERT INTO CIMSHOPPING2021.dbo.SituationStock2021 -- Pour insérer dans la table déjà créée | |
SELECT | |
DB_NAME() AS Societe | |
,rp.Name AS Magasin | |
,ar.AR_Ref | |
,ar.AR_Design | |
,fa.FA_CodeFamille | |
,fa.FA_Intitule | |
,af.CT_Num | |
,af.AF_RefFourniss | |
,af.AF_PrixAch | |
,ar.AR_PrixAch | |
,ar.AR_PUNet | |
,dl.LastPrice | |
,de.DE_No | |
,de.DE_Intitule | |
,rp.DE_No_Master AS DE_No_Dest | |
,de.DE_Intitule AS DE_Intitule_Dest | |
,st.AS_QteSto | |
,st.AS_MontSto | |
,IIF(ISNULL(t0.AC_PrixVen, 0) = 0, ar.AR_PrixVen, t0.AC_PrixVen) AS AC_PrixVen_T0 | |
,t1.AC_PrixVen AS AC_PrixVen_T1 | |
,ar.AR_Sommeil | |
,ISNULL(rp.RepositoryId, 0) AS RepositoryId | |
,IIF(DB_NAME() = 'SHOPPING', IIF(st.AS_QteSto = 0 AND st.AS_MontSto = 0, 0, 1), 0) AS Action_RAZ_Stock | |
,IIF(rp.RepositoryId IS NULL, 0, IIF(st.AS_QteSto > 0, 1, 0)) AS Action_Import_Stock | |
--INTO CIMSHOPPING2021.dbo.SituationStock2021 -- Uniquement en création de la table | |
FROM SHOPPING..F_ARTICLE ar | |
INNER JOIN SHOPPING..F_FAMILLE fa | |
ON ar.FA_CodeFamille = fa.FA_CodeFamille | |
INNER JOIN F_ARTSTOCK st | |
ON ar.AR_Ref = st.AR_Ref | |
INNER JOIN F_DEPOT de | |
ON st.DE_No = de.DE_No | |
LEFT JOIN SHOPPING..F_ARTFOURNISS af | |
ON ar.AR_Ref = af.AR_Ref | |
AND af.AF_Principal = 1 | |
INNER JOIN (SELECT -- INNER JOIN dans le cas des autres bases, LEFT dans le cas de SHOPPING | |
r.RepositoryId | |
,r.SiteID | |
,r.DE_No_Master | |
,r.DE_No_Site | |
,r.Name | |
FROM CIMSHOPPING2021..Repository r | |
INNER JOIN CIMSHOPPING2021..Site s | |
ON s.SiteID = r.SiteID | |
AND s.DatabaseName = DB_NAME()) AS rp | |
ON rp.DE_No_Site = de.DE_No | |
LEFT JOIN dl | |
ON dl.AR_Ref = ar.AR_Ref | |
LEFT JOIN t0 | |
ON ar.AR_Ref = t0.AR_Ref | |
LEFT JOIN t1 | |
ON ar.AR_Ref = t1.AR_Ref | |
ORDER BY ar.AR_Ref, de.DE_No |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment