April 8, 2014 at 9:05 am
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
April 8, 2014 at 9:12 am
Abu Dina (4/8/2014)
I'm using Sybase for thisSample 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?
April 8, 2014 at 9:14 am
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.
April 8, 2014 at 9:16 am
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 🙂
April 8, 2014 at 9:25 am
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
April 8, 2014 at 9:28 am
April 8, 2014 at 9:43 am
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';
April 8, 2014 at 10:05 am
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
April 8, 2014 at 10:07 am
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
April 8, 2014 at 10:30 am
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