SQL Statement To Find The The Maximum Record
Oct 12, 2007
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:
MEMBERID EFECTIVEDATE
------------ ------------------
699361401 20070101
699361401 20070501
732612701 20070101
732612701 20070501
575424301 20070101
575424301 20070501
192939801 20070101
192939801 20070501
458645001 20070101
458645001 20070501
View 4 Replies
ADVERTISEMENT
Jun 3, 2004
I'm using replication between two server, but I don't know the maximum record will be move from Database 1 on Server1 to Database2 on Server2 in per minute.please help me, thanks!
View 2 Replies
View Related
Jun 27, 2007
Does anyone know of a built-in function to return the maximum possiblevalue of a given datatype? I have to return the biggest value for asmalldatetime or datetime in a view if the field is null, but can'tfind such a function. The closest I've come is:select datalength(cast(getdate() as smalldatetime))....but that only return the number of bytes, not the value itself,which is '6-6-2079 11:59'I know I could create my own lookup table and function, but I washoping that Transact-SQL would have a built-in solution--John Hunter
View 3 Replies
View Related
Feb 19, 2012
My db contains two table Employee_detail and Student_Detail.Employee_detail has Emp_id and Student_Detail has stud_id.Now my problem is that i want to get one maximum no from these two table.
View 11 Replies
View Related
Dec 19, 2007
Hi All,
Here is my story, how to change a column called Flag_Status based on the maximum Updated date. i.e. i want to make Flag_Status be 1 for the records which have maximum Updated_date (current record) and the rest to make it 0. for example accountID 1 has three records updated, but only one is current the rest are historical, thus i want the history record to be Falg_status 0 and the current record be 1. Note that Inserted_Date and Updated_Date are created using SSIS Derived column During loading the source table, it helps me when each record is inserted into the Data warehouse.
Here is My source table Name: Source_table,
CREATE TABLE dbo.Source_table
(
AccountID INT PRIMARY KEY,
Price int,
Address varchar(30),
added DATETIME DEFAULT GETDATE(),
edited DATETIME DEFAULT GETDATE(),
Flag_Status Int Default 1
)
GO
Here is the Fact_table
CREATE TABLE dbo.Fact_table
(
Fact_table_Key Int Identity (1, 1) NOT NULL,
AccountID INT,
Price INT,
Address varchar(30),
added DATETIME DEFAULT GETDATE(),
edited DATETIME DEFAULT GETDATE(),
editor VARCHAR(64),
Flag_Status Int Default 1,
Inserted_Date DATETIME DEFAULT GETDATE(),
Updated_Date DATETIME DEFAULT GETDATE()
)
GO
Source_table:
AccountID Price Address added edited Flag_Status
---------------- --------- ------------- ----------- ------------- ------------------
1 10 xyz 01-2006 01-2006 1
2 14 abc 02-2006 02-2006 1
3 13 mno 03-2006 03-2006 1
Here is the fact table, table name= Fact_table
Fact_table_Key AccountID Price Address added edited Flag_Status Inserted_Date Updated_Date
-------------------- ------------- --------- ------------ ------- --------- ---------------- ------------------ -------------------
1 1 10 xyz 01-2006 01-2006 1 05-2006 NULL
2 2 14 abc 02-2006 02-2006 1 05-2006 NULL
3 3 13 mno 03-2006 03-2006 1 05-2006 NULL
4 1 17 ght 01-2006 06-2006 1 NULL 08-2006
5 2 18 dmc 02-2006 07-2006 1 NULL 08-2006
6 3 20 kmc 03-2006 09-2006 1 NULL 10-2006
7 1 19 xyz 01-2006 11-2006 1 NULL 12-2006
8 2 19 klm 02-2006 01-2007 1 NULL 02-2007
9 3 21 pqr 03-2006 03-2007 1 NULL 04-2007
Here is what i am thinking: But it gives me Wrong Flag_Status.
UPDATE Fact_table
SET Flag_Status =
CASE
WHEN (SELECT Max(Updated_Date) From Fact_table
WHERE AccountID IN (SELECT AccountID FROM Fact_table
Group By AccountID Having Count(AccountID)>1)) >
(SELECT Max(edited) From Source_table
WHERE Flag_Status = 1)
THEN 1
WHEN (SELECT AccountID From Source_table
Group By AccountID
Having Count(AccountID) =1) =
(SELECT AccountID From Fact_table
WHERE Updated_Date IS NULL)
THEN 1
ELSE 0
END
View 12 Replies
View Related
Jun 13, 2013
How to select the orderID, order date and the maximum-price unit of each order in the below tables in 2 ways:
1-using correlated sub-query
2-not using correlated sub-query
I only selected orderID, order date and the maximum price of each order but not the unit name.This is my query:
Code:
SELECT dbo.[Order Details].OrderID, Max(dbo.[Order Details].UnitPrice)as MaxUnitprice, dbo.Orders.OrderDate
FROM dbo.[Order Details] INNER JOIN
dbo.Products ON dbo.[Order Details].ProductID = dbo.Products.ProductID INNER JOIN
dbo.Orders ON dbo.[Order Details].OrderID = dbo.Orders.OrderID
GROUP BY dbo.[Order Details].OrderID, dbo.Orders.OrderDate
View 6 Replies
View Related
Feb 17, 2006
Hello
Can anyone tell me what the maximum length sql statement SQL CE can run is?
Thank you
Gráinne
View 5 Replies
View Related
Mar 24, 2007
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
View 1 Replies
View Related
Feb 24, 2007
Hello friends,
I am sorry to be posting this message in this forum. But thought if anyone does have any idea abt my problem, which i illustrate below.
I want to know what is the maximum number of columns that an INSERT statement can handle?
I have come across a situation where i need to insert 15 values in a table.
INSERT INTO myTable (col1, col2, col3,....., col15) VALUES (v1, v2, v3,...., v15).
Actual statement:
INSERT INTO BillSell (bnumber, stock, qty, price, tprice, seen, bill_id, brokerage, service_tax, brok_st, stt, brok_st_stt, total_cost, total_rate_pu, check)
VALUES ('ICICI 06-12-2004 reshma', 'RELIND', 10, 541.7375183271872, 5417.375183271873, 'no', 2402, 40.2375, 4.92507, 45.162569999999995, 6.771718979089841, 51.934288979089835, 5365.0, 536.5, 'check')
When I call this in Java program, it asks me to check the syntax of the statement as follows :
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'check) VALUES ('ICICI 06-12-2004 reshma', 'RELIND', 10, 541.7375183271872, 5417.' at line 1
Please make notice that all the data types of the table columns and the values passed in the statement match.
Now the problem is that, when from the above INSERT statement, i remove the last column (i.e. the "check" column and its corresponding value passed i.e. 'check'), the code works fine. I observe here that now there are 14 columns in the statement.
So i need the solutions for this. I cannot move further in the program......
Please help.
Trust in Technology mate .....'Not in Human Beings'
View 3 Replies
View Related
Jan 8, 2002
How many characters can be used in a query?
Say I have a query that inserts into a varchar(4000). I need to do ~100 of them. Can all this data be passed at once. Or would I need to break it up?
Thoughts?
Dano
View 1 Replies
View Related
Nov 29, 2014
I am new to SQL but have managed to create a table with five columns. The problem I am having is when I try and run the INSERT command I get an error "Maximum characters exceeded in SQL statement"
My table code is:
create table myemployees_MPA0510
(FirstName Varchar (15), LastName
varchar (20), Title varchar (25),
Age number (3), salary number (9));
And my INSERT code is:
Insert into myemployees_MPA0510
(First, Last, Title, Age, Salary)
Values ('Jonie', 'Weber', 'Secretary', 28, 19500.00) ('Potsy', 'Webber', 'Programmer', 32,
45300.00) ('Dirk', 'Smith', 'Programmer II', 45 75020.00) ('Mark', 'Aldridge' 'Technical', 52,
12000.00) ('Peter', 'Wright' 'Admin' 30, 11000.00) ('Lucy', 'May', 'Technical', 15500.00)
('Robert', 'Hurst', 'Finance', 54, 16000.00) ('Ann', 'Green', 'HR', 43, 21000.00);
View 2 Replies
View Related
Mar 22, 2007
Code:
"SELECT " & _
"Asset_Number, Inv_ID " & _
"FROM " & _
"tbl_Asset "
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...)
View 2 Replies
View Related
Apr 14, 2008
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
100 Gwalior
101 Indore
102 Bhopal
pls help me out ...............
Yaman
View 2 Replies
View Related
Mar 20, 2007
hi all,
if we have 100 same records, how can we select any 50 of it? thanks
~~~Focus on problem, not solution~~~
View 20 Replies
View Related
Sep 12, 2015
I have some code I build 2 weeks ago which I’ve been running daily but it’s suddenly stopped working with the following error.
“The table "tbl_Intraday_Tmp" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes. INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit” When I google this there seems to be a related to tables with vast numbers of columns.
My table tbl_Intraday_tmp is relatively small. It has 7 columns. 1 of varchar(5), 3 of decimal(9,3) and 2 of decimal(18,0). The bit I’m puzzled with is it was working and stopped.
I don’t recall changing anything but I wouldn’t rule that out. I ‘ve inspected the source files and I don’t believe they have changed either.
DECLARE
@FileName varchar(50),
@Path varchar(50),
@SqlCmd varchar(1000)
= '',
@ASXCode varchar(5),
@Offset decimal(18,0),
[code]....
View 5 Replies
View Related
Apr 20, 2012
I am using MS SQL server 2008, and i have a table with 350 columns and when i m trying to create one more column its giving error with below message -
Warning: The table XXX has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes.
INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.
how can i resolve this?
View 14 Replies
View Related
Aug 14, 2001
Is there any method so that I can find that the particular user has changed the record in the particualr table..?
View 1 Replies
View Related
Feb 8, 2005
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"??
View 14 Replies
View Related
Oct 15, 2013
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...
View 4 Replies
View Related
Sep 23, 2007
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.
View 3 Replies
View Related
Jul 23, 2005
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
View 2 Replies
View Related
Dec 11, 2007
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?
View 1 Replies
View Related
Oct 29, 2014
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
View 3 Replies
View Related
Apr 2, 2015
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
select item (nvarchar(20) , contract (nvarchar(20)) , original_start_rent (datetime) from deltickitem
I would like to see just the item number and the contract number it has been added to.
View 11 Replies
View Related
Sep 18, 2015
I need to build a CTE query to find for the same Cabstamp (document) where i have different Origin.
I know that i can build this with a correlated subquery, but i´am curious about using CTE.
I post sample create Script:
create table #temp (Cabstamp varchar(10), account varchar(10),document varchar(15), origin varchar(2), debit numeric(10,2), credit numeric(10,2), datalc datetime)
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
[code]....
View 5 Replies
View Related
Jul 15, 2015
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.
how to write a query for my non match condition?
View 5 Replies
View Related
Oct 1, 2007
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.
View 16 Replies
View Related
Oct 13, 2012
Table :
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.
View 1 Replies
View Related
Sep 19, 2015
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,
[Code] .....
View 1 Replies
View Related
Mar 3, 2015
I have the following tables:
--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.
Here's the SQL I have thus far...
SELECT a.id
,a.account
,a.deposit
,a.total
,a.budget
,case when a.total <= a.budget
[Code] ....
View 4 Replies
View Related
Jul 15, 2015
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?
View 3 Replies
View Related
Nov 27, 2006
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)
View 1 Replies
View Related
Feb 23, 2015
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:
FromDate ToDate Customer Number Contract Number
02/01/2015 07/31/2015 2555 456
01/15/2015 01/31/2015 2555 456
04/01/2013 09/30/2015 2555 123
03/13/2013 03/31/2013 2555 123
From this table, I would like a query that would give me this result:
01/15/2015 07/31/2015 2555 456
03/13/2013 09/30/2015 2555 123
There will likely be more than just 2 consecutive records per contract number.
View 4 Replies
View Related
