Getting The Latest Dated Row For Each User In A Table
Dec 2, 2005
Hi all,
I want to have a query that will return me a single row for each user
from a table where the table has many rows for each user. The single
returned row for each user must be the most recently dated entry
([I7-Change-Date]) for that user.
An example of the code I have so far is as follows, but it obviously doesn;t work.select DISTINCT([I1-Customer-Ref]) AS Cust,([i7-w-fixed-amnt]) as WaterFixedAmt, ([i7-w-rv-amnt]) as WaterRVAmt, ([i7-s-fixed-amnt]) as SewerageFixedAmt, ([I7-Change-Date]) AS [Date]from r07UnMeasuredBills ORDER BY Cust, [I7-Change-Date] DESC
I am using MS SQL Server 7 for this.
Thanks
Tryst
View 6 Replies
ADVERTISEMENT
Apr 23, 2007
Hi!
I would like to select the 10 latest logged in users and then display them in a gridview-control....
But Iam not good at SQL, so I would really appreciate if someone could help me to write the select-part for this!!
(I use the database 'ASPNETDB.mdf', which is automatically created by VVD 2005 Express Edition)
View 2 Replies
View Related
Apr 2, 2008
Hi
Im in a bit of a pickle due to a large amount of data in a table and the query Im running is taking a very long time, so Id like to get some advice from the guys here to see if it can be done another way.
Ok, so heres the background. I have a table, see desc below, that contains transaction data for a number of users. Each user may have more than one transaction. In each row the SEQUENCENUMBER column is populated with a timestamp for the transaction, so I was using this to get the latest. However there are over 1 million rows in the table and the query below has taken over 12 hours so far to run. Has anyone any ideas on how I can make life a little easier for myself here?
SQL Query
Code:
select user,recipient,sequencenumber from trans_database where user IN (select distinct(user) from trans_database)
order by sequencenumber desc limit 1;
Table Layout
Code:
ID int(10) unsigned (NULL) NO PRI (NULL) auto_increment select,insert,update,references
RECIPIENT varchar(5) latin1_swedish_ci NO select,insert,update,references
DONOR varchar(5) latin1_swedish_ci NO select,insert,update,references
USER varchar(20) latin1_swedish_ci NO select,insert,update,references
UPDATETYPE varchar(10) latin1_swedish_ci NO select,insert,update,references
REPFLAG varchar(1) latin1_swedish_ci NO select,insert,update,references
OPFIELD varchar(20) latin1_swedish_ci NO select,insert,update,references
SEQUENCENUMBER varchar(18) latin1_swedish_ci NO select,insert,update,references
REQUESTDTS varchar(20) latin1_swedish_ci NO select,insert,update,references
PROCESSED varchar(1) latin1_swedish_ci YES (NULL) select,insert,update,references
View 2 Replies
View Related
Sep 1, 2006
For performance issue, I believe many program should have a house-keeping procedure to clean up transaction history. Is there any best practice to perform this? Or should it be done simply by moving transaction data from the transaction table into a history table? Any better or consideration that I should be concerned of?
View 1 Replies
View Related
May 20, 2008
Im trying to create a line graph for displaying the number of page views, in this instance for the last 7 days, problem is I would like to dispaly the graph as the last 7 days, so for today, tuesday would be the last day to hold a value on the graph and the six previous would be monday through wednesday descending, currently the values for the graph are thrown in by the day where monday always shows in the same position in the graph which is not that great,, (hope that makes sense)
If I could get the values out of the database in the right order I can use them to display in the graph as like,
Currently this is not as as I like,
SELECT COUNT([Day]) as num,[Day],dDate FROM ListingStats
WHERE iListingID=12
AND (dDate <= GetDate() AND dDate >= DateAdd(d,-7,GetDate()))
GROUP BY [DAY],dDate
ORDER BY [DAY] ASC
where the date is a datetime field, can I trunc this to get just the date so this current format will work? day represents an Enum of the day of the week 1=monday etc
View 10 Replies
View Related
Jul 4, 2015
I have a matrix report with STORES in the row group and DATES in the column group. The table sums on SALES. The DATES column is formatted like =format(Fields!DATES.Value, "MMM yyyy"). The table also has 2 parameters @Start and @End. This all works great but I then added a child report so that the user can click on the SALES value for any sale by month and store. The child report uses the @Start and @End parameters from the original report but this is where I run into problems.
Rather than bringing me the sales details for a particular store and month it brings back everything from the time period selected with the original date parameters. So say I originally selected 2015-01-01 to 2015-06-30 with the parameters when I select on FEB 15 in my matrix report I get Febs data along with all the other months ie Jan-Jun 15. The DATES fields in both reports are in the same date format - in fact both reports use exactly the same dataset.
I realize it's something to do with the formatting of the DATE field not being recognized in the linked report.
View 5 Replies
View Related
Jan 21, 2014
I have a data column named Date_In with format of 20-May-13. How can I filter the data to select based on year only. I used
Select
*
From apps.xdmc_852@erpprod
Where Year(Date_in) = 2014
This code is not working. I also need a way to automatically update to next week range. For example, last week I used where function like this
Where Date_in = to_date('01/12/2014','MM/DD/YYYY')
Is there a way it will automatically at the next 7 days to the code above when the date is available. This week I have to go back and changed the code in to this:
Where Date_in = to_date('01/19/2014','MM/DD/YYYY')
View 3 Replies
View Related
Mar 5, 2007
How to modify the below procedure in order to get the latest date ( the biggest value ) in the column “Deadline�, type smalldatetime, ( example: 24/02/2007 00:00:00 ) for a given customer “UserName� ?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE PROCEDURE GetLatestDate
(@UserName VARCHAR(50)
AS
SELECT Deadline
FROM CustomerItems
WHERE UserName = @UserName
View 2 Replies
View Related
Oct 4, 1998
I think you experts must know how to do this.. I just can`t find in any book a good example or related information....
1) I have a time-logged table like this
Product Status Datetime Where
A Received 1/1/90 23:00 Chicago
A Sold 1/7/90 08:30 New York
A Returned 1/8/90 04:30 Paris
B Bought 1/12/91 06:39 Dallas
B Received 1/13/91 04:30 Chicago
B Returned 1/14/91 14:23 Chicago
C SendOut 1/15/91 07:45 Chicago
D Bought 1/25/92 06:30 London
etc..
All A`s are the same unique product as B and C are too, above is thus a history log of each product item. The records are not updated, just insertions only. (a DELETE on some record in time is likely, but no UPDATES...) I use the history log for several analisys purposes
2) Now, I want by one single SQL query get ONLY latest status of all products,
Like this (No I do not want to keep a separate "status" table each time I insert to the above table)
Product Status Datetime Where
A Returned 1/8/90 04:30 Paris
B Returned 1/14/91 14:23 Chicago
C SendOut 1/15/91 07:45 Chicago
D Bought 1/25/92 06:30 London
how to do such QUERY. It tried many things without exactly what I need.
I am not a good SQL programmer as U may know now..
the QUERY also should run on MS-ACCESS MDB
3) Replication
The history table is updated by each location and SQL server must keep all table in same condition using replication. Anybody has a suggestion how to setup replication in SQL server for this. It means each insertion into the log is send to each individual site. Can I publish to the same table from each site to each site , so that each change in the log gets reflected in the other site`s history table ?
Thanks a lot !
View 2 Replies
View Related
Mar 17, 2015
I have a table where i am inserting into temp table, I mean selecting the records from existing table. From this how can i get latest records.
create table studentmarks
(
id int,
name varchar(20),
marks int
)
Insert into dbo.studentmarks values(1,'sha',20);
[Code] ....
How to write a sql query to get the below output
studentname totalmarks
sha 90
hu 120
View 1 Replies
View Related
Jul 20, 2005
Dear All,Suppose in the program a record is added to a table whoseprimary key is a identity field. If I really want to get the lastestvalue for that field after the insertion, is it the best way to useIDENT_CURRENT() to obtain this value?Thanks for your kind attentionYours faithfully,Benny
View 2 Replies
View Related
Sep 24, 2007
I have two table, tblCharge and tblSentence, for each charge, there are one or more sentences, if I join the two tables together using ChargeID such as:
select * from tblCharge c join tblSentence s on c.ChargeID=s.ChargeID
, all the sentences for each charge are returned. There is a field called DateCreated in tblSentence, I only want the latest sentence for each charge returned, how can I do this?
I tried to create a function to get the latest sentence for a chargeID like the following:
select * from tblCharge c join tblSentence s on s.SentenceID=LatestSentenceID(c.ChargeID) but it runs very slow, any idea to improve it?
thanks,
View 4 Replies
View Related
Aug 29, 2014
Let's say I have a table of data as per the below..
I'm trying to extract only the green highlighted items..
The rules applied are: Only the latest data concerning all cases, and only 1 line (the latest) per case.
As you can see in the image, I don't want the 2nd,3rd, and 4th record extracted cause they are all superseded by more recent records (identified as they are further in the table).
I've considered using either Distinct or Having? but can't get that to work.. If I could use Distinct but then ensure it's the latest record in the table that would be perfect.
View 7 Replies
View Related
Feb 15, 2008
Hi Experts,
I have to find the latest file in a folder and export data to a table in sql server.
The code should be something that has to be incorporated into a t-sql stored procedure.
The file name would for example abc_defYYYYMMDD.xls.
would i be able to find the latest file in the folder using the the datestamp (YYYYMMDD) in the filename.
Please note i would have files in other format and names with datestamp attached to it, so the code has to pick specific file for which the file name starts with 'abc_def'
and export data to a table.
Any help would be highly appreciated
View 10 Replies
View Related
Aug 27, 2015
I need to find the missing months in a table for the earliest and latest start dates per ID_No.  As an example:
create table #InputTable (ID_No int ,OccurMonth datetime)
insert into #InputTable (ID_No,OccurMonth)Â
select 10, '2007-11-01' Union all
select 10, '2007-12-01' Union all
select 10, '2008-01-01' Union all
select 20, '2009-01-01' Union all
select 20, '2009-02-01' Union all
select 20, '2009-04-01' Union all
select 30, '2010-05-01' Union all
select 30, '2010-08-01' Union all
select 30, '2010-09-01' Union all
select 40, '2008-03-01'
For the above table, the answer should be:
ID_No OccurMonth
----- ----------
20 2009-02-01
30 2010-06-01
30 2010-07-01
1) don't include an ID column,
2) don't use the start date/end dates in the data or
3) use cursors, which are forbidden in my environment.
View 9 Replies
View Related
Mar 18, 2014
I have a situation where deleting old records is blocking updating latest records on highly transactional table and getting timeout errors from application.
In details, I have one table called Tran_table1 in OLTP database. This Tran_table1 is highly transactional table, it will receive data for insert/update continuously
While archiving 2 years old records from Tran_table1 into Tran_table1_archive in batches(using DELETE OUTPUT INTO clause), if there is any UPDATEs on Tran_table1,these updates are getting blocked and result is timeout errors in application.
Is there any SQL Server hints to avoid blocking ..
View 3 Replies
View Related
Jun 13, 2006
I am using SQL Server 2005 and trying to create a linked server on Oracle 10. I used the commands below:
EXEC sp_addlinkedserver
@server = 'test1',
@srvproduct = 'Oracle',
@provider = 'MSDAORA',
@datasrc = 'testsource'
exec sp_addlinkedsrvlogin
@rmtsrvname = 'test1',
@useself = 'false',
@rmtuser='sp',
@rmtpassword='sp'
When I execute
select * from test1...COUNTRY
I get the error. "The OLE DB provider "MSDAORA" for linked server "...." does not contain the table "COUNTRY". The table either does not exist or the current user does not have permissions on that table."
The 'sp' user I am connecting is the owner of the table. What could be the problem ?
Thanks a lot.
View 3 Replies
View Related
Jul 20, 2005
Does anyone know where to find or how to write a quick user defined fucntionthat will return a table object when passed the string name of the tableobject. The reason why I want dynamicallly set the table name in a storedprocudue WITHOUT using concatination and exec a SQL String.HenceIf @small_int_parameter_previous = 1 then@vchar_tablename = "sales_previous"else@vchar_tablename = "sales"Endselect * from udf_TableLookup(@vchar_tablename )So if I pass 1, that means I want all records from "sales_previous"otherwise give me all records from "sales" (Sales_Previous would last yearssales data for example).udf_TableLookup would I guess lookup in sysobjects for the table name andreturn the table object? I don't know how to do this.I want to do this to avoid having 2 stored procedures..one for current andone for previous year.Please respond to group so others may benfiit from you knowledge.ThanksErik
View 2 Replies
View Related
Jan 17, 2006
I have 2 tables:
TableA:
Name
UserA
UserB
UserC
Table B:
Name Data
UserA xxx
UserB asdasd
UserB ewrsad
UserC dsafasc
UserA sdf
UserB dfvr4
I want to count the total entries in Table B for every user in Table A. The output would be:
Name Count
UserA 2
UserB 3
UserC 1
I can use a Select Count statement, but I will have to make a SQL call for every user in Table A. Also, Table A is dynamic, so the users are always changing. Can this be incorporated into one SQL call to count the total rows in Table B for each user in Table A?
View 5 Replies
View Related
Dec 7, 2006
This is my first time to deploy an asp.net2 web site. Everything is working fine on my local computer but when i published the web site on a remote computer i get the error "Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed" (only in pages that try to access the database)
Help pleaseee
View 3 Replies
View Related
May 23, 2007
Is there any Posibility to change a User Table to System Table.
How to create one system table.
I am in Big mess that One of the Table I am using is in System Type.
I cant Index the same. Is there any Mistake we can change a user table to system table.....
View 9 Replies
View Related
Jun 23, 2006
We are in the early stages of developing a small project and I have a question I would like an opinion on.
When deciding how to design a table to store information on users who will be different types.
The project involves setting up webpage that allow " appointment setters" to book demos for our Sales reps. We want to keep track of how many each appointment setter books.
I have a user table with user_id, fname,lname, type. Where type represents the type of users they will be either "appointment setter" or "Sales Rep".
In the Appointment table I would like to store the Appoinment setter ID, sales rep Id, date/time appointment was booked and some other data on the appointment.
My question is how do I set up the relationship from the appointment table back to the user table.
Since each record in the appointment table will have a App Setter ID and a Sales Rep ID.
Should I just pick either the app setter id or the sales rep id as the foreign key back to the userID in the user table. Or is there another method how to handle this.
Any thoughts or articles would be greatly appreciated.
thanks,
View 1 Replies
View Related
Jan 19, 2008
ok suppose i want to create a table with a user specified name...... how should i replace the name with the variable name="table1" Dim condb string ....... Dim cmdDB2 As SqlCommand Dim name as string=("table1") Dim selkta As String = "CREATE TABLE name(no int,quest text,op1 text)" cmdDB2 = New SqlClient.SqlCommand(selkta, conDB) conDB.Open() cmdDB2.ExecuteNonQuery() conDB.Close()thank u
View 4 Replies
View Related
May 22, 2008
Hi,
Please give the T-SQL script for this ? Thanks
Shanth
View 4 Replies
View Related
Sep 20, 2007
My table are
Customer: customerId ,name
Order: orderId, customerId, product,date
I want to display latest order from the customer
View 2 Replies
View Related
Jul 26, 2000
Hi everybody,
In PL/SQL, they hv versions. Is there any versions available for T-SQL?. If so, what is the latest version available in T-SQL?.
advance in thanks,
Srini
View 1 Replies
View Related
Nov 21, 2007
I use a table called "version" to store version number for every module
my question is how to get highest version number in this table?
table:
MODULE|MAJOR|MINOR|REVISION
modul1|6|1|0
modul2|6|3|1
modul3|6|2|8
in this case i want modul2|6|3|1 as the result
already try some subquery but got problem and a lot of result
tq...
View 9 Replies
View Related
Sep 3, 2007
guys help please...I have a table with 3 columns (TransactionID, CustomerID, TrasanctionDate) and there could be posibility that one or more rows could have the same value for CustomerID. Now my question is it posible to retrieve the row of latest TrasanctionDate made by a particular customer?
Ex:
TransactionID | Customer ID | TrasanctionDate
1 | 2 | 9/3/2007 12:00:00 AM
2 | 3 | 9/4/2007 12:00:00 AM
3 | 2 | 9/5/2007 12:00:00 AM
4 | 2 | 9/6/2007 12:00:00 AM
Say, i want to retive the latest trasanction of customer with CustomerID equals to 2. The output shoudl be is row 4 since it has the lates date with CustomerID equals to 2
4 | 2 | 9/6/2007 12:00:00 AM
Any help will be greatly appreciated. Thanks in advance!
View 3 Replies
View Related
Feb 25, 2004
how to get the name of the latest file of a particular directory
thru query
i.e
i have files in a directory
1130am.txt
11.45am.txt
1200am.txt
my query should return 1200am.txt
View 2 Replies
View Related
Apr 8, 2008
So here is my dilemma:
We have licensed software from a 3rd party and we do not have the source code, nor can we do anything to change the database schema, write our own procs, triggers, etc.
With that said, we want to know which records in certain tables have been added or modified since a certain time (for example, in the past 24 hours). However, none of the tables has any datetime or timestamp columns on them for create date, update date, etc.
I tried to look into hacking thru DBCC log (dbname, 2) to see if that would help, what a mess. Using a tool like that from Luminent or Red Gate doesnt help either, because long term this needs to be automated if possible. Does anyone have any ideas on how to do this, or is the answer simply, "Sorry mate, you need to get them to allow you to change the database to add new columns and/or triggers". :)
View 5 Replies
View Related
Jan 24, 2014
I have table has column called [last modified date] it has time too , i need to get the [latest modified date] for particular ID , there are many updates to the ID and i just need the latest date.
[PAX ID] | [Last Modified Date]
1 2013-01-02 18:23:00
1 2013-01-02 11:42:00
3 2013-01-01 09:00:00
3 2013-01-08 16:05:00
5 2013-01-01 09:09:00
View 2 Replies
View Related
Feb 6, 2008
Hi,
this will be my first post to sqlteam.com.
I've been trying to work out this problem for awhile, and figured I really need help. I have a set of data that shows the results of a number of tests that I have been doing on a software. Each test I have done records the test_ID, test_Result, and the date_Tested. An example of the columns would be like this:
test_ID | test_Result | date_Tested
3112 | PASS | 2007-11-23 09:29:40.230
3112 | FAIL | 2007-02-22 09:22:00.230
3112 | FAIL | 2007-01-21 09:21:40.234
3113 | PASS | 2007-11-23 09:29:40.230
3114 | PASS | 2007-11-23 09:29:40.230
3115 | FAIL | 2008-01-23 09:29:40.230
3115 | PASS | 2007-11-23 09:29:40.230
3116 | FAIL | 2007-12-25 09:29:40.230
3116 | PASS | 2007-11-23 09:29:40.230
Now what I want is ONLY the latest result for each test_ID, I do not want repeated test_IDs to show up. So my expected result should be:
test_ID | test_Result | date_Tested
3112 | PASS | 2007-11-23 09:29:40.230
3113 | PASS | 2007-11-23 09:29:40.230
3114 | PASS | 2007-11-23 09:29:40.230
3115 | FAIL | 2008-01-23 09:29:40.230
3116 | FAIL | 2007-12-25 09:29:40.230
Is there a way I can query this? I've tried using "distinct test_ID" but it would still show the repeated test_ID due to the date_Tested not being the same.. also Group By will not work. I'm not sure what to do here. Any ideas anyone?
Thanks in advance.
View 4 Replies
View Related
Jun 14, 2006
I have a column which stores dates (datetime data type). I would liketo fetch one data which is the most latest date among them. So if thereare 04/01/06, 05/08/06, 05/12/06, 06/15/06, then 06/15/06 is the one Ineed to output.how can I write this in sql stmt?select datebeginfrom testtablewhere datebegin = ?????I don't think it is simple as I thought. I have no idea what I need towrite in where clause to make this work.thanks.
View 1 Replies
View Related
