k3报表SQL

要 点：

1，不可以对物理表进行修改、删除操作 2，不可以对数据库进行修改、删除操作 3，数据的暂时存储使用临时表 4，必须有一个要返回的查询语句 5，关键字只能做为查询条件

下述示例只做为参考使用 示例一：本期未记账单据 declare @StartTime datetime, @EndTime datetime,

@CurYear smallint,

@CurPeriod smallint

SELECT @CurPeriod =CONVERT(smallint,FValue) FROM t_SystemProFile WHERE FCategory='IC' AND FKey='CurrentPeriod'

SELECT @CurYear =CONVERT(smallint,FValue) FROM t_SystemProFile WHERE FCategory='IC' AND FKey='CurrentYear'

EXEC GetPeriodStartEnd @CurYear,@CurPeriod ,@StartTime output,@EndTime output

SELECT t1.FInterID, t1.FDate AS '日期',

第 1 页 共 7 页

t1.FBillNO as '单据号码', t3.FShortNumber as '物料编码', t3.FName as '物料名称', t4.FName as '单位', t2.FQty as '数量', t2.FPrice as '单价', t2.FAmount as '金额'

FROM ICStockBill t1,ICStockBillEntry t2,t_ICItem t3,t_MeasureUnit t4 WHERE t1.FInterID=t2.FInterID AND t2.FItemID=t3.FItemID

AND t3.FUnitGroupID=t4.FUnitGroupID AND t4.FStandard=1 AND t1.FPosted=0

AND t1.FDate>=@StartTime AND t1.FDate<=@EndTime

示例二：本期无单价入库单据 declare @StartTime datetime, @EndTime datetime,

@CurYear smallint,

@CurPeriod smallint

SELECT @CurPeriod =CONVERT(smallint,FValue) FROM t_SystemProFile

第 2 页 共 7 页

WHERE FCategory='IC' AND FKey='CurrentPeriod'

SELECT @CurYear =CONVERT(smallint,FValue) FROM t_SystemProFile WHERE FCategory='IC' AND FKey='CurrentYear'

EXEC GetPeriodStartEnd @CurYear,@CurPeriod ,@StartTime output,@EndTime output

SELECT t1.FInterID, t1.FDate AS '日期', t1.FBillNO as '单据号码', t3.FShortNumber as '物料编码', t3.FName as '物料名称', t4.FName as '单位', t2.FQty as '数量', t2.FPrice as '单价', t2.FAmount as '金额'

FROM ICStockBill t1,ICStockBillEntry t2,t_ICItem t3,t_MeasureUnit t4 WHERE t1.FInterID=t2.FInterID AND t2.FItemID=t3.FItemID

AND t3.FUnitGroupID=t4.FUnitGroupID AND t4.FStandard=1 AND t2.FPrice=0

AND t1.FTranType in (1,2,5,10,40) AND t1.FDate>=@StartTime

第 3 页 共 7 页

AND t1.FDate<=@EndTime

示例三：未生成凭证单据

SELECT t1.FInterID, t1.FDate AS '日期', t1.FBillNO as '单据号码', t3.FShortNumber as '物料编码', t3.FName as '物料名称', t4.FName as '单位', t2.FQty as '数量', t2.FPrice as '单价', t2.FAmount as '金额'

FROM ICStockBill t1,ICStockBillEntry t2,t_ICItem t3,t_MeasureUnit t4 WHERE t1.FInterID=t2.FInterID AND t2.FItemID=t3.FItemID

AND t3.FUnitGroupID=t4.FUnitGroupID AND t4.FStandard=1 AND t1.FVchInterID IS Null AND t1.FTranType<>41 AND t3.FNumber>='*ItemNo*' AND t3.FNumber<='#ItemNo#'

第 4 页 共 7 页

示例四：收发汇总表

SELECT t3.FShortNumber AS '物料代码', t3.FName AS '物料名称', t3.FModel AS '规格型号', t4.FName AS '单位',

SUM(CASE WHEN t1.FTranType in (1,2,5,10,40) THEN t2.FQty ELSE 0 END) AS '收入数量',

SUM(CASE WHEN t1.FTranType in (1,2,5,10,40) THEN t2.FAmount ELSE 0 END) AS '收入金额',

SUM(CASE WHEN t1.FTranType in (24,28,29,21,43) THEN t2.FQty ELSE 0 END) AS '发出数量',

SUM(CASE WHEN t1.FTranType in (24,28,29,21,43) THEN t2.FQty ELSE 0 END) AS '发出金额',

t3.FQtyDecimal AS '数量精度', t3.FPriceDecimal AS '单价精度'

FROM ICStockBill t1,ICStockBillEntry t2,t_ICItem t3,t_MeasureUnit t4 WHERE t1.FInterID=t2.FInterID AND t2.FItemID=t3.FItemID AND t4.FItemID=t3.FUnitID AND t1.FDate>='********' and t1.FDate<='########' AND t4.FStandard=1

第 5 页 共 7 页

本文来源：https://www.bwwdw.com/article/1o4d.html