Shorthand to compare multiple columns against same condition in SQL Server?
0
Does a shorthand exist that allows you to compare multiple columns against the same condition in the WHERE clause?
SELECT *
FROM [Table]
WHERE [Date1] BETWEEN x AND y
OR [Date2] BETWEEN x AND y
OR [Date3] BETWEEN x and y
OR [Date4] BETWEEN x and y
It's not the end of the world to copy and paste this condition and replace [Date x] with each column, but it sure isn't fun.
sql
sql-server tsql edited Nov 21 at 3:44
Rahul Neekhra
6021627
asked Nov 20 at 17:27
Jacob Armstrong
141
|
show 1 more comment
0
Does a shorthand exist that allows you to compare multiple columns against the same condition in the WHERE clause?
SELECT *
FROM [Table]
WHERE [Date1] BETWEEN x AND y
OR [Date2] BETWEEN x AND y
OR [Date3] BETWEEN x and y
OR [Date4] BETWEEN x and y
It's not the end of the world to copy and paste this condition and replace [Date x] with each column, but it sure isn't fun.
sql
sql-server tsql edited Nov 21 at 3:44
Rahul Neekhra
6021627
asked Nov 20 at 17:27
Jacob Armstrong
141
1
The only way would be to create a string and execute the string with sp_executesql. This is probably not a good solution due to performance, but I don't think there's another way.
– Jon Vote
Nov 20 at 17:34
2
When I see this I can't help but think that the design is not well normalized. There really shouldn't be all that many datetime columns in most tables.
– Sean Lange
Nov 20 at 19:26
Maybe you could look into doing something with an UNPIVOT.
– Tab Alleman
Nov 20 at 19:32
2
@SeanLange While I don't have the power to change the database structure (just an analyst at a company), I would love to hear opinions on a better structure for my own knowledge. The table I'm working with is a "Status" table for mortgage loans where there's a single row for each loan and multiple columns for each status containing the respective date the loan was placed into that status. What would be a better structure for this relationship?
– Jacob Armstrong
Nov 20 at 20:33
1
Instead of columns for each status those should be rows. I would do something like have a "parent" row for the loan and then another table for LoanStatus and each row would have the LoanID, StatusID and StatusDate or something close to that. It allows multiple times a given status could be used etc and provides a more accurate picture of the history. Poorly normalized data structures are definitely going to be an uphill battle from the sound of the design you are working with here.
– Sean Lange
Nov 20 at 21:12
|
show 1 more comment
0
0
0
Does a shorthand exist that allows you to compare multiple columns against the same condition in the WHERE clause?
SELECT *
FROM [Table]
WHERE [Date1] BETWEEN x AND y
OR [Date2] BETWEEN x AND y
OR [Date3] BETWEEN x and y
OR [Date4] BETWEEN x and y
It's not the end of the world to copy and paste this condition and replace [Date x] with each column, but it sure isn't fun.
sql
sql-server tsql edited Nov 21 at 3:44
Rahul Neekhra
6021627
asked Nov 20 at 17:27
Jacob Armstrong
141
Does a shorthand exist that allows you to compare multiple columns against the same condition in the WHERE clause?
SELECT *
FROM [Table]
WHERE [Date1] BETWEEN x AND y
OR [Date2] BETWEEN x AND y
OR [Date3] BETWEEN x and y
OR [Date4] BETWEEN x and y
It's not the end of the world to copy and paste this condition and replace [Date x] with each column, but it sure isn't fun.
sql
sql-server tsql sql
sql-server tsql edited Nov 21 at 3:44
Rahul Neekhra
6021627
asked Nov 20 at 17:27
Jacob Armstrong
141
edited Nov 21 at 3:44
Rahul Neekhra
6021627
asked Nov 20 at 17:27
Jacob Armstrong
141
edited Nov 21 at 3:44
Rahul Neekhra
6021627
edited Nov 21 at 3:44
Rahul Neekhra
6021627
edited Nov 21 at 3:44
Rahul Neekhra
6021627
6021627
asked Nov 20 at 17:27
Jacob Armstrong
141
asked Nov 20 at 17:27
Jacob Armstrong
141
asked Nov 20 at 17:27
Jacob Armstrong
141
141
1
The only way would be to create a string and execute the string with sp_executesql. This is probably not a good solution due to performance, but I don't think there's another way.
– Jon Vote
Nov 20 at 17:34
2
When I see this I can't help but think that the design is not well normalized. There really shouldn't be all that many datetime columns in most tables.
– Sean Lange
Nov 20 at 19:26
Maybe you could look into doing something with an UNPIVOT.
– Tab Alleman
Nov 20 at 19:32
2
@SeanLange While I don't have the power to change the database structure (just an analyst at a company), I would love to hear opinions on a better structure for my own knowledge. The table I'm working with is a "Status" table for mortgage loans where there's a single row for each loan and multiple columns for each status containing the respective date the loan was placed into that status. What would be a better structure for this relationship?
– Jacob Armstrong
Nov 20 at 20:33
1
Instead of columns for each status those should be rows. I would do something like have a "parent" row for the loan and then another table for LoanStatus and each row would have the LoanID, StatusID and StatusDate or something close to that. It allows multiple times a given status could be used etc and provides a more accurate picture of the history. Poorly normalized data structures are definitely going to be an uphill battle from the sound of the design you are working with here.
– Sean Lange
Nov 20 at 21:12
|
show 1 more comment
1
The only way would be to create a string and execute the string with sp_executesql. This is probably not a good solution due to performance, but I don't think there's another way.
– Jon Vote
Nov 20 at 17:34
2
When I see this I can't help but think that the design is not well normalized. There really shouldn't be all that many datetime columns in most tables.
– Sean Lange
Nov 20 at 19:26
Maybe you could look into doing something with an UNPIVOT.
– Tab Alleman
Nov 20 at 19:32
2
@SeanLange While I don't have the power to change the database structure (just an analyst at a company), I would love to hear opinions on a better structure for my own knowledge. The table I'm working with is a "Status" table for mortgage loans where there's a single row for each loan and multiple columns for each status containing the respective date the loan was placed into that status. What would be a better structure for this relationship?
– Jacob Armstrong
Nov 20 at 20:33
1
Instead of columns for each status those should be rows. I would do something like have a "parent" row for the loan and then another table for LoanStatus and each row would have the LoanID, StatusID and StatusDate or something close to that. It allows multiple times a given status could be used etc and provides a more accurate picture of the history. Poorly normalized data structures are definitely going to be an uphill battle from the sound of the design you are working with here.
– Sean Lange
Nov 20 at 21:12
1
1
The only way would be to create a string and execute the string with sp_executesql. This is probably not a good solution due to performance, but I don't think there's another way.
– Jon Vote
Nov 20 at 17:34
The only way would be to create a string and execute the string with sp_executesql. This is probably not a good solution due to performance, but I don't think there's another way.
– Jon Vote
Nov 20 at 17:34
2
2
When I see this I can't help but think that the design is not well normalized. There really shouldn't be all that many datetime columns in most tables.
– Sean Lange
Nov 20 at 19:26
When I see this I can't help but think that the design is not well normalized. There really shouldn't be all that many datetime columns in most tables.
– Sean Lange
Nov 20 at 19:26
Maybe you could look into doing something with an UNPIVOT.
– Tab Alleman
Nov 20 at 19:32
Maybe you could look into doing something with an UNPIVOT.
– Tab Alleman
Nov 20 at 19:32
2
2
@SeanLange While I don't have the power to change the database structure (just an analyst at a company), I would love to hear opinions on a better structure for my own knowledge. The table I'm working with is a "Status" table for mortgage loans where there's a single row for each loan and multiple columns for each status containing the respective date the loan was placed into that status. What would be a better structure for this relationship?
– Jacob Armstrong
Nov 20 at 20:33
@SeanLange While I don't have the power to change the database structure (just an analyst at a company), I would love to hear opinions on a better structure for my own knowledge. The table I'm working with is a "Status" table for mortgage loans where there's a single row for each loan and multiple columns for each status containing the respective date the loan was placed into that status. What would be a better structure for this relationship?
– Jacob Armstrong
Nov 20 at 20:33
1
1
Instead of columns for each status those should be rows. I would do something like have a "parent" row for the loan and then another table for LoanStatus and each row would have the LoanID, StatusID and StatusDate or something close to that. It allows multiple times a given status could be used etc and provides a more accurate picture of the history. Poorly normalized data structures are definitely going to be an uphill battle from the sound of the design you are working with here.
– Sean Lange
Nov 20 at 21:12
Instead of columns for each status those should be rows. I would do something like have a "parent" row for the loan and then another table for LoanStatus and each row would have the LoanID, StatusID and StatusDate or something close to that. It allows multiple times a given status could be used etc and provides a more accurate picture of the history. Poorly normalized data structures are definitely going to be an uphill battle from the sound of the design you are working with here.
– Sean Lange
Nov 20 at 21:12
|
show 1 more comment
2 Answers
2
active
oldest
votes
2
You can also write the query like this (in SQL Server 2008 or later):
SELECT * FROM [Table]
WHERE EXISTS (
SELECT *
FROM (VALUES (Date1),(Date2),(Date3),(Date4)) v (TheDate)
WHERE TheDate BETWEEN x AND y
)
However, I don't see any benefits of doing so (in terms of peformance or readability).
Of course, things would be different if you need to write Date1=x OR Date2=x OR Date3=x OR Date4=x, because in this case you can simply write x IN (Date1, Date2, Date3, Date4).
answered Nov 20 at 19:38
Razvan Socol
2,3941921
add a comment |
1
You could use cross apply and values, but the result is even more cumbersome than the code you have right now:
SELECT *
FROM [Table]
CROSS APPLY
(
SELECT MIN([Date]) As MinDate,
MAX([Date]) As MaxDate
FROM (VALUES ([Date1]), ([Date2]), ([Date3]), ([Date4])) VALS([Date])
)
WHERE MinDate <= y
AND MaxDate >= x
AND x <= y
With that being said, I agree with Sean Lange's comment - Seems like the table structure is ill-designed and all these dates values should be in a different table, referenced by this table with a one-to-many relationship.
answered Nov 20 at 19:33
Zohar Peled
52.4k73273
Your query does not return the same results as the original query. At a first glance, you could say it is fixable by replacing < with <= and > with >=. However, even after that it won't return the same results if x>y (it should return nothing in this case).
– Razvan Socol
Nov 20 at 19:46
2
ifx > ythenmindate < y and maxdate > xwill return false. However you are correct about the inclusiveness ofbetween...and. A good read: What do BETWEEN and the devil have in common? by Aaron Bertrand.
– Zohar Peled
Nov 20 at 19:53
How about when x=3, y=2, Date1=1 and Date2=Date3=Date4=4?
– Razvan Socol
Nov 21 at 5:49
@RazvanSocol correct. Also easily fixable by addingand x <= y, though...
– Zohar Peled
Nov 21 at 8:01
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53398382%2fshorthand-to-compare-multiple-columns-against-same-condition-in-sql-server%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
2
You can also write the query like this (in SQL Server 2008 or later):
SELECT * FROM [Table]
WHERE EXISTS (
SELECT *
FROM (VALUES (Date1),(Date2),(Date3),(Date4)) v (TheDate)
WHERE TheDate BETWEEN x AND y
)
However, I don't see any benefits of doing so (in terms of peformance or readability).
Of course, things would be different if you need to write Date1=x OR Date2=x OR Date3=x OR Date4=x, because in this case you can simply write x IN (Date1, Date2, Date3, Date4).
answered Nov 20 at 19:38
Razvan Socol
2,3941921
add a comment |
2
You can also write the query like this (in SQL Server 2008 or later):
SELECT * FROM [Table]
WHERE EXISTS (
SELECT *
FROM (VALUES (Date1),(Date2),(Date3),(Date4)) v (TheDate)
WHERE TheDate BETWEEN x AND y
)
However, I don't see any benefits of doing so (in terms of peformance or readability).
Of course, things would be different if you need to write Date1=x OR Date2=x OR Date3=x OR Date4=x, because in this case you can simply write x IN (Date1, Date2, Date3, Date4).
answered Nov 20 at 19:38
Razvan Socol
2,3941921
add a comment |
2
2
2
You can also write the query like this (in SQL Server 2008 or later):
SELECT * FROM [Table]
WHERE EXISTS (
SELECT *
FROM (VALUES (Date1),(Date2),(Date3),(Date4)) v (TheDate)
WHERE TheDate BETWEEN x AND y
)
However, I don't see any benefits of doing so (in terms of peformance or readability).
Of course, things would be different if you need to write Date1=x OR Date2=x OR Date3=x OR Date4=x, because in this case you can simply write x IN (Date1, Date2, Date3, Date4).
answered Nov 20 at 19:38
Razvan Socol
2,3941921
You can also write the query like this (in SQL Server 2008 or later):
SELECT * FROM [Table]
WHERE EXISTS (
SELECT *
FROM (VALUES (Date1),(Date2),(Date3),(Date4)) v (TheDate)
WHERE TheDate BETWEEN x AND y
)
However, I don't see any benefits of doing so (in terms of peformance or readability).
Of course, things would be different if you need to write Date1=x OR Date2=x OR Date3=x OR Date4=x, because in this case you can simply write x IN (Date1, Date2, Date3, Date4).
answered Nov 20 at 19:38
Razvan Socol
2,3941921
answered Nov 20 at 19:38
Razvan Socol
2,3941921
answered Nov 20 at 19:38
Razvan Socol
2,3941921
answered Nov 20 at 19:38
Razvan Socol
2,3941921
2,3941921
add a comment |
add a comment |
1
You could use cross apply and values, but the result is even more cumbersome than the code you have right now:
SELECT *
FROM [Table]
CROSS APPLY
(
SELECT MIN([Date]) As MinDate,
MAX([Date]) As MaxDate
FROM (VALUES ([Date1]), ([Date2]), ([Date3]), ([Date4])) VALS([Date])
)
WHERE MinDate <= y
AND MaxDate >= x
AND x <= y
With that being said, I agree with Sean Lange's comment - Seems like the table structure is ill-designed and all these dates values should be in a different table, referenced by this table with a one-to-many relationship.
answered Nov 20 at 19:33
Zohar Peled
52.4k73273
Your query does not return the same results as the original query. At a first glance, you could say it is fixable by replacing < with <= and > with >=. However, even after that it won't return the same results if x>y (it should return nothing in this case).
– Razvan Socol
Nov 20 at 19:46
2
ifx > ythenmindate < y and maxdate > xwill return false. However you are correct about the inclusiveness ofbetween...and. A good read: What do BETWEEN and the devil have in common? by Aaron Bertrand.
– Zohar Peled
Nov 20 at 19:53
How about when x=3, y=2, Date1=1 and Date2=Date3=Date4=4?
– Razvan Socol
Nov 21 at 5:49
@RazvanSocol correct. Also easily fixable by addingand x <= y, though...
– Zohar Peled
Nov 21 at 8:01
add a comment |
1
You could use cross apply and values, but the result is even more cumbersome than the code you have right now:
SELECT *
FROM [Table]
CROSS APPLY
(
SELECT MIN([Date]) As MinDate,
MAX([Date]) As MaxDate
FROM (VALUES ([Date1]), ([Date2]), ([Date3]), ([Date4])) VALS([Date])
)
WHERE MinDate <= y
AND MaxDate >= x
AND x <= y
With that being said, I agree with Sean Lange's comment - Seems like the table structure is ill-designed and all these dates values should be in a different table, referenced by this table with a one-to-many relationship.
answered Nov 20 at 19:33
Zohar Peled
52.4k73273
Your query does not return the same results as the original query. At a first glance, you could say it is fixable by replacing < with <= and > with >=. However, even after that it won't return the same results if x>y (it should return nothing in this case).
– Razvan Socol
Nov 20 at 19:46
2
ifx > ythenmindate < y and maxdate > xwill return false. However you are correct about the inclusiveness ofbetween...and. A good read: What do BETWEEN and the devil have in common? by Aaron Bertrand.
– Zohar Peled
Nov 20 at 19:53
How about when x=3, y=2, Date1=1 and Date2=Date3=Date4=4?
– Razvan Socol
Nov 21 at 5:49
@RazvanSocol correct. Also easily fixable by addingand x <= y, though...
– Zohar Peled
Nov 21 at 8:01
add a comment |
1
1
1
You could use cross apply and values, but the result is even more cumbersome than the code you have right now:
SELECT *
FROM [Table]
CROSS APPLY
(
SELECT MIN([Date]) As MinDate,
MAX([Date]) As MaxDate
FROM (VALUES ([Date1]), ([Date2]), ([Date3]), ([Date4])) VALS([Date])
)
WHERE MinDate <= y
AND MaxDate >= x
AND x <= y
With that being said, I agree with Sean Lange's comment - Seems like the table structure is ill-designed and all these dates values should be in a different table, referenced by this table with a one-to-many relationship.
answered Nov 20 at 19:33
Zohar Peled
52.4k73273
You could use cross apply and values, but the result is even more cumbersome than the code you have right now:
SELECT *
FROM [Table]
CROSS APPLY
(
SELECT MIN([Date]) As MinDate,
MAX([Date]) As MaxDate
FROM (VALUES ([Date1]), ([Date2]), ([Date3]), ([Date4])) VALS([Date])
)
WHERE MinDate <= y
AND MaxDate >= x
AND x <= y
With that being said, I agree with Sean Lange's comment - Seems like the table structure is ill-designed and all these dates values should be in a different table, referenced by this table with a one-to-many relationship.
answered Nov 20 at 19:33
Zohar Peled
52.4k73273
edited Nov 21 at 8:02
answered Nov 20 at 19:33
Zohar Peled
52.4k73273
answered Nov 20 at 19:33
Zohar Peled
52.4k73273
answered Nov 20 at 19:33
Zohar Peled
52.4k73273
52.4k73273
Your query does not return the same results as the original query. At a first glance, you could say it is fixable by replacing < with <= and > with >=. However, even after that it won't return the same results if x>y (it should return nothing in this case).
– Razvan Socol
Nov 20 at 19:46
2
ifx > ythenmindate < y and maxdate > xwill return false. However you are correct about the inclusiveness ofbetween...and. A good read: What do BETWEEN and the devil have in common? by Aaron Bertrand.
– Zohar Peled
Nov 20 at 19:53
How about when x=3, y=2, Date1=1 and Date2=Date3=Date4=4?
– Razvan Socol
Nov 21 at 5:49
@RazvanSocol correct. Also easily fixable by addingand x <= y, though...
– Zohar Peled
Nov 21 at 8:01
add a comment |
Your query does not return the same results as the original query. At a first glance, you could say it is fixable by replacing < with <= and > with >=. However, even after that it won't return the same results if x>y (it should return nothing in this case).
– Razvan Socol
Nov 20 at 19:46
2
ifx > ythenmindate < y and maxdate > xwill return false. However you are correct about the inclusiveness ofbetween...and. A good read: What do BETWEEN and the devil have in common? by Aaron Bertrand.
– Zohar Peled
Nov 20 at 19:53
How about when x=3, y=2, Date1=1 and Date2=Date3=Date4=4?
– Razvan Socol
Nov 21 at 5:49
@RazvanSocol correct. Also easily fixable by addingand x <= y, though...
– Zohar Peled
Nov 21 at 8:01
Your query does not return the same results as the original query. At a first glance, you could say it is fixable by replacing < with <= and > with >=. However, even after that it won't return the same results if x>y (it should return nothing in this case).
– Razvan Socol
Nov 20 at 19:46
Your query does not return the same results as the original query. At a first glance, you could say it is fixable by replacing < with <= and > with >=. However, even after that it won't return the same results if x>y (it should return nothing in this case).
– Razvan Socol
Nov 20 at 19:46
2
2
if
x > y then mindate < y and maxdate > x will return false. However you are correct about the inclusiveness of between...and. A good read: What do BETWEEN and the devil have in common? by Aaron Bertrand.– Zohar Peled
Nov 20 at 19:53
if
x > y then mindate < y and maxdate > x will return false. However you are correct about the inclusiveness of between...and. A good read: What do BETWEEN and the devil have in common? by Aaron Bertrand.– Zohar Peled
Nov 20 at 19:53
How about when x=3, y=2, Date1=1 and Date2=Date3=Date4=4?
– Razvan Socol
Nov 21 at 5:49
How about when x=3, y=2, Date1=1 and Date2=Date3=Date4=4?
– Razvan Socol
Nov 21 at 5:49
@RazvanSocol correct. Also easily fixable by adding
and x <= y, though...– Zohar Peled
Nov 21 at 8:01
@RazvanSocol correct. Also easily fixable by adding
and x <= y, though...– Zohar Peled
Nov 21 at 8:01
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53398382%2fshorthand-to-compare-multiple-columns-against-same-condition-in-sql-server%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
The only way would be to create a string and execute the string with sp_executesql. This is probably not a good solution due to performance, but I don't think there's another way.
– Jon Vote
Nov 20 at 17:34
2
When I see this I can't help but think that the design is not well normalized. There really shouldn't be all that many datetime columns in most tables.
– Sean Lange
Nov 20 at 19:26
Maybe you could look into doing something with an UNPIVOT.
– Tab Alleman
Nov 20 at 19:32
2
@SeanLange While I don't have the power to change the database structure (just an analyst at a company), I would love to hear opinions on a better structure for my own knowledge. The table I'm working with is a "Status" table for mortgage loans where there's a single row for each loan and multiple columns for each status containing the respective date the loan was placed into that status. What would be a better structure for this relationship?
– Jacob Armstrong
Nov 20 at 20:33
1
Instead of columns for each status those should be rows. I would do something like have a "parent" row for the loan and then another table for LoanStatus and each row would have the LoanID, StatusID and StatusDate or something close to that. It allows multiple times a given status could be used etc and provides a more accurate picture of the history. Poorly normalized data structures are definitely going to be an uphill battle from the sound of the design you are working with here.
– Sean Lange
Nov 20 at 21:12