Hi,
I have a database called (lets say) mySales. I have several fields I need to extract from the database:

acct, saleDate, cost

I want to be able to write a query that will return to me records with the first purchase date and the last (of a given month) AND have it also give me the costs associated with those dates ONLY if they are different. Ex:

Acct item desc saleDate Cost
1 soap bar 10.01.07 1.23
1 soap bar 10.05.07 12.39
1 soap bar 10.31.07 3.28

My query will return the first purchase date (10.01.07) and the last (10.31.07).

SELECT acct, min(saleDate) '1st Date', max(saleDate) 'Last Date', min(Cost) 'MinCost', max(Cost) 'MaxCost'
FROM mySales
where Acct = 1
and saleDate BETWEEN '10-01-2007' AND '10-31-2007'
group by acct
having min(Cost) <> max(Cost)

and have it display the following:
Acct 1st Date Last Date MinCost MaxCost
1 10.01.07 10.31.07 1.23 3.28

Can anyone PLEASE help me with this? I am having difficulty figuring out how to display both min/Max Costs on the same line (for some strange reason). Any help is appreciated.




SLBIBS

__________________
SLBIBS

A little more information on this topic. My current query returns:
1 10.01.07 10.31.07 1.23 12.39
It is giving me the max Cost value from the records and not the cost value from the last record (of purchase).

SLBIBS

DECLARE @Sample TABLE (Acct INT, Item VARCHAR(10), [Desc] VARCHAR(10), SaleDate DATETIME, Cost MONEY)

INSERT @Sample
SELECT 1, 'soap', 'bar', '10.01.07', 1.23 UNION ALL
SELECT 1, 'soap', 'bar', '10.05.07', 12.39 UNION ALL
SELECT 1, 'soap', 'bar', '10.31.07', 3.28

;WITH Yak (Acct, [1st date], [Last date], MinCost, MaxCost)
AS (
SELECT Acct,
        MAX(CASE WHEN a = 1 THEN SaleDate ELSE NULL END),
        MAX(CASE WHEN b = 1 THEN SaleDate ELSE NULL END),
        MAX(CASE WHEN a = 1 THEN Cost ELSE NULL END),
        MAX(CASE WHEN b = 1 THEN Cost ELSE NULL END)
FROM (
            SELECT Acct,
                SaleDate,
                Cost,
                ROW_NUMBER() OVER (PARTITION BY Acct ORDER BY SaleDate) AS a,
                ROW_NUMBER() OVER (PARTITION BY Acct ORDER BY SaleDate DESC) AS b
            FROM @Sample
        ) AS d
WHERE 1 IN (a, b)
GROUP BY Acct
)

SELECT Acct,
    [1st date],
    [Last date],
    MinCost,
    MaxCost
FROM Yak
WHERE MinCost <> MaxCost

Thanks for the reply Peso!

It appears your select statement works if the acct is 1 but what if there are 1000 different accounts? The way I am reading your code says if it is acct 1, do "something" otherwise Null and that will not work for me. The query will be added to a Sproc and data returned based upon User Input.

I am a Novice with Sql Server 2005 (in particular T-Sql) so I may not be looking at this the way you intended it.

Regards

SLBIBS

Try it please!
The first part is only for mimic your personal environment of which I have no access.

What you need to do, is copy the ";WITH Yak" part and below and run in your environment.