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


ADVERTISEMENT

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

SQL Server 2008 :: Balance Sheet Query Showing Wrong Amounts On VAT Accounts Only

May 21, 2015

I have one query which is pulling Balance sheet amounts from SAP Business One database. The query is giving the correct figures for the rest of the accounts except for the VAT Input refundable account 123600 and VAT Output Payable account 221400. The query sums up totals at Title account level(FatherNum) and the above accounts are the title accounts:

SELECT CAST(T0.TransId AS Varchar(30)) AS TransId, CASE WHEN t3.FatherNum IN ('100000', '350000') THEN '-3 OK' ELSE CAST(T0.TransType AS Varchar(30))
END AS TransType, CAST(T0.BaseRef AS VarChar(30)) AS BaseRef, T0.RefDate,T0.Number as Docnum, DATEPART(Month, T0.RefDate) AS JrnMonth, T0.FinncPriod, T1.Account, T1.Debit,
T1.Credit, T1.Debit - T1.Credit AS JrnAmt, ISNULL(T1.SYSCred, 0) AS SysCred, ISNULL(T1.SYSDeb, 0) AS SysDeb, T1.ShortName, T1.Ref1, T1.Ref2,

[code]....

View 8 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

Analysis :: SSAS Calculation With Division Combined With A Time Calculation?

Sep 17, 2015

I have created calcalated measures in a SQL Server 2012 SSAS multi dimensional model by creating empty measures in the cube and use scope statements to fill the calculation.

(so I can use measure security on calculations

as explained here  )

SCOPE [Measures].[C];

THIS = IIF([B]=0,0,[Measures].[A]/[Measures].[B]);

View 2 Replies View Related

Converting Oracle Calculation To Sql Server 2005 Calculation

Jul 19, 2007

Hi I am having to convert some oracle reports to Reporting Services. Where I am having difficulty is with the

calculations.

Oracle

TO_DATE(TO_CHAR(Visit Date+Visit Time/24/60/60,'DD-Mon-YYYY HH24:MISS'),'DD-Mon-YYYY HH24:MISS')



this is a sfar as I have got with the sql version

SQLSERVER2005

= DateAdd("s",Fields!VISIT_DATE.Value,Fields!VISIT_TIME.Value246060 )



visit_date is date datatype visit_time is number datatype. have removed : from MI(here)SS as was showing as smiley.



using:

VS 2005 BI Tools

SQLServer 2005



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

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

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

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

Make Procedure To Check Balance &> Or &< Totalcost

Sep 9, 2007

 helo  all...,i have create procedure can decrease totalcost from order table(database:games.dbo) with balance in bill table(database:bank.dbo). my 2 database in same server is name "boy"
i have 2 database like: bank.dbo and games.dbo 
 in games.dbo, have a table name is order(user_id,no_order,date,totalcost)
in bank.dbo, have a table name like is bill(no_bill,balance)
this is a list of bill table
no_bill            balance
111222            200$
222444            10$ 
this is a list of order table 
user_id            no_order            date            totalcost
    a                     1                  1/1/07             50$
when customer insert no_bill(111222) in page and click a button,  then bill table became
no_bill            balance
111222            150$
222444            10$
when customer insert no_bill(222444) in page and click a button, then message "sorry, your balance is not enough" 
mystore procedure like:ALTER PROCEDURE [dbo].[pay](    @no_bill AS INT,    @no_order AS int,    @totalcost AS money)ASBEGIN    BEGIN TRANSACTION            DECLARE @balanc AS money                SET @balanc= (SELECT [balance] FROM Bank.dbo.bill WHERE [no_bill] = @no_bill)        UPDATE [bank.dbo.bill]        SET            [balance] = @balanc - @totalcost        WHERE            [no_bill] = @no_bill    COMMIT TRANSACTIONEND it can decrease money in bank, but i want it ceck money if balance > totalcost, so balance-totalcost,if balance<totalcost,so error message"sorry, your balance not enough"is it can make in procedure?thx... 

View 2 Replies View Related

Running Sum Is A Unique ID Field - Balance On Certain Date?

Dec 20, 2012

I am working on a software primarily related to accounting with visual basic

Many of the problems and proposed several solutions for running sum, but I have read almost all of these interpretations,

have applied when running sum is a unique ID field. but almost all of the functioning of accounting reports are in chronological order. So he questioned what was the balance on 05.05.2012.

ID of the process is done, if the user enters a process backward a few things since then, the transaction date is old, but the ID is new, are experiencing problems. eg

ID DATE DEBIT CREDIT BALANCE

1 02.02.2012 100.00 0.00 100.00

6 04.04.2012 0.00 150.00 -50.00

3 02.05.2012 70.00 0.00 20.00

4 02.05.2012 80.00 0.00 100.00

2 06.06.2012 120.00 0.00 220.00

must balance the figure above.

if ID = 2 record, the record date 06/06/2012 and if the ID = 6, date 04/04/2012, the date the order is made if the listing is experiencing trouble getting balance. 2 records in the same day when the balance at worst formula breaks down. The big point is that I hang out in my project.

the only solution is to use a temporary table or writing about it in Visual Basic seems to calculate. the use of temporary table is not a solution, but unfortunately also very fast.

View 1 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

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 View Related

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

Power Pivot :: Stock Balance - Last Date

Oct 23, 2015

I am struggling with the Lastdate function. I have got stock balance data and want to show the number of products/models that are on stock at the latest date of the stock balance table.

My DAX formula is as follows:

=CALCULATE(DISTINCTCOUNT('3S-StockData'[Article Model]);LASTDATE('3S-StockData'[Date]))

I get the wanted results for all aggregated product groups, on product/model level however the formula does not give me the information wanted (see screenshot).

Basically, the formula calculates correct, but I want in my example only models shown with the date 2015-10-21.

View 2 Replies View Related

SQL Server 2012 :: Recurrent Balance That Resets When Hitting A Value

Oct 15, 2014

I have query that calculate a running balance, but I need to reset that balance when it reaches 240. Then start calculating the balance again from that value. For instance, let's see we have the following table:

YearAmmount
200049.95
200179.92
200279.92
2003114.99
2004120.00

[Code] ....

If I run the following query to get the running balance:

SELECT Year,
Ammount,
SUM(Ammount) OVER(ORDER BY Year ROWS UNBOUNDED PRECEDING) AS RunningTotal
FROM _YearlyTotals

I get:

YearAmmountRunningTotal
200049.9549.95
200179.92129.87
200279.92209.79
2003114.99324.78
2004120.00444.78

[Code] ....

I'm looking to reset the Running Total if it reaches 240, and continue calculating from there: Something like this:

YearAmmountRunningTotalAdjustment
200049.9549.9549.95
200179.92129.870.00
200279.92209.790.00
2003114.99240.00-84.78
2004120.00240.00-120.00

[Code] ....

Here is the code to create the table and insert data:

CREATE TABLE _YearlyTotals(
[Year] int NULL,
Amount numeric(18, 2) NULL
) ON [PRIMARY]
INSERT INTO _YearlyTotals([Year],Ammount) VALUES (2000,49.95)
INSERT INTO _YearlyTotals([Year],Ammount) VALUES (2001,79.92)

[Code] .....

View 4 Replies View Related

Power Pivot :: Determine Opening And Closing Balance

Jun 3, 2015

I am preparing a monthly cash flow statement and for doing so, I need to determine the monthly opening and closing cash balance.  To simplify, this is what I did.

1. Dragged months to the columns labels

2. Created a slicer for selecting the Financial Year.  In this slicer, I chose 2015-16 i.e. April 1, 2015 to March 31, 2016.

Since I have data only for two months of this Financial Year i.e. April and May, only these two months show up in the column labels.

To determine the monthly opening cash balance, I added the following measure

=CLOSINGBALANCEMONTH(SUM(bank_book[Balance]),PREVIOUSMONTH(calendar[Date]))

To determine the monthly closing cash balance, I added the following measure

=CLOSINGBALANCEMONTH(SUM(bank_book[Balance]),calendar[Date])

Much to my surprise, I got the result as seen in the image below.  Cells B5, C6 and D5 are blank.  On going through my Bank Book, I realised that

1. There is no figure in cell B5 (Opening balance of April) because there was no transaction on the last day of March i.e. March 31.  The last transaction was on March 28

2. There is no figure in cell C6 (Closing balance of May) because there was no transaction on the last day of May i.e. May 31.  The last transaction was on May 30

3. There is no figure in cell D5 (Opening balance of June) - same reason as mentioned in point 2 above.

As seen in the image, I have also computed the monthly "Last date of previous month" and "last date of current month" but do not know how to make use of them in computing the opening and closing Cash balances.

Please also note that there can be multiple transactions on the last day of any month.  For e.g., let's say the last day of transaction in May 2015 was May 30 (not may 31) but there were multiple transactions on this last day (both inflow and outflow). 

So I cannot simply determine the last day of transaction and take MAX/MIN/SUM that day.  I have to take the final balance on that day.

How to solve this problem i.e. in determining the opening and closing cash balances.

View 10 Replies View Related

SQL Server 2012 :: Creating Running Total Based On Single (SUM) Opening Balance

Jun 7, 2015

I am creating a query that shows the consumption of stock against Manf Orders (M/O) and struggling on the last hurdle. I am having difficulties calculating a running total based on an Opening Balance. The first line returns the correct results but the following lines do not. I have tried other variants of the "Over Partition" but still no joy?

SELECT CASE WHEN ROWNUMBER > 1 THEN ''
ELSE A.Component
END AS Component ,
CASE WHEN ROWNUMBER > 1 THEN ''
ELSE A.SKU

[Code] ....

Please see attachment to view output.

View 7 Replies View Related

Reporting Services :: Unable To Calculate Running Balance Column Correctly In SSRS

Oct 7, 2015

I created a view that i want to use in ssrs.In the view there is a column for running balance.In the table contain transaction of inventory with their quantity.

"SELECT     TOP (100) PERCENT ITEMNMBR, TRXSORCE, DOCTYPE, DOCNUMBR, DOCDATE, HSTMODUL, UOFM, UNITCOST, EXTDCOST, TRXQtyInBase,
                          (SELECT     SUM(TRXQtyInBase) AS Expr1
                            FROM          dbo.INVTRXB AS b
                            WHERE      (DEX_ROW_ID <= a.DEX_ROW_ID) AND (ITEMNMBR = a.ITEMNMBR)) AS ENDQTY
FROM         dbo.INVTRXB AS a
ORDER BY ITEMNMBR, DOCDATE"

If i run the query on sql or use the view on ssr. The end qty is  not showing accurately.I ran it on another database it works perfectly. Then i noticed that the dex_row_ID of the second database is sequential as the date. But for the initial database it was not sequential as with the date.

View 4 Replies View Related

Reporting Services :: Get Computed Current Row Closing Balance Column Value In Next Row Opening Value Column

Nov 17, 2015

I have a Table Having Date,Opening,Addition,Sale values where opening value comes in the very first row other times it is zero.

In ssrs how can i have a report showing closing value = Opening+Addition-Sale  in current row (it is simple for 1st row ). this closing be the opening value in next row and same formula to be continued...

Date Opening AdditionTotalTank saleClosing Stock
01-11-15 14435 0 14435 8243 20627
02-11-15 0 15000 15000 9433
03-11-15 0 9000 9000 9436
04-11-15 0 12000 12000 8392
05-11-15 0 6000 6000 8157
06-11-15 0 12000 12000 8456
07-11-15 0 15000 15000 10903
08-11-15 0 6000 6000 8485
09-11-15 0 6000 6000 9413
10-11-15 0 21000 21000 6413

View 7 Replies View Related

Reading From A Xl Sheet

Jul 17, 2007

Hi All,

I would like to know if i can read data from xl sheet using a stored procedure?

Thanks in advance

vishu

View 17 Replies View Related

Dropping Excel Sheet

May 14, 2002

I guess nobodys heard of this? I'm using DTS to transform data to Excel spreadsheet. I have a DROP TABLE `data$` then a CREATE TABLE `data$` the old data is cleared but the new data is appended to the blank rows of the old data. So if I had 5 rows before now I have 10. And the new data has 5 blank rows before it.

I've tried deleting the excel file & replacing it with a new one.
I've used the wizard thinking it was me but no good, it still happens.

HELP!

View 1 Replies View Related

Export To Excel Sheet

Sep 12, 2007

I have a table calld studentMaster

I want export this this table to excel sheet.

please help me any one

View 3 Replies View Related

Read Each Excel Sheet

Nov 2, 2007

Hi,
I have an excel spreadsheet with several sheets.
These sheets get populated with data from an external source database i.e. a third party application.
I would like to create a SSIS package to read the data in each sheet.
Please note that I do know how to create SSIS package to read an excel file with only one sheet.
Is there a functionality in SSIS to loop though each sheet in one excel file?
Thanks

View 1 Replies View Related

Xml Input Using Xml Style Sheet

Jan 21, 2007

Trying to install SQL express but can't. I used the uninstall tool and it keeps hanging up on this error.

I am using XP and IE7.

I have also adjust my security setting to allow cross domains. Short of going and getting another machine I am at a loss.

No beta software is on machine and I also cleaned the registry..



I am not a developer. This looks like an issue all around MS..

View 1 Replies View Related

How To Transfer Data From Spread Sheet

Sep 25, 2001

how to transfer data from Excel spread sheet to table in the data base table..?

View 1 Replies View Related

Table Join Between Excel Sheet And MS-SQL

Oct 13, 2006

My company has recently transitioned to a centralized Oracle database model.
For the sake of security, the "powers that be" have also denied any query ability to any central tables. They refuse to create views or any other open tables for people to query.
Instead, they provide a "tool" which people can use to download data - to Excel Spreadsheets.
In the past, before this "improvement" lots of users in the local plants were able to query the old system to bring data into spreadsheets for reports, analysis, etc.
Now the place is jammed to the hilt with linked spreadsheets - people do their "table joins" with linked cells and Excel VLookups (yuk). This is because the "powers that be" still demand that these reports, analysis, etc. be done.

I am trying to use SQL-DMO to create a table join between one of these Excel Data pulls and a MS SQL Server table in Excel so that I can join without VLookup. IS SQL-DMO the right way to go?

Has anyone done this? I think I am close, but I don't know how to use the SQL-DMO attached Excel table object I've created in a join. I can't see the object in MS Query. I am not adverse to doing the whole thing in VBA...

Here is another question. Most of these Data pulls using the "tool" (ball and chain, boat anchor) are done once a day or once a week. Would a better strategy be to create MS SQL server Tables that are dropped and re-written when the data is pulled into Excel? This would mean that the report worksheet does not have to import the Excel Data pull sheet to MS SQL when it updates its query.

View 7 Replies View Related

Excel Destination Spread Sheet

Dec 10, 2007

Hi,

I've a problem with excel destination spread sheet.I've created a package which pulls the data from sql server and load it into excel sheet.The main thing Ive to do is I've to create different destination tables(work sheets) for different data.i.e.,The source is a sql query which pulls the data in groupwise with group by clause.So,I've to create individual work sheet for each group with that data.How it can be done.Please, advice me.

Thanks in advance.

View 1 Replies View Related







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