Last Backup

SELECT DISTINCT     T1.name,  T3.user_name
,       T3.name  AS BACKUP_NAME
,       (DATEDIFF( SS,  T3.backup_start_date , T3.backup_finish_date )) AS DURATION
,       T3.backup_start_date
,       T3.backup_finish_date
,       T3.type AS [TYPE]
,     T3.backup_size /1048576.0 BackupSizeMB
,     T6.physical_device_name
, cast((T3.backup_size /1048576.0) /  (DATEDIFF( SS,  T3.backup_start_date , T3.backup_finish_date )+ 1 ) as decimal(10,2)) AS MBPS
FROM master..sysdatabases T1
INNER JOIN msdb..backupset T3 ON (T3.database_name = T1.name  )
LEFT OUTER JOIN msdb..backupmediaset  T5 ON ( T3.media_set_id  = T5.media_set_id  )
LEFT OUTER JOIN msdb..backupmediafamily  T6 ON ( T6.media_set_id  = T5.media_set_id )
WHERE
DATABASEPROPERTYEX(T1.name,'STATUS') = 'ONLINE' AND  (DATEDIFF( SS,  T3.backup_start_date , T3.backup_finish_date )) > 0 and
type = 'D'
--T3.backup_finish_date in (select max(backup_finish_date)
--from msdb..backupset where [type] = 'D' or [type] = 'L' or [type]='I'
--group by database_name, [type])


DECLARE @DateReport int
SET @DateReport = 5

SELECT distinct A.name DBName, A.Dates Date, ISNULL(B.LastBackUpTaken, 'No Backup') Status
FROM (SELECT m.name, CONVERT(VARCHAR(8),DATEADD(DD,-number,GETDATE()),112) Dates
from master..spt_values cross join master..sysdatabases m
WHERE type = 'P' and number < @DateReport and number > 0 AND DATABASEPROPERTYEX(m.name,'STATUS') = 'ONLINE') A
LEFT JOIN (SELECT
T1.Name as DatabaseName,
COALESCE(Convert(varchar(8), (T2.backup_finish_date), 112),'Not Yet Taken') as LastBackUpTaken
FROM master..sysdatabases T1 LEFT OUTER JOIN msdb.dbo.backupset T2
ON T2.database_name = T1.name
WHERE T2.backup_finish_date > DATEADD(DD,-@DateReport,GETDATE()) AND T2.type = 'D' AND DATABASEPROPERTYEX(T1.name,'STATUS') = 'ONLINE' ) B ON A.Dates = B.LastBackUpTaken and A.name = B.DatabaseName
WHERE B.LastBackUpTaken IS NULL AND @@SERVERNAME NOT LIKE 'HQFKRNDB' and @@SERVERNAME NOT LIKE '%JBBWIMDBP37%' AND A.name not like 'KanaResponse_Reporting' and A.name not like 'tempdb'
order by Dates desc