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
I need sql query to select max date shipment where sum of quantity required > sum of quantity shipped from sales line table but i dont know how to make my sales line table as following
Here the result of query i need is max shipment date is 07/05/2015 and quantity required is 600 and quantity shipped is 450 so tat i must select this date because quantity Required is big from quantity shippedmy question How i write the query get result above in sql server 2005.
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; /
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!
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?
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
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:
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
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?
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:
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)
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
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
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.
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)
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...
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.
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
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?
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!
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.
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.
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:
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
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.
When a user orders an amount of a given product, I want to update the available quantity in my db. I use a loop to insert each ordered product item and the needed quantity into an ORDERED_ITEMS table (these items are tied to an order record in another table). The available_quantity is in the STOCK table. Here is my code. Can you help me out with how I can achieve this? (I know my queries should be in SP's, but for now while I'm getting everything the way I want it, it's easier to write it directly in my code.) Thanks! //insert the individual items string items_to_ship = "";
foreach (GridViewRow rw in GridView1.Rows) { if (rw.RowType == DataControlRowType.DataRow) { if (((TextBox)rw.Cells[4].FindControl("qtyneeded")).Text != "") { int qty = Convert.ToInt32(((TextBox)rw.Cells[4].FindControl("qtyneeded")).Text); int itm = Convert.ToInt32((rw.Cells[0].Text));
SqlConnection mycn2 = new SqlConnection(myConnectionStr); SqlCommand myCommand4; SqlDataReader rdr = null; int new_qty = 0; myCommand4 = new SqlCommand ("INSERT INTO SHP_ORDERED_ITEMS (ship_id, item_id, quantity) values(@ship_id,@item_id,@quantity);" + "SELECT item_description, qty_available from shp_stock where item_id = @item_id;", mycn2);
myCommand4.Connection.Open(); rdr = myCommand4.ExecuteReader(); while (rdr.Read()) { // create a list of item descriptions and quantities of each, for use in the email items_to_ship += (string)rdr["item_description"] + " (" + qty.ToString() + ")<br>";
In a database (Access) i have many lines. In one of colomnes (Label) i have entrys "AAA", "BBB", "CCC", ... . I would like to know how many "AAA" and
how many "BBB" we have in database.
Is this possible to achieve this by using i = RecordSet.Fields (Label). ... mixad with other functions where i is an integer and RecordSet a record set?
what a SQL query cal deliver a i as Integer, where i is quantity of lines labeled with "AAA".
currently we have some shipping software that has a mqsql database locally.There are filters on the program and when an item is shipped from our warehouse it goes into a filter "Shipped".We have a SKU and also quantity for the product.
Now we have an inventory program that also has a mqsql database,this has the same SKU as the shipping software database.what we hope to do is when an SKU is shipped in our software program(ie it Goes into the "Shipped" folder),it will deduct the quantity from the adjacent SKU in the inventory program database.
Note that the 2 databases are independent from each other but would like them to be in effect linked to each other.
I am trying to query only the Max date dependant on quantity
Create Table dbo.TestParts (Part char(30), Desc1 char(50), Desc2 char(50)); Create Table dbo.TestStructure (Model char(30), Part char(30), EDATE smalldatetime, QtyPer float); GO Insert INTO dbo.TestParts Values('101111','Widget A', 'Batteries Not Included'),
[Code] ....
Looking for a return of:
PART Description EDATE QtyPer 101112 Widget B ..... 2012-12-03 3 101113 Widget C ..... 2012-12-03 5 101114 Widget D ..... 2012-12-01 1
Widget A should not show because the last date the qty was changed to Zero
Closest that I have come..........(which is pulling the part 101111 which should be incorrect)
Select ts.Part, RTRIM(tp.Desc1) + ' ' + RTRIM(tp.Desc2) as Description, ts.EDATE, ts.QtyPer FROM testing.dbo.TestStructure ts Inner Join (Select Part,MAX(EDATE) as Date FROM testing.dbo.TestStructure WHERE QTYPER <> '0'
I need to query SQL Server Express 2012 records to find 6 serial numbers that are all assigned to one common unique number. Normally in this use case, 12 serial numbers are assigned to one common unique number, so I'm trying to find the odd entry.
Select FullItemName, Region, IssuedQuantity from Transactions.TransactionBaseMain where EnvironmentID = 34 and ModeID=2 and UnitOfIssueID=73 AND itemid=5605 and TransactionType in ('Issue') and DATEDIFF(DD,TransactionDate,GETDATE())<30 Group by FullItemName,Region,IssuedQuantity Order by FullItemName,Region,IssuedQuantity
I need to group the IssuedQuantity by region. (Add up the IssuedQuantity for the region).
In my Insert into #TempTable I need to calculate the 'qty_wasted' as difference between 'qty_received' and 'qty_used' Where would I put the calc statement?
INSERT INTO #TempTable (job_date, job_number, cost_code, qty_received, qty_used, qty_wasted, productId, plant_id) SELECT dbo.Batch.ReportDate AS job_date, dbo.Job.CompanyJobId AS job_number, dbo.Item.CompanyItemId AS cost_code, dbo.Product.CompanyProductId as productId, SUBSTRING(dbo.Job.CompanyJobId, 1,3) as plant_id, qty_received = CASE dbo.SourceType.CompanySourceTypeId WHEN 'MA' then SUM(dbo.ProductionEvent.Quantity) ELSE 0 END, qty_used = CASE dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN SUM(dbo.ProductionEvent.AlternateQuantity) ELSE 0 END FROM dbo.Batch INNER JOIN dbo.Event ON dbo.Batch.BatchGuid = dbo.Event.BatchGuid INNER JOIN.....
Hello all, I have an interesting question about calculating a price in my database for a quantity entered. I have a join table that I store ProductID, SizeID, Quantity and Price. The price for a particular product changes based most often on the quantity ordered. For example, if you order one unit of a widget the price is 10.00, however if you order one dozen units of the same widget the price drop to 9.75 and so on.
Here is a sample of my table....columns are in the order I specified above.
So depending on if my widget is available in certain sizes, the second column, then each product has an price for the size id and quantity at which the price break occurs.
I use this stored procedure to return the price or price range based on the input parameters entered.
CREATE PROCEDURE dbo.sp_GetPrice @ProductID INT, @QuantityID INT = NULL, @SizeID INT = NULL AS BEGIN SET NOCOUNT ON IF @QuantityID IS NOT NULL AND @SizeID IS NULL -- WE ARE L0OKING FOR A SPECIFIC PRICE BUT DO NOT HAVE A SIZE SPECIFIED SELECT DISTINCT Price AS 'Price' FROM join_ProductSizeQuantityPrice WHERE ProductID = @ProductID AND Quantity = @QuantityID
IF @QuantityID IS NOT NULL AND @SizeID IS NOT NULL -- WE WANT THE EXACT PRICE FOR THE SIZE AND QUANTITY SPECIFIED SELECT Price AS 'Price' FROM join_ProductSizeQuantityPrice WHERE ProductID = @ProductID AND SizeID = @SizeID AND Quantity = @QuantityID IF @SizeID IS NULL AND @QuantityID IS NULL SELECT MIN(Price) AS 'Price Range' -- WE ARE LOOKING FOR A PRICE RANGE FROM join_ProductSizeQuantityPrice WHERE ProductID = @ProductID UNION ALL SELECT MAX(Price) FROM join_ProductSizeQuantityPrice WHERE ProductId = @ProductID
END GO
So everything works great, however, when a user orders an quantity amount like 7, 13, 26 - which is not part of the table - I want to give them the discount available to them for the price break appropriate.
For example if a customer orders 16 widgets of size 2 the price break threshold they have crossed is one dozen, however they have not yet reached the next one which is two dozen. Therefore I want to offer the price associated with one dozen widgets of size id which is: $9.75. Once I have this a simple calculation of this price * 16 units would give me a total but my question is, how do I elegantly design this quantity / right price per unit calculation?
my friend said that to decrease quantity of item must use store procedure or trigger.. is there can use datatableadapter to decrease quantity? if yes.., so which the easy to use? procedure or trigger or tableadapter?ok, thx....
helo alll...,this is my data:my table is item(productid,stock) ,order(customerid,no_order), and orderdetail (no_order,productid,quantity) example: order and orderdetail displayed in gridview....order is displayed like this: customerid no_orderdetail A 1detail B 2 when i click detail in row 2, it's display orderdetail:no_order productid quantity 2 c1 2 2 p1 3 i have make all this is ok. but i want to decrease stok in item with quantity in orderdetail.my code in procedure like:CREATE PROCEDURE [dbo].[order_item](@productid AS varchar,@quantity AS INT)ASBEGINBEGIN TRANSACTIONDECLARE @no_order AS INTDECLARE @stock AS INTINSERT INTO [Orderdetail]([ProductId],[Quantity])VALUES(@productId,@quantity)SET @no_order = SCOPE_IDENTITY()SET @Stock = (SELECT [Stock] FROM [item] WHERE [ProductId] = @productId)UPDATE [item]SET[Stock] = @Stock - @quantityWHERE[ProductId] = @productIdCOMMIT TRANSACTIONENDreturn it can't work..how about his true code in store procedure?ok..., thx..
from this code, i want to reduce product's quantity in database. when I click buy button,it reduce old quantity and new quantity (quantity from cart when I want to buy it) in auto. please advise me.
