Account Information System (Practical usage of Views Cursors and Temp Tables)
When we are working with any of the following domain projects mainly (Agriculture Sector or Banking Sector).We came to hear a word called Account Information System. So how to work with Account Information System. I want to discuss in this Article.And many programmers think why we need to use cursors if there are suitable alternatives are available.This article will highlight the importance of views and cursors in sql batch programming.
Day to Day in this competitive world to attract the Customers/Retailers .The distributors will keep certain schemes in which they assure you some gifts if the customer/Retailer purchase goods with them.This type of Account information system you will have mainly three types of conditions.
Credit : If you buy some goods from the Distributor You will have credited some points based on the Amount You purchased the Goods.
Debit : Once customer met certain Points and he wants a gift in order to avail that gift he has to redeemed some points and can have a gift.
Hold : once you redeemed your points. But the gift is not yet delivered. So inorder not to use those points by the customer for another gift.
So consider a situation I need to show my account information like this in the front end
The Grid information is as follows.. How to achieve this by Backend (Sql - server)?
Note:Thi s is for a particular Retailer
Date No. of Purchases Credit Hold Debit Total
12/02/2012 2 80 0 0 80
Inner grid
Show details of the Purchases…
18/02/2012 5 90 0 0 170
Inner grid
show details of purchases
22/02/2012 7 40 0 0 210
Show details of inner grid
24/02/2012 10 10 60 0 160
Show inner grid values
Here see there are so many posibilities in this scenario say I have only credit of the day, I can have only Debit of the day and I can have only Hold of the day. I can have credit and hold but not debit of the day,I have debit and hold but no credit of the day and I have credit and debit and no hold of the day. Debit and Hold should minus the previous total . And credit should add the total and they need to add as a cumulative manner(i.e. add to previous balance) and there is expiry date say season 1 dates are only added for season 1 they can not carry forward to season 2 and so on...those expiry dates are mentioned in season Table.
The Importance of Views and Cursors
Many people write articles for views and cursors. But no article will describe the Practical usage of views and cursors. I think the best way to use views and cursors are for these scenarios. Cursors are mainly used in sql-server because of fetching rows from views and thier ability to move through out the Records .
Consider a situation there is a view I want to retrive row by row data from the view but that view has no row_id column .So you can n't use while loop. The only easiest and fastest way you can use to retrive the row by row data is with cursors.
Now i have a Big transaction table.
I will explain how this big transaction table…
Credited Points are Directly taken from the transaction table
Debited Points(Gift redemption table) the Delivery status =4 the gift is said to be delivered
Hold Points the Delivery status = 6 the gift is not delivered
The Conditions will be there in Look up table …
Now the Programmer Logic….
Create a view can be done by manually through coding or either by Design approach.By draging and dropping the tables in the view is the best approach because we can join the relations of tables in a easiest way.
For Example if you want write through programatically.You can write like this
Basic structure of View
Create view [viewname]
create view [dbo].[OrganizationContactsView] with schemabinding as
(
select [FirstName], [LastName], [Contacts].[UserID], [Contacts].[ContactDescription], [Contacts].[IsPublic]
from [dbo].[Contacts]
inner join [dbo].[Users] on [Contacts].[UserID] = [Users].[UserId]
);
Indexed View
create unique clustered index IX_OrganizationContactsView_OrgId on [OrganizationContactsView](OrgId,UserId)
First Divide these three Conditions and put in different views.
1)Creditor View 2)Hold view 3) Debitor View.
Now place the Creditor view in one Main Temporary table select Contactid,seasonid, particulars as Particulars , mpoints as Credit,0 as Hold,0 as Debit,TransactionDate into #TEMPCreditorViewreds from CreditorView where Contactid=@Contactid group by Contactid,seasonid,Particulars,mpoints,TransactionDate
Now I have all the data which is there in the creditor view I will move in to one Main temp table.The view is filtered with Retailer code or by consumer code.
Now I want the Debit view data .But i can not loop the debit view because I do n't have row_id in that view as above mentioned the bestway to use cursors is this scenario.Further I declare a Cursor for this
declare @Contacids varchar(30),@Seasonids int,@Debits int,@TransactionDates varchar(30)
DECLARE Debit_cursor CURSOR FOR
SELECT Debit,Seasonid,ContactId,CreatedDate from DebitView WHERE ContactId=@Contactid
OPEN Debit_cursor
while @DebitViewCount >0
Begin
FETCH NEXT FROM Debit_cursor
INTO @Debits,@Seasonids,@Contacids,@TransactionDates
insert into #TEMPCreditorViewreds(SeasonId,Debit,Hold,Credit,ContactId,TransactionDate)
values(@Seasonids,@Debits,0,0,@Contactid,@TransactionDates)
set @DebitViewCount =@DebitViewCount - 1
End
CLOSE Debit_cursor
DEALLOCATE Debit_cursor
And so to Hold also......
Than I will create Maintemp table and create a column LINE ID with default value 0 and orderby date
Create Table #TempViewRecordset(Rowid int,lineid int default 0,TransactionDate varchar(40),Hold int,Debit int,Credit int, seasonid varchar(30), bal int default 0,particulars int)
Declare @UpdatedRowCount int
Declare @UpdatedRowid int
select @UpdatedRowCount =count(Rowid) from #TempViewRecordset
set @UpdatedRowid = 1
Declare @UpdateRowids int
DECLARE UpdateRow_cursor CURSOR FOR
SELECT Rowid from #TempViewRecordset
OPEN UpdateRow_cursor
while @UpdatedRowCount> 0
Begin
FETCH NEXT FROM UpdateRow_cursor INTO @UpdateRowids
update #TempViewRecordset set lineid = @UpdatedRowid where Rowid = @UpdateRowids
set @UpdatedRowid =@UpdatedRowid + 1
set @UpdatedRowCount=@UpdatedRowCount - 1
End
CLOSE UpdateRow_cursor
DEALLOCATE UpdateRow_cursor
I update all the LINEID using Cursors.Based on that i add their totals on cummulative basis.By Using a While loop inorder to retrive row by row data.As credit will add to the Previous total,Hold and debit will Minus the previous total according to season based.Below code illustrates that
declare @previousrow int
declare @Nextrow int
declare @countrows int
Declare @rowid as int
declare @balance int
declare @Holding as int
Declare @Crediting as int
Declare @Debiting as int
declare @Prevbalance int
declare @PrevHold as int
Declare @PrevCredit as int
Declare @PrevDebit as int
Declare @prevseasonid int
Declare @currseasonid int
Declare @flagbalance bit
declare @seasondummyids varchar(max)
set @flagbalance = 0
set @rowid = 1
Declare @FirstRow int
set @FirstRow = 1
select @countrows = count(*) from #TempViewRecordset
set @previousrow =@rowid
--Declare @prevseasonid int,@currseasonid int, @Crediting int,@Holding int,@Debiting int,@TransactionDatesHold varchar(30)
set @FirstRow = 1
select @countrows = count(*) from #TempViewRecordset
set @previousrow =@rowid
select top 1 @Holding = Hold,@Debiting=Debit,@Crediting =Credit from #TempViewRecordset
update #TempViewRecordset set bal= ISNULL(@Crediting,0) - (@Holding + @Debiting) where Rowid = @FirstRow
while (@rowid<= @countrows)
begin
set @previousrow =@rowid
set @Nextrow =@previousrow + 1
select @prevseasonid = seasonid from #TempViewRecordset where lineid = @previousrow
select @currseasonid = seasonid,@Crediting =Credit,@Holding=Hold,@Debiting =Debit from #TempViewRecordset where lineid = @Nextrow
if @flagbalance = 0 and @prevseasonid = @currseasonid
Begin
select @PrevCredit =Credit,@PrevHold=Hold,@PrevDebit =Debit,@Prevbalance=bal from #TempViewRecordset where lineid = @previousrow
update #TempViewRecordset set bal= ISNULL(@Crediting,0) - ISNULL(@Holding ,0) - ISNULL(@Debiting,0) + @Prevbalance where lineid = @Nextrow
print @Prevbalance
print @PrevDebit
End
if @prevseasonid != @currseasonid
begin
--update #TempViewRecordset set bal= ISNULL(@Crediting,0)- ISNULL(@Holding,0) + ISNULL(@Debiting,0) where lineid = @Nextrow
update #TempViewRecordset set bal= ISNULL(@Crediting,0)- ISNULL(@Holding,0) where lineid = @Nextrow
set @flagbalance = 0
End
set @rowid =@rowid + 1
End
Points to Remember when using Views
Whenever you insert a data in a database table that data may not automatically reflect into your view.so what you do is hit the Execute button which is on the top of the view and then you will see the expected output.But everytime you can n't manually do that especially when views are use in sql batch programming for this you can use this below statement before fetching data from the view exec sp_refreshview titleview
Follow