test 2018.05.26, Sql Server

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