Can this be done without using cursor?
-
Well you see that a single query can achieve that. But since you can do it in asp have the coders present the data as the personel wants it and just do a simple "unpivoted" select.
look how simple it is to write the code in the client side :
while not myrs.eof
iCurrentOrder = MyRs.Fields("Orderid").value
while MyRs.Fields("Orderid").value
'concatenate the information, response.write it on the same line whatever
MyRs.MoveNext
wend
changed order, change line
wend
then you're done in 15 line of code and you don't have 250 subqueries on the server with 247 possible null fields to check in the client code.
-
What Remi is trying to say is that for the case of three possible tiers it will be something like this:
select item
, min(case when rank = 1 then qty end) as qty-1
, min(case when rank = 1 then price end) as price-1
, min(case when rank = 2 then qty end) as qty-2
, min(case when rank = 2 then price end) as price-2
, min(case when rank = 3 then qty end) as qty-3
, min(case when rank = 3 then price end) as price-3
from
(
select item, qty, Price, (select count(*) from yourtable t2 where t2.item = t1.item and t1.Qty <= t2.Qty) as Rank
from YourTable t1) sq
group by item
but if the number of tiers changes you will have to change that over and over, while doing it on the client side the query remains the same you just change your loop at client side
hth
* Noel -
Just curious... does it still work if you have the same item ordered twice with the same quantity (let's say for 2 different adress)?
Won't the ranking return a tie in a situation like this?
-
Just a final comment... Even if the number of items change : the query doesn't change and the client code doesn't change (if done right).
It works, has 0 maintenance to it plus the fact that it obeys every proper design rule in the book.
There's nothing more to say to this thread. If you want to reinvent the wheel, then please be my guest but I like my wheel the way it is.
Good luck with that report.
-
Because bull2000 said that this is a tiered pricing table I assumed that you will have to have different qtys per item to conform a tier. Now if this is an order table you will have to look for a tie breaker in the ranking subquery
* Noel -
Do you have an exemple of such a tie breaker?.. can't think of one right off.
-
something like this will do :
((select itemtype it count(*) c
from table t2 group by itemtype ) m -- get max per type
join numbers on m.c < = n ) -- expand row counts
but I would need more details like if pk are numbers or characters , etc but that will give you the idea.
* Noel -
I'm not sure I understand your tie breaker.
Can you write a tie breaker for this query (without using the colid column
)?Select C.Name, (Select count(*) as Total from dbo.SysColumns C2 where C2.Name <= C.Name) as Total from dbo.SysColumns C order by C.Name
-
select Name, number
from
(Select [Name], count(*) as cnt
from dbo.SysColumns
group by [name] ) Cols
join
(select number
from master.dbo.spt_values
where type ='P' and number > 0) n on n.number<= Cols.cnt
* Noel -
Here's the final query if anyone is interested :
SELECT TOP 100 PERCENT dtRanks.Name, dtRanks.Rank - dtTieBreaker.number + 1 AS NewRank
FROM (SELECT Name, number
FROM (SELECT [Name], COUNT(*) AS cnt
FROM dbo.SysColumns
GROUP BY [name]) Cols JOIN
(SELECT number
FROM master.dbo.spt_values
WHERE type = 'P' AND number > 0) n ON n.number <= Cols.cnt) dtTieBreaker INNER JOIN
(SELECT DISTINCT C.Name,
(SELECT COUNT(*) AS Total
FROM dbo.SysColumns C2
WHERE C2.Name <= C.Name) AS Rank
FROM dbo.SysColumns C) dtRanks ON dtTieBreaker.Name = dtRanks.Name
ORDER BY dtRanks.Name, NewRank
-
Hi Guys,
As a simple solution, would it not have been easier to just use a DTS package to pump the data into excel - sorting through it in small bits on the way?
Then run it when necessary.
Just a thought........
Have fun
Steve
We need men who can dream of things that never were.
-
The root of the problem is that he wants to do it in a single report formated query... He's not even sure he wants to use excel to do this.
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply