Week begining and ending of a month

[sushmitha]

I have a date field in my table in format m/dd/yyyy hh:mm:ss AM.

Depending the date selected by user, I need to display the corresponding month's data like previous 4 weeks

If the month begining falls in weekday other than Monday, It should give the whole month begining week's data

For Ex: Aug 01 2008 is Friday.
When user runs report he/she see data for the whole Aug 2008 and also the week ending Aug 01 2008 which includes 07/28, 07/29,7/30,7/31.

On the whole one should see the data asof every weekend for that particular month including month begining falls in previous month's weekend

I tried a query with switch statement but it is not working with Business Objects.

Please advise
This is my urgent requirement

 

[sushmitha]

Here is the query I am using to get this done.

I have a date field in my table in format m/dd/yyyy hh:mm:ss AM.

Depending the date selected by user, I need to display the corresponding month's data like previous 4 weeks

If the month begining falls in weekday other than Monday, It should give the whole month begining week's data

For Ex: Aug 01 2008 is Friday.
When user runs report he/she see data for the whole Aug 2008 and also the week ending Aug 01 2008 which includes 07/28, 07/29,7/30,7/31.

On the whole one should see the data asof every weekend for that particular month including month begining falls in previous month's weekend

I tried a query with switch statement but it is not working with Business Objects.

Please advise
This is my urgent requirement

Here is the query I am using at Acces which is working fine.But Dateadd function is giving me error when I use this in Business Objects

Can anybody help me the other workaround for this ?/

SELECT X.week AS week, sum(X.TotalCalls) AS [sum], X.vgroup AS groupname
FROM [select tbl_ATTLog_Master.vDate, tbl_ATTLog_Master.TotalCalls, tbl_ATTLog_Master.vgroup,
switch(tbl_ATTLog_Master.vDate<=
dateadd("d",6,dateadd("d",-weekday(DateSerial(year(date()), month(date()), 1)), DateSerial(year(date()), month(date()), 1)))
and
tbl_ATTLog_Master.vDate >=
dateadd("d",-weekday(DateSerial(year(date()), month(date()), 1)), DateSerial(year(date()), month(date()), 1)) , dateadd("d",-weekday(DateSerial(year(date()), month(date()), 1)), DateSerial(year(date()), month(date()), 1)),
tbl_ATTLog_Master.vDate>
dateadd("d",6,dateadd("d",-weekday(DateSerial(year(date()), month(date()), 1)), DateSerial(year(date()), month(date()), 1)))
and
tbl_ATTLog_Master.vDate <=
dateadd("d",13,dateadd("d",-weekday(DateSerial(year(date()), month(date()), 1)), DateSerial(year(date()), month(date()), 1)))
, dateadd("d",13,dateadd("d",-weekday(DateSerial(year(date()), month(date()), 1)), DateSerial(year(date()), month(date()), 1))),
tbl_ATTLog_Master.vDate>
dateadd("d",13,dateadd("d",-weekday(DateSerial(year(date()), month(date()), 1)), DateSerial(year(date()), month(date()), 1)))
and
tbl_ATTLog_Master.vDate <=
dateadd("d",20,dateadd("d",-weekday(DateSerial(year(date()), month(date()), 1)), DateSerial(year(date()), month(date()), 1)))
, dateadd("d",20,dateadd("d",-weekday(DateSerial(year(date()), month(date()), 1)), DateSerial(year(date()), month(date()), 1))) ,
tbl_ATTLog_Master.vDate>
dateadd("d",20,dateadd("d",-weekday(DateSerial(year(date()), month(date()), 1)), DateSerial(year(date()), month(date()), 1)))
and
tbl_ATTLog_Master.vDate <=
dateadd("d",27,dateadd("d",-weekday(DateSerial(year(date()), month(date()), 1)), DateSerial(year(date()), month(date()), 1)))
,dateadd("d",27,dateadd("d",-weekday(DateSerial(year(date()), month(date()), 1)), DateSerial(year(date()), month(date()), 1))) ,
tbl_ATTLog_Master.vDate>
dateadd("d",27,dateadd("d",-weekday(DateSerial(year(date()), month(date()), 1)), DateSerial(year(date()), month(date()), 1)))
and
tbl_ATTLog_Master.vDate <=
dateadd("d",34,dateadd("d",-weekday(DateSerial(year(date()), month(date()), 1)), DateSerial(year(date()), month(date()), 1)))
, dateadd("d",34,dateadd("d",-weekday(DateSerial(year(date()), month(date()), 1)), DateSerial(year(date()), month(date()), 1)))
) as week
from
tbl_ATTLog_Master
where
tbl_ATTLog_Master.vDate >= dateadd("d",-weekday(DateSerial(year(date()), month(date()), 1)), DateSerial(year(date()),
month(date()), 1)) and
tbl_ATTLog_Master.vDate <=
dateadd("d",34,dateadd("d",-weekday(DateSerial(year(date()), month(date()), 1)), DateSerial(year(date()), month(date()), 1)))
]. AS X
GROUP BY X.week, X.vgroup;

 

[khawar]

Download the attached sample and open query sample query

In the parameter enter any date of august 2008 or july 2008 it will select the dates from table including the dates of first week from previous month as required by you

 

[sushmitha]

Download the attached sample and open query sample query

In the parameter enter any date of august 2008 or july 2008 it will select the dates from table including the dates of first week from previous month as required by you

Hey..This is working fine. But I need to see data groupby each weekend in that criteria.

For Ex: If I enter date 7/25/2008, Total should be group by each weekend 7/4, 7/11, 7/18, 7/25 and display these dates in column headings

Also If I enter 7/28, it should give me data only upto 7/25 with column headings all weekends

Can you let me know how to do these ?? This is the first time I am working with access

 

[khawar]

You can manipulate the above sql to do this easily
To group by dates use a group by query based on current query(crosstab)

 

[sushmitha]

Not working

You can manipulate the above sql to do this easily
To group by dates use a group by query based on current query(crosstab)

I tried working on Crosstab query. It is not showing data by weekend.
Can you help in getting this.

I tried all the options today. May be I am not great in analysis