Taking the closing balance as the opening balance of the follwing month

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

I want to make the closing balance of the current month lets say of March to be the opening balance of April in sql or crystal reports how to i go about it. Help need the solution agently please. the script below is the one i have used to get the balance:

SQL

select StockNumber,InventoryName,Year1,Month1,RECEIVE_FROM_PO,RECEIVE_XFER,RECEIVE_ADJ,
_RETURN,ADJUSTMENT,ISSUE,PT_ISSUE,TRANSFER, SUM(ABS(RECEIVE_FROM_PO + RECEIVE_XFER + RECEIVE_ADJ + _RETURN))
- SUM(ABS(ADJUSTMENT + ISSUE + PT_ISSUE + TRANSFER))
 as BALANCE, BALANCE as OPENING_BAL
into final2
from dbo.drug_summary$
GROUP BY StockNumber,InventoryName,Year1,Month1,RECEIVE_FROM_PO,RECEIVE_XFER,RECEIVE_ADJ,
_RETURN,ADJUSTMENT,ISSUE,PT_ISSUE,TRANSFER,BALANCE

Posted 9-Dec-12 19:12pm

vusamozi

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Aarti Meswania · Accepted Answer · 2012-12-09T20:03:00

Solution 1

example,
A table inv [Inventory]
----

VouNo int
VouDt datetime
InvId int
Qty Numeric(12,2)   [+ if inward... - if Outward]

so,
Query is like below

SQL

select VouDt, InvId, 
(select sum(Qty) from inv where VouDt< i.VouDt) as OpnQty,
sum(Qty) as Qty,
(select sum(Qty) from inv where VouDt< i.VouDt)- sum(Qty) as ClsQty
from Inv as i
where voudt >= '2012-04-01' and vouDt<='2012-04-30' -- filter date here it's for april-2012
group by InvId,VouDt

Happy Coding!
:)

Posted 9-Dec-12 20:03pm

Aarti Meswania

Comments

vusamozi 10-Dec-12 9:26am

thanx Aarti but the script is giving me errors and i have dibugged some and this error seems to pop up:- Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '-'.

below is the code and the table:

USE [centraldb]
GO
/****** Object: Table [dbo].[Sheet2$] Script Date: 12/10/2012 16:16:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Sheet2$](
[StockNumber] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Description] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[InventoryName] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Mth/Yr] [datetime] NULL,
[ADJUSTMENT] [float] NULL,
[ISSUE] [float] NULL,
[PT_ISSUE] [float] NULL,
[RECEIVE_ADJ] [float] NULL,
[RECEIVE_FROM_PO] [float] NULL,
[RECEIVE_XFER] [float] NULL,
[_RETURN] [float] NULL,
[TRANSFER] [float] NULL
) ON [PRIMARY]

SELECT StockNumber,InventoryName,Mth/Yr,RECEIVE_FROM_PO,RECEIVE_XFER,RECEIVE_ADJ,_RETURN,ADJUSTMENT,ISSUE,PT_ISSUE,TRANSFER,
(SELECT SUM(RECEIVE_FROM_PO + RECEIVE_XFER + RECEIVE_ADJ + _RETURN + CLOSING_BAL) from dbo.Sheet2$ where Mth/Yr < i.Mth/Yr)
- SUM(ADJUSTMENT + ISSUE + PT_ISSUE + TRANSFER) FROM Sheet2$ AS OPENING_BAL,
(SELECT SUM(ADJUSTMENT + ISSUE + PT_ISSUE + TRANSFER) from dbo.Sheet2$ where Mth/Yr < i.Mth/Yr) - SUM(ADJUSTMENT + ISSUE + PT_ISSUE + TRANSFER)
AS CLOSING_BAL FROM dbo.Sheet2$ AS i
WHERE Mth/Yr >= '2004-01-01' AND Mth/Yr <= '2012-12-31'
GROUP BY Mth/Yr

please help agently

Aarti Meswania 10-Dec-12 10:28am

you wrote it wrong
it should be like this,

select StockNumber, [Description], InventoryName, [Mth/Yr],Opn_Qty,Qty,Opn_Qty-Qty as Cls_Qty
from
(

select
StockNumber, [Description], InventoryName, [Mth/Yr],

(
SUM(RECEIVE_FROM_PO + RECEIVE_XFER + RECEIVE_ADJ + _RETURN)
-SUM(ADJUSTMENT + ISSUE + PT_ISSUE + [TRANSFER])
) as Qty,

(
SELECT SUM(RECEIVE_FROM_PO + RECEIVE_XFER + RECEIVE_ADJ + _RETURN)
-SUM(ADJUSTMENT + ISSUE + PT_ISSUE + [TRANSFER])
from dbo.Sheet2$ where [Mth/Yr] < i.[Mth/Yr]
)
AS Opn_Qty

from a.dbo.sheet2$ as i

WHERE [Mth/Yr] >= '2004-01-01' AND [Mth/Yr] <= '2012-12-31'
GROUP BY StockNumber, [Description], InventoryName, [Mth/Yr]

)
as a

vusamozi 11-Dec-12 9:24am

thank you Aarti i have used the script and its working just fine but i have issues with the null values it brings after running the script, yet they is a qty value.

vusamozi 11-Dec-12 9:24am

thank you Aarti i have used the script and its working just fine but i have issues with the null values it brings after running the script, yet they is a qty value.

Aarti Meswania 11-Dec-12 20:47pm

okay
use isnull function

change like below,

SUM( Isnull(RECEIVE_FROM_PO,0) + Isnull(RECEIVE_XFER,0) + Isnull(RECEIVE_ADJ,0)...

this way put all qty fields in isnull function then test

Happy Coding!
:)

Glad to help you!
:)

vusamozi 12-Dec-12 1:08am

Morning Aarti, i get this error when i use the isnull function:- Msg 174, Level 15, State 1, Line 12
The isnull function requires 2 argument(s).

And the script is picking the same figure closing quantity for all months as opening qty. if you can help to make the closing qty for 2004/01/01 to be the opening qty for 2004/02/01 and be added as this :: SUM(RECEIVE_FROM_PO + RECEIVE_XFER + RECEIVE_ADJ + _RETURN + closing qty)
-SUM(ADJUSTMENT + ISSUE + PT_ISSUE + [TRANSFER]) closing qty of february 2004

vusamozi 17-Dec-12 3:46am

select StockNumber, [Description], InventoryName, [Mth/Yr],Opn_Qty,Qty,Opn_Qty-Qty as Cls_Qty from ( select StockNumber, [Description], InventoryName, [Mth/Yr], ( SUM(RECEIVE_FROM_PO + RECEIVE_XFER + RECEIVE_ADJ + _RETURN) -SUM(ADJUSTMENT + ISSUE + PT_ISSUE + [TRANSFER]) ) as Qty, ( SELECT SUM(RECEIVE_FROM_PO + RECEIVE_XFER + RECEIVE_ADJ + _RETURN) -SUM(ADJUSTMENT + ISSUE + PT_ISSUE + [TRANSFER]) from dbo.Sheet2$ where [Mth/Yr] < i.[Mth/Yr] ) AS Opn_Qty from a.dbo.sheet2$ as i WHERE [Mth/Yr] >= '2004-01-01' AND [Mth/Yr] <= '2012-12-31' GROUP BY StockNumber, [Description], InventoryName, [Mth/Yr] ) as a

am still having a problem Aarti when i run the script it say Invalid object name 'a.dbo.sheet2$'.

Aarti Meswania 17-Dec-12 3:52am

replace
a.dbo.sheet2$
with
dbo.[sheet2$]

irfanansari 10-Mar-16 6:51am

VouDt< i.VouDt can you explain about it what is i here working ?

irfanansari 26-Sep-19 8:38am

2012-04-01 00:00:00.000 62 NULL -1.00 NULL
2012-04-02 00:00:00.000 99 -1.00 1.00 -2.00
2012-04-03 00:00:00.000 8 0.00 1.00 -1.00
2012-04-04 00:00:00.000 8 1.00 1.00 0.00
first value openbalance is null is is good but sloe null why and then 02-04-2012 open balance -1 is ok but closing -2 why

Aarti Meswania 27-Sep-19 2:24am

can you please post your sql query?

irfanansari 27-Sep-19 7:49am

select VouDt, InvId,
(select sum(Qty) from inv where VouDt< i.VouDt) as OpnQty,
sum(Qty) as Qty,
(select sum(Qty) from inv where VouDt< i.VouDt)- sum(Qty) as ClsQty
from Inv as i
where voudt >= '2012-04-01' and vouDt<='2012-04-30' -- filter date here it's for april-2012
group by InvId,VouDt

Aarti Meswania 30-Sep-19 5:21am

there are 3 columns here for quantity, OpenQty, Qty (which means consumed), closingQty.

if opening Quantity is -1 (A thing(mobile) was already borrowed)
consumed quantity is 1, it's another A thing(mobile) you are borrowing so it's having -ve sign (-1)
so closing qty is "opening Quantity" - "consumed quantity" = "closing quantity"
so -1-1=-2 (you have borrowed 2 things(mobiles))
hope that helps, actually logic is easy if you have a stock and qty is consumed from that, but here it's case where you do not have stock and to serve customer you borrow new mobile from sister firm, sale it and then later you gonna return another new mobile to the sister firm when you bring new stock.

irfanansari 30-Sep-19 5:58am

sir ok i have a -1 as closing date 2012-04-01 ok and opening balance null ok understandable
then 201-04-02 become last closing balance -1 will be opening -1 it is ok and qty 1
then how it is closing balance -2 it can be 0 or null as closing because -1+1=0 as closing balance
2012-04-03 opening balance is OK 0 and closing -1 why if qty 1
opeing is 0 and qty 1 then closing must be 1 but on 2012-04-03 closing -1 ??????

irfanansari 30-Sep-19 5:58am

sir ok i have a -1 as closing date 2012-04-01 ok and opening balance null ok understandable
then 201-04-02 become last closing balance -1 will be opening -1 it is ok and qty 1
then how it is closing balance -2 it can be 0 or null as closing because -1+1=0 as closing balance
2012-04-03 opening balance is OK 0 and closing -1 why if qty 1
opeing is 0 and qty 1 then closing must be 1 but on 2012-04-03 closing -1 ??????

Aarti Meswania 30-Sep-19 6:01am

vusamozi asked question in which stock is considered.
and when you sell something qty reduce not add up.

irfanansari 30-Sep-19 7:21am

yes sir thanks a lot sir i have a use full query which i got thanks a lot for helping me

select sum(case when ddate < '2019-09-02'
then credit end) -
sum(case when ddate < '2019-09-02'
then debit end)[Opening Balance],
sum(case when ddate <= '2019-09-03'
then credit end) -
sum(case when ddate <= '2019-09-03'
then debit end)[Closing Balance] from tt

irfanansari 30-Sep-19 7:30am

sir thanks a lot just please can you help tell what is i here mean and work its for learning for me thanks

(select sum(Qty) from inv where VouDt< i.VouDt) as OpnQty

i.VouDt what is mean of i here ?

Aarti Meswania 30-Sep-19 9:46am

Irfan, first of all I am a female :) you can say aarti instead of sir.
so....
here, (select sum(Qty) from inv where VouDt< i.VouDt) as OpnQty
means collecting qty field values which falls into less than running date and sum up qty to retrieve opening qty. for running date.
you can learn subquery for further details

irfanansari 30-Sep-19 10:32am

i am so sorry aarti you are best one who teaching me a lot you are first one who helping me a lot really i am deep of heart thanks full of you i am new learner and actually i am creating a website ERP base application in asp.net i am not big expert first application website i am making by helping bootstrap css and vb.net asp.net. Aarti this is my first experience any person helped me continuously reply me all my question i am again thanks full for you i am From Pakistan I Pray For You Allah Give You Success In Your Life
Aarti if you believe me and if you not mind then would you like to give me your any contact as i personally take help from you please
thanks a lot

Aarti Meswania 1-Oct-19 3:26am

Glad to help you through this platform. However, I can't exchange the contact information, irfan. also I am not too much sharp in ASP.net.
Codeproject members are active n helpful to each other. you can post your queries here on codeproject and people who have knowledge in relevant topic would help you :)