March 27, 2009 at 2:12 pm
Hello
I have a visit table where every row has a client id, date and missed column. so if missed = 1, then the client id not show up on that date.
I need to do a query to find out people who did not show up in last 90 days. Please help.
RH
March 27, 2009 at 2:30 pm
Hi
I'm not sure if I understood you correctly, but this?
SELECT *
FROM AnyTableWhoseNameYouDidNotSay
WHERE missed = 1
If not please provide your table definition and some sample data.
Greets
Flo
March 27, 2009 at 2:32 pm
Add something about the date being greater than getdate()-90 to Flo's query, and you have what you seem to need.
Are you familiar with basic query writing? I'm asking because your question is about as basic as SQL queries get. If writing queries is brand new to you, I highly recommend doing a few of the beginning tutorials on MSDN. They'll get you started pretty well.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 27, 2009 at 2:36 pm
Oops... sorry!
@Flo
Read slow!
Thanks GSquared!
March 27, 2009 at 8:51 pm
Thanks to both of you.
To clarify - attached is a sample data file with three columns (student, date, missed). I need to find people who did not show up in any class in last two days (3/26 - 3/27). Note - client 2 and 4 missed yesterday's class but client 2 showed up today (3/27). So my query should return only client 2.
In other works, list of clients who did not show in last X days?
March 28, 2009 at 5:07 am
Try this:
DECLARE @t TABLE (student INT, dt DATETIME, missed BIT)
INSERT INTO @t
-- today
SELECT 1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 0, 0), 0
UNION SELECT 2, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 0, 0), 1
UNION SELECT 3, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 0, 0), 0
-- yesterday
UNION SELECT 1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, 0), 0
UNION SELECT 2, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, 0), 1
UNION SELECT 3, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, 0), 1
-- day before yesterday
UNION SELECT 1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 2, 0), 0
UNION SELECT 2, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 2, 0), 0
UNION SELECT 3, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 2, 0), 0
SELECT student, MAX(dt)
FROM @t
WHERE missed = 0
GROUP BY student
HAVING MAX(dt) < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, 0)
As GScuared asked, do you know the basics of t-sql? You really should read some tutorials for start up. You don't have to read thousands of pages of books but you should have some fundamental experiences. We will always help you since we see that you also try to figure out but it could be difficult for you to understand the answers without some basics.
Greets
Flo
March 28, 2009 at 8:17 am
Thanks
I got the picture
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply