I have a table called Register that has the following in it
Policy_number, Policy_date_time, Portfolio_set, Status..The rule for the table is that the last record for each portfolio_set for a policy the status needs to be 'A' but there have been instances that the last record status is 'I'
I need to identify the record that is out of place..In the example below record number 2.
example
policy_number Policy_date_time Portfolio_set, status
12345 1/1/2011 1 I
12345 1/2/2011 1 A
12345 1/3/2011 1 I
12345 1/4/2011 1 I
I need to identify that the second record is in the wrong place...
i have a table name is HH tableit has two columns 'hhno' and hhname'HH tabelehhno hhname100 suresh101 baba103 rami want to insert a one record(102 , chandra) in HH table between(101,baba) and( 103 ,ram).how can i insert them please help ,me thanks
I run an ASP.NET website with a monthly contest. Members can enter 1 script for each contest. Scripts can then be voted on. There are three relevant SQL tables: Contest with the field: ContestIDScript with the fields: ContestID, ScriptID, and TitleScriptComment with the fields: ScriptID and Score Scripts are grouped by contest and come in place (first, second, third, etc.) based on the average of all the scores they received. I need a QUICK stored procedure that will accept ScriptID as a parameter and return back the place that script came in, in that particular contest. Currently, I get this information by creating a virtual table and populating it with joined data from Script and ScriptComment. I order the table by average Score desc. I then use a select statement against that table using the ScriptID and return back the row number. This works, but it is very slow. There are times where I might need to list 300 scripts and I need to do this lookup for each script to display what place they came in. I need a much faster way to get the place of any script in a contest. I think the Virtual Table is killing the speed. I have been trying to find a solution using a view or the new SQL 2005 command Row_Number(), but I have been unsuccessful. If anyone has a speedy solution, I would love to hear it. Thanks a lot, Chris Messineo
I tried checking to see if the point at which the reader was, that if it was the record I am looking for to go ahead and add the table data to a label. But for some reason it's only taking the first record in the database and not the one I thought I was at.[CODE] public void UpdateMaleHistLbl() { SqlConnection conn = new SqlConnection("Server=localhost\SqlExpress;Database=MyFamTree;" + "Integrated Security=True"); SqlCommand comm = new SqlCommand("SELECT * FROM FatherHistTable, MotherHistTable, UsersTable WHERE UsersTable.UserName = @usrnmeLbl ", conn); comm.Parameters.AddWithValue("@usrnmeLbl", usrnmeLbl.Text); conn.Open(); SqlDataReader reader = comm.ExecuteReader(); while (reader.Read()) { string usr = reader["username"].ToString(); usr = usr.TrimEnd(); string pss = reader["password"].ToString(); pss = pss.TrimEnd(); if (usrnmeLbl.Text == usr) { if (hiddenpassLbl.Text == pss) { maleHistLbl.Text = reader["GG_Grandfather"] + " > "; maleHistLbl.Text += reader["G_Grandfather"] + " > "; maleHistLbl.Text += reader["Grandfather"] + " > "; maleHistLbl.Text += reader["Father"] + " > "; maleHistLbl.Text += reader["Son"] + " > "; maleHistLbl.Text += reader["Grandson"] + " > "; maleHistLbl.Text += reader["G_Grandson"] + " > "; maleHistLbl.Text += reader["GG_Grandson"] + "<br /><br />"; } } break; //exit out of the loop since user found } reader.Close(); conn.Close(); }}[/CODE]Thanks in advance
OKay looking at this highly simplified query here is my problem. We have a ton of Asset numbers that are the same and for each asset number the inv_id should also be the same.....
However, since this data gets entered by humans - sometimes the inv_id is wrong.... I need to figure out a query that fleshes out the rows where asset_nums are the same but inv_id is different than other rows...?
Of course this may be impossible..... I mean what if there was only two records by the same asset_num and each inv_id was different? How could the query know which was the right one......
I need to run a report that finds the invalid inv_id's so that they can corrected.....
any thoughts on how this is done?
My thoughts are - using an inner select... something to the effect of :
SELECT .... FROM (SELECT * WHERE (inv_id = inv_id) > 1) GROUP BY asset_num.........
I just dont know exactly what the correct syntax is....
muchas gracias....
(btw this is actually ms-sql but mysql forum is so much more popular and the syntaxes are similar enough....thank you...)
sno Empid Address Date ------------------------------------------------ 1100bhopal2008-01-02 00:00:00.000 2100indore2008-02-02 00:00:00.000 3100gwalior2008-03-03 00:00:00.000 4101gwalior2008-01-02 00:00:00.000 5101Indore2008-02-02 00:00:00.000 6102bhopal2008-01-01 00:00:00.000 ------------------------------------------------ I want last update Address of employee from this table like
I have a mystery database on my hands...it is part of an application that my company bought, and it uses SQL server for its backend. The reporting features built in are not good enough, so I need to write some queries by hand...trouble is I am having a hard time figuring out how the schema works...using the front end they gave us I put a value of "12345" for a field I need to get to, but I can not locate where in the db it gets stored....can anyone tell me a way to query that will look at every single record and every single field in the db to find the value "12345"??
Hello I need an alarm or raise an event from SQL Server after updating to get it in .net. I use SQL Server 2005 Express Edition and vb.net2 for my programming language. Suppose that in a windows form I have a grid and I'm working with this page another client is working with this same page .He is editing the information of a record of a grid and save it in database so I want to give an alarm or an event that raise from SQL Server (for example in a trigger) , what can I do? Thanks.
Is there a way to tell who or what process deleted a record in a SQL table.I know you can setup triggers or Profiler - but is there a way to see how arecord was deleted if you DO NOT have a trigger or Profiler already running?This is SQL 2000 Standard, sp3a with default options and configuration.Thanks
I have a select statment that gives me an error, cannot go more than rowsize 8094. I am trying to find the particualr record/acctno, where the rowsize is excedding the max limi of 8094 chars. Once I have the acctno, I will correct it. But how do I find which acct has this issue? Bascailly how can I find which record size is more than 8094 chars?
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
I need to find all items in an inventory table where a field has been unticked in the last 24 hours (there is no audit trail), as well as the contract number of the contract it has been added to (it will not have existed in that table before).
These are the two table querys in their basic form:
select item (nvarchar(20)), inactive (bit) from inventory
I have a table with member information. Each member has multiple effective dates. I would like to query to find the record for each member that has the maximum effective date. A sample table is below:
insert into #temp (Cabstamp,account,Document, origin, debit, credit, datalc) select 'ADM12345',111,'CMP-01','FO',1000,0, '20150110' union select 'ADM12345',112,'CMP-01','FO', 500, 0,'20150110' union select 'ADM12345',6811,'CMP-01','DO',0,1500,'20150110' union
I have 2 tables .Lets Say tableA and tableB.Both Have Columns ClaimNumber,Amount. Now, to get the matched records for these 2 tables, i wrote the following query Select * from tableA A Inner Join tableB B on A.ClaimNumber = B.ClaimNumber and A.Amount = B.Amount This query works perfectly fine and gives me only matching records, however if i want to have records which match with ClaimNumber and not with Amount i wrote something like this
Select * from tableA A Inner Join tableB B on A.ClaimNumber = B.ClaimNumber and A.Amount <> B.Amount.
And this query produces wrong results, its giving me match and also non match records.
Looking to see if thier is a better way to find the last record entered in a group of records.
What I'm doing now is finding the max for the secound column and then doing a sub query to find the max of the third column having the second columns equal.
Table example using simplied data.
PolId
CoveragId
EffDate
Status
Limit1
2
1
9/7/2007
a
10000
2
2
9/7/2007
a
150000
2
2
10/1/2007
a
200000
3
1
9/7/2007
a
10000
The parent program addes a row every time the data is changed. To make things worst; the records arn't always in sqenal order like the above table and some time edits the row instead.
The current query returns a single value. from a single table.
Current code used in the select protion on a larger query. bpi = basicpolicyInformation.
( Select c1.limit1 From AFW_Coverage as c1 Where c1.PolId=bpi.PolId and c1.CoverageId = (select max(CoverageId) as CoverageId From AFW_Coverage as c where c.PolId = c1.PolId and c.CoverageCode = 'Dwelling' and status <> 'D' ) and c1.effDate = (select max(Effdate) as Effdate From AFW_Coverage as c where c.PolId = c1.PolId and c.CoverageID = c1.CoverageId )
Explain the current code. It uses the two sub queries to find the correct record ID that has the data needed.
ChangeID ChangeDate EquipmentID ModuleID EquipStatus 1 12/9/08 230 1789 Normal 2 13/9/08 450 1245 Normal 3 17/9/08 230 1789 Open 4 21/9/08 230 1899 Open 5 21/9/08 450 1674 Normal 6 22/9/08 450 2364 Normal
Given a date, what module was each equipment item in on that date?How do I get the date of the nearest previous event from a list like this? I got a query from one of the post in this Forum only using Cross Apply to find the nearest record from the above table based on Date i.e.
SELECT outerT.* FROM your_table AS outerT CROSS APPLY ( SELECT TOP 1 equipment_id , change_date FROM your_table AS innerT WHERE innerT.change_date <= @point_in_time AND innerT.equipment_id = outerT.equipment_id ORDER BY change_date DESC ) AS applicable_records WHERE applicable_records.change_date = outerT.change_date
The problem is I need to get this query without using Cross Apply as i need to use the same for the LINQ which doesn't support Cross Apply.
The "Last" function in the query below (line 4 & 5) is not exactly what I'm after. The last function finds the last record in that table, but i need to find the most recent record in the table according to a date field.
Code: SELECT tblinmate.statusid, tblinmate.activedate, Last(tblclassificationhistory.classificationid) AS LastOfclassificationID, Last(tblsquadhistory.squadid) AS LastOfsquadID, tblperson.firstname, tblperson.middlename, tblperson.lastname,
[Code] ....
The query below finds the most recent record in a table according to a date field, my problem is i dont know how to integrate this Query into the above to replace the "Last" function
Code: SELECT a.inmateID, a.classificationID, b.max_date FROM ( SELECT tblClassificationHistory.inmateID, tblClassificationHistory.classificationID,
--acts as a transaction table CREATE TABLE #TestData ( id int not null identity(1,1) primary key, account varchar(10) not null, deposit int not null
[Code] ....
--desired results -- within each account group, when a individual record causes the groups running total to exceed the group's budget, show the difference that causes the groups running total to exceed the budget as unbudgeted. The amount of that transaction upto the budget amount show as renewal. For any succeeding individual records in the group, the amount of that transaction is Unbudgeted.
I seem to be able to see where a procedure is being recompiled, but not the actual statement that was executing the procedure.
Note, with 2008 there is a DMV called dm_exec_procedure_stats , which is not present in 2005
USE YourDb;
SELECT qt.[text] AS [SP Name], qs.last_execution_time, qs.execution_count AS [Execution Count] FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt WHERE qt.dbid = DB_ID() AND objectid = OBJECT_ID('YourProc')
The above shows results that include the CREATE PROCEDURE statements for the procedure in question, but this only indicates that the procedure was being recompiled, not necessarily that it was being executed?
Currently I have the following stored procedure which simply adds a new row in my SQL Express 2005. What I want is that -1). before inserting the record find out the new ID (primary key) value. (ID is automatically a sequential integer generated by SQL Server)2). and set COMPANY_ID = (new) ID Any thoughts? Thanks ALTER PROCEDURE usp_tbl_Company_Insert @Company_ID int, @Name varchar(200), AS<FIND THE NEW ID of the new row in the database> @Company_ID = (new ID) INSERT INTO tbl_Company (Company_ID, Name,)VALUES (@Company_ID, @Name)
I have a table full of service invoice records. Some of the invoices are continuous, meaning that there may be an invoice from 01-16-2015 through the end of that month, but then another invoice that starts on feb 1 and goes for 6 months.
I want to only pull the most recent. Keep in mind that there may be other invoices in the same table for a different period. An example might be:
I have the table with the similar set of records which mentioned below, find the time difference between two rows of record. By Using the MsgOut column i have to find time taken b/w PS & PV and some record doesnt have PV .
Can anyone give me any input on this. Recently TempDB one of my production server came down because tempDB got so big that it chewed up all the space in it's drive. My TempDB was in drive C:, where the Operating system and the rest of the SQL systems databases are(msdb,model,master). The actual production data are located in another logical RAID 5(Drive E:) Drive. I want to prevent the problem from happening again. Is it wise or does it degrade performance if i move TEMPDB from drive C: to drive E:? Is this going to cause a major bottom neck in drive E:, where the data are located?
what happens if the physical location of a box(which had sql server 2000 on it) is chaned. what happens to the replication and distributed queries. Thanks.
Where would i place an orderby my DateCreated field...everywhere i try to place it i get this error... The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified. Any help with this issue would be greatly appreciated....
CREATE PROCEDURE GetTimeCard
( @LoginID nvarchar(50),
@DateRangeFrom datetime,
@DateRangeTo datetime
)
AS
BEGIN
IF ( @DateRangeFrom = '1/1/1753' ) AND ( @DateRangeTo = '1/1/1753' )
join OPS_TimeCardHours tce on tc.TimeCardID = tce.TimeCardID
join OPS_Employees oe on oe.EmployeeID = tc.EmployeeID
where oe.LoginID = @LoginID
group by tc.TimeCardID, tc.DateCreated ,oe.FirstName,tc.DateEntered, oe.LastName ) x
left outer join (
select tc.TimeCardID, tc.DateCreated,tc.DateEntered, sum(tce.ExpenseAmount) as TotalExpenses
from OPS_TimeCards tc
join OPS_TimeCardExpenses tce on tc.TimeCardID = tce.TimeCardID
join OPS_Employees oe on oe.EmployeeID = tc.EmployeeID
where oe.LoginID = @LoginID
group by tc.TimeCardID, tc.DateCreated,tc.DateEntered ) x1 on x1.TimeCardID = x.TimeCardID and x1.DateCreated = x.DateCreated and x1.DateEntered = x.DateEntered
join OPS_TimeCardHours tce on tc.TimeCardID = tce.TimeCardID
join OPS_Employees oe on oe.EmployeeID = tc.EmployeeID
where oe.LoginID = @LoginID
group by tc.TimeCardID, tc.DateCreated, tc.DateEntered) x2 on x2.TimeCardID = x.TimeCardID and x2.DateCreated = x.DateCreated and x2.DateEntered = x.DateEntered
join OPS_TimeCardHours tce on tc.TimeCardID = tce.TimeCardID
join OPS_Employees oe on oe.EmployeeID = tc.EmployeeID
where oe.LoginID = @LoginID And tc.DateCreated BETWEEN @DateRangeFrom AND @DateRangeTo
group by tc.TimeCardID, tc.DateCreated ,oe.FirstName,tc.DateEntered, oe.LastName ) x
left outer join (
select tc.TimeCardID, tc.DateCreated,tc.DateEntered, sum(tce.ExpenseAmount) as TotalExpenses
from OPS_TimeCards tc
join OPS_TimeCardExpenses tce on tc.TimeCardID = tce.TimeCardID
join OPS_Employees oe on oe.EmployeeID = tc.EmployeeID
where oe.LoginID = @LoginID
group by tc.TimeCardID, tc.DateCreated,tc.DateEntered ) x1 on x1.TimeCardID = x.TimeCardID and x1.DateCreated = x.DateCreated and x1.DateEntered = x.DateEntered
join OPS_TimeCardHours tce on tc.TimeCardID = tce.TimeCardID
join OPS_Employees oe on oe.EmployeeID = tc.EmployeeID
where oe.LoginID = @LoginID
group by tc.TimeCardID, tc.DateCreated, tc.DateEntered) x2 on x2.TimeCardID = x.TimeCardID and x2.DateCreated = x.DateCreated and x2.DateEntered = x.DateEntered
I have a design that includes articles that will be searched. Obviously its too slow to put them into fields, and impossible because some have photos or are otherwise html documents. So I want to put pointers to their location.
Two questions. For each deployment, both desktop and web, where is the best place to put the articles. In any folder, or only in an iis virtual folder?
