lisa_mc
asked on
how to do multiple partitioning
Hi everyone
I was wondering if its possible to partition a set of data then get this data and partition it again?
My scenarios are
1) Need to firstly check that a register which has the same date, time, staff_code, week_no, and course I want to only pick the modules beginning with m (b.aos_code sorted desc)
2) Having carried out the first check I then need check the data for registers that occur same date, time, staff_code, week_no which have two courses attached in this case I want to select the highest nvq_lvl and if the nvq_lvls are the same then check the hrs_total
I have seperated both scenarios into partitions but im not sure if you can do what I have attached below ie do you need to partition and then store in a table and then partition again?
(I have removed the tables to shorten the code)
Help appreciated
I was wondering if its possible to partition a set of data then get this data and partition it again?
My scenarios are
1) Need to firstly check that a register which has the same date, time, staff_code, week_no, and course I want to only pick the modules beginning with m (b.aos_code sorted desc)
2) Having carried out the first check I then need check the data for registers that occur same date, time, staff_code, week_no which have two courses attached in this case I want to select the highest nvq_lvl and if the nvq_lvls are the same then check the hrs_total
I have seperated both scenarios into partitions but im not sure if you can do what I have attached below ie do you need to partition and then store in a table and then partition again?
(I have removed the tables to shorten the code)
Help appreciated
select acad_period, register_id, register_group, staff_code, week_no, day_of_week, date, [month_no],
start_time, end_time,
convert(varchar, convert(int,floor([Total Hours])))+':'+
replicate('0',(2 - len(convert (varchar, convert(int,([Total Hours] - floor([Total Hours])) * 60.0)))))
+convert (varchar, convert(int,([Total Hours] - floor([Total Hours])) * 60.0)) as 'Total Hours',
dept_code,
[m_code], [m_period], [m_desc], [c_code], [c_period], [c_desc],
[c_moa], [c_hrs], nvq_lvl_ind, [rn], [rn1]
FROM
(
select a.acad_period, a.register_id, a.register_group, a.staff_code,
a.week_no,
c.day_of_week, g.date,
left(CONVERT ( char(20) , g.date , 101 ),2) as 'month_no',
c.start_time, c.end_time,
ROUND(cast((datediff(mi, MIN(c.start_time), MAX(c.end_time)) / 60.0) as FLOAT),2) AS 'Total Hours',
c.dept_code,
b.aos_code as 'm_code', b.aos_period as 'm_period', f.full_desc as 'm_desc',
e.aos_code as 'c_code', e.aos_period as 'c_period', e.full_desc as 'c_desc', e.moa_code as 'c_moa',
e.hrs_total as 'c_hrs',
h.nvq_lvl_ind,
row_number() over (partition by a.register_id, a.register_group, a.staff_code, a.week_no, c.day_of_week, g.date,
c.start_time, c.end_time, e.aos_code, e.aos_period
order by b.aos_code desc ) as rn ,
row_number() over (partition by a.register_id, a.register_group, a.staff_code, a.week_no, c.day_of_week, g.date,
c.start_time, c.end_time
order by h.nvq_lvl_ind, e.hrs_total desc ) as rn1
from tables ....
where a.acad_period = '10/11'
group by a.acad_period, a.register_id, a.register_group, a.staff_code, a.week_no,
c.day_of_week, g.date, c.start_time,
c.end_time, c.dept_code,
b.aos_code, b.aos_period, f.full_desc,
e.aos_code, e.aos_period, e.full_desc,
e.moa_code, e.hrs_total, h.nvq_lvl_ind
) X
where (rn = 1 and rn1 = 1)
order by staff_code, [c_code], [c_period], register_id, register_group, date
ASKER
Hi Icohan
I didnt think I could do that becasue everytime I executed it I was getting a different number of rows so I know something wasn't right.
Do you know is there any other way to do this ie could I partition then rank - sort of briefly looked it up last night so need to look at it more.
Or should I just do the first partition copying the results into a #temp_table then carry out the second partition
what do you think - do you have any suggestions?
Thanks
I didnt think I could do that becasue everytime I executed it I was getting a different number of rows so I know something wasn't right.
Do you know is there any other way to do this ie could I partition then rank - sort of briefly looked it up last night so need to look at it more.
Or should I just do the first partition copying the results into a #temp_table then carry out the second partition
what do you think - do you have any suggestions?
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have accepted my own comment as it turns out I only ever need one partition because even if I do select a register which doesn't have a module (beginning with m) attached (using partition rn) I will always gets the correct answer using (partition Rn1) because I don't care what the module name is I am only extracting the course details
My objection is that I gave an answer to the question AS IT WAS posted however all the comments above and further to that are not related and far from that.
ASKER
Hi Icohan
I also asked another question which was
>> I have seperated both scenarios into partitions but im not sure if you can do what I have attached below ie do you need to partition and then store in a table and then partition again? <<
But from your first comment you haven't answered this
I also thought that you maybe could have advised me as to why I was trying to partition twice when I only needed to ever partition once as one partition will only ever bring out one line of data. Especially since in the second partition I mentioned nothing about modules therefore is not seen as important
I did not ask how to partition as I had attached code showing that I understood this - whereas your answer shows me how to structure a partition correctly and why use partitioning (which I did not ask)
I don't have a problem with assigning points but I am not going to give them away when I feel I came to answer the question first - sorry
I also asked another question which was
>> I have seperated both scenarios into partitions but im not sure if you can do what I have attached below ie do you need to partition and then store in a table and then partition again? <<
But from your first comment you haven't answered this
I also thought that you maybe could have advised me as to why I was trying to partition twice when I only needed to ever partition once as one partition will only ever bring out one line of data. Especially since in the second partition I mentioned nothing about modules therefore is not seen as important
I did not ask how to partition as I had attached code showing that I understood this - whereas your answer shows me how to structure a partition correctly and why use partitioning (which I did not ask)
I don't have a problem with assigning points but I am not going to give them away when I feel I came to answer the question first - sorry
"The partition key must exist as a single column in the table and must meet certain criteria."
http://msdn.microsoft.com/en-us/library/ms345146(v=sql.90).aspx#sql2k5parti_topic9
from general topic
http://msdn.microsoft.com/en-us/library/ms345146(v=sql.90).aspx