May 4, 2006
Hello,I need to find students that have 4 consecutive absences. When astudent is absent 4 times in a row, they can be dropped from the class.My class attendance file contains each attendance by date and whetherthey were present or not. When the student has 4 consecutive value 1(absent) for a given session and a given class the are considered to bedropped.If I needed to know the total number of absences, I know I could groupand summarize, but this one has the consecutive twist.Table:CREATE TABLE "dbo"."clsatt"("FULL_CLASS_ID" CHAR(15) NOT NULL,"STUDENT_ID" CHAR(20) NULL,"SESSION_ID" CHAR(10) NULL,"MEETING" SMALLINT NOT NULL,"PRESENT" CHAR(2) NOT NULL);Present value of 1 is absent, value of 2 is present (3 means holiday)Classes typically meet 12 times.I would want something likeFULL_CLASS_ID, STUDENT_ID, SESSION_ID, 'Dropped'as the output.Notice in the example the first student was absent the last 4 meetingsThe second student 5 absensesand the third student was totally absentIn these three examples, they are flagged as dropped.TIARobInserts:---------------------------------------------------------------------------------insert into clsatt values ('BUS100','1675812194','200203',1,'2')insert into clsatt values ('BUS100','1675812194','200203',2,'2')insert into clsatt values ('BUS100','1675812194','200203',3,'2')insert into clsatt values ('BUS100','1675812194','200203',4,'2')insert into clsatt values ('BUS100','1675812194','200203',5,'2')insert into clsatt values ('BUS100','1675812194','200203',6,'2')insert into clsatt values ('BUS100','1675812194','200203',7,'2')insert into clsatt values ('BUS100','1675812194','200203',8,'2')insert into clsatt values ('BUS100','1675812194','200203',9,'1')insert into clsatt values ('BUS100','1675812194','200203',10,'1')insert into clsatt values ('BUS100','1675812194','200203',11,'1')insert into clsatt values ('BUS100','1675812194','200203',12,'1')insert into clsatt values ('BUS100','1712400537','200203',1,'2')insert into clsatt values ('BUS100','1712400537','200203',2,'2')insert into clsatt values ('BUS100','1712400537','200203',3,'2')insert into clsatt values ('BUS100','1712400537','200203',4,'2')insert into clsatt values ('BUS100','1712400537','200203',5,'2')insert into clsatt values ('BUS100','1712400537','200203',6,'2')insert into clsatt values ('BUS100','1712400537','200203',7,'2')insert into clsatt values ('BUS100','1712400537','200203',8,'1')insert into clsatt values ('BUS100','1712400537','200203',9,'1')insert into clsatt values ('BUS100','1712400537','200203',10,'1')insert into clsatt values ('BUS100','1712400537','200203',11,'1')insert into clsatt values ('BUS100','1712400537','200203',12,'1')insert into clsatt values ('BUS100','1801704805','200203',1,'1')insert into clsatt values ('BUS100','1801704805','200203',2,'1')insert into clsatt values ('BUS100','1801704805','200203',3,'1')insert into clsatt values ('BUS100','1801704805','200203',4,'1')insert into clsatt values ('BUS100','1801704805','200203',5,'1')insert into clsatt values ('BUS100','1801704805','200203',6,'1')insert into clsatt values ('BUS100','1801704805','200203',7,'1')insert into clsatt values ('BUS100','1801704805','200203',8,'1')insert into clsatt values ('BUS100','1801704805','200203',9,'1')insert into clsatt values ('BUS100','1801704805','200203',10,'1')insert into clsatt values ('BUS100','1801704805','200203',11,'1')insert into clsatt values ('BUS100','1801704805','200203',12,'1')insert into clsatt values ('BUS100','1922287588','200203',1,'1')insert into clsatt values ('BUS100','1922287588','200203',2,'1')insert into clsatt values ('BUS100','1922287588','200203',3,'2')insert into clsatt values ('BUS100','1922287588','200203',4,'2')insert into clsatt values ('BUS100','1922287588','200203',5,'2')insert into clsatt values ('BUS100','1922287588','200203',6,'2')insert into clsatt values ('BUS100','1922287588','200203',7,'2')insert into clsatt values ('BUS100','1922287588','200203',8,'2')insert into clsatt values ('BUS100','1922287588','200203',9,'2')insert into clsatt values ('BUS100','1922287588','200203',10,'2')insert into clsatt values ('BUS100','1922287588','200203',11,'1')insert into clsatt values ('BUS100','1922287588','200203',12,'2')insert into clsatt values ('BUS100','2188469657','200203',1,'1')insert into clsatt values ('BUS100','2188469657','200203',2,'1')insert into clsatt values ('BUS100','2188469657','200203',3,'2')insert into clsatt values ('BUS100','2188469657','200203',4,'2')insert into clsatt values ('BUS100','2188469657','200203',5,'2')insert into clsatt values ('BUS100','2188469657','200203',6,'2')insert into clsatt values ('BUS100','2188469657','200203',7,'2')insert into clsatt values ('BUS100','2188469657','200203',8,'2')insert into clsatt values ('BUS100','2188469657','200203',9,'1')insert into clsatt values ('BUS100','2188469657','200203',10,'1')insert into clsatt values ('BUS100','2188469657','200203',11,'1')insert into clsatt values ('BUS100','2188469657','200203',12,'2')insert into clsatt values ('BUS100','2515197431','200203',1,'1')insert into clsatt values ('BUS100','2515197431','200203',2,'1')insert into clsatt values ('BUS100','2515197431','200203',3,'2')insert into clsatt values ('BUS100','2515197431','200203',4,'2')insert into clsatt values ('BUS100','2515197431','200203',5,'1')insert into clsatt values ('BUS100','2515197431','200203',6,'2')insert into clsatt values ('BUS100','2515197431','200203',7,'2')insert into clsatt values ('BUS100','2515197431','200203',8,'1')insert into clsatt values ('BUS100','2515197431','200203',9,'2')insert into clsatt values ('BUS100','2515197431','200203',10,'2')insert into clsatt values ('BUS100','2515197431','200203',11,'1')insert into clsatt values ('BUS100','2515197431','200203',12,'2')
View 6 Replies
View Related
Aug 29, 2007
I have a cube that I read in a data flow section using OLE DB. To get this to work I had to do an ad hoc query through a sql server database ( this is problem that is described in post 219068).
So I now have the data and I want to derive new columns based on that data using an if statement:
if ( ISNULL(" [Facility].[REGION].[NATCODE].[MEMBER_CAPTION] " ) comptype = "NAT" else comptype = "REG"
comptype is the new derived column I am creating.
But when I try to save this I get an error: The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a requred element such as parenthesis
I selected the cube element from the columns list in derived element, when it initially put it in it looked like:
[[Facility].[REGION].[NATCODE].[MEMBER_CAPTION] ]
and gave error:
The token "[" was not recognized. The expression cannot be parsed because it contains invalid elements at the location specified.
Below is the statement I use to get data back from AS
select * from openrowset('MSOLAP', 'DATASOURCE=local; Initial Catalog=Patient Demographics 2005;',
'with member [Measures].[TimeDisplayName] as [Calendar].CurrentMember.Name
SELECT NON EMPTY { [Measures].[TimeDisplayName],[Measures].[Case Count],
[Measures].[A100 Bathing],[Measures].[A100 Bed Chair Wheelchair],
[Measures].[A100 Bladder],[Measures].[A100 Bowel],
[Measures].[A100 Dressing Lower],[Measures].[A100 Dressing Upper],
[Measures].[A100 Eating],[Measures].[A100 Grooming],
[Measures].[A100 Stairs],[Measures].[A100 Toilet],
[Measures].[A100 Toileting],[Measures].[A100 Tub Shower],
[Measures].[A100 Walk Wheelchair],[Measures].[A200 Comprehension],
[Measures].[A200 Expression],[Measures].[A200 Interaction],
[Measures].[A200 Memory],[Measures].[A200 Problem Solving],
[Measures].[D100 Bathing],[Measures].[D100 Bed Chair Wheelchair],
[Measures].[D100 Bladder],[Measures].[D100 Bowel],
[Measures].[D100 Dressing Lower],[Measures].[D100 Dressing Upper],
[Measures].[D100 Eating],[Measures].[D100 Grooming],
[Measures].[D100 Stairs],[Measures].[D100 Toilet],
[Measures].[D100 Toileting],[Measures].[D100 Tub Shower],
[Measures].[D100 Walk Wheelchair],[Measures].[D200 Comprehension],
[Measures].[D200 Expression],[Measures].[D200 Interaction],
[Measures].[D200 Memory],[Measures].[D200 Problem Solving] } ON COLUMNS,
({[Facility].[REGION].[NATCODE], [Facility].[REGION].[REGCODE]} *
[RIC].[CMGGRPCD].ALLMEMBERS ) ON ROWS FROM [CMG Demographics]
WHERE [Calendar].[Quarter 2 (2007)]')
So can some one help me on how I can do this derived colum.
View 7 Replies
View Related
