Help with 'TOP 2' query... 1

[Neil Toulouse]

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:

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...

 

[johnherman]

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

 

[gmmastros]

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

1	2017-03-23 00:00:00.000	0
1	2017-03-24 00:00:00.000	0
[red]1	2017-04-21 00:00:00.000	1
1	2017-04-22 00:00:00.000	1[/red]

2	2017-03-23 00:00:00.000	1
2	2017-03-24 00:00:00.000	1
[red]2	2017-04-21 00:00:00.000	1
2	2017-04-22 00:00:00.000	1[/red]

3	2017-03-23 00:00:00.000	0
3	2017-03-24 00:00:00.000	1
[red]3	2017-04-21 00:00:00.000	1
3	2017-04-22 00:00:00.000	0[/red]

4	2017-03-23 00:00:00.000	0
4	2017-03-24 00:00:00.000	0
[red]4	2017-04-21 00:00:00.000	1
4	2017-04-22 00:00:00.000	1[/red]

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

 

[gmmastros]

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

; 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

 

[Andrzejek]

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:

[pre]
[highlight #FCE94F]1 2017-03-23 00:00:00.000 0
1 2017-03-24 00:00:00.000 0
[/highlight]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

[highlight #FCE94F]4 2017-03-23 00:00:00.000 0
4 2017-03-24 00:00:00.000 0[/highlight]
4 2017-04-21 00:00:00.000 1
4 2017-04-22 00:00:00.000 1
[/pre]

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[Olaf Doschke]

I interpret it the same way, Andy.

Georges code results in this for me:

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:

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:

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.

 

[Neil Toulouse]

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...

 

[Neil Toulouse]

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...

 

[Olaf Doschke]

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

Bye, Olaf.