1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106
| USE [Suivi_Compteur]
GO
/****** Object: StoredProcedure [dbo].[spSGM_Stat_Mvtfab_PostChar] Script Date: 02/28/2014 15:14:06 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
/*
Procedure : spSGM_Stat_Mvtfab_PostChar
Createur : F.R. BROUSSAIS
Date : 23/09/2004
Objet : Compte le nombre de mouvements de fabrication entre 2 dates pour un poste de charge donné.
Compte par mf_trans (test et ReTest différenciés) et par statut
Possibilité de limiter les résultats à un produit et/ou à une transaction
Parametres en entree: @pr_num n° du poste de travail
@dateDebut date de début du test (incluse)
@dateFin date de fin du test (NON incluse)
@mf_trans Filtre sur la transaction associé au poste
@pt_num Filtre sur le code produit
@Re_Distinct Afficher les tests et les reTests. Additionne les mouvements Test + ReTest si @Re_Distinct <> 1
@Statut_Distinct Affiche les statuts des mouvements (GO, NOGO...). Additionne les mouvements GO + NOGO +... si @Statut_Distinct <> 1
Resultat en retour: mf_trans, mf_statut, compte de mouvements : NbMvt
Modification le 25/11/2004 par F.R. BROUSSAIS : Ajout des paramètres @Re_Distinct et @Statut_Distinct et modifications du code qui en découlent...
Modification le 21/01/2005 par F.R. BROUSSAIS : Correction du code de la requête générée quand pr_num NOT NULL
*/
CREATE PROCEDURE [dbo].[spSGM_Stat_Mvtfab_PostChar]
@pr_num int = NULL,
@dateDebut datetime,
@dateFin datetime,
@mf_trans varchar(25) = NULL,
@pt_num varchar(18) = NULL,
@Re_Distinct tinyInt = 1,
@Statut_Distinct tinyInt = 1
AS
DECLARE @codeSQL nvarchar(4000)
DECLARE @Param nvarchar(500)
CREATE TABLE #Stat (mf_trans varchar(25), mf_statut varchar(5), NbMvt integer, Re tinyint)
SET @Param=' @dDateDebut datetime, @dDateFin datetime '
SET @codeSQL= 'INSERT INTO #Stat (mf_trans, mf_statut, NbMvt, Re) '
IF @Re_Distinct = 1
BEGIN
SET @codeSQL = @codeSQL + ' SELECT DISTINCT mf_trans, mf_statut, COUNT(*) as NbMvt, NULL '
+ ' FROM mvtfabr WHERE mf_dmvtdeb >= @dDateDebut AND mf_dmvtDeb < @dDateFin '
IF @pr_num IS NOT NULL SET @codeSQL = @codeSQL + ' AND pr_num = '+ cast(@pr_num AS varchar(20))
IF @pt_num IS NOT NULL SET @codeSQL = @codeSQL + ' AND pt_num = ''' + @pt_num + ''''
IF @mf_trans IS NOT NULL SET @codeSQL = @codeSQL +' AND (mf_trans = ''' + @mf_trans + ''' OR mf_trans = ''Re_' + @mf_trans + ''')'
SET @codeSQL = @codeSQL + ' GROUP BY mf_trans, mf_statut ORDER BY mf_trans, mf_statut'
END
ELSE
BEGIN
SET @codeSQL = @codeSQL + ' SELECT DISTINCT mf_trans,mf_statut,COUNT(*) as NbMvt, 0 '
+ ' FROM mvtfabr WHERE mf_dmvtdeb>=@dDateDebut AND mf_dmvtDeb<@dDateFin '
IF @pr_num IS NOT NULL SET @codeSQL = @codeSQL + ' AND pr_num=' + cast(@pr_num AS varchar(20))
IF @pt_num IS NOT NULL SET @codeSQL = @codeSQL + ' AND pt_num=''' + @pt_num + ''''
IF @mf_trans IS NOT NULL SET @codeSQL = @codeSQL +' AND mf_trans=''' + @mf_trans + ''''
SET @codeSQL = @codeSQL + ' AND LEFT(mf_trans, 3)<>''re_'''
+ ' GROUP BY mf_trans,mf_statut ' -- ORDER BY mf_trans, mf_statut '
SET @codeSQL = @codeSQL + ' UNION '
+ ' SELECT DISTINCT RIGHT(mf_trans,LEN(mf_trans)-3),mf_statut,COUNT(*) as NbMvt, 1 '
+ ' FROM mvtfabr WHERE mf_dmvtdeb>=@dDateDebut AND mf_dmvtDeb<@dDateFin '
IF @pr_num IS NOT NULL SET @codeSQL = @codeSQL + ' AND pr_num=' + cast(@pr_num AS varchar(20))
IF @pt_num IS NOT NULL SET @codeSQL = @codeSQL + ' AND pt_num=''' + @pt_num + ''''
IF @mf_trans IS NOT NULL SET @codeSQL = @codeSQL +' AND mf_trans=''Re_' + @mf_trans + ''''
SET @codeSQL = @codeSQL + ' AND LEFT(mf_trans, 3)=''re_'''
+ ' GROUP BY mf_trans,mf_statut '
END
PRINT @codeSQL
EXEC sp_executesql @codeSQL,@Param, @dDateDebut = @datedebut, @dDateFin = @Datefin
-- select * from #stat
IF @Statut_Distinct = 1
BEGIN
SELECT distinct mf_trans, mf_statut, SUM(NbMvt) AS NbMvt FROM #Stat
GROUP BY mf_trans, mf_statut
ORDER BY mf_trans, mf_statut
END
ELSE
BEGIN
SELECT distinct mf_trans, '' as mf_statut, SUM(NbMvt) AS NbMvt FROM #Stat
GROUP BY mf_trans
ORDER BY mf_trans
END
GO[ATTACH=CONFIG]139264[/ATTACH] |
Partager