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
ADVERTISEMENT
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Mar 20, 2014
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29
output should be ......
ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29
View 0 Replies
View Related
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
Mar 18, 2014
I have a situation where deleting old records is blocking updating latest records on highly transactional table and getting timeout errors from application.
In details, I have one table called Tran_table1 in OLTP database. This Tran_table1 is highly transactional table, it will receive data for insert/update continuously
While archiving 2 years old records from Tran_table1 into Tran_table1_archive in batches(using DELETE OUTPUT INTO clause), if there is any UPDATEs on Tran_table1,these updates are getting blocked and result is timeout errors in application.
Is there any SQL Server hints to avoid blocking ..
View 3 Replies
View Related
Sep 7, 2015
declare @table table (
ParentID INT,
ChildID INT,
Value float
)
INSERT INTO @table
SELECT 1,1,1.2
[code]....
This case ParentID - Child 1 ,1 & 2,2 and 3,3 records are called as parent where as null , 1 is child whoose parent is 1 similarly null,2 records are child whoose parent is 2 , .....
Now my requirement is to display parent records with value ascending and display next child records to the corresponding parent and parent records are sorted ascending
--Final output should be
PatentID ChildID VALUE
33 1.12
null3 56.7
null3 43.6
11 1.2
null1 4.8
null1 4.6
22 1.8
null1 1.4
View 2 Replies
View Related
Aug 6, 2007
HI,
I have been trying to solve the locking problem from past couple of days. Please help mee!!
Scenario:
--------------
I have a SSIS package in which 2 data flow tasks. 1st data flow task deletes records from a 5 tables and the 2nd data flow task should insert records into 1 of the five tables after the success of 1st data flow task. This scenario runs in Transacation.
The above scenrio in the 2nd data flow task hangs in runtime. It does not complete. with sp_who2 command i could see that there is an intent share lock(LK_M_IS) on the table and the status is SUSPENDED.
I dont know how to come out of this locking. Please help.
Thanks ,
Sunil
View 7 Replies
View Related
Nov 2, 2006
Hello
Im searching for a solution to set all matrix row or cell the same height.
it schoud looks like this example:
This is a simple matrix
test a
text b
text c
text d
text e
text f
text g
This is a matrix with all the same row-height.
test a
text b
.
text c
.
.
text d
text e
text f
text g
.
.
Thx you a lot
View 3 Replies
View Related
Dec 3, 2014
I have a table with about half a million records, each representing a patient in my county.
Each record has a field (RRank) which basically sorts the patients as to how "unwell" they are according to a previously-applied algorithm. The most unwell patient has an RRank of 1, the next-most unwell has RRank=2 etc.
I have just deleted several hundred records (which relate to patients now deceased) from the table, thereby leaving gaps in the RRank sequence. I want to renumber the remaining recs to get rid of the gaps.
I can see what I want to accomplish by using ROW_NUMBER, thus:
SELECT ROW_NUMBER() Over (ORDER BY RRank) as RecNumber, RRank
FROM RPL
ORDER BY RRank
I see the numbers in the RecNumber column falling behind the RRank as I scan down the results
My question is: How to convert this into an UPDATE statement? I had hoped that I could do something like:
UPDATE RISC_PatientList_TEMP
SET RRank = ROW_NUMBER() Over (ORDER BY RRank);
but the system informs that window functions will only work on SELECT (which UPDATE isn't) or ORDER BY (which I can't legally add).
View 5 Replies
View Related
Jul 23, 2005
I need a little help here..I want to transfer ONLY new records AND update any modified recordsfrom Oracle into SQL Server using DTS. How should I go about it?a) how do I use global variable to get max date.Where and what DTS task should I use to complete the job? Data DrivenQuery? Transform data task? How ? can u give me samples. Perhaps youcan email me the Demo Package as well.b) so far, what I did was,- I have datemodified field in my Oracle table so that I can comparewith datelastrun of my DTS package to get new records- records in Oracle having datemodified >Max(datelastrun), and transferto SQL Server table.Now, I am stuck as to where should I proceed - how can I transfer theserecords?Hope u can give me some lights. Thank you in advance.
View 2 Replies
View Related
Jan 18, 2008
I have a query similar to the following. The intent of this query is to retrieve the top 6 records meeting the specified criteria (LOGTYPENAME = 'Process Status Start' OR LOGTYPENAME = 'Process Status End' ) based on most recent dates. Please keep in mind that I expect to return up to 6 records for each unique LogProcessName. This could be thousands of different LogProcessNames with up to 6 records for each.
1) The table I am executing against currently is very large in size and thus takes a long time to execute against. It would seem there must be a more efficient query to get the results I am looking for?
2) CTE doesn't work on SQL 2000. I need a query that does.
3) I cannot modify the database itself in the process.
;WITH cte AS (
SELECT [LogProcessName], [LogBody], [LogDate], [LogGUID], row_number()
OVER(PARTITION BY [LogProcessName]
ORDER BY [LogDate] DESC)
AS RN
FROM [LOGTABLE]
WHERE [LogTypeGUID] IN (
SELECT LogTypeGUID
FROM LOGTYPE
WHERE LogTypeName = 'Process Status Start'
OR LogTypeName = 'Process Status End' ) )
SELECT *
FROM cte
WHERE RN = 1 OR RN = 2 OR RN = 3 OR RN = 4 OR RN = 5 OR RN = 6
ORDER BY [LogProcessName] DESC, [LogDate] DESC
Does anybody else have any idea that would yield the results that I am looking for and take into account items 1-3 above?
Thanks in advance.
View 4 Replies
View Related
Nov 28, 2005
I tried to port 10000 records using DTS. After porting of 9900 records I got an error and comes out without any result. But I want to keep the records which has been ported till the error occured. Plz help me.
View 1 Replies
View Related
Oct 31, 2007
Hi, I have had this problem for a while and have not been able solve it.
What im looking at doing is looping thru my patient table and trying to organise the patients in to there admission sequence, so when patient "A" comes in and is treated at my hospital and is discharged and admitted to another Hospital within one day then patient "A" will get a code of 1 being there first admission.
then if patient "A" is admitted again but there admission date is greater than one day they get a code of 2 being for there second admission but will need to loop thru table looking for other admissions and discharges.
The table name is Adm_disc_Match_tbl
Basically what i have 4 fields.
Index_key = which is the patient common link (text)
ur_episode = this wil change for each Hospital (text)
Admission_datetime = patient admission date and time (datetime)
Discharge_datetime = patient discharge date and time (datetime)
example of data
Code: ( text )
Index_key,ur_episode,Admission_datetime,discharge_ datetime
HERBERT-7/1929,513884-1686900,4/07/2006 10:58,17/07/2006 13:37
HERBERT-7/1929,C023092-1698859,17/07/2006 13:20,24/07/2006 0:30
ELSIE-5/1916,G148445-1720874,8/08/2006 11:00,30/08/2006 10:00
STANISLAWA-3/1918 ,G119981-1720045,8/08/2006 13:01,22/08/2006 12:13
FREDA-11/1925,183772-1998910,27/03/2007 9:53,3/04/2007 11:06
FREDA-11/1925,G147858-2007408,3/04/2007 10:49,26/04/2007 12:39
FREDA-11/1925,183772-2037727,28/04/2007 17:05,9/05/2007 11:41
FREDA-11/1925,G147858-2052082,9/05/2007 12:00,25/05/2007 11:17
If anyone could help it would be much appreciated.
View 6 Replies
View Related
