Is it possible to find the position of an item without retrieving all the records in the table?
I have a products table, and want to find out how its sales are doing within a particular category.
My table consists of the following columns of interest:
ProductID, CategoryID, ItemsSold.
So, how would I turn the following query into an (not sure if the terminology is correct) aggregate query?SELECT * FROM dbo.Products
WHERE CategoryID = 10
ORDER BY ItemsSoldAlso, is it possible to include the SUM() of the items (in the same category) in the aggregate function or would I need to perform a separate query?Any help would be very much appreciated.Thanks.
I have a table of 30,000 records on my handheld which runs windows 4.2 and sql me. When the item I am searching for is at the top of the table, the search finds it easily. When the item is in the middle or lower, it doesnt find it. I am not running out of memory and my database is on the local memory. Here is my code:
String query = "SELECT * FROM Products WHERE Barcode = " + TheReaderData.Text;
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
We have a work order notes table in our ERP system, and I want to see the most recent note record for each work order. Sometimes there will one be one, so I want to see all those, but sometimes there will be several notes for each work order and in this case I want to see only the most recently added note for that work order.
The query below shows some results as an example. In this case I want to see all the records except for work order number DN-000023 where I only want to see the note dated/timed 07-12-2011 16:52 (the most recent one).
select id, worknumber, date, notes from worksordernotes
id worknumber date ----------- ------------ ----------------------- -------------------- 1 DN-000056 2011-12-07 13:22:00 13.20 PM JAMES- SPOK 2 DN-000079 2011-12-07 14:24:00 JCB HAVE TOLD ME THE 4 DN-000065 2011-12-07 15:48:00 ANDY FROM SITE RANG 5 DN-000023 2011-12-07 15:54:00 CHASED THIS 4 TIMES 6 DN-000023 2011-12-07 16:52:00 HOLTS ATTENDED THIS 7 DN-000092 2011-12-08 09:50:00 RETURNING WITH PARTS
Use the Northwind database Products table as an example.Purchasing dept gets a report showing when inventory items on hand qty arebelow the reorder level.easy enough:Select ProductID, ProductName, SupplierID, UnitsInStock, ReorderLevelfrom Productswhere (UnitsInStock < ReorderLevel)Results:ProductID ProductName SupplierID UnitsInStock ReorderLevel2 Chang 1 17253 Aniseed Syrup 1 1325It would be nice to know what other products are purchased from this samevendor in case other items are close to their reorder level.All products for Supplier ID 1Select ProductID, ProductName, SupplierID, UnitsInStock, ReorderLevelfrom Productswhere SupplierID = 1Results:ProductID ProductName SupplierID UnitsInStock ReorderLevel1 Chai 1 39102 Chang 1 17253 Aniseed Syrup 1 1325This shows there is 1 more product (Chai) that also comes from Supplier 1.Is there a way to show all items from a vendor when some of the items arebelow the reorder level without needing a separate query for each vendor?Thanks
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.
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.
Hi,I want to check that Is there row exists in table or not.Please correct me. Dim conn As SqlConnection Dim comm As SqlCommand Dim reader As SqlDataReader Dim connstring As String connstring = ConfigurationManager.ConnectionStrings("iharyana").ConnectionString conn = New SqlConnection(connstring) comm = New SqlCommand("select * from test where username=@username", conn) comm.Parameters.Add("@username", Data.SqlDbType.VarChar, 20) comm.Parameters("@username").Value = uname conn.Open() reader = comm.ExecuteReader While reader.Read If reader.Item("username").ToString = "" Then Response.Redirect("http://www.iharyana.com") End If End While reader.Close() conn.Close()
I have a sql table containing names of departments. Is there a way after a user has typed a department in a textbox on a web page I can search for it in the sql table and if it isn't there then add it. I am using asp.net for the web page.
I want to figure out if a student has not been to class in the last 3 scheduled days. The days do not have to be consecutive.
I have 2 tables. One table with schedules and one with attended time.
What I really need is to the find the 3rd highest MAX() from the schedule table for each student. I was wondering if someone has any idea how I could find these record?
Example scheduled records student1 2/4/2004 7 hours scheduled student2 2/4/2004 8 hours scheduled student1 2/3/2004 8 hours scheduled student1 2/2/2004 6 hours scheduled <---Need this one student2 2/2/2004 8 hours scheduled student2 1/30/2004 4 hours scheduled <---Need this one ...thousands more...
It has been a real brain teaser for me. If anyone has even an inelegant solution, I would love to see it.
I need to figure out hours based on StartTime and EndTime (year does not matter) from the first table and totaled for the entire week based on the effective date in the second table.
I need it to return the follwing: WorkHourGroup TotalWorkHours TJOHNSO 24 Hours
I am designing an application where multiple users can be assign to a product for review. If a user doesn't have access to the product, they are not allowed to see it. I have attached my table design. All users are assign to a role. See attached screen
While building Report Model Solution in Business Intelligent Studio i am getting following errror- More than one item in the Entity 'table' has the name 'columns'. Item names must be unique among immediate siblings.
Please advice how to resolve this one. Thanks Ashwin.
I need to setup a script to read all the table names in the database above and then query the database to find the list of Stored Procedure using each table.(SQL Server)
helo all...,i want to make procedure like:examplei have table: item (itemid,itemname,stock)orderdetail(no_order,itemid,quantity)itemmoment(itemid,itemname,stock)item table itemid itemname stock c1 coconut 2 p1 peanut 2orderdetail tableno_order itemid quantity 1 c1 5itemmoment tableitemid itemname stock c1 coconut 0 p1 peanut 0 when customer paid, his quantity in orderdetail decrease stock in item table..so stock in item table became:itemid itemname stock c1 coconut -3 p1 peanut 2it's not good, because stock may not minus...so i want to move -3 to itemmoment table..so stock in item table became:itemid itemname stock c1 coconut 0 p1 peanut 2and in itemmoment table became:itemid itemname stock c1 coconut 3 p1 peanut 0my store procedure like:ALTER PROCEDURE [dbo].[orders]( @no_order as integer, @itemid AS varchar(50), @quantity AS INT)ASBEGIN BEGIN TRANSACTION DECLARE @currentStock AS INT SET @currentStock = (SELECT [Stok] FROM [item] WHERE [itemid] = @itemid) UPDATE [item] SET [Stock] = @currentStock - @quantity WHERE [itemid] = @itemid COMMIT TRANSACTIONENDit's only decrease stock with quantity. i want move stock minus from item to itemmoment..can anyone add code to my store procedure?plss.. helpp.thxx....
I 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 table 'stores' that has 3 columns (storeid, article, doc), I have a second table 'allstores' that has 3 columns(storeid(always 'ALL'), article, doc). The stores table's storeid column will have a stores id, then will have multiple articles, and docs. The 'allstores' table will have 'all' in the store for every article and doc combination. This table is like the master lookup table for all possible article and doc combinations. The 'stores' table will have the actual article and doc per storeid.
What I am wanting to pull is all article, doc combinations that exist in the 'allstores' table, but do not exist in the 'stores' table, per storeid. So if the article/doc combination exists in the 'allstores' table and in the 'stores' table for storeid of 50 does not use that combination, but store 51 does, I want the output of storeid 50, and what combination does not exist for that storeid. I will try this example:
'allstores' 'Stores' storeid doc article storeid doc article ALL 0010 001 101 0010 001 ALL 0010 002 101 0010 002 ALL 0011 001 102 0011 002 ALL 0011 002
So I want the query to pull the one from 'allstores' that does not exist in 'stores' which in this case would the 3rd record "ALL 0011 001".
I have a Product table with the columnsAcctNumProdCodeInvoiceDateI can have multiple rows for a given AcctNum:123 A 01/01/2005123 B 01/02/2005123 C 01/03/2005234 C 02/01/2004345 A 01/01/2005345 B 01/02/2005I need the max(InvoiceDate) and if the max for a given AcctNum is a ProdCode= B. So if the latest InvoiceDate is for a given AcctNum is B then returnthat row.123 B 01/02/2005Would not be returned because the max Invoice date for AcctNum 123 isProdCode C.345 B 01/02/2005Would be returned because the max Invoice date for AcctNum 345 is ProdCodeB.I can solve this using a cursor fairly easily by using a distinct AcctNum inthe cursor select and getting the max InvoiceDate for each AcctNum. This isa costly and I'm looking for a solution using temp tables or a query tohandle this problem.I hope I have made this clear enough (sorry if I was too verbose). Thanks inadvance for your help.-p
I need to find out some property of a tableAs following sql statement: select mtable.name,mcolumn.name,mtype.name,mcolumn.is_identity from sys.tables mtable, sys.all_columns mcolumn, sys.types mtype where mcolumn.object_id = mtable.object_id and mcolumn.system_type_id = mtype.system_type_id and mtable.name in ('TestTable') order by mtable.name,mcolumn.column_id
But there is no PK information. Follwing sql statement shows the PK name of a table, but no composite info(which fields): select mtable.name,mkey.name from sys.key_constraints mkey, sys.tables mtable where mkey.parent_object_id = mtable.object_id and mkey.type = 'PK' and mkey.name like 'TestTable'
How can I find which fields are PK in a table?thanks... -Winson
So randomly every 1 to 6 days queries start timing out and I'm almost positive it's from an improperly terminated transaction Is there a way to snoop this out the next time it happens? Like when a table's locked I can look and see yea this is the transaction it's in the middle of?
Find value match in SQL table Is there any application that can compare two tables and find the similarities (there is no high rate of exact match on the field values but there are similarities)?
Hi, I have table which stores the fund name and its data. We get quarterly information from the fund co. Suppose if the user wants to add a fund thats not in our database we let then add a ClientFundId and a FundName. But may be after sometime the fund company may add that fund in the next quarter.. So how do i get rid of Duplicated Data.. In the ClientFundId column we can a 9 letter Aplhanumeric or a 5 letter character but if the fund co.. provides those values the 5 letter characters are stored in Ticker column and the 9 letter words are stored in Cusip column.. So i just wrote this query hoping i could retrieve the duplicate values but it didnt list any..but i found one this is my query.. Select FundId, Cusip, Ticker, ClientFundId, FundName, ShortName From Fund Where
ClientFundId = Ticker or ClientFundId = Cusip Any help will appreciated Thanks Karen
Hello, I have 2 tables, and use objectdatasource and stored procedures, with sql server. Let say in the first table I have IDCustomer as a datakey, and other records, and in the second I have the same IDCustomer and CustomerName. I have an INSERT stored procedure that will create a new record in the first table (so generate a new IDCustomer value), and I would like to insert immediately this new value in the second table. How can I know the value of this new IDCustomer ? What is the best way to handle that ? Once the insert in the first table is done should read it the table and extract (with an executescalar) the value and then insert it in the second table ? This solution should work but I am not sure this is the best one. Thanks for your help.
I'd like to know if I can make one proc/cursor that I can pass only a table name and it will determine what cols exist in the table?
AND, what is the syntax for such a query?
I will be receiving data back where I will receive the accountID and only the deltas will have values all other columns will be null.
My proc will update an existing record, updating the specific col when an accountID exists, it will create a new record if the accountID does not exist.
I'd like to search all the cols getting their names and values when not null.
I can construct a proc for each table searching each col by known name.
However, I'd like to know if I can make one proc/cursor that I can pass only a table name and it will determine the cols?
All my tables start with cols AccountID and end with RecID with any number of cols inbetween.
TIA
JeffP... cross posted to ms.pub.mssqlserver.programming
Im trying to find a table and Drop in T-SQL using this script.
/* Start */ Use Students IF exists (Select * from information_schema.tables where table_name ='##Exams_result) drop table ##Exams_result go Create table ##Exams_result..............etc
/* end */
But I cant find my temporary table on this way... Any sugestion?
Luiz Lucasi lc@culting.com Rio de Janeiro - Brazil
