123 A 01/01/2005
123 B 01/02/2005
123 C 01/03/2005
234 C 02/01/2004
345 A 01/01/2005
345 B 01/02/2005
I 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 return
that row.
123 B 01/02/2005
Would not be returned because the max Invoice date for AcctNum 123 is
ProdCode C.
345 B 01/02/2005
Would be returned because the max Invoice date for AcctNum 345 is ProdCode
B.
I can solve this using a cursor fairly easily by using a distinct AcctNum in
the cursor select and getting the max InvoiceDate for each AcctNum. This is
a costly and I'm looking for a solution using temp tables or a query to
handle this problem.
I hope I have made this clear enough (sorry if I was too verbose). Thanks in
advance for your help.
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)
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 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
does someone have a querry to display the duplicate records in a table.
Table: zipcode dma
My data upload is failing because there is a primary key on zipcode and the source data (42k records) has about 50 duplicate zipcode records in it. It is possible that there is a unique combo of zipcode / dma but I need to identify the duplicate records to determine that.
How can i find out the no of rows in each table. if the no of table are more than thousands(i.e. 1000+,2000+ etc..) are there. Certainly 'select count(0) from <table>' is not the good idea. Egarly awaiting for the solution.
I need to find the primary key of a table, in MySQL i used SHOW COLUMNS and looped through them to find which one was primary if any. The MSSQL equivalent is SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = 'table_name' apparently. However the result doesnt give me any key information. How can i find out 1. if a primary key exists on a table 2. what column that primary key exists on
I have very large sql server with 15 databases and thousands of tables. We have an "employee" table where we have historically been joining to the "EmployeeName" field in procs, views, etc.(bad practice, I know). I would like to now go back and make things right. I would like to identify ALL procs and views that have a join on the "EmployeeName" field, and modify it to use the "employeeid" field. I would like a script or a suggestion that would help me identify all the places where I would need to make this change.
I thought of querying the syscomments table, but the joins are not always laid out the same way so I know I wouldn't be able to catch all of them. Maybe using profiler to capture all statements executed and have them trigger an email to a DB developer every time? I don't know... Suggestions?
I have this script bellow which does what it is supposed to. However it only outputs the cust_id. I want it to show all the columns in the table. How would I do this?
SELECT cust_id FROM cust_table WHERE cust_name in ('Billy','John') and rownum < 100 GROUP BY cust_id HAVING COUNT(*) > 1;
I have a warehouse table but I don't know which query will update warehouse inside of information ? Thus, how to write a query list all query have include this warehouse table name in there ?
Hi Friends, is it possible to find a table in which database it is? ex: i have one table name rider. i've created it in one database, but i dont know in which database it is.but i know the server name.
Env: SQL Server 2000The following sql stmt seems to find a particular table's sizeprogrammatically:select top 1 [rows],rowcntfrom sysindexeswhere ID = object_id('aUserTable')and status = 0and used > 0However,a) I'm not 100% sure of its consistency;b) Both [rows] col and [rowcnt] col seems to produce same data, whichone is supposed to be more accurate (or more up to date)?TIA.
I have created a report as shown below using OLTP tables.
No Description CA PA Variance
10101 Bank of Tampa 388353.69 208048.92 87%
10104 Wachovia Operating 275933.37 - 0%
10105 Wachovia Payroll 217.95 - 0%
10201 Accounts Receivable 16735.60 34829.14 -52%
20002 Account Payable -175919.72 -106018.68 66%
Now I want to insert total in the middle of the table, like I want to display: Total of CA when No in (10101,10104,10105) as 'Total Cash' then I want to display total of 'Total Cash' + 'Accounts Receivable'(10201) as 'Cash & Receivables'
acutally due to certain reason i have changed the few tables name in my database, but now i m facing problem in my software, i have alot of procedures and function as well in my database and these are using old table name, Is there any script that i can use to replace the name of the tables. i have checked few procedure/function in which i use table name with different style like [dbo].[tablename] and in some places i just use only [tablename].
