Get Latest Records When Date And Time Are Separate Columns?

Mar 26, 2012

I have 2 tables:

TransactionsImport (which is the destination table)
TransactionsImportDelta

I need to do the following:

Get the records with the latest date and time in the destination table TransactionsImport
Get the records with the latest date and time in the destination table TransactionsImportDelta table
Insert the records from the TransactionsImportDelta table into TransactionsImport that have a greater date & time than the current records in TransactionsImport table.

Problem is date & time are in separate columns:

Table structure:

Date Time ID
2011121305154107142201008300100
2011121305154122B1L13ZY0000A07YD
2011121304504735142201090002600
2011121304504737142201095008300
2011121304504737142201090002600

View 2 Replies


ADVERTISEMENT

SQL Server 2008 :: Loop Through Date Time Records To Find A Match From Multiple Other Date Time Records?

Aug 5, 2015

I'm looking for a way of taking a query which returns a set of date time fields (probable maximum of 20 rows) and looping through each value to see if it exists in a separate table.

E.g.

Query 1

Select ID, Person, ProposedEvent, DayField, TimeField
from MyOptions
where person = 'me'

Table

Select Person, ExistingEvent, DayField, TimeField
from MyTimetable
where person ='me'

Loop through Query 1 and if it finds ANY matching Dayfield AND Timefield in Query/Table 2, return the ProposedEvent (just as a message, the loop could stop there), if no match a message saying all is fine can proceed to process form blah blah.

I'm essentially wanting somebody to select a bunch of events in a form, query 1 then finds all the days and times those events happen and check that none of them exist in the MyTimetable table.

View 5 Replies View Related

SQL 2012 :: Split CSV Records To Separate Columns

Jun 12, 2014

I've a table as below

custid,companyname,phone,address
2,AAAAA,(222) 222-2222,address 2
3,cust 3,(333) 333-3333,address 3
5,BBBBB,(333) 333-3333,DDDDD
6,cust 6,(222) 222-2222,address 6
7,cust 7,(222) 222-2222,address 7

How to split csv values to new fields. so that the desired output should be as below

custidcompanynamephone address
2 AAAAA (222) 222-2222 address 2
3 cust 3 (333) 333-3333 address 3
5 BBBBB (333) 333-3333 DDDDD
6 cust 6 (222) 222-2222 address 6
7 cust 7 (222) 222-2222 address 7

View 9 Replies View Related

Searching For Latest Date And Time Only

Jul 20, 2005

> This is what I want to do with the data table below.[color=blue]>> I only want it to show one id for each issue. ( ie, 4001 only once,> 4002 only once, etc. But only the one with the latest date and time)>> Ie. 4001 should only be in the table once, with the latest date -> which should be :> 400109/12/200317:12:09 (as I only want to show the last time> the id was updated - do you get me now )>> Ie. 4002 should show only 400211/12/200315:25:13>[/color][color=blue]>>> idhs_change_datehs_change_time> 400127/10/200310:38:27> 400109/12/200314:43:58> 400109/12/200317:12:09> 400227/10/200310:56:28> 400209/12/200314:44:11> 400211/12/200315:25:13> 400327/10/200311:13:12> 400309/12/200314:44:21> 400310/12/200310:48:02> 400310/12/200313:25:09> 400427/10/200311:28:09> 400409/12/200314:44:29> 400409/12/200317:18:28> 400527/10/200315:55:40> 400528/10/200310:18:24> 400627/10/200315:59:47> 400628/10/200310:18:38> 400609/12/200314:44:40> 400609/12/200316:47:15>[/color]

View 3 Replies View Related

Selecting The Latest Date From Different Columns

Jul 20, 2005

I have 6 columns, all with dates within them, i.e.Proposed Start Date 1Proposed Start Date 2Proposed Start Date 3Proposed Finish Date 1Proposed Finish Date 2Proposed Finish Date 3What I need to do is narrow this down into two fields:Start DateFinish DateSo I need to find the newest value from the columns. i.e.If PS1 filled PS2 and PS3 empty, then Start Date = PS1If PS3 empty and PS2 filled, then Start Date = PS2If PS3 filled then PS3and similarly for Proposed Finish Dates.Anyone knew how I can do this.(Maybe the following will help for the programmers out there:If PS3 <> nullThen StartDate = PS3Else if PS2 <> nullThen StartDate = PS2ElseStartDate = PS1End IFJagdip

View 5 Replies View Related

BETWEEN With Date/Time In Separate Fields

Apr 22, 2008

My group is working on a project for school and neither of us have much experience with SQL or ASP. We are pretty much just writing an SQL query to get the data in a certain date range and using ASP to write the tables with the data in it.

Our problem is the SQL database has separate fields for date and time. There is nothing we can do about this because the VB.NET program that populates the tables automatically does this. We can't figure out how to do the BETWEEN statement when they are separate fields. We can do it for a date range and it works fine. An example of what we want to do is show the data from 11:00 am on 04/01/2008 until 4:00pm 04/03/2008.

Does anyone have any idea what we can do? I really want to learn this but it's frustrating because we can't seem to get much help at school.

View 5 Replies View Related

How To Separate Date &&amp; Time During Export

Aug 30, 2007

I would like to export sql server 2005 tables to a text file using SSIS. It is easy to do this as a direct export. However, how could I separate the datetime field into two fields: one for date only and one for time only?

View 4 Replies View Related

How Can I Get All Records For Both Tables With The Latest Begin Date If Exists?

Jun 15, 2006

Itemlookup tableField names : index_id (primary key), itemno, description.It has a child table, which is ItemPriceHistory tableThe relationship to the child table is one (parent table)-to-many(child table). - It is possible to have no child record for some rowsin the parent table.ItemPriceHistory tableField names: index_id (primary key), itemlookupID (foreign key of theItemlookup table), date begin, priceIt is a child table of the itemlookup table.How can I get all records for both tables with the latest begin date ifexists?I also need to show the records in the parent table if there is norelated record in the child table.Please help

View 4 Replies View Related

Determine Value Based On Two Separate Date Columns

Jun 20, 2014

This is the logic I need to incorporate in to sql

if the getdate() < term start date then R
if the getdate() > term date date and getdate() < term end date then C
if the getdate() > term end date then H

I have come up with the following type of case statement that will allow me to determine the first two values (to a degree), however , I need to evaluate the end date as well.

Is there a way to look at multiple columns in a case statement?

CASE
WHEN DATEDIFF(DD,GETDATE(),TRM_BEGIN_DTE) >0 THEN 'R' else 'C' end

View 5 Replies View Related

Date/Time Of Latest SQL Server 2005 Instance Restart

Jan 28, 2008



Is the Date/Time of the latest SQL Server 2005 instance restart stored somewhere in the system catalog?
This value is quite relevant when retrieving information on the costliest unused indexes in the instance.

I know of one way of doing this, which is to search the SQL Logs for this info.
This solution is very unwieldy - we recycle the logs daily and we only keep the logs of the last 7 days.

View 4 Replies View Related

Converting Datestamp To Separate Date And Time Stamp

Jan 11, 2007

I need to separate the date stamp (which looks like this 2006-10-05 09:08:41.000) into the date in this format 05OCT2006 and then the date stamp separate in this format 09:08. Thanks!

View 2 Replies View Related

Combine Separate Date && Time Fields Into One Datetime Field?

Dec 26, 2006

Good morning.I am importing an XLS file into one of my tables. The fields are:Date Id Time IO12/22/2006 2 12:48:45 PM 912/22/2006 16 5:40:55 AM 112/22/2006 16 12:03:59 PM 2When I do the import, I get the following:Date Id Time IO12/22/2006 12:00:00AM 2 12/30/1899 12:48:45 PM 212/22/2006 12:00:00AM 16 12/30/1899 5:40:55 AM 112/22/2006 12:00:00AM 16 12/30/1899 12:03:59 PM 2Here are my doubts:1. Would it be better to combine the Date & Time fields into onecolumn? If so, how?2. What issues or problems might I have when I program SQL reports, ifI leave the fields as they are?Any comments or suggestions will be very much welcomed.Cheers mates.

View 2 Replies View Related

Combine Separate Date &&amp; Time Fields Into One Datetime Field?

Dec 26, 2006

Good morning.

I am importing an XLS file into one of my tables. The fields are:

Date Id Time IO







12/22/2006
2
12:48:45 PM
9


12/22/2006
16
5:40:55 AM
1


12/22/2006
16
12:03:59 PM
2


When I do the import, I get the following:

Date Id Time IO
12/22/2006 12:00:00AM 2 12/30/1899 12:48:45 PM 2
12/22/2006 12:00:00AM 16 12/30/1899 5:40:55 AM 1
12/22/2006 12:00:00AM 16 12/30/1899 12:03:59 PM 2

Here are my doubts:

1. Is it be better to combine the Date & Time fields into one column? Advantages/Disadvantages?
2. If I don't combine them, should I use varchar or datetime data type?
2. What issues or problems might I have when I program SQL reports, if I leave the fields as they are?

Any comments or suggestions will be very much welcomed.

Cheers mates.

View 3 Replies View Related

Add 2 Separate Columns From Separate Tables Using Trigger

Feb 15, 2012

I am trying to add 2 separate columns from separate tables i.e column1 should be added to column 2 when inserted and I want to use a trigger but i don't know the syntax to use...

View 14 Replies View Related

Deleting Old Records Is Blocking Updating Latest Records On Highly Transactional Table

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

Combine 2 Columns To Get Date Time

Aug 28, 2007

adate atime
08-21-2007 11:09
08-20-2007 16:49
08-03-2007 00:39

I would like to combine adate with atime to get adatetime

View 5 Replies View Related

Transact SQL :: Represent Time Interval Between 2 Date Columns

May 20, 2015

I have a simple table as shown:

I want to have values on the last column to represent the time interval between the 2 date columns (visits); i.e for event-ID 2 for example, I will have

entry(EventID = 2)  - exit(EventID = 1), and so on

View 7 Replies View Related

T-SQL (SS2K8) :: Creating Missing Records In A Date / Time Range?

Nov 6, 2014

creating the missing records in a date/time range.

However, I need to return different groups for each span of records.

here's some data....

aaa1
aaa7
bbb2
bbb5
bbb6

The numbers are the hour of the day.

I need to return

aaa 0 0
aaa 1 1
aaa 2 0
aaa 3 0
...
bbb 0 0
bbb 1 0
bbb 2 1
...
and so on.

I've got a numbers table and I can left join with it but I just get nulls for the missing hours instead of having it as above.....I can't think of a way of repeating the groups for each of the 'missing' hours - other than creating a length insert statement to fill in the gaps....unless that is the only way of doing it.

View 6 Replies View Related

T-SQL (SS2K8) :: Forcing Existence Of Date Records For A Graph Of Counts Over Time

Oct 14, 2014

I'm trying to get a count of Employed and Available contractors per time period, and I have a table of Contracts... something like:

CREATE TABLE empContract(
empContractID INT IDENTITY(10000,1) PRIMARY KEY,
StartDate DATE NOT NULL,
EndDate DATE,
ContractorAssigned INT,
FOREIGN KEY ContractorID REFERENCES Contractor(ContractorID)
);

I don't think this is possible without the existence of some kind of Calendar table. Given the existence of a calendar table, the query seems really simple - just something like:

SELECT cal.CalendarDate, ec.ContractID
FROM Calendar cal LEFT JOIN empContract ec ON cal.CalendarDate BETWEEN ec.StartDate AND ec.EndDate

The left join forces the existence of all dates in a range (@StartDate and @EndDate), so that when I try to create a graph with counts by day, I don't have any gaps in my time series.

View 2 Replies View Related

Transact SQL :: Selecting Latest Date But Over Another Date Column Including Nulls?

Nov 26, 2015

CREATE TABLE #Dateissue
(ID int,
Code nvarchar (20),
Datein datetime,
Declined datetime )

[Code] .....

I have a table here.  I want  find a way of getting the latest date, when the code is the same.  If the Declined date is null.  Then I still want the latest date.  E.g. ID 3.  

If the declined date is filled in.  Then I want to get the row, when the Datein column value is greater then the declined date only.

I tried grouping it by max date, but   i got an error message when trying this out.  Against the code  

WHERE MAX(Datein) > Declined

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.  What do I need to do to get both my outputs working? 

View 9 Replies View Related

Transact SQL :: Find Latest Start Date After A Gap In Date Column

Apr 19, 2015

My requirement is to get the earliest start date after a gap in a date column.My date field will be like this.

Table Name-XXX
StartDate(Column Name)
2014/10/01
2014/11/01
2014/12/01

[code]...

 In this scenario i need the latest start date after the gap ie. 2015/09/01 .If there is no gap in the date column i need 2014/10/01

View 10 Replies View Related

Transact SQL :: Find Latest Start Date After A Gap In Date Field For Each ID

Apr 23, 2015

My requirement is to get the latest start date after a gap in a month for each id and if there is no gap for that particular id minimum date for that id should be taken….Given below the scenario

ID          StartDate
1            2014-01-01
1            2014-02-01
1            2014-05-01-------After Gap Restarted
1            2014-06-01
1            2014-09-01---------After last gap restarted
1            2014-10-01
1            2014-11-01
2            2014-01-01
2           2014-02-01
2            2014-03-01
2            2014-04-01
2            2014-05-01
2            2014-06-01
2            2014-07-01

For Id 1 the start date after the latest gap is  2014-10-01 and for id=2 there is no gap so i need the minimum date  2014-01-01

My Expected Output
id             Startdate
1             2014-10-01
2             2014-01-01

View 4 Replies View Related

How To Get Latest Temperature Reading For Each Date In A Date Range

Sep 28, 2012

I have to display the last temperature reading from an activity table for all the dates in a selected date range.So if I select the date range from 09/01/2012 to 09/30/2012, the results should look like this:

Date Temperature
09/01/2012 73.5
09/02/2012 75.2
09/03/2012 76.3
09/04/2012 73.3
09/05/2012 77.0
09/06/2012 74.5
and so on.

I am using this to get the dates listed:
WITH CTE_DatesTable
AS
(
SELECT CAST('20120901' as date) AS [Date]
UNION ALL
SELECT DATEADD(dd, 1, [Date])
FROM CTE_DatesTable
WHERE DATEADD(dd, 1, [Date]) <= '20120930'
)
SELECT [Date]
FROM CTE_DatesTable

How could I get the temperature if I did a sub-query here?

View 5 Replies View Related

Need Query For Latest Time Per ID

Jun 11, 2006

I've been baffled over how to do this without using a script... I would like to get the info I need with a single query.

Here's my scenario, the table looks like this (simplified):

ID, TIME
101, 5am
101, 6am
104, 5am
260, 5am
104, 6am
260, 6am
101, 7am
260, 9am
104, 7am
101, 8am

So basically I have a column of identifications and a column of times. They won't necessarily be in order. I would like a query that gives me this:

101, 8am
104, 7am
260, 9am

It would order the IDs ascending, only showing the newest time assigned to that ID in the table.

Thanks in advance for any help :)

View 14 Replies View Related

How To SELECT The Latest Records?

Sep 21, 2007

Hello!

I have a table, where one of the columns is the date/timestamp of when each row was inserted. I want to be able to extract the most recently inserted rows.

With Sybase (a not so distant cousin of MS SQL) the following works:

select * from TABLE having date = max(date)


With MS SQL, however, the same query does not work:

Column 'TABLE.date' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.


What's the solution? Thanks!

View 14 Replies View Related

Latest Unique Records, How To Get?

Dec 3, 2007

Let's say I have a data entry from a pool of employees:
table is as follow:
EmpNo Branch Date Amount
1 A101 11/30/2007 $0.90
1 A101 11/30/2007 $1.20
2 A101 11/30/2007 $0.90
3 A101 11/30/2007 $0.80

How can I select the whole table and only take in 1 unique latest entry if there are multiple entries for the same day, same branch under same employee number?

Thanks! :D

View 7 Replies View Related

Get The Latest Changed Records

Feb 13, 2008

Hi,

I hava a table with the following information

CREATE TABLE TEMP1 (REFID INT, REVISION INT, FIELDNAM VARCHAR(10), VALUE VARCHAR(10));
INSERT INTO TEMP1 VALUES(1001, 0, 'A', 'A2');
INSERT INTO TEMP1 VALUES(1001, 0, 'C', 'C2');
INSERT INTO TEMP1 VALUES(1001, 0, 'E', 'E2');
INSERT INTO TEMP1 VALUES(1002, 0, 'A', 'A3');
INSERT INTO TEMP1 VALUES(1002, 0, 'B', 'B2');
INSERT INTO TEMP1 VALUES(1002, 0, 'E', 'E3');
INSERT INTO TEMP1 VALUES(1001, 1, 'A', 'A4');
INSERT INTO TEMP1 VALUES(1001, 1, 'E', 'E4');

Here based on latest revision and refid I should get the fieldnam and value.
Expected output:
REFID FIELDNAM VALUE REVISION
1001 A A4 1
1001 E E4 1
1002 B B2 0
1001 C C2 0

View 7 Replies View Related

Find Latest Records

Dec 16, 2007

Hi all,

I have a question regarding SQL Server Performance and would be grateful for a tip. Let's say I have a DB with 50.000 records. These records belong to 1.000 different datasets, so there is 1 actual and 49 historical data records. For example a company with 1000 employees has a database where each year a new record is created for each employee so after 50 years they have 50.000 records (50 years x 1000 employees). 1 record is actual, and 49 are historical. What is the best way to store this? Main target is performance for the enduser, so when an employee clicks "See all my records" it should be fast. But on the other hand the application mainly works only with the current year. Additionally it should also be fast for the boss of business unit who wants to see the latest records of his e.g. 100 employees. I have some ideas and would like to get your opinion:

1. Retrieve by latest date
Just store the records. To get the current year just select the record with the latest year. Disadvantage might be with larger databases: If the company switches to store the requests per month, each user would have 600 records (12 months x 50 years). Each time the latest record should be retrieved, 600 recards have to be compared regarding the latest date (or sorted by date descending using Top1, but this might be a problem for the boss then? Or could this be combined for a group if the boss wants to see all the latest records of his employees?).

2. Add a 'IsCurrent'-Flag
Each time a new record is stored it should be compared to the latest record. If it is newer, the 'IsCurrent'-Flag should be removed and then checked on the new record. This should be fast processed (because on saving a new record it only needs to be checked against the currently 'IsCurrent'-flagged record), and for retrieving the current record no further comparison is necessary. But how could I do this? I need to update the "AddRecord"-SP with this comparison, but I don't know how to do this.

Currently number 2 is my favorite, I just don't know how to do it ;-) What is your opinion about it, and could you include an example?

Thanks

View 20 Replies View Related

Separate Columns

Nov 9, 2005

Hi to all of you,

I am new to SQL I have a problem that I can’t solve.
I have a column with Surname and name (SMITH, James) in one table with data I just need to separate in two columns in one Surname in the other one Name I know how to unite two columns using substrings but not to separate in two columns.

Help is highly appreciated

View 4 Replies View Related

Query To Get Latest 2 Records For Each Group

Aug 21, 2014

select
DayRank = ROW_NUMBER() OVER(ORDER BY a.datedel DESC),
a.order,a.line,a.datedel,a.recpt,b.status,
b.item,b.t_sup
from historytbl a
inner join order b
on a.order = b.order
and a.line = b.line
and a.status =4
group by a.order,line,a.datedel,a.recpt,b.status,b.item,b.sup

The query is returned the results below.

Rank OrderLineDateDelrecptitemsup
----- -------------------------------
1aaa102014-18-08rc1zzz1231122
2bbb202014-08-08rc2zzz1231122
3ccc302014-04-08rc3zzz1231122
4ddd902014-08-11rc6yyy123333
5eee102014-05-11rc7yyy123333
5fff90 2014-02-11rc8yyy123333
6ggg102014-05-10rc9qqq123444
7hhh502014-04-10rc0qqq123444
8iii102014-04-10rc5rrr123555

However, I want to have the query only show most recent two records for each group of item and sup, please see the results I want below.

Rank OrderLineDateDelrecptitemsup
----- -------------------------------
1aaa102014-18-08rc1zzz1231122
2bbb202014-08-08rc2zzz1231122

4ddd902014-08-11rc6yyy123333
5eee102014-05-11rc7yyy123333

6ggg102014-05-10rc9qqq123444
7hhh502014-04-10rc0qqq123444

View 4 Replies View Related

Distinct Serial Number And Latest Time

Feb 12, 2015

select SERIALNO, Max(TIME)
from dbo.TASK A
join dbo.Status B
on A.TID=B.TID
where A.ID in ('1111',2222')
and A.TYPE='Pen'
group by B.SERIAL_NO, B.TIME
order by BM.TIME

For this query I may get serial no duplicates but times are unique

For that serial no, I have to find the recent time. But if I use group by, I am getting the wrong no

View 2 Replies View Related

SQL Server 2008 :: How To Get Latest Records From Table

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

Select Latest Records From GROUP BY Query

Feb 26, 2014

I have a table T (a1, ..., an, time, id). I need to select those rows that have different id (GROUP BY id), and from each "id group" the row that has the latest field 'time'. Something like SELECT a1, ..., an, time, id ORDER BY time DESC GROUP BY id. This is the wrong syntax and I don't know how to handle this.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved