April 7, 2022 at 4:42 pm
The issue is that I need to select the MAX date when:
If the Max Date from my_column is equal to today's date, I need to select the Max Date from yesterday. If the Max Date from my_column is not equal to today's date, I can select the Max Date from today.
I am trying to do this using "case", but it is not working how I thought it would, sample code here is more like a sudo code to show the issue:
select account, name, street, due_date from my_table
where CAST(CONVERT(VARCHAR,due_date,102) AS DATETIME) IN (
-- if the date in the due_date column is not equal to today's date, I need to select today's -1 or yesterday's
case when MAX( CAST(CONVERT(VARCHAR,due_date,102) AS DATETIME) ) = CONVERT(VARCHAR,GETDATE() ,23)
-- otherwise it can just get the max today's date from the due_date column
then ( SELECT MAX( CAST(CONVERT(VARCHAR,due_date,102) AS DATETIME) -1 ) FROM my_table
else MAX( CAST(CONVERT(VARCHAR,due_date,102) AS DATETIME) ) = CONVERT(VARCHAR,GETDATE() ,23)
)
)
and account = '012345'
April 7, 2022 at 4:45 pm
Your description is hard to understand.
Please provide DDL, sample data as INSERT scripts and desired results based on that sample data, and it will be easier for someone to provide a working solution.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 7, 2022 at 5:16 pm
I'll start off by saying that putting columns in functions in the WHERE clause is almost always going to cause full table or index scans and that's going to kill performance.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 7, 2022 at 5:32 pm
Maybe using case isn't the right approach for this.
April 7, 2022 at 6:30 pm
This is what worked for me, but any rooms for improvement or suggestions is gratefully appreciated:
SELECT account, name, street, due_date
FROM my_table
WHERE
CAST(CONVERT(VARCHAR,due_date,102) AS DATETIME) = (
case when CONVERT(VARCHAR,due_date,102) = CONVERT(VARCHAR,GETDATE() ,102)
then (SELECT MAX( CAST(CONVERT(VARCHAR,due_date,102) AS DATETIME) -1 ) FROM my_table )
else (SELECT MAX( CAST(CONVERT(VARCHAR,due_date,102) AS DATETIME) ) FROM my_table )
end
)
and account = '012345'
April 7, 2022 at 7:39 pm
Untested, but I think this is tidier. Converting date to VARCHAR is going to hit performance. Also, I suggest that you never use VARCHAR without specifying its length. Under some circumstances, the default length of VARCHAR is 1.
DECLARE @MaxDate DATE =
(
SELECT MAX(CAST(CONVERT(VARCHAR, due_date, 102) AS DATETIME) - 1)
FROM my_table
);
DECLARE @MaxDateLess1 DATE = DATEADD(DAY, -1, @MaxDate);
DECLARE @Today DATE = GETDATE();
SELECT
account
, name
, street
, due_date
FROM my_table
CROSS APPLY
(SELECT DueDateX = CAST(due_Date AS DATE)) c1
CROSS APPLY
(
SELECT RelDate = CASE
WHEN c1.DueDateX = @Today THEN
@MaxDateLess1
ELSE
@MaxDate
END
) c2
WHERE c1.DueDateX = c2.RelDate
AND account = '012345';
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 7, 2022 at 10:13 pm
CASE WHEN DueDate = CAST (GETDATE() AS DATE) THEN DATEADD(dd,-1, DueDate ) else DueDate end
so here we are saying if Due date = today(Max date) then you subtract a day otherwise just take the due date
April 8, 2022 at 5:44 pm
Note the "<" in the comparison; that is required. This handles due_date correctly whether it is a datetime data type or a date.
;WITH cte_get_max_due_date AS (
SELECT DATEADD(DAY, CASE WHEN CAST(MAX(due_date) AS date) = CAST(GETDATE() AS date)
THEN -1 ELSE 0 END, CAST(GETDATE() AS date)) AS max_due_date
FROM dbo.my_table
)
SELECT mt.account, mt.name, mt.street, mt.due_date
FROM dbo.my_table mt
CROSS JOIN cte_get_max_due_date cg
WHERE mt.due_date < DATEADD(DAY, 1, cg.max_due_date)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 9, 2022 at 1:39 am
This is what worked for me, but any rooms for improvement or suggestions is gratefully appreciated:
SELECT account, name, street, due_date FROM my_table WHERE CAST(CONVERT(VARCHAR,due_date,102) AS DATETIME) = (
case when CONVERT(VARCHAR,due_date,102) = CONVERT(VARCHAR,GETDATE() ,102) then (SELECT MAX( CAST(CONVERT(VARCHAR,due_date,102) AS DATETIME) -1 ) FROM my_table ) else (SELECT MAX( CAST(CONVERT(VARCHAR,due_date,102) AS DATETIME) ) FROM my_table ) end
) and account = '012345'
What is the datetype for the due_date column?
Also, please post all the due dates in table for a real account number and the account number that you used. Some of the solutions look like they may work but only if there's one row per account. If there IS only one row per account in whatever "my_table" is in real life, please state that instead of posting the data I requested. And, no, I don't believe the latter is true but I have to check.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 12, 2022 at 9:14 am
The issue is that I need to select the MAX date when:
If the Max Date from my_column is equal to today's date, I need to select the Max Date from yesterday. If the Max Date from my_column is not equal to today's date, I can select the Max Date from today.
I could be completely wrong, but based on the quoted sentence it sounds to me like you only want days before todays date included in the selected dataset.
But this interpretation doesn't jive with the query you published as working, so probably not.
Anyway, here's my "non-compliant" query with some imagined test data (you never supplied those!!):
/* Setting up some imaginary test data */
DECLARE @my_table TABLE (
account varchar(10),
name varchar(40),
street varchar(40),
due_date datetime
)
INSERT INTO @my_table (account, name, street, due_date)
VALUES
-- Previous dates..
('012345','Roger Daltry','Kensington Street 45','20220407'),
('012345','Roger Daltry','Kensington Street 45','20220408'),
('012345','Roger Daltry','Kensington Street 45','20220408'),
('012345','Roger Daltry','Kensington Street 45','20220409'),
('012345','Roger Daltry','Kensington Street 45','20220409'),
('012345','Roger Daltry','Kensington Street 45','20220410'),
('012345','Roger Daltry','Kensington Street 45','20220411'),
('012345','Roger Daltry','Kensington Street 45','20220411'),
-- Today's date..
('012345','Roger Daltry','Kensington Street 45','20220412'),
('012345','Roger Daltry','Kensington Street 45','20220412'),
-- Future dates..
('012345','Roger Daltry','Kensington Street 45','20220413'),
('012345','Roger Daltry','Kensington Street 45','20220415'),
-- Different account...
('123456','Roberta Daltry','Kensington Street 45','20220411'),
('123456','Roberta Daltry','Kensington Street 45','20220412'),
('123456','Roberta Daltry','Kensington Street 45','20220414'),
('123456','Roberta Daltry','Kensington Street 45','20220416'),
('123456','Roberta Daltry','Kensington Street 45','20220417')
/* My takeaway from your description */
SELECT account, name, street, due_date
FROM @my_table
WHERE CAST(due_date AS DATE) = (
SELECT CAST(MAX(due_date) AS DATE)
FROM @my_table mt1
WHERE
CAST(due_date AS DATE) < CAST(GETDATE() AS DATE)
)
AND account = '012345';
/* Alternative way to express my takeaway from your description */
SELECT account, name, street, due_date
FROM @my_table mt
CROSS APPLY (
SELECT MAX(mt1.due_date) AS max_due_date
FROM @my_table mt1
WHERE
mt1.due_date < CAST(GETDATE() AS DATE)
) mdd
WHERE
mt.due_date = mdd.max_due_date
AND mt.account = '012345'
/* Your "working" alternative, that admittedly runs counter to my interpretation of your problem description */
SELECT account, name, street, due_date
FROM @my_table
WHERE
CAST(CONVERT(VARCHAR,due_date,102) AS DATETIME) = (
case
when CONVERT(VARCHAR,due_date,102) = CONVERT(VARCHAR,GETDATE() ,102)
then (SELECT MAX( CAST(CONVERT(VARCHAR,due_date,102) AS DATETIME) -1 ) FROM @my_table )
else (SELECT MAX( CAST(CONVERT(VARCHAR,due_date,102) AS DATETIME) ) FROM @my_table )
end
)
and account = '012345';
The result from these three queries (when run today, i.e. April 12, 2022):
account name street due_date
---------- ---------------------------------------- ---------------------------------------- -----------------------
012345 Roger Daltry Kensington Street 45 2022-04-11 00:00:00.000
012345 Roger Daltry Kensington Street 45 2022-04-11 00:00:00.000
(2 rows affected)
account name street due_date
---------- ---------------------------------------- ---------------------------------------- -----------------------
012345 Roger Daltry Kensington Street 45 2022-04-11 00:00:00.000
012345 Roger Daltry Kensington Street 45 2022-04-11 00:00:00.000
(2 rows affected)
account name street due_date
---------- ---------------------------------------- ---------------------------------------- -----------------------
(0 rows affected)
Completion time: 2022-04-12T11:10:26.5502383+02:00
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply