Run Code
|
API
|
Code Wall
|
Users
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
test 2018.05.26
declare @g VARCHAR(50) = '9100010830'; declare @dt date = '20170101'; declare @c table (d date); with cte(n) as ( select 0 union all select n+1 from cte where n < datediff(day,@dt,dateadd(day,-1,dateadd(year,1,@dt))) ) insert @c select dateadd(dd, n, @dt) as d from cte option (maxrecursion 0) -- Справочник товаров CREATE TABLE #Goods( ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, code VARCHAR(50) NOT NULL ) -- Таблица движения товара CREATE TABLE #Uchcard( ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, goods_id INT NOT NULL FOREIGN KEY REFERENCES Goods(ID), [date] DATETIME NOT NULL, goods_in INT NOT NULL, goods_out INT NOT NULL ) -- данные INSERT INTO #Goods(code) VALUES('9100010830') -- движение товара INSERT INTO #Uchcard(goods_id, [date], goods_in, goods_out) SELECT N'1' AS [goods_id], N'2017-01-04 14:51:00.000' AS [dat], N'0' AS [ingoods], N'1' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-01-11 09:59:00.000' AS [dat], N'0' AS [ingoods], N'11' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-01-13 16:16:27.000' AS [dat], N'60' AS [ingoods], N'0' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-01-18 09:51:00.000' AS [dat], N'0' AS [ingoods], N'24' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-01-23 10:19:00.000' AS [dat], N'0' AS [ingoods], N'6' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-01-25 12:26:00.000' AS [dat], N'0' AS [ingoods], N'2' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-01-27 10:43:00.000' AS [dat], N'0' AS [ingoods], N'24' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-01-30 09:40:00.000' AS [dat], N'0' AS [ingoods], N'2' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-01-30 17:02:00.000' AS [dat], N'0' AS [ingoods], N'2' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-02-10 10:09:29.907' AS [dat], N'0' AS [ingoods], N'1' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-02-11 09:59:00.000' AS [dat], N'0' AS [ingoods], N'0' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-02-17 20:01:05.000' AS [dat], N'79' AS [ingoods], N'0' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-02-27 10:04:00.000' AS [dat], N'0' AS [ingoods], N'10' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-02-28 16:43:00.000' AS [dat], N'0' AS [ingoods], N'1' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-03-03 09:30:00.000' AS [dat], N'0' AS [ingoods], N'12' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-03-10 09:26:00.000' AS [dat], N'0' AS [ingoods], N'5' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-03-13 09:29:00.000' AS [dat], N'0' AS [ingoods], N'8' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-03-17 10:14:00.000' AS [dat], N'0' AS [ingoods], N'10' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-03-18 14:28:00.000' AS [dat], N'0' AS [ingoods], N'2' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-03-19 16:15:00.000' AS [dat], N'0' AS [ingoods], N'1' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-03-20 09:21:00.000' AS [dat], N'0' AS [ingoods], N'6' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-03-22 18:40:52.000' AS [dat], N'50' AS [ingoods], N'0' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-03-24 09:18:00.000' AS [dat], N'0' AS [ingoods], N'6' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-03-25 09:55:00.000' AS [dat], N'0' AS [ingoods], N'0' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-03-31 09:47:00.000' AS [dat], N'0' AS [ingoods], N'4' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-04-07 16:15:00.000' AS [dat], N'0' AS [ingoods], N'6' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-04-14 09:44:00.000' AS [dat], N'0' AS [ingoods], N'4' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-04-17 19:39:00.000' AS [dat], N'0' AS [ingoods], N'1' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-04-21 09:40:00.000' AS [dat], N'0' AS [ingoods], N'6' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-04-24 09:56:00.000' AS [dat], N'0' AS [ingoods], N'8' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-04-27 11:25:00.000' AS [dat], N'0' AS [ingoods], N'1' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-04-28 09:54:00.000' AS [dat], N'0' AS [ingoods], N'2' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-05-17 12:40:00.000' AS [dat], N'0' AS [ingoods], N'2' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-05-19 09:32:00.000' AS [dat], N'0' AS [ingoods], N'2' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-05-19 12:25:13.000' AS [dat], N'60' AS [ingoods], N'0' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-05-22 09:43:00.000' AS [dat], N'0' AS [ingoods], N'6' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-05-28 10:13:00.000' AS [dat], N'0' AS [ingoods], N'1' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-05-30 16:35:00.000' AS [dat], N'0' AS [ingoods], N'1' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-06-02 09:40:00.000' AS [dat], N'0' AS [ingoods], N'4' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-06-08 10:25:00.000' AS [dat], N'0' AS [ingoods], N'2' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-06-09 11:47:52.000' AS [dat], N'129' AS [ingoods], N'0' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-06-15 10:43:00.000' AS [dat], N'0' AS [ingoods], N'4' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-06-22 10:04:00.000' AS [dat], N'0' AS [ingoods], N'8' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-06-26 09:19:00.000' AS [dat], N'0' AS [ingoods], N'6' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-07-05 17:47:33.000' AS [dat], N'168' AS [ingoods], N'0' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-07-06 09:36:00.000' AS [dat], N'0' AS [ingoods], N'50' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-07-07 12:05:39.000' AS [dat], N'270' AS [ingoods], N'0' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-07-11 10:30:00.000' AS [dat], N'0' AS [ingoods], N'1' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-07-14 09:34:00.000' AS [dat], N'0' AS [ingoods], N'100' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-07-19 10:04:00.000' AS [dat], N'0' AS [ingoods], N'66' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-07-24 09:26:00.000' AS [dat], N'0' AS [ingoods], N'25' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-07-24 18:07:00.000' AS [dat], N'0' AS [ingoods], N'1' AS [outgoods] UNION ALL SELECT N'1' AS [goods_id], N'2017-07-27 09:59:00.000' AS [dat], N'0' AS [ingoods], N'30' AS [outgoods] select c.d [Дата движения] , isnull(goods_in,0) as Приход , isnull(goods_out,0) as Расход ,sum(isnull(goods_in,0)+(-1)*isnull(goods_out,0)) over (order by c.d) - (isnull(goods_in,0)+(-1)*isnull(goods_out,0)) as [Остаток на начало дня] ,sum(isnull(goods_in,0)+(-1)*isnull(goods_out,0)) over (order by c.d) as [Остаток на конец дня] from @c as c cross join (select id from #Goods where code = @g) as g left join (select goods_id, convert(date,[date]) as d, sum(goods_in) as goods_in, sum(goods_out) as goods_out from #Uchcard group by goods_id, convert(date,[date]) ) as uc on uc.d = c.d and g.id = uc.goods_id drop table #Goods drop table #Uchcard
run
|
edit
|
history
|
help
0
Please
log in
to post a comment.
Sql Question 1
Using pre-built schema in sql server
student
Rename column name using 'sp_rename' command
Use the right tool to get identity values back after an insert
Adding, subtracting, rounding and calculating differences
SQL ASSESSMENT
Second Query
How to find 2nd highest salary
Train Reservation
Please log in to post a comment.