How to use sql to generate monthly reports based on journals

lgdbest

Journal style
Date Warehouse Goods Name In / Out Quantity (Negative Number of Outbound)

Generate report format for a certain year and month
Warehouse Goods Name Early Month Month Inbound Month Outbound Month Outbound

michealhenry

--Assuming to query the December 2016 report
declare @month varchar (6)
set @month = '201612'

select warehouse, goods name,
  The beginning of the month = (select sum (quantity) from running account where the name of the goods = a. The name of the goods and convert (varchar (6), date, 112) <@month),
Monthly storage = (select sum (case when quantity> 0 then quantity else 0 end)
          from running account
          where goods name = a. goods name and convert (varchar (6), date, 112) = @month),
Monthly release = (select sum (case when quantity <0 then-quantity else 0 end)
          from running account
          where goods name = a. goods name and convert (varchar (6), date, 112) = @month),
Monthly balance = (select sum (quantity) from running account where cargo name = a. Cargo name and convert (varchar (6), date, 112) <= @month)
from running account a


--Because the previous data can no longer be changed, it is recommended that the landlord export the data to another table
When inquiring directly, the speed of checking will be much faster!

lgdbest

Thank youmichealhenry
Yes in sql query analyzer, and how to get rid of the rows where the beginning of the month and the monthly storage are all 0
Also can it be used in the access database?

lgdbest

Can CONVERT be used in access?