TSQL-Extract only new clients for current month 3

[MwTV]

Assistance Needed:

Have the following table named "Client";

StartDate----EndDate----ClientID------Name
1/1/07-------3/31/07----246B----------Sheila
4/1/07-------5/31/07----246B----------Sheila
10/1/07-------12/31/07---246B----------Sheila
10/1/07-------12/31/07---258C----------Jane
9/1/07--------12/31/07---278D----------John

Interested in selecting only the records that did not have a StartDate prior to the first day of
the current month, October 1, 2007. In essence, I need only the new clients for the month not clients who might have been a client in the past and are returning.

In this case, my query should only extract the record for Jane. Note, although Sheila, Jane and John are current clients for the month, only Jane is a new client.

So far, I have the following sql script within within Sql Query Analyzer;

Select StartDate, EndDate, ClientID, Name
from Client
where (select ClientID where min(StartDate) >= {d' 10/1/07})

What modifications are needed, if any, to extract the desired result?

Thanks in advance.

 

[PDreyer]

select * from Client a
where STartDate>='10/1/07'
  and not exists
      (select 1 from Client b
       where a.ClientID=b.ClientID
         and STartDate<'10/1/07')
[code]
And you can replace the hard coded date with
dateadd(mm,datediff(mm,0,getdate()),0)

 

[MwTV]

Will the formula, "dateadd(mm,datediff(mm,0,getdate()),0)"

always result in the first day of the month if I run the query at any time during the month?

Also, I assume that there is another formula that will give the last day of the month?

Thanks in advance.

 

[gmmastros]

>> Will the formula, "dateadd(mm,datediff(mm,0,getdate()),0)"

Yes

>> last day of the month

Select dateadd(mm,datediff(mm,0,getdate())+ 1,-1)

First day of next month:

Select dateadd(mm,datediff(mm,0,getdate())+ 1,0

I am show that last one because I suspect you would be better off using that instead. Let me explain.

If you want to get records for a given month, you are better off using the logic...
[tt]
Where DateColumn >= FirstDayOfMonth
And DateColumn < FirstDayOfNextMonth[/tt]

If your DateColumn includes a time component, the using "less than first day of next month" will give you the right results. For example, the last day of this month is October 31. The forumula provided to you will return 2007-10-31 00:00:00.0000. Notice that the time component is midnight. So, any records that have a time component for October 31 will NOT be included. By using "Less than Nov 1" those records would be included.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Try this query...

[COLOR=blue]Select[/color]  Client.*
[COLOR=blue]From[/color]    Client
        [COLOR=blue]Inner[/color] [COLOR=blue]Join[/color] (
          [COLOR=blue]Select[/color] ClientId
          [COLOR=blue]From[/color]   Client
          [COLOR=blue]Group[/color] [COLOR=blue]By[/color] ClientId
          [COLOR=blue]Having[/color]  [COLOR=#FF00FF]Min[/color](StartDate) >= [COLOR=#FF00FF]dateadd[/color](mm,[COLOR=#FF00FF]datediff[/color](mm,0,[COLOR=#FF00FF]getdate[/color]()),0)
                  And [COLOR=#FF00FF]Min[/color](StartDate) < [COLOR=#FF00FF]dateadd[/color](mm,[COLOR=#FF00FF]datediff[/color](mm,0,[COLOR=#FF00FF]getdate[/color]())+ 1,0)
          ) [COLOR=blue]As[/color] ClientStartDates
          [COLOR=blue]On[/color] [COLOR=blue]T[/color].ClientId = ClientStartDates.ClientId

If there is anything about this query that you don't understand, let me know and I will explain it for you.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[MwTV]

Thanks for the insight.

This has really helped tremendously! Now, it appears that I will not have to worry about typing in different dates every month.

Several additional questions;

Will the formulae work for Access Jet Queries as well?
(I assume that it will definitely work for passthrough queries if it will work in sql query analyzer.)

Also, in the last line ("On T.ClientId = ClientStartDates.ClientId"), shouldn't 'T' be aliased earlier in the sql script?

Thank you.

 

[ESquared]

No it won't work for Access queries as well. Something like this might do it:

1st of month: DateSerial(Year(Date()), Month(Date()), 1)
last of month: DateSerial(Year(Date()), Month(Date()) + 1, 0)
1st of next month: DateSerial(Year(Date()), Month(Date()) + 1, 1)

or

alternate last of month:
DateSerial(Year(Date()), Month(Date()) + 1, 1) - 1

 

[gmmastros]

>> shouldn't 'T' be aliased earlier in the sql script?

Yes. Sorry. I was playing around with this query using a table variable to test it. The T in the query should really be CLIENT.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom