>Hi,
>
>i have a parent child table for invoice.
>
>Parent Table -Invoice
>: Invoice_Id, Invoice_Amount, Invoice_item, Invoice_Date.
>
>Child Table - Invoice_Detail
>: Item_#, Product_Id, Quantity, Unit_Price, Invoice_Id.
>
>i need to know the following through sql,
>hope you can help me, thanks ...
>
>case 1: Max & Min Amount of Purchase of a day.
>--------------------------------------------------------------------------
>Required output : Type of Invoice Invoice_Number Invoice_Amont
> --------------- -------------- -------------
> Maximum Invoice 9999999999 $99,999.99
> Minumum Invoice 9999999999 $99,999.99

SELECT Invoice_Id, Invoice_Amount FROM Invoice;
WHERE Invoice_Amount IN;
(SELECT MAX(Invoice_Amount) FROM Invoice;
WHERE Invoice_Date = DATE());
AND Invoice_Date = DATE()

SELECT Invoice_Id, Invoice_Amount FROM Invoice;
WHERE Invoice_Amount IN;
(SELECT MIN(Invoice_Amount) FROM Invoice;
WHERE Invoice_Date = DATE());
AND Invoice_Date = DATE()

Note: These will yield multiple results if more than one invoice shares the maximum or minimum value.


>case 2: 1, 2, 3 item & above invoice of a day.
>--------------------------------------------------------------------------
>require output : Type of Invoice Number_Of_Invoice Total_Amount
> --------------- ----------------- -------------
> 1_Item_Invoice 999 $9,999,999.99
> 2_Item_Invoice 999 $9,999,999.99
> 3_Item_Above 999 $9,999,999.99
>

SELECT COUNT(Item_#) AS Detail_Count, Invoice_Id FROM Invoice_Detail;
GROUP BY Invoice_Id INTO CURSOR Temp
SELECT COUNT(Invoice_Id), SUM(Invoice_Amount) FROM Invoice;
WHERE Invoice_Id IN;
(SELECT Invoice_Id FROM Temp WHERE Detail_Count = 1);
AND Invoice_Date = DATE()
SELECT COUNT(Invoice_Id), SUM(Invoice_Amount) FROM Invoice;
WHERE Invoice_Id IN;
(SELECT Invoice_Id FROM Temp WHERE Detail_Count = 2);
AND Invoice_Date = DATE()
SELECT COUNT(Invoice_Id), SUM(Invoice_Amount) FROM Invoice;
WHERE Invoice_Id IN;
(SELECT Invoice_Id FROM Temp WHERE Detail_Count >= 3);
AND Invoice_Date = DATE()
USE IN Temp


>case 3: Total & Average of Invoice in a day
>--------------------------------------------------------------------------
>require output : Type of Invoice Number_Of_Invoice Total_Amount
> --------------- ----------------- -------------
> Total_Invoice 999 $9,999,999.99
> Average_Invoice 999 $9,999,999.99

SELECT COUNT(Invoice_Id), SUM(Invoice_Id) FROM Invoice;
WHERE Invoice_Date = DATE()
SELECT COUNT(Invoice_Id), AVG(Invoice_Id) FROM Invoice;
WHERE Invoice_Date = DATE()


Of course, if you are calculating for a date other than today's date, you would specify the date instead of using the DATE() function.

Mark