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:
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,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