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....
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
shipment date quantity required quantity shipped
07/05/2015 200 100
07/05/2015 300 300
07/05/2015 100 50
06/05/2015 200 100
06/05/2015 200 200
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 created a dataset table adapter with existing stored procedure.Stored Procedure has 3 parameters.I am trying to create a page where use can search on different field and generate report on gried view control. They can either search by one parameter , two or may be multiple.
But When I try to run this it gives me error if I dont put all three parameters, how can I make this work so that even if I put one, two or multiple parameter it works error free.
CREATE PROCEDURE [dbo].[ConvertParentSupplier_SP] @supplierID varchar(10),@active varchar(10)ASselect distinct Supplier.SupplierID, SupplierName, ParentSupplierID, isnull(Active,'')as Activefrom supplier where supplier.ParentSupplierID =@supplierIDand Supplier.SupplierID=Supplier.ParentSupplierID and supplier.active=@activeorder by supplier.supplieridGO
I have a BOM table with all finished item receipes and semi items recipes. create a query where semi item materials are also listed in finished item recipe.
View 5 Replies View RelatedHi,
Here's my problem. I have 2 tasks defined in my Control Flow tab:
EXECUTE SQL--------->EXECUTE DTS 2000 PACKAGE
When I attempt to run it, by right-clicking the EXECUTE SQL task, and selecting "Execute Task", it only runs the EXECUTE SQL part (successfully), and does not "kick off" the EXECUTE DTS 2000 PACKAGE, after it is done running (even though it completes successfully, as shown by the green box).
Yes, they are connected by a dark green arrow, as indicated in my diagram above.
Why is this?? Am I missing something here? Need help.
THANKS
Hello,Basically, I have a table with 2 fieldsId item#1 33332 33333 22224 22225 22226 33337 33338 3333I would like to only select the last identical Item# which in this case would be the id 6,7 and 8Any idea how could I do that?Thanks
View 1 Replies View RelatedDear all,
I wanna ask you how to decrease log file size..?
For example i have database file with data file size is 122,166 Kb
and the database log file is 6,330,176 KB
Thanks,
Oko Sakti Banget
A want to user a procedure to decrease a number in a col. by 1 each day.
I need some type of operation
then a job has to run the procedure to decrease a number once a day.
I don't know how to go about the operation? I am going to the SQL Query Anylizer to help me run the script to test it. I have in a col. of 201 numbers, and I will subtract 1 from each day from all recorders. I guess you could call this a count down or timer. Does this code work for me?
Thank You,
Insert into newusers (Timer-1)
Select Timer
From newuses
GO
We were running a 4 processor server with 8 GB RAM and 800GB hard diskspace on Windows 2000 server and SQL Server 2000.Recently we have upgraded the server to 8 processor and added another800GB. The OS was upgradded to Windows 2003 enterprise edition.All the processor are 2.5GHz Xeon preocessors.After upgradation the performance of the server has gone down from whatit was giving before the upgrade.It seems that multiprocessing is not ocurring.The model of the server is HP DL740The OS is installed in a built in array(5i controller) of the serverand the SQL server is installed in a external array(6400 controller).I will really aprecite if anyone can give any clue to improve theperformance.Thanks in advance.Taw.
View 5 Replies View RelatedHello Guys,
Just wondering if someone can help me decrease the size of mdf and ldf files. In the past production database "NewUniverse" had been allocated space of 100 GB for mdf file and 8 GB of ldf file. However the data file has only used 30 GB of data. But now due to disk space related reason, I tried to decrease the datafile size from 100 GB to 40 GB. But I am not able to do it.
Any help in this regard would be appreciated.
Hi
I installed " Web Wiz Forum ASP SQL 2000 DB "
it work fine but
when i added some data in the forum for example my db size is 1.45 MB
after i delete those data the db size will not decrease
is there any code that i must enter on the sql server setup file
Excuse me i asked this question in the web wix forum site but they don`t
answer me
if know what i must to do plz tell me
Thanks
I have a database that the transaction log grows about 1 GB per day. I would like this size was decreased daily. Does anyone have any suggestions?
Some friends told me that after the Full backup that is done daily, I should perform a backup of transanction log with option to truncate and after, make a shrink in the database. That is exactly what should be done?
Thank you,
Best Regards,
Ralph Haddad
Hi all,
How to derease the database file size in Primary filegroup.
Thanks,
hi everyone
what the matrix's class name ?
where is the matrix's dll?
Can i create a new class extends the matrix?
I want to override the matrix's onpaint method.
Can i do this ?
why the table not have the column group?
Hi,
My DB size (Right click on DB Name, Data Files tab, Space Allocated field) was 10914 MB.
I delete a huge table (1.2 million records * 15 columns).
I checked the db size again. It didnt change.
Shouldn't it decrease because I delete a huge table ??
This is an example of my data:
Col1 Col2 Col3
1 LONGDESCRIPTION1 111
1 LONGDESCRIPTION1 222
1 LONGDESCRIPTION1 333
1 LONGDESCRIPTION1 444
2 LONGDESCRIPTION2 XXX
2 LONGDESCRIPTION2 YYY
2 LONGDESCRIPTION2 XYZ
2 LONGDESCRIPTION2 999
This is an example of what I want my report to look like (ill put row numbers to help identify)
Col1 Col2 Col3 Row Num
1 LONGDESCRIPTION1 111 1
222 2
333 3
444 4
Subtotal Col3Sum (PAGEBREAK)
2 LONGDESCRIPTION2 XXX 5
YYY 6
XYZ 7
999 8
Subtotal Col3Sum
The Settings:
I'm currently using a single TABLE DETAIL ROW with HIDE DUPLICATES in Col1 and Col2.
The LONGDESCRIPTION field can be expected to take 2 row heights worth of space because it wont fit.
I've also got CAN INCREASE TO ACCOMODATE CONTENTS and CAN DECREASE TO ACCOMODATE CONTENTS selected.
The Problem:
When Printing/PrintPreviewing Page1, the Height of Rows 2/3/4 ARE THE CORRECT SIZE (only 1 row high, meaning that can decrease to accomodate contents works).
BUT when Printing/PrintPreviewing Page2, the Height of Rows 6/7/8 FOLLOW THE HEIGHT of ROW 5 (two rows high, meaning that can decrease to accomodate contents DOES NOT WORK).
Rows 2/3/4 has the same Row Height as Row 1 ONLY ON PAGES after Page1.
Rows 6/7/8 has the same Row Height as Row 5 ONLY ON PAGES after Page1.
Can decrease to accomodate contents does not seem to be working for Pages after page 1.
I have a database consisting of two main tables and 12 sub tables.
This was leading to increase in database size. So we thought of storing the sub tables data in the main tables in form of xml in a column of varchar(2000) type.
So we created a new database that only had 2 tables and we stored the data of the sub tables in the new column of the main table.
Surprisingly we saw that the database size increased rather than decreasing .
I have another query regarding the graph/chart that i made in report builder. May i know on how to lessen the space between a column in my stacked bart chart.
View 5 Replies View RelatedI am having trouble finishing the last bit of a report. The report shows orders that customers have placed that contain 0 promo items, All promo items (all items in order are promo items), and a mix of promo and non promo (at least 1 promo item and 1 non-promo item). Ive simplified this a bit for ease of understanding but lets assume we have 2 tables: A Promo table that contains the items on promotion and the dates that promotion is valid, and a Sales table, that contains the order number, order date, and sku ordered.
I've already written code that finds orders that have at least 1 promo item in them, and using that, I can determine what orders have 0 promo items in them. Where I am stuck is taking the orders that have at least 1 promo item in them, and separating them into orders that have only promo items, and those that have both promo and not promo items in them. Also, there are several promos throughout the year (called "Offers") so in my code below, you can see 2 different Offers ("JF" and "MA") with their corresponding dates they are valid. They will never overlap. My results also have to be split out by Offer so management can look at the results of each offer separately. Here is some code:
Code:
create table #Promos (
Offer varchar(2) null,
SKU int null,
StartDt date null,
EndDt date null
[Code] ....
So my results should show OrderNo A1111 in the Promo and No Promo group because of SKU 5 not being promotional during the time that order was placed. OrderNo A2222 should be in the Promo Only group because both SKUs on the order were promotional at the time the order was placed.
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
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.Parameters.Add("@ship_id", SqlDbType.UniqueIdentifier).Value = shipmentID; myCommand4.Parameters.Add("@item_id", SqlDbType.Int).Value = itm;
myCommand4.Parameters.Add("@quantity", SqlDbType.Int).Value = qty;
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>";
}
myCommand4.Connection.Close();
}
}
}
Quantity of entrys from record set
Hi All!
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".
How can i do this with VBA?
Thank You all!
Alexander
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'
[Code] .....
I have a table Item_used like this
Itemname Employee Quantity
pencil samlopez 10
pencil samlopez 5
All I want is to make a report that sum all the quantity of the same items with the same employee like this
Itemname Employee Quantity
pencil samlopez 15
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.
View 2 Replies View RelatedI have the following query
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).
I'll give a basic example of my table.
OrderID, ProductID, Quantity
1,1,4
I would like to display the above row 4x (hence qty of 4)
1,1,"item 1"
1,1,"item 2"
1,1,"item 3"
1,1,"item 4"
Any suggestions? Thanks.
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?
CREATE TABLE#TempTable
(
job_date datetime,
job_number char(15),
cost_code char(15),
qty_received decimal(8,2),
qty_used decimal(8,2),
qty_wasted decimal(8,2),
productId char(25),
plant_id char(10)
)
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.
1, 1, 1, 10.00
1, 2, 1, 10.00
1, 3, 1, 10.00
1, 1, 6, 9.90
1, 2, 6, 9.90
1, 3, 6, 9.90
1, 1, 12, 9.75
1, 2, 12, 9.75
1, 3, 12, 9.75
1, 1, 24, 9.50
1, 2, 24, 9.50
1, 3, 24, 9.50
1, 3, 36, 9.30
1, 2, 36, 9.30
1, 1, 36, 9.30
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?
-Brian
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..
View 8 Replies View Relatedprotected void Buy_Click(object sender, EventArgs e)
{SqlConnection conn = new SqlConnection();
SqlCommand cmd = new SqlCommand();
try
{String connStr = ConfigurationManager.ConnectionStrings["project_DataFile"].ConnectionString;
conn.ConnectionString = connStr;
conn.Open();
cmd.Connection = conn;ArrayList cart = (ArrayList)Session["Cart"];
{String oldSel = "SELECT Qty_warehouse FROM Producttable";
Int32 sel = 0;Int32.TryParse(oldSel, out sel);
string num = cart.Count.ToString();Int32 numQty = 0;
Int32.TryParse(num, out numQty);int nQty_warehouse = 0;
nQty_warehouse = sel - numQty;String sql = "UPDATE Producttable" + " SET Qty_warehouse =" + "'"+ nQty_warehouse +"'";
cmd.CommandText = sql;}
}catch (Exception ex)
{
Response.Write(ex.ToString());
}
finally
{if (conn.State.Equals(ConnectionState.Open))
{
conn.Close();
Response.Write("end.aspx");
}
}
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'.