有大神帮忙看下吗?本月合计,本年合计问题

Kaelz ERP开发  2015-07-27 02:51:12
类似财务账本的显示
CREATE TABLE [dbo].[stockin](
[fdate] [datetime] NULL,
[fitemname] [varchar](100) NULL,
[fqtyin] [decimal](18, 2) NULL,
[fqtyou] [decimal](18, 2) NULL,
[fmonth] [datetime] NULL,
[fday] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[stockin] ([fdate], [fitemname], [fqtyin], [fqtyou], [fmonth], [fday]) VALUES (CAST(0x000092F600000000 AS DateTime), N'AAA', CAST(125.00 AS Decimal(18, 2)), CAST(20.00 AS Decimal(18, 2)), CAST(0x00009312018B80D4 AS DateTime), CAST(0x00009312018B7FA8 AS DateTime))
INSERT [dbo].[stockin] ([fdate], [fitemname], [fqtyin], [fqtyou], [fmonth], [fday]) VALUES (CAST(0x000092FD00000000 AS DateTime), N'AAA', CAST(120.00 AS Decimal(18, 2)), CAST(20.00 AS Decimal(18, 2)), CAST(0x00009312018B80D4 AS DateTime), CAST(0x00009312018B7FA8 AS DateTime))
INSERT [dbo].[stockin] ([fdate], [fitemname], [fqtyin], [fqtyou], [fmonth], [fday]) VALUES (CAST(0x0000930000000000 AS DateTime), N'BBB', CAST(-20.00 AS Decimal(18, 2)), CAST(20.00 AS Decimal(18, 2)), CAST(0x00009312018B80D4 AS DateTime), CAST(0x00009312018B7FA8 AS DateTime))
INSERT [dbo].[stockin] ([fdate], [fitemname], [fqtyin], [fqtyou], [fmonth], [fday]) VALUES (CAST(0x0000931100000000 AS DateTime), N'AAA', CAST(25.00 AS Decimal(18, 2)), CAST(20.00 AS Decimal(18, 2)), CAST(0x00009312018B80D4 AS DateTime), CAST(0x00009312018B7FA8 AS DateTime))
INSERT [dbo].[stockin] ([fdate], [fitemname], [fqtyin], [fqtyou], [fmonth], [fday]) VALUES (CAST(0x0000931500000000 AS DateTime), N'BBB', CAST(100.00 AS Decimal(18, 2)), CAST(20.00 AS Decimal(18, 2)), CAST(0x0000932E018B80D4 AS DateTime), CAST(0x0000932E018B7FA8 AS DateTime))
INSERT [dbo].[stockin] ([fdate], [fitemname], [fqtyin], [fqtyou], [fmonth], [fday]) VALUES (CAST(0x0000933300000000 AS DateTime), N'CCC', CAST(120.00 AS Decimal(18, 2)), CAST(20.00 AS Decimal(18, 2)), CAST(0x0000934D018B80D4 AS DateTime), CAST(0x0000934D018B7FA8 AS DateTime))

求每月的本月合计,每个月下面追加本年合计

该如何用SQL实现呢??大神求帮忙!!!万分感谢!
...全文
116 点赞 收藏 9
写回复
9 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
Tiger_Zhao 2015-07-27
不分批号一起累计
;WITH a AS (
SELECT fitemname,
YEAR(fdate) [year],
MONTH(fdate) [month],
CONVERT(varchar(23),fdate,121) fdate,
fqtyin,
fqtyou,
fqtyin-fqtyou fqty
FROM stockin
)
,b AS (
SELECT [year],
[month],
SUM(fqtyin) fqtyin,
SUM(fqtyou) fqtyou,
SUM(fqty) fqty,
GROUPING([month]) g1,
GROUPING([year]) g2
FROM a
GROUP BY [year], [month] WITH ROLLUP
)
,c AS (
SELECT fitemname,
[year],
[month],
fdate,
fqtyin,
fqtyou,
fqty,
0 g0,
0 g1
FROM a
UNION ALL
SELECT '' fitemname,
[year],
[month],
CONVERT(varchar(23),
CASE WHEN g1 = 1 THEN
CONVERT(varchar(4),[year])
ELSE
CONVERT(varchar(4),[year])+'-'+
RIGHT('0'+CONVERT(varchar(4),[month]),2)
END) fdate,
fqtyin,
fqtyou,
fqty,
1 g0,
g1
FROM b
WHERE g2 <> 1
)
SELECT fitemname,
fdate,
CASE WHEN g1 = 1 THEN '本年累计'
WHEN g0 = 1 THEN '本月累计'
ELSE ''
END memo,
fqtyin,
fqtyou,
fqty
FROM c
ORDER BY g1,[month],g0

fitemname  fdate                   memo         fqtyin     fqtyou       fqty
---------- ----------------------- -------- ---------- ---------- ----------
AAA 2003-01-03 00:00:00.000 125.00 20.00 105.00
AAA 2003-01-10 00:00:00.000 120.00 20.00 100.00
BBB 2003-01-13 00:00:00.000 -20.00 20.00 -40.00
AAA 2003-01-30 00:00:00.000 25.00 20.00 5.00
2003-01 本月累计 250.00 80.00 170.00
BBB 2003-02-03 00:00:00.000 100.00 20.00 80.00
2003-02 本月累计 100.00 20.00 80.00
CCC 2003-03-05 00:00:00.000 120.00 20.00 100.00
2003-03 本月累计 120.00 20.00 100.00
2003 本年累计 470.00 120.00 350.00
回复
Tiger_Zhao 2015-07-27
不是同一个批号啊!
你到底是怎么累计的?
回复
Kaelz 2015-07-27
引用 6 楼 Tiger_Zhao 的回复:
;WITH a AS (
    SELECT fitemname,
           YEAR(fdate) [year],
           MONTH(fdate) [month],
           CONVERT(varchar(23),fdate,121) fdate,
           fqtyin,
           fqtyou,
           fqtyin-fqtyou fqty
      FROM stockin
)
,b AS (
    SELECT fitemname,
           [year],
           [month],
           SUM(fqtyin) fqtyin,
           SUM(fqtyou) fqtyou,
           SUM(fqty) fqty,
           GROUPING([month]) g1,
           GROUPING([year]) g2,
           GROUPING(fitemname) g3
     FROM a
 GROUP BY fitemname, [year], [month] WITH ROLLUP
)
,c AS (
    SELECT fitemname,
           [year],
           [month],
           fdate,
           fqtyin,
           fqtyou,
           fqty,
           0 g0,
           0 g1
      FROM a
    UNION ALL
    SELECT fitemname,
           [year],
           [month],
           CONVERT(varchar(23),
                   CASE WHEN g1 = 1 THEN
                        CONVERT(varchar(4),[year])
                   ELSE 
                        CONVERT(varchar(4),[year])+'-'+
                        RIGHT('0'+CONVERT(varchar(4),[month]),2)
                   END) fdate,
           fqtyin,
           fqtyou,
           fqty,
           1 g0,
           g1
      FROM b
     WHERE g2 <> 1
       AND g3 <> 1
)
    SELECT fitemname,
           fdate,
           CASE WHEN g1 = 1 THEN '本年累计'
                WHEN g0 = 1 THEN '本月累计'
                ELSE ''
           END memo,
           fqtyin,
           fqtyou,
           fqty
      FROM c
  ORDER BY fitemname,g1,[month],g0
fitemname  fdate                   memo         fqtyin     fqtyou       fqty
---------- ----------------------- -------- ---------- ---------- ----------
AAA        2003-01-03 00:00:00.000              125.00      20.00     105.00
AAA        2003-01-10 00:00:00.000              120.00      20.00     100.00
AAA        2003-01-30 00:00:00.000               25.00      20.00       5.00
AAA        2003-01                 本月累计     270.00      60.00     210.00
AAA        2003                    本年累计     270.00      60.00     210.00
BBB        2003-01-13 00:00:00.000              -20.00      20.00     -40.00
BBB        2003-01                 本月累计     -20.00      20.00     -40.00
BBB        2003-02-03 00:00:00.000              100.00      20.00      80.00
BBB        2003-02                 本月累计     100.00      20.00      80.00
BBB        2003                    本年累计      80.00      40.00      40.00
CCC        2003-03-05 00:00:00.000              120.00      20.00     100.00
CCC        2003-03                 本月累计     120.00      20.00     100.00
CCC        2003                    本年累计     120.00      20.00     100.00
还是有点问题 1-13号被1月份独立了,本年累计是往前包含的,3月份要包含1.2.3月份的数据
回复
Tiger_Zhao 2015-07-27
;WITH a AS (
SELECT fitemname,
YEAR(fdate) [year],
MONTH(fdate) [month],
CONVERT(varchar(23),fdate,121) fdate,
fqtyin,
fqtyou,
fqtyin-fqtyou fqty
FROM stockin
)
,b AS (
SELECT fitemname,
[year],
[month],
SUM(fqtyin) fqtyin,
SUM(fqtyou) fqtyou,
SUM(fqty) fqty,
GROUPING([month]) g1,
GROUPING([year]) g2,
GROUPING(fitemname) g3
FROM a
GROUP BY fitemname, [year], [month] WITH ROLLUP
)
,c AS (
SELECT fitemname,
[year],
[month],
fdate,
fqtyin,
fqtyou,
fqty,
0 g0,
0 g1
FROM a
UNION ALL
SELECT fitemname,
[year],
[month],
CONVERT(varchar(23),
CASE WHEN g1 = 1 THEN
CONVERT(varchar(4),[year])
ELSE
CONVERT(varchar(4),[year])+'-'+
RIGHT('0'+CONVERT(varchar(4),[month]),2)
END) fdate,
fqtyin,
fqtyou,
fqty,
1 g0,
g1
FROM b
WHERE g2 <> 1
AND g3 <> 1
)
SELECT fitemname,
fdate,
CASE WHEN g1 = 1 THEN '本年累计'
WHEN g0 = 1 THEN '本月累计'
ELSE ''
END memo,
fqtyin,
fqtyou,
fqty
FROM c
ORDER BY fitemname,g1,[month],g0

fitemname  fdate                   memo         fqtyin     fqtyou       fqty
---------- ----------------------- -------- ---------- ---------- ----------
AAA 2003-01-03 00:00:00.000 125.00 20.00 105.00
AAA 2003-01-10 00:00:00.000 120.00 20.00 100.00
AAA 2003-01-30 00:00:00.000 25.00 20.00 5.00
AAA 2003-01 本月累计 270.00 60.00 210.00
AAA 2003 本年累计 270.00 60.00 210.00
BBB 2003-01-13 00:00:00.000 -20.00 20.00 -40.00
BBB 2003-01 本月累计 -20.00 20.00 -40.00
BBB 2003-02-03 00:00:00.000 100.00 20.00 80.00
BBB 2003-02 本月累计 100.00 20.00 80.00
BBB 2003 本年累计 80.00 40.00 40.00
CCC 2003-03-05 00:00:00.000 120.00 20.00 100.00
CCC 2003-03 本月累计 120.00 20.00 100.00
CCC 2003 本年累计 120.00 20.00 100.00
回复
Kaelz 2015-07-27
引用 2 楼 u010024618 的回复:
你的结存数量是啥算的
收入-付出
回复
zbdzjx 2015-07-27
噢,还有一个结存,结存来源在哪?如果是收入,就用上期的结存加上本期的收入;如果是付出,就用上期的结存减去本期的付出。
回复
zbdzjx 2015-07-27
本月合计,类似下面的语句:
select convert(varchar(7), 日期, 121) 年月, SUM(收入), SUM(付出) from 表 group by convert(varchar(7), 日期, 121)
本年合计,类似下面的语句:
select a.年月, SUM(b.收入) 收入, SUM(b.付出) 付出 from 
(select convert(varchar(7), 日期, 121) 年月, SUM(收入) 收入, SUM(付出) 付出 from 表 group by convert(varchar(7), 日期, 121)) a
left join 
(select convert(varchar(7), 日期, 121) 年月, SUM(收入) 收入, SUM(付出) 付出 from 表 group by convert(varchar(7), 日期, 121)) b
on a.年月>=b.年月 and SUBSTRING(a.年月,1,4)=SUBSTRING(b.年月,1,4)
group by a.年月
将年月转换成当月最后一天的最后一秒,网上可以找到。
回复
freecodex 2015-07-27
你的结存数量是啥算的
回复
Kaelz 2015-07-27
大神们来看看啊~
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2015-07-27 02:51
社区公告
暂无公告