Simple query I'm struggling with!

  • I'm using Sybase for this

    Sample data:

    CREATE TABLE #SampleData (ID INT, Tran_Date DATETIME, Ref VARCHAR(10) NULL)

    INSERT INTO #SampleData(ID, Tran_Date, Ref)

    SELECT 1, '2013-01-15', NULL UNION ALL

    SELECT 1, '2012-04-10', 'Kames' UNION ALL

    SELECT 1, '2013-10-05', 'Adjustment' UNION ALL

    SELECT 1, '2013-01-01', NULL UNION ALL

    SELECT 2, '2013-10-05', 'n00b' UNION ALL

    SELECT 2, '2013-10-04', 'Adjustment' UNION ALL

    SELECT 2, '2012-07-04', NULL UNION ALL

    SELECT 3, '2012-01-06', 'Adjustment' UNION ALL

    SELECT 3, '2013-09-08', 'Credit' UNION ALL

    SELECT 3, '2013-10-01', 'Adjustment'

    What I'm trying to do is find IDs where the latest entry has a REF = 'Adjustment' but the date is not greater than 05/10/2013 so from my sample data I would pull back IDs 1 and 3

    Can anyone help?

    Thanks.

    ---------------------------------------------------------

    It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
    David Edwards - Media lens[/url]

    Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
    Howard Zinn

  • Abu Dina (4/8/2014)


    I'm using Sybase for this

    Sample data:

    CREATE TABLE #SampleData (ID INT, Tran_Date DATETIME, Ref VARCHAR(10) NULL)

    INSERT INTO #SampleData(ID, Tran_Date, Ref)

    SELECT 1, '2013-01-15', NULL UNION ALL

    SELECT 1, '2012-04-10', 'Kames' UNION ALL

    SELECT 1, '2013-10-05', 'Adjustment' UNION ALL

    SELECT 1, '2013-01-01', NULL UNION ALL

    SELECT 2, '2013-10-05', 'n00b' UNION ALL

    SELECT 2, '2013-10-04', 'Adjustment' UNION ALL

    SELECT 2, '2012-07-04', NULL UNION ALL

    SELECT 3, '2012-01-06', 'Adjustment' UNION ALL

    SELECT 3, '2013-09-08', 'Credit' UNION ALL

    SELECT 3, '2013-10-01', 'Adjustment'

    What I'm trying to do is find IDs where the latest entry has a REF = 'Adjustment' but the date is not greater than 05/10/2013 so from my sample data I would pull back IDs 1 and 3

    Can anyone help?

    Thanks.

    Not sure but it looks to me that all the Adjustment dates are less than 2013-10-05, unless what you want are looking for all adjustment entries for the same max date where that date is not greater than 2013-10-5. Does that sounds about right?

  • Is the date we comparing to October 5th or May 10th?select *

    from #SampleData

    where Ref = 'Adjustment'

    and Tran_Date < '20130511'This returns a single row if we are using May 5th.



    Microsoft Certified Master - SQL Server 2008
    Follow me on twitter: @keith_tate

    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Using the row_number function

    😎

    cREATE TABLE #SampleData (ID INT, Tran_Date DATETIME, Ref VARCHAR(10) NULL)

    INSERT INTO #SampleData(ID, Tran_Date, Ref)

    SELECT 1, '2013-01-15', NULL UNION ALL

    SELECT 1, '2012-04-10', 'Kames' UNION ALL

    SELECT 1, '2013-10-05', 'Adjustment' UNION ALL

    SELECT 1, '2013-01-01', NULL UNION ALL

    SELECT 2, '2013-10-05', 'n00b' UNION ALL

    SELECT 2, '2013-10-04', 'Adjustment' UNION ALL

    SELECT 2, '2012-07-04', NULL UNION ALL

    SELECT 3, '2012-01-06', 'Adjustment' UNION ALL

    SELECT 3, '2013-09-08', 'Credit' UNION ALL

    SELECT 3, '2013-10-01', 'Adjustment'

    SELECT

    X.ID

    ,X.Tran_Date

    ,X.Ref

    FROM

    (

    SELECT

    ID

    ,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Tran_Date DESC) AS ID_RID

    ,Tran_Date

    ,Ref

    FROM #SampleData

    ) AS X

    WHERE X.ID_RID = 1

    AND X.Ref = 'Adjustment';

    drop table #SampleData

    Edit: spelling 🙂

  • Sorry this is UK date so it's 5th October.

    I'm actually trying to use this with an EXISTS operator. Basically the above is an extract from a transactions table and my requirement is to find customers whose last transaction date was on or before the 5th October 2013 (this bit I've done) but also where the last reference is of type Adjustment.

    This is what I'm struggling with.

    Please note that I am using Sybase so I can't use CTEs or any ranking functions.

    ---------------------------------------------------------

    It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
    David Edwards - Media lens[/url]

    Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
    Howard Zinn

  • I completely missed the request. I didn't see the "latest" in the post:blush:.



    Microsoft Certified Master - SQL Server 2008
    Follow me on twitter: @keith_tate

    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Abu Dina (4/8/2014)


    Sorry this is UK date so it's 5th October.

    I'm actually trying to use this with an EXISTS operator. Basically the above is an extract from a transactions table and my requirement is to find customers whose last transaction date was on or before the 5th October 2013 (this bit I've done) but also where the last reference is of type Adjustment.

    This is what I'm struggling with.

    Please note that I am using Sybase so I can't use CTEs or any ranking functions.

    Building on Eirikur's solution without using ranking functions:

    drop table #SampleData

    CREATE TABLE #SampleData (ID INT, Tran_Date DATETIME, Ref VARCHAR(10) NULL)

    INSERT INTO #SampleData(ID, Tran_Date, Ref)

    SELECT 1, '2013-01-15', NULL UNION ALL

    SELECT 1, '2012-04-10', 'Kames' UNION ALL

    SELECT 1, '2013-10-05', 'Adjustment' UNION ALL

    SELECT 1, '2013-01-01', NULL UNION ALL

    SELECT 2, '2013-10-05', 'n00b' UNION ALL

    SELECT 2, '2013-10-04', 'Adjustment' UNION ALL

    SELECT 2, '2012-07-04', NULL UNION ALL

    SELECT 3, '2012-01-06', 'Adjustment' UNION ALL

    SELECT 3, '2013-09-08', 'Credit' UNION ALL

    SELECT 3, '2013-10-01', 'Adjustment'

    SELECT

    a.ID

    ,a.Tran_Date

    ,a.Ref

    FROM

    #SampleData a

    join

    (

    SELECT

    ID

    ,max(Tran_Date) as Tran_Date

    FROM #SampleData

    group by ID

    ) AS X on a.ID = x.ID

    and a.Tran_Date = x.Tran_Date

    WHERE a.Ref = 'Adjustment'

    and a.Tran_Date <= '20131005';



    Microsoft Certified Master - SQL Server 2008
    Follow me on twitter: @keith_tate

    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Sybase has supported RANK with the OVER clause for some time, which version are you using?

    Try this out, should work 😎

    cREATE TABLE #SampleData (ID INT, Tran_Date DATETIME, Ref VARCHAR(10) NULL)

    INSERT INTO #SampleData(ID, Tran_Date, Ref)

    SELECT 1, '2013-01-15', NULL UNION ALL

    SELECT 1, '2012-04-10', 'Kames' UNION ALL

    SELECT 1, '2013-10-05', 'Adjustment' UNION ALL

    SELECT 1, '2013-01-01', NULL UNION ALL

    SELECT 2, '2013-10-05', 'n00b' UNION ALL

    SELECT 2, '2013-10-04', 'Adjustment' UNION ALL

    SELECT 2, '2012-07-04', NULL UNION ALL

    SELECT 3, '2012-01-06', 'Adjustment' UNION ALL

    SELECT 3, '2013-09-08', 'Credit' UNION ALL

    SELECT 3, '2013-10-01', 'Adjustment'

    SELECT

    X.ID

    ,X.Tran_Date

    ,X.Ref

    FROM

    (

    SELECT

    ID

    /* change ROW_NUMBER to RANK */

    ,RANK() OVER (PARTITION BY ID ORDER BY Tran_Date DESC) AS ID_RID

    ,Tran_Date

    ,Ref

    FROM #SampleData

    ) AS X

    WHERE X.ID_RID = 1

    AND X.Ref = 'Adjustment';

    drop table #SampleData

  • SELECT @@VERSION gives me back:

    Adaptive Server Enterprise/15.5/EBF

    I thought these function are not part of ASE?!

    ---------------------------------------------------------

    It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
    David Edwards - Media lens[/url]

    Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
    Howard Zinn

  • Abu Dina (4/8/2014)


    SELECT @@VERSION gives me back:

    Adaptive Server Enterprise/15.5/EBF

    I thought these function are not part of ASE?!

    IQ and Anywhere yes, ASE (to my knowledge) no.

    😎

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply