July 31, 2003 at 8:35 am
We have table SHSVCHST with fields:
SH_Cust_Alpha, SH_Serial_No, SH_Invoice_Date, SH_Meter.
I want receive the next result:
Sh_Serial_No, Min(Sh_Invoice_Date), SH_Meter (when Min(Sh_Invoice_Date), Max(SH_Invoice_Date), Sh_Meter (when Max(Sh_Invoice_Date).
So far I have done this way, but I am not sure it is correct...
select distinct a.sh_serial_no, a.sh_invoice_date, a.sh_meter
into #temp1
from shsvchst a, (select sh_cust_alpha, sh_serial_no, min(sh_invoice_date) min_sh_invoice_date
from shsvchst
group by sh_cust_alpha, sh_serial_no) b
where a.sh_invoice_date = b.min_sh_invoice_date
and b.sh_cust_alpha = a.sh_cust_alpha
and b.sh_cust_alpha = 94084
and b.sh_serial_no = a.sh_serial_no
order by a.sh_serial_no, a.sh_invoice_date
select distinct a.sh_serial_no, a.sh_invoice_date, a.sh_meter
into #temp2
from shsvchst a, (select sh_cust_alpha, sh_serial_no, max(sh_invoice_date) max_sh_invoice_date
from shsvchst
group by sh_cust_alpha, sh_serial_no) b
where a.sh_invoice_date = b.max_sh_invoice_date
and b.sh_cust_alpha = a.sh_cust_alpha
and b.sh_cust_alpha = 94084
and b.sh_serial_no = a.sh_serial_no
order by a.sh_serial_no, a.sh_invoice_date
select a.sh_serial_no, a.sh_invoice_date MinDate, a.sh_meter,
b.sh_invoice_date MaxDate, b.sh_meter
from #temp1 a, #temp2 b
where a.sh_serial_no = b.sh_serial_no
order by a.sh_serial_no
July 31, 2003 at 8:55 am
You can do this in one query, without the need for temp tables :
SELECT H.sh_serial_no,
Mini.Invoice_Date, Mini.Meter,
Maxi.Invoice_Date, Maxi.Meter
FROM SHSVCHST Head
INNER JOIN
(SELECT S1.sh_serial_no,
S1.sh_invoice_date,
S1.sh_meter
FROM SHSVCHST S1
INNER JOIN
(SELECT sh_serail_no,
min(sh_invoice_date) min_invoice_date
FROM SHSVCHST
GROUP BY sh_serial_no) S2
ON S1.sh_serial_no = S2.sh_serial_no
AND S1.sh_invoice_date = S2.min_invoice_date) Mini
ON Head.sh_serial_no = Mini.sh_serail_no
...
The same construct for the maximum, of course.
July 31, 2003 at 10:44 am
Thank you very much. I rewrote the query using the old join style.
SELECT DISTINCT H.sh_serial_no,
Mini.sh_invoice_date,
Mini.sh_meter,
Maxi.sh_invoice_date,
Maxi.sh_meter
FROM SHSVCHST H,
(SELECT S1.sh_serial_no,
S1.sh_invoice_date,
S1.sh_meter
FROM SHSVCHST S1,
(SELECT sh_serial_no,
min(sh_invoice_date) min_invoice_date
FROM SHSVCHST
GROUP BY sh_serial_no) S2
WHERES1.sh_serial_no = S2.sh_serial_no
AND S1.sh_invoice_date = S2.min_invoice_date) Mini,
(SELECT S1.sh_serial_no,
S1.sh_invoice_date,
S1.sh_meter
FROM SHSVCHST S1,
(SELECT sh_serial_no,
max(sh_invoice_date) max_invoice_date
FROM SHSVCHST
GROUP BY sh_serial_no) S2
WHERES1.sh_serial_no = S2.sh_serial_no
AND S1.sh_invoice_date = S2.max_invoice_date) Maxi
WHERE H.sh_serial_no = Mini.sh_serial_no
AND H.sh_serial_no = Maxi.sh_serial_no
AND H.sh_cust_alpha = 94084
ORDER BY H.sh_serial_no, Mini.sh_invoice_date
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply