September 26, 2014 at 9:16 am
the select statement in the join orders the numbers asc and if you run everything below the case statement it works, but as you run the whole thing the order is no longer there.
I need the order to work. What am I missing?
--Assign Intern's students
Update tblObjectType3_14
Set [FldNumeric23764]= case when tID = 1 and Q.n <= 100 then 2
when tID = 2 and Q.n <= 150 then 2
when tID = 3 and Q.n <= 150 then 2
when tID = 4 and Q.n <= 130 then 2
when tID = 5 and Q.n <= 100 then 2
when tID = 6 and Q.n <= 130 then 2
when tID = 7 and Q.n <= 70 then 2
when tID = 8 and Q.n <= 80 then 2
when tID = 9 and Q.n <= 60 then 2
when tID = 10 and Q.n <= 130 then 2
else null end
--Select Q.*, T.tid
From tblObjectType3_14 C14
Inner Join tblObjectType3_12 C12 on C12.nID=C14.nID
Left outer join tblCustomerText1 CT1 on CT1.nCustomerID = C14.nID --For Phone Numbers
Inner Join tblObjectType3_10 C10 on C10.nID=C14.nID
inner join tblcustomer C on C.acustid = C14.nid
Inner Join (
Select C14.nID, [FldNumeric23760] StackRank
, [FldNumeric23757] AcadRatingBand
, [FldNumeric23758] LTEBand
, Row_Number() OVER(PARTITION BY [FldNumeric22749]
ORDER BY [FldNumeric23760] asc) n
From tblObjectType3_14 C14
Inner Join tblObjectType3_10 C10 on C10.nID = C14.nID
Inner Join tblObjectType3_6 C6 on C6.nID = C14.nID
Inner Join tblObjectType3_3 C3 on C3.nID = C14.nID
Inner Join tblObjectType3_4 C4 on C4.nID = C14.nID
Where [FldNumeric23760] is not null--Stack Rank is not null
and ([FldNumeric23757] in (1,2,3,4,6) --AcadRatingBand
OR ([FldNumeric23757]=5 and [FldNumeric21785] in (5,6,7)))--or Admit in ARB 1
and [FldNumeric23758] between 1 and 9--LTEBand
and (C4.[FldNumeric21974] <> 6 OR [FldNumeric21974] IS NULL)--Not deferred
and C3.FldNumeric21785 <> 8 --Not Denied
and ([FldNumeric23764] <>1 or [FldNumeric23764] is null)--not assigned to an AC
and (C14.[FldNumeric23765] IS NULL) --Override not in use
--Order by [FldNumeric23760] asc
) Q on Q.nID = C14.nID
Inner Join tblTerritory T on T.tID=C12.FldNumeric23369
where (tPhone is not null or CT1.strFld3 is not null or CT1.strFld6 is not null)--Have a phone number (pref, mobile, home)
September 26, 2014 at 9:26 am
Let's start with some formatting. You can use the IFCode shortcuts (on the left side when posting) for code windows and such.
UPDATE tblObjectType3_14
SET [FldNumeric23764] = CASE
WHEN tID = 1
AND Q.n <= 100
THEN 2
WHEN tID = 2
AND Q.n <= 150
THEN 2
WHEN tID = 3
AND Q.n <= 150
THEN 2
WHEN tID = 4
AND Q.n <= 130
THEN 2
WHEN tID = 5
AND Q.n <= 100
THEN 2
WHEN tID = 6
AND Q.n <= 130
THEN 2
WHEN tID = 7
AND Q.n <= 70
THEN 2
WHEN tID = 8
AND Q.n <= 80
THEN 2
WHEN tID = 9
AND Q.n <= 60
THEN 2
WHEN tID = 10
AND Q.n <= 130
THEN 2
ELSE NULL
END
--Select Q.*, T.tid
FROM tblObjectType3_14 C14
INNER JOIN tblObjectType3_12 C12 ON C12.nID = C14.nID
LEFT JOIN tblCustomerText1 CT1 ON CT1.nCustomerID = C14.nID --For Phone Numbers
INNER JOIN tblObjectType3_10 C10 ON C10.nID = C14.nID
INNER JOIN tblcustomer C ON C.acustid = C14.nid
INNER JOIN (
SELECT C14.nID
,[FldNumeric23760] StackRank
,[FldNumeric23757] AcadRatingBand
,[FldNumeric23758] LTEBand
,Row_Number() OVER (
PARTITION BY [FldNumeric22749] ORDER BY [FldNumeric23760] ASC
) n
FROM tblObjectType3_14 C14
INNER JOIN tblObjectType3_10 C10 ON C10.nID = C14.nID
INNER JOIN tblObjectType3_6 C6 ON C6.nID = C14.nID
INNER JOIN tblObjectType3_3 C3 ON C3.nID = C14.nID
INNER JOIN tblObjectType3_4 C4 ON C4.nID = C14.nID
WHERE [FldNumeric23760] IS NOT NULL --Stack Rank is not null
AND (
[FldNumeric23757] IN (
1
,2
,3
,4
,6
) --AcadRatingBand
OR (
[FldNumeric23757] = 5
AND [FldNumeric21785] IN (
5
,6
,7
)
)
) --or Admit in ARB 1
AND [FldNumeric23758] BETWEEN 1
AND 9 --LTEBand
AND (
C4.[FldNumeric21974] <> 6
OR [FldNumeric21974] IS NULL
) --Not deferred
AND C3.FldNumeric21785 <> 8 --Not Denied
AND (
[FldNumeric23764] <> 1
OR [FldNumeric23764] IS NULL
) --not assigned to an AC
AND (C14.[FldNumeric23765] IS NULL) --Override not in use
--Order by [FldNumeric23760] asc
) Q ON Q.nID = C14.nID
INNER JOIN tblTerritory T ON T.tID = C12.FldNumeric23369
WHERE (
tPhone IS NOT NULL
OR CT1.strFld3 IS NOT NULL
OR CT1.strFld6 IS NOT NULL
) --Have a phone number (pref, mobile, home)
Now that we can read it, can you explain what the problem is?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 26, 2014 at 9:38 am
Shouldn't you be setting FldNumeric23764 to different values here:
UPDATE C14 SET
[FldNumeric23764]= case
when tID = 1 and Q.n <= 100 then 2
when tID = 2 and Q.n <= 150 then 2
when tID = 3 and Q.n <= 150 then 2
when tID = 4 and Q.n <= 130 then 2
when tID = 5 and Q.n <= 100 then 2
when tID = 6 and Q.n <= 130 then 2
when tID = 7 and Q.n <= 70 then 2
when tID = 8 and Q.n <= 80 then 2
when tID = 9 and Q.n <= 60 then 2
when tID = 10 and Q.n <= 130 then 2
else null end
FROM tblObjectType3_14 C14
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 26, 2014 at 9:56 am
the select statement in the join orders the numbers asc and if you run everything below the case statement it works, but as you run the whole thing the order is no longer there
September 26, 2014 at 10:06 am
Shelley Allen (9/26/2014)
the select statement in the join orders the numbers asc and if you run everything below the case statement it works, but as you run the whole thing the order is no longer there
What do you mean the order is no longer there? Remember we can't see your screen and have barely a vague idea of what you doing here. Are the new values not what you think they should be?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 26, 2014 at 10:39 am
the select statement in the join assigns a number in the "n" column
1-whatever
it also orders it there
however when trying to use the case statement at the top the "n" is no longer ordered in asc order so it just assigns 100 people not the 100 highest people
September 26, 2014 at 10:42 am
smaller example:
this set of code
Select *--top 10 c14.nID, [FldNumeric23760]
from tblObjectType3_14 C14
Inner Join tblObjectType3_12 C12 on C12.nID=C14.nID
Inner Join tblTerritory T on T.tID=C12.FldNumeric23369
where [FldNumeric23764] = 2
and tID = 1
order by [FldNumeric23760]
NOTE: there are 66 values total. how would you only take the top 10 values in [FldNumeric23760] (low is good) and assign it to something
September 26, 2014 at 10:49 am
It's going to be hard to troubleshoot this minus test data and expected results.
That said - something does jump out: The only place you have the ORDER BY specified is within the row_number() statement, which doesn't get used anywhere else that I can tell.The row_number() calculation looks to be generated and then tossed away, so it does nothing for you.
I was expecting to see it used in the outer level join OR the WHERE clause, but I can't see it.
This line:
,Row_Number() OVER (
PARTITION BY [FldNumeric22749] ORDER BY [FldNumeric23760] ASC
) n
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply