Create table tb (

ID int identifies the primary key,

Item varchar (10),-product number

Quantityint,-number of transactions

Flagbit,-transaction sign, 1 stands for receipt, and 0 stands for delivery, which can effectively distinguish returns (negative numbers).

Date Date Time)-Transaction Date

Insert tb SELECT 'aa', 100, 1,' 2005- 1- 1'

UNION ALL SELECT 'aa '，90， 1，' 2005-2- 1 '

UNION ALL SELECT 'aa '，55，0，' 2005-2- 1 '

UNION ALL SELECT 'aa '，- 10， 1，' 2005-2-2 '

Combine all options' aa', -5, 0,' 2005-2-3'

UNION ALL SELECT 'aa '，200， 1，' 2005-2-2 '

UNION ALL SELECT 'aa '，90， 1，' 2005-2- 1 '

UNION ALL SELECT 'bb '，95， 1，' 2005-2-2 '

Union Election' bb', 65, 0,' 2005-2-3'

UNION ALL SELECT 'bb '，- 15， 1，' 2005-2-5 '

UNION ALL SELECT 'bb '，-20，0，' 2005-2-5 '

UNION ALL SELECT 'bb '， 100， 1，' 2005-2-7 '

UNION ALL SELECT' cc', 100, 1,' 2005- 1-7'

go to

-Query time period definition

Declare @dt 1 date time, @dt2 date time.

Select @dt 1='2005-2- 1', @dt2='2005-2- 10'

-Inquiry

-Count the amount of data that is not available in the time period (delete the query if it is not needed)

Select the project,

Date=CONVERT(char( 10)，@dt 1， 120)，

Beginning = sum (when Flag= 1, the quantity is otherwise-the quantity ends),

[IN]=0，

[IN_Retrun]=0，

[OUT]=0，

[OUT_Return]=0，

Balance = sum (when Flag= 1, the quantity is otherwise-the quantity ends)

From tb a

Where the date & lt@dt 1 does not exist (

SELECT * FROM tb where item = a. Item and date & gt@dt 1 and date & ltDATEADD(Day, 1, @dt2)).

Group by project

Joint ownership

-Transaction data for a specific period of time.

Select the project,

Date=CONVERT(char( 10)，Date， 120)，

Opening=ISNULL (select SUM (when Flag= 1, the quantity is ELSE- the quantity ends).

Starting from tb, where item = a. Item and date < minutes (date)), 0),

[IN]= is null(SUM(Flag = 1 and Quantity & gt0 ends), 0),

[in _ retry] = is null (sum (flag =1and Quantity & lt0 THEN- end of quantity), 0),

[OUT]=ISNULL(SUM (when Flag=0 and Quantity & gt0 ends), 0),

[OUT _ Return]= is null(SUM(Flag = 0 and Quantity & lt0 THEN- end of quantity), 0),

Balance=ISNULL (select SUM (if Flag= 1, the quantity is ELSE- the quantity ends).

Starting from tb, where item = a. Item and date < = max (date), 0)

From tb a

Where date & gt=@dt 1 and date & ltDATEADD(Day, 1, @dt2).

GROUP BY CONVERT (character (10), date, 120), project

Sort by project and date

/*-Result

IN _ Retrun OUT OUT _ Return balance at the beginning of project date

- - - - - - - -

aa 2005-02-0 1 100 180 55 0225

aa 2005-02-02 225 200 10 0 4 15

aa 2005-02-03 465438

bb 2005-02-02 095 00 95

bb 2005-02-03

30 0 15 0 20 35

35 100 0 135

cc 2005-02-0 1 100 0 0 0 100

- */

Itjob wishes you success.