Extraction of Data based on Date

[#1]

Hello All,

Kinldy help me on below issue.

If my Extraction Date is 8th or greater than 8th of any month then the data that will be extracted will be from 1st of that month till Date.

For Eg,

Extraction Date = 15-FEB-2013 Then the data that will be extracted will be from 01-FEB-2013 to 15-FEB-2013

If my Extraction Date is less than 8th of any month then my data that will be extracted will be for previous month.

For Eg,

Extraction Date = 05-FEB-2013 Then the data that will be extracted will be from 01-JAN-2013 to 01-FEB-2013.

Kindly suggest.

Thanks in Advance

Sumeet

[tresesco]

May be something like this?

Let vDateFlag=If(day(today())>7, 1,2);
Let vDateExtFlag= If( $(vDateFlag)=2,'Inmonth(Date, Addmonths(Today(),-1),0)', 'InMonthToDate(Date,today(),0)');

Load * Inline [
Date, Amt
1-11-2013, 1
1-12-2013, 1
2-12-2014, 1
1-1-2014, 1
2-1-2014, 1
3-1-2014, 1
1-3-2014, 1
1-4-2014, 1

]
Where $(vDateExtFlag);

[Sokkorn · Accepted solution]

Hi Sumeet,

Here is the script to get start

If Day(Today())>=8 Then
  Let vFromDate = Date(MonthStart(Today()),'DD-MMM-YYYY');
  Let vToDate = Date(Today(),'DD-MMM-YYYY');
  SQL Select * from TableName Where DateField Between '$(vFromDate)' and '$(vToDate)';
Else
  Let vFromDate = Date(MonthStart(AddMonths(Today(),-1)),'DD-MMM-YYYY');
  Let vToDate = Date(MonthStart(Today()),'DD-MMM-YYYY');
  SQL Select * from TableName Where DateField Between '$(vFromDate)' and '$(vToDate)';
End If

Regards,

Sokkorn

[#4]

Thanks a lottttt Sokkorn and Tresesco....