需要帮助SQL
问题描述:
我的表看起来像这样:需要帮助SQL
ITEMCODE CP_ID Wloc Color Status STOCK_DATE
---------------------------------------------------------
1 259 1 148 71 I 10/30/2014
2 259 1 148 71 I 10/30/2014
3 259 1 148 71 B 10/30/2014
4 259 1 148 71 I 10/30/2014
5 259 1 148 71 D 10/30/2014
6 259 1 148 71 D 10/29/2014
7 259 1 148 71 B 10/29/2014
8 259 1 148 71 I 10/29/2014
9 259 1 148 71 I 10/29/2014
状态代码
I Inward
B Blocked
D Dispatched
我正在寻找一个股票的报告,像
ITEM_CODE Opening_Stock Closing_Stock Outward_STOCK Blocked_Stock Inward_Stock Stock_Date
----------------------------------------------------------------------------------------------------------------------
259 0 1 1 1 2 10/29/2014
259 1 3 1 1 3 10/30/2014
阻止可用尚未派发。
尝试一些东西一样得到了当前日期查找所有日期..
Select Distinct
ITEMCODE as ITEM_CODE,
'0' as Opening_Stock,
(
Select count(status)
From sysdba.stock
Where status = 'I'
And stock_date = date
Group By Itemcode
) as INWARD_STOCK,
(
Select count(status)
From sysdba.stock
Where status = 'D'
And stock_date = date
Group By Itemcode
) as OUTWARD_STOCK,
(
Select count(status)
From sysdba.stock
Where status = 'B'
And stock_date = date
Group By Itemcode
) as BLOCKED_STOCK,
(Opening_stock + INWARD_STOCK - OUTWARD_STOCK) as Closing_Stock,
Stock_date
From sysdba.stock
Where stock_date = date
Group By ITEMCODE, STOCK_DATE
如何着手建立这个报告?
所有帮助表示赞赏!
答
这不是一个完整的答案,但也许别人会运行这个。
SELECT
ITEMCODE as ITEM_CODE,
0 AS Opening_Stock,
SUM(CASE status
WHEN 'D' THEN -1
WHEN 'I' THEN 1
ELSE 0 END) AS Closing_Stock,
SUM(CASE [status] WHEN 'D' THEN 1 ELSE 0 END) AS OUTWARD_STOCK,
SUM(CASE [status] WHEN 'B' THEN 1 ELSE 0 END) AS BLOCKED_STOCK,
SUM(CASE [status] WHEN 'I' THEN 1 ELSE 0 END) AS INWARD_STOCK,
Stock_date
FROM dbo.Table_1 a
--WHERE stock_date = CAST(GETDATE() AS DATE)
GROUP BY ITEMCODE, STOCK_DATE
我的猜测是,@Machha想昨天的Closing_Stock
是今天的Opening_Stock
。
+0
谢谢,也会试试这个! – Machha 2014-10-30 20:39:10
答
CREATE TRIGGER [DBO].[Stock_close] ON [DBO].[ITEM_INWARD] after
UPDATE,
INSERT AS IF EXISTS
(
SELECT Count(*)
FROM stock_close_rpt xx,
inserted yy
WHERE Cast(xx.stock_date AS DATE) = Cast(Getdate() AS DATE)
AND xx.item_code=yy.item_code
)
BEGIN
UPDATE stock_close_rpt
SET item_code=a.item_code ,
opening_stock=
(
SELECT closing_stock =
CASE
WHEN
(
SELECT closing_stock
FROM stock_close_rpt
WHERE Cast(stock_date AS DATE) =
(
SELECT Max(Cast(stock_date AS DATE))
FROM stock_close_rpt c,
inserted d
WHERE c.item_code=d.item_code)) IS NOT NULL THEN closing_stock
ELSE '0'
END
FROM stock_close_rpt),
inward_stock=
(
SELECT status =
CASE
WHEN
(
SELECT Count(ab.status)
FROM item_inward ab,
inserted cd
WHERE ab.status = 'I'
AND Cast(ab.dt_added AS DATE) = Cast(Getdate() AS DATE)
AND ab.item_code=cd.item_code
AND ab.colour_id=cd.colour_id
GROUP BY ab.item_code,
ab.colour_id) IS NOT NULL THEN Count(status)
ELSE '0'
END
FROM item_inward
WHERE status = 'I'
AND Cast(dt_added AS DATE) = Cast(Getdate() AS DATE)),
outward_stock=
(
SELECT status =
CASE
WHEN
(
SELECT Count(ab.status)
FROM item_inward ab,
inserted cd
WHERE ab.status = 'D'
AND Cast(ab.dt_added AS DATE) = Cast(Getdate() AS DATE)
AND ab.item_code=cd.item_code
AND ab.colour_id=cd.colour_id
GROUP BY ab.item_code,
ab.colour_id) IS NOT NULL THEN Count(status)
ELSE '0'
END
FROM item_inward
WHERE status = 'D'
AND Cast(dt_added AS DATE) = Cast(Getdate() AS DATE)),
blocked_stock=
(
SELECT status =
CASE
WHEN
(
SELECT Count(ab.status)
FROM item_inward ab,
inserted cd
WHERE ab.status = 'B'
AND Cast(ab.dt_added AS DATE) = Cast(Getdate() AS DATE)
AND ab.item_code=cd.item_code
AND ab.colour_id=cd.colour_id
GROUP BY ab.item_code,
ab.colour_id) IS NOT NULL THEN Count(status)
ELSE '0'
END
FROM item_inward
WHERE status = 'B'
AND Cast(dt_added AS DATE) = Cast(Getdate() AS DATE)) ,
closing_stock=(
(
SELECT closing_stock =
CASE
WHEN
(
SELECT closing_stock
FROM stock_close_rpt
WHERE Cast(stock_date AS DATE) =
(
SELECT Max(Cast(stock_date AS DATE))
FROM stock_close_rpt c,
inserted d
WHERE c.item_code=d.item_code)) IS NOT NULL THEN closing_stock
ELSE '0'
END
FROM stock_close_rpt)+(
(
SELECT status =
CASE
WHEN
(
SELECT Count(ab.status)
FROM item_inward ab,
inserted cd
WHERE ab.status = 'I'
AND Cast(ab.dt_added AS DATE) = Cast(Getdate() AS DATE)
AND ab.item_code=cd.item_code
AND ab.colour_id=cd.colour_id
GROUP BY ab.item_code,
ab.colour_id) IS NOT NULL THEN Count(status)
ELSE '0'
END
FROM item_inward
WHERE status = 'I'
AND Cast(dt_added AS DATE) = Cast(Getdate() AS DATE)) -
(
SELECT status =
CASE
WHEN
(
SELECT Count(ab.status)
FROM item_inward ab,
inserted cd
WHERE ab.status = 'D'
AND Cast(ab.dt_added AS DATE) = Cast(Getdate() AS DATE)
AND ab.item_code=cd.item_code
AND ab.colour_id=cd.colour_id
GROUP BY ab.item_code,
ab.colour_id) IS NOT NULL THEN Count(status)
ELSE '0'
END
FROM item_inward
WHERE status = 'D'
AND Cast(dt_added AS DATE) = Cast(Getdate() AS DATE)))),
colour_id= a.colour_id,
stock_date=a.dt_added,
cp_id=a.cp_id
FROM dbo.item_inward a,
inserted b
WHERE a.item_code=b.item_code
AND Cast(a.dt_added AS DATE)=Cast(b.dt_added AS DATE)
group BY a.dt_added,
a.item_code,
a.colour_id,
a.cp_id ;
END
ELSE
BEGIN
INSERT INTO stock_close_rpt
SELECT a.item_code ,
(
SELECT closing_stock =
CASE
WHEN
(
SELECT closing_stock
FROM stock_close_rpt
WHERE Cast(stock_date AS DATE) =
(
SELECT Max(Cast(stock_date AS DATE))
FROM stock_close_rpt c,
inserted d
WHERE c.item_code=d.item_code)) IS NOT NULL THEN closing_stock
ELSE '0'
END
FROM stock_close_rpt) AS opening_stock,
(
SELECT status =
CASE
WHEN
(
SELECT Count(ab.status)
FROM item_inward ab,
inserted cd
WHERE ab.status = 'I'
AND Cast(ab.dt_added AS DATE) = Cast(Getdate() AS DATE)
AND ab.item_code=cd.item_code
AND ab.colour_id=cd.colour_id
GROUP BY ab.item_code,
ab.colour_id) IS NOT NULL THEN Count(status)
ELSE '0'
END
FROM item_inward
WHERE status = 'I'
AND Cast(dt_added AS DATE) = Cast(Getdate() AS DATE)) AS inward_stock,
(
SELECT status =
CASE
WHEN
(
SELECT Count(ab.status)
FROM item_inward ab,
inserted cd
WHERE ab.status = 'D'
AND Cast(ab.dt_added AS DATE) = Cast(Getdate() AS DATE)
AND ab.item_code=cd.item_code
AND ab.colour_id=cd.colour_id
GROUP BY ab.item_code,
ab.colour_id) IS NOT NULL THEN Count(status)
ELSE '0'
END
FROM item_inward
WHERE status = 'D'
AND Cast(dt_added AS DATE) = Cast(Getdate() AS DATE)) AS outward_stock,
(
SELECT status =
CASE
WHEN
(
SELECT Count(ab.status)
FROM item_inward ab,
inserted cd
WHERE ab.status = 'B'
AND Cast(ab.dt_added AS DATE) = Cast(Getdate() AS DATE)
AND ab.item_code=cd.item_code
AND ab.colour_id=cd.colour_id
GROUP BY ab.item_code,
ab.colour_id) IS NOT NULL THEN Count(status)
ELSE '0'
END
FROM item_inward
WHERE status = 'B'
AND Cast(dt_added AS DATE) = Cast(Getdate() AS DATE)) AS blocked_stock,
(
(
SELECT closing_stock =
CASE
WHEN
(
SELECT closing_stock
FROM stock_close_rpt
WHERE Cast(stock_date AS DATE) =
(
SELECT Max(Cast(stock_date AS DATE))
FROM stock_close_rpt c,
inserted d
WHERE c.item_code=d.item_code)) IS NOT NULL THEN closing_stock
ELSE '0'
END
FROM stock_close_rpt)+(
(
SELECT status =
CASE
WHEN
(
SELECT Count(ab.status)
FROM item_inward ab,
inserted cd
WHERE ab.status = 'I'
AND Cast(ab.dt_added AS DATE) = Cast(Getdate() AS DATE)
AND ab.item_code=cd.item_code
AND ab.colour_id=cd.colour_id
GROUP BY ab.item_code,
ab.colour_id) IS NOT NULL THEN Count(status)
ELSE '0'
END
FROM item_inward
WHERE status = 'I'
AND Cast(dt_added AS DATE) = Cast(Getdate() AS DATE)) -
(
SELECT status =
CASE
WHEN
(
SELECT Count(ab.status)
FROM item_inward ab,
inserted cd
WHERE ab.status = 'D'
AND Cast(ab.dt_added AS DATE) = Cast(Getdate() AS DATE)
AND ab.item_code=cd.item_code
AND ab.colour_id=cd.colour_id
GROUP BY ab.item_code,
ab.colour_id) IS NOT NULL THEN Count(status)
ELSE '0'
END
FROM item_inward
WHERE status = 'D'
AND Cast(dt_added AS DATE) = Cast(Getdate() AS DATE)))) AS closing_stock,
a.colour_id,
a.cp_id,
a.dt_added
FROM dbo.item_inward a,
inserted b
WHERE a.item_code=b.item_code
AND Cast(a.dt_added AS DATE)=Cast(b.dt_added AS DATE)
GROUP BY a.dt_added,
a.item_code,
a.colour_id,
a.cp_id;
END
+0
需要上述更新中的group by子句的解决方案statment – Machha 2014-10-30 20:38:42
这很好。祝你好运。你有问题吗? – 2014-10-30 14:56:02
'任何帮助赞赏'不是一个问题。 – 2014-10-30 14:56:33
解释你为什么得到样本结果,并编辑原始表格以便读取它。有比标题更多的字段 – JotaBe 2014-10-30 14:57:55