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
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.
> 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]
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
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.
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?
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
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
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.
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!
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.
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.
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...
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 ..
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
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.
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.
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?Â
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
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?
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.
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?
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
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?
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.
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
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
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.