I have a column [BatchDate] that contains dates in text format:
032221
032121
032021
031921
etc....
For days Tuesday, Wednesday, Thursday, Friday, I'm trying to get results on just those days.
However, on Monday, I want results from Friday, Saturday, Sunday, Monday, to catch weekend orders.
I'm not getting the syntax correct. As well, there must be shorter ways to write a CASE statement in the WHERE clause.
Can someone give me a shove in the right direction?
SELECT DISTINCT BatchID, EDIProviderSort,
CONVERT(varchar, SUBSTRING(BatchDate, 1, 2) + '/' + SUBSTRING(BatchDate, 3, 2) + '/20' + SUBSTRING(BatchDate, 5, 2), 101) AS BatchDatte,
DATENAME(dw,CONVERT(varchar, SUBSTRING(BatchDate, 1, 2) + '/' + SUBSTRING(BatchDate, 3, 2) + '/20' + SUBSTRING(BatchDate, 5, 2), 101)) AS DName
FROM UPSBatchInvNo
WHERE (WhsID = 'FL') and
CASE DATENAME(dw,CONVERT(varchar, SUBSTRING(BatchDate, 1, 2) + '/' + SUBSTRING(BatchDate, 3, 2) + '/20' + SUBSTRING(BatchDate, 5, 2), 101)) When 'Monday' Then
DATENAME(dw,CONVERT(varchar, SUBSTRING(BatchDate, 1, 2) + '/' + SUBSTRING(BatchDate, 3, 2) + '/20' + SUBSTRING(BatchDate, 5, 2), 101)) = 'Monday' AND
DATENAME(dw,CONVERT(varchar, SUBSTRING(BatchDate, 1, 2) + '/' + SUBSTRING(BatchDate, 3, 2) + '/20' + SUBSTRING(BatchDate, 5, 2), 101)) = 'Sunday' AND
DATENAME(dw,CONVERT(varchar, SUBSTRING(BatchDate, 1, 2) + '/' + SUBSTRING(BatchDate, 3, 2) + '/20' + SUBSTRING(BatchDate, 5, 2), 101)) = 'Saturday' AND
DATENAME(dw,CONVERT(varchar, SUBSTRING(BatchDate, 1, 2) + '/' + SUBSTRING(BatchDate, 3, 2) + '/20' + SUBSTRING(BatchDate, 5, 2), 101)) = 'Friday'END
ORDER BY BatchDatte DESC, EDIProviderSort ASC, BatchID ASC
March 22, 2021 at 6:01 pm
SELECT DISTINCT
BatchID, EDIProviderSort,
CONVERT(varchar(10), BatchDateConv, 101) AS BatchDatte,
DName
FROM UPSBatchInvNo
CROSS APPLY (
SELECT CAST('20' + RIGHT(BatchDate, 2) + LEFT(BatchDate, 4) AS date) AS BatchDateConv
) AS ca1
CROSS APPLY(
SELECT DATENAME(WEEKDAY, BatchDateConv) AS DName
) AS ca2
WHERE (WhsID = 'FL') AND
DName NOT IN ('Saturday', 'Sunday') AND
BatchDateConv BETWEEN DATEADD(DAY, CASE WHEN DName = 'Monday' THEN -3 ELSE 0 END, BatchDateConv) AND BatchDateConv
ORDER BY BatchDateConv DESC, EDIProviderSort ASC, BatchID ASC
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".
Re: CROSS APPLY ... a totally new one to me. Thanks!
I had to change ORDER BY BatchDateConv to: ORDER BY BatchDatte
Error: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
But it works. Very much appreciated!
March 22, 2021 at 7:48 pm
Ooh, right. I tired to change the sort because you formatted BatchDatte to mm/dd/yyyy which will not sort correctly.
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".
March 29, 2021 at 3:49 pm
>> I have a column [batch_date] that contains dates in text format: <<
The only format we allowed in ANSI ISO standard SQL is based on the ISO 8601 standard (yyyy-mm-dd). Since modern programming is based on a tiered architecture, your local strings should have been reformatted in an input layer before the database even sees them. A lot of old COBOL programmers crap up things like this because their language had no such concept.
Also, there is no CASE statement in SQL! In this language, CASE is an expression; expressions return a single value of a known data type, I would assume you want date coming back.
>> For days Tuesday, Wednesday, Thursday, Friday, I'm trying to get results on just those days. <<
If you look, you'll find a day of the week function. Another trick is to put an ordinal date in a calendar table, and be sure that the weekend has the same ordinal number as Monday.
>> However, on Monday, I want results from Friday, Saturday, Sunday, Monday, to catch weekend orders. <<
What does "results" mean? Is it the total value the orders placed on that particular day? The count of orders? Some other inventory function?
I also strongly recommend not using the COBOL style CONVERT () function. It's a left over from the old Sybase days and was put in there for you COBOL programmers. It lets them fake. The use of a PICTURE clause to do display formatting. I hope you understand that in SQL display formatting is done in a presentation layer, not in a query.
I'd also like to point out since we have no DDL, that the use of a SELECT DISTINCT is rare and properly written SQL; we generally have a key and don't have to worry about removing redundancy at runtime. What you're going to get from people who have to work with what you posted is a lot of wild guesses about what you might've meant and the horror of "COBOL written in SQL" but new programmers often fall back on
Would you like to follow netiquette, and post some DDL? Would you like to explain what you want?
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply