December 12, 2012 at 8:34 am
here is my current, functional NTILE statement.
select cr.sup, cr.part, cr.loc, cr.countrank, cr.days,
NTILE(240) OVER (PARTITION by LOC order by countrank desc) as partitionday
from cc_v_countrank cr
details about the data are that for each cr.loc above, there is a single cr.days. cr.days is joined into a view, so it is available in a separate table for loc,days that is normalized.
What I need to do is get cr.days (or equivilent) into the NTILE() replacing the current '240'.
Any ideas?
notes:
to clarify what the data is, 'loc' is various locations that inventory is held, countrank is a number I derive from various places to determine how important it is to count this item. days is how many days it can take to count each item. NTILE divides these up nicely so I can say if partitionday = 1, count this item. once they count the item, it updates the source date and the rank drops to zero. yesterday's 2's basically become 1 because they are the highest rank. other factors influence the rank so each day the sequence might vary a little but that isn't important.
The key here is that each location might have a different number of days they could. One location might count once per week, less 4 weeks of break, so their number is 48, which I hold in a table. Another might count items daily, less some number of days for breaks/other inventory duties so they have 240.
Thanks in advance!
(fyi, I have considered a cursor to do this, looping through each location, but that feels very brute force and lacks elegance.)
December 12, 2012 at 8:49 am
After looking at the Books Online entry for NTILE, it's expecting an "integer expression", and it does allow you to access fields that exist in the PARTITION BY expression, but does NOT allow accessing fields that exist in the FROM expression, so the only way I can see this happening is if you can group your data by the cr.days values using the PARTITION BY expression. Not sure what impact that would have, but I suspect it would have to be the first field in the PARTITION BY list, assuming that's viable in the first place.
Let me know. I agree that a CURSOR could be ugly here.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 12, 2012 at 8:53 am
sgmunson (12/12/2012)
After looking at the Books Online entry for NTILE, it's expecting an "integer expression", and it does allow you to access fields that exist in the PARTITION BY expression, but does NOT allow accessing fields that exist in the FROM expression, so the only way I can see this happening is if you can group your data by the cr.days values using the PARTITION BY expression. Not sure what impact that would have, but I suspect it would have to be the first field in the PARTITION BY list, assuming that's viable in the first place.
I can't figure out how to access the fields in partition by...
I could do this in stages and partition by days then loc, that wouldn't matter because the loc would still partition out the same which is what is important, but I can't see how to access that field..
December 12, 2012 at 8:58 am
Without table data to work with, I have no testing capacity, but I want to know what you get when you do something like this:
select cr.sup, cr.part, cr.loc, cr.countrank, cr.days,
NTILE(cr.days) OVER (PARTITION by cr.days, LOC order by countrank desc) as partitionday
from cc_v_countrank cr
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 12, 2012 at 9:01 am
sgmunson (12/12/2012)
Without table data to work with, I have no testing capacity, but I want to know what you get when you do something like this:
select cr.sup, cr.part, cr.loc, cr.countrank, cr.days,
NTILE(cr.days) OVER (PARTITION by cr.days, LOC order by countrank desc) as partitionday
from cc_v_countrank cr
The multi-part identifier "cr.days" could not be bound.
December 12, 2012 at 9:15 am
Ok, how about:
select cr.sup, cr.part, cr.loc, cr.countrank, cr.days,
NTILE(days) OVER (PARTITION by cr.days, LOC order by countrank desc) as partitionday
from cc_v_countrank cr
and if the results aren't quite right, try reversing the order of the PARTITION BY clause (assuming this works). Alternatively, you may need to run this query once for each value of cr.days. Ugly yes, but functional, as the NTILE can accept a declared variable instead of a field. There's an example online that uses that format and it's from an MS site.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 12, 2012 at 9:18 am
sgmunson (12/12/2012)
Ok, how about:
select cr.sup, cr.part, cr.loc, cr.countrank, cr.days,
NTILE(days) OVER (PARTITION by cr.days, LOC order by countrank desc) as partitionday
from cc_v_countrank cr
and if the results aren't quite right, try reversing the order of the PARTITION BY clause (assuming this works). Alternatively, you may need to run this query once for each value of cr.days. Ugly yes, but functional, as the NTILE can accept a declared variable instead of a field. There's an example online that uses that format and it's from an MS site.
no go, if I don't reference the table I get
Invalid column name 'days'
swapping the partition order doesn't do anything. I'm thinking that I need distinct rows of 'days' for it to work. Looks like I'll be using a cursor.
December 12, 2012 at 9:41 am
Well, I managed to find a working example, and perhaps you can tailor this to suit:
declare @a table (a int null, b int null)
insert @a values (1, 1), (1, 2), (2, 3), (2, 4);
declare @b-2 table (groups int null)
insert @b-2 values (3), (2)
select *
from @b-2 outside
cross
apply (
select *,
group_id = ntile(outside.groups) over (partition by outside.groups order by a.a)
from @a a
) itvf;
This came from the following right here on sqlservercentral:
http://www.sqlservercentral.com/Forums/Topic918639-392-1.aspx#bm918647
Not sure exactly how to apply that to your situation, but I've tried it on my local instance of 2008 R2, and it works without syntax errors.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 12, 2012 at 2:02 pm
sgmunson (12/12/2012)
Well, I managed to find a working example, and perhaps you can tailor this to suit:
declare @a table (a int null, b int null)
insert @a values (1, 1), (1, 2), (2, 3), (2, 4);
declare @b-2 table (groups int null)
insert @b-2 values (3), (2)
select *
from @b-2 outside
cross
apply (
select *,
group_id = ntile(outside.groups) over (partition by outside.groups order by a.a)
from @a a
) itvf;
This came from the following right here on sqlservercentral:
http://www.sqlservercentral.com/Forums/Topic918639-392-1.aspx#bm918647
Not sure exactly how to apply that to your situation, but I've tried it on my local instance of 2008 R2, and it works without syntax errors.
Thanks. I'm going to try to work through this on my need. In the mean time I did a cursor :/ I don't know what the emoticon for *shame* is else I would use it...
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply