[Closed] TSQL advice

I will ask our SQL guru at work, when he gets in 🙂

You could look at using a [url= http://msdn.microsoft.com/en-us/library/ms176102.aspx ]rank[/url] on the order date instead of a group by. That may speed things up.

Something like (untested!):

[code]
SELECT *
,RANK() OVER
(PARTITION BY fso.accountid ORDER BY invoicedate) AS InvoiceRank
FROM FilteredAccount
INNER JOIN filteredsalesorder fso
ON fso.accountid = FilteredAccount.accountid
WHERE RANK() OVER
(PARTITION BY fso.accountid ORDER BY invoicedate) = 1
and invoicedate >DATEADD(m,-1,getdate())
[/code]

I see where acjim is going but id be looking to use windowing to do
MIN(invoicedate) OVER( Partition by AccountID)

And lose the bottom group by and the outer select (oops i think you still need the outer select for the where)

(select * is bad practice too but i presume you are just showing us!)

What DB technology?

select * is bad practice too but i presume you are just showing us!)

for testing it is quick to write.

system is 2008 r2

Using the MIN() windowing function is more elegant, i wonder if it's more efficient? I guess the internal function is similar?

The other thing to do would be check your indexes - if you don't have one on invoicedate and accountid you should do.

i have been told by the DBA that the tables are indexed,

just got to figure out if the method your suggesting is quicker than how i was doing it.

never trust the DBA 😉

If you dont need the invoice date, surely the quickest way would be:

select distinct accountid
from filteredsalesorder
where invoicedate > DATEADD(m,-1,getdate())

I'm assuming accountid and invoicedate would be in a non-clustered index for a table like that.

If you need to only see particular accounts then maybe a CTE like so:

with monthoneorders
as
(
select distinct accountid
from filteredsalesorder
where invoicedate > DATEADD(m,-1,getdate())
)

select *
from monthoneorders
join FilteredAccount on monthoneorders.accountid = filteredAccount.accountid

damitamit, what your giving accounts who have ordered in the last month, what i am after is the accounts whose FIRST order in the last month. Not the same thing.

Ah, sorry. Wasn't paying attention.

Another possible way to do it would be:

with orders
as
(
select accountid, invoicedate, ROW_NUMBER() over (partition by accountid order by invoicedate asc) as rownum
from filteredsalesorder

)

select *
from orders
join FilteredAccount on orders.accountid = filteredAccount.accountid
where orders.invoicedate > DATEADD(m,-1,getdate())
and orders.rownum = 1

Thou I'm not sure it will be faster than your original query...