只显示on hand quantity and wharehouse 而且quantity 大于零。
SELECT
i.
[
item
]
,i.
[
description
]
,iw.
[
qty_on_hand
]
,iw.
[
whse
]
FROM [ dbo ] . [ item ] i
INNER JOIN [ dbo ] . [ itemwhse ] iw ON (i. [ item ] = iw. [ item ] )
WHERE iw. [ qty_on_hand ] > 0
FROM [ dbo ] . [ item ] i
INNER JOIN [ dbo ] . [ itemwhse ] iw ON (i. [ item ] = iw. [ item ] )
WHERE iw. [ qty_on_hand ] > 0
下面这段code是for Finance使用,显示数量,unit cost and amount:
代码
SELECT
i.
[
item
]
,i.
[
description
]
,
SUM
(iw.qty_on_hand)
AS
[
OnhangQty
]
,
CASE WHEN i. [ cost_type ] = ' A ' THEN ' Actul ' ELSE ' Standard ' END AS [ CostType ] ,
CASE WHEN i. [ cost_method ] = ' A ' THEN ' Average ' ELSE ' Standard ' END AS [ CostMethod ] ,i. [ unit_cost ] , SUM (iw. [ qty_on_hand ] * i. [ unit_cost ] ) AS [ Amount ]
FROM [ dbo ] . [ item ] i
INNER JOIN [ dbo ] . [ itemwhse ] iw ON (i. [ item ] = iw. [ item ] )
WHERE iw. [ qty_on_hand ] > 0 GROUP BY i. [ item ] ,i. [ description ] ,i. [ cost_type ] ,i. [ cost_method ] ,i. [ unit_cost ]
CASE WHEN i. [ cost_type ] = ' A ' THEN ' Actul ' ELSE ' Standard ' END AS [ CostType ] ,
CASE WHEN i. [ cost_method ] = ' A ' THEN ' Average ' ELSE ' Standard ' END AS [ CostMethod ] ,i. [ unit_cost ] , SUM (iw. [ qty_on_hand ] * i. [ unit_cost ] ) AS [ Amount ]
FROM [ dbo ] . [ item ] i
INNER JOIN [ dbo ] . [ itemwhse ] iw ON (i. [ item ] = iw. [ item ] )
WHERE iw. [ qty_on_hand ] > 0 GROUP BY i. [ item ] ,i. [ description ] ,i. [ cost_type ] ,i. [ cost_method ] ,i. [ unit_cost ]
下面这段SQL是Syteline system中定制过的Inventory Balance 报表脚本。定制只显示需求字段以及是否显示item cost。只作备忘保存于此。
代码
CREATE
PROCEDURE
[
dbo
]
.
[
usp_SyteLine_GetInventoryReport
]
(
@ConditionExpression nvarchar ( 3000 ),
@IsShowCost bit
)
AS
DECLARE @sql nvarchar ( 4000 )
IF @IsShowCost = 1
SET @sql = ' SELECT i.[item] AS [Item],i.[description] AS [Description],iw.[whse] AS [Warehouse],iw.[qty_on_hand] AS [Quantity],i.[unit_cost] AS [Cost],i.[u_m] AS [U/M]
FROM [dbo].[item] i
LEFT JOIN [dbo].[itemwhse] iw
ON (i.item = iw.item) '
ELSE
SET @sql = ' SELECT i.[item] AS [Item],i.[description] AS [Description],iw.[whse] AS [Warehouse],iw.[qty_on_hand] AS [Quantity],i.[u_m] AS [U/M]
FROM [dbo].[item] i
LEFT JOIN [dbo].[itemwhse] iw
ON (i.item = iw.item) '
IF LEN ( @ConditionExpression ) <= 0
SELECT @sql += ' ORDER BY i.[item],iw.[whse] '
ELSE
SELECT @sql += ' WHERE ' + @ConditionExpression + ' ORDER BY i.[item],iw.[whse] '
EXECUTE ( @sql )
GO
(
@ConditionExpression nvarchar ( 3000 ),
@IsShowCost bit
)
AS
DECLARE @sql nvarchar ( 4000 )
IF @IsShowCost = 1
SET @sql = ' SELECT i.[item] AS [Item],i.[description] AS [Description],iw.[whse] AS [Warehouse],iw.[qty_on_hand] AS [Quantity],i.[unit_cost] AS [Cost],i.[u_m] AS [U/M]
FROM [dbo].[item] i
LEFT JOIN [dbo].[itemwhse] iw
ON (i.item = iw.item) '
ELSE
SET @sql = ' SELECT i.[item] AS [Item],i.[description] AS [Description],iw.[whse] AS [Warehouse],iw.[qty_on_hand] AS [Quantity],i.[u_m] AS [U/M]
FROM [dbo].[item] i
LEFT JOIN [dbo].[itemwhse] iw
ON (i.item = iw.item) '
IF LEN ( @ConditionExpression ) <= 0
SELECT @sql += ' ORDER BY i.[item],iw.[whse] '
ELSE
SELECT @sql += ' WHERE ' + @ConditionExpression + ' ORDER BY i.[item],iw.[whse] '
EXECUTE ( @sql )
GO