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
| USE [Clark]
GO
/****** Object: StoredProcedure [dbo].[sp_DoTotal] Script Date: 03/06/2011 00:12:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_DoTotal]
@ChauffeurID int,
@TotalType int
AS
set @TotalType = (SELECT
CASE @TotalType
WHEN 0 THEN 0
WHEN 9 THEN 9
ELSE 0
END)
IF @ChauffeurID IS NULL
BEGIN
INSERT INTO
[Clark].[dbo].TotalChauffeur
(
ChauffeurID,
ChauffeurName,
Date,
Gewicht
)
SELECT
[ChauffeurID]
,[ChauffeurName]
,MAX(Date) as Date
,SUM([bruto])-sum([tarra]) as Gewicht
FROM [Clark].[dbo].[BakMove]
where ChauffeurID in (select ChauffeurID From [BakMove] where TotalStatus=@TotalType)
group by
[ChauffeurID]
,[ChauffeurName]
,CAST(Date as Date)
END
ELSE
BEGIN
INSERT INTO
[Clark].[dbo].TotalChauffeur
(
ChauffeurID,
ChauffeurName,
Date,
Gewicht
)
SELECT
[ChauffeurID]
,[ChauffeurName]
,MAX(Date) as Date
,SUM([bruto])-sum([tarra]) as Gewicht
FROM [BakMove]
where ChauffeurID in
(select
ChauffeurID
From [BakMove]
where TotalStatus=@TotalType
AND ChauffeurID=@ChauffeurID)
group by
[ChauffeurID]
,[ChauffeurName]
,CAST(Date as Date)
END
return |
Partager