How to display min and max date for every person? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to display min and max date for every person?

Author

Topic

lamenramen
Starting Member

6 Posts

Posted - 2011-02-23 : 20:37:38

Hi there,

I am trying to display the first and last date that every customer visited a store.

I can find the first using the MIN function, and the last using the MAX function.

I get something like this:


customerName       firstDate  
jones              1/1/2000   
smith              1/2/2009

and then


customerName       lastDate  
jones              1/1/2011  
smith              12/03/2010

However, how can I combine the two together in one table? I am having trouble pasting together the results of these queries. I've tried a cross join and union, but those haven't worked. I'd like the output to be like so:


customerName       firstDate       lastDate

jones              1/1/2000        1/1/2011
smith              1/2/2009        12/03/2010

After this, I'd like to find the total bill of their first order and their last order. The billing information is located in a different table and would be joined by customer name.

So in the end I would want:


customerName  firstDate  lastDate  firstOrderBill    lastOrderBill

jones        1/1/2000    1/1/2011     23.98             99.98      
smith        1/2/2009    12/03/2010   12.89             65.24

I think if I learn this, I can do the rest of what I need myself.

THANKS!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-23 : 20:58:45

1. for getting the first and last date on the same result, you can use min() and max() on the same query


select customerName, min(visitdate) as firstdate, max(visitdate) as lastdate
from   yourtable
group by customerName

2. for the 2nd part, where you want to show the first & last bill amount, if you are using SQL 2005 / 2008, you can use the row_number() function. Give it a try

KH
[spoiler]Time is always against us[/spoiler]

chris_n_osborne
Starting Member

34 Posts

Posted - 2011-02-23 : 21:09:21

[code]
/*
I do not know the structure of your tables or the data in them.
I guessed about the table names.
I guessed about some of the column names.
I guessed about the data types of some columns.
I guessed about the candidate key on the Orders table.
*/

CREATE TABLE Customers
(customerID INTEGER
,customerName NVARCHAR(32)
,CONSTRAINT pk_Customers PRIMARY KEY (customerID)
)
go

INSERT INTO Customers
(customerID
,customerName
)
SELECT 1, 'jones' UNION ALL
SELECT 2, 'smith'
go

SELECT * FROM Customers -- Display contents.
go

CREATE TABLE Orders
(OrdersID INTEGER
,customerID INTEGER
,storeVisit DATE
,orderBill MONEY
,CONSTRAINT pk_Orders PRIMARY KEY (OrdersID)
,CONSTRAINT un_Orders_CandidateKey UNIQUE (customerID, storeVisit)
,CONSTRAINT fk_Orders_Customers_CustomerID
FOREIGN KEY (customerID)
REFERENCES Customers (customerID)
)
go

INSERT INTO Orders
(ordersID
,customerID
,storeVisit
,orderBill
)
SELECT 1, 1, '2001-01-01', 23.98 UNION ALL
SELECT 2, 1, '2011-01-01', 99.09 UNION ALL
SELECT 3, 2, '2009-01-02', 12.89 UNION ALL
SELECT 4, 2, '2010-12-03', 65.24
go

SELECT * FROM Orders -- Display contents.
go

-- Proposed query:

SELECT C1.customerName
,MIN(O1.storeVisit) AS firstDate
,MAX(O1.storeVisit) AS lastDate
,MIN(O1.orderBill) AS firstOrderBill
,MAX(O1.orderBill) AS lastOrderBill
FROM Orders AS O1
INNER JOIN
Customers AS C1
ON O1.customerID = C1.customerID
GROUP BY C1.customerName

customerName firstDate lastDate firstOrderBill lastOrderBill
-------------------------------- ---------- ---------- --------------------- ---------------------
jones 2001-01-01 2011-01-01 23.98 99.09
smith 2009-01-02 2010-12-03 12.89 65.24
[/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-23 : 21:43:19

Chris, your query is returning the minimum Order Bill not the Bill that corresponding to the first Order Bill

try changing the bill amount like and see


SELECT 1, 1, '2001-01-01', 123.98 UNION ALL

KH
[spoiler]Time is always against us[/spoiler]

chris_n_osborne
Starting Member

34 Posts

Posted - 2011-02-23 : 22:29:51

Oh, I see. Then:


CREATE TABLE Customers
(customerID       INTEGER
,customerName     NVARCHAR(32)
,CONSTRAINT pk_Customers PRIMARY KEY (customerID)
)

INSERT INTO Customers
(customerID
,customerName
)
SELECT 1, 'jones' UNION ALL
SELECT 2, 'smith' UNION ALL
SELECT 3, 'adams' UNION ALL

SELECT * FROM Customers -- Display contents.
go

CREATE TABLE Orders
(OrdersID               INTEGER
,customerID             INTEGER
,storeVisit             DATE
,orderBill              MONEY
,CONSTRAINT pk_Orders PRIMARY KEY (OrdersID)
,CONSTRAINT un_Orders_CandidateKey UNIQUE (customerID, storeVisit)
,CONSTRAINT fk_Orders_Customers_CustomerID
            FOREIGN KEY (customerID)
            REFERENCES Customers (customerID)
)

INSERT INTO Orders
(ordersID
,customerID
,storeVisit
,orderBill
)
SELECT 1, 1, '2001-01-01', 23.98 UNION ALL
SELECT 2, 1, '2011-01-01', 99.09 UNION ALL
SELECT 3, 2, '2009-01-02', 12.89 UNION ALL
SELECT 4, 2, '2010-12-03', 65.24 UNION ALL
SELECT 5, 3, '2010-01-01', 99.00 UNION ALL
SELECT 6, 3, '2011-01-01', 50.00
go

SELECT * FROM Orders -- Display contents.
go

-- New Query:

  SELECT C1.customerID
        ,C1.customerName
        ,MIN(O1.storeVisit) AS firstDate
        ,MAX(O1.storeVisit) AS lastDate 
        ,(SELECT O01.orderBill
            FROM Orders AS O01
           WHERE O01.CustomerID = C1.CustomerID
             AND O01.storeVisit = MIN(O1.storeVisit)) AS firstOrderBill
        ,(SELECT O02.orderBill
            FROM Orders AS O02
           WHERE O02.CustomerID = C1.CustomerID
             AND O02.storeVisit = MAX(O1.storeVisit)) AS lastOrderBill
    FROM Orders AS O1
         INNER JOIN
         Customers AS C1
         ON O1.customerID = C1.customerID
GROUP BY C1.customerID
        ,C1.customerName


customerID  customerName                     firstDate  lastDate   firstOrderBill        lastOrderBill
----------- -------------------------------- ---------- ---------- --------------------- ---------------------
1           jones                            2001-01-01 2011-01-01 23.98                 99.09
2           smith                            2009-01-02 2010-12-03 12.89                 65.24
3           adams                            2010-01-01 2011-01-01 99.00                 50.00

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-23 : 22:37:25

the query can be much simplified with use of row_number(). Provided that OP is using SQL 2005/2008

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources