set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go-- =============================================
-- Author:
-- Create date: <2009-11-09,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[s_GetReportDate_Data]
@Date datetime
--,@Custer nvarchar(10),
-- @Province nvarchar(10),@Good NVARCHAR(20)
--参数:日期 @Date; 经销商 @Custer 地区@Province 商品@Good
AS
BEGIN TRY
BEGIN TRANSACTIONDeclare @Date01 nvarchar(10)
Declare @Date02 nvarchar(10)
Declare @AreaId nchar(50)
Declare @UniqueID nchar(36)
Declare @ItemID nchar(25)
Declare @CreateDate datetime
Declare @Min INT
Declare @Sin INT
Declare @Mout INT
Declare @Sout INT
DECLARE @sql NVARCHAR(2000)
--设置隔离等级
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
--BEGIN
/* select * FROM DayReportMaster where status=1 //D.Tin As BRTH,D.Tout As BRXS,D.Tinv As BRKC
select D.ItemID,'TH' AS TYPE,sum(Month('2009-8-31 17:16:28')) from DayReportMaster M
left join DayReportSupDetail D on M.UniqueID=D.RefKey
where M.CreateDate=('2009-8-31 17:16:28') group by D.ItemID------
SELECT s.AreaName,C.[UniCode],C.CompNick,D.ItemID,i.ItemName,d.TIn,d.TOut,d.TInv,
M.CreateDate
FROM dbo.DayReportMaster M
INNER JOIN dbo.DayReportSupDetail D ON
M.uniqueid=d.refkey INNER JOIN dbo.Company C ON M.[UniCode]=C.[UniCode] INNER JOIN
dbo.SaleArea S ON C.Area=S.AreaId INNER JOIN dbo.Item I ON D.ItemID=I.ItemID
;SELECT * FROM DayReport_ReportDW
*/
---------------
DELETE FROM dbo.DayReport_ReportDW;
-------------------------先插入展示数据----------------------
INSERT INTO dbo.DayReport_ReportDW (
AreaId,
AreaName,
UniqueID,
CompName,
ItemID,
ItemName,
Tin,
Tout,
Tinv,
CreateDate
)
SELECT C.Area,s.AreaName,M.[UniCode],C.CompNick,D.ItemID,I.ItemName,D.TIn,D.TOut,D.TInv,
M.CreateDate
FROM dbo.DayReportMaster M
INNER JOIN dbo.DayReportSupDetail D ON M.uniqueid=D.refkey
LEFT JOIN dbo.Company C ON M.[UniCode]=C.[UniCode]
LEFT JOIN dbo.SaleArea S ON C.Area=s.AreaId
LEFT JOIN dbo.Item I ON d.ItemID=i.ItemID
--WHERE CONVERT(VARCHAR(10),M.CreateDate,120)=@Date --('2009-08-30 17:08:34')--GETDATE()
ORDER BY C.Area Asc
----------------------更新月数据和累计数据------------------
DECLARE cur CURSOR FOR
SELECT AreaId,UniqueID,ItemID,CreateDate
FROM DayReport_ReportDW
OPEN cur
FETCH NEXT FROM cur INTO @AreaId,@UniqueID,@ItemID,@CreateDateWHILE @@FETCH_STATUS=0
BEGIN
SELECT @Min=
(SELECT SUM(Tin)
FROM dbo.DayReportMaster M
INNER JOIN dbo.DayReportSupDetail D ON M.uniqueid=D.refkey
LEFT JOIN dbo.Company C ON M.[UniCode]=C.[UniCode]
LEFT JOIN dbo.SaleArea S ON C.Area=s.AreaId
LEFT JOIN dbo.Item I ON d.ItemID=i.ItemID
WHERE DATEDIFF(mm,CONVERT(VARCHAR(10),M.CreateDate,120),@Date)=0
AND Area=@AreaId AND M.UniqueID=@UniqueID AND D.ItemID=@ItemID) ELSE SUM(Tin) END);
SELECT @Sin=SUM(Tin)
FROM
(SELECT Area,M.UniqueID,D.ItemID,d.Tin AS Tin FROM dbo.DayReportMaster M
INNER JOIN dbo.DayReportSupDetail D ON M.uniqueid=D.refkey
LEFT JOIN dbo.Company C ON M.[UniCode]=C.[UniCode]
LEFT JOIN dbo.SaleArea S ON C.Area=s.AreaId
LEFT JOIN dbo.Item I ON d.ItemID=i.ItemID
) AS A
WHERE Area=@AreaId AND UniqueID=@UniqueID AND ItemID=@ItemID
UPDATE dbo.DayReport_ReportDW
SET [Min]=@Min,[Sin]=@Sin
WHERE AreaId=@AreaId AND UniqueID=@UniqueID AND ItemID=@ItemID
FETCH NEXT FROM cur INTO @AreaId,@UniqueID,@ItemID,@CreateDate
END
CLOSE cur
DEALLOCATE cur
IF @@ERROR<>0
ROLLBACK
COMMIT
END TRY
BEGIN CATCH
CLOSE cur
DEALLOCATE cur
IF @@TRANCOUNT > 0
ROLLBACK DECLARE @ErrMsg NVARCHAR(4000)
SELECT @ErrMsg=ERROR_MESSAGE()
END CATCH--END
--EXEC s_GetReportDate_Data '2009-08-30'
--SELECT * FROM DayReport_ReportDW
/*(SELECT C.Area,s.AreaName,M.[UniCode],C.CompNick,D.ItemID,I.ItemName,D.TIn,D.TOut,D.TInv,
M.CreateDate
FROM
*/大家帮我改一下,就是这一段
BEGIN
SELECT @Min=
(SELECT SUM(Tin)
FROM dbo.DayReportMaster M
INNER JOIN dbo.DayReportSupDetail D ON M.uniqueid=D.refkey
LEFT JOIN dbo.Company C ON M.[UniCode]=C.[UniCode]
LEFT JOIN dbo.SaleArea S ON C.Area=s.AreaId
LEFT JOIN dbo.Item I ON d.ItemID=i.ItemID
WHERE DATEDIFF(mm,CONVERT(VARCHAR(10),M.CreateDate,120),@Date)=0
AND Area=@AreaId AND M.UniqueID=@UniqueID AND D.ItemID=@ItemID) ELSE SUM(Tin) END);
SELECT @Sin=SUM(Tin)
FROM
(SELECT Area,M.UniqueID,D.ItemID,d.Tin AS Tin FROM dbo.DayReportMaster M
INNER JOIN dbo.DayReportSupDetail D ON M.uniqueid=D.refkey
LEFT JOIN dbo.Company C ON M.[UniCode]=C.[UniCode]
LEFT JOIN dbo.SaleArea S ON C.Area=s.AreaId
LEFT JOIN dbo.Item I ON d.ItemID=i.ItemID
) AS A
WHERE Area=@AreaId AND UniqueID=@UniqueID AND ItemID=@ItemID
UPDATE dbo.DayReport_ReportDW
SET [Min]=@Min,[Sin]=@Sin
WHERE AreaId=@AreaId AND UniqueID=@UniqueID AND ItemID=@ItemID
FETCH NEXT FROM cur INTO @AreaId,@UniqueID,@ItemID,@CreateDate
END我想实现对[Min]按月统计后对该字段进行更新(主要是统计和给变量赋值有问题)
我这个游标写的有些问题
帮忙改改
多谢了
set QUOTED_IDENTIFIER ON
go-- =============================================
-- Author:
-- Create date: <2009-11-09,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[s_GetReportDate_Data]
@Date datetime
--,@Custer nvarchar(10),
-- @Province nvarchar(10),@Good NVARCHAR(20)
--参数:日期 @Date; 经销商 @Custer 地区@Province 商品@Good
AS
BEGIN TRY
BEGIN TRANSACTIONDeclare @Date01 nvarchar(10)
Declare @Date02 nvarchar(10)
Declare @AreaId nchar(50)
Declare @UniqueID nchar(36)
Declare @ItemID nchar(25)
Declare @CreateDate datetime
Declare @Min INT
Declare @Sin INT
Declare @Mout INT
Declare @Sout INT
DECLARE @sql NVARCHAR(2000)
--设置隔离等级
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
--BEGIN
/* select * FROM DayReportMaster where status=1 //D.Tin As BRTH,D.Tout As BRXS,D.Tinv As BRKC
select D.ItemID,'TH' AS TYPE,sum(Month('2009-8-31 17:16:28')) from DayReportMaster M
left join DayReportSupDetail D on M.UniqueID=D.RefKey
where M.CreateDate=('2009-8-31 17:16:28') group by D.ItemID------
SELECT s.AreaName,C.[UniCode],C.CompNick,D.ItemID,i.ItemName,d.TIn,d.TOut,d.TInv,
M.CreateDate
FROM dbo.DayReportMaster M
INNER JOIN dbo.DayReportSupDetail D ON
M.uniqueid=d.refkey INNER JOIN dbo.Company C ON M.[UniCode]=C.[UniCode] INNER JOIN
dbo.SaleArea S ON C.Area=S.AreaId INNER JOIN dbo.Item I ON D.ItemID=I.ItemID
;SELECT * FROM DayReport_ReportDW
*/
---------------
DELETE FROM dbo.DayReport_ReportDW;
-------------------------先插入展示数据----------------------
INSERT INTO dbo.DayReport_ReportDW (
AreaId,
AreaName,
UniqueID,
CompName,
ItemID,
ItemName,
Tin,
Tout,
Tinv,
CreateDate
)
SELECT C.Area,s.AreaName,M.[UniCode],C.CompNick,D.ItemID,I.ItemName,D.TIn,D.TOut,D.TInv,
M.CreateDate
FROM dbo.DayReportMaster M
INNER JOIN dbo.DayReportSupDetail D ON M.uniqueid=D.refkey
LEFT JOIN dbo.Company C ON M.[UniCode]=C.[UniCode]
LEFT JOIN dbo.SaleArea S ON C.Area=s.AreaId
LEFT JOIN dbo.Item I ON d.ItemID=i.ItemID
--WHERE CONVERT(VARCHAR(10),M.CreateDate,120)=@Date --('2009-08-30 17:08:34')--GETDATE()
ORDER BY C.Area Asc
----------------------更新月数据和累计数据------------------
DECLARE cur CURSOR FOR
SELECT AreaId,UniqueID,ItemID,CreateDate
FROM DayReport_ReportDW
OPEN cur
FETCH NEXT FROM cur INTO @AreaId,@UniqueID,@ItemID,@CreateDateWHILE @@FETCH_STATUS=0
BEGIN
SELECT @Min=
(SELECT SUM(Tin)
FROM dbo.DayReportMaster M
INNER JOIN dbo.DayReportSupDetail D ON M.uniqueid=D.refkey
LEFT JOIN dbo.Company C ON M.[UniCode]=C.[UniCode]
LEFT JOIN dbo.SaleArea S ON C.Area=s.AreaId
LEFT JOIN dbo.Item I ON d.ItemID=i.ItemID
WHERE DATEDIFF(mm,CONVERT(VARCHAR(10),M.CreateDate,120),@Date)=0
AND Area=@AreaId AND M.UniqueID=@UniqueID AND D.ItemID=@ItemID) ELSE SUM(Tin) END);
SELECT @Sin=SUM(Tin)
FROM
(SELECT Area,M.UniqueID,D.ItemID,d.Tin AS Tin FROM dbo.DayReportMaster M
INNER JOIN dbo.DayReportSupDetail D ON M.uniqueid=D.refkey
LEFT JOIN dbo.Company C ON M.[UniCode]=C.[UniCode]
LEFT JOIN dbo.SaleArea S ON C.Area=s.AreaId
LEFT JOIN dbo.Item I ON d.ItemID=i.ItemID
) AS A
WHERE Area=@AreaId AND UniqueID=@UniqueID AND ItemID=@ItemID
UPDATE dbo.DayReport_ReportDW
SET [Min]=@Min,[Sin]=@Sin
WHERE AreaId=@AreaId AND UniqueID=@UniqueID AND ItemID=@ItemID
FETCH NEXT FROM cur INTO @AreaId,@UniqueID,@ItemID,@CreateDate
END
CLOSE cur
DEALLOCATE cur
IF @@ERROR<>0
ROLLBACK
COMMIT
END TRY
BEGIN CATCH
CLOSE cur
DEALLOCATE cur
IF @@TRANCOUNT > 0
ROLLBACK DECLARE @ErrMsg NVARCHAR(4000)
SELECT @ErrMsg=ERROR_MESSAGE()
END CATCH--END
--EXEC s_GetReportDate_Data '2009-08-30'
--SELECT * FROM DayReport_ReportDW
/*(SELECT C.Area,s.AreaName,M.[UniCode],C.CompNick,D.ItemID,I.ItemName,D.TIn,D.TOut,D.TInv,
M.CreateDate
FROM
*/大家帮我改一下,就是这一段
BEGIN
SELECT @Min=
(SELECT SUM(Tin)
FROM dbo.DayReportMaster M
INNER JOIN dbo.DayReportSupDetail D ON M.uniqueid=D.refkey
LEFT JOIN dbo.Company C ON M.[UniCode]=C.[UniCode]
LEFT JOIN dbo.SaleArea S ON C.Area=s.AreaId
LEFT JOIN dbo.Item I ON d.ItemID=i.ItemID
WHERE DATEDIFF(mm,CONVERT(VARCHAR(10),M.CreateDate,120),@Date)=0
AND Area=@AreaId AND M.UniqueID=@UniqueID AND D.ItemID=@ItemID) ELSE SUM(Tin) END);
SELECT @Sin=SUM(Tin)
FROM
(SELECT Area,M.UniqueID,D.ItemID,d.Tin AS Tin FROM dbo.DayReportMaster M
INNER JOIN dbo.DayReportSupDetail D ON M.uniqueid=D.refkey
LEFT JOIN dbo.Company C ON M.[UniCode]=C.[UniCode]
LEFT JOIN dbo.SaleArea S ON C.Area=s.AreaId
LEFT JOIN dbo.Item I ON d.ItemID=i.ItemID
) AS A
WHERE Area=@AreaId AND UniqueID=@UniqueID AND ItemID=@ItemID
UPDATE dbo.DayReport_ReportDW
SET [Min]=@Min,[Sin]=@Sin
WHERE AreaId=@AreaId AND UniqueID=@UniqueID AND ItemID=@ItemID
FETCH NEXT FROM cur INTO @AreaId,@UniqueID,@ItemID,@CreateDate
END我想实现对[Min]按月统计后对该字段进行更新(主要是统计和给变量赋值有问题)
我这个游标写的有些问题
帮忙改改
多谢了
你想sum什么?
--这里不应该关联主表(DayReportMaster),应该关联从表(明细表) DayReportSupDetail
SELECT @Min=
(SELECT SUM(Tin)
FROM dbo.DayReportMaster M
INNER JOIN dbo.DayReportSupDetail D ON M.uniqueid=D.refkey
LEFT JOIN dbo.Company C ON M.[UniCode]=C.[UniCode]
LEFT JOIN dbo.SaleArea S ON C.Area=s.AreaId
LEFT JOIN dbo.Item I ON d.ItemID=i.ItemID
WHERE DATEDIFF(mm,CONVERT(VARCHAR(10),M.CreateDate,120),@Date)=0
AND Area=@AreaId AND M.UniqueID=@UniqueID AND D.ItemID=@ItemID) ELSE SUM(Tin) END);
SELECT @Sin=SUM(Tin)
FROM
(SELECT Area,M.UniqueID,D.ItemID,d.Tin AS Tin FROM dbo.DayReportMaster M
INNER JOIN dbo.DayReportSupDetail D ON M.uniqueid=D.refkey
LEFT JOIN dbo.Company C ON M.[UniCode]=C.[UniCode]
LEFT JOIN dbo.SaleArea S ON C.Area=s.AreaId
LEFT JOIN dbo.Item I ON d.ItemID=i.ItemID
) AS A
WHERE Area=@AreaId AND UniqueID=@UniqueID AND ItemID=@ItemID
UPDATE dbo.DayReport_ReportDW
SET [Min]=@Min,[Sin]=@Sin
WHERE AreaId=@AreaId AND UniqueID=@UniqueID AND ItemID=@ItemID
FETCH NEXT FROM cur INTO @AreaId,@UniqueID,@ItemID,@CreateDate
END
那个注释掉的就不看了
看这段吧
有点问题
LEFT JOIN dbo.Company C ON D.refkey=C.[UniCode] --这里不应该关联主表(DayReportMaster),应该关联从表(明细表) DayReportSupDetail
FROM
(SELECT Area,M.UniqueID,D.ItemID,d.Tin AS Tin FROM dbo.DayReportMaster M
INNER JOIN dbo.DayReportSupDetail D ON M.uniqueid=D.refkey
LEFT JOIN dbo.Company C ON M.[UniCode]=C.[UniCode]
LEFT JOIN dbo.SaleArea S ON C.Area=s.AreaId
LEFT JOIN dbo.Item I ON d.ItemID=i.ItemID
) AS A
WHERE Area=@AreaId AND UniqueID=@UniqueID AND ItemID=@ItemID
我想是不是这块有问题
就是不能把统计值赋给变量
是的
这块有问题
看下面这段
实际上这两句sql是一个意思
就是想求出满足条件的记录的和
然后一条条赋值过去
进行更新
SELECT @Min=
(SELECT SUM(Tin)
FROM dbo.DayReportMaster M
INNER JOIN dbo.DayReportSupDetail D ON M.uniqueid=D.refkey
LEFT JOIN dbo.Company C ON M.[UniCode]=C.[UniCode]
LEFT JOIN dbo.SaleArea S ON C.Area=s.AreaId
LEFT JOIN dbo.Item I ON d.ItemID=i.ItemID
WHERE DATEDIFF(mm,CONVERT(VARCHAR(10),M.CreateDate,120),@Date)=0
AND Area=@AreaId AND M.UniqueID=@UniqueID AND D.ItemID=@ItemID) AS B ;
SELECT @Sin=SUM(Tin)
FROM
(SELECT Area,M.UniqueID,D.ItemID,d.Tin AS Tin FROM dbo.DayReportMaster M
INNER JOIN dbo.DayReportSupDetail D ON M.uniqueid=D.refkey
LEFT JOIN dbo.Company C ON M.[UniCode]=C.[UniCode]
LEFT JOIN dbo.SaleArea S ON C.Area=s.AreaId
LEFT JOIN dbo.Item I ON d.ItemID=i.ItemID
) AS A
WHERE Area=@AreaId AND UniqueID=@UniqueID AND ItemID=@ItemID
UPDATE dbo.DayReport_ReportDW
SET [Min]=@Min,[Sin]=@Sin
WHERE AreaId=@AreaId AND UniqueID=@UniqueID AND ItemID=@ItemID
FETCH NEXT FROM cur INTO @AreaId,@UniqueID,@ItemID,@CreateDate
END
因为刚才改过了
实际上是这样的