cancel
Showing results for 
Search instead for 
Did you mean: 

Need Media ID's added to this report

lisareinhart
Level 4

Can someone assist in getting the media id's added into this report?

SELECT ms.networkName AS 'Master Server'
        , di.id AS 'Image'
        , utcBigIntToNomTime(di.writeStartTime) AS 'Backup Date'
        , utcBigIntToNomTime(di.expirationTime) AS 'Expiration Date'
        , utcBigIntToNomTime(di.deletionTime) AS 'Deletion Date'
FROM domain_Image di
INNER JOIN domain_MasterServer ms
ON di.masterServerId = ms.id
WHERE ((di.filesFileName LIKE '%INCR%' AND utcBigIntToNomTime(di.expirationTime) > GETDATE()+30)
    OR (di.filesFileName LIKE '%FULL%' AND utcBigIntToNomTime(di.expirationTime) > GETDATE()+90))
AND di.id LIKE '%[ClientName]%'
 

1 ACCEPTED SOLUTION

Accepted Solutions

Arojasbe
Level 3

I think what you're looking for is

SELECT DISTINCT ms.networkName AS 'Master Server'

        , di.id AS 'Image'
        , imf.mediaId AS 'Media Id'
        , utcBigIntToNomTime(di.writeStartTime) AS 'Backup Date'
        , utcBigIntToNomTime(di.expirationTime) AS 'Expiration Date'
        , utcBigIntToNomTime(di.deletionTime) AS 'Deletion Date'
FROM domain_Image di
INNER JOIN domain_ImageFragment imf
ON di.id = imf.imageId
INNER JOIN domain_MasterServer ms
ON di.masterServerId = ms.id
WHERE ((di.filesFileName LIKE '%INCR%' AND utcBigIntToNomTime(di.expirationTime) > GETDATE()+30)
    OR (di.filesFileName LIKE '%FULL%' AND utcBigIntToNomTime(di.expirationTime) > GETDATE()+90))
AND di.id LIKE '%[ClientName]%'

 

Regards

View solution in original post

4 REPLIES 4

Arojasbe
Level 3

I think what you're looking for is this script:

SELECT ms.networkName AS 'Master Server'
        , di.id AS 'Image'
        , imf.mediaId AS 'Media Id'
        , utcBigIntToNomTime(di.writeStartTime) AS 'Backup Date'
        , utcBigIntToNomTime(di.expirationTime) AS 'Expiration Date'
        , utcBigIntToNomTime(di.deletionTime) AS 'Deletion Date'
FROM domain_Image di
INNER JOIN domain_ImageFragment imf
ON di.id = imf.imageId

INNER JOIN domain_MasterServer ms
ON di.masterServerId = ms.id
WHERE ((di.filesFileName LIKE '%INCR%' AND utcBigIntToNomTime(di.expirationTime) > GETDATE()+30)
    OR (di.filesFileName LIKE '%FULL%' AND utcBigIntToNomTime(di.expirationTime) > GETDATE()+90))
AND di.id LIKE '%[ClientName]%'

 

I hope this is what you want

lisareinhart
Level 4

This does help thank you, but can the duplicate tape id's be weeded out and only appear once?

Arojasbe
Level 3

I think what you're looking for is

SELECT DISTINCT ms.networkName AS 'Master Server'

        , di.id AS 'Image'
        , imf.mediaId AS 'Media Id'
        , utcBigIntToNomTime(di.writeStartTime) AS 'Backup Date'
        , utcBigIntToNomTime(di.expirationTime) AS 'Expiration Date'
        , utcBigIntToNomTime(di.deletionTime) AS 'Deletion Date'
FROM domain_Image di
INNER JOIN domain_ImageFragment imf
ON di.id = imf.imageId
INNER JOIN domain_MasterServer ms
ON di.masterServerId = ms.id
WHERE ((di.filesFileName LIKE '%INCR%' AND utcBigIntToNomTime(di.expirationTime) > GETDATE()+30)
    OR (di.filesFileName LIKE '%FULL%' AND utcBigIntToNomTime(di.expirationTime) > GETDATE()+90))
AND di.id LIKE '%[ClientName]%'

 

Regards

lisareinhart
Level 4

Thank you!