Display last 12 weekdays data

[sushmitha]

How to get the previous 12days (only weekdays.Exclude weekends)
by selecting a date using query group by each day

 

[ByteMyzer]

Try something like the following (substitute highlighted table and field names as appropriate):

SELECT T1.*
FROM [b][i]MyTable[/i][/b] T1
INNER JOIN
  (
    SELECT TOP 12 T2.[b][i]MyDateField[/i][/b]
    FROM [b][i]MyTable[/i][/b] T2
    WHERE Weekday(T2.[b][i]MyDateField[/i][/b]) NOT IN (1,7)
    ORDER BY T2.[b][i]MyDateField[/i][/b] DESC
  ) T2 ON T1.[b][i]MyDateField[/i][/b] = T2.[b][i]MyDateField[/i][/b];

 

[sushmitha]

Try something like the following (substitute highlighted table and field names as appropriate):

SELECT T1.*
FROM [B][I]MyTable[/I][/B] T1
INNER JOIN
  (
    SELECT TOP 12 T2.[B][I]MyDateField[/I][/B]
    FROM [B][I]MyTable[/I][/B] T2
    WHERE Weekday(T2.[B][I]MyDateField[/I][/B]) NOT IN (1,7)
    ORDER BY T2.[B][I]MyDateField[/I][/B] DESC
  ) T2 ON T1.[B][I]MyDateField[/I][/B] = T2.[B][I]MyDateField[/I][/B];

The above query is not showing correct results. Also I have date field in only one table. I need to get the previous 12 days excluding holidays

I think we can use weekday function. But I am not getting the logic to use it

 

[ByteMyzer]

I am also only using one table. I am simply using two instances of the same table within a single query. The first instance is to display all of the fields from the table. The second instance is used in an inline subquery to filter the dates selected to the 12 most recent, excluding Saturdays and Sundays.

Can you post an example from your database, the table data and the query results so that we can see how the results are not correct?

 

[sushmitha]

I am also only using one table. I am simply using two instances of the same table within a single query. The first instance is to display all of the fields from the table. The second instance is used in an inline subquery to filter the dates selected to the 12 most recent, excluding Saturdays and Sundays.

Can you post an example from your database, the table data and the query results so that we can see how the results are not correct?

I cannot use inline query in my situation. I need to write a single sql query as I need to display in Business Objects report

 

[MSAccessRookie]

I am also only using one table. I am simply using two instances of the same table within a single query. The first instance is to display all of the fields from the table. The second instance is used in an inline subquery to filter the dates selected to the 12 most recent, excluding Saturdays and Sundays.

Can you post an example from your database, the table data and the query results so that we can see how the results are not correct?

Also, the query that you are running please Because I'm sure that you must have had to modify his to make it work for you.

 

[ByteMyzer]

I cannot use inline query in my situation. I need to write a single sql query as I need to display in Business Objects report

The query I provided is a single SQL query statement. It happens to incorporate an inline subquery, but it is still a single SQL statement. You are working with a Microsoft Access Database, yes?

 

[sushmitha]

Also, the query that you are running please Because I'm sure that you must have had to modify his to make it work for you.

Here is my query
SELECT *
FROM tbl_Log AS T1 INNER JOIN [SELECT TOP 12 tbl_Log.cdate FROM tbl_Log T2
WHERE Weekday(T2.cdate) NOT IN (1,7)
ORDER BY T2.cdate DESC
]. AS T2 ON T1.cdate = T2.cdate;

 

[MSAccessRookie]

Here is my query
SELECT *
FROM tbl_Log AS T1 INNER JOIN [SELECT TOP 12 tbl_Log.cdate FROM tbl_Log T2
WHERE Weekday(T2.cdate) NOT IN (1,7)
ORDER BY T2.cdate DESC
]. AS T2 ON T1.cdate = T2.cdate;

I modified it to work with my transactions table, and it works fine with the exception that it returned all records from the top 12 dates, not the top 12 records.

SELECT *
FROM tblTransactions AS T1 INNER JOIN [SELECT TOP 12 T2.TRdate FROM tblTransactions T2
WHERE Weekday(T2.TRdate) NOT IN (1,7)
ORDER BY T2.TRdate DESC
]. AS T2 ON T1.TRdate = T2.TRdate;

 

[ByteMyzer]

I modified it to work with my transactions table, and it works fine with the exception that it returned all records from the top 12 dates, not the top 12 records.

That's what sushmitha asked for, the top 12 days:

How to get the previous 12days (only weekdays.Exclude weekends)
by selecting a date using query group by each day

sushmitha, the query looks right. Can you also post a sample of the results you are getting, and of the source table data?

 

[boblarson]

I need to get the previous 12 days excluding holidays

I don't think any of the code here will exclude holidays. If you need that to occur as well, you are going to need a table of holidays to also exclude against (correct me if I'm wrong).

 

[ByteMyzer]

I don't think any of the code here will exclude holidays. If you need that to occur as well, you are going to need a table of holidays to also exclude against (correct me if I'm wrong).

No, you are quite correct. You need a separate table with dates to be excluded from the search. In this case, those dates would need to include Saturdays, Sundays and Holidays.

A variation on my original query might use something like the following for this scenario:

SELECT T1.*
FROM [b][i]MyTable[/i][/b] T1
INNER JOIN
 (
  SELECT TOP 12 T2.[b][i]MyDateField[/i][/b]
  FROM [b][i]MyTable[/i][/b] T2
  WHERE T2.[b][i]MyDateField[/i][/b] NOT IN
   (SELECT T3.[b][i]ExcludeDate[/i][/b]
    FROM [b][i]MyExcludeTable[/i][/b] T3
   )
  ORDER BY T2.[b][i]MyDateField[/i][/b] DESC
 ) T2 ON T1.[b][i]MyDateField[/i][/b] = T2.[b][i]MyDateField[/i][/b];

 

[sushmitha]

That's what sushmitha asked for, the top 12 days:


sushmitha, the query looks right. Can you also post a sample of the results you are getting, and of the source table data?

The query is not giving me previous 12 days data.

Please find the attached Datevalues in table and query results.

When I enter 11/7/2008, query should give me the previous 12 days data excluding weekends

Results should include 11/3/2008, 11/4/2008 which are available in table
The current query is not doing that

 

[ByteMyzer]

See my answer under duplicate thread: http://www.access-programmers.co.uk/forums/showthread.php?p=778421#post778421