34,588
社区成员
发帖
与我相关
我的任务
分享
if not object_id(N'Tempdb..#T_1') is null
drop table #T_1
Go
Create table #T_1([产品] nvarchar(20),[入库时间] nvarchar(20),[入库数量] int)
Insert #T_1
select N'A',N'12月1日',20 union all
select N'A',N'11月18日',10 union all
select N'A',N'10月18日',15 union ALL
select N'A',N'9月20日',30 union all
select N'A',N'8月3日',25 union all
select N'B',N'12月1日',20 union all
select N'B',N'11月18日',10 union all
select N'B',N'10月1日',15 union all
select N'B',N'9月20日',30 union all
select N'B',N'8月3日',25
if not object_id(N'Tempdb..#T_2') is null
drop table #T_2
Go
CREATE TABLE #T_2
(产品 nvarchar(20),结存数 int)
INSERT INTO #T_2
SELECT 'A',50
WITH CTE_1
AS
(SELECT *,
CAST(CAST(YEAR(GETDATE()) AS VARCHAR)+'.'+REPLACE(REPLACE([入库时间],'月','.'),'日','') AS DATE) AS 完整时间 FROM #T_1 A),
CTE_2
AS
(SELECT A.产品,结存数,最早入库时间,
SUM(CASE WHEN SUB_TOTAL<=结存数 THEN 入库数量 ELSE 结存数-(SUB_TOTAL-入库数量) END) AS 期间库存,
CASE WHEN DATEDIFF(DD,完整时间,GETDATE())<30 THEN '30天内'
WHEN DATEDIFF(DD,完整时间,GETDATE())>=30 AND DATEDIFF(DD,完整时间,GETDATE())<=60 THEN '30-60天'
WHEN DATEDIFF(DD,完整时间,GETDATE())>=61 AND DATEDIFF(DD,完整时间,GETDATE())<90 THEN '61-90天'
ELSE '91天以上' END AS 期间
FROM
(SELECT A.*,B.SUB_TOTAL,C.结存数,MIN(完整时间) OVER (PARTITION BY A.产品) AS 最早入库时间
FROM CTE_1 A
OUTER APPLY (SELECT SUM([入库数量]) AS SUB_TOTAL FROM CTE_1 WHERE 产品=A.产品 AND 完整时间>=A.完整时间) AS B
JOIN #T_2 C ON A.产品=C.产品 AND B.SUB_TOTAL-A.入库数量<=C.结存数) AS A
GROUP BY A.产品,结存数,最早入库时间,CASE WHEN DATEDIFF(DD,完整时间,GETDATE())<30 THEN '30天内'
WHEN DATEDIFF(DD,完整时间,GETDATE())>=30 AND DATEDIFF(DD,完整时间,GETDATE())<=60 THEN '30-60天'
WHEN DATEDIFF(DD,完整时间,GETDATE())>=61 AND DATEDIFF(DD,完整时间,GETDATE())<90 THEN '61-90天'
ELSE '91天以上' END)
SELECT 产品,结存数,最早入库时间,
ISNULL([30天内],0) AS [30天内],ISNULL([30-60天],0) AS [30-60天],ISNULL([61-90天],0) AS [61-90天],ISNULL([91天以上],0) AS [91天以上]
FROM CTE_2 A
PIVOT (MAX(期间库存) FOR 期间 IN ([30天内],[30-60天],[61-90天],[91天以上])) B
use Tempdb
go
--> --> 听雨停了-->测试数据
if not object_id(N'Tempdb..#tabA') is null
drop table #tabA
Go
Create table #tabA([产品] nvarchar(21),[入库时间] nvarchar(26),[入库数量] int)
Insert #tabA
select N'A',N'12月1日',20 union all
select N'A',N'11月18日',10 union all
select N'A',N'10月18日',15 union ALL --A改成了18号
select N'A',N'9月20日',30 union all
select N'A',N'8月3日',25 union all
select N'B',N'12月1日',20 union all
select N'B',N'11月18日',10 union all
select N'B',N'10月1日',15 union all --B没改(数据上面复制下来的)
select N'B',N'9月20日',30 union all
select N'B',N'8月3日',25
if not object_id(N'Tempdb..#tabB') is null
drop table #tabB
Go
Create table #tabB([产品] nvarchar(21),[结存数] int)
Insert #tabB
select N'A',50 UNION ALL
select N'B',80
--测试数据结束
--先删除#tabC表,因为后面会用到
if not object_id(N'Tempdb..#tabC') is null
drop table #tabC
GO
--说实话你这里面的时间搞成这样也是醉了,好好的日期类型不用搞成几月几日干嘛啊。
;WITH cte AS (
SELECT *,cast(('2017-'+replace(replace(入库时间,'月','-'),'日','')) as date) as dt,ROW_NUMBER() OVER(PARTITION BY 产品 order BY cast(('2017-'+replace(replace(入库时间,'月','-'),'日','')) as date) desc) as rn
FROM #tabA
),
cte2 AS(
SELECT *,
CASE WHEN dt>DATEADD(DAY,-30,GETDATE())
THEN '1'
WHEN dt<DATEADD(DAY,-30,GETDATE()) AND dt>DATEADD(DAY,-60,GETDATE())
THEN '2'
WHEN dt<DATEADD(DAY,-60,GETDATE()) AND dt>DATEADD(DAY,-90,GETDATE())
THEN '3'
WHEN dt<DATEADD(DAY,-90,GETDATE())
THEN '4'
END AS title
FROM cte
),
cte3 AS (
SELECT 产品,title,sum(入库数量) as cnt,row_number() over(PARTITION by 产品 order by title) as rn
FROM cte2 GROUP BY 产品,title
),
cte4 AS (
SELECT a.*, b.结存数-a.cnt as total_cnt FROM cte3 a
INNER JOIN #tabb b ON a.产品=b.产品
WHERE rn=1
UNION ALL
SELECT a.*, b.total_cnt-a.cnt
FROM cte3 a
INNER JOIN cte4 b ON a.rn=b.rn+1 AND a.产品=b.产品
),
cte5 AS (
SELECT *,ROW_NUMBER() OVER(PARTITION BY 产品 order by total_cnt desc) as rn_2 FROM cte4
WHERE total_cnt<=0
),
--统计每个产品在 (30天内 30-60天 61到90天 91天以上) 的真实库存数据
cte6 AS (
SELECT 产品,title,cnt FROM cte4
WHERE total_cnt>0
UNION ALL
SELECT 产品,title,cnt-abs(total_cnt) FROM cte5
WHERE rn_2=1
),
cte7 AS (
SELECT a.*,b.结存数, b.结存数-a.入库数量 as cnt FROM cte a
INNER JOIN #tabb b ON a.产品=b.产品
WHERE rn=1
UNION ALL
SELECT a.*,b.结存数, b.cnt-a.入库数量
FROM cte a
INNER JOIN cte7 b ON a.rn=b.rn+1 AND a.产品=b.产品
),
cte8 AS (
SELECT 产品,入库时间,结存数,row_number() over(PARTITION by 产品 order by dt desc ) as rn
FROM cte7
WHERE cnt<=0
),
--获取每个产品的最早入库时间
cte9 AS (
SELECT 产品,入库时间,结存数 FROM cte8
WHERE rn=1
),
--建立辅助表,用以连接上面的cte6
cte10 AS (
SELECT 1 AS id ,'30天内' as title union all
SELECT 2 AS id ,'31-60天' as title union all
SELECT 3 AS id ,'61到90天' as title union all
SELECT 4 AS id ,'91天以上' as title
)
--最终数据插入#tabC中
SELECT a.产品,a.cnt,b.入库时间,b.结存数,c.title
into #tabC
FROM cte6 a
INNER JOIN cte9 b ON a.产品=b.产品
RIGHT JOIN cte10 c ON a.title=c.id
--查询最终数据
SELECT * FROM #tabC
--#tabC表行转列显示
DECLARE @name VARCHAR(max),@sql VARCHAR(max)
set @name =stuff((SELECT DISTINCT ',['+[title]+']' from #tabC for xml PATH('')),1,1,'')
set @sql ='SELECT * from #tabC pivot(max(cnt)for title in('+@name+'))a where isnull(产品,'''')<>'''''
PRINT @sql
EXEC( @sql)
SET NOCOUNT ON
DECLARE @t1 TABLE (rid INT,productName NVARCHAR(10),inTime DATETIME,inCnt INT)
INSERT INTO @t1 VALUES (1,'A','2017-12-01',20)
INSERT INTO @t1 VALUES (2,'A','2017-11-18',10)
INSERT INTO @t1 VALUES (3,'A','2017-10-18',15)
INSERT INTO @t1 VALUES (4,'A','2017-09-20',30)
INSERT INTO @t1 VALUES (5,'A','2017-08-03',25)
DECLARE @t2 TABLE (productName NVARCHAR(10),jcCnt INT)
INSERT INTO @t2 VALUES ('A',50)
;with cte as (
SELECT t2.productName
,t2.jcCnt
,ISNULL((SELECT sum(inCnt) FROM @t1 AS t1 WHERE t1.productName=t2.productName AND DATEDIFF(DAY,t1.inTime,GETDATE())<30),0) AS in30
,ISNULL((SELECT sum(inCnt) FROM @t1 AS t1 WHERE t1.productName=t2.productName AND DATEDIFF(DAY,t1.inTime,GETDATE()) BETWEEN 30 AND 60 ),0) AS in30and60
,ISNULL((SELECT sum(inCnt) FROM @t1 AS t1 WHERE t1.productName=t2.productName AND DATEDIFF(DAY,t1.inTime,GETDATE()) BETWEEN 61 AND 90 ),0) AS in61and90
,ISNULL((SELECT sum(inCnt) FROM @t1 AS t1 WHERE t1.productName=t2.productName AND DATEDIFF(DAY,t1.inTime,GETDATE())>=91),0) AS over91
FROM @t2 AS t2
),cte2 AS(
SELECT *
,jcCnt-in30 as sub_in30
,jcCnt-in30-in30and60 as sub_in30and60
,jcCnt-in30-in30and60-in61and90 AS sub_in61and90
,jcCnt-in30-in30and60-in61and90-over91 AS sub_over91
FROM cte
),cte3 AS (
SELECT productName
,jcCnt
,CASE WHEN sub_in30>=0 THEN in30 ELSE 0 END AS in30
,CASE WHEN sub_in30and60>=0 THEN in30and60 ELSE sub_in30 END AS in30and60
,CASE WHEN sub_in61and90>=0 THEN in61and90 ELSE sub_in30and60 END AS in61and90
,CASE WHEN sub_over91>=0 THEN over91 ELSE sub_in61and90 END AS over91
FROM cte2
),cte4 AS (
SELECT productName
,jcCnt
,CASE WHEN in30>=0 THEN in30 ELSE 0 END AS in30
,CASE WHEN in30and60>=0 THEN in30and60 ELSE 0 END AS in30and60
,CASE WHEN in61and90>=0 THEN in61and90 ELSE 0 END AS in61and90
,CASE WHEN over91>=0 THEN over91 ELSE 0 END AS over91
FROM cte3
),cte5 AS (
SELECT 'in30' AS flag,productName,MIN(inTime) AS minInTime FROM @t1 AS t1 WHERE DATEDIFF(DAY,t1.inTime,GETDATE())<30 GROUP BY t1.productName
UNION ALL
SELECT 'in30and60' AS flag,productName,MIN(inTime) AS minInTime FROM @t1 AS t1 WHERE DATEDIFF(DAY,t1.inTime,GETDATE()) BETWEEN 30 AND 60 GROUP BY t1.productName
UNION ALL
SELECT 'in61and90' AS flag,productName,MIN(inTime) AS minInTime FROM @t1 AS t1 WHERE DATEDIFF(DAY,t1.inTime,GETDATE()) BETWEEN 61 AND 90 GROUP BY t1.productName
UNION ALL
SELECT 'over91' AS flag,productName,MIN(inTime) AS minInTime FROM @t1 AS t1 WHERE DATEDIFF(DAY,t1.inTime,GETDATE()) >=91 GROUP BY t1.productName
)
SELECT productName
,jcCnt
,(SELECT minInTime
FROM cte5
WHERE flag=CASE WHEN A.in30=0 OR A.in30and60=0 THEN 'in30'
WHEN A.in61and90=0 THEN 'in30and60'
ELSE 'in61and90' END
) AS minInTime
,in30
,in30and60
,in61and90
,over91
FROM cte4 AS A
/*
productName jcCnt minInTime in30 in30and60 in61and90 over91
----------- ----------- ----------------------- ----------- ----------- ----------- -----------
A 50 2017-09-20 00:00:00.000 30 15 5 0
*/
按你意思拼出来了, 有小问题你自己再改改SET NOCOUNT ON
DECLARE @t1 TABLE (rid INT,productName NVARCHAR(10),inTime DATETIME,inCnt INT)
INSERT INTO @t1 VALUES (1,'A','2017-12-01',20)
INSERT INTO @t1 VALUES (2,'A','2017-11-18',10)
INSERT INTO @t1 VALUES (3,'A','2017-10-01',15)
INSERT INTO @t1 VALUES (4,'A','2017-09-20',30)
INSERT INTO @t1 VALUES (5,'A','2017-08-03',25)
DECLARE @t2 TABLE (productName NVARCHAR(10),jcCnt INT)
INSERT INTO @t2 VALUES ('A',50)
SELECT t2.productName
,t2.jcCnt
,ISNULL((SELECT sum(inCnt) FROM @t1 AS t1 WHERE t1.productName=t2.productName AND DATEDIFF(DAY,t1.inTime,GETDATE())<30),0) AS in30
,ISNULL((SELECT sum(inCnt) FROM @t1 AS t1 WHERE t1.productName=t2.productName AND DATEDIFF(DAY,t1.inTime,GETDATE()) BETWEEN 30 AND 60 ),0) AS in30and60
,ISNULL((SELECT sum(inCnt) FROM @t1 AS t1 WHERE t1.productName=t2.productName AND DATEDIFF(DAY,t1.inTime,GETDATE()) BETWEEN 61 AND 90 ),0) AS in61and90
,ISNULL((SELECT sum(inCnt) FROM @t1 AS t1 WHERE t1.productName=t2.productName AND DATEDIFF(DAY,t1.inTime,GETDATE())>=91),0) AS over91
FROM @t2 AS t2
/*
productName jcCnt in30 in30and60 in61and90 over91
----------- ----------- ----------- ----------- ----------- -----------
A 50 30 0 45 25
*/
基本就是这样, 结果跟你的有些对不上, 你自己去计算日期吧。