sql into the master new challenges

Category: MS-SQL Server -> Applications Author: jing8881247591 Date: 2009-10-26 13:57:57
 
jing8881247591
2009-10-26 13:57:57
Now such a material sheet dates are
with the inventory of existing stocks of the missing quantity

aa 2010-05-11 ; 5.0000 37.00 -32.0000 (required inventory - existing inventory)
aa 2010-05-15 4.0000 37.00 -33.0000 (required inventory - existing inventory)
aa 2010-05-17 12.0000 37.00 ; -25.0000 (required inventory - existing inventory)

bb 2010-05-12 7.0000 8 -1.0000 ; (required inventory - existing inventory)
bb 2010-05-11 2.0000 8 -6.0000 (required inventory - existing inventory)

I think the result is
date materials required with the inventory of existing stocks of the missing quantity
aa 2010-05-11 5.0000 ; 37.00 -32.0000 (required inventory - existing inventory)
aa 2010-05-15 ; 4.0000 37.00 -28.0000 (before the required date inventory and - existing inventory)
aa 2010-05-17 12.0000 37.00 -16.0000 (required before date inventory and - now Stock)
bb 2010-05-12 7.0000 6 ; 1.0000
bb 2010-05-11 2.0000 6 3.0000
Awu50ling
2009-10-26 14:12:17
This is not a question of whether SQL is a design problem
doulin008
2009-10-26 14:20:02
----------------------------------------------------------------
-- Author  :SQL77( )
-- Date    :2010-05-11 12:31:02
-- Version:
--      Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) 
-- Oct 14 2005 00:33:37 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
-->  :#tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go 
create table #tb([ ] varchar(2),[ ] datetime,[ ] numeric(6,4),[ ] numeric(4,2),[ ] numeric(6,4))
insert #tb
select 'aa','2010-05-11',5.0000,37.00,-32.0000 union all
select 'aa','2010-05-15',4.0000,37.00,-33.0000 union all
select 'aa','2010-05-17',12.0000,37.00,-25.0000 union all
select 'bb','2010-05-12',7.0000,8,-1.0000 union all
select 'bb','2010-05-11',2.0000,8,-6.0000
-------------- --------------------------

select 
,
,
[ ],
,
- +(SELECT SUM([ ]) FROM #TB WHERE [ ]=T.[ ] AND [ ]<=T.[ ]) 
 from #tb T
---------------- ----------------------------
/* ]

(5  )
                                                                                                    
---- ----------------------- --------------------------------------- --------------------------------------- ---------------------------------------
aa   2010-05-11 00:00:00.000 5.0000                                  37.00                                   -32.0000
aa   2010-05-15 00:00:00.000 4.0000                                  37.00                                   -28.0000
aa   2010-05-17 00:00:00.000 12.0000                                 37.00                                   -16.0000
bb   2010-05-12 00:00:00.000 7.0000                                  8.00                                    1.0000
bb   2010-05-11 00:00:00.000 2.0000                                  8.00                                    -6.0000

(5  )


*/
loveyouaini1000
2009-10-26 14:27:37
LZ must be redesigned, which is what the table ah
suggestions:

material inventory table
amount of materials
aa 37
bb 8

amount of material purchase date table

aa 11 2010-05-11
bb 11 2010 - 05-11
aa 15 2010-05-11

amount of material ordering table
date
aa 4 2010-05 -11
bb 5 2010-05-11
aa 12 2010-05-11

material inventory table records the current number, according to the stock tables and a library table subtraction
tables and inventory tables from order to check for the missing amount
ILoveShark
2009-10-26 14:31:15

create table #test(  varchar(10),  datetime,    decimal(19,4),  decimal(19,2))
insert #test select 'aa','2010-05-11',5.0000, 37.00 
insert #test select 'aa','2010-05-15',4.0000, 37.00
insert #test select 'aa','2010-05-17',12.0000, 37.00

insert #test select 'bb','2010-05-12',7.0000, 8
insert #test select 'bb','2010-05-11',2.0000, 8


select *
,(select sum( ) from #test where a. =   and a. >= )- 
from #test a

                                                                                                          
---------- ----------------------- --------------------------------------- --------------------------------------- ---------------------------------------
aa         2010-05-11 00:00:00.000 5.0000                                  37.00                                   -32.0000
aa         2010-05-15 00:00:00.000 4.0000                                  37.00                                   -28.0000
aa         2010-05-17 00:00:00.000 12.0000                                 37.00                                   -16.0000
bb         2010-05-12 00:00:00.000 7.0000                                  8.00                                    1.0000
bb         2010-05-11 00:00:00.000 2.0000                                  8.00                                    -6.0000

(5  )
egc88
2009-10-26 14:35:55
table design sucks