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 '18 at 3:44
Rahul Neekhra
6001627
asked Nov 20 '18 at 17:27
Jacob ArmstrongJacob Armstrong
142
|
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 '18 at 3:44
Rahul Neekhra
6001627
asked Nov 20 '18 at 17:27
Jacob ArmstrongJacob Armstrong
142
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 '18 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 '18 at 19:26
Maybe you could look into doing something with an UNPIVOT.
– Tab Alleman
Nov 20 '18 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 '18 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 '18 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 '18 at 3:44
Rahul Neekhra
6001627
asked Nov 20 '18 at 17:27
Jacob ArmstrongJacob Armstrong
142
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 '18 at 3:44
Rahul Neekhra
6001627
asked Nov 20 '18 at 17:27
Jacob ArmstrongJacob Armstrong
142
edited Nov 21 '18 at 3:44
Rahul Neekhra
6001627
asked Nov 20 '18 at 17:27
Jacob ArmstrongJacob Armstrong
142
edited Nov 21 '18 at 3:44
Rahul Neekhra
6001627
edited Nov 21 '18 at 3:44
Rahul Neekhra
6001627
edited Nov 21 '18 at 3:44
Rahul Neekhra
6001627
6001627
asked Nov 20 '18 at 17:27
Jacob ArmstrongJacob Armstrong
142
asked Nov 20 '18 at 17:27
Jacob ArmstrongJacob Armstrong
142
asked Nov 20 '18 at 17:27
Jacob ArmstrongJacob Armstrong
142
142
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 '18 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 '18 at 19:26
Maybe you could look into doing something with an UNPIVOT.
– Tab Alleman
Nov 20 '18 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 '18 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 '18 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 '18 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 '18 at 19:26
Maybe you could look into doing something with an UNPIVOT.
– Tab Alleman
Nov 20 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 at 19:26
Maybe you could look into doing something with an UNPIVOT.
– Tab Alleman
Nov 20 '18 at 19:32
Maybe you could look into doing something with an UNPIVOT.
– Tab Alleman
Nov 20 '18 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 '18 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 '18 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 '18 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 '18 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 '18 at 19:38
Razvan SocolRazvan Socol
2,65211022
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 '18 at 19:33
Zohar PeledZohar Peled
54.9k73373
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 '18 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 '18 at 19:53
How about when x=3, y=2, Date1=1 and Date2=Date3=Date4=4?
– Razvan Socol
Nov 21 '18 at 5:49
@RazvanSocol correct. Also easily fixable by addingand x <= y, though...
– Zohar Peled
Nov 21 '18 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 '18 at 19:38
Razvan SocolRazvan Socol
2,65211022
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 '18 at 19:38
Razvan SocolRazvan Socol
2,65211022
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 '18 at 19:38
Razvan SocolRazvan Socol
2,65211022
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 '18 at 19:38
Razvan SocolRazvan Socol
2,65211022
answered Nov 20 '18 at 19:38
Razvan SocolRazvan Socol
2,65211022
answered Nov 20 '18 at 19:38
Razvan SocolRazvan Socol
2,65211022
answered Nov 20 '18 at 19:38
Razvan SocolRazvan Socol
2,65211022
2,65211022
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 '18 at 19:33
Zohar PeledZohar Peled
54.9k73373
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 '18 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 '18 at 19:53
How about when x=3, y=2, Date1=1 and Date2=Date3=Date4=4?
– Razvan Socol
Nov 21 '18 at 5:49
@RazvanSocol correct. Also easily fixable by addingand x <= y, though...
– Zohar Peled
Nov 21 '18 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 '18 at 19:33
Zohar PeledZohar Peled
54.9k73373
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 '18 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 '18 at 19:53
How about when x=3, y=2, Date1=1 and Date2=Date3=Date4=4?
– Razvan Socol
Nov 21 '18 at 5:49
@RazvanSocol correct. Also easily fixable by addingand x <= y, though...
– Zohar Peled
Nov 21 '18 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 '18 at 19:33
Zohar PeledZohar Peled
54.9k73373
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 '18 at 19:33
Zohar PeledZohar Peled
54.9k73373
edited Nov 21 '18 at 8:02
answered Nov 20 '18 at 19:33
Zohar PeledZohar Peled
54.9k73373
answered Nov 20 '18 at 19:33
Zohar PeledZohar Peled
54.9k73373
answered Nov 20 '18 at 19:33
Zohar PeledZohar Peled
54.9k73373
54.9k73373
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 '18 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 '18 at 19:53
How about when x=3, y=2, Date1=1 and Date2=Date3=Date4=4?
– Razvan Socol
Nov 21 '18 at 5:49
@RazvanSocol correct. Also easily fixable by addingand x <= y, though...
– Zohar Peled
Nov 21 '18 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 '18 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 '18 at 19:53
How about when x=3, y=2, Date1=1 and Date2=Date3=Date4=4?
– Razvan Socol
Nov 21 '18 at 5:49
@RazvanSocol correct. Also easily fixable by addingand x <= y, though...
– Zohar Peled
Nov 21 '18 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 '18 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 '18 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 '18 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 '18 at 19:53
How about when x=3, y=2, Date1=1 and Date2=Date3=Date4=4?
– Razvan Socol
Nov 21 '18 at 5:49
How about when x=3, y=2, Date1=1 and Date2=Date3=Date4=4?
– Razvan Socol
Nov 21 '18 at 5:49
@RazvanSocol correct. Also easily fixable by adding
and x <= y, though...– Zohar Peled
Nov 21 '18 at 8:01
@RazvanSocol correct. Also easily fixable by adding
and x <= y, though...– Zohar Peled
Nov 21 '18 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.
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 '18 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 '18 at 19:26
Maybe you could look into doing something with an UNPIVOT.
– Tab Alleman
Nov 20 '18 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 '18 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 '18 at 21:12