SELECT Min Date

31 Dec 2005 6:09 PM

Scott

My below code selects the last 10 days of data from Northwind, how can I set
that variable to the min date or dtStartDate variable?

To be clear, my results below are fine, however, I need a SET dtStartDate =
"The min date that below CODE is using as it's start date where criteria."

CODE 1:
declare @dtStartDate datetime, @dtEndDate datetime, @dtMaxDate datetime

set @dtMaxDate = (SELECT MAX(OrderDate) FROM Orders)
set @dtEndDate = @dtMaxDate

SELECT Orders.OrderDate, SUM(Orders.Freight) AS SumOfFreight FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.OrderDate IN (SELECT DISTINCT TOP 10 Orders.OrderDate
FROM Orders WHERE Orders.OrderDate <= @dtEndDate ORDER BY
Orders.OrderDate DESC) GROUP BY Orders.OrderDate

31 Dec 2005 8:58 PM

Scott

the correct result for @dtStartDate would be 1998-04-23 in my example if
anyone looks at my issue.

Show quote

"Scott" <sbai***@mileslumber.com> wrote in message
news:unW3XYjDGHA.2956@TK2MSFTNGP14.phx.gbl...
> My below code selects the last 10 days of data from Northwind, how can I
> set that variable to the min date or dtStartDate variable?
>
> To be clear, my results below are fine, however, I need a SET dtStartDate
> = "The min date that below CODE is using as it's start date where
> criteria."
>
> CODE 1:
> declare @dtStartDate datetime, @dtEndDate datetime, @dtMaxDate datetime
>
> set @dtMaxDate = (SELECT MAX(OrderDate) FROM Orders)
> set @dtEndDate = @dtMaxDate
>
> SELECT Orders.OrderDate, SUM(Orders.Freight) AS SumOfFreight FROM
> Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
> WHERE Orders.OrderDate IN (SELECT DISTINCT TOP 10
> Orders.OrderDate
> FROM Orders WHERE Orders.OrderDate <= @dtEndDate ORDER BY
> Orders.OrderDate DESC) GROUP BY Orders.OrderDate
>
>

1 Jan 2006 12:07 AM

Erland Sommarskog

Scott (sbai***@mileslumber.com) writes:

> My below code selects the last 10 days of data from Northwind, how can I
> set that variable to the min date or dtStartDate variable?
>
> To be clear, my results below are fine, however, I need a SET
> dtStartDate > = "The min date that below CODE is using as it's start
> date where criteria."

Not really sure what you are looking for, but what about:

   declare @startdate datetime
   SELECT @startdate = MIN(OrderDate)
   FROM    (SELECT DISTINCT TOP 10 OrderDate
            FROM   Northwind..Orders
            ORDER BY OrderDate DESC) AS c
   select @startdate

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

1 Jan 2006 2:46 AM

Scott

that's exactly what i needed. thx.

Show quote

"Erland Sommarskog" <esq***@sommarskog.se> wrote in message
news:Xns973EB7759381Yazorman@127.0.0.1...
> Scott (sbai***@mileslumber.com) writes:
>> My below code selects the last 10 days of data from Northwind, how can I
>> set that variable to the min date or dtStartDate variable?
>>
>> To be clear, my results below are fine, however, I need a SET
>> dtStartDate > = "The min date that below CODE is using as it's start
>> date where criteria."
>
> Not really sure what you are looking for, but what about:
>
>   declare @startdate datetime
>   SELECT @startdate = MIN(OrderDate)
>   FROM    (SELECT DISTINCT TOP 10 OrderDate
>            FROM   Northwind..Orders
>            ORDER BY OrderDate DESC) AS c
>   select @startdate
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx