May 16, 2018 at 3:50 pm
Hi all,
Select datename(dw, Date) As 'Actual Day Entered' , Date, notes
from Logs
Where Notes like '%Attended - %'
And Notes Not like '%Make-up class%'
Gets me
Actual Day Entered Date notes
Thursday 2008-01-03 Attended - Thursday Adult 12:00
(BTW, if anyone knows how to make results sets appear tabular in page here, I'd be grateful)
What I want to do is check whether the 'Notes' column contains any weekday OTHER than that in the 'Actual Day Entered' column of the result set , which is not the case in the above example. Any weekday listed will be in the following format:
notes
Attended - Monday Adult
Attended - Tuesday Adult 12:00
Attended - Tuesday Inter/Adv
etc
The Date column is DateTime.
TIA
May 16, 2018 at 3:56 pm
JaybeeSQL - Wednesday, May 16, 2018 3:50 PMHi all,
Select datename(dw, Date) As 'Actual Day Entered' Date notes
Thursday 2008-01-03 Attended - Thursday Adult 12:00 ', Date, (notes) --, *
from Logs
Where Notes like '%Attended - %'
And Notes Not like '%Make-up class%'Gets me
Actual Day Entered Date notes
Thursday 2008-01-03 Attended - Thursday Adult 12:00What I want to do is check whether the 'Notes' column contains any weekday OTHER than that in the 'Actual Day Entered' column of the result set , which is not the case in the above example. Any weekday listed will be in the following format:
notes
Attended - Monday Adult
Attended - Tuesday Adult 12:00
Attended - Tuesday Inter/Adv
etcThe Date column is DateTime.
TIA
I'm lost, not sure what you are asking.
May 16, 2018 at 4:00 pm
I hit 'Post' far too early, hopefully makes sense now.
May 16, 2018 at 4:06 pm
JaybeeSQL - Wednesday, May 16, 2018 4:00 PMI hit 'Post' far too early, hopefully makes sense now.
Nope, still don't understand what you are looking for here.
May 16, 2018 at 5:37 pm
JaybeeSQL - Wednesday, May 16, 2018 4:00 PMI hit 'Post' far too early, hopefully makes sense now.
It really doesn't. Please post the DDL and inserts for some sample data. As a guess without any DDL or sample data, maybe you want to add to your where clause something like: WHERE notes not like '%' + datename(dw, Date) + '%'
No idea what is in the notes or exactly what you are looking for in regards to "any other day" - in addition, exclusively, do the notes have multiple days, is it per user...who knows.
Sue
May 17, 2018 at 7:28 am
Sue_H - Wednesday, May 16, 2018 5:37 PMJaybeeSQL - Wednesday, May 16, 2018 4:00 PMI hit 'Post' far too early, hopefully makes sense now.It really doesn't. Please post the DDL and inserts for some sample data. As a guess without any DDL or sample data, maybe you want to add to your where clause something like:
WHERE notes not like '%' + datename(dw, Date) + '%'No idea what is in the notes or exactly what you are looking for in regards to "any other day" - in addition, exclusively, do the notes have multiple days, is it per user...who knows.
Sue
Well Sue, looks like you found the crystal ball. I wonder where it will land next?
May 17, 2018 at 8:32 am
This might be an option if more than one day is mentioned in Notes.
CREATE TABLE Logs( Date DATETIME, Notes VARCHAR(100))
INSERT INTO dbo.Logs
(
Date,
Notes
)
VALUES
( '20180514', 'Attended - Monday Adult'),
( '20180515', 'Attended - Tuesday Adult 12:00'),
( '20180516', 'Attended - Tuesday Inter/Adv');
SELECT datename(dw, Date) As 'Actual Day Entered', Date, notes
--Thursday 2008-01-03 Attended - Thursday Adult 12:00 ', Date, (notes) --, *
from Logs l
Where Notes like '%Attended - %'
And Notes Not like '%Make-up class%'
AND NOT EXISTS( SELECT *
FROM (VALUES('%Monday%'),
('%Tuesday%'),
('%Wednesday%'),
('%Thursday%'),
('%Friday%'),
('%Saturday%'),
('%Sunday%'))x(DayName)
WHERE '%' + DATENAME(dw, l.Date) + '%' <> x.DayName
AND l.Notes LIKE x.DayName)
GO
DROP TABLE dbo.Logs
May 17, 2018 at 9:35 am
Sue_H - Wednesday, May 16, 2018 5:37 PMJaybeeSQL - Wednesday, May 16, 2018 4:00 PMI hit 'Post' far too early, hopefully makes sense now.It really doesn't. Please post the DDL and inserts for some sample data. As a guess without any DDL or sample data, maybe you want to add to your where clause something like:
WHERE notes not like '%' + datename(dw, Date) + '%'No idea what is in the notes or exactly what you are looking for in regards to "any other day" - in addition, exclusively, do the notes have multiple days, is it per user...who knows.
Sue
This does rather assume that the "notes" field is populated in free text by people who can spell out the full day properly, that the database/field isn't using a case-sensitive collation, or that an application is populating the field...
Otherwise, someone somewhere is bound to write "thrusday" and wonder why it's not matching "Thursday"...
Or something.
Basically, free-text fields are nasty.
Thomas Rushton
blog: https://thelonedba.wordpress.com
May 24, 2018 at 2:09 pm
Here's the code that got me to where I wanted to be, but first let me explain what happened:
The 'Logs' table is a pretty denormalized one, which serves multiple functions, one of which is class attendance. There are about 150k rows. The 'Notes' field is a manually entered one the user can populate via keyboard or drop-down. As you can imagine the latter is prone to erroneous data entry. Part of my mission is to cleanse the data (for more accurate reporting), I've identified 2.8% of the records within show one date, namely that on which the entry was made, but the 'Notes' column states the student attended on another, ie "Came last Monday". Having gone through the 2.8% I created an exclusions list to whittle away those that specified past attendance, finally yielding 200 or so where the notes did NOT specify when the student came, only that he was absent that day. I will develop this code further to update the 'Date' column of the 2.8% to match the day specified in 'Notes', minus of course the 200 rows.
By the way, apologies, I'm using the SQL Code format function to encapsulate my code but it's still not appearing tidy, and frankly I'm out of patience after wasting around for 20 minutes of trial and error...
May 24, 2018 at 2:11 pm
ThomasRushton - Thursday, May 17, 2018 9:35 AMSue_H - Wednesday, May 16, 2018 5:37 PMJaybeeSQL - Wednesday, May 16, 2018 4:00 PMI hit 'Post' far too early, hopefully makes sense now.It really doesn't. Please post the DDL and inserts for some sample data. As a guess without any DDL or sample data, maybe you want to add to your where clause something like:
WHERE notes not like '%' + datename(dw, Date) + '%'No idea what is in the notes or exactly what you are looking for in regards to "any other day" - in addition, exclusively, do the notes have multiple days, is it per user...who knows.
Sue
This does rather assume that the "notes" field is populated in free text by people who can spell out the full day properly, that the database/field isn't using a case-sensitive collation, or that an application is populating the field...
Otherwise, someone somewhere is bound to write "thrusday" and wonder why it's not matching "Thursday"...
Or something.
Basically, free-text fields are nasty.
This worked for me...--Check for mispelt day names:
Select Notes
From Logs
Where notes like '%day' And
(Notes not like '%Monday%' And Notes not like '%Tuesday%' And Notes not like '%Wednesday%' And Notes not like '%Thursday%' And Notes not like '%Friday%' And Notes not like '%Saturday%'
And Notes not like '%Sunday%' And Notes not like '%today%' And Notes not like '%yesterday%' And Notes not like '%everyday%'
And Notes not like '%holiday%')
May 24, 2018 at 2:19 pm
Sue_H - Wednesday, May 16, 2018 5:37 PMJaybeeSQL - Wednesday, May 16, 2018 4:00 PMI hit 'Post' far too early, hopefully makes sense now.It really doesn't. Please post the DDL and inserts for some sample data. As a guess without any DDL or sample data, maybe you want to add to your where clause something like:
WHERE notes not like '%' + datename(dw, Date) + '%'No idea what is in the notes or exactly what you are looking for in regards to "any other day" - in addition, exclusively, do the notes have multiple days, is it per user...who knows.
Sue
That was indeed the solution, I was unaware you could use
1) wildcards with a concatenation;
2) a derived column as a filter for the 'Where' clause.
I learned something, thank you Sue 🙂
May 24, 2018 at 2:45 pm
Is this better?
SELECT
DATENAME(dw, [Date]) AS 'Day This Data Was Entered'
, [Date]
, ([notes]) --, *
FROM
[Logs]
WHERE
[Notes] NOT LIKE '%' + DATENAME(dw, [Date]) + '%'
--And (Notes like '%Attended - %' Or Notes like '%Came to %' or Notes like '%Came on%' or Notes like '%Attedned%')
--And Notes Not like '%Make-up class%'
--Ensure Weekdays are included in the Notes
AND
(
[Notes] LIKE '%Monday%'
OR [Notes] LIKE '%Tuesday%'
OR [Notes] LIKE '%Wednesday%'
OR [Notes] LIKE '%Thursday%'
OR [Notes] LIKE '%Friday%'
OR [Notes] LIKE '%Saturday%'
OR [Notes] LIKE '%Sunday%'
)
--Ensure Most Common are excluded from the Notes
AND
(
[Notes] NOT LIKE '%Little Eagles%'
AND [Notes] NOT LIKE '%Beginners%'
AND [Notes] NOT LIKE '%Inter/Adv%'
AND [Notes] NOT LIKE '%Adult%'
AND [Notes] NOT LIKE '%Black Belt class%'
AND [Notes] NOT LIKE '%Int/Adv%'
AND [Notes] NOT LIKE '%Intermediate/Advanced 7:30pm%'
AND [Notes] NOT LIKE '%Saturday 10:30am class%'
AND [Notes] NOT LIKE 'Attedned%'
AND [Notes] NOT LIKE 'Attended%'
AND [Notes] NOT LIKE 'Attedended%'
AND [Notes] NOT LIKE '%Black Belt/MC Class%'
AND [Notes] NOT LIKE '%Master Kim Private lesson%'
AND [Notes] NOT LIKE '%Attended%'
AND [Notes] NOT LIKE '%Attended%'
AND [Notes] NOT LIKE 'Came To Class%'
AND [Notes] NOT LIKE 'Came Last%'
AND [Notes] NOT LIKE '%came to youth class instead of LE class.%'
AND [Notes] NOT LIKE '%Attended%'
AND [Notes] NOT LIKE '%Came on%'
AND [Notes] NOT LIKE '%Came to%'
AND [Notes] NOT LIKE '%Cameto%'
AND [Notes] NOT LIKE 'Cameto%'
AND [Notes] NOT LIKE 'Was %'
AND [Notes] NOT LIKE '%ttended%'
AND [Notes] NOT LIKE 'came %'
AND [Notes] NOT LIKE '%Attended%'
)
ORDER BY
[Notes];
May 25, 2018 at 3:32 am
Lynn Pettis - Thursday, May 24, 2018 2:45 PMIs this better?
SELECT
DATENAME(dw, [Date]) AS 'Day This Data Was Entered'
, [Date]
, ([notes]) --, *
FROM
[Logs]
WHERE
[Notes] NOT LIKE '%' + DATENAME(dw, [Date]) + '%'
--And (Notes like '%Attended - %' Or Notes like '%Came to %' or Notes like '%Came on%' or Notes like '%Attedned%')
--And Notes Not like '%Make-up class%'
--Ensure Weekdays are included in the Notes
AND
(
[Notes] LIKE '%Monday%'
OR [Notes] LIKE '%Tuesday%'
OR [Notes] LIKE '%Wednesday%'
OR [Notes] LIKE '%Thursday%'
OR [Notes] LIKE '%Friday%'
OR [Notes] LIKE '%Saturday%'
OR [Notes] LIKE '%Sunday%'
)
--Ensure Most Common are excluded from the Notes
AND
(
[Notes] NOT LIKE '%Little Eagles%'
AND [Notes] NOT LIKE '%Beginners%'
AND [Notes] NOT LIKE '%Inter/Adv%'
AND [Notes] NOT LIKE '%Adult%'
AND [Notes] NOT LIKE '%Black Belt class%'
AND [Notes] NOT LIKE '%Int/Adv%'
AND [Notes] NOT LIKE '%Intermediate/Advanced 7:30pm%'
AND [Notes] NOT LIKE '%Saturday 10:30am class%'
AND [Notes] NOT LIKE 'Attedned%'
AND [Notes] NOT LIKE 'Attended%'
AND [Notes] NOT LIKE 'Attedended%'
AND [Notes] NOT LIKE '%Black Belt/MC Class%'
AND [Notes] NOT LIKE '%Master Kim Private lesson%'
AND [Notes] NOT LIKE '%Attended%'
AND [Notes] NOT LIKE '%Attended%'
AND [Notes] NOT LIKE 'Came To Class%'
AND [Notes] NOT LIKE 'Came Last%'
AND [Notes] NOT LIKE '%came to youth class instead of LE class.%'
AND [Notes] NOT LIKE '%Attended%'
AND [Notes] NOT LIKE '%Came on%'
AND [Notes] NOT LIKE '%Came to%'
AND [Notes] NOT LIKE '%Cameto%'
AND [Notes] NOT LIKE 'Cameto%'
AND [Notes] NOT LIKE 'Was %'
AND [Notes] NOT LIKE '%ttended%'
AND [Notes] NOT LIKE 'came %'
AND [Notes] NOT LIKE '%Attended%'
)
ORDER BY
[Notes];
Much, but while I assume a Mgt.Studio plugin was used to format that code, I don't see how that would prevent this site's SQL CODE function mangling the appearance once again.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply