January 23, 2017 at 5:21 pm
Hi all,
I've got a table showing student attendance for a Karate school.
I'd like to return all rows containing a string in one column, where attendance has fallen to below 2x per week.
Here's what I've got so far:
--Return last belt colour where enthusiasm wanes
Select ProfileID, Cast (date as date) As 'Date', notes
From logs
Where notes like '%Belt Test Passed%'
Group by profileId, notes, date
Order by profileId, Date
What I'd like is only those rows where 1) you have the string, AND 2) immediately (ie chronologically) precede rows showing fewer than 2 entries for that Profile Id in the following week. (I guess this would need some kind of self join, or Partition statement, or Min(Date).
So, this is what I want - this guy was a Yellow belt and started losing interest (I don't necessarily need the last two rows in my final result set, I manually added these two just here to illustrate).
2435 2008-04-26 Yellow/Red Stripe- Belt Test Passed
2435 2008-04-28 Attended
2435 2008-05-06 Attended
At the moment I get this
4136 2012-08-25 White / Blue Stripe- Belt Test Passed
4136 2012-10-27 White / Green Stripe- Belt Test Passed
4136 2012-12-22 White/ Red Stripe- Belt Test Passed
4136 2013-02-23 White / Black Stripe- Belt Test Passed
4136 2013-04-27 Yellow/White Stripe- Belt Test Passed
4136 2013-06-22 Yellow/Red Stripe- Belt Test Passed
4136 2013-08-24 Yellow/BlackStripe- Belt Test Passed
January 23, 2017 at 8:24 pm
Without CREATE TABLE and INSERT scripts, there's not much we can do to help you. =(
January 23, 2017 at 11:10 pm
Here's code that I think would do the trick, you would get better performance using windowing functions to do this, but my head's not in the game for that right now :).
Your sample shows where a student has attended twice in the week following a belt pass as an issue, but your description states that you want where a student has attended less than two (i.e. 0..1) times in the week following the pass to be an issue. I've assumed your text descriptor means less than or including 2 days attended.
declare @logs table (
ProfileID int
, date datetime
, notes varchar(500)
)
insert into @logs
select 4136, '2012-08-25', 'White / Blue Stripe- Belt Test Passed'
union all select 4136, '2012-10-27', 'White / Green Stripe- Belt Test Passed'
union all select 4136, '2012-12-22', 'White/ Red Stripe- Belt Test Passed'
union all select 4136, '2012-12-23', 'Attended'
union all select 4136, '2012-12-24', 'Attended'
union all select 4136, '2012-12-25', 'Attended'
union all select 4136, '2013-02-23', 'White / Black Stripe- Belt Test Passed'
union all select 4136, '2013-04-27', 'Yellow/White Stripe- Belt Test Passed'
union all select 4136, '2013-06-22', 'Yellow/Red Stripe- Belt Test Passed'
union all select 4136, '2013-08-24', 'Yellow/BlackStripe- Belt Test Passed'
union all select 4136, '2013-08-25', 'Attended'
union all select 4136, '2013-08-26', 'Attended'
union all select 4136, '2013-08-27', 'Yellow/BlackStripe- Belt Test Passed'
union all select 4136, '2013-08-28', 'Attended'
union all select 4136, '2013-08-29', 'Attended'
union all select 4136, '2013-08-29', 'Attended'
union all select 4137, '2013-08-30', 'Attended'
union all select 4138, '2013-08-29', 'Yellow/BlackStripe- Belt Test Passed'
union all select 4138, '2013-08-30', 'Attended'
union all select 4130, '2013-08-29', 'White/ Red Stripe- Belt Test Passed';
select mylogs.ProfileID, mylogs.Date, mylogs.notes
from @logs as mylogs
-- latest pass with poor post-attendance
cross apply (
select date, profileid, notes
from @logs as pass_log
where pass_log.notes like '%Passed%'
-- No later passes
and not exists (
select *
from @logs as later_pass_log
where later_pass_log.ProfileID = pass_log.ProfileID
and later_pass_log.notes like '%Passed%'
and later_pass_log.date > pass_log.date
)
-- Less than 3 attendances in the following week
and (
select count(*)
from @logs as attended_log
where attended_log.ProfileID = pass_log.ProfileID
and attended_log.date > pass_log.date
and attended_log.date <= dateadd(week, 1, pass_log.date)
) < 3
and pass_log.date <= mylogs.date
and pass_log.ProfileID = mylogs.ProfileID
) as latest_relevant_pass
Let me know if I've interpreted your request correctly,
Andrew P.
January 24, 2017 at 4:28 am
Andrew P - Monday, January 23, 2017 11:10 PMHere's code that I think would do the trick, you would get better performance using windowing functions to do this, but my head's not in the game for that right now :).Your sample shows where a student has attended twice in the week following a belt pass as an issue, but your description states that you want where a student has attended less than two (i.e. 0..1) times in the week following the pass to be an issue. I've assumed your text descriptor means less than or including 2 days attended.
declare @logs table (
ProfileID int
, date datetime
, notes varchar(500)
)
insert into @logs
select 4136, '2012-08-25', 'White / Blue Stripe- Belt Test Passed'
union all select 4136, '2012-10-27', 'White / Green Stripe- Belt Test Passed'
union all select 4136, '2012-12-22', 'White/ Red Stripe- Belt Test Passed'
union all select 4136, '2012-12-23', 'Attended'
union all select 4136, '2012-12-24', 'Attended'
union all select 4136, '2012-12-25', 'Attended'
union all select 4136, '2013-02-23', 'White / Black Stripe- Belt Test Passed'
union all select 4136, '2013-04-27', 'Yellow/White Stripe- Belt Test Passed'
union all select 4136, '2013-06-22', 'Yellow/Red Stripe- Belt Test Passed'
union all select 4136, '2013-08-24', 'Yellow/BlackStripe- Belt Test Passed'
union all select 4136, '2013-08-25', 'Attended'
union all select 4136, '2013-08-26', 'Attended'
union all select 4136, '2013-08-27', 'Yellow/BlackStripe- Belt Test Passed'
union all select 4136, '2013-08-28', 'Attended'
union all select 4136, '2013-08-29', 'Attended'
union all select 4136, '2013-08-29', 'Attended'
union all select 4137, '2013-08-30', 'Attended'
union all select 4138, '2013-08-29', 'Yellow/BlackStripe- Belt Test Passed'
union all select 4138, '2013-08-30', 'Attended'
union all select 4130, '2013-08-29', 'White/ Red Stripe- Belt Test Passed';select mylogs.ProfileID, mylogs.Date, mylogs.notes
from @logs as mylogs
-- latest pass with poor post-attendance
cross apply (
select date, profileid, notes
from @logs as pass_log
where pass_log.notes like '%Passed%'
-- No later passes
and not exists (
select *
from @logs as later_pass_log
where later_pass_log.ProfileID = pass_log.ProfileID
and later_pass_log.notes like '%Passed%'
and later_pass_log.date > pass_log.date
)
-- Less than 3 attendances in the following week
and (
select count(*)
from @logs as attended_log
where attended_log.ProfileID = pass_log.ProfileID
and attended_log.date > pass_log.date
and attended_log.date <= dateadd(week, 1, pass_log.date)
) < 3
and pass_log.date <= mylogs.date
and pass_log.ProfileID = mylogs.ProfileID
) as latest_relevant_passLet me know if I've interpreted your request correctly,
Andrew P.
Very close, despite the fact that in retrospect my post was ambiguous. Sorry I'm a DBA not used to delving this deep into code, but needs must.
When a student fails to attend class 2x per week, that is an issue/incident. What I want (for now) is his most recent belt pass PRIOR to the incident. Later I'll try to get clever and use this code as a base for Data Mining.
The 'Cross Apply' was a nifty idea! I wonder if it's possible to populate the @Logs temp table with values generated inline, possibly by my original Select, ie the inserted values don't need to be pasted in with every runtime. I'm also intrigued by these 'Windowing' ?? functions.
January 24, 2017 at 4:37 am
JaybeeSQL - Tuesday, January 24, 2017 4:28 AMVery close, despite the fact that in retrospect my post was ambiguous. Sorry I'm a DBA not used to delving this deep into code, but needs must.When a student fails to attend class 2x per week, that is an issue/incident. What I want (for now) is his most recent belt pass PRIOR to the incident. Later I'll try to get clever and use this code as a base for Data Mining.
The 'Cross Apply' was a nifty idea! I wonder if it's possible to populate the @Logs temp table with values generated inline, possibly by my original Select, ie the inserted values don't need to be pasted in with every runtime. I'm also intrigued by these 'Windowing' ?? functions.
Perhaps giving those CREATE and INSERT statements pietlinden asked for will help us close that final gap for you.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 24, 2017 at 5:45 am
Thom A - Tuesday, January 24, 2017 4:37 AMJaybeeSQL - Tuesday, January 24, 2017 4:28 AMVery close, despite the fact that in retrospect my post was ambiguous. Sorry I'm a DBA not used to delving this deep into code, but needs must.When a student fails to attend class 2x per week, that is an issue/incident. What I want (for now) is his most recent belt pass PRIOR to the incident. Later I'll try to get clever and use this code as a base for Data Mining.
The 'Cross Apply' was a nifty idea! I wonder if it's possible to populate the @Logs temp table with values generated inline, possibly by my original Select, ie the inserted values don't need to be pasted in with every runtime. I'm also intrigued by these 'Windowing' ?? functions.
Perhaps giving those CREATE and INSERT statements pietlinden asked for will help us close that final gap for you.
Perhaps...
USE [MARS]
GO
/****** Object: Table [dbo].[logs] Script Date: 24/01/2017 12:43:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[logs](
[id] [int] IDENTITY(1,1) NOT NULL,
[profileId] [int] NULL,
[logId] [int] NULL,
[date] [datetime2](7) NULL DEFAULT (getdate()),
[time] [datetime2](7) NULL DEFAULT (getdate()),
[notes] [nvarchar](max) NULL,
[userId] [int] NULL,
[rankId] [int] NULL,
[grade] [nvarchar](10) NULL,
[awardedFor] [nvarchar](50) NULL,
[classId] [int] NULL,
[itemId] [int] NULL,
[cost] [int] NULL DEFAULT ((0)),
[bit] NULL,
[letter] [bit] NULL,
[fax] [bit] NULL,
[call] [bit] NULL,
[StaffLogin_Out] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
USE [MARS]
GO
INSERT INTO [dbo].[logs]
([profileId]
,[logId]
,[date]
,[time]
,[notes]
,[userId]
,[rankId]
,[grade]
,[awardedFor]
,[classId]
,[itemId]
,[cost]
,
,[letter]
,[fax]
,[call]
,[StaffLogin_Out])
VALUES
(<profileId, int,>
,<logId, int,>
,<date, datetime2(7),>
,<time, datetime2(7),>
,<notes, nvarchar(max),>
,<userId, int,>
,<rankId, int,>
,<grade, nvarchar(10),>
,<awardedFor, nvarchar(50),>
,<classId, int,>
,<itemId, int,>
,<cost, int,>
,<email, bit,>
,<letter, bit,>
,<fax, bit,>
,<call, bit,>
,<StaffLogin_Out, int,>)
GO
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply