Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
Password Reminder
Register
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #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
Reply With Quote
  #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
Reply With Quote
  #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

Reply With Quote
  #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
Reply With Quote
  #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.


Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 10:33 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.