Show only Previous Month's data in SQL

Question

I have been told to create a query that will show the top 10 most used applications in our company, and on the 11th row should group all other rows into it and call the record "Other" and sum all the sessions together.

How do I modify this code to only show monthly records?

Code Snippet

SELECT TOP 10 dbo_LU_APPNAME.APPNAME, Count(*) AS SessionNos
FROM dbo_LU_APPNAME INNER JOIN dbo_SDB_SESSION ON dbo_LU_APPNAME.PK_APPNAMEID =  
                                                  dbo_SDB_SESSION.FK_APPNAMEID
GROUP BY dbo_LU_APPNAME.APPNAME
ORDER BY Count(*) DESC;

UNION ALL SELECT "Other" AS APPNAME, Count(*) AS SessionNos 
FROM (dbo_LU_APPNAME 
INNER JOIN dbo_SDB_SESSION  
ON dbo_LU_APPNAME.PK_APPNAMEID = dbo_SDB_SESSION.FK_APPNAMEID)  
LEFT JOIN (SELECT TOP 10 dbo_LU_APPNAME.APPNAME, Count(*) AS SessionNos 
           FROM dbo_LU_APPNAME  
           INNER JOIN dbo_SDB_SESSION 
           ON dbo_LU_APPNAME.PK_APPNAMEID = dbo_SDB_SESSION.FK_APPNAMEID 
           GROUP BY dbo_LU_APPNAME.APPNAME
           ORDER BY Count(*) DESC) AS s  ON dbo_LU_APPNAME.APPNAME = s.APPNAME

WHERE s.APPNAME Is Null
GROUP BY "Other";

The dbo_SDB_SESSION table has many fields and from those we will need to use: - SESSIONSTART - SESSIONEND

The code could be something like this:

WHERE (((dbo_SDB_SESSION.SESSIONSTART) Between Now() And DateAdd("d",-30,Now())))

Question

How do I modify the above code to only show the previous month's data?

This is an SQL View into Access 2007.

Answer 1

As long as you are always in the next month and always just want to extract the previous month then:

SELECT Table1.Dated, Table1.Text, Month([Dated]) AS DatedMonth
FROM Table1
WHERE (((Month([Dated]))=Month(Now())-1));

Comparing month to month - as this is a function your performance may vary.

Answer 2

I am worried that you are creating "dead code walking." That is, while you can do amazing things in SQL, that doesn't always mean you should do so.

Is there any opportunity to move this to a Business Logic Layer or even just the UI (a case could be made for either)? The problem is that your data layer code is being driven to extreme levels of complexity due to what are essentially UI layer requirements.

If you were to pull the data in two stages with clearly named ("readable") procedural code, you'd likely end up with a more maintainable result. If you take the SQL-only route, you may end up with a query that no one will ever want to touch again due to its inherent complexity.

Answer 3

SELECT TOP 10 dbo_LU_APPNAME.APPNAME, Count(*) AS SessionNos
FROM dbo_LU_APPNAME INNER JOIN dbo_SDB_SESSION ON dbo_LU_APPNAME.PK_APPNAMEID = dbo_SDB_SESSION.FK_APPNAMEID
WHERE (((dbo_SDB_SESSION.SESSIONSTART) Between Now() And DateAdd("d",-30,Now())))
GROUP BY dbo_LU_APPNAME.APPNAME
ORDER BY Count(*) DESC;

UNION ALL SELECT "Other" AS APPNAME, Count(*) AS SessionNos 
FROM (dbo_LU_APPNAME
INNER JOIN dbo_SDB_SESSION  
ON dbo_LU_APPNAME.PK_APPNAMEID = dbo_SDB_SESSION.FK_APPNAMEID)  
LEFT JOIN (SELECT TOP 10 dbo_LU_APPNAME.APPNAME, Count(*) AS SessionNos 
           FROM dbo_LU_APPNAME
           INNER JOIN dbo_SDB_SESSION 
           ON dbo_LU_APPNAME.PK_APPNAMEID = dbo_SDB_SESSION.FK_APPNAMEID
           WHERE (((dbo_SDB_SESSION.SESSIONSTART) Between Now() And DateAdd("d",-31,Now())))
           GROUP BY dbo_LU_APPNAME.APPNAME
           ORDER BY Count(*) DESC) AS s  ON dbo_LU_APPNAME.APPNAME = s.APPNAME

WHERE s.APPNAME Is Null
GROUP BY "Other";

I found the solution.

Did a bit of playing around.

Cheers folks :)