Prev: XML - Eliminate Namespace in Elements
Next: Dealing with BAD Dates in SSIS
From: tshad on 24 Jun 2010 14:45
I have a result set that I need to filter down to the last date something
was done.

The problem is that I can't seem to use the derived table in the From clause
of my Subquery. The Query looks something like:

SELECT
sFirstname,
sLastName,
nProductID,
VisitDate,
dVisitDate
FROM
(
SELECT
P.sFirstName,
P.sLastName,
PE.nProductID,
CONVERT(varchar,PE.dVisitDate,101),
PE.dVisitDate
From Person P
Join PersonEvent PE on P.PersonID = PE.PersonID
WHERE ...
) AS A
WHERE dStart = (Select Max(dStart) from A WHERE A.sFirstName = sFirstName
and A.sLastName = sLastName and A.nProductID = ProductID)

I use the dStart as my Max because I don't want to test my maximum date as a
string.

I also use the derived table as I don't want to have to handle the different
JOINS I would need in the Max subquery as that is already done.

How would I change this to only the latest date for each person and Product?
This obviously doesn't work.

Thanks,

Tom




From: tshad on 24 Jun 2010 15:37
I could probably put this in a temporary table then do the select off of
that but I'd rather use the derived table.

Not sure why I can't use the derived table as it is just a table???

Tom

"tshad" <tfs(a)dslextreme.com> wrote in message
news:uz31o18ELHA.1868(a)TK2MSFTNGP05.phx.gbl...
>I have a result set that I need to filter down to the last date something
>was done.
>
> The problem is that I can't seem to use the derived table in the From
> clause of my Subquery. The Query looks something like:
>
> SELECT
> sFirstname,
> sLastName,
> nProductID,
> VisitDate,
> dVisitDate
> FROM
> (
> SELECT
> P.sFirstName,
> P.sLastName,
> PE.nProductID,
> CONVERT(varchar,PE.dVisitDate,101),
> PE.dVisitDate
> From Person P
> Join PersonEvent PE on P.PersonID = PE.PersonID
> WHERE ...
> ) AS A
> WHERE dStart = (Select Max(dStart) from A WHERE A.sFirstName = sFirstName
> and A.sLastName = sLastName and A.nProductID = ProductID)
>
> I use the dStart as my Max because I don't want to test my maximum date as
> a string.
>
> I also use the derived table as I don't want to have to handle the
> different JOINS I would need in the Max subquery as that is already done.
>
> How would I change this to only the latest date for each person and
> Product? This obviously doesn't work.
>
> Thanks,
>
> Tom
>
>
>
>


From: tshad on 24 Jun 2010 15:45

I could do a SELECT/INTO a temp table such as #table and then do:

SELECT *
FROM #table
WHERE dStart = (SELECT MAX(dStart)
FROM #table
WHERE A.sFirstName = sFirstName
and A.sLastName = sLastName
and A.nProductID = ProductID)

This does seem to work.

But I would rather not use a temporary table.

Tom

"tshad" <tfs(a)dslextreme.com> wrote in message
news:uXg%231S9ELHA.2276(a)TK2MSFTNGP06.phx.gbl...
>I could probably put this in a temporary table then do the select off of
>that but I'd rather use the derived table.
>
> Not sure why I can't use the derived table as it is just a table???
>
> Tom
>
> "tshad" <tfs(a)dslextreme.com> wrote in message
> news:uz31o18ELHA.1868(a)TK2MSFTNGP05.phx.gbl...
>>I have a result set that I need to filter down to the last date something
>>was done.
>>
>> The problem is that I can't seem to use the derived table in the From
>> clause of my Subquery. The Query looks something like:
>>
>> SELECT
>> sFirstname,
>> sLastName,
>> nProductID,
>> VisitDate,
>> dVisitDate
>> FROM
>> (
>> SELECT
>> P.sFirstName,
>> P.sLastName,
>> PE.nProductID,
>> CONVERT(varchar,PE.dVisitDate,101),
>> PE.dVisitDate
>> From Person P
>> Join PersonEvent PE on P.PersonID = PE.PersonID
>> WHERE ...
>> ) AS A
>> WHERE dStart = (Select Max(dStart) from A WHERE A.sFirstName = sFirstName
>> and A.sLastName = sLastName and A.nProductID = ProductID)
>>
>> I use the dStart as my Max because I don't want to test my maximum date
>> as a string.
>>
>> I also use the derived table as I don't want to have to handle the
>> different JOINS I would need in the Max subquery as that is already done.
>>
>> How would I change this to only the latest date for each person and
>> Product? This obviously doesn't work.
>>
>> Thanks,
>>
>> Tom
>>
>>
>>
>>
>
>


From: Bob Barrows on 24 Jun 2010 15:57
tshad wrote:
> I could probably put this in a temporary table then do the select off
> of that but I'd rather use the derived table.
>
> Not sure why I can't use the derived table as it is just a table???
>

And I'm not sure what's preventing you. Are you getting an error
message? if so, what is it?
--
HTH,
Bob Barrows


From: Erland Sommarskog on 24 Jun 2010 17:22
tshad (tfs(a)dslextreme.com) writes:
> I have a result set that I need to filter down to the last date something
> was done.
>
> The problem is that I can't seem to use the derived table in the From
> clause of my Subquery. The Query looks something like:
>
> SELECT
> sFirstname,
> sLastName,
> nProductID,
> VisitDate,
> dVisitDate
> FROM
> (
> SELECT
> P.sFirstName,
> P.sLastName,
> PE.nProductID,
> CONVERT(varchar,PE.dVisitDate,101),
> PE.dVisitDate
> From Person P
> Join PersonEvent PE on P.PersonID = PE.PersonID
> WHERE ...
> ) AS A
> WHERE dStart = (Select Max(dStart) from A WHERE A.sFirstName = sFirstName
> and A.sLastName = sLastName and A.nProductID = ProductID)

You can use a CTE instead (if you are on SQL 2005 that is).

WITH A AS (
SELECT P.sFirstName,
P.sLastName,
PE.nProductID,
CONVERT(varchar,PE.dVisitDate,101),
PE.dVisitDate
From Person P
Join PersonEvent PE on P.PersonID = PE.PersonID
WHERE ...
)
SELECT sFirstname,
sLastName,
nProductID,
VisitDate,
dVisitDate
FROM A
WHERE dStart = (Select Max(dStart) from A WHERE A.sFirstName = sFirstName
and A.sLastName = sLastName and A.nProductID = ProductID)

But this is not a fantastic solution; despite the syntax the query in
the CTE is likely to be executed twice.

A better solution is to add this to the CTE:

rowno = row_number() OVER(PARTITION BY P.sFirstName, P.sLastName,
PE.nPRoductID
ORDER BY dStart DESC)

In the WHERE clause rather than the subquery, you have:

WHERE rowno = 1




--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 |  Next  |  Last
Pages: 1 2 3
Prev: XML - Eliminate Namespace in Elements
Next: Dealing with BAD Dates in SSIS