First of all, I am by no means an SQL guru, although I dabble...
My problem is as follows; I have a large database containing a table for
iteminfo (basic item descriptions) (.[itemno])
solditems (information on an item's sale) (.[itemno] .[ratecode] .[qty] .[price])
rates (basically the sale price for the items) (.[ratecode])
I need to generate a report (crystal reports) that shows for each item how many times it's been sold under each rate and the total value for that. Problem I run into is that the [rates] table might be a different size for different databases, and more rates can get added or unused rates get removed.
I've built the following SQL query that gets this info out, but if there are 10 different rates in the database I need to copy-past the code below 10 times making the query very messy, and of course it doesn't take into account an 11th rate, and starts returning odd values if a rate is removed (or worse, if rate nr 5 gets removed and the 6 becomes 5, 7 becomes 6, etc).
Note: s.itemno refers to the main query and is the itemnumber for the specific item being queried.
(select count(solditems.ratecode)
from solditems
where solditems.itemno = s.itemno
and solditems.ratecode = (
select [ratecode]
from (
select row_number() over (order by [ratecode]) as rownumber, *
from dbo.rates
) as mytable
where rownumber = '**1**'
)
) as ratqty**1**,
(select sum(solditems.qty * solditems.price) from solditems
where solditems.itemno = s.itemno and solditems.ratecode = (select
[ratecode] from (select row_number() over (order by [ratecode]) as
rownumber, * from dbo.rates) as mytable where rownumber = '**1**'))
as rateval**1**,
...
Were I using a programming language like C I'd just write a for loop like (Pseudocode): int maxi = count(rates.ratecode) for i = 0 and i!=maxi {do stuff for rownumber i} i++.
Q1: Is there a way to build a for loop like that in SQL (has to be compatible with crystal reports, so the simpler the better).
Q2: How would I go about avoiding issues if a rate is removed and the subsequent tables move up a spot (6 becomes 5, 7 becomes 6, etc).
Bonus Q: Above code is not very elegant, is there a simpler way?
Requested sample output:
itemno 1 has been sold 5 times for €1000 each under ratecode 1, 1 times for €100 under ratecode 2 itemno 2 has been sold 6 times for €500 each under ratecode 1, etc.
To avoid loops and copy-pasting you'll need a dynamic pivot for two aggregated values like that:
DECLARE
@cmd NVARCHAR(MAX) = N'',
@qty_cols NVARCHAR(MAX) = N'',
@val_cols NVARCHAR(MAX) = N'',
@n int = 0
-- this can be done in any other manner like via spt_values or FOR XML and so on
SELECT TOP 100 PERCENT
@n += 1,
@qty_cols += ', [rateqty-' + CAST(@n AS VARCHAR(10)) + ']',
@val_cols += ', [rateval-' + CAST(@n AS VARCHAR(10)) + ']',
@cmd += ', MAX([rateqty-' + CAST(@n AS VARCHAR(10)) + ']) as [rateqty-' + CAST(@n AS VARCHAR(10)) + ']'
+ ', MAX([rateval-' + CAST(@n AS VARCHAR(10)) + ']) as [rateval-' + CAST(@n AS VARCHAR(10)) + ']'
FROM #rates r
ORDER BY r.ratecode
set @cmd = 'SELECT ' + STUFF(@cmd, 1, 2, '') + '
from
(
select
''rateqty-'' + CAST(rn AS VARCHAR(10)) ratecode_qty,
''rateval-'' + CAST(rn AS VARCHAR(10)) ratecode_val,
rateqty, rateval
from (
select ratecode, rateqty, rateval, ROW_NUMBER() OVER(ORDER BY r.ratecode) rn
from #rates r
) r
) r
pivot (max(r.rateqty) for ratecode_qty in (' + STUFF(@qty_cols, 1, 2, '') + ' )) p1
pivot (max(p1.rateval) for ratecode_val in (' + STUFF(@val_cols, 1, 2, '') + ' )) p2
order by 1'
exec(@cmd)
;
For these sample data:
insert into rates(ratecode)
values (1), (2), (3)
insert into solditems(ratecode, qty, price)
values
(1, 5, 22),
(1, 2, 22),
(3, 1, 33)
output will be:
| rateqty-1 | rateval-1 | rateqty-2 | rateval-2 | rateqty-3 | rateval-3 |
|-----------|-----------|-----------|-----------|-----------|-----------|
| 2 | 154 | 0 | 0 | 1 | 33 |
Full source here: http://sqlfiddle.com/#!18/6f390/42
As of the question about simplifying your subqueries in the report where more source tables partake then my guess (since you've shown only part of code) is ratecodes have to be CROSS JOINed to the outer query (which sounds a bit scary), or joined to solditems directly and aggregated (and then pivoted) like that:
SELECT
...
FROM ... <outer query>
CROSS APPLY
(
select
r.ratecode,
count(1) rateqty, -- i don't quite understand what is this supposed to be; number of transactions?
sum(si.qty * si.price) rateval
from solditems si
INNER JOIN dbo.rates r
ON si.ratecode = r.ratecode
where si.itemno = s.itemno
group by r.ratecode
) s
upd: after posting this subquery I realized that dbo.rates is used nowhere except ROWNUMBER. So if you do pivoting, no join to rates required here:
SELECT
...
FROM ... <outer query>
CROSS APPLY
(
select
si.ratecode,
count(1) rateqty,
sum(si.qty * si.price) rateval
from solditems si
where si.itemno = s.itemno
group by si.ratecode
) s