Want To Load Balance And Cluster 3 SQL 2005 Servers

Oct 9, 2007

Currently we have a single SQL server. It went down and the higher ups were none to happy. Lots of money lost, down time, unhappy customers....the whole nine yards. They want to throw all sorts of money at the problem and want a solution that is high availability and provides load balancing. I think I came up with a solution.

3x Windows 2003 Enterprise Edition servers running SQL 2005 Standard in a cluster all connected to a SAN.

I'm guessing I need to set up Network Load Balancing in order to load balance the SQL database. What we would love to have happen is if one of the servers goes down, everything else just picks up. That coupled with our database that is increasing in size and transactions gets load balanced.

Am I going down the right path? Something else I should look at? Or that I am missing?

Thanks!

View 5 Replies


ADVERTISEMENT

Want To Load Balance And Cluster 3 SQL 2005 Servers

Oct 9, 2007

Currently we have a single SQL server. It went down and the higher ups were none to happy. Lots of money lost, down time, unhappy customers....the whole nine yards. They want to throw all sorts of money at the problem and want a solution that is high availability and provides load balancing. I think I came up with a solution.

3x Windows 2003 Enterprise Edition servers running SQL 2005 Standard in a cluster all connected to a SAN.

I'm guessing I need to set up Network Load Balancing in order to load balance the SQL database. What we would love to have happen is if one of the servers goes down, everything else just picks up. That coupled with our database that is increasing in size and transactions gets load balanced.

Am I going down the right path? Something else I should look at? Or that I am missing?

Thanks and sorry for posting this in multiple places!

View 3 Replies View Related

Load Balance Clustering On 2005?

Jul 8, 2005

My company is planning on using a datacenter for our customers.  These are manufacturing plants from which we collect batch and trend data (currently stored in SQL Server)

View 6 Replies View Related

SQL Clustering With Load Balance

Apr 18, 2006

For our application we are planning 4 Appserver/webservers with load balancing and 4 SQL servers in cluster with active/active setup. We are looking for some info on the active/active setup for SQL server 2000 Clustering.

1. Which cluster node replication method (2n, 2n +p or n+1) we should go for.
2. How operating system "windows 2003 server" should be configured and its role in failover.
3. How active/active failover setup for SQL Server 2000 really works and how much data loss happens in case of failover.
4. For web applications, how the session state is managed in SQL Clustering

View 4 Replies View Related

Clustering, Security, Performance, Load Balance

Nov 27, 2007

I think this question has been asked number of times. However, I amlooking for some specific information. Perhaps some of you can helpclose the gap. Or perhaps you can point me towards right direction.Perhaps this group can help me fill in ms-sqlserver related followingquestions.1. Do this database have data Clustering capabilities?1a. If yes, what mechanism is used such as shared disk, share nothing,etc.2. Do these dB have Security features?2a. If yes, what security features are supported? For instance do theysupport encryption or SSL connection?3. How does the database perform and what is the criteria for theperformance matrix?4. Do they have inbuilt load balance capabilities?I want to thank everyone for taking your time to read thiscorrespondence. I will also greatly appreciate your efforts in sharingyour thoughts.Regards,Manish

View 3 Replies View Related

SQL 2005 - Shared Load Cluster

Apr 26, 2006

Hi

This is more a product featuer question which I cant seem to find the answer to

If we wanted to share the loads of SQL Server requests over say 2 or 4 servers, is this possible?

What I mean is basically Active/Active load balancing / clustering? So we have one Virtual IP and all requests are forwarded to the least busy server.....

J

View 1 Replies View Related

How Can I Cluster Sql 2005 For Load Balancing?

Nov 6, 2006

i have a table with 10,000,000,000 records and i need Select and Insert many
records from or into this table in less than one second.
i can't buy a very expensive hardware(Server) for this SQL Server 2005
but i can buy many medium price hardwares(Servers) for this SQL Server
2005.
how can i distribute or cluster this table between many hardwares(Servers)?
note: i have few users (maximum 5 users) for my database but i have a
very large table and Sql server 2005 server need to respond to this
users in less than 1 second.
i want to distribute this huge table in seperated hardwares. becuase i
can't buy a very expensive hardware from my server but i can buy many
medium price hardware for my server.
note: i need this: when a user run a select query on this huge table
his/her request distribute between many hardwares not one hardware.

View 3 Replies View Related

SQL 2005 Reporting Services On Load Balanced Web Servers

Feb 27, 2007

Is the any issues running SQL 2005 Reporting Services on Load balanced web servers?

View 1 Replies View Related

SQL Server 2005 64bit SP2 Installation Issue On Cluster Servers.

Nov 23, 2007

Hi All,

We have been experiencing issues with SQL Server 2005 SP2 upgrade on cluster servers.
After running the setup, it gives failure message for Database services.

Installing SP2 on 64bit cluster server we get error regarding "passive node not patched properly or run the setup from passive node". However, select serverproperty(€˜productlevel€™) returns the output as "SP2". On more digging we found from SQL Server Error log, that the resourcedb of that server is of level 9.0.0.3199

€œThe resource database build version is 9.00.1399. This is an informational message only. No user action is required.€?

However if we do the SQL Server 2005 SP2 upgrade on standalone machines with the same software repository, it upgrades successfully and also resourcedb upgraded to 3042 version. So if we look at the errorlog of a standalone machine it gives following message.

"The resource database build version is 9.00.3042. This is an informational message only. No user action is required."

I again reiterate that, this is the issue we have faced on 64bit SQL Server 2005 SP2 upgrade only on cluster SQL Servers, however on standalone machine it works fine and 32bit SP2 installation works fine.

Please let us know the solution for this.

regards
Jay Mehta

View 2 Replies View Related

T-SQL (SS2K8) :: Update Row Beginning Balance With Previous Row Trial Balance

Aug 1, 2014

I have a table with Million plus records. I have been able to calculate the Trial_Balance for all months.

Now I am trying to provide a Beginning Balance for all months and the Logic is the Beginning Balance of July would be the Trial_Balance of June. I thought I could just do a self Join but this is not working.

UPDATE dbo.TrialBalance
SET Beginning_Balance_Debit = B.Trial_Balance_Debit
FROM (SELECT DATEADD(month, -1, Calendar_Month) AS PrevCalMonth,Trial_Balance_Debit
FROM dbo.TrialBalance) AS B INNER JOIN dbo.TrialBalance A
ON b.PrevCalMonth=A.Calendar_Month

[Code] ....

View 5 Replies View Related

Load Balancing Cluster

Nov 30, 2007

need the concpet of load balancing cluster ?can any one help me out with any link....?

View 1 Replies View Related

SQL Express On Load Balanced Servers

Sep 21, 2006



Hi,

I have two 2003 servers which are load balanced - not for high demand but for resiliance. The application that is being run on both servers shares data on a NAS raid 5 device.

Can I install SQL Express on each of these servers and have them share the databases on the NAS device?

Help please anybody?

Paul

View 3 Replies View Related

[Q]SQL Server 2000 Cluster Load Balancing

Nov 8, 2001

Hey guys,

Lately we have come across a problem where our application is undergoing some extreme load against the SQL 2000 server database we have setup, where the server is hitting 100% CPU utilization each time. Currently the box is a 2 processor box.

Here is the question I have. I have seen under most SQL Server clusters that an active/passive setup is implemented. Where the passive server just exists as a failover mechanism. What I am looking for is some information on how to setup active/active setup where each server receives processes to handle.

Has anyone created a setup like this? Are there any standard benchmarking tools that can be used to see how this configuration increases performance? Is this setup more favorable than going to a 4 processor server as oppossed to our current 2 processor server?

BTW: We have noticed that after a web application where the user sits idle for a while the SQL Server application loses the connection with the application user...Is this the SQL Timeout causing the connection to disconnect?

Thanx for any response...

Bri

View 1 Replies View Related

SQL 2012 :: Load Balancing And Fail Over Cluster

Jun 13, 2014

Is there such a thing called 'Load balancing' on fail over cluster?

View 2 Replies View Related

Using Cluster Servers And Preallocated Space

Mar 30, 2007

Hi All,

I am doing some design work on a new system. The plan is to use clustered servers accessing the same backend instance. I want to prepoulate the table with our unique id (for say 5M rows for sake of discussion). Where I am puzzled is how do I guarantee unique ids to the application when both servers are looking for the same next available row? The sql could be something like

select min(id) from table where user_id is null

The trouble our current system runs into is that there are occasions where the users generate the same id which of course messes up the application.


Is this a case where a store procedure would do the insert and return the id to the user is a better solution?

Thanks

View 1 Replies View Related

MSCS SQL2000 Cluster 64 And 32 Bit Servers

Jul 23, 2005

Is it possible to create Fail-over Cluster with 64-bit (Itanium2) and 32-bit (Intel Xeon) nodes?

View 2 Replies View Related

Access Denied To Cluster Storage When Restoring 2005 Full Text DB To 2014 Cluster

Jun 10, 2015

I am in the process of moving databases from a SQL 2005 Standard version to a 2-node 2014 cluster.All of my 2005 databases back up successfully.They all restore without issue except for one database that has a full text catalog. I get this message

Msg 7610, Level 16, State 1, Line 2
Access is denied to "fileStoragedataMSSQLSERVERFullTextCatalog", or the path is invalid.
Msg 3156, Level 16, State 50, Line 2
File 'sysft_FTCatalog' cannot be restored to 'fileStoragedataMSSQLSERVERFullTextCatalog'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 2
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

[code]....

I went as far as giving the folder full access to everyone temporarily and received the same error.

View 1 Replies View Related

Upgrading Mulitple 7.0 Servers To 2000 Cluster Server

Jun 6, 2002

We shall be taking a bunch of 7.0 instances and moving/upgrading to a SQL 2000 cluster server. I was thinking of creating new named instances on the 2000 cluster and upgrading each 7.0 server to it's respective named instance. Also thought of using the 2000 copy database wizard; I was told this didn't always work. Anyone hear of problems with this?
Thanks

View 2 Replies View Related

Linking SQL 2005 Servers To SQL 2000 Servers Problems

Sep 27, 2007

I am in the middle of a major migraton project, moving from x86 SQL 2000 to IA64 SQL 2005. I have a business need to link to several legacy servers. I have a number of problems I am trying to solve.

1) Linking a Kerberos server to a non-Kerberos server.
2) Linking x64 or IA64 servers to x86 servers.
3) Linking SQL 2005 to SQL 2000.

Two of the errors I am encountering are:
------------------------------
TCP Provider: An existing connection was forcibly closed by the remote host.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
OLE DB provider "SQLNCLI" for linked server "SCDC250DB" returned message "Communication link failure".
(Microsoft SQL Server, Error: 10054)
------------------------------
And
------------------------------
The OLE DB provider "SQLNCLI" for the linked server "SCDC250DB" reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "SCDC250DB".
OLE DB provider "SQLCLI" for linked server "SCDC250DB" returned message "Invalid authorization specification".
(Microsoft SQL Server, Error: 7399)

If someone has worked through these problems before, I would appreciate it if you could direct me to the relevant documentation to resolve these issues.

Thanks!


Brandon Forest

Database Administrator

Data & Web Services Team

Sutter Connect Information Technologyforesb@sutterhealth.org

View 2 Replies View Related

SQL 2012 :: Does Secondary Replicas In AlwaysOn Have To Reside In Windows Cluster Servers

Sep 18, 2014

I know now that AlwaysOn feature HAS to be installed/configured on a Windows Clustering environment, BUT the secondary replicas, like the Disaster recovery replica residing in a different Data Center HAS to be also in a Windows Clustering environment or can it reside on a SINGLE SQL Server INSTANCE?.

View 6 Replies View Related

How To Upgrade SQL Server 2000 Cluster To SQL Server 2005 Cluster(Database)

May 8, 2007

Hi



We are planning to upgrade the SQL Server in our production environment from SQL Server 2000 to SQL Server 2005. This is a 4 Node cluster environment with 3 Databases on 3 Virtual instances. The main requirement is to achieve this with no/minimal downtime.



Could you please suggest or direct me to any documentation for the best practices used to upgrade such an environment?



Thanks

Priyanka

View 2 Replies View Related

Upgrading A SQL Server 2000 Cluster To A SQL Server 2005 Cluster

Dec 28, 2007



We're upgrading a SQL Server 2000 cluster (Active/Passive) running on Windows 2000 Server to a SQL Server 2005 Cluster running on Windows Server 2003. We can't purchase new hardware and we have no spare hardware. We also need to move from Windows 2000 Server to Windows 2003 Server at the same time. We want to keep downtime to a bare minimum.

What we were thinking was the following steps... Anyone try this?

1. Break the link between the servers.

2. Install a fresh copy of windows 2003 server on one side along with SQL Server 2005. While this step is running, the active node would still be live on Windows 2000 Server and SQL Server 2000 serving our customers.

3. Restore a copy of a backup from the active production side to the node we're upgrading and at that point we would bring the active node down, switching the active node to be the newly upgraded server.

4. As a final step, the old active node would now have the link to it broken, we would install a fresh copy of windows 2003 server on it and sql server 2005. At this point we would bring it back into the cluster and the cluster would be complete again.

Thoughts?

View 2 Replies View Related

Upgrading SQL Server 2000 Cluster To SQL Server 2005 Cluster

May 14, 2008



Friends -

Need your help and guidence for doing upgrading SQL Server 2000 Cluster to SQL Server 2005 Cluster.

Let me explain my current environment.

1. Currently SQL Server 2000 Cluster environment is running on Windows 2000 Server we need to upgrade this to SQL Server 2005 on Windows 2003 Server. >>> Production environment.


My Plans:

1. On Testing Environment Install SQL Server 2000 cluster on Windows 2003 Server and do a restore of databases from the produciton environment.

2. Upgrade In-Place from SQL Server 2000 Cluster to SQL Server 2005 Cluster.

My doubts

1. Can i install SQL Server 2000 Cluster on Windows 2003 Server. Is it possible or not.

Please advise me and correct my steps.

Cheers
VSH

View 1 Replies View Related

Load Backup From 2000 To 2005 Or Recreate Structure On 2005?

Apr 23, 2008

Hello,

I would like to ask you if there is better to recreate database structure on 2005 from 2000 and move data or to just load
2000 backup.

Currently I loaded the backup, but I am wondering if there might be slightly better performance on 2005 when recreating structure on 2005 to loading 2000 backup?

Does loading 2000 backup create 2005 binary structure?

Thanks for help in advance

View 5 Replies View Related

Update Balance

Jul 25, 2006

I am trying to write a trigger which updates the customers balance when an invoice is deleted. The tables are created and I can post if needed, but I get a compilation error when I use the follow code.

create or replace trigger trg_updatecustbalance2
after delete on invoice
for each row
begin
update customer
set cust_balance = cust_balance - inv_amount
where customer.cust_num = invoice.cust_num;
end;
/

Can anyone help me figure out where my error?

View 8 Replies View Related

Quantity Balance And Sum

Aug 14, 2007

I have following table structure


CREATE TABLE [dbo].[EMPLOYEE] (
[REC_ID] [int] IDENTITY (1, 1) NOT NULL ,
[EMP_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[NAME] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PROJECT_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LOGIN_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO



CREATE TABLE [dbo].[PPE_STOCK] (
[REC_ID] [int] IDENTITY (1, 1) NOT NULL ,
[PPE_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PPE_NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[QUANTITY] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PPE_ITEM_ISSUE] (
[PII_ID] [int] IDENTITY (1, 1) NOT NULL ,
[EMP_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DATE_REQUEST] [datetime] NULL ,
[QUANTITY_ISSUE] [int] NULL ,
[DATE_ISSUE] [datetime] NULL
) ON [PRIMARY]
GO


I want to calcalate the balance quantity avaiable in the stock.

PPE_STOCK => STOCK OF QUANTITY TABLE
PPE_ITEM_ISSUE => STOCK QUANTITY ISSUE TO EMPLOYEE TABLE

query retrieve like this.

Name....EMP_ID....PPE_NAME,..QUANTITY_ISSUE.Stock Qty.Balance Qty
-----------------------------------------------------------------
Martin..p0012456..Safety Gloves 1 .......100........99
Martin..p0012566..Safety Glass 1 .......100........99
Peter...p00123456.Safety Gloves 1........100........98
Jone....p00987654.Safety Helmet 1........100........99
Khan....p0012122..Safety Helmet 1........100........98
....

stock quanity minus the issue qauantity and retreive balance QTY
how ?

How can calcuate / retrieve the balance quantity by the query ?

Please us query calcuation.

regards
Martin

View 3 Replies View Related

Balance Off 2 Records?

Feb 20, 2008

dear all experts please kindly provide ur help thanks.



suppose i hv 2 records in a table as follow:





customer_id status qty date

123456 install 24 2008-02-20

123456 disconnect -24 2008-02-20







how can i exclude these 2 records from my table? actually you can see the net gain is 0, but in my report reflects install qty is 24 and disconnect qty is -24. i dont want these 2 records reflect in my report, how can i achieve such result? thanks in advance!



View 8 Replies View Related

How To Balance The Database

Jan 30, 2007

Hello,

I started to use data mining sql server recently, and I have a little problem:

I'm trying to predict a boolean variable, but my database has only 1/4 of true results, because of that my predict probability is a little bit low in this kind of cases.

My question is: Is there a way of increasing the importance of a database entry in order to avoid editing a database with 40000 entries?

Thank you very much.

View 3 Replies View Related

Step By Step For Upgrading SQL Server 2000 Cluster To SQL Server 2005 Cluster

May 15, 2008



Friends -

Could any one of you provide steps for upgrading SQL Server 2000 cluster to SQL server 2005 cluster.

My environment is Windows 2003 server.

Appreciate your support.

Cheers
VSH

View 1 Replies View Related

Balance Sheet Calculation

Apr 28, 2008

Hello Friends,

I have a doubt regarding balance sheet calculation in my Software. I am using a stored procedure for these calculations. I have some commands for calculation as given below:

/******************************************************************/
DECLARE @car_req FLOAT
DECLARE @amt_gen FLOAT
DECLARE @amt_shp FLOAT
DECLARE @amt_sho FLOAT
DECLARE @bal_gen FLOAT
DECLARE @bal_shp FLOAT
DECLARE @bal_sho FLOAT
DECLARE @tab_id INT

CREATE TABLE #Temp
(
rec_id INT IDENTITY NOT NULL
,cargo_requiredFLOAT
,amount_generalFLOAT
, amount_shipFLOAT
, amount_shoreFLOAT
,balance_generalFLOAT
, balance_shipFLOAT
, balance_shoreFLOAT
)

INSERT INTO #Temp (cargo_required, amount_general, amount_ship, amount_shore)
SELECT 5000, 1500, 1450, 1490
UNION ALL
SELECT 0, 3500, 3500, 3500
UNION ALL
SELECT 7000, 4500, 4550, 4560
UNION ALL
SELECT 0, 2500, 2000, 2000


DECLARE temp_cursor CURSOR FAST_FORWARD FOR
SELECT rec_id, cargo_required, amount_general, amount_ship, amount_shore
FROM #Temp

OPEN temp_cursor

FETCH NEXT FROM temp_cursor
INTO @tab_id, @car_req, @amt_gen, @amt_shp, @amt_sho

WHILE @@FETCH_STATUS = 0
BEGIN
IF (@car_req > 0)
BEGIN
UPDATE #Temp
SET balance_general = @car_req - amount_general
,balance_ship= @car_req - amount_ship
,balance_shore= @car_req - amount_shore
WHERE rec_id = @tab_id
END
ELSE
BEGIN
UPDATE #Temp
SET balance_general = @bal_gen - amount_general
,balance_ship= @bal_shp - amount_ship
,balance_shore= @bal_sho - amount_shore
WHERE rec_id = @tab_id
END

SELECT @bal_gen = balance_general, @bal_shp = balance_ship, @bal_sho = balance_shore
FROM #Temp
WHERE rec_id = @tab_id

FETCH NEXT FROM temp_cursor
INTO @tab_id, @car_req, @amt_gen, @amt_shp, @amt_sho
END

SELECT * FROM #Temp

CLOSE temp_cursor
DEALLOCATE temp_cursor

DROP TABLE #Temp

/***************************************************************/

This is what acutally I am having in my stored procedure except the temporary table. The values which I am inserting here will get from my actual tables. What I need is to calculate balance. If you run this script you can see how the balance is calculating using the cursor.

I want an efficient way to calculate the same. I know cursor is not at all good option in stored procedures. Do you have any Idea to make this script in an efficient and simple way?

Thanks and Regards
Boney

View 2 Replies View Related

Running Balance Calculation - PLEASE HELP

Jun 23, 2008

Hi guys, I've really been struggling with this issue for quite a while and the solution still elludes me.
If anyone can help me I will worship you as a god for ever! [Wink]

The Scenario is as follows:
I have a table where several documents are linked to each other via a foreign key called ReconNum.
Basically this table is used to link Invoices, Credit Notes and Payments to each other.
Thus you get the following layout:

ReconNum LineID DocID DocType ReconAmount
111 0 101 Payment 20 000
111 1 202 Credit Note 12 0000
111 2 303 Payment 5500
111 3 404 Invoice 10 000
111 4 505 Credit Note 22500
111 5 606 Invoice 30 000
111 6 607 Invoice 20 000

What I need to do is assign values from each of the Payment/Credit Note documents to the Invoices. Thus getting the following result:

Inv# DocID DocType AmountApplied DocBal InvBal
404 101 Payment 10 000 10 000 0
606 101 Payment 10 000 0 20 000
606 202 Credit Note 12 000 0 8 000
606 303 Payment 5500 0 2 500
606 505 Credit Note 2500 20 000 0
607 505 Credit Note 20 000 0 0

I've come close, but it seems to be the old chicken or the egg problem.i.e. I need one field to calculate the other and vica-versa.
(Amount Applied, DocBal and Inv Bal are all dependant on each other)

NOTE: The only field I'm really interested in is the AmountApplied field, but amount applied depends on the DocBal and InvBal fields.

I need a running balance on both the Amount Due on the Invoice, and the Amount Availble on the document being assigned to the invoices. The problem is that I'm trhowing the data into a temporary table and cant use fields in the temp table for my calculation.
i.e. AppliedSum = if (Doc.Available amount on Doc < Invoice Balance) then Doc.Available Amount
elseif (Doc.Available amount on Doc > Invoice Balance) then Invoice Balance

Invoice Balance = Invoice Tot - sum(Applied Amounts)

Any help would be breatly appreciated!

View 15 Replies View Related

Balance Sheet Calculation

Apr 28, 2008

Hello Friends,

I have a doubt regarding balance sheet calculation in my Software. I am using a stored procedure for these calculations. I have some commands for calculation as given below:DECLARE @bal_gen FLOAT
DECLARE @bal_shp FLOAT
DECLARE @bal_sho FLOAT
DECLARE @tab_id INT

CREATE TABLE #Temp
(
rec_id INT IDENTITY NOT NULL
, cargo_required FLOAT
, amount_general FLOAT
, amount_ship FLOAT
, amount_shore FLOAT
, balance_general FLOAT
, balance_ship FLOAT
, balance_shore FLOAT
)

INSERT INTO #Temp (cargo_required, amount_general, amount_ship, amount_shore)
SELECT 5000, 1500, 1450, 1490
UNION ALL
SELECT 0, 3500, 3500, 3500
UNION ALL
SELECT 7000, 4500, 4550, 4560
UNION ALL
SELECT 0, 2500, 2000, 2000



SELECT @bal_gen=0,@bal_shp=0,@bal_sho=0
SELECT @tab_id=MIN(rec_id)
FROM #Temp

WHILE @tab_id IS NOT NULL
BEGIN
UPDATE #Temp
SET @bal_gen=balance_general =CASE WHEN cargo_required > 0 THEN cargo_required ELSE @bal_gen END - amount_general
, @bal_shp=balance_ship = CASE WHEN cargo_required > 0 THEN cargo_required ELSE @bal_shp END - amount_ship
, @bal_sho=balance_shore = CASE WHEN cargo_required > 0 THEN cargo_required ELSE @bal_sho END - amount_shore
WHERE rec_id = @tab_id

SELECT @tab_id=MIN(rec_id)
FROM #Temp
WHERE rec_id >@tab_id
END

Select * FROM #Temp
This is what acutally I am having in my stored procedure except the temporary table. The values which I am inserting here will get from my actual tables. What I need is to calculate balance. If you run this script you can see how the balance is calculating using the WHILE loop.

I want an efficient way to calculate the same. Is there any way to calculate it without using loop methods.
Thanks and Regards
Boney

View 3 Replies View Related

T-SQL (SS2K8) :: Add Balance Of Previous Row To Current Row

Jul 2, 2014

I am novice to intermediate writer of T-SQL. Here is my current Query:

SELECT [FISCALYEAR],
[ACCTPERIOD],
SUM([ACTIVITYDEBIT]) AS TrialBalanceDebit,
[POSTINGTYPE]
FROM [dbo].[TB_Lookup]
WHERE [POSTINGTYPE]='Profit & Loss'
GROUP BY [FISCALYEAR],[ACCTPERIOD], [POSTINGTYPE]
ORDER BY acctperiod ASCand this is what is produces.

FISCALYEARACCTPERIODTrialBalanceDebitPOSTINGTYPE
2014 201401 282361372.13000 Profit & Loss
2014 201402 227246272.86000 Profit & Loss
2014 201403 315489534.33000 Profit & Loss
2014 201404 287423793.76150 Profit & Loss
2014 201405 256521290.76000 Profit & Loss
2014 201406 65582951.30000 Profit & Loss

Now I need a way to add another field that takes the TrialBalanceDebit from current ACCTPERIOD and adds it to the Previous ACCTPERIOD TrialBalanceDebit.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved