July 16, 2015 at 5:10 am
I have an attendance table with attendance per week either "Present" or "Absent"
and need to have it changed as follows with reasons attached:
If a person does not attend for an entire month he is changed to a "DROPOUT" for the weeks he/she was absent before that month until the time he re-attended.
I've tried using SQL that check for "Streaks" checking for months with Combined Attendances but cannot get the "Dropped out from" to be correct. Any help will be highly appreciated.
Using Sample SourceData having 1 as "Absent" & 0 as "Present" , I've used the following SQL but have some bugs
/* Calculate RunGroup */
SELECT WeekNum, Abs_Status, (SELECT COUNT(*) FROM [SourceData] G WHERE G.Abs_Status <> GR.Abs_Status AND G.WeekNum <= GR.WeekNum) as RunGroup
INTO [RunGroup]
FROM [SourceData] GR;
GO
/* Determine how many weeks in each run */
SELECT Abs_Status, MIN(WeekNum) as StartDate, MAX(WeekNum) as EndDate, COUNT(*) as Games
INTO [WeeksinRun]
FROM [RunGroup] A GROUP BY Abs_Status, RunGroup ORDER BY Min(WeekNum);
GO
/****** What to mark as Dropouts ******/
SELECT [StartDate]
,[EndDate]
INTO [WeekstoUpd]
FROM [WeeksinRun] a,[SourceData] b, [SourceData] c
where a.[StartDate] = b.[Weeknum]
and a.[EndDate] = c.[Weeknum]
and b.[MONTH] <> c.[MONTH]
and a.Abs_Status = '1'
and a.[StartDate] <> '2012 Week 01';
GO
/****** Update Dropout Weeks ******/
update [SourceData]
set [SourceData].[Abs_Status] = '-2'
FROM [SourceData],[WeekstoUpd]
where [WeekNum]>=[StartDate] and [WeekNum]<=[EndDate];
GO
/****** Update Absent Weeks ******/
UPDATE [SourceData]
SET [Abs_Status] = '-1'
FROM [SourceData]
WHERE [SourceData].[Abs_Status] = '1';
GO
July 16, 2015 at 7:06 am
to make it easier for others...here is your source data from fiddle
CREATE TABLE [dbo].[SourceData](
[WeekNum] [nvarchar](128) NULL,
[MONTH] [varchar](50) NULL,
[Abs_Status] [int] NOT NULL,
);
INSERT INTO [SourceData] VALUES ('2013 Week 01','2013/01',1);
INSERT INTO [SourceData] VALUES ('2013 Week 02','2013/01',1);
INSERT INTO [SourceData] VALUES ('2013 Week 03','2013/01',1);
INSERT INTO [SourceData] VALUES ('2013 Week 04','2013/01',0);
INSERT INTO [SourceData] VALUES ('2013 Week 05','2013/01',1);
INSERT INTO [SourceData] VALUES ('2013 Week 06','2013/02',1);
INSERT INTO [SourceData] VALUES ('2013 Week 07','2013/02',1);
INSERT INTO [SourceData] VALUES ('2013 Week 08','2013/02',1);
INSERT INTO [SourceData] VALUES ('2013 Week 09','2013/02',0);
INSERT INTO [SourceData] VALUES ('2013 Week 10','2013/03',0);
INSERT INTO [SourceData] VALUES ('2013 Week 11','2013/03',0);
INSERT INTO [SourceData] VALUES ('2013 Week 12','2013/03',0);
INSERT INTO [SourceData] VALUES ('2013 Week 13','2013/03',0);
INSERT INTO [SourceData] VALUES ('2013 Week 14','2013/04',1);
INSERT INTO [SourceData] VALUES ('2013 Week 15','2013/04',1);
INSERT INTO [SourceData] VALUES ('2013 Week 16','2013/04',1);
INSERT INTO [SourceData] VALUES ('2013 Week 17','2013/04',0);
INSERT INTO [SourceData] VALUES ('2013 Week 18','2013/05',1);
INSERT INTO [SourceData] VALUES ('2013 Week 19','2013/05',1);
INSERT INTO [SourceData] VALUES ('2013 Week 20','2013/05',1);
INSERT INTO [SourceData] VALUES ('2013 Week 21','2013/05',0);
INSERT INTO [SourceData] VALUES ('2013 Week 22','2013/05',1);
INSERT INTO [SourceData] VALUES ('2013 Week 23','2013/06',1);
INSERT INTO [SourceData] VALUES ('2013 Week 24','2013/06',1);
INSERT INTO [SourceData] VALUES ('2013 Week 25','2013/06',0);
INSERT INTO [SourceData] VALUES ('2013 Week 26','2013/06',0);
INSERT INTO [SourceData] VALUES ('2013 Week 27','2013/07',1);
INSERT INTO [SourceData] VALUES ('2013 Week 28','2013/07',1);
INSERT INTO [SourceData] VALUES ('2013 Week 29','2013/07',1);
INSERT INTO [SourceData] VALUES ('2013 Week 30','2013/07',0);
INSERT INTO [SourceData] VALUES ('2013 Week 31','2013/08',1);
INSERT INTO [SourceData] VALUES ('2013 Week 32','2013/08',1);
INSERT INTO [SourceData] VALUES ('2013 Week 33','2013/08',0);
INSERT INTO [SourceData] VALUES ('2013 Week 34','2013/08',1);
INSERT INTO [SourceData] VALUES ('2013 Week 35','2013/08',1);
INSERT INTO [SourceData] VALUES ('2013 Week 36','2013/09',1);
INSERT INTO [SourceData] VALUES ('2013 Week 37','2013/09',1);
INSERT INTO [SourceData] VALUES ('2013 Week 38','2013/09',1);
INSERT INTO [SourceData] VALUES ('2013 Week 39','2013/09',0);
INSERT INTO [SourceData] VALUES ('2013 Week 40','2013/10',1);
INSERT INTO [SourceData] VALUES ('2013 Week 41','2013/10',1);
INSERT INTO [SourceData] VALUES ('2013 Week 42','2013/10',1);
INSERT INTO [SourceData] VALUES ('2013 Week 43','2013/10',0);
INSERT INTO [SourceData] VALUES ('2013 Week 44','2013/10',1);
INSERT INTO [SourceData] VALUES ('2013 Week 45','2013/11',1);
INSERT INTO [SourceData] VALUES ('2013 Week 46','2013/11',1);
INSERT INTO [SourceData] VALUES ('2013 Week 47','2013/11',1);
INSERT INTO [SourceData] VALUES ('2013 Week 48','2013/11',0);
INSERT INTO [SourceData] VALUES ('2013 Week 49','2013/12',1);
INSERT INTO [SourceData] VALUES ('2013 Week 50','2013/12',1);
INSERT INTO [SourceData] VALUES ('2013 Week 51','2013/12',0);
INSERT INTO [SourceData] VALUES ('2013 Week 52','2013/12',1);
INSERT INTO [SourceData] VALUES ('2013 Week 53','2013/12',1);
INSERT INTO [SourceData] VALUES ('2014 Week 01','2014/01',1);
INSERT INTO [SourceData] VALUES ('2014 Week 02','2014/01',1);
INSERT INTO [SourceData] VALUES ('2014 Week 03','2014/01',1);
INSERT INTO [SourceData] VALUES ('2014 Week 04','2014/01',1);
INSERT INTO [SourceData] VALUES ('2014 Week 05','2014/01',1);
INSERT INTO [SourceData] VALUES ('2014 Week 06','2014/02',1);
INSERT INTO [SourceData] VALUES ('2014 Week 07','2014/02',1);
INSERT INTO [SourceData] VALUES ('2014 Week 08','2014/02',0);
INSERT INTO [SourceData] VALUES ('2014 Week 09','2014/02',1);
INSERT INTO [SourceData] VALUES ('2014 Week 10','2014/03',1);
INSERT INTO [SourceData] VALUES ('2014 Week 11','2014/03',1);
INSERT INTO [SourceData] VALUES ('2014 Week 12','2014/03',1);
INSERT INTO [SourceData] VALUES ('2014 Week 13','2014/03',1);
could you please clarify what results you are expecting from the above
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 16, 2015 at 7:08 am
I'll just copy your SQL Fiddle code in here to make it clear for anyone that might have the same issue or want to add a comment.
CREATE TABLE [dbo].[SourceData](
[WeekNum] [nvarchar](128) NULL,
[MONTH] [varchar](50) NULL,
[Abs_Status] [int] NOT NULL,
);
INSERT INTO [SourceData] VALUES ('2013 Week 01','2013/01',1);
INSERT INTO [SourceData] VALUES ('2013 Week 02','2013/01',1);
INSERT INTO [SourceData] VALUES ('2013 Week 03','2013/01',1);
INSERT INTO [SourceData] VALUES ('2013 Week 04','2013/01',0);
INSERT INTO [SourceData] VALUES ('2013 Week 05','2013/01',1);
INSERT INTO [SourceData] VALUES ('2013 Week 06','2013/02',1);
INSERT INTO [SourceData] VALUES ('2013 Week 07','2013/02',1);
INSERT INTO [SourceData] VALUES ('2013 Week 08','2013/02',1);
INSERT INTO [SourceData] VALUES ('2013 Week 09','2013/02',0);
INSERT INTO [SourceData] VALUES ('2013 Week 10','2013/03',0);
INSERT INTO [SourceData] VALUES ('2013 Week 11','2013/03',0);
INSERT INTO [SourceData] VALUES ('2013 Week 12','2013/03',0);
INSERT INTO [SourceData] VALUES ('2013 Week 13','2013/03',0);
INSERT INTO [SourceData] VALUES ('2013 Week 14','2013/04',1);
INSERT INTO [SourceData] VALUES ('2013 Week 15','2013/04',1);
INSERT INTO [SourceData] VALUES ('2013 Week 16','2013/04',1);
INSERT INTO [SourceData] VALUES ('2013 Week 17','2013/04',0);
INSERT INTO [SourceData] VALUES ('2013 Week 18','2013/05',1);
INSERT INTO [SourceData] VALUES ('2013 Week 19','2013/05',1);
INSERT INTO [SourceData] VALUES ('2013 Week 20','2013/05',1);
INSERT INTO [SourceData] VALUES ('2013 Week 21','2013/05',0);
INSERT INTO [SourceData] VALUES ('2013 Week 22','2013/05',1);
INSERT INTO [SourceData] VALUES ('2013 Week 23','2013/06',1);
INSERT INTO [SourceData] VALUES ('2013 Week 24','2013/06',1);
INSERT INTO [SourceData] VALUES ('2013 Week 25','2013/06',0);
INSERT INTO [SourceData] VALUES ('2013 Week 26','2013/06',0);
INSERT INTO [SourceData] VALUES ('2013 Week 27','2013/07',1);
INSERT INTO [SourceData] VALUES ('2013 Week 28','2013/07',1);
INSERT INTO [SourceData] VALUES ('2013 Week 29','2013/07',1);
INSERT INTO [SourceData] VALUES ('2013 Week 30','2013/07',0);
INSERT INTO [SourceData] VALUES ('2013 Week 31','2013/08',1);
INSERT INTO [SourceData] VALUES ('2013 Week 32','2013/08',1);
INSERT INTO [SourceData] VALUES ('2013 Week 33','2013/08',0);
INSERT INTO [SourceData] VALUES ('2013 Week 34','2013/08',1);
INSERT INTO [SourceData] VALUES ('2013 Week 35','2013/08',1);
INSERT INTO [SourceData] VALUES ('2013 Week 36','2013/09',1);
INSERT INTO [SourceData] VALUES ('2013 Week 37','2013/09',1);
INSERT INTO [SourceData] VALUES ('2013 Week 38','2013/09',1);
INSERT INTO [SourceData] VALUES ('2013 Week 39','2013/09',0);
INSERT INTO [SourceData] VALUES ('2013 Week 40','2013/10',1);
INSERT INTO [SourceData] VALUES ('2013 Week 41','2013/10',1);
INSERT INTO [SourceData] VALUES ('2013 Week 42','2013/10',1);
INSERT INTO [SourceData] VALUES ('2013 Week 43','2013/10',0);
INSERT INTO [SourceData] VALUES ('2013 Week 44','2013/10',1);
INSERT INTO [SourceData] VALUES ('2013 Week 45','2013/11',1);
INSERT INTO [SourceData] VALUES ('2013 Week 46','2013/11',1);
INSERT INTO [SourceData] VALUES ('2013 Week 47','2013/11',1);
INSERT INTO [SourceData] VALUES ('2013 Week 48','2013/11',0);
INSERT INTO [SourceData] VALUES ('2013 Week 49','2013/12',1);
INSERT INTO [SourceData] VALUES ('2013 Week 50','2013/12',1);
INSERT INTO [SourceData] VALUES ('2013 Week 51','2013/12',0);
INSERT INTO [SourceData] VALUES ('2013 Week 52','2013/12',1);
INSERT INTO [SourceData] VALUES ('2013 Week 53','2013/12',1);
INSERT INTO [SourceData] VALUES ('2014 Week 01','2014/01',1);
INSERT INTO [SourceData] VALUES ('2014 Week 02','2014/01',1);
INSERT INTO [SourceData] VALUES ('2014 Week 03','2014/01',1);
INSERT INTO [SourceData] VALUES ('2014 Week 04','2014/01',1);
INSERT INTO [SourceData] VALUES ('2014 Week 05','2014/01',1);
INSERT INTO [SourceData] VALUES ('2014 Week 06','2014/02',1);
INSERT INTO [SourceData] VALUES ('2014 Week 07','2014/02',1);
INSERT INTO [SourceData] VALUES ('2014 Week 08','2014/02',0);
INSERT INTO [SourceData] VALUES ('2014 Week 09','2014/02',1);
INSERT INTO [SourceData] VALUES ('2014 Week 10','2014/03',1);
INSERT INTO [SourceData] VALUES ('2014 Week 11','2014/03',1);
INSERT INTO [SourceData] VALUES ('2014 Week 12','2014/03',1);
INSERT INTO [SourceData] VALUES ('2014 Week 13','2014/03',1);
You might have overcomplicated the code as windowed functions could help you solve this problem.
select * ,
CASE WHEN SUM(Abs_Status) OVER( PARTITION BY MONTH)
= COUNT(*) OVER( PARTITION BY MONTH) THEN -2
ELSE Abs_Status * -1 END
from [SourceData];
If you want to change this into an update, you could use an updateable CTE.
WITH CTE AS(
select * ,
CASE WHEN SUM(Abs_Status) OVER( PARTITION BY MONTH)
= COUNT(*) OVER( PARTITION BY MONTH) THEN -2
ELSE Abs_Status * -1 END AS New_Status
from [SourceData];
)
UPDATE CTE
SET Abs_Status = New_Status
WHERE Abs_Status = 1 -- This will only update the necessary rows.
July 16, 2015 at 8:17 am
Thank you so much for simplifying that Code.
Its works perfectly except for
'2013 Week 52' to '2014 Week 07' - All should be -2 based on my rules above.
and
'2014 Week 09' to '2014 Week 13' - All should be -2 based on my rules above.
July 16, 2015 at 8:23 am
Thank you for replying.
Output should be
MonthWeekStatus
2013/012013 Week 01-1
2013/012013 Week 02-1
2013/012013 Week 03-1
2013/012013 Week 040
2013/012013 Week 05-1
2013/022013 Week 06-1
2013/022013 Week 07-1
2013/022013 Week 08-1
2013/022013 Week 090
2013/032013 Week 100
2013/032013 Week 110
2013/032013 Week 120
2013/032013 Week 130
2013/042013 Week 14-1
2013/042013 Week 15-1
2013/042013 Week 16-1
2013/042013 Week 170
2013/052013 Week 18-1
2013/052013 Week 19-1
2013/052013 Week 20-1
2013/052013 Week 210
2013/052013 Week 22-1
2013/062013 Week 23-1
2013/062013 Week 24-1
2013/062013 Week 250
2013/062013 Week 260
2013/072013 Week 27-1
2013/072013 Week 28-1
2013/072013 Week 29-1
2013/072013 Week 300
2013/082013 Week 31-1
2013/082013 Week 32-1
2013/082013 Week 330
2013/082013 Week 34-1
2013/082013 Week 35-1
2013/092013 Week 36-1
2013/092013 Week 37-1
2013/092013 Week 38-1
2013/092013 Week 390
2013/102013 Week 40-1
2013/102013 Week 41-1
2013/102013 Week 42-1
2013/102013 Week 430
2013/102013 Week 44-1
2013/112013 Week 45-1
2013/112013 Week 46-1
2013/112013 Week 47-1
2013/112013 Week 480
2013/122013 Week 49-1
2013/122013 Week 50-1
2013/122013 Week 510
2013/122013 Week 52-2
2013/122013 Week 53-2
2014/012014 Week 01-2
2014/012014 Week 02-2
2014/012014 Week 03-2
2014/012014 Week 04-2
2014/012014 Week 05-2
2014/022014 Week 06-2
2014/022014 Week 07-2
2014/022014 Week 080
2014/022014 Week 09-2
2014/032014 Week 10-2
2014/032014 Week 11-2
2014/032014 Week 12-2
2014/032014 Week 13-2
July 16, 2015 at 9:15 am
Ok, so you want to change the whole streak and I only changed the month. This code will update the data correctly.
WITH CTE AS(
select * ,
CASE WHEN SUM(Abs_Status) OVER( PARTITION BY MONTH)
= COUNT(*) OVER( PARTITION BY MONTH) THEN -2
ELSE Abs_Status * -1 END AS New_Status,
ROW_NUMBER() OVER(ORDER BY WeekNum) -
ROW_NUMBER() OVER(PARTITION BY Abs_Status ORDER BY WeekNum) AS grouper
from [SourceData]
),
CTE2 AS(
SELECT WeekNum,
Month,
Abs_Status,
CASE WHEN EXISTS(SELECT 1 FROM CTE i
WHERE i.New_Status = -2
AND c.grouper = i.grouper
AND c.Abs_Status = i.Abs_Status)
THEN -2 ELSE New_Status END New_Status
FROM CTE c
)
UPDATE CTE2
SET Abs_Status = New_Status
WHERE Abs_Status = 1
SELECT * FROM SourceData
ORDER BY WeekNum
July 21, 2015 at 1:52 am
Thank you so much, Luis C - Works perfectly.
July 21, 2015 at 9:38 am
aslam.jeewa (7/21/2015)
Thank you so much, Luis C - Works perfectly.
The most important part. Do you understand how it works? Remember that you will need to support this code.
July 22, 2015 at 11:17 am
Probably a dumb question, you are doing the work on CTE2 so ... HOW is the original SourceData table updated (I ran it and sure enough table SourceData appears to have been magically updated ...) ???
[font="Courier New"]UPDATE CTE2
SET Abs_Status = New_Status
WHERE Abs_Status = 1
SELECT * FROM SourceData
ORDER BY WeekNum[/font]
I would have understood if the last update was
[font="Courier New"]UPDATE s
SET Abs_Status = c.New_Status
FROM SourceData s
INNER JOIN CTE2 c ON c.WeekNum = s.WeekNum
WHERE c.Abs_Status = 1
[/font]
July 22, 2015 at 12:26 pm
That's one of the great things of CTEs. When you update a CTE, you're actually updating the underlying tables. This is not restricted to updates, you can insert and delete as well. Of course, there are restrictions on what you can do (See the section 'Updatable Views' in here: https://msdn.microsoft.com/en-us/library/ms187956.aspx). These feature allows to remove duplicates in an easy way using row_number.
July 22, 2015 at 1:38 pm
Wunderbar !
This instance is one of the single most valuable benefit of browsing through the SSC forum questions, out of sheer interest in learning more, not just because a topic happens to be an actual problem I need to fix.
I had been toying with common table expressions for a while, looking at various articles and this is the first time I am made aware of the effect on the underlying table. (Was this really in the books on line ? I usually learn more on real-life solutions by reading the forum than by the simple examples given in the books on line).
As a suggestion, this would definitely be worthwhile to include a comment to this effect in the your code. I doubt I was the only one who did not "really understand" the solution. I needed your code to discover a serious gap in my knowledge of cte's.
That one makes my day !
July 22, 2015 at 2:20 pm
j-1064772 (7/22/2015)
Was this really in the books on line ?
There's not much detail on this in BOL. There's a paragraph with an example in the Limitations and Restrictions section of UPDATE.
On the CTE article there's only a mention that a CTE can be followed by an insert, update, delete or merge.
However, the rules are only explained for views. The best explanations on modifying data through CTEs come from blogs (or forums). That's a reason on why this feature is not widely known.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply