SQL Server - Last Full Week Data - Asked By Adam Clark on 05-Nov-08 10:45 AM

I am trying to create a dynamic query that allows me to pull only the data from the last full week, Sunday through Saturday. I want it to pull from last weeks Sunday through Saturday and then on the next sunday roll over to capture the previous week from there. Thanks for any help.

SQL Code to Retrieve last week's data - Binny ch replied to Adam Clark on 05-Nov-08 11:57 AM

The best way to handle this is with a calendar table.  Barring that you could use something like the code below, but you need to understand the issues involved first.

The code below works, but it is based on an assumption that may not be true in all cases.  Note the commented out command "SET DATEFIRST 7".
That is the default for U.S. English.  If it is set to something else
the code does not work.  You can SELECT @@DATEFIRST to see the current
value.

Anyway, the example below runs through a set of dates and shows you the day of week and date of the input data, and the day of week and date of the calculated Monday of the previous week.  Getting to Sunday of the previous week is just a matter of adding six days to that.
set nocount on
--SET DATEFIRST 7

declare @d datetime
set @d = dateadd(day,+7,getdate())

while @d > (getdate() - 3)
BEGIN
SELECT datename(weekday,@d), @d,
datename(weekday,dateadd(week, datediff(week, 0,
dateadd(day,-8,@d)), 0)),
dateadd(week, datediff(week, 0, dateadd(day,-8,@d)), 0)
Set @d = dateadd(day,-1,@d)
END

Retrieve Last Week Data - Shailendrasinh Parmar replied to Adam Clark on 05-Nov-08 11:46 PM

try this code - C_A P replied to Adam Clark on 06-Nov-08 07:34 AM

---------------------------------------------------------------------------------
--Database Backups for all databases For Previous Week
---------------------------------------------------------------------------------
SELECT 
   
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
   
msdb.dbo.backupset.database_name
   
msdb.dbo.backupset.backup_start_date
   
msdb.dbo.backupset.backup_finish_date,
   
msdb.dbo.backupset.expiration_date,
   
CASE msdb..backupset.type 
       
WHEN 'D' THEN 'Database' 
       
WHEN 'L' THEN 'Log' 
   
END AS backup_type
   
msdb.dbo.backupset.backup_size
   
msdb.dbo.backupmediafamily.logical_device_name
   
msdb.dbo.backupmediafamily.physical_device_name,  
   
msdb.dbo.backupset.name AS backupset_name,
   
msdb.dbo.backupset.description
FROM   msdb.dbo.backupmediafamily 
   
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id msdb.dbo.backupset.media_set_id 
WHERE  (CONVERT(datetimemsdb.dbo.backupset.backup_start_date102) >= GETDATE() - 7
ORDER BY 
   
msdb.dbo.backupset.database_name,
   
msdb.dbo.backupset.backup_finish_date

source from

http://www.mssqltips.com/tip.asp?tip=1601