March 13, 2013 at 9:58 am
Hopefully, I am posting this in a right area if not I am sorry. Dont mind the $$$$,#### and ****
I am trying to get the last 60 days of records from today, and I am having issued writing the query that returns that value....RDMS its tie to SQL/Oracle database and here is my query that is currently working but I just like to see the last 60 days if anyone can possible help!!!
SELECT INFORMENT.PRODUCT_OFFER_PURCHASE.*******_#######, INFORMENT.INVOLVED_PARTY.INVOLVED_PARTY_ID, To_Char(INFORMENT.PRODUCT_OFFER_PURCHASE.DATE_ADDED, 'YYYYMMDD'), INFORMENT.DEPOSIT_$$$$$$$.BAL_LEDGER_CURRENT, INFORMENT.PRODUCT_OFFER_PURCHASE.INTEREST_RATE, To_Char(INFORMENT.PRODUCT_OFFER_PURCHASE.DATE_OPEN, 'YYYYMMDD'), To_Char(INFORMENT.PRODUCT_OFFER_PURCHASE.DATE_CLOSE, 'YYYYMMDD'), INFORMENT.PRODUCT_OFFER_PURCHASE.*******_STATUS_CODE, INFORMENT.PRODUCT_OFFER_PURCHASE.CLIENT_DEFINED
FROM INFORMENT.PRODUCT_OFFER_PURCHASE, INFORMENT.INVOLVED_PARTY, INFORMENT.DEPOSIT_$$$$$$$$$
WHERE INFORMENT.PRODUCT_OFFER_PURCHASE.INVOLVED_PARTY_ID_PRIMARY = INFORMENT.INVOLVED_PARTY.INVOLVED_PARTY_ID
AND INFORMENT.PRODUCT_OFFER_PURCHASE.******** = INFORMENT.INVOLVED_PARTY.*********
AND INFORMENT.PRODUCT_OFFER_PURCHASE.******** = INFORMENT.DEPOSIT_$$$$$$$.********
AND INFORMENT.PRODUCT_OFFER_PURCHASE.APPL_ID = INFORMENT.DEPOSIT_$$$$$$$.APPL_ID
AND INFORMENT.PRODUCT_OFFER_PURCHASE.*******_######## = INFORMENT.DEPOSIT_$$$$$$$$.******_#######
AND INFORMENT.PRODUCT_OFFER_PURCHASE.********* = '020'
and
(INFORMENT.PRODUCT_OFFER_PURCHASE.APPL_ID = 'SAV'
or
INFORMENT.PRODUCT_OFFER_PURCHASE.APPL_ID = 'DDA')
March 13, 2013 at 10:37 am
tn_350z (3/13/2013)
Hopefully, I am posting this in a right area if not I am sorry. Dont mind the $$$$,#### and ****I am trying to get the last 60 days of records from today, and I am having issued writing the query that returns that value....RDMS its tie to SQL/Oracle database and here is my query that is currently working but I just like to see the last 60 days if anyone can possible help!!!
SELECT INFORMENT.PRODUCT_OFFER_PURCHASE.*******_#######, INFORMENT.INVOLVED_PARTY.INVOLVED_PARTY_ID, To_Char(INFORMENT.PRODUCT_OFFER_PURCHASE.DATE_ADDED, 'YYYYMMDD'), INFORMENT.DEPOSIT_$$$$$$$.BAL_LEDGER_CURRENT, INFORMENT.PRODUCT_OFFER_PURCHASE.INTEREST_RATE, To_Char(INFORMENT.PRODUCT_OFFER_PURCHASE.DATE_OPEN, 'YYYYMMDD'), To_Char(INFORMENT.PRODUCT_OFFER_PURCHASE.DATE_CLOSE, 'YYYYMMDD'), INFORMENT.PRODUCT_OFFER_PURCHASE.*******_STATUS_CODE, INFORMENT.PRODUCT_OFFER_PURCHASE.CLIENT_DEFINED
FROM INFORMENT.PRODUCT_OFFER_PURCHASE, INFORMENT.INVOLVED_PARTY, INFORMENT.DEPOSIT_$$$$$$$$$
WHERE INFORMENT.PRODUCT_OFFER_PURCHASE.INVOLVED_PARTY_ID_PRIMARY = INFORMENT.INVOLVED_PARTY.INVOLVED_PARTY_ID
AND INFORMENT.PRODUCT_OFFER_PURCHASE.******** = INFORMENT.INVOLVED_PARTY.*********
AND INFORMENT.PRODUCT_OFFER_PURCHASE.******** = INFORMENT.DEPOSIT_$$$$$$$.********
AND INFORMENT.PRODUCT_OFFER_PURCHASE.APPL_ID = INFORMENT.DEPOSIT_$$$$$$$.APPL_ID
AND INFORMENT.PRODUCT_OFFER_PURCHASE.*******_######## = INFORMENT.DEPOSIT_$$$$$$$$.******_#######
AND INFORMENT.PRODUCT_OFFER_PURCHASE.********* = '020'
and
(INFORMENT.PRODUCT_OFFER_PURCHASE.APPL_ID = 'SAV'
or
INFORMENT.PRODUCT_OFFER_PURCHASE.APPL_ID = 'DDA')
To see the last 60 days you need to determine which column(s) you can filter on. Then it is just Where [YourColumn] > dateadd(DAY, 60, getdate())
Also, you should use the newer join constructs and not the old style joins. Additionally table aliases would make far more legible.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 13, 2013 at 11:41 am
Sean Lange (3/13/2013)
tn_350z (3/13/2013)
Hopefully, I am posting this in a right area if not I am sorry. Dont mind the $$$$,#### and ****I am trying to get the last 60 days of records from today, and I am having issued writing the query that returns that value....RDMS its tie to SQL/Oracle database and here is my query that is currently working but I just like to see the last 60 days if anyone can possible help!!!
SELECT INFORMENT.PRODUCT_OFFER_PURCHASE.*******_#######, INFORMENT.INVOLVED_PARTY.INVOLVED_PARTY_ID, To_Char(INFORMENT.PRODUCT_OFFER_PURCHASE.DATE_ADDED, 'YYYYMMDD'), INFORMENT.DEPOSIT_$$$$$$$.BAL_LEDGER_CURRENT, INFORMENT.PRODUCT_OFFER_PURCHASE.INTEREST_RATE, To_Char(INFORMENT.PRODUCT_OFFER_PURCHASE.DATE_OPEN, 'YYYYMMDD'), To_Char(INFORMENT.PRODUCT_OFFER_PURCHASE.DATE_CLOSE, 'YYYYMMDD'), INFORMENT.PRODUCT_OFFER_PURCHASE.*******_STATUS_CODE, INFORMENT.PRODUCT_OFFER_PURCHASE.CLIENT_DEFINED
FROM INFORMENT.PRODUCT_OFFER_PURCHASE, INFORMENT.INVOLVED_PARTY, INFORMENT.DEPOSIT_$$$$$$$$$
WHERE INFORMENT.PRODUCT_OFFER_PURCHASE.INVOLVED_PARTY_ID_PRIMARY = INFORMENT.INVOLVED_PARTY.INVOLVED_PARTY_ID
AND INFORMENT.PRODUCT_OFFER_PURCHASE.******** = INFORMENT.INVOLVED_PARTY.*********
AND INFORMENT.PRODUCT_OFFER_PURCHASE.******** = INFORMENT.DEPOSIT_$$$$$$$.********
AND INFORMENT.PRODUCT_OFFER_PURCHASE.APPL_ID = INFORMENT.DEPOSIT_$$$$$$$.APPL_ID
AND INFORMENT.PRODUCT_OFFER_PURCHASE.*******_######## = INFORMENT.DEPOSIT_$$$$$$$$.******_#######
AND INFORMENT.PRODUCT_OFFER_PURCHASE.********* = '020'
and
(INFORMENT.PRODUCT_OFFER_PURCHASE.APPL_ID = 'SAV'
or
INFORMENT.PRODUCT_OFFER_PURCHASE.APPL_ID = 'DDA')
To see the last 60 days you need to determine which column(s) you can filter on. Then it is just Where [YourColumn] > dateadd(DAY, 60, getdate())
Also, you should use the newer join constructs and not the old style joins. Additionally table aliases would make far more legible.
Shouldn't the dateadd look like this to go back 60 days, DATEADD(DAY, -60, GETDATE())?
March 13, 2013 at 12:25 pm
Lynn Pettis (3/13/2013)
Sean Lange (3/13/2013)
tn_350z (3/13/2013)
Hopefully, I am posting this in a right area if not I am sorry. Dont mind the $$$$,#### and ****I am trying to get the last 60 days of records from today, and I am having issued writing the query that returns that value....RDMS its tie to SQL/Oracle database and here is my query that is currently working but I just like to see the last 60 days if anyone can possible help!!!
SELECT INFORMENT.PRODUCT_OFFER_PURCHASE.*******_#######, INFORMENT.INVOLVED_PARTY.INVOLVED_PARTY_ID, To_Char(INFORMENT.PRODUCT_OFFER_PURCHASE.DATE_ADDED, 'YYYYMMDD'), INFORMENT.DEPOSIT_$$$$$$$.BAL_LEDGER_CURRENT, INFORMENT.PRODUCT_OFFER_PURCHASE.INTEREST_RATE, To_Char(INFORMENT.PRODUCT_OFFER_PURCHASE.DATE_OPEN, 'YYYYMMDD'), To_Char(INFORMENT.PRODUCT_OFFER_PURCHASE.DATE_CLOSE, 'YYYYMMDD'), INFORMENT.PRODUCT_OFFER_PURCHASE.*******_STATUS_CODE, INFORMENT.PRODUCT_OFFER_PURCHASE.CLIENT_DEFINED
FROM INFORMENT.PRODUCT_OFFER_PURCHASE, INFORMENT.INVOLVED_PARTY, INFORMENT.DEPOSIT_$$$$$$$$$
WHERE INFORMENT.PRODUCT_OFFER_PURCHASE.INVOLVED_PARTY_ID_PRIMARY = INFORMENT.INVOLVED_PARTY.INVOLVED_PARTY_ID
AND INFORMENT.PRODUCT_OFFER_PURCHASE.******** = INFORMENT.INVOLVED_PARTY.*********
AND INFORMENT.PRODUCT_OFFER_PURCHASE.******** = INFORMENT.DEPOSIT_$$$$$$$.********
AND INFORMENT.PRODUCT_OFFER_PURCHASE.APPL_ID = INFORMENT.DEPOSIT_$$$$$$$.APPL_ID
AND INFORMENT.PRODUCT_OFFER_PURCHASE.*******_######## = INFORMENT.DEPOSIT_$$$$$$$$.******_#######
AND INFORMENT.PRODUCT_OFFER_PURCHASE.********* = '020'
and
(INFORMENT.PRODUCT_OFFER_PURCHASE.APPL_ID = 'SAV'
or
INFORMENT.PRODUCT_OFFER_PURCHASE.APPL_ID = 'DDA')
To see the last 60 days you need to determine which column(s) you can filter on. Then it is just Where [YourColumn] > dateadd(DAY, 60, getdate())
Also, you should use the newer join constructs and not the old style joins. Additionally table aliases would make far more legible.
Shouldn't the dateadd look like this to go back 60 days, DATEADD(DAY, -60, GETDATE())?
Doh. Thanks again for the correction Lynn.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply