Using PLSQL, searching for advice with 2 different sub-formulas.
Let me have a data set 1) a unique headcount, and 2) a total number of credits, as the total time running. Raw data:
This is transactional data - every time a student registers or does a course, then the record, date, student ID and credit is included (with the syllabus number and a group Along with other relevant data) Per-student per course a record.
STUDENT_ID credit date 1 3 01-Jan-12 1 2 02-Jan-12 57 1 03-Jan-12 1 1 03-Jan-12 < P> Processed Data:
It needs to be seen by the owner - later it will be used for trending (for example, to see what happens in January-01 this year is Jan-01 of the year , e.t.c.). Date of unique headset date 1 3 01-Jan-12 1 5 02-Jan-12 2 7 03-Jan-12 For this Cruel views are to write a group of different groups (one for each day), and store them together. For example: "DATE" as "DATE" as "SumCredits", '01 -JAN-12 'as the "UniqueHeadcount", SUM (CREDIT_HR), as "DATE". Select (DISTINCT STUDENT_ID) TO_CHAR (DATE, 'titled') & lt; = '20120101' Select the group by '01-Jan-12 'COUNT COUNT (separate STUDENT_ID) as "Unique headcount", in the form of SUM (CREDIT_HR) as "sucrements", '02 -JAN-12' In "Date" to TO_CHAR (DATE, 'yyyymmdd') & lt; = '201220102' '02 -JAN-12 'union by group ... and it works - the results are correct - but as you can see - it's near the elegant There is nowhere else - and if you have to do it for 365 days, then ... this is an animal
So far in my quest, I have learned about an 'over' clause which is used I can - like:
"UniqueHeadcount", SUM (CREDIT_HR) over the SELECT COUNT (DISTINCT STUDENT_ID) (ORNDER BY TRUNC (RSTS_DATE) between ROWS, unbounded PRECEEDING and current ROW) (TRUNC (RSTS_DA According to TE, "SumCredits" between ROWS open exposure and current ROW), TRUNC (RSTS_DATE) as registration from "DATE" to This query is in a way smaller (yay) But there are two important problems that I can not get yet, the way to get out First of all, it works with (DISTINCT COUNT) (design, partially?). So I comment for a moment, but then go into another problem: it ignores the TRUNC () function RSTS_DATE, although it appears only one day / month / year value when you run a SELECT on it It really has time too, so the result I get is not considered more than just the date, rather often - so instead of one record per day, my processed data copy Day brings hundreds of records (each for each course registration). For example:
UniqueHeadcount SumCredits Date 1 3 01-Jan-12 1 5 02-Jan-12 2 6 03-Jan-12 (Hidden time: 07:32:27) 2 7 03-Jan-12 (Hidden time: 08:01:33) I'm not after that.
That's why I am looking for expertise - if I have explained it so far - is there any other way to use this section, or maybe another feature may be that I am using PLSQL for this? I am not strong in PLSQL if you can not tell, but if someone can give me some direction - even Google's words, I appreciate the help.
Thanks : SumCredits as CREDIT_HR, TRUNC by TRUNC (RSTS_DATE) registration group at RSTS_DATE (Unique Headset) (Unreserved between PRECEDING and current ROW RSTS_DATE ROWS According to the order) UniqueHeadcount, in the form of SUM (SumDrits) (under the RSTDTERAS ROS in-built processing and existing line), R & DDTET by CRDAT As an
Comments
Post a Comment