September 15, 2011 at 9:11 am
Hi Experts
Can some one please help me in this scenario new to MS T-sql (MS 2008) (No PK Keys dont ask why)
Detail Table Backup
DBName Finshdate Status
Test1 08/02/2011 Good
Test2 08/02/2011 Good
Test1 08/03/2011 Good
Test2 08/03/2011 Good
Test4 08/04/2011 Good
Test1 08/05/2011 Good
Database Table
Name
Test1
Test2
Test3
Test4
Sample Query to find all Database Backup status for a Date Range
Start Date 08/01/2011
End Date 08/07/2011
Output Should be
Database Date Status
Test1 08/01/2011 None
Test2 08/01/2011 None
Test3 08/01/2011 None
Test4 08/01/2011 None
Test1 08/02/2011 Good
Test2 08/02/2011 Good
Test3 08/02/2011 None
Test4 08/02/2011 None
..etc
Test1 08/07/2011 None
Test2 08/07/2011 None
Test3 08/07/2011 None
Test4 08/07/2011 None
Please Help
how will i Achive this with a Query Sample Code Please
Thanks
September 15, 2011 at 9:38 am
Like this?
DECLARE @StartDate DATETIME
, @EndDate DATETIME;
SELECT @StartDate = '08/01/2011'
,@EndDate = '08/07/2011'
IF OBJECT_ID('TempDB..#Database_Table') IS NOT NULL
DROP TABLE #Database_Table
CREATE TABLE #Database_Table
( TestID VARCHAR(25))
INSERT INTO #Database_Table ( TestID )
SELECT 'Test1'
UNION ALL SELECT 'Test2'
UNION ALL SELECT 'Test3'
UNION ALL SELECT 'Test4'
IF OBJECT_ID('TempDB..#TestsAndDates') IS NOT NULL
DROP TABLE #TestsAndDates
;WITH Tens (N) AS
(
SELECT 0 N UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0
),
Thousands (N) AS
(
SELECT t1.N FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3
),
Numbers AS
(
-- Numbers tables ; contain numbers from 1 to 1000
SELECT 0 N
UNION ALL
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) N FROM Thousands
)
, Dates AS
(
SELECT Dates = DATEADD(DD , N , @StartDate )
FROM Numbers
WHERE N <= DATEDIFF(DD,@StartDate , @EndDate)
)
SELECT A.TestID, B.Dates
INTO #TestsAndDates
FROM #Database_Table A
CROSS JOIN Dates B
CREATE CLUSTERED INDEX [IX_#TestsAndDates_AllCols] ON #TestsAndDates ( Dates ASC , TestID ASC )
IF OBJECT_ID('TempDB..#Detail_Table_Backup') IS NOT NULL
DROP TABLE #Detail_Table_Backup
--== Sample data
CREATE TABLE #Detail_Table_Backup
( DBName VARCHAR(25) , Finshdate DATETIME, Status VARCHAR(10)) ;
--== Added to optimize the query
CREATE NONCLUSTERED INDEX NIX_#Detail_Table_Backup_MainCols
ON #Detail_Table_Backup ( Finshdate ASC , DBName ASC )
INCLUDE ( Status ) ;
INSERT INTO #Detail_Table_Backup (DBName,Finshdate,Status)
SELECT 'Test1', '08/02/2011' ,'Good'
UNION ALL SELECT 'Test2', '08/02/2011' ,'Good'
UNION ALL SELECT 'Test1', '08/03/2011' ,'Good'
UNION ALL SELECT 'Test2', '08/03/2011' ,'Good'
UNION ALL SELECT 'Test4', '08/04/2011' ,'Good'
UNION ALL SELECT 'Test1', '08/05/2011' ,'Good';
--== Final Query
SELECT A.Dates , A.TestID , ISNULL (B.Status , 'None') Status
FROM #TestsAndDates A
LEFT JOIN #Detail_Table_Backup B
ON A.Dates = B.Finshdate
AND A.TestID = B.DBName
September 15, 2011 at 1:25 pm
Thanks Coldcoffe
I will try your solution will give some ideas. Actually, i am looking for a Query for a date range if any Database Backup where done or not or Backup missed in that Date Range. I have Bk Infor in Detail Table Backup
Database Finshdate Status
Test1 08/02/2011 Good
Test2 08/02/2011 Good
Test1 08/03/2011 Good
Test2 08/03/2011 Good
Test4 08/04/2011 Good
Test1 08/05/2011 Good
Database Table
Name
Test1
Test2
Test3
Test4
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply