CODEWITHSUNDEEP
sqlreporting
Himberjack
Himberjack

Reputation: 5802

SQL query with week days

I would like to know what is the best way of creating a report that will be grouped by the last 7 days - but not every day i have data. for example:

08/01/10 | 0 08/02/10 | 5 08/03/10 | 6 08/04/10 | 10 08/05/10 | 0 08/06/10 | 11 08/07/10 | 1

is the only option is to create a dummy table with those days and join them altogether?

thank you

Upvotes: 2

Views: 1066

Answers (4)

Steve Weet
Steve Weet

Reputation: 28402

Many people will suggest methods for dynamically creating a range of dates that you can then join against. This will certainly work but in my experience a calendar table is the way to go. This will make the SQL trivial and generic at the cost of maintaining the calendar table.

At some point in the future someone will come along and ask for another report that excludes weekends. You then have to make your dynamic days generation account for weekends. Then someone will ask for working-days excluding public-holidays at which point you have no choice but to create a calendar table.

I would suggest you bite the bullet and create a calendar table to join against. Pre-populate it with every date and if you want to think ahead then add columns for "Working Day" and maybe even week number if your company uses a non-standard week-number for reporting

Upvotes: 3

Michael Pakhantsov
Michael Pakhantsov

Reputation: 25390

Try something like this

WITH LastDays (calc_date)
AS
(SELECT DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP) - 6, 0)
UNION ALL
SELECT DATEADD(DAY, 1, calc_date)
FROM LastDays
WHERE DATEADD(DAY, 1, calc_date) < CURRENT_TIMESTAMP)
SELECT ...
FROM LastDays l LEFT JOIN (YourQuery) t ON (l.cal_date = t.YourDateColumn);

Upvotes: 3

HotTester
HotTester

Reputation: 5778

select the last 7 transactions and left join it with your query and then group by the date column. hope this helps.

Upvotes: 0

Tobiasopdenbrouw
Tobiasopdenbrouw

Reputation: 14039

You don't mention the specific language (please do for a more detailed answer), but most versions of sql have a function for the current date (GetDate(), for instance). You could take that date, subtract x (7) days and build your WHERE statement like that.

Then you could GROUP BY the day-part of that date.

Upvotes: 0

Related Questions