Transact SQL :: Get Max Date From Table Where Quantity Require Big From Quantity Shipped?
May 8, 2015
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 have 3 tables. Stores, Dates and Transactions. I want to combine all Stores with all Dates in one table and then calculate Last Stock Quantity.
Stores London Paris Prague
Dates 1.1.2014 2.1.2014 3.1.2014
Transactions 1.1.2014 London 1000 1.1.2014 Paris 1300 1.1.2014 Prague 1500 2.1.2014 London 800 3.1.2014 Prague 1200
And result should look like this Last_Quantity should be Quantity for last date in Transactions table.
1.1.2014 London 1000 1.1.2014 Paris 1300 1.1.2014 Prague 1500 2.1.2014 London 800 2.1.2014 Paris 1300 2.1.2014 Prague 1500 3.1.2014 London 800 3.1.2014 Paris 1300 3.1.2014 Prague 1200
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 have a column "Cost" (data type=varchar) and another column "Qty" (data type=int)
Now in outer query, I need to multiply this 2 columns Cost*Qty.But after trying some cast operation still getting "Error converting data type varchar to numeric."
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 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.
how to get the quantity from output of sql query command for example, I could get the container name by below command
select container_name from sysibmadm.snapcontainer get the container number by select TBSP_NUM_CONTAINERS from SYSIBMADM.SNAPTBSP_PART
now I want to get the container number by below command output result select container_name from sysibmadm.snapcontainer..so what more command should I add on above command, I mean I want to get the container number by container name from the output of above command, not by 'join'.
I'm trying to write sum function in SQL with only basic operators without using aggregation function, but I don't know how or if it's possible or not!? I've searched but can't find anything on the internet
For example we have table order:
OrderID ProductID Quantity ---------------------------- 1001 15 5 1002 35 7 1002 10 10 1003 50 30 1004 47 15 We can sum up the quantity with sum function in sql
SELECT SUM(Quantity) FROM OrderTable
How can I get the total quantity without using any aggregate functions?
should I set to "0" as a default value on a quantity and a price fieldor set to "null"?if a user enter nothing on the quantity or price field on a webbrowser, should I treat it as null or "0"? I am so confused about thisconcept. please advise me. thank you.
Hi There are 2 databases db1 and db2 on SQL server 7 with tables 1 and 2 respectively. Both these tables have a field called Quantity. IF quantity gets updated in table 1 then that change should be immediately reflected in table2. I though an update on table1 would serve the purpose but this trigger doesn't seem to be working however there are no errors during the syntax check. The code for the trigger that i have used on table 1 is as below CREATE TRIGGER [Qty_UPDATE] ON [Table1] FOR UPDATE AS IF UPDATE (Qty) BEGIN UPDATE db2.dbo.table2 SET db2.dbo.table2.qty = qty END Please help Thanks in advance
My need is to count the quantity of payments with 20000 step. This is my Oracle code. I'm in process of moving from Oracle to SQL Server. How to rewrite this code for SQL Server.
WITH got_grps AS ( SELECT TRUNC (sys_creation_date) AS date_creation , ( 1
I have been asked to report on missing Stock in my works Warehouses. My work uses SAP Business One for ERP, and Accellos for Warehouse Management. Both SAP / Accellos maintain stock levels, and whilst they do talk to each other (in real time), nothing is perfect and stock counts (within each system) sometimes develop discrepancies.
Here is the code that I developed to show stock discrepancies -
Code: SELECT Tx.[Item Code] , ISNULL(Ty.Qty, 0) AS 'A1 Qty'
Im making a shopping cart website for a school project in ASP.net with VB. I need help subtracting the quantity purchased (its saved in a session) from the stock number saved in a database.I know this:UPDATE inventory SET stock = stock - <quantity_purchased> WHERE id = <inventory_id>But I dont understand how to get the quantity purchased from the session to <quantity_purchased>. I tried putting the name of the session there and I got an error, i tried saving the session into a dim didnt work either.
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT [stock] FROM [product]" InsertCommand="INSERT INTO [product] ([stock]) VALUES (@stock)" UpdateCommand="UPDATE product SET stock = (stock - @Quantity) WHERE (productID = @productID)"> <InsertParameters> <asp:Parameter Name="stock" Type="Int16" /> </InsertParameters> <UpdateParameters> <asp:SessionParameter Name="Quantity" SessionField="Quantity" Type="Int32" /> <asp:SessionParameter Name="productID" SessionField="productID" Type="Int32" /> </UpdateParameters> </asp:SqlDataSource> and I have than in my VB code on submit : SqlDataSource1.Update()
Deciding whether or not to use a CTE or this simple faster approach utilizing system tables, hijacking them.
SELECT s.ORDER_NUMBER, s.PRODUCT_ID, 1 AS QTY, s.VALUE/s.QTY AS VALUE FROM @SPLITROW s INNER JOIN master.dbo.spt_values t ON t.type='P' AND t.number BETWEEN 1 AND s.QTY
Just wanted to know if its okay to use system tables in a production environment and if there are any pit falls of using them ?
This is what I want to do:1. Delete all tables in database with table names that ends with anumber.2. Leave all other tables in tact.3. Table names are unknown.4. Numbers attached to table names are unknown.5. Unknown number of tables in database.For example:(Tables in database)AccountAccount1Account2BinderBinder1Binder2Binder3.......I want to delete all the tables in the database with the exceptionof Account and Binder.I know that there are no wildcards in the "Drop Table tablename"syntax. Does anyone have any suggestions on how to write this sqlstatement?Note: I am executing this statement in MS Access with the"DoCmd.RunSQL sql_statement" command.Thanks for any help!
I am developing basket and need a simple way to get price x quantity + total price.
I get back a recordset which has a varying number of items, each item has a price and quantity. Using SELECT ((productprice)*(productquantity)) AS subtotal, product name, productid
I don't know how to add all subtotals to get a total, I have tried putting in SUM but but cant get it to work.
