![]() ![]() This can work with minimums as well, just select a very low date like if you want NULL to be the minimum. ![]() Not the most eligant way to do things, but it defeinitely gets the job done. Wrap that in a CASE statement to replace the date back to NULL and group our data by StoreID.ĬASE WHEN MAX(COALESCE(EndDate, ’′)) = ’′ THEN NULL ELSE MAX(EndDate) END AS Date Next we take the MAX of the dates, which if NULL will evaluate as and be greater than any other date in our table. We will use COALESCE to replace any NULL EndDate with a date that is in the future that will not be coming up in our data anywhere, Decemseems like a reasonable date for this. Combine that with a case statement and we are good to go. Luckily we can reqrite the query to trick MAX into picking the NULL value. However, if there is a NULL EndDate then the store is still being listed as active and we need to bring back NULL instead of any other dates listed. The business rules in this case say that we want to pull back the maximum end date for each store. If we are to SELECT MAX(EndDate) From WorkSchedule we would get as the result, even when grouping by StoreID. ![]() For instance, take the following data set as an example. If we have a column containing only dates for instance and there is a NULL date, MAX and MIN will both ignore that value. By default the functions MAX and MIN do not count NULL in their evaluation of your data.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |