Can't select data from same column on 2 different dates

PCCUtech
PCCUtech used Ask the Experts™
on
Hi There, I have a strange issue and I hope that you can help me.

I am working through an interface that only allows me to send a single select statement to our server, I cannot make temporary tables nor can I add any functions or procedures.

Basically this is my problem,  I have a table that looks something like this.

ACCTNUM      EFFDATE      AMT
-----------       ----------    -------
1111111      08-01-2011   1000
4444444      08-01-2011   1100
3333333      08-01-2011   1253
1111111      08-02-2011   1000
4444444      08-02-2011   1100
3333333      08-02-2011   1300
1111111      08-03-2011   4000
4444444      08-03-2011   1100
3333333      08-03-2011   1300
etc


The query I need to make needs to take a look at this table and return any account that has seen a balance change in the last 24 hours.

Thus if I query on the 08-03-2011 date it should show me account number 1111111 as that accounts balance has changed from the previous day.

Is there any way to do this?

I don't have any trouble pulling the accounts for a single day (any single day) of course.  I am really hoping that I am over thinking this and missing something.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Select *
FROM tableName tb1
Inner Join TableName tb2 ON tb1.AcotNum = tb2.AcctNum
where tb1.EffDate = SearchDate
AND tb2.EffDate = dateadd(SearchDate, 'd', -1)

This will return records ONLY where an account is in there for whatever date you are searching on, and the day before.

Author

Commented:
That is pretty close (completely forgot about joining a table on itself, how rusty is THAT!)

What I actually need though is for it to return when the AMT changes day over day OR when it's new (there was nothing the day before).

Any pointers there?

Author

Commented:
In essence I need to take this kind of functionality

Create table
   temp1
 as Select Distinct
        DT.ACCTNUM,
        DT.AMT
      From
        WH_ACCTDEPOSIT DT
      Where
        DT.Effdate = to_Date('08-03-2011','MM-DD-YYYY')
        

Create table
   temp1
 as Select Distinct
        DT.ACCTNUM,
        DT.AMT
      From
        WH_ACCTDEPOSIT DT
      Where
        DT.Effdate = to_Date('08-02-2011','MM-DD-YYYY')
        
Select TEMP1.ACCTNUM from Temp1 where Temp1.AMT <> Temp2.AMT



And get it into a single select statement.


Thanks again.
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Production Manager / Application Support Manager
Commented:
if the question is in oracle,then try this :

Select *
FROM tableName t1, TableName t2
where t1.AcctNum = t2.AcctNum
AND t2.EffDate = t1.effdate - 1
and t1.EffDate = '03-Aug-2011'
and t1.amt <> t2.amt   -- this will pick up all the changed ones
union
select *
from tablename t1
where t1.EffDate = '03-Aug-2011'
not exists ( select 1 from tablename t2
where t2.acctnum = t1.acctnum
and t2.EffDate = t1.effdate - 1  ) -- this will pick up all new ones which do not have previous one
awking00Information Technology Specialist

Commented:
See attached.
query.txt
awking00Information Technology Specialist
Commented:
Sorry, missed the part about if the account is new. Try again.
query.txt

Author

Commented:
Both of these work well.  Then union one runs faster but I also learned a lot from awking00.  

Not being an Oracle guy I never knew about the lag function, very handy indeed.

Thanks gents.
awking00Information Technology Specialist

Commented:
Since there can be no duplicates between the first and second parts of the union query, use UNION ALL, which does not require any sorting and should run even faster.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial