August 22, 2014 at 2:48 am
Hi,
Iām hoping someone can help.
I have created a report where the current date needs to be 2 months greater than the due date. However I am stuck on the bit for the current date section of the report. Could someone please help me with the code to extract data where the current date is 2 months greater than the due date?
Many thanks for the help in advance.
Kind Regards,
R
August 22, 2014 at 3:45 am
Does the data come from a database (do you use a SQL query)?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 22, 2014 at 4:01 am
Hi,
The due date is coming from the database. So the report needs to run based on the day the report is run. For example if I run the report today the results should show me all the transactions where the current date is 2 months greater than the due date that is stored in the database. Hope this helps.
Kind Regards,
R
August 22, 2014 at 4:14 am
rkl1v09 (8/22/2014)
Hi,The due date is coming from the database. So the report needs to run based on the day the report is run. For example if I run the report today the results should show me all the transactions where the current date is 2 months greater than the due date that is stored in the database. Hope this helps.
Kind Regards,
R
Can you post the SQL query? We cannot see your screen or read your mind, so we need something to work with š
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 22, 2014 at 4:23 am
Hi,
The code is
SELECT h.apar_name,a.ext_inv_ref,a.currency,SUM(a.rest_amount) AS Outstanding,a.account,a.trans_date, a.due_date,a.rem_level,a.remind_date, a.voucher_no, a.apar_id, h.apar_gr_id,DATEDIFF(MONTH,a.trans_date,a.due_date) AS MONTHS, a.status, a.pay_flag, a.dc_flag
FROM acutrans a
INNER JOIN acuheader h ON a.apar_id=h.apar_id AND a.client=h.client
WHERE DATEDIFF(MONTH,a.trans_date,a.due_date) >= 2
AND h.apar_gr_id = 'HA'
AND a.rem_level IN ('0','1')
AND a.account = 8500
AND a.apar_id IN (select apar_id from uvicustbal WHERE Balance != 0.00)
AND a.status != 'P'
AND a.client = 'RU'
GROUP BY h.apar_name,a.ext_inv_ref,a.currency,a.account,a.trans_date, a.due_date,a.rem_level,a.remind_date, a.voucher_no, a.apar_id, h.apar_gr_id, a.status, a.pay_flag, a.dc_flag
UNION ALL
SELECT h.apar_name,a.ext_inv_ref,a.currency,SUM(a.rest_amount) AS Outstanding,a.account,a.trans_date, a.due_date,a.rem_level,a.remind_date, a.voucher_no, a.apar_id, h.apar_gr_id,DATEDIFF(MONTH,a.trans_date,a.due_date) AS MONTHS, a.status, a.pay_flag, a.dc_flag
FROM acutrans a
INNER JOIN acuheader h ON a.apar_id=h.apar_id AND a.client=h.client
WHERE DATEDIFF(MONTH,a.trans_date,a.due_date) >= 2
AND h.apar_gr_id != 'HU'
AND a.rem_level IN ('0','1','2')
AND a.account = 8500
AND a.apar_id IN (select apar_id from uvicustbal WHERE Balance != 0.00)
AND a.status != 'P'
AND a.client = 'RU'
GROUP BY h.apar_name,a.ext_inv_ref,a.currency,a.account,a.trans_date, a.due_date,a.rem_level,a.remind_date, a.voucher_no, a.apar_id, h.apar_gr_id, a.status, a.pay_flag, a.dc_flag
ORDER BY rem_level, voucher_no
The where clause that I have used isn't correct. And that is the bit that I am stuck on.
Kind Regards,
R
August 22, 2014 at 4:42 am
Important questions. What data type is DueDate? DATE or DATETIME / SMALLDATETIME?
If a DATETIME variety, do you need everything from midnight on or are you looking for literally 2 months (including hours)?
FYI: I think you're looking for the DATEADD function, not DATEDIFF. But I can give you a more exact answer (in code) once you answer my questions.
August 22, 2014 at 7:31 am
Hi Brandie,
Due date is DATETIME and i am literally just looking for 2 months data.
Kind Regards,
R
August 22, 2014 at 7:36 am
rkl1v09 (8/22/2014)
The where clause that I have used isn't correct. And that is the bit that I am stuck on.
In what sense is it not correct?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 22, 2014 at 7:38 am
rkl1v09 (8/22/2014)
Hi Brandie,Due date is DATETIME and i am literally just looking for 2 months data.
Kind Regards,
R
Then instead of
WHERE DATEDIFF(MONTH,a.trans_date,a.due_date) >= 2
Try
WHERE a.due_date >= DATEADD(m,-2,CONVERT(DATE,GETDATE(),101))
This gives you every date in the last two months. If you want every date before the last two months, just switch the greater than sign to a less than sign.
August 22, 2014 at 7:44 am
Hi Brandie,
Thank you so much for that. Really appreciate your help š
Kind Regards,
R
August 22, 2014 at 7:46 am
You are quite welcome.
EDIT: It occurs to me that you might want to substitute "trandate" for GETDATE().
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply