Hi,
This is a query that joins a vouple of tables to display all the products purchased by a group of customers and the price they paid for it.
SELECT DISTINCT (p.code),p.descript_1 + ' ' + p.descript_2 + ' ' + p.descript_3 as description,sol.p_sales as price,sol.q_ordered as quantity,(sol.p_sales * sol.q_ordered) as total,so.date_in as dateFROM EfasLive..debtor AS d
INNER JOIN Informatica..so AS so ON so.deb_code = d.code AND so.co_code = d.co_code
INNER JOIN Informatica..so_line AS sol ON sol.code = so.code AND sol.co_code = so.co_code AND sol.acc_year = so.acc_year AND sol.efas = so.efas
INNER JOIN EfasLive..part AS p ON p.code = sol.part
WHERE d.[grp{003}] = 'GROUP' AND p.co_code = 1 AND p.code NOT LIKE '&%' AND so.date_in > DATEADD(m,-3,GETDATE()) AND sol.q_ordered > 0
ORDER BY (p.code), datum DESC
The problem with this is that it returns multiple lines for every product (p.code). Like so:
code description price quantity total date
603244 description_1 17.950000 150.000000 2692.500000000000 2007-08-01 00:00:00
603244 description_1 17.950000 150.000000 2692.500000000000 2007-07-10 00:00:00
603245 description_2 17.950000 40.000000 718.000000000000 2007-07-24 00:00:00
603245 description_2 17.950000 25.000000 448.750000000000 2007-07-16 00:00:00
603663 description_3 16.890000 27.000000 456.030000000000 2007-07-20 00:00:00
603663 description_3 16.890000 150.000000 2533.500000000000 2007-07-10 00:00:00
603663 description_3 16.890000 30.000000 506.700000000000 2007-07-03 00:00:00
I'd like there to be only 1 line for every different code with it's description. The idea is that the other rows are dropped and that only the first one remains. The one with the most recent purchase. I tried with GROUP BY but that's probably wrong since you'd have to add all the other columns as well and you end up with the same one. And even with adding a HAVING at the end I can't see how this could be solved
edit: There aren't any actual relationships in the tables (it's ancient you see ...) I'm using SQL 2005 though.
HelloJust a few ideas, do not have an MSSQL instace nearby to test:
1) use cursor, which I'd prefer to avoid
- declare cursor for "Select Distinct (code) From EfasLive..part"
2) do something like
Select ....
From (Select Distinct (code) From EfasLive..part) as p Inner Join... (the rest of the tables)...
Where date = Max(date)
The idea is to join distinct "code" values with other tables and filter only the most recent one for each table (that's what "Max(date)" is for)
3) try to use CTEs (Common Table Expressions)
Post the solution after you find one! Tnx
|||
Hmz I'll try out some of this stuff. Thx! But the multiple instances of code don't come from Efaslive..part. They are actually from so_line. An so_line is actually an orderline. For every order there could be multiple lines each containing a different product. But since it's over a timespan of 3 months it will include multiple orders and thus multiple so_lines containing the same product (once for every order it was in). So doing a distinct on code in Efaslive..part probably won't work. Or at least it doesn't make sense to me 
edit: actually this can be simplified ... just pretend that the result I get is a simple select query from a single table. As if it was a CTE 
I'll look into the pointer thing.
|||Here the query,
Code Snippet
;With CTE
as
(
SELECT DISTINCT
p.code
, p.descript_1 + ' ' + p.descript_2 + ' ' + p.descript_3 as description
, sol.p_sales as price
, sol.q_ordered as quantity
, sol.p_sales * sol.q_ordered as total
, so.date_in as date
, max(so.date_in) over(partition by p.code) as maxdate
--, Row_Number() over(partition by p.code order by so.date_in desc) rid
FROM
EfasLive..debtor AS d
INNER JOIN Informatica..so AS so
ON so.deb_code = d.code
AND so.co_code = d.co_code
INNER JOIN Informatica..so_line AS sol
ON sol.code = so.code
AND sol.co_code = so.co_code
AND sol.acc_year = so.acc_year
AND sol.efas = so.efas
INNER JOIN EfasLive..part AS p
ON p.code = sol.part
WHERE
d.[grp{003}] = 'GROUP'
AND p.co_code = 1
AND p.code NOT LIKE '&%'
AND so.date_in > DATEADD(m,-3,GETDATE())
AND sol.q_ordered > 0
)
Select
code
, description
, price
, quantity
, total
, date
From
CTE
Where
date = maxdate
--rid=1
