INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Help with 'TOP 2' query...

Help with 'TOP 2' query...

(OP)
Hi guys

I'm thinking this should be a straightforward query but I'm at a loss at the mo!

Basically, I have an 'Attendance' table, something like this:

CODE

ENRID	DATE		ATTENDED
1	24/03/2017	0
1	23/03/2017	0
1	22/04/2017	1
1	21/04/2017	1
2	24/03/2017	1
2	23/03/2017	1
2	22/04/2017	1
2	21/04/2017	1
3	24/03/2017	1
3	23/03/2017	0
3	22/04/2017	0
3	21/04/2017	1
4	24/03/2017	0
4	23/03/2017	0
4	22/04/2017	1
4	21/04/2017	1 

What I am trying to achieve is a distinct list of 'ENRID' where their last two sessions 'DATE' (reverse chronological order) are 'not attended' (Attended=0)

...so from the example data, the query should return ENRID 1 & 4, and not ENRID 2 & 3.

Can you help?

TIA

Neil

I like work. It fascinates me. I can sit and look at it for hours...

RE: Help with 'TOP 2' query...

untested - something to get you started - and there are probably some very eloquent solutions as well

select enrid from table c
where sum(a.attended, b.attended) = 0 and
c.date in (select max(a.date) from table a, select max(b.date) from table b where b.date <> a.date)

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity


RE: Help with 'TOP 2' query...

I think your expected output is wrong. Your sample data, when sorted by ENRID and date, is this...

CODE

1	2017-03-23 00:00:00.000	0
1	2017-03-24 00:00:00.000	0
1	2017-04-21 00:00:00.000	1
1	2017-04-22 00:00:00.000	1

2	2017-03-23 00:00:00.000	1
2	2017-03-24 00:00:00.000	1
2	2017-04-21 00:00:00.000	1
2	2017-04-22 00:00:00.000	1

3	2017-03-23 00:00:00.000	0
3	2017-03-24 00:00:00.000	1
3	2017-04-21 00:00:00.000	1
3	2017-04-22 00:00:00.000	0

4	2017-03-23 00:00:00.000	0
4	2017-03-24 00:00:00.000	0
4	2017-04-21 00:00:00.000	1
4	2017-04-22 00:00:00.000	1 

If I understand correctly, you only want to consider the rows shown above in red. Is this correct?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

RE: Help with 'TOP 2' query...

Based on your original question, I think the query you want is:

CODE

; With Data As
(
  Select *, 
         Row_Number() Over (Partition By ENRID Order By Date DESC) As RowId
  From   YourTableNameHere
)
Select  ENRID,
        Count(Case When Attended = 0 Then 1 Else NULL End) As CountOfNotAttended
From    Data
Where   RowId < 3
Group by ENRID
Having Count(Case When Attended = 0 Then 1 Else NULL End) = 2 

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

RE: Help with 'TOP 2' query...

Quote:

distinct list of 'ENRID' where their last two sessions 'DATE' (reverse chronological order) are 'not attended' (Attended=0)

...so from the example data, the query should return ENRID 1 & 4

So I would guess:

1	2017-03-23 00:00:00.000	0
1	2017-03-24 00:00:00.000	0
1	2017-04-21 00:00:00.000	1
1	2017-04-22 00:00:00.000	1

2	2017-03-23 00:00:00.000	1
2	2017-03-24 00:00:00.000	1
2	2017-04-21 00:00:00.000	1
2	2017-04-22 00:00:00.000	1

3	2017-03-23 00:00:00.000	0
3	2017-03-24 00:00:00.000	1
3	2017-04-21 00:00:00.000	1
3	2017-04-22 00:00:00.000	0

4	2017-03-23 00:00:00.000	0
4	2017-03-24 00:00:00.000	0
4	2017-04-21 00:00:00.000	1
4	2017-04-22 00:00:00.000	1 
 

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Help with 'TOP 2' query...

I interpret it the same way, Andy.

Georges code results in this for me:

CODE

Declare @yourtablenamehere as Table (enrID int, date date, attended bit)

insert into @yourtablenamehere values 
(1, '20170324', 0),(1, '20170323', 0),(1, '20170322', 1),(1, '20170321', 1),
(2, '20170324', 1),(1, '20170323', 1),(1, '20170322', 1),(1, '20170321', 1),
(3, '20170324', 1),(1, '20170323', 0),(1, '20170322', 0),(1, '20170321', 1),
(4, '20170324', 0),(1, '20170323', 0),(1, '20170322', 1),(1, '20170321', 1);

With Data As
(
  Select *, 
         Row_Number() Over (Partition By ENRID Order By Date DESC) As RowId
  From   @YourTableNameHere
)
Select  ENRID,
        Count(Case When Attended = 0 Then 1 Else NULL End) As CountOfNotAttended
From    Data
Where   RowId < 3
Group by ENRID
Having Count(Case When Attended = 0 Then 1 Else NULL End) = 2 



So it solves the problem correctly. Also from looking at the code, it looks at rows 1 and 2 of all partitions only:

CODE

With Data As
(
  Select *, 
         Row_Number() Over (Partition By ENRID Order By Date DESC) As RowId
  From   @YourTableNameHere
)
Select  *
From    Data
Where   RowId < 3 

That's exactly the data to look at.

My go on it using windowing functions again:

CODE

Declare @yourtablenamehere as Table (enrID int, date date, attended bit)

insert into @yourtablenamehere values 
(1, '20170324', 0),(1, '20170323', 0),(1, '20170322', 1),(1, '20170321', 1),
(2, '20170324', 1),(2, '20170323', 1),(2, '20170322', 1),(2, '20170321', 1),
(3, '20170324', 1),(3, '20170323', 0),(3, '20170322', 0),(3, '20170321', 1),
(4, '20170324', 0),(4, '20170323', 0),(4, '20170322', 1),(4, '20170321', 1);

With Data As
(
  Select enrID, date,
         attended as lastattended,
         Lead(attended) Over (Partition By ENRID Order By Date DESC) as previousattended,
		 row_number() Over (Partition By ENRID Order By Date DESC) as RowId
  From   @YourTableNameHere
)

Select * From data where RowID=1 and  lastattended=0 and previousattended=0 


Georges solution has one big advantage, you can decide to look for attendents missing 2,3,4,... appointments simply by changing the rowid<=N and having count(...) = N.

What I wonder is, FatSlug, why you have all the dates multiple times. I would imagine you have an appointments table where the dates come from and could limit all joins to only the last two dates. That would filter data to the top two rows without row_number() and would then only need a simple count, so the finally easiest solution would start at only querying what you need to look at in the first place, by first only querying the last two (or however many) appointments and then go from there,

Bye, Olaf.




RE: Help with 'TOP 2' query...

(OP)
Hi guys!

Many thanks for the responses, most interesting :) Hopefully I will get chance to properly digest it all today.

(And just to clarify, regarding the data, it is as Andrzejek and Olaf interpret it, as in, I want to return the ENRID if the last two sessions have NOT been attended.)

Thanks again and i will post back later.

Neil



I like work. It fascinates me. I can sit and look at it for hours...

RE: Help with 'TOP 2' query...

(OP)
George's solution worked a treat!

Olaf - yes indeed the data is not stored as per my example (it comes via three tables) but i am trying to intercept a process with minimal effort, and at the point I want to interfere, the query is returning that result set.

Thanks for all the advise!

I like work. It fascinates me. I can sit and look at it for hours...

RE: Help with 'TOP 2' query...

I'd intercept a step before that, again because you alredy have too much data for determining the past two sessions attendence.

Bye, Olaf.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close