Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2007
    Location
    Ohio, USA
    Posts
    142

    Unanswered: Help with three table join query.

    I feel like I'm a join away, here... I have tables for servers, databases, and backups. What I want to know is, what was the the most recent backup date and type for each database?
    Code:
    /* Tables */
    CREATE TABLE [dbo].[Servers](
      [ServerID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_ServerID]  DEFAULT (newid()),
      [ServerName] [nvarchar](60) NOT NULL,
     CONSTRAINT [PK_Servers_ServerID] PRIMARY KEY CLUSTERED ([ServerID] ASC),
     CONSTRAINT [UNIDX_Servers_ServerName] UNIQUE NONCLUSTERED ([ServerName] ASC)
    )
    GO
     
    CREATE TABLE [dbo].[Databases](
      [DatabaseID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_DatabaseID]  DEFAULT (newid()),
      [ServerID] [uniqueidentifier] NOT NULL,
      [DatabaseName] [nvarchar](100) NOT NULL,
      [RecoveryModel] [nvarchar](11) NOT NULL,
      [OwnerUID] [nvarchar](30) NULL,
     CONSTRAINT [PK_Databases_DatabaseID] PRIMARY KEY CLUSTERED ([DatabaseID] ASC),
     CONSTRAINT [UNIDX_Databases_DBName_ServerID] UNIQUE NONCLUSTERED ([DatabaseName] ASC, [ServerID] ASC)
    ) 
    GO
     
    ALTER TABLE [dbo].[Databases]  
    ADD CONSTRAINT [FK_Databases_Servers] FOREIGN KEY([ServerID])
    REFERENCES [dbo].[Servers] ([ServerID])
    GO
     
    CREATE TABLE [dbo].[Backups](
      [BackupID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_BackupID]  DEFAULT (newid()),
      [DatabaseID] [uniqueidentifier] NOT NULL,
      [BackupDate] [datetime] NOT NULL,
      [BackupType] [char](4) NOT NULL,
     CONSTRAINT [PK_Backups_BackupID] PRIMARY KEY CLUSTERED ([BackupID] ASC)
    ) 
    GO
     
    ALTER TABLE [dbo].[Backups]  
    ADD CONSTRAINT [FK_Backups_Databases] FOREIGN KEY ([DatabaseID])
    REFERENCES [dbo].[Databases] ([DatabaseID])
    GO
     
    /* Some Sample Data. */
    INSERT INTO [Servers]
    SELECT 'B50EF4C5-2751-49CF-88D9-1BDAC5F5913B','AEPPROD03' 
    GO
     
    INSERT INTO [Databases]
    SELECT '03FC0DF7-163E-45DC-A837-09B14F3740A4','B50EF4C5-2751-49CF-88D9-1BDAC5F5913B','msdb','SIMPLE','sa' UNION ALL
    SELECT 'DD0757B9-D1BE-4407-ABE0-24625CE65076','B50EF4C5-2751-49CF-88D9-1BDAC5F5913B','model','SIMPLE','sa' UNION ALL
    SELECT 'B9232B6F-3E09-480B-8658-4FE9ED2CC387','B50EF4C5-2751-49CF-88D9-1BDAC5F5913B','master','SIMPLE','sa' UNION ALL
    SELECT '9C5CC23D-72D8-40CC-B1B0-665CD5F27120','B50EF4C5-2751-49CF-88D9-1BDAC5F5913B','pn_RedFlag','FULL','MM04519' UNION ALL
    SELECT '13055420-D791-4802-B75E-ADAB4C022BE7','B50EF4C5-2751-49CF-88D9-1BDAC5F5913B','pn_ProductionData','BULK_LOGGED','MM04519'
    GO
     
    INSERT INTO Backups
    SELECT '94539C6E-C459-42B1-928C-02E94BA2D230','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr  3 2008  2:04AM','Tran' UNION ALL
    SELECT '24CE98B1-C737-46EF-92D2-048309DF192B','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr  2 2008  8:04PM','Tran' UNION ALL
    SELECT '6521DA25-BB6C-4407-964A-09BBEAB8978D','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr  3 2008  8:04AM','Tran' UNION ALL
    SELECT 'B0CB6502-F022-4F46-9994-177BB61E082F','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr  3 2008 12:04PM','Tran' UNION ALL
    SELECT '69C7078B-F5C3-4805-815C-1D3F6450628E','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr  4 2008  6:04AM','Tran' UNION ALL
    SELECT '8C6D591E-8633-409C-8585-231B3C0920FC','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr  2 2008  8:04AM','Tran' UNION ALL
    SELECT 'A7216995-BC83-48CF-B5E6-27EFF2E8A841','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr  3 2008  6:04PM','Tran' UNION ALL
    SELECT 'A2537957-E601-4BCE-A23B-317021219BA0','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr  3 2008  8:04PM','Tran' UNION ALL
    SELECT '4A23B5C6-65A4-483D-8F70-32B2D459769A','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr  3 2008  4:04PM','Tran' UNION ALL
    SELECT '8812B10D-B2F0-48A1-ACEF-451FC1572D18','03FC0DF7-163E-45DC-A837-09B14F3740A4','Apr  4 2008  2:40AM','Full' UNION ALL
    SELECT '60C80D58-5FDC-41E4-A529-4CBA6A04B4EA','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr  3 2008  2:04PM','Tran' UNION ALL
    SELECT 'B7D4F3FF-DDC9-4BD3-8AE8-4CD9C4C31375','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr  4 2008  2:04AM','Tran' UNION ALL
    SELECT '1A2C3054-5563-465C-9DF8-53A154DB4DF1','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr  2 2008 12:04PM','Tran' UNION ALL
    SELECT '90C4F821-3251-482D-B103-566F7AA72CBE','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr  2 2008  6:04PM','Tran' UNION ALL
    SELECT '53F9773E-9F09-44E8-9BC8-6F253D66106C','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr  2 2008  2:04PM','Tran' UNION ALL
    SELECT 'F60C2566-44C4-495E-80BA-76831048451D','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr  3 2008 10:04PM','Tran' UNION ALL
    SELECT 'FBF29499-D4ED-4AB0-BBE8-76FFA3CC6737','03FC0DF7-163E-45DC-A837-09B14F3740A4','Apr  3 2008  2:40AM','Full' UNION ALL
    SELECT '88C1F74E-CB0E-4CEC-9720-8D47558AAC5A','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr  4 2008 12:04AM','Tran' UNION ALL
    SELECT '45DD22A0-16A0-4BEE-942E-8FA27DC52C31','B9232B6F-3E09-480B-8658-4FE9ED2CC387','Apr  4 2008  2:10AM','Full' UNION ALL
    SELECT 'F9F2EEB2-EFAD-4229-994C-9F175269AF76','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr  2 2008 10:04AM','Tran' UNION ALL
    SELECT 'FE754924-E31A-43BC-8BD3-B3CA0EA02633','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr  3 2008 10:04AM','Tran' UNION ALL
    SELECT '8C692625-B071-4A12-A620-B7A4A56AC1B7','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr  3 2008  4:04AM','Tran' UNION ALL
    SELECT '62FBC73D-49C3-4572-A017-C025ACBF0948','13055420-D791-4802-B75E-ADAB4C022BE7','Apr  3 2008  1:21AM','Diff' UNION ALL
    SELECT '938DF985-C0E4-41E7-9510-C02FFE06F186','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr  2 2008 10:04PM','Tran' UNION ALL
    SELECT 'F19BD5F6-0FD4-4BD2-91DA-D03CF9719124','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr  4 2008  3:04AM','Full' UNION ALL
    SELECT 'D7B39FE1-B4BF-4AA0-9D3F-D0A363BFB757','13055420-D791-4802-B75E-ADAB4C022BE7','Apr  4 2008  1:20AM','Diff' UNION ALL
    SELECT '500BC0C0-0B7D-4D65-B635-D51F89DCA726','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr  4 2008  4:04AM','Tran' UNION ALL
    SELECT '943474EF-2AE1-49B5-9B8F-DA42630EB195','B9232B6F-3E09-480B-8658-4FE9ED2CC387','Apr  3 2008  2:10AM','Full' UNION ALL
    SELECT 'CA58E9A5-155B-404A-B57D-DA902A2A1202','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr  3 2008  6:04AM','Tran' UNION ALL
    SELECT 'F2F58383-1E7B-4191-BB89-E99E63B08202','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr  3 2008 12:04AM','Tran' UNION ALL
    SELECT '985F479B-4C11-4C6D-AE7E-F44B3426DF7A','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr  3 2008  3:04AM','Full' UNION ALL
    SELECT '966B6917-C284-46CA-8664-9DFBEF222BEF','13055420-D791-4802-B75E-ADAB4C022BE7','Mar 30 2008  1:32AM','Full' UNION ALL
    SELECT 'A588C63F-3887-4B4E-9AD3-F668BEB4194C','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr  2 2008  4:04PM','Tran'
    GO
     
    /* The query so far */
    SELECT
      CASE
        WHEN DATEADD(dd,-2,getdate()) > MAX(B.BackupDate) THEN 'OMFG'
        WHEN DATEADD(dd,-1,getdate()) > MAX(B.BackupDate) THEN 'wtf?'
        ELSE 'k'
      END AS BackupStatus,
      S.ServerName, 
      D.DatabaseName, 
      MAX(B.BackupDate) AS BackupDate, 
      B.BackupType
    FROM Backups B
    INNER JOIN Databases D
      ON B.DatabaseID = D.DatabaseID
    INNER JOIN Servers S
      ON D.ServerID = S.ServerID
    GROUP BY S.ServerName, D.DatabaseName, B.BackupType
    ORDER BY S.ServerName, D.DatabaseName
    GO
     
     
    /* Current Output */
    -- BackupStatus ServerName    DatabaseName        BackupDate              BackupType
    -- ------------ ------------- ------------------- ----------------------- ----------
    -- k            AEPPROD03     master              2008-04-04 02:10:00.000 Full
    -- k            AEPPROD03     msdb                2008-04-04 02:40:00.000 Full
    -- k            AEPPROD03     pn_ProductionData   2008-04-04 01:20:00.000 Diff
    -- OMFG         AEPPROD03     pn_ProductionData   2008-03-30 01:32:00.000 Full
    -- k            AEPPROD03     pn_RedFlag          2008-04-04 03:04:00.000 Full
    -- k            AEPPROD03     pn_RedFlag          2008-04-04 06:04:00.000 Tran
     
    /* Desired output */
    -- BackupStatus ServerName    DatabaseName        BackupDate              BackupType
    -- ------------ ------------- ------------------- ----------------------- ----------
    -- k            AEPPROD03     master              2008-04-04 02:10:00.000 Full
    -- k            AEPPROD03     msdb                2008-04-04 02:40:00.000 Full
    -- k            AEPPROD03     pn_ProductionData   2008-04-04 01:20:00.000 Diff
    -- k            AEPPROD03     pn_RedFlag          2008-04-04 06:04:00.000 Tran
    Any help is appreciated.
    Thanks.
    -D.
    David Maxwell
    Data Integrity? Yeah, I've heard of that...

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    What version SQL? 2K5 has different options for the "last record of this type" sort of question.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2007
    Location
    Ohio, USA
    Posts
    142
    It's 2005, 9.00.3152.
    David Maxwell
    Data Integrity? Yeah, I've heard of that...

  4. #4
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    This query produces the desired result set from your test case:

    SELECT
    CASE
    WHEN DATEADD(dd,-2,getdate()) > MAX(B.BackupDate) THEN 'OMFG'
    WHEN DATEADD(dd,-1,getdate()) > MAX(B.BackupDate) THEN 'wtf?'
    ELSE 'k'
    END AS BackupStatus,
    S.ServerName,
    D.DatabaseName,
    B.BackupDate,
    B.BackupType
    FROM Backups B,
    Databases D,
    Servers S
    Where B.DatabaseID = D.DatabaseID
    AND D.ServerID = S.ServerID
    AND B.BackupDate =
    (Select MAX(BackupDate)
    FROM backups
    WHERE backups.DatabaseID = D.DatabaseID)
    ORDER BY S.ServerName, D.DatabaseName


    Does this do what you need it to do?

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Another solution. Similar principle, bit more complex.

    Code:
    SELECT     BackupStatus    =    CASE
                                    WHEN DATEADD(dd,-2,getdate()) > bus.BackupDate THEN 
                                        'OMFG'
                                    WHEN DATEADD(dd,-1,getdate()) > bus.BackupDate THEN 
                                        'wtf?'
                                    ELSE 
                                        'k'
                                END
            , bus.ServerName
            , bus.DatabaseName
            , bus.BackupDate
            , bus.BackupType
    FROM    
            (SELECT    S.ServerName
                    , D.DatabaseName
                    , B.BackupDate
                    , B.BackupType
                    , latest        =    ROW_NUMBER() OVER    (PARTITION BY    S.ServerName
                                                                            , D.DatabaseName
                                                            ORDER BY        B.BackupDate    DESC)
            FROM    dbo.Backups        AS B
            INNER JOIN 
                    dbo.Databases    AS D
            ON    B.DatabaseID    = D.DatabaseID
            INNER JOIN 
                    dbo.Servers        AS S
            ON    D.ServerID        = S.ServerID) AS bus
    WHERE    latest = 1
    ORDER BY bus.ServerName
            , bus.DatabaseName
    GO
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Aug 2004
    Location
    Berlin, Germany
    Posts
    12

    Mille viae ducunt hominem per saecula Romam

    With one more select

    SELECT
    BS.BackupStatus,
    S.ServerName,
    D.DatabaseName,
    BS.BackupDate,
    B2.BackupType
    FROM (
    SELECT
    CASE
    WHEN DATEADD(dd,-2,getdate()) > MAX(B.BackupDate) THEN 'OMFG'
    WHEN DATEADD(dd,-1,getdate()) > MAX(B.BackupDate) THEN 'wtf?'
    ELSE 'k'
    END AS BackupStatus,
    MAX(B.BackupDate) AS BackupDate,
    B.DatabaseID as DatabaseID
    FROM Backups B
    GROUP BY B.DatabaseID
    ) as BS INNER JOIN Backups B2
    on BS.DatabaseID = B2.DatabaseID
    AND BS.BackupDate = B2.BackupDate
    INNER JOIN Databases D
    ON BS.DatabaseID = D.DatabaseID
    INNER JOIN Servers S
    ON D.ServerID = S.ServerID

    L.

  7. #7
    Join Date
    Jun 2007
    Location
    Ohio, USA
    Posts
    142
    Buckeye234: Missing the GROUP BY.
    Msg 8120, Level 16, State 1, Line 147
    Column 'Servers.ServerName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    Pootle_Flump: Works perfectly. I'll have to work with that OVER function some more to understand it. Looks cool, though.

    Lento: Works perfectly as well.
    Thank you all for your help.
    Last edited by ReadySetStop; 04-04-08 at 11:32. Reason: Formatting. Darn cut and paste...
    David Maxwell
    Data Integrity? Yeah, I've heard of that...

  8. #8
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    Sorry about, that, improper cut and paste. The query I posted will work properly if you remove the "MAX" function from both lines in the case statement. There is no need for it with that query.

  9. #9
    Join Date
    Jun 2007
    Location
    Ohio, USA
    Posts
    142
    Got it. Very nice.

    Thanks for the help.
    David Maxwell
    Data Integrity? Yeah, I've heard of that...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •