Get intermediate time periods in SQL Server [closed]
I want to generate a table in SQL of intermediate joined states. E.g. I have the following table
status_1 status_2 start_date_V1 end_date_v1 start_date_2 end_date_v2
--------------------------------------------------------------------------------
A B 01Jan2018 31Jul2018 31Dec2017 31Jan2018
A C 01Jan2018 31Jul2018 01Feb2018 30Dec2018
In this table there are start and end dates of the different states "status_1" and "status_2". I wan to have the information about the changes of the two joined states. The desired table would be:
status_1 status_2 start_date end_date
-----------------------------------------------
A B 01Jan2018 31Jan2018
A C 01Feb2018 31Jul2018
The following image might help to understand the problem:
Can anyone help?
sql
sql-server pivot-table transformation edited Nov 23 '18 at 22:06
mpromonet
6,248103865
asked Nov 23 '18 at 12:49
Lazloo XpLazloo Xp
968
closed as unclear what you're asking by Damien_The_Unbeliever, Cindy Meister, Nic3500, Rob, lagom Nov 24 '18 at 4:05
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
|
show 5 more comments
I want to generate a table in SQL of intermediate joined states. E.g. I have the following table
status_1 status_2 start_date_V1 end_date_v1 start_date_2 end_date_v2
--------------------------------------------------------------------------------
A B 01Jan2018 31Jul2018 31Dec2017 31Jan2018
A C 01Jan2018 31Jul2018 01Feb2018 30Dec2018
In this table there are start and end dates of the different states "status_1" and "status_2". I wan to have the information about the changes of the two joined states. The desired table would be:
status_1 status_2 start_date end_date
-----------------------------------------------
A B 01Jan2018 31Jan2018
A C 01Feb2018 31Jul2018
The following image might help to understand the problem:
Can anyone help?
sql
sql-server pivot-table transformation edited Nov 23 '18 at 22:06
mpromonet
6,248103865
asked Nov 23 '18 at 12:49
Lazloo XpLazloo Xp
968
closed as unclear what you're asking by Damien_The_Unbeliever, Cindy Meister, Nic3500, Rob, lagom Nov 24 '18 at 4:05
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
3
Please explain the logic for getting the results you want. It is not clear what the dates in the result set have to do with the dates in the original data.
– Gordon Linoff
Nov 23 '18 at 12:53
is that a "date" date or a varchar date?
– JonTout
Nov 23 '18 at 13:01
The date is "date" date.
– Lazloo Xp
Nov 23 '18 at 13:04
To the logic: Each row indicates from when the combination of status_1 & status_2 is valid. A & B is only valid between 01Jan2018 and 31Jan2018. A&C is only valid between 01Feb2018 and 31Jul2018
– Lazloo Xp
Nov 23 '18 at 13:07
How are you working that out? Telling us what it represents without telling us how it does doesn't tell us what we need.
– Larnu
Nov 23 '18 at 13:09
|
show 5 more comments
I want to generate a table in SQL of intermediate joined states. E.g. I have the following table
status_1 status_2 start_date_V1 end_date_v1 start_date_2 end_date_v2
--------------------------------------------------------------------------------
A B 01Jan2018 31Jul2018 31Dec2017 31Jan2018
A C 01Jan2018 31Jul2018 01Feb2018 30Dec2018
In this table there are start and end dates of the different states "status_1" and "status_2". I wan to have the information about the changes of the two joined states. The desired table would be:
status_1 status_2 start_date end_date
-----------------------------------------------
A B 01Jan2018 31Jan2018
A C 01Feb2018 31Jul2018
The following image might help to understand the problem:
Can anyone help?
sql
sql-server pivot-table transformation edited Nov 23 '18 at 22:06
mpromonet
6,248103865
asked Nov 23 '18 at 12:49
Lazloo XpLazloo Xp
968
I want to generate a table in SQL of intermediate joined states. E.g. I have the following table
status_1 status_2 start_date_V1 end_date_v1 start_date_2 end_date_v2
--------------------------------------------------------------------------------
A B 01Jan2018 31Jul2018 31Dec2017 31Jan2018
A C 01Jan2018 31Jul2018 01Feb2018 30Dec2018
In this table there are start and end dates of the different states "status_1" and "status_2". I wan to have the information about the changes of the two joined states. The desired table would be:
status_1 status_2 start_date end_date
-----------------------------------------------
A B 01Jan2018 31Jan2018
A C 01Feb2018 31Jul2018
The following image might help to understand the problem:
Can anyone help?
sql
sql-server pivot-table transformation sql
sql-server pivot-table transformation edited Nov 23 '18 at 22:06
mpromonet
6,248103865
asked Nov 23 '18 at 12:49
Lazloo XpLazloo Xp
968
edited Nov 23 '18 at 22:06
mpromonet
6,248103865
asked Nov 23 '18 at 12:49
Lazloo XpLazloo Xp
968
edited Nov 23 '18 at 22:06
mpromonet
6,248103865
edited Nov 23 '18 at 22:06
mpromonet
6,248103865
edited Nov 23 '18 at 22:06
mpromonet
6,248103865
6,248103865
asked Nov 23 '18 at 12:49
Lazloo XpLazloo Xp
968
asked Nov 23 '18 at 12:49
Lazloo XpLazloo Xp
968
asked Nov 23 '18 at 12:49
Lazloo XpLazloo Xp
968
968
closed as unclear what you're asking by Damien_The_Unbeliever, Cindy Meister, Nic3500, Rob, lagom Nov 24 '18 at 4:05
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
closed as unclear what you're asking by Damien_The_Unbeliever, Cindy Meister, Nic3500, Rob, lagom Nov 24 '18 at 4:05
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
3
Please explain the logic for getting the results you want. It is not clear what the dates in the result set have to do with the dates in the original data.
– Gordon Linoff
Nov 23 '18 at 12:53
is that a "date" date or a varchar date?
– JonTout
Nov 23 '18 at 13:01
The date is "date" date.
– Lazloo Xp
Nov 23 '18 at 13:04
To the logic: Each row indicates from when the combination of status_1 & status_2 is valid. A & B is only valid between 01Jan2018 and 31Jan2018. A&C is only valid between 01Feb2018 and 31Jul2018
– Lazloo Xp
Nov 23 '18 at 13:07
How are you working that out? Telling us what it represents without telling us how it does doesn't tell us what we need.
– Larnu
Nov 23 '18 at 13:09
|
show 5 more comments
3
Please explain the logic for getting the results you want. It is not clear what the dates in the result set have to do with the dates in the original data.
– Gordon Linoff
Nov 23 '18 at 12:53
is that a "date" date or a varchar date?
– JonTout
Nov 23 '18 at 13:01
The date is "date" date.
– Lazloo Xp
Nov 23 '18 at 13:04
To the logic: Each row indicates from when the combination of status_1 & status_2 is valid. A & B is only valid between 01Jan2018 and 31Jan2018. A&C is only valid between 01Feb2018 and 31Jul2018
– Lazloo Xp
Nov 23 '18 at 13:07
How are you working that out? Telling us what it represents without telling us how it does doesn't tell us what we need.
– Larnu
Nov 23 '18 at 13:09
3
3
Please explain the logic for getting the results you want. It is not clear what the dates in the result set have to do with the dates in the original data.
– Gordon Linoff
Nov 23 '18 at 12:53
Please explain the logic for getting the results you want. It is not clear what the dates in the result set have to do with the dates in the original data.
– Gordon Linoff
Nov 23 '18 at 12:53
is that a "date" date or a varchar date?
– JonTout
Nov 23 '18 at 13:01
is that a "date" date or a varchar date?
– JonTout
Nov 23 '18 at 13:01
The date is "date" date.
– Lazloo Xp
Nov 23 '18 at 13:04
The date is "date" date.
– Lazloo Xp
Nov 23 '18 at 13:04
To the logic: Each row indicates from when the combination of status_1 & status_2 is valid. A & B is only valid between 01Jan2018 and 31Jan2018. A&C is only valid between 01Feb2018 and 31Jul2018
– Lazloo Xp
Nov 23 '18 at 13:07
To the logic: Each row indicates from when the combination of status_1 & status_2 is valid. A & B is only valid between 01Jan2018 and 31Jan2018. A&C is only valid between 01Feb2018 and 31Jul2018
– Lazloo Xp
Nov 23 '18 at 13:07
How are you working that out? Telling us what it represents without telling us how it does doesn't tell us what we need.
– Larnu
Nov 23 '18 at 13:09
How are you working that out? Telling us what it represents without telling us how it does doesn't tell us what we need.
– Larnu
Nov 23 '18 at 13:09
|
show 5 more comments
1 Answer
1
active
oldest
votes
Seems like you need the intersecting time period(?), that'd be solved with a simple 'CASE-WHEN-ELSE'-statement for each date in the query result.
SELECT
[status1],
[status2],
[start_date] = CASE WHEN [start_date_V1] < [start_date_2] THEN [start_date_V1] ELSE [start_date_2] END,
[end_date] = CASE WHEN [end_date_v1] < [end_date_v2] THEN [end_date_v1] ELSE [end_date_v2] END
FROM Table
If you've got many date columns (known amount), it'd be cleaner to type it as below. However, beware that sub queries like this can slow down your queries tremendously, if you don't know what you're doing.
SELECT
Status1,
Status2,
-- New Name Name of custom group of values Column1 Column2 Name of custom group of values
-- | | | | |
[start_date] = (SELECT MAX(StartDate) FROM (VALUES (start_date_1), (start_date_2)) AS value(StartDate)),
[end_date] = (SELECT MIN(EndDate) FROM (VALUES (end_date_1), (end_date_2)) AS value(EndDate))
FROM Table
answered Nov 23 '18 at 14:01
Erik BlomgrenErik Blomgren
52226
This is possible if I only have two states. However, is there a generalization possible if I have n states?
– Lazloo Xp
Nov 23 '18 at 15:38
Is "n" a known number? I understand that comparing 5-10 columns in a case-when is no fun. If "n" is not a known number it'd have to involve a loop i reckon.
– Erik Blomgren
Nov 23 '18 at 16:24
I added more friendly version for handling many date columns, if the number of date columns is static. If the number of date column isn't known, or changing, you'd have to use dynamic SQL and a loop / cursor.
– Erik Blomgren
Nov 23 '18 at 16:38
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Seems like you need the intersecting time period(?), that'd be solved with a simple 'CASE-WHEN-ELSE'-statement for each date in the query result.
SELECT
[status1],
[status2],
[start_date] = CASE WHEN [start_date_V1] < [start_date_2] THEN [start_date_V1] ELSE [start_date_2] END,
[end_date] = CASE WHEN [end_date_v1] < [end_date_v2] THEN [end_date_v1] ELSE [end_date_v2] END
FROM Table
If you've got many date columns (known amount), it'd be cleaner to type it as below. However, beware that sub queries like this can slow down your queries tremendously, if you don't know what you're doing.
SELECT
Status1,
Status2,
-- New Name Name of custom group of values Column1 Column2 Name of custom group of values
-- | | | | |
[start_date] = (SELECT MAX(StartDate) FROM (VALUES (start_date_1), (start_date_2)) AS value(StartDate)),
[end_date] = (SELECT MIN(EndDate) FROM (VALUES (end_date_1), (end_date_2)) AS value(EndDate))
FROM Table
answered Nov 23 '18 at 14:01
Erik BlomgrenErik Blomgren
52226
This is possible if I only have two states. However, is there a generalization possible if I have n states?
– Lazloo Xp
Nov 23 '18 at 15:38
Is "n" a known number? I understand that comparing 5-10 columns in a case-when is no fun. If "n" is not a known number it'd have to involve a loop i reckon.
– Erik Blomgren
Nov 23 '18 at 16:24
I added more friendly version for handling many date columns, if the number of date columns is static. If the number of date column isn't known, or changing, you'd have to use dynamic SQL and a loop / cursor.
– Erik Blomgren
Nov 23 '18 at 16:38
add a comment |
Seems like you need the intersecting time period(?), that'd be solved with a simple 'CASE-WHEN-ELSE'-statement for each date in the query result.
SELECT
[status1],
[status2],
[start_date] = CASE WHEN [start_date_V1] < [start_date_2] THEN [start_date_V1] ELSE [start_date_2] END,
[end_date] = CASE WHEN [end_date_v1] < [end_date_v2] THEN [end_date_v1] ELSE [end_date_v2] END
FROM Table
If you've got many date columns (known amount), it'd be cleaner to type it as below. However, beware that sub queries like this can slow down your queries tremendously, if you don't know what you're doing.
SELECT
Status1,
Status2,
-- New Name Name of custom group of values Column1 Column2 Name of custom group of values
-- | | | | |
[start_date] = (SELECT MAX(StartDate) FROM (VALUES (start_date_1), (start_date_2)) AS value(StartDate)),
[end_date] = (SELECT MIN(EndDate) FROM (VALUES (end_date_1), (end_date_2)) AS value(EndDate))
FROM Table
answered Nov 23 '18 at 14:01
Erik BlomgrenErik Blomgren
52226
This is possible if I only have two states. However, is there a generalization possible if I have n states?
– Lazloo Xp
Nov 23 '18 at 15:38
Is "n" a known number? I understand that comparing 5-10 columns in a case-when is no fun. If "n" is not a known number it'd have to involve a loop i reckon.
– Erik Blomgren
Nov 23 '18 at 16:24
I added more friendly version for handling many date columns, if the number of date columns is static. If the number of date column isn't known, or changing, you'd have to use dynamic SQL and a loop / cursor.
– Erik Blomgren
Nov 23 '18 at 16:38
add a comment |
Seems like you need the intersecting time period(?), that'd be solved with a simple 'CASE-WHEN-ELSE'-statement for each date in the query result.
SELECT
[status1],
[status2],
[start_date] = CASE WHEN [start_date_V1] < [start_date_2] THEN [start_date_V1] ELSE [start_date_2] END,
[end_date] = CASE WHEN [end_date_v1] < [end_date_v2] THEN [end_date_v1] ELSE [end_date_v2] END
FROM Table
If you've got many date columns (known amount), it'd be cleaner to type it as below. However, beware that sub queries like this can slow down your queries tremendously, if you don't know what you're doing.
SELECT
Status1,
Status2,
-- New Name Name of custom group of values Column1 Column2 Name of custom group of values
-- | | | | |
[start_date] = (SELECT MAX(StartDate) FROM (VALUES (start_date_1), (start_date_2)) AS value(StartDate)),
[end_date] = (SELECT MIN(EndDate) FROM (VALUES (end_date_1), (end_date_2)) AS value(EndDate))
FROM Table
answered Nov 23 '18 at 14:01
Erik BlomgrenErik Blomgren
52226
Seems like you need the intersecting time period(?), that'd be solved with a simple 'CASE-WHEN-ELSE'-statement for each date in the query result.
SELECT
[status1],
[status2],
[start_date] = CASE WHEN [start_date_V1] < [start_date_2] THEN [start_date_V1] ELSE [start_date_2] END,
[end_date] = CASE WHEN [end_date_v1] < [end_date_v2] THEN [end_date_v1] ELSE [end_date_v2] END
FROM Table
If you've got many date columns (known amount), it'd be cleaner to type it as below. However, beware that sub queries like this can slow down your queries tremendously, if you don't know what you're doing.
SELECT
Status1,
Status2,
-- New Name Name of custom group of values Column1 Column2 Name of custom group of values
-- | | | | |
[start_date] = (SELECT MAX(StartDate) FROM (VALUES (start_date_1), (start_date_2)) AS value(StartDate)),
[end_date] = (SELECT MIN(EndDate) FROM (VALUES (end_date_1), (end_date_2)) AS value(EndDate))
FROM Table
answered Nov 23 '18 at 14:01
Erik BlomgrenErik Blomgren
52226
edited Nov 23 '18 at 16:45
answered Nov 23 '18 at 14:01
Erik BlomgrenErik Blomgren
52226
answered Nov 23 '18 at 14:01
Erik BlomgrenErik Blomgren
52226
answered Nov 23 '18 at 14:01
Erik BlomgrenErik Blomgren
52226
52226
This is possible if I only have two states. However, is there a generalization possible if I have n states?
– Lazloo Xp
Nov 23 '18 at 15:38
Is "n" a known number? I understand that comparing 5-10 columns in a case-when is no fun. If "n" is not a known number it'd have to involve a loop i reckon.
– Erik Blomgren
Nov 23 '18 at 16:24
I added more friendly version for handling many date columns, if the number of date columns is static. If the number of date column isn't known, or changing, you'd have to use dynamic SQL and a loop / cursor.
– Erik Blomgren
Nov 23 '18 at 16:38
add a comment |
This is possible if I only have two states. However, is there a generalization possible if I have n states?
– Lazloo Xp
Nov 23 '18 at 15:38
Is "n" a known number? I understand that comparing 5-10 columns in a case-when is no fun. If "n" is not a known number it'd have to involve a loop i reckon.
– Erik Blomgren
Nov 23 '18 at 16:24
I added more friendly version for handling many date columns, if the number of date columns is static. If the number of date column isn't known, or changing, you'd have to use dynamic SQL and a loop / cursor.
– Erik Blomgren
Nov 23 '18 at 16:38
This is possible if I only have two states. However, is there a generalization possible if I have n states?
– Lazloo Xp
Nov 23 '18 at 15:38
This is possible if I only have two states. However, is there a generalization possible if I have n states?
– Lazloo Xp
Nov 23 '18 at 15:38
Is "n" a known number? I understand that comparing 5-10 columns in a case-when is no fun. If "n" is not a known number it'd have to involve a loop i reckon.
– Erik Blomgren
Nov 23 '18 at 16:24
Is "n" a known number? I understand that comparing 5-10 columns in a case-when is no fun. If "n" is not a known number it'd have to involve a loop i reckon.
– Erik Blomgren
Nov 23 '18 at 16:24
I added more friendly version for handling many date columns, if the number of date columns is static. If the number of date column isn't known, or changing, you'd have to use dynamic SQL and a loop / cursor.
– Erik Blomgren
Nov 23 '18 at 16:38
I added more friendly version for handling many date columns, if the number of date columns is static. If the number of date column isn't known, or changing, you'd have to use dynamic SQL and a loop / cursor.
– Erik Blomgren
Nov 23 '18 at 16:38
add a comment |
3
Please explain the logic for getting the results you want. It is not clear what the dates in the result set have to do with the dates in the original data.
– Gordon Linoff
Nov 23 '18 at 12:53
is that a "date" date or a varchar date?
– JonTout
Nov 23 '18 at 13:01
The date is "date" date.
– Lazloo Xp
Nov 23 '18 at 13:04
To the logic: Each row indicates from when the combination of status_1 & status_2 is valid. A & B is only valid between 01Jan2018 and 31Jan2018. A&C is only valid between 01Feb2018 and 31Jul2018
– Lazloo Xp
Nov 23 '18 at 13:07
How are you working that out? Telling us what it represents without telling us how it does doesn't tell us what we need.
– Larnu
Nov 23 '18 at 13:09