Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
BobRoberts
Posting Yak Master
109 Posts |
 Posted - 2009-02-18 : 17:15:01
|
| With the following data, MIN(ROW_DATE) yields "2008-11-17" Is there a way to get the function to yield the lowest date, but only evaluating those rows with a ROW_TYPE of "1"? In this case, the function should yield a ROW_DATE of "2008-12-30". I should mention that there will always be a row with a ROW_TYPE of "1" for any particular group of TRANS_ID's, but there may not necessarily be other ROW_TYPE's. Also, the lowest ROW_TYPE would be "1".-----------------------------------------ROW_TYPE------------TRANS_ID-----ROW_DATE7-------------------90000200-----2009-01-055-------------------90000200-----2008-11-171-------------------90000200-----2008-12-30 |
|
|
Skorch
Constraint Violating Yak Guru
300 Posts |
Posted - 2009-02-18 : 17:25:18
|
| Can't you just include the ROW_TYPE in the WHERE clause? Something like this...SELECT MIN(ROW_DATE)FROM YourTableWHERE ROW_TYPE = 1Some days you're the dog, and some days you're the fire hydrant. |
 |
|
|
BobRoberts
Posting Yak Master
109 Posts |
Posted - 2009-02-18 : 17:57:56
|
| No, I don't think I can change the WHERE clause, because then that excludes those other rows, which are actually used in netting a group total. I only want to change the DEFINITIVE date for the group whose TRANS_ID is the same. Right now it is the LOWEST, i.e, earliest, date - I want it to be the earliest date with a ROW_TYPE of "1". By the way, in the grouping of TRANS_ID's there must always be a ROW_TYPE of "1", but there can be many, or no, ROW_TYPE's of "5", "7".I guess I could post the actual SQL statement, but I wanted to reduce the problem to its essential form. I'm trying to make this modification without having any impact on the functionality of the rest of the SQL statement - I just want to change the date that it's coming up with to do a date comparison on a range of form-entered dates, but I think the principal would be the same if this were, say, a column representing quantities or dollar amounts. |
|
|
|
Skorch
Constraint Violating Yak Guru
300 Posts |
Posted - 2009-02-18 : 18:34:14
|
| I think it would be best if you posted your actual SQL query and expected results.Some days you're the dog, and some days you're the fire hydrant. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-19 : 03:10:34
|
quote:
Originally posted by BobRoberts
With the following data, MIN(ROW_DATE) yields "2008-11-17" Is there a way to get the function to yield the lowest date, but only evaluating those rows with a ROW_TYPE of "1"? In this case, the function should yield a ROW_DATE of "2008-12-30". I should mention that there will always be a row with a ROW_TYPE of "1" for any particular group of TRANS_ID's, but there may not necessarily be other ROW_TYPE's. Also, the lowest ROW_TYPE would be "1".-----------------------------------------ROW_TYPE------------TRANS_ID-----ROW_DATE7-------------------90000200-----2009-01-055-------------------90000200-----2008-11-171-------------------90000200-----2008-12-30
yup. just useMIN(CASE WHEN ROW_TYPE=1 THEN ROW_DATE ELSE NULL END) |
|
|
|
BobRoberts
Posting Yak Master
109 Posts |
Posted - 2009-02-19 : 12:33:23
|
| VISAKH16, your suggestion for the CASE statement works when running it withing SQL 2005 Express, but when I run it within Access 2003, it bombs out with a "incorrect syntax near" the "NULL" part. If I try to turn it into an IIF(), then it bombs out with a "incorrect syntax near" the "=" sign. Also, I should point out that the IIF() bombs out both within SQL 2005 Express, and within Access. Here is a test I constructed, that yields an error around the "=" sign:SELECT TESTTABLE.ROW_TYPE, IIF(TESTTABLE.ROW_TYPE = '5','FIVE','NOT FIVE') AS TESTRESULT FROM TESTTABLE |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-19 : 12:38:55
|
| this is sql server forum. so whatever solutions given will work only in sql server. if you want access related solution, post this in access forum. |
|
|
|
|
|
|
|
|
Question on MIN() Function