June 20, 2011 at 4:20 am
Hi All,
i have below requirement, we are checking only month and date.
If the FYME is < or = payment date then Calculated Report Due Year = payment year + 2 years,
Else Calculated Report Due Year = Payment Year + 1 year(..FYME Means Below Example 8/30. we are checking month and date only, when month and date >=Payment date then add year(paymentdate)+2 else year(paymentdate)+1)
For example if the FYME is 8/30 and a payment was made on 7/31/2011 then the calculated year would be 2011 +1 = 2012
But if a payment for the same grantee was made on 9/1/2011 then the calculated year would be 2011 +2 = 2013
please help me how to do this task.
thanks in advance
June 20, 2011 at 6:05 am
What have you tried so far? I am willing to help you if you can provide the following:
1) DDL and DML to build some sample tables.
2) Expected results.
3) The queries you have tried so far.
If you're not sure what I mean please have a look at this article:
http://qa.sqlservercentral.com/articles/Best+Practices/61537/[/url]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 20, 2011 at 7:40 am
Thanks for your Reply,
Ex Data:
FYME Paymentdate
12/31 2009-07-15 00:00:00.000
8/30 2009-07-15 00:00:00.000
9/30 2008-12-30 00:00:00.000
12/31 2010-01-05 00:00:00.000
06/30 2010-02-04 00:00:00.000
02/28 2009-08-11 00:00:00.000
this is the data like, we dont need to check the Paymentdate year only Month and date, we need to check.
DDL Statement:
select
case when cast(substring(FYME,0,2) as varchar)<= cast(month(PAIDDATE)as varchar) then
cast(cast(FYME as varchar)+'/' +cast(YEAR(PAIDDATE)+2 as varchar) as datetime) else
cast(cast(FYME as varchar)+'/' +cast(YEAR(PAIDDATE)+1 as varchar) as datetime) end as SystemReportDueDate
from Paymentstatus_Dim
I write this state ment but i am compareing only month,actual my requirement is compareing month and date in FYME and Paiddate.
Thanks
June 20, 2011 at 8:06 am
Thank you for attempting to provide what I asked for, however that is quite what I had in mind. Did you read the article? Specifically first section titled "The Wrong Way to Post Data" and "What It Should Look Like When Done". An image attached to the thread is not going to work.
A SELECT statement is not DDL, but thank you for providing it, that at least tells me what you have tried so far.
DDL stands for Data Definition Language and what that means in terms of what we're looking for is a CREATE TABLE statement.
DML stands for Data Modification Language and what that means in terms of what we're looking for is a series of INSERT statements to populate the table you created with your DDL.
Expected results are very important, please post those too.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 20, 2011 at 9:31 am
Let me echo OPC.THREE's request.
What he is asking for are the statements to create sample tables and populate them with sample data. We also need to see exactly what results you would expect to see from the data provided. That way we can cut and paste your exact code and data to set up the problem and offer you coded and tested solutions that produce the expected results. Sending images and descriptions requires others to do all the work of trying to recreate the problem.
Describing your question in general terms is often too imprecise for the volunteers here to be able to help you. By creating the code to create sample tables and data, you save everyone a lot of time messaging back and forth trying to understand your problem. You will find that more people respond faster to your questions, if you take the time to set the problem up. Thanks.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 20, 2011 at 11:54 am
Could try something like changing your CASE clause to:
DATEDIFF(dd, CAST(CAST(YEAR(PAIDDATE) AS VARCHAR(4)) + '/' + FYME AS SMALLDATETIME), PAIDDATE) > 0
But without much more info, can't really help you more than that.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply