Awal Perpustakaan Pelajari Kode Download Dukungan Komunitas Forums
Microsoft Developer Network >
> 
> 
Consecutive Entry Counts

已答复 Consecutive Entry Counts

  • 02 Mei 2012 12:49
     
     
    Masuk untuk Memilih
    0

    Hi all

    A bit of a conundrum for you.

    I have a query which displays a register mark for each learner, grouped by week number, day number and time of register.

    What I would like to do is a query whereby if a mark = "A", 4 times consecutively through these data rows, then I would like that query to flag it up.  So for example:

    Learner A  Reg1  Day1  Week1  Time1 MarkA

    ""               ""      Day1     ""       Time2 MarkA

    ""               ""      Day1     ""       Time3 MarkA

    ""               ""      Day1     ""       Time4 MarkA

    ""               ""      Day2  Week2  Time1  MarkA

    etc etc etc

    ...would flag in a query, because Mark A has been entered against this learner 4 times in a row.

    Any ideas on how I could count the number of Mark As consecutively, please?

    Thanks in advance.

     

Semua Balasan

  • 02 Mei 2012 12:58
    Penjawab Pertanyaan
     
     
    Masuk untuk Memilih
    0

    WITH cte  ---sorry cannot test it

    AS

    (

    SELECT *, 0 AS flag,COUNT(*) OVER (PARTITION BY day,mark) cnt FROM  tbl

    ) UPDATE cte SET flag= CASE WHEN cnt>=4 THEN 1 END

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

     
  • 02 Mei 2012 13:12
     
     
    Masuk untuk Memilih
    0

    Hi Uri - thanks for your reply.

    Do you think this will work without counting over the day?  I am after 4 consecutives regardless of which day it is.

    Thanks.

     
  • 02 Mei 2012 13:28
     
     Saran Jawaban Memiliki Kode
    Masuk untuk Memilih
    0

    Here's a way... (something this complex can be done many ways... this is probably a good blend of readability and "gets it done").   

    Declare @Learner Table (LID int identity, Name Varchar(99), Mark Char(1))
Insert @Learner
 Select 'Al', 'A' UNION ALL
 Select 'Al', 'A' UNION ALL 
 Select 'Al', 'A' UNION ALL 
 Select 'Bob', 'A' UNION ALL 
 Select 'Bob', 'A' UNION ALL 
 Select 'Bob', 'A' UNION ALL 
 Select 'Bob', 'B' UNION ALL 
 Select 'Cy', 'A' UNION ALL 
 Select 'Cy', 'A' UNION ALL 
 Select 'Cy', 'A' UNION ALL 
 Select 'Cy', 'A' UNION ALL 
 Select 'Dan', 'A' UNION ALL 
 Select 'Dan', 'A' UNION ALL 
 Select 'Dan', 'A' UNION ALL 
 Select 'Dan', 'A' UNION ALL 
 Select 'Dan', 'A' UNION ALL 
 Select 'Ed', 'A'   UNION ALL
 Select 'Ed', 'B'   UNION ALL
 Select 'Ed', 'A'   UNION ALL
 Select 'Ed', 'A'   UNION ALL
 Select 'Ed', 'A'   UNION ALL
 Select 'Ed', 'A'  

;With NumberedList as (Select row_number() over(order by LID) as RowNum, * from @Learner)
Select *
       , Case When Mark = 'A'
               and (Select Count(*)
                      from NUMBEREDLIST NL2 
                     where NL2.Name = NL.Name 
                       and NL2.Mark = 'A' 
                       and NL2.RowNum between NL.RowNum and NL.RowNum + 3) >= 4 
              Then 'T' 
              Else '' 
          End as Flagged
   from NUMBEREDLIST NL

    If you happen to already have an Identifier that is sequential, then you could skip the NUMBEREDLIST step (where we create a sequential numbering scheme for the rows), but this is probably a better way to do it.  How big is the table? 


    EDIT: Oh, what version of SQL?  There are some new features in SQL 2012 that represent totally new ways of approaching this... (Lag() and Lead(), but chances are this isn't 2012.
    • Diedit oleh johnqflorida 02 Mei 2012 13:33 Addes SQL 2012 question
    • Diedit oleh johnqflorida 02 Mei 2012 13:36 Added SQL2012, fixed typo
    • Disarankan sebagai Jawaban oleh SQL Novice 01 02 Mei 2012 14:20
    •  
     
  • 02 Mei 2012 14:58
     
      Memiliki Kode
    Masuk untuk Memilih
    0

    Hi John!

    Thanks for your solution.

    You've provided me with a good start, but it isn't working properly.

    If you take a look below, row numbers 1 through to 10 have an A - so ideally, all these should be flagged with 1.

    You can also see that row 18 randomly has been flagged as a 1 too, even though there are no absences near that week.....

    1	340	ELT09115116	12	4	09:00 - 10:30	A	1
2	341	ELT09115116	12	4	11:00 - 12:30	A	1
3	342	ELT09115116	12	4	13:15 - 14:45	A	1
4	343	ELT09115116	12	4	15:00 - 16:30	A	1
5	344	ELT09115116	14	1	09:00 - 10:30	A	1
6	345	ELT09115116	14	1	11:00 - 12:30	A	1
7	346	ELT09115116	14	1	13:15 - 14:45	A	1
8	347	ELT09115116	14	2	09:00 - 10:30	A	0
9	348	ELT09115116	14	2	15:00 - 16:30	A	0
10	349	ELT09115116	14	2	16:30 - 18:00	A	0
11	350	ELT09115116	14	3	09:00 - 10:30	T	0
12	351	ELT09115116	14	3	11:00 - 12:30	T	0
13	352	ELT09115116	14	4	09:00 - 10:30	A	0
14	353	ELT09115116	14	4	11:00 - 12:30	A	0
15	354	ELT09115116	14	4	13:15 - 14:45	A	0
16	355	ELT09115116	14	4	15:00 - 16:30	P	0
17	356	ELT09115116	14	5	09:00 - 10:30	T	0
18	357	ELT09115116	15	1	09:00 - 10:30	A	1
19	358	ELT09115116	15	1	11:00 - 12:30	A	0
20	359	ELT09115116	15	1	13:15 - 14:45	A	0


    edit - The database is a 2005 version.
    • Diedit oleh divvyboy 02 Mei 2012 14:58
    •  
     
  • 02 Mei 2012 15:20
    Moderator
     
      Memiliki Kode
    Masuk untuk Memilih
    2

    Try:

    Declare @Learner Table (LID int identity, Name Varchar(99), dt datetime default convert(varchar(10),CURRENT_TIMESTAMP,112), 
Mark Char(1))
Insert @Learner (Name, Mark)
 Select 'Al', 'A' UNION ALL
 Select 'Al', 'A' UNION ALL 
 Select 'Al', 'A' UNION ALL 
 
 Select 'Bob', 'A' UNION ALL 
 Select 'Bob', 'A' UNION ALL 
 Select 'Bob', 'A' UNION ALL 
 Select 'Bob', 'B' UNION ALL 
 Select 'Cy', 'A' UNION ALL 
 Select 'Cy', 'A' UNION ALL 
 Select 'Cy', 'C' UNION ALL 
 Select 'Cy', 'A' UNION ALL 
 Select 'Dan', 'A' UNION ALL 
 Select 'Dan', 'A' UNION ALL 
 Select 'Dan', 'C' UNION ALL 
 Select 'Dan', 'A' UNION ALL 
 Select 'Dan', 'A' UNION ALL 
 Select 'Ed', 'A'   UNION ALL
 Select 'Ed', 'B'   UNION ALL
 Select 'Ed', 'A'   UNION ALL
 Select 'Ed', 'A'   UNION ALL
 Select 'Ed', 'A'   UNION ALL
 Select 'Ed', 'A'  
 
 ;with cte as (select *, ROW_NUMBER() over (PARTITION by [Name],[Dt] order by lID) - 
 ROW_NUMBER() over (PARTITION by Mark,[Name],[Dt] order by lID) as Grp from @Learner)
 
  select Name, dt, [Mark], COUNT(*) as cntConsecutive
 from cte 
 where [Mark] = 'A'
 group by Name, dt, Grp, Mark
 having COUNT(*) >=4

    The idea of this solution is the general idea of solving "gaps and islands" type of the problem.

    See this blog post

    Refactoring Ranges

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

     
  • 02 Mei 2012 15:47
     
      Memiliki Kode
    Masuk untuk Memilih
    0

    Hi Naomi - nice to confer with you once more.

    In the case of my table then, how would I go about utilising your solution - baring in mind I need to find consecutive "A" Marks...?

    My result set contains the following:

    Student_ID          Week_No    Day                     Time          Absence

    ELT09115116	12	4	09:00 - 10:30	A
ELT09115116	12	4	11:00 - 12:30	A
ELT09115116	12	4	13:15 - 14:45	A
ELT09115116	12	4	15:00 - 16:30	A
ELT09115116	14	1	09:00 - 10:30	A
ELT09115116	14	1	11:00 - 12:30	A
ELT09115116	14	1	13:15 - 14:45	A
ELT09115116	14	2	09:00 - 10:30	A
ELT09115116	14	2	15:00 - 16:30	A
ELT09115116	14	2	16:30 - 18:00	A
ELT09115116	14	3	09:00 - 10:30	T
ELT09115116	14	3	11:00 - 12:30	T
ELT09115116	14	4	09:00 - 10:30	A
ELT09115116	14	4	11:00 - 12:30	A
ELT09115116	14	4	13:15 - 14:45	A
ELT09115116	14	4	15:00 - 16:30	P
ELT09115116	14	5	09:00 - 10:30	T
ELT09115116	15	1	09:00 - 10:30	A
ELT09115116	15	1	11:00 - 12:30	A
ELT09115116	15	1	13:15 - 14:45	A
ELT09115116	15	2	09:00 - 10:30	A
ELT09115116	15	2	11:00 - 12:30	T
ELT09115116	15	2	11:00 - 12:30	P
ELT09115116	15	2	15:00 - 16:30	A
ELT09115116	15	2	16:30 - 18:00	A
ELT09115116	15	3	09:00 - 10:30	T
ELT09115116	15	3	11:00 - 12:30	T
ELT09115116	15	4	09:00 - 10:30	A
ELT09115116	15	4	11:00 - 12:30	P
ELT09115116	15	4	13:15 - 14:45	P
ELT09115116	15	4	15:00 - 16:30	P
ELT09115116	15	5	09:00 - 10:30	A
ELT09115116	15	5	09:00 - 10:30	T
ELT09115116	16	1	09:00 - 10:30	A
ELT09115116	16	1	11:00 - 12:30	A
ELT09115116	16	1	13:15 - 14:45	P
ELT09115116	16	2	09:00 - 10:30	P
ELT09115116	16	2	11:00 - 12:30	P
ELT09115116	16	2	11:00 - 12:30	T
ELT09115116	16	2	15:00 - 16:30	P
ELT09115116	16	2	16:30 - 18:00	P
ELT09115116	16	3	09:00 - 10:30	P
ELT09115116	16	3	09:00 - 10:30	T
ELT09115116	16	3	11:00 - 12:30	P
ELT09115116	16	3	11:00 - 12:30	T
ELT09115116	16	4	09:00 - 10:30	P
ELT09115116	16	4	11:00 - 12:30	P
ELT09115116	16	4	13:15 - 14:45	P
ELT09115116	16	4	15:00 - 16:30	P
ELT09115116	16	5	09:00 - 10:30	P
ELT09115116	16	5	09:00 - 10:30	T
ELT09115116	17	1	09:00 - 10:30	P
ELT09115116	17	1	11:00 - 12:30	A
ELT09115116	17	2	09:00 - 10:30	P
ELT09115116	17	2	11:00 - 12:30	A
ELT09115116	17	2	11:00 - 12:30	T
ELT09115116	17	2	15:00 - 16:30	P
ELT09115116	17	2	16:30 - 18:00	P
ELT09115116	17	3	09:00 - 10:30	A
ELT09115116	17	3	09:00 - 10:30	T
ELT09115116	17	3	11:00 - 12:30	A
ELT09115116	17	3	11:00 - 12:30	T
ELT09115116	17	4	09:00 - 10:30	A
ELT09115116	17	4	11:00 - 12:30	A
ELT09115116	17	4	13:15 - 14:45	P
ELT09115116	17	4	15:00 - 16:30	P
ELT09115116	17	5	09:00 - 10:30	P
ELT09115116	17	5	09:00 - 10:30	T
ELT09115116	18	1	09:00 - 10:30	L
ELT09115116	18	1	11:00 - 12:30	P
ELT09115116	18	2	09:00 - 10:30	A
ELT09115116	18	2	11:00 - 12:30	T
ELT09115116	18	2	11:00 - 12:30	P
ELT09115116	18	2	15:00 - 16:30	S
ELT09115116	18	2	16:30 - 18:00	A
ELT09115116	18	3	09:00 - 10:30	P
ELT09115116	18	3	09:00 - 10:30	T
ELT09115116	18	3	11:00 - 12:30	P
ELT09115116	18	3	11:00 - 12:30	T
ELT09115116	18	4	09:00 - 10:30	L
ELT09115116	18	4	11:00 - 12:30	P
ELT09115116	18	4	13:15 - 14:45	A
ELT09115116	18	4	15:00 - 16:30	A
ELT09115116	18	5	09:00 - 10:30	A
ELT09115116	18	5	09:00 - 10:30	T
ELT09115116	19	1	09:00 - 10:30	L
ELT09115116	19	1	11:00 - 12:30	P
ELT09115116	19	1	13:15 - 14:45	P
ELT09115116	19	2	09:00 - 10:30	P
ELT09115116	19	2	11:00 - 12:30	P
ELT09115116	19	2	11:00 - 12:30	T
ELT09115116	19	2	15:00 - 16:30	S
ELT09115116	19	2	16:30 - 18:00	P
ELT09115116	19	3	09:00 - 10:30	P
ELT09115116	19	3	09:00 - 10:30	T
ELT09115116	19	3	11:00 - 12:30	T
ELT09115116	19	3	11:00 - 12:30	P
ELT09115116	19	4	09:00 - 10:30	P
ELT09115116	19	4	11:00 - 12:30	P
ELT09115116	19	4	13:15 - 14:45	A
ELT09115116	19	4	15:00 - 16:30	P
ELT09115116	19	5	09:00 - 10:30	S
ELT09115116	19	5	09:00 - 10:30	T
ELT09115116	20	1	09:00 - 10:30	A
ELT09115116	20	1	11:00 - 12:30	A
ELT09115116	20	1	13:15 - 14:45	A
ELT09115116	20	2	09:00 - 10:30	P
ELT09115116	20	2	11:00 - 12:30	P
ELT09115116	20	2	11:00 - 12:30	T
ELT09115116	20	2	15:00 - 16:30	P
ELT09115116	20	2	16:30 - 18:00	P
ELT09115116	20	3	09:00 - 10:30	P
ELT09115116	20	3	09:00 - 10:30	T
ELT09115116	20	3	11:00 - 12:30	T
ELT09115116	20	3	11:00 - 12:30	P
ELT09115116	20	4	09:00 - 10:30	P
ELT09115116	20	4	11:00 - 12:30	P
ELT09115116	20	4	13:15 - 14:45	P
ELT09115116	20	4	15:00 - 16:30	P
ELT09115116	20	5	09:00 - 10:30	P
ELT09115116	20	5	09:00 - 10:30	T
ELT09115116	23	4	09:00 - 10:30	A
ELT09115116	23	4	11:00 - 12:30	A
ELT09115116	23	4	13:15 - 14:45	A
ELT09115116	23	4	15:00 - 16:30	A
ELT09115116	23	5	09:00 - 10:30	A
ELT09115116	23	5	09:00 - 10:30	T
ELT09115116	24	1	09:00 - 10:30	A
ELT09115116	24	1	11:00 - 12:30	P
ELT09115116	24	1	13:15 - 14:45	P
ELT09115116	24	2	09:00 - 10:30	P
ELT09115116	24	2	11:00 - 12:30	P
ELT09115116	24	2	11:00 - 12:30	T
ELT09115116	24	2	15:00 - 16:30	S
ELT09115116	24	2	16:30 - 18:00	S
ELT09115116	24	3	09:00 - 10:30	P
ELT09115116	24	3	09:00 - 10:30	T
ELT09115116	24	3	11:00 - 12:30	T
ELT09115116	24	3	11:00 - 12:30	P
ELT09115116	24	4	09:00 - 10:30	P
ELT09115116	24	4	11:00 - 12:30	P
ELT09115116	24	4	13:15 - 14:45	P
ELT09115116	24	4	15:00 - 16:30	P
ELT09115116	24	5	09:00 - 10:30	P
ELT09115116	24	5	09:00 - 10:30	T
ELT09115116	26	1	09:00 - 10:30	P
ELT09115116	26	1	11:00 - 12:30	P
ELT09115116	26	1	13:15 - 14:45	S
ELT09115116	26	2	09:00 - 10:30	P
ELT09115116	26	2	11:00 - 12:30	P
ELT09115116	26	2	11:00 - 12:30	T
ELT09115116	26	2	15:00 - 16:30	P
ELT09115116	26	2	16:30 - 18:00	P
ELT09115116	26	3	09:00 - 10:30	P
ELT09115116	26	3	09:00 - 10:30	T
ELT09115116	26	3	11:00 - 12:30	P
ELT09115116	26	3	11:00 - 12:30	T
ELT09115116	26	4	09:00 - 10:30	P
ELT09115116	26	4	11:00 - 12:30	P
ELT09115116	26	4	13:15 - 14:45	P
ELT09115116	26	4	15:00 - 16:30	P
ELT09115116	26	5	09:00 - 10:30	P
ELT09115116	26	5	09:00 - 10:30	T
ELT09115116	27	1	09:00 - 10:30	S
ELT09115116	27	1	11:00 - 12:30	A
ELT09115116	27	1	13:15 - 14:45	A
ELT09115116	27	2	09:00 - 10:30	P
ELT09115116	27	2	11:00 - 12:30	T
ELT09115116	27	2	11:00 - 12:30	P
ELT09115116	27	2	15:00 - 16:30	A
ELT09115116	27	2	16:30 - 18:00	A
ELT09115116	27	3	09:00 - 10:30	P
ELT09115116	27	3	09:00 - 10:30	T
ELT09115116	27	3	11:00 - 12:30	A
ELT09115116	27	3	11:00 - 12:30	T
ELT09115116	27	4	09:00 - 10:30	Z
ELT09115116	27	4	11:00 - 12:30	Z
ELT09115116	27	5	09:00 - 10:30	T
ELT09115116	28	1	09:00 - 10:30	P
ELT09115116	28	1	11:00 - 12:30	P
ELT09115116	28	1	13:15 - 14:45	P
ELT09115116	28	2	09:00 - 10:30	L
ELT09115116	28	2	11:00 - 12:30	P
ELT09115116	28	2	11:00 - 12:30	T
ELT09115116	28	2	15:00 - 16:30	P
ELT09115116	28	2	16:30 - 18:00	P
ELT09115116	28	3	09:00 - 10:30	P
ELT09115116	28	3	09:00 - 10:30	T
ELT09115116	28	3	11:00 - 12:30	T
ELT09115116	28	3	11:00 - 12:30	A
ELT09115116	28	4	09:00 - 10:30	P
ELT09115116	28	4	11:00 - 12:30	P
ELT09115116	28	4	13:15 - 14:45	P
ELT09115116	28	4	15:00 - 16:30	P
ELT09115116	28	5	09:00 - 10:30	T
ELT09115116	30	1	09:00 - 10:30	P
ELT09115116	30	1	11:00 - 12:30	P
ELT09115116	30	1	13:15 - 14:45	P
ELT09115116	30	2	09:00 - 10:30	P
ELT09115116	30	2	11:00 - 12:30	P
ELT09115116	30	2	11:00 - 12:30	T
ELT09115116	30	2	15:00 - 16:30	P
ELT09115116	30	2	16:30 - 18:00	P
ELT09115116	30	3	09:00 - 10:30	A
ELT09115116	30	3	09:00 - 10:30	T
ELT09115116	30	3	11:00 - 12:30	A
ELT09115116	30	3	11:00 - 12:30	T
ELT09115116	30	4	09:00 - 10:30	P
ELT09115116	30	4	11:00 - 12:30	P
ELT09115116	30	5	09:00 - 10:30	T
ELT09115116	30	5	09:00 - 10:30	A
ELT09115116	31	1	09:00 - 10:30	L
ELT09115116	31	1	11:00 - 12:30	P
ELT09115116	31	1	13:15 - 14:45	P
ELT09115116	31	2	09:00 - 10:30	P
ELT09115116	31	2	11:00 - 12:30	P
ELT09115116	31	2	11:00 - 12:30	T
ELT09115116	31	2	15:00 - 16:30	P
ELT09115116	31	2	16:30 - 18:00	P
ELT09115116	31	3	09:00 - 10:30	T
ELT09115116	31	3	09:00 - 10:30	A
ELT09115116	31	3	11:00 - 12:30	P
ELT09115116	31	3	11:00 - 12:30	T
ELT09115116	31	4	09:00 - 10:30	A
ELT09115116	31	4	11:00 - 12:30	S
ELT09115116	31	4	13:15 - 14:45	P
ELT09115116	31	4	15:00 - 16:30	P
ELT09115116	31	5	09:00 - 10:30	P
ELT09115116	31	5	09:00 - 10:30	T
ELT09115116	32	1	09:00 - 10:30	P
ELT09115116	32	1	11:00 - 12:30	L
ELT09115116	32	1	13:15 - 14:45	P
ELT09115116	32	2	09:00 - 10:30	L
ELT09115116	32	2	11:00 - 12:30	P
ELT09115116	32	2	11:00 - 12:30	T
ELT09115116	32	2	15:00 - 16:30	A
ELT09115116	32	2	16:30 - 18:00	P
ELT09115116	32	3	09:00 - 10:30	A
ELT09115116	32	3	09:00 - 10:30	T
ELT09115116	32	3	11:00 - 12:30	T
ELT09115116	32	4	09:00 - 10:30	P
ELT09115116	32	4	11:00 - 12:30	P
ELT09115116	32	4	13:15 - 14:45	P
ELT09115116	32	4	15:00 - 16:30	P
ELT09115116	32	5	09:00 - 10:30	T
ELT09115116	32	5	09:00 - 10:30	P
ELT09115116	33	1	09:00 - 10:30	P
ELT09115116	33	1	11:00 - 12:30	P
ELT09115116	33	1	13:15 - 14:45	P
ELT09115116	33	2	09:00 - 10:30	P
ELT09115116	33	2	11:00 - 12:30	P
ELT09115116	33	2	11:00 - 12:30	T
ELT09115116	33	2	15:00 - 16:30	P
ELT09115116	33	2	16:30 - 18:00	P
ELT09115116	33	3	09:00 - 10:30	T
ELT09115116	33	3	09:00 - 10:30	P
ELT09115116	33	3	11:00 - 12:30	T
ELT09115116	33	4	09:00 - 10:30	A
ELT09115116	33	4	11:00 - 12:30	S
ELT09115116	33	4	13:15 - 14:45	A
ELT09115116	33	4	15:00 - 16:30	A
ELT09115116	33	5	09:00 - 10:30	A
ELT09115116	33	5	09:00 - 10:30	T
ELT09115116	34	1	09:00 - 10:30	A
ELT09115116	34	1	11:00 - 12:30	A
ELT09115116	34	1	13:15 - 14:45	A
ELT09115116	34	2	09:00 - 10:30	A
ELT09115116	34	2	11:00 - 12:30	A
ELT09115116	34	2	11:00 - 12:30	T
ELT09115116	34	2	15:00 - 16:30	A
ELT09115116	34	2	16:30 - 18:00	A
ELT09115116	34	3	09:00 - 10:30	T
ELT09115116	34	3	11:00 - 12:30	T
ELT09115116	34	4	09:00 - 10:30	A
ELT09115116	34	4	11:00 - 12:30	A
ELT09115116	34	4	13:15 - 14:45	A
ELT09115116	34	4	15:00 - 16:30	A
ELT09115116	34	5	09:00 - 10:30	A
ELT09115116	34	5	09:00 - 10:30	T
ELT09115116	35	1	09:00 - 10:30	A
ELT09115116	35	1	11:00 - 12:30	A
ELT09115116	35	2	11:00 - 12:30	A
ELT09115116	35	2	11:00 - 12:30	T
ELT09115116	35	2	15:00 - 16:30	P
ELT09115116	35	2	16:30 - 18:00	P
ELT09115116	35	3	09:00 - 10:30	T
ELT09115116	35	3	11:00 - 12:30	T
ELT09115116	35	4	09:00 - 10:30	A
ELT09115116	35	4	11:00 - 12:30	A
ELT09115116	35	4	13:15 - 14:45	A
ELT09115116	35	4	15:00 - 16:30	P
ELT09115116	35	5	09:00 - 10:30	T
ELT09115116	38	1	09:00 - 10:30	A
ELT09115116	38	1	11:00 - 12:30	A
ELT09115116	38	1	13:15 - 14:45	A
ELT09115116	38	2	09:00 - 10:30	A
ELT09115116	38	2	11:00 - 12:30	T
ELT09115116	38	2	11:00 - 12:30	A
ELT09115116	38	2	15:00 - 16:30	A
ELT09115116	38	2	16:30 - 18:00	A
ELT09115116	38	3	09:00 - 10:30	A
ELT09115116	38	3	09:00 - 10:30	T
ELT09115116	38	3	11:00 - 12:30	A
ELT09115116	38	3	11:00 - 12:30	T
ELT09115116	38	4	09:00 - 10:30	P
ELT09115116	38	4	11:00 - 12:30	P
ELT09115116	38	4	13:15 - 14:45	P
ELT09115116	38	4	15:00 - 16:30	P
ELT09115116	38	5	09:00 - 10:30	P
ELT09115116	38	5	09:00 - 10:30	T
ELT09115116	39	1	09:00 - 10:30	P
ELT09115116	39	1	11:00 - 12:30	P
ELT09115116	39	1	13:15 - 14:45	P
ELT09115116	39	2	09:00 - 10:30	P
ELT09115116	39	2	11:00 - 12:30	P
ELT09115116	39	2	11:00 - 12:30	T
ELT09115116	39	2	15:00 - 16:30	P
ELT09115116	39	2	16:30 - 18:00	P
ELT09115116	39	3	09:00 - 10:30	A
ELT09115116	39	3	09:00 - 10:30	T
ELT09115116	39	3	11:00 - 12:30	A
ELT09115116	39	3	11:00 - 12:30	T
ELT09115116	39	4	09:00 - 10:30	A
ELT09115116	39	4	11:00 - 12:30	A
ELT09115116	39	4	13:15 - 14:45	P
ELT09115116	39	4	15:00 - 16:30	P
ELT09115116	39	5	09:00 - 10:30	T
ELT09115116	39	5	09:00 - 10:30	A
ELT09115116	40	1	09:00 - 10:30	A
ELT09115116	40	1	11:00 - 12:30	A
ELT09115116	40	1	13:15 - 14:45	L
ELT09115116	40	2	09:00 - 10:30	A
ELT09115116	40	2	11:00 - 12:30	T
ELT09115116	40	2	15:00 - 16:30	S
ELT09115116	40	2	16:30 - 18:00	P
ELT09115116	40	3	09:00 - 10:30	A
ELT09115116	40	3	09:00 - 10:30	T
ELT09115116	40	3	11:00 - 12:30	T
ELT09115116	40	3	11:00 - 12:30	A

    Thanks for your help!

    EDIT - there will be more Student_ID's in the final dataset, rather than just the one ID displayed above.

    • Diedit oleh divvyboy 02 Mei 2012 15:51
    •  
     
  • 02 Mei 2012 16:13
    Moderator
     
     Jawab Memiliki Kode
    Masuk untuk Memilih
    2

    Hi Naomi - nice to confer with you once more.

    In the case of my table then, how would I go about utilising your solution - baring in mind I need to find consecutive "A" Marks...?

    My result set contains the following:

    Student_ID          Week_No    Day                     Time          Absence

    ELT09115116	40	3	09:00 - 10:30	T
ELT09115116	40	3	11:00 - 12:30	T
ELT09115116	40	3	11:00 - 12:30	A

    Thanks for your help!

    EDIT - there will be more Student_ID's in the final dataset, rather than just the one ID displayed above.

    Using my solution for your structure it will be:

    ;with cte as (select *, ROW_NUMBER() over (PARTITION by [Student_ID],[Week_No],[Day] order by [time]) - 
 ROW_NUMBER() over (PARTITION by Absence,[Student_ID],[Week_no],[Day] order by [Time]) as Grp from @Learner)
 
  select [Student_ID], Week_No, [Day], COUNT(*) as cntConsecutive
 from cte 
 where [Absence] = 'A'
 group by [Student_ID], [Week_No],[Day], Grp, [Absence]
 having COUNT(*) >=4

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

     
  • 02 Mei 2012 17:02
     
     
    Masuk untuk Memilih
    0
    Oh,I thought you meant "if this and the next 4 are A", then flag the current one, not all of them.  Looks like you got an answer anyway though! :-)
     
  • 03 Mei 2012 8:37
     
      Memiliki Kode
    Masuk untuk Memilih
    0

    Hi again Naomi

    This is brilliant - thank you!

    I have amended the query so as not to group by the week or day - as consecutive A marks may run over 2 days or 2 weeks etc.  Here's what I've amended it to:

    with cte as (select *, ROW_NUMBER() over (PARTITION by [Student_ID] order by [Week_No],[Day_of_week],[time]) - 
 ROW_NUMBER() over (PARTITION by Absence,[Student_ID] order by [Week_no],[Day_of_week],[Time]) as Grp from UKBA_Test)
 
 select [Student_ID], COUNT(*) as cntConsecutive
 from cte 
 where [Absence] = 'A'
 group by [Student_ID], Grp, [Absence]
 having COUNT(*) >=4

    Leaving result set as:

    Student_ID      Absences

    ELT09115116	10
ELT09115116	4
ELT09115116	5
ELT09115116	5
ELT09115116	5
ELT09115116	4
ELT09115116	4

    Is there now any way I can ascertain what the first day and week number of the first "A" mark is please, since this will be used to identify the beginning of prolonged absence?

    Thanks again in advance.

     
  • 03 Mei 2012 12:05
    Moderator
     
     
    Masuk untuk Memilih
    1
    Just add MIN(Week) as [Week Start], MIN(Day) as [First Day] into the last query to identify the first week and day it starts.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

     
  • 04 Mei 2012 9:56
     
     
    Masuk untuk Memilih
    0

    You, madam, are a star!

    Everything works as it should.

    Could I request another favour and ask for the plain English breakdown of the "ROW_NUMBER() over (PARTITION by [Student_ID] order by [Week_No],[Day_of_week],[time]) - ROW_NUMBER() over (PARTITION by Absence,[Student_ID] order by [Week_no],[Day_of_week],[Time])" please?  I've never used this function before and would like to learn how it works.  I've done some research on the ROW_NUMBER() function but it looks as though you've combined two of them, which, for me, is confusing.  Could you explain how this works please?

    Many thanks again Naomi :)

     
  • 04 Mei 2012 11:09
     
      Memiliki Kode
    Masuk untuk Memilih
    0

    Also, I have attempted to create the CTE with a subquery - but there seems to be a syntax error that the CTE doesn't like.  Here's the code:

    with cte as (select *, ROW_NUMBER() over (PARTITION by [Student_ID] order by [Week_No],[Day_of_week],[Time]) - 
 ROW_NUMBER() over (PARTITION by [Student_ID],Absence order by [Week_no],[Day_of_week],[Time]) as Grp from
(SELECT Subquery........

))

    Can I run a subquery within a CTE or doesn't the CTE like this?

    Thanks.

     
  • 04 Mei 2012 12:26
    Moderator
     
     
    Masuk untuk Memilih
    0

    That's a trick used to identify the consecutive mark. I was thinking of writing a blog a while ago on this topic after one night of reflecting on this problem, but as usual, I never did.

    There is a good blog by Plamen Ratchev I always refer on this problem (which I already gave you a reference in my earlier message). There are also good articles (and explanations in his book) by Itzik Ben-Gan. There was an article by Jeff Moden on this topic also. Just google on 'Gaps and Islands problem' to read more about this idea.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

     
  • 04 Mei 2012 12:46
    Moderator
     
     
    Masuk untuk Memilih
    1
    You can run a subquery inside cte, but you must give the subquery (derived table) an alias.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

     
  • 04 Mei 2012 13:09
     
     
    Masuk untuk Memilih
    0
    Thanks for all your help Naomi and John - i'm off for a beer this weekend in celebration :D
     
  • 07 Mei 2012 16:12
     
     
    Masuk untuk Memilih
    0

    Hi again all.  I've just realised something else that's problematic....

    The MIN(Day) function brings through the minimum day of the group - not the first row day of the group.  How can I bring this through please?

    Thanks.

     
  • 07 Mei 2012 16:18
    Moderator
     
     
    Masuk untuk Memilih
    0

    For bringing info related to the first row in a group please see these blog posts:

    Optimizing TOP N per Group Queries - blog by Itzik Ben-Gan explaining various optimization ideas
    Including an Aggregated Column's Related Values - Erik and mine blog presenting several solutions of the problem with explanations for each
    Including an Aggregated Column's Related Values - Part 2 - my blog post with use cases for the previous blog

    Perhaps for your case get the main information into temp table first and then apply the idea from these blogs to get the first row.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

     
© 2013 Microsoft. All rights reserved.
|
Tanggapan Situs