Prev: New Instance Installation Failure
Next: Replace no init backup with init backup?
From: Erland Sommarskog on 26 Jun 2010 06:45
Cindy Lee (danparker276(a)yahoo.com) writes:
> Actually the query does work without the
> HAVING max(payDate) = (SELECT MAX(payDate) WHERE payDate < '2010010')
>
> because of the group bys. I'll test it out to see which is faster,
> but I was just wondering if there was a general rule when it comes to
> this.

This is what you said:

I want to get a list of employees who were all paid on the last
payDate in employeeLog before my query date(20100101)

I would take this that if you have these rows:

Jane 2009-12-23
Bill 2009-12-23
Jane 2009-11-25
Bill 2009-11-25
Rick 2009-11-25

You only want to see Jane and Bill, but not Rick, because he was not
paid on that last PayDate.

And that is indeed what the second query you posted would achieve.

--
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

From: Cindy Lee on 27 Jun 2010 01:07
On Jun 26, 3:45 am, Erland Sommarskog <esq...(a)sommarskog.se> wrote:
> Cindy Lee (danparker...(a)yahoo.com) writes:
> > Actually the query does work without the
> >  HAVING max(payDate) = (SELECT MAX(payDate) WHERE payDate < '2010010')
>
> > because of the group bys.  I'll test it out to see which is faster,
> > but I was just wondering if there was a general rule when it comes to
> > this.
>
> This is what you said:
>
>    I want to get a list of employees who were all paid on the last
>    payDate in employeeLog before my query date(20100101)
>
> I would take this that if you have these rows:
>
>   Jane 2009-12-23
>   Bill 2009-12-23
>   Jane 2009-11-25
>   Bill 2009-11-25
>   Rick 2009-11-25
>
> You only want to see Jane and Bill, but not Rick, because he was not
> paid on that last PayDate.
>
> And that is indeed what the second query you posted would achieve.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...(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

It's the query without the subselect I want, because it runs a lot
faster. Here's a sample below and it works becaue I don't put the
payDate in the group by.
I'm getting the results I want, is this the right way to do it, or is
there a faster way.


declare @employees table (companyId int, firstName varchar(10),
payDate int)
insert into @employees (companyId,firstName,payDate) VALUES (1,'Joe',
2007)
insert into @employees (companyId,firstName,payDate) VALUES (1,'Bob',
2007)
insert into @employees (companyId,firstName,payDate) VALUES (1,'Joe',
2008)
insert into @employees (companyId,firstName,payDate) VALUES (1,'Bob',
2008)
insert into @employees (companyId,firstName,payDate) VALUES (2,'Mary',
2008)
insert into @employees (companyId,firstName,payDate) VALUES (2,'Mary',
2009)
insert into @employees (companyId,firstName,payDate) VALUES (1,'Joe',
2010)
insert into @employees (companyId,firstName,payDate) VALUES (1,'Bob',
2010)


select companyId, firstName ,max(payDate)
from @employees where payDate< 2010
and companyId=1 group by companyId, firstName


From: Erland Sommarskog on 27 Jun 2010 05:28
Cindy Lee (danparker276(a)yahoo.com) writes:
> It's the query without the subselect I want, because it runs a lot
> faster.

It's completely uninteresting whether one query runs faster than the
other, as long as they return different results.

> Here's a sample below and it works becaue I don't put the
> payDate in the group by.
> I'm getting the results I want, is this the right way to do it, or is
> there a faster way.

Add this to your test data:

insert into @employees (companyId,firstName,payDate) VALUES (1,'Joe',2009)
insert into @employees (companyId,firstName,payDate) VALUES (1,'Sue',2002)
insert into @employees (companyId,firstName,payDate) VALUES (1,'Sue',2003)


Do you still get the results you are looking for?




--
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

From: Cindy Lee on 27 Jun 2010 12:09
On Jun 27, 2:28 am, Erland Sommarskog <esq...(a)sommarskog.se> wrote:
> Cindy Lee (danparker...(a)yahoo.com) writes:
> > It's the query without the subselect I want, because it runs a lot
> > faster.  
>
> It's completely uninteresting whether one query runs faster than the
> other, as long as they return different results.
>
> > Here's a sample below and it works becaue I don't put the
> > payDate in the group by.
> > I'm getting the results I want, is this the right way to do it, or is
> > there a faster way.
>
> Add this to your test data:
>
> insert into @employees (companyId,firstName,payDate) VALUES (1,'Joe',2009)
> insert into @employees (companyId,firstName,payDate) VALUES (1,'Sue',2002)
> insert into @employees (companyId,firstName,payDate) VALUES (1,'Sue',2003)
>
> Do you still get the results you are looking for?
>
> --
> Erland Sommarskog, SQL Server MVP, esq...(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

Ahh, thank you. Adding that data gave me bad results, I would have
only expected 1 row from that and got 3. It looks like I will have to
use the sub-select after all.

First  |  Prev  | 
Pages: 1 2
Prev: New Instance Installation Failure
Next: Replace no init backup with init backup?