October 10, 2018 at 9:20 pm
Hi All,
I have a table design like below. What is the best to identify most recent date from StartDate and EndDate on Table_1 and then find the oldest date from StartDate and EndDate on Table_2 finally compare the most recent date found from table_1 with oldest date from Table_2. Is it possible to do with a single query or I should consider get and keep the dates in a temporary table then compare? Thanks in advance for your help
CREATE TABLE [dbo].[Table_1](
[id] [int] IDENTITY(1,1) NOT NULL,
[StartDate] [datetime] NULL,
[EndDate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Table_2](
[id] [int] IDENTITY(1,1) NOT NULL,
[StartDate] [datetime] NULL,
[EndDate] [datetime] NULL
) ON [PRIMARY]
GO
Regards,
Salam
October 11, 2018 at 6:52 am
This can be one way of comparing the min date row in one table to max date row in another table
; WITH CTE_1 AS (SELECT * , ROW_NUMBER() OVER(ORDER BY startdate DESC , enddate DESC) AS orderby FROM Table_1) ,
CTE_2 AS
(SELECT * , ROW_NUMBER() OVER(ORDER BY startdate , enddate ) AS orderby FROM Table_2)
SELECT * FROM CTE_1 c1 inner join CTE_2 c2
ON c1.orderby = c2.orderby
October 15, 2018 at 10:19 am
salamlemon - Wednesday, October 10, 2018 9:20 PMHi All,I have a table design like below. What is the best to identify most recent date from StartDate and EndDate on Table_1 and then find the oldest date from StartDate and EndDate on Table_2 finally compare the most recent date found from table_1 with oldest date from Table_2. Is it possible to do with a single query or I should consider get and keep the dates in a temporary table then compare? Thanks in advance for your help
CREATE TABLE [dbo].[Table_1](
[id] [int] IDENTITY(1,1) NOT NULL,
[StartDate] [datetime] NULL,
[EndDate] [datetime] NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[Table_2](
[id] [int] IDENTITY(1,1) NOT NULL,
[StartDate] [datetime] NULL,
[EndDate] [datetime] NULL
) ON [PRIMARY]
GORegards,
Salam
Before you look at solutions, you need to better state the problem. How, exactly, does one determine, that a pair of dates is the earliest or the latest, for either table? If you only had one date field in each table, this would be a piece of cake, but until we have criteria that determines exactly which row in each table is the "earliest" or the "latest", we quite literally have nothing to go on. It boils down to what rule do you use to determine that a given date pair is the "earliest"? Does the start date alone answer that question? Similarly, does the end date alone decide which row as the "latest" ?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 15, 2018 at 10:56 am
;WITH CTE AS
(
SELECT MAX(X.TABLE_1Dates) NewestDate -- Newest Date From TABLE_1
FROM TABLE_1 A
CROSS APPLY(VALUES (A.StartDate),(A.EndDate)) X(TABLE_1Dates)
),
CTE2 AS
(
SELECT MIN(X.TABLE_2Dates) OldestDate -- Oldest Date From TABLE_2
FROM TABLE_2 A
CROSS APPLY(VALUES (A.StartDate),(A.EndDate)) X(TABLE_2Dates)
)
SELECT *
FROM CTE,CTE2
October 15, 2018 at 11:15 am
sgmunson - Monday, October 15, 2018 10:19 AMsalamlemon - Wednesday, October 10, 2018 9:20 PMHi All,I have a table design like below. What is the best to identify most recent date from StartDate and EndDate on Table_1 and then find the oldest date from StartDate and EndDate on Table_2 finally compare the most recent date found from table_1 with oldest date from Table_2. Is it possible to do with a single query or I should consider get and keep the dates in a temporary table then compare? Thanks in advance for your help
CREATE TABLE [dbo].[Table_1](
[id] [int] IDENTITY(1,1) NOT NULL,
[StartDate] [datetime] NULL,
[EndDate] [datetime] NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[Table_2](
[id] [int] IDENTITY(1,1) NOT NULL,
[StartDate] [datetime] NULL,
[EndDate] [datetime] NULL
) ON [PRIMARY]
GORegards,
SalamBefore you look at solutions, you need to better state the problem. How, exactly, does one determine, that a pair of dates is the earliest or the latest, for either table? If you only had one date field in each table, this would be a piece of cake, but until we have criteria that determines exactly which row in each table is the "earliest" or the "latest", we quite literally have nothing to go on. It boils down to what rule do you use to determine that a given date pair is the "earliest"? Does the start date alone answer that question? Similarly, does the end date alone decide which row as the "latest" ?
Further more, is there date range crossovers? For instance, can you have one line with dates 10/01/2018 (start) and 10/15/2018 (end) and another line with 09/16/2018 (start) to 10/15/2018 (end)?
If so, which line do you consider most recent? What about lines that start with the same date but end with different dates? What if you have multiple lines crossing over each other's date range?
October 15, 2018 at 11:25 am
Brandie Tarvin - Monday, October 15, 2018 11:15 AMsgmunson - Monday, October 15, 2018 10:19 AMsalamlemon - Wednesday, October 10, 2018 9:20 PMHi All,I have a table design like below. What is the best to identify most recent date from StartDate and EndDate on Table_1 and then find the oldest date from StartDate and EndDate on Table_2 finally compare the most recent date found from table_1 with oldest date from Table_2. Is it possible to do with a single query or I should consider get and keep the dates in a temporary table then compare? Thanks in advance for your help
CREATE TABLE [dbo].[Table_1](
[id] [int] IDENTITY(1,1) NOT NULL,
[StartDate] [datetime] NULL,
[EndDate] [datetime] NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[Table_2](
[id] [int] IDENTITY(1,1) NOT NULL,
[StartDate] [datetime] NULL,
[EndDate] [datetime] NULL
) ON [PRIMARY]
GORegards,
SalamBefore you look at solutions, you need to better state the problem. How, exactly, does one determine, that a pair of dates is the earliest or the latest, for either table? If you only had one date field in each table, this would be a piece of cake, but until we have criteria that determines exactly which row in each table is the "earliest" or the "latest", we quite literally have nothing to go on. It boils down to what rule do you use to determine that a given date pair is the "earliest"? Does the start date alone answer that question? Similarly, does the end date alone decide which row as the "latest" ?
Further more, is there date range crossovers? For instance, can you have one line with dates 10/01/2018 (start) and 10/15/2018 (end) and another line with 09/16/2018 (start) to 10/15/2018 (end)?
If so, which line do you consider most recent? What about lines that start with the same date but end with different dates? What if you have multiple lines crossing over each other's date range?
The way I read the question was to find the earliest date out of StartDate and EndDate, meaning the start date could be later than the end date. That's how the question is worded but I don't think that is how the data will be.
October 15, 2018 at 11:31 am
Jonathan AC Roberts - Monday, October 15, 2018 11:25 AMBrandie Tarvin - Monday, October 15, 2018 11:15 AMsgmunson - Monday, October 15, 2018 10:19 AMsalamlemon - Wednesday, October 10, 2018 9:20 PMHi All,I have a table design like below. What is the best to identify most recent date from StartDate and EndDate on Table_1 and then find the oldest date from StartDate and EndDate on Table_2 finally compare the most recent date found from table_1 with oldest date from Table_2. Is it possible to do with a single query or I should consider get and keep the dates in a temporary table then compare? Thanks in advance for your help
CREATE TABLE [dbo].[Table_1](
[id] [int] IDENTITY(1,1) NOT NULL,
[StartDate] [datetime] NULL,
[EndDate] [datetime] NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[Table_2](
[id] [int] IDENTITY(1,1) NOT NULL,
[StartDate] [datetime] NULL,
[EndDate] [datetime] NULL
) ON [PRIMARY]
GORegards,
SalamBefore you look at solutions, you need to better state the problem. How, exactly, does one determine, that a pair of dates is the earliest or the latest, for either table? If you only had one date field in each table, this would be a piece of cake, but until we have criteria that determines exactly which row in each table is the "earliest" or the "latest", we quite literally have nothing to go on. It boils down to what rule do you use to determine that a given date pair is the "earliest"? Does the start date alone answer that question? Similarly, does the end date alone decide which row as the "latest" ?
Further more, is there date range crossovers? For instance, can you have one line with dates 10/01/2018 (start) and 10/15/2018 (end) and another line with 09/16/2018 (start) to 10/15/2018 (end)?
If so, which line do you consider most recent? What about lines that start with the same date but end with different dates? What if you have multiple lines crossing over each other's date range?
The way I read the question was to find the earliest date out of StartDate and EndDate, meaning the start date could be later than the end date. That's how the question is worded but I don't think that is how the data will be.
My environment is set up to use date ranges like this, so when I read the question, I read it as a reference to the entire date range as opposed to one date or the other. So, yes, the OP needs to clarify what (s)he's looking for.
October 15, 2018 at 3:25 pm
salamlemon - Wednesday, October 10, 2018 9:20 PM
>> I have a table design like below. <<
thank you for posting DDL; so many people don’t. However, as you can see from the responses, it’s not very clear what’s really going on. Remember that 80 – 90% of the work in SQL is done with the DDL. Once the DDL is clear, the DML usually follows easily.
I’m going to assume that you know that an identity table property cannot be used as a column, and therefore cannot be a key. I hope you also know that by definition (it’s not optional!), all tables must have a key. We also need some constraints to prevent an interval from starting before it’s finished. The usual convention is to use the ISO half-open interval model, so the end date has to be nullable in the start date cannot be. We also see that these two tables have no relationship. A single reference from one to the other, nor do the reference a common table. Basically, you’ve just used SQL to model a 1950s deck of punch cards! Here are some guesses and repairing the DDL.
Version one: allows duplicate intervals, no relationship
CREATE TABLE Foo
(foo_id CHAR(5) NOT NULL PRIMARY KEY,
foo_start_date DATE NOT NULL,
foo_end_date DATE,
CHECK (foo_start_date <= foo_end_date));
CREATE TABLE Bar
(bar_id CHAR(5) NOT NULL PRIMARY KEY,
bar_start_date DATE NOT NULL,
bar_end_date DATE,
CHECK (foo_start_date <= foo_end_date));
Version Two: unique intervals
CREATE TABLE Foo
(foo_start_date DATE NOT NULL PRIMARY KEY,
foo_end_date DATE,
UNIQUE (foo_start_date, foo_end_date),
CHECK (foo_start_date <= foo_end_date));
CREATE TABLE Bar
(bar_start_date DATE NOT NULL PRIMARY KEY,
bar_end_date DATE,
UNIQUE (foo_start_date, foo_end_date)
CHECK (foo_start_date <= foo_end_date));
Version Three: single table
CREATE TABLE FooBar
(start_date DATE NOT NULL,
end_date DATE,
CHECK (start_date <= end_date),
event_type CHAR(3) NOT NULL
CHECK(event_type IN (‘foo’, ‘bar’),
PRIMARY KEY (start_date, event_type));
>> What is the best to identify most recent date StartDate and EndDate on table Foo and then find the oldest date from StartDate and EndDate on table Bar finally compare the most recent date found from table Foo with the oldest date from table Bar. Is it possible to do with a single query or I should consider get and keep the dates in a temporary table then compare? <<
Please answer the question that others asked, and give us some sample data.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply