| |

VerySource

 Forgot password?
 Register
Search
View: 865|Reply: 3

How to use sql to generate monthly reports based on journals

[Copy link]

1

Threads

3

Posts

3.00

Credits

Newbie

Rank: 1

Credits
3.00

 China

Post time: 2020-2-12 21:00:01
| Show all posts |Read mode
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
Reply

Use magic Report

0

Threads

49

Posts

35.00

Credits

Newbie

Rank: 1

Credits
35.00

 China

Post time: 2020-4-15 02:00:01
| Show all posts
--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!
Reply

Use magic Report

1

Threads

3

Posts

3.00

Credits

Newbie

Rank: 1

Credits
3.00

 China

 Author| Post time: 2020-5-6 10:00:01
| Show all posts
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?
Reply

Use magic Report

1

Threads

3

Posts

3.00

Credits

Newbie

Rank: 1

Credits
3.00

 China

 Author| Post time: 2020-5-6 18:30:02
| Show all posts
Can CONVERT be used in access?
Reply

Use magic Report

You have to log in before you can reply Login | Register

Points Rules

Contact us|Archive|Mobile|CopyRight © 2008-2023|verysource.com ( 京ICP备17048824号-1 )

Quick Reply To Top Return to the list