Home Forums SQL Server 2008 T-SQL (SS2K8) How to pick records within 2 months period RE: How to pick records within 2 months period

  • July 29, 2015 at 8:13 pm

    #1815988

    joemai (7/29/2015)

    My case is I have customers with multiple ordering dates, I'll to run a query to pull only records within 2 months period based on the previous picking records. Ex is below:

    create table #tmp_CusttInfo

    (patID varchar(20),

    enc_date datetime)

    go

    insert into #tmp_CustInfo

    select '111','2015-01-01 09:25:05.000'

    union

    select '111','2015-01-01 10:25:05.000'

    union

    select '111','2015-02-01 09:25:05.000'

    union

    select '111','2015-03-01 09:25:05.000'

    union

    select '111','2015-03-01 10:25:05.000'

    union

    select '111','2015-04-01 09:25:05.000'

    union

    select '111','2015-05-01 09:25:05.000'

    union

    select '111','2015-05-01 10:25:05.000'

    union

    select '111','2015-06-01 09:25:05.000'

    union

    select '222','2015-01-01 09:25:05.000'

    union

    select '222', '2015-01-01 10:25:05.000'

    union

    select '222','2015-02-01 09:25:05.000'

    union

    select '222','2015-03-01 09:25:05.000'

    union

    select '222','2015-03-01 10:25:05.000'

    union

    select '222','2015-04-01 09:25:05.000'

    union

    select '222','2015-05-01 09:25:05.000'

    union

    select '222','2015-05-01 10:25:05.000'

    union

    select '222','2015-06-01 09:25:05.000'

    I'd like to get:

    CustID OrderDate

    111 2015-01-01 09:25:05.000

    111 2015-03-01 09:25:05.000

    111 2015-05-01 09:25:05.000

    222 2015-01-01 09:25:05.000

    222 2015-03-01 09:25:05.000

    222 2015-05-01 09:25:05.000

    Is that possible. Thanks a milliion.

    Minh

    Based upon your expected results, there has to be more rules than what you've said so far. For instance, let's take a look at just patID = '111':

    Why do the results have only the first sale for a date?

    The first row has no sales in the prior 2 months. Why is it included in the results?

    Same for the second and third rows.

    Why do the results only have odd months?

    Please outline all of the rules that are necessary to get these results.

    Thanks!

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2