Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old October 31st, 2007, 03:14 PM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 22
Thanks: 1
Thanked 0 Times in 0 Posts
Default MAX/MIN Value Help

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
  #2 (permalink)  
Old October 31st, 2007, 03:32 PM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 22
Thanks: 1
Thanked 0 Times in 0 Posts
Default

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
  #3 (permalink)  
Old November 5th, 2007, 05:43 AM
Friend of Wrox
 
Join Date: May 2006
Location: Helsingborg, , Sweden.
Posts: 246
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

  #4 (permalink)  
Old November 5th, 2007, 10:14 AM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 22
Thanks: 1
Thanked 0 Times in 0 Posts
Default

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
  #5 (permalink)  
Old November 6th, 2007, 04:00 AM
Friend of Wrox
 
Join Date: May 2006
Location: Helsingborg, , Sweden.
Posts: 246
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.




Similar Threads
Thread Thread Starter Forum Replies Last Post
How does one get the Max or Min Filename? crabjoe ASP.NET 1.0 and 1.1 Basics 4 January 15th, 2008 04:48 PM
MIN( MAX( UPDATE Stuart Stalker SQL Language 3 February 21st, 2007 07:10 AM
Min / Max followup danbush XSLT 2 August 31st, 2005 12:02 PM
MAX and MIN ON TD cleytonjordan XSLT 7 July 27th, 2005 12:39 PM
How to use Min,Max,Abs functions braindog_43 XSLT 0 February 7th, 2005 06:04 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.