Code:
AccountNo StartDateTime EndDateTime
1234 4/30/2012 8:00 AM 5/15/2012 8:00 AM
Of course there are multiple accounts, about 2100 in this case but they very by time periods, and multiple start and stop dates for an account. I need to get the start times and match them w/ the nearest end times but AFTER the value of the start time. Nearest end times must be forced to correspond to the nearest start time but there are some start times w/o end times and end times w/o start times due to user data entry errors. I need a solution that handles this. It is ok w/ the customer to make the assumption of nearest times supposedly going together so they can show the users the errors. I am on SQL Server 2008 R2.
I have such a scenario: - two tables with record containing car vehicle number, datetime of message and other data like weight ect. - first table contains only two messages for one car per one day - second has many messages for one car for one day
I would like to get a list of messages from first table but joined with the nearest (previous) record for the same car from second table.
Table A Table BBOL# B_BOL#Chargeback#Hi All,I have been struggling with this for the past few months. I have twotables that I'm inner joining on BOL#=B_BOL#. This works fine. Now forthe problem....When there are chargeback# fields associated with B_BOL#they aren't being captured as additional records. None of my tableshave primary keys because at any given time any field can contain a"null" value, so I am unable to assign a foreign key, I think. How canI attach the subsequent records to the main record to bring back theadditonal data? Please help!!!
Hi, I've got a table of categories. I also have a table of items each assigned to one of those categories. Each item has many image urls associated with it. I'd like to get a list of all of the categories and one image (I don't care which) for each category. Here's the tables:
Categories ID | Name ------------------- 1 Kids 2 Men's 3 Womens
I would like to know if it's possible to return a single record by joining the tables below. [Persons] PersonID [int] | PageViewed [int] =============== ================= 1 10 2 5 3 2 4 12
[PersonNames] - PersonID JOINS Persons.PersonID PersonID [int] | NameID [int] | PersonName [nvarchar] | PopularVotes [int] =============== ============== ======================= =================== 1 1 Samantha Brown 5 1 2 Samantha Green 10 2 3 Richard T 10 3 4 Riko T 0 4 5 Sammie H 0
[AltNames] - backup for searches caused by common spelling mistakes AltNameID [int] | AltNames [nvarchar] ================ ============================= 1 Sam, Samantha, Sammie, Sammy 2 Riko, Rico
[PersonAllNames] - JOINS [PersonNames.NameID] ON [AltNames.AltNameID] NameID [int] | AltNameID [int] ============= ================ 1 1 4 1 3 2 This is ideally what I'd like to have returned: PersonID | PageViewed | MostPopularName | NameSearch ========= ============ ================= ================= 1 10 Samantha Green Samantha Brown, Samantha Green, Sam, Samantha, Sammie, Sammy 2 5 Richard T Richard T 3 2 Riko T Riko T, Riko, Rico 4 12 Sammie H Sammie H, Sam, Samantha, Sammie, Sammy
[MostPopularName] is [PersonNames.PopularVotes DESC].[NameSearch] combines all records from [PersonNames.PersonName] and [AltNames.AltNames].
The purpose for this is that I'd like to cache the results table so that all searches can just perform a lookup against the NameSearch field. Any help would be greatly appreciated. Thanks, Pete.
I have tried joining several tables and the result displays duplicate rows of virtually every line/row. I have tried using distinct but this didn't work. I know it could because there's several columns from some of the tables named the same.
HiI need to retrieve data from a table comparing datetimes. I mean I've atable with a datetime data and I'll need to retrieve rows with date time[color=blue]> now and date time < now + 5 minutes...[/color]Is this posible ? Which is the best / easy way ?Thanks in advanceJ
What methods work for storing empty dates? I've read that some people pick an old date and use it to represent empty. I'm not fond of the idea, because then I'll have to strip that date whenever I display the field in my UI.
Any other ways to do this? I'm using SQL Server 2005 and C#.
Hi:I have created a table that has a column of smalldatetimes. For any given day, there may be different records with the same date but with different times. I've created the query SELECT Event FROM Events where Start=@Start and I input start as a selected date from the calendar control. Because of the different times, I do not get any matches. Could someone help me with a query that will get all of the events on the same day? Regards,Roger
We have inherited an appointments database that has a table tblAppointments
Within this table there are 2 fields, ApptFrom & ApptTo, these are the appointment start & finish times.
My problem is, the values in these fields are held as integers ranging from 0 - 288. I have worked out the scale for the integers, it is based on a 24hr clock with each segment representing 5mins
Eg: 0 = 0:00
12 = 1.00
24 = 2.00
36 = 3.00
€¦
...
288 = 24:00
What I need is a piece of T_SQL that will translate these into times that can be read by a normal user
So, say I have a record with an ApptFrom = 36 & ApptTo = 42, I need to be able to show these as ApptFrom = 3.00 & ApptTo = 3.30
Hello! I have a field "End" in my database that is mapped as DateTime and allows nulls. Now I want to do a SQL-Select (in a SqlDataSource) like SELECT * FROM My_Table Where (([End] = @EndDate) OR ([End] = null)) @EndDate is a valid DateTime, but the second OR condition doesn't work. What is the best way to check if the [End]-field is empty or null? Thank you very much!
I have a requirement to be able to calculate the transit time between international locations when I have the flight departure time and flight arrival times and departure and arrival locations.
We have a problem though, in that the datetime stored are local times, and the journeys can involve crossing the international date line in either direction, so it is possible for the arrival time to be earlier than the departure time!
To give a famous example, if you flew Concorde from London to Washington, you could depart at 10am on the 1st of June (London time) and arrive at 8.00am on 1st June (Washington time)
Even worse, you could leave Brisbane at 2.00 am on the 1st June, cross the International Date Line and arrive at San Francisco at 23:00 on 31st May!
Hi How do I get a nearest distance of a point? For example, I have two tables A and B and I want to find the nearest distance between the records of the two tables. In addition, one of the tables should also give me the distance. The data I have geo spatial data. Can this be done in SQL Help will be appreciated
Hi, I'm making a birthdays database where I want to list everyone in it ordered by the firstcoming birthdays according to the current date.
I have all the birthday records stored in a table called bursdager and the person name is stored in the navn column and the persons birthday date is stored in the dato column.
I'm having some problems, currently I have this statement:
Code:
DECLARE @tbl TABLE (navn VARCHAR(60), dato DATETIME)
INSERT INTO @tbl SELECT navn, dato FROM bursdager WHERE DATEPART(month, dato) >= DATEPART(month, getdate()) ORDER BY DATEPART(month, dato), DATEPART(day, dato)
INSERT INTO @tbl -- those are the one who allready have had birthday this year SELECT navn, dato FROM bursdager WHERE DATEPART(month, dato) < DATEPART(month, getdate()) ORDER BY DATEPART(month, dato), DATEPART(day, dato)
SELECT * FROM @tbl
It works *allmost* as it should-- except, it still lists the last persons who had birthday first, even the days after their birthday if the month is still the same.
I thought about adding an additional check: Code:
AND DATEPART(day, dato) >= DATEPART(day, getdate())
in the WHERE clause of the SELECT statement but that won't be correct either because it then just lists everyone based on whether the day number the person was born is higher or less than the day number of the current date.
Anyone have any suggestions? Is there an easier way to do it?
I have a field with seconds in it and I need to disply it in hours which I can do by dividing it by 3600, but I am trying to figure out how to round it up to the nearest 15 minutes. I have tried a couple of things with ROUND and CEILING, but am not getting the right numbers back. Any help would be greatly appreciated.
I have the following tableCREATE TABLE Readings(ReadingTime DATETIME NOT NULL DEFAULT(GETDATE()) PRIMARY KEY,Reading int NOT NULL)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050101', 1)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050201', 12)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050301', 15)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050401', 31)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050801', 51)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20051101', 106)GO-- list the tableSELECT ReadingTime, Reading FROM ReadingsGOIt is a table of readings of a free-running counter that istime-stamped. I need to determine the value of the reading thatcorresponds to the closest date to the supplied dateAre there more optimal/efficient ways of accomplishing this than thefollowing?DECLARE @when DATETIMESET @when = '20050505'SELECT TOP 1 ReadingTime, Reading FROM ReadingsORDER BY abs(DATEDIFF(minute, ReadingTime, @when))The above gives me the desired result of ('20050401', 31).Any suggestions would be appreciated
OK, this is the scenario. I have a database with many columns ( each a mean value and a standard deviation, and with it a set of coordinates that i want to retrieve ).
Then i have a value that i want to query with the database, by comparing it with the mean and its standard deviation, and it should return a few sets (lets say 2) of coordinates whereby the the value of the mean is closet to the one in the database, in order of nearest value. How should i do it, since i am not using the exact value of the mean in the database?
I know its a bit confusing the way i wrote, but anyone understand wat i am trying to say and can help, i am very grateful. I had googled around for answers but cannot find. Thanks.
I have a date (my birthday). I would like to find the closest birthdays to mine, both before and after my birthday. I would like to list the people in my database who are the closest age to me, but in that order. So sorting my table by age and taking a row below and above my birthday is not going to work. This is because the three people below me may all have their birthday the next day, while those above me may have theirs years before mine.
Birthdays sorted by date:
05/10/1979 jim 12/01/1980 bob 10/04/1983 mike 10/05/1983 larry 11/21/1983 dan 12/07/1984 josh 05/07/1999 dylan
The order I wish to achieve is:
10/05/1983 larry 11/21/1983 dan 12/07/1984 josh 12/01/1980 bob 05/10/1979 jim 05/07/1999 dylan
I have a table with 257 mil records with latitude and longitude data.
My goal is to find the closest intersecting values from a locations table (88 rows) and update any of the 257 mil records that are applicable with the location_Name and Location_Group_Name.
The query I have works but doesn't perform well on such a big data set.
CREATE TABLE #Positions -- Base table 257 mil rows. Actual table has 20 columns ( IDBigInt PRIMARY KEY, LatitudeDec(10,6), LongitudeDec(10,6),
[Code] ....
Attached you will find the tables, test data, a function to measure distance and some queries that work but are too slow for this much data.
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.
I found in another forum that if I take the seconds and divide them by 15 then round up and multiply them by 4 I can get this done, but I can't figure out how to work it into my select statement. Anyhelp would be greatly appreciated. dbo.SLPTRANS.TimeSpent is the field I am trying to convert.
SELECT dbo.SLPTRANS.ClientID, SUM(dbo.SLPTRANS.TransValue) AS Expr1, dbo.SLPTRANS.TimeSpent AS Expr2 FROM dbo.SLPTRANS INNER JOIN dbo.INVOICE ON dbo.SLPTRANS.InvoiceID = dbo.INVOICE.RecordID GROUP BY dbo.SLPTRANS.ClientID HAVING (dbo.SLPTRANS.ClientID = 405)
Is there a way that I can do this at the table level to automatically handle the rounding of seconds, etc. down to the minute automatically without having to use a trigger? Â
Here is a very basic example of what I am trying to do:
--example:  '09-22-2007 15:07:18.850' this is the value inserted into the table by the code select getdate()Â
 --example: '2007-09-22 15:07:00.000'  this is the value I want to store in the table select dateadd(mi, datediff(mi, 0, getdate()), 0)
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
