August 29, 2016 at 3:21 pm
Disclaimer - Patience please - this is my first go at DB development.
I have to identify records in a table that has multiple rows, and from the multiple row, ID the date that is closest between a startdate endedate in another table. If the requirement were just identify the existance of a record, an existance check would work.
I am having a hard time understanding how to get SQL to do this.
So while this query returns data it is not the appropriate test. Please give advice if possible. If not clear, ask I will try to help.
WITH
CALLS(StartTime, MyKey) AS
(
SELECT Convert(NVarchar(5),StartTime,108),MyKey
FROM cc
WHERE
EXISTS
(
SELECT 1 FROM cc
JOIN o
ON cc.Key = o.Key
WHERE
Convert(NVarchar(5),cc.StartTime,108) BETWEEN o.StartTime1 AND o.Time1
OR Convert(NVarchar(5),cc.StartTime,108) BETWEEN o.DeliveryTime2 AND o.Deliveryime2
)
)
SELECT MyKey, STARTTIME FROM CALLS
August 29, 2016 at 3:47 pm
Hi,
Welcome to SSC. Since you're new and your question isn't entirely clear, perhaps reading one (or both) of these articles will help us to help you.
and/or
Jeff Moden's article on Forum Etiquette: How to post...
The answer I've seen around here is doing a ABS(DATEDIFF ... ) between the possible answers and then take the top 1 values.
August 30, 2016 at 3:35 am
Give us the defintiion of the tables, as well as the data in the source tables.
Create another result table which will contain the result set that you need based on the source data.
Using that, we can help you build the code you need to get the result.
August 30, 2016 at 8:01 am
Also, please define what you mean by "closest". Closest to what? The start point of your range, the end point of your range, the midpoint of your range?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 31, 2016 at 7:04 am
If you are looking for closest to the start date or end date, you could just use a query to identify all the records that are between them, and then use either MIN(), or MAX() For the date closest to the start date or the end date respectively.
September 1, 2016 at 8:51 pm
Disclaimer - Patience please - this is my first go at DB development.
I have to identify records [sic] in a table that has multiple rows, and from the multiple row, identify the date that is closest between a call_start_date and call_end_date in another table. If the requirement were just identify the existence of a record [sic], an existence check would work. [\quote]
You have made classic beginner mistakes. Rows are absolutely nothing whatsoever like records. This is a fundamental concept in RDBMS. Then in violation of basic netiquette, you fail to post DDL. We have no idea what your tables look like; we just have to guess. (noobs get one pass:crazy:) Then whoever taught you SQL is way out of date! The convert() is an old Sybase legacy thing that was put in for COBOL programmers in the 1980's. It is not legacy code so much as "family curse" code:angry:.
One of the basic principles of data modeling and the ISO 11179 metadata standards is that a data element name does not include metadata. That means naming something "key" is a serious design error. This tells us how something is used and not what it is by its nature.
Since SQL is a database language, not a computational language (this also includes temporal computations!), we usually create a calendar table. It is keyed on the calendar date (and does not need any Phil factor space left in it – – nobody is going to add an extra day to it). The other columns can include the ISO 8601 week date, the ISO 8601, ordinal date, and the Julian day.
My guess the easiest way would be to use the Julian date (and astronomers term; Google it) and get the, the absolute difference among the pairs of start and finish dates for intervals. But we do not have a rule for what to do when something falls on a Wednesday (in German "Mittwoch" (midweek)" or in American "hump day"), which is equi-distant from Monday and and Sunday.
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
September 2, 2016 at 12:50 pm
We are going to need your table definitions and a script to fill them with sample data. (Directly from the articles that you were linked to). I am not clear on your definition of duplication. Within your main table? Across to the other table ? Both?
Let us know what you want the data to look like exactly in the end.
----------------------------------------------------
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply