November 11, 2016 at 8:38 am
HI,
If I run this query it returns exactly what I want in column2 (a date)
SELECT
'Benefit Table Last Refresh Date',
CAST(CAST(MAX(last_user_update) AS DATE) AS NVARCHAR(10)),
CASE WHEN MAX(last_user_update )<DATEADD(day, -1, getdate()) THEN 0 ELSE 1 end
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'pec_prod')
AND OBJECT_ID=OBJECT_ID('pec.rgt_Portal_BenefitAccumulatorPeriods')
If I add it to the end of my union I get a NULL for column 2 and I can't figure out why:
DECLARE @reportServerStatus NVARCHAR(30)
IF EXISTS (SELECT 1
FROM master.dbo.sysprocesses
WHERE program_name = N'Report Server')
BEGIN
SET @reportServerStatus = 'Online'
END
ELSE
BEGIN
SET @reportServerStatus = '*OFFLINE*'
END
SELECT
td1 = 'Geocoded Evips Addresses',
td3 =
CAST((SUM(CASE WHEN latitude IS NULL THEN 0 ELSE 1 END)) AS NVARCHAR(10)) +
' of ' +
CAST(COUNT(1) AS NVARCHAR(10)) +
' : ' +
LEFT(
(
CAST((SUM(CASE WHEN latitude IS NULL THEN 0 ELSE 1 END)) AS DECIMAL(10, 2))
/
CAST(COUNT(1) AS DECIMAL(10, 2))
)* 100, 5) + '%'
,
CASE WHEN
(
CAST((SUM(CASE WHEN latitude IS NULL THEN 0 ELSE 1 END)) AS DECIMAL(10, 2))
/
CAST(COUNT(1) AS DECIMAL(10, 2))
)
<.993 THEN 0 ELSE 1 END -- eVips geocoding must be greater than 99.3 percent or a review is needed.
FROM [evips_PROD].pec.tGeoCodingData
-- only care about practice or facility addresses
LEFT JOIN eVIPS_Prod.dbo.PracticeLocations ON
PracticeLocations.AddressID = tGeoCodingData.addressID
LEFT JOIN eVIPS_Prod.dbo.FacilityAddresses ON
FacilityAddresses.AddressID = tGeoCodingData.addressID
WHERE
FacilityAddresses.AddressID IS NOT NULL
OR
PracticeLocations.AddressID IS NOT NULL
UNION ALL
-- PLEXIS GEOCODING
SELECT
td = 'Geocoded Plexis Addresses',
td =
CAST((SUM(CASE WHEN latitude IS NULL THEN 0 ELSE 1 END)) AS NVARCHAR(10)) +
' of ' +
CAST(COUNT(1) AS NVARCHAR(10)) +
' : ' +
LEFT(
(
CAST((SUM(CASE WHEN latitude IS NULL THEN 0 ELSE 1 END)) AS DECIMAL(10, 2))
/
CAST(COUNT(1) AS DECIMAL(10, 2))
)* 100, 5) + '%'
,
CASE WHEN
(
CAST((SUM(CASE WHEN latitude IS NULL THEN 0 ELSE 1 END)) AS DECIMAL(10, 2))
/
CAST(COUNT(1) AS DECIMAL(10, 2))
)
<.998 THEN 0 ELSE 1 END -- Plexis geocoding must be greater than 99.8 percent or a review is needed.
FROM [pec_prod].pec.tGeoCodingData
UNION ALL
SELECT
'NodeB Report Server Status',
@reportServerStatus,
CASE WHEN @reportServerStatus = 'Online' THEN 1 ELSE 0 END -- server must be online
UNION ALL
SELECT
'Last HEDIS.DRE_Members Refresh Date',
CAST(CAST(MAX(date_Created) AS DATE) AS NVARCHAR(10)),
CASE WHEN DATEDIFF(DAY, MAX(date_Created), GETDATE())> 2 THEN 0 ELSE 1 end-- if data is more than 2 days old.... report it
FROM pec_prod.HEDIS.DRE_Members
UNION ALL
SELECT
'eVIPS Directory Snapshot',
CAST(MAX(SnapshotDate) AS NVARCHAR(50)),
CASE WHEN MAX(SnapshotDate)<DATEADD(HOUR, -1, getdate()) THEN 0 ELSE 1 end
FROM eVIPS_Prod.pec.PEC_RGT_DirectoryStatus_Snapshot
UNION ALL
SELECT
'PML Refresh',
'Date: ' + FORMAT(MAX(xrhCreateDt), 'yyyy/MM/dd') + ' - RowCount: ' + CAST(COUNT(*) AS VARCHAR(50)),
CASE WHEN
DATEDIFF(DAY, MAX(xrhCreateDt), GETDATE())> 2
OR
COUNT(*) < 100000
THEN 0 ELSE 1 end-- if data is more than 2 days old.... report it
FROM pecXRefData INNER JOIN pecXRefHeader ON xrhID = xr_HeaderID AND xrh_CatalogID=1045
--ROB ADDED 11/11/2016 check to see if rgt_Portal_BenefitAccumulatorPeriods has been updated
UNION ALL
SELECT
'Benefit Table Last Refresh Date',
CAST(CAST(MAX(last_user_update) AS DATE) AS NVARCHAR(20)),
CASE WHEN MAX(last_user_update )<DATEADD(day, -1, getdate()) THEN 0 ELSE 1 end
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'pec_prod')
AND OBJECT_ID=OBJECT_ID('pec.rgt_Portal_BenefitAccumulatorPeriods')
--END
November 11, 2016 at 9:48 am
The most obvious explanation is that the NULL value is coming from some other part of the UNION. Does the first column value match the value in this part of the query?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 11, 2016 at 10:45 am
krypto69 (11/11/2016)
HI,If I run this query it returns exactly what I want in column2 (a date)
SELECT'Benefit Table Last Refresh Date',
CAST(CAST(MAX(last_user_update) AS DATE) AS NVARCHAR(10)),
CASE WHEN MAX(last_user_update )<DATEADD(day, -1, getdate()) THEN 0 ELSE 1 end
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'pec_prod')
AND OBJECT_ID=OBJECT_ID('pec.rgt_Portal_BenefitAccumulatorPeriods')
If I add it to the end of my union I get a NULL for column 2 and I can't figure out why:
DECLARE @reportServerStatus NVARCHAR(30)
IF EXISTS (SELECT 1
FROM master.dbo.sysprocesses
WHERE program_name = N'Report Server')
BEGIN
SET @reportServerStatus = 'Online'
END
ELSE
BEGIN
SET @reportServerStatus = '*OFFLINE*'
END
SELECT
td1 = 'Geocoded Evips Addresses',
td3 =
CAST((SUM(CASE WHEN latitude IS NULL THEN 0 ELSE 1 END)) AS NVARCHAR(10)) +
' of ' +
CAST(COUNT(1) AS NVARCHAR(10)) +
' : ' +
LEFT(
(
CAST((SUM(CASE WHEN latitude IS NULL THEN 0 ELSE 1 END)) AS DECIMAL(10, 2))
/
CAST(COUNT(1) AS DECIMAL(10, 2))
)* 100, 5) + '%'
,
CASE WHEN
(
CAST((SUM(CASE WHEN latitude IS NULL THEN 0 ELSE 1 END)) AS DECIMAL(10, 2))
/
CAST(COUNT(1) AS DECIMAL(10, 2))
)
<.993 THEN 0 ELSE 1 END -- eVips geocoding must be greater than 99.3 percent or a review is needed.
FROM [evips_PROD].pec.tGeoCodingData
-- only care about practice or facility addresses
LEFT JOIN eVIPS_Prod.dbo.PracticeLocations ON
PracticeLocations.AddressID = tGeoCodingData.addressID
LEFT JOIN eVIPS_Prod.dbo.FacilityAddresses ON
FacilityAddresses.AddressID = tGeoCodingData.addressID
WHERE
FacilityAddresses.AddressID IS NOT NULL
OR
PracticeLocations.AddressID IS NOT NULL
UNION ALL
-- PLEXIS GEOCODING
SELECT
td = 'Geocoded Plexis Addresses', -- This line should be "td1 = ..."
--** td = ** This line can go away or be "td3 = "
CAST((SUM(CASE WHEN latitude IS NULL THEN 0 ELSE 1 END)) AS NVARCHAR(10)) +
' of ' +
CAST(COUNT(1) AS NVARCHAR(10)) +
' : ' +
LEFT(
(
CAST((SUM(CASE WHEN latitude IS NULL THEN 0 ELSE 1 END)) AS DECIMAL(10, 2))
/
CAST(COUNT(1) AS DECIMAL(10, 2))
)* 100, 5) + '%'
,
CASE WHEN
(
CAST((SUM(CASE WHEN latitude IS NULL THEN 0 ELSE 1 END)) AS DECIMAL(10, 2))
/
CAST(COUNT(1) AS DECIMAL(10, 2))
)
<.998 THEN 0 ELSE 1 END -- Plexis geocoding must be greater than 99.8 percent or a review is needed.
FROM [pec_prod].pec.tGeoCodingData
UNION ALL
SELECT
'NodeB Report Server Status',
@reportServerStatus,
CASE WHEN @reportServerStatus = 'Online' THEN 1 ELSE 0 END -- server must be online
UNION ALL
SELECT
'Last HEDIS.DRE_Members Refresh Date',
CAST(CAST(MAX(date_Created) AS DATE) AS NVARCHAR(10)),
CASE WHEN DATEDIFF(DAY, MAX(date_Created), GETDATE())> 2 THEN 0 ELSE 1 end-- if data is more than 2 days old.... report it
FROM pec_prod.HEDIS.DRE_Members
UNION ALL
SELECT
'eVIPS Directory Snapshot',
CAST(MAX(SnapshotDate) AS NVARCHAR(50)),
CASE WHEN MAX(SnapshotDate)<DATEADD(HOUR, -1, getdate()) THEN 0 ELSE 1 end
FROM eVIPS_Prod.pec.PEC_RGT_DirectoryStatus_Snapshot
UNION ALL
SELECT
'PML Refresh',
'Date: ' + FORMAT(MAX(xrhCreateDt), 'yyyy/MM/dd') + ' - RowCount: ' + CAST(COUNT(*) AS VARCHAR(50)),
CASE WHEN
DATEDIFF(DAY, MAX(xrhCreateDt), GETDATE())> 2
OR
COUNT(*) < 100000
THEN 0 ELSE 1 end-- if data is more than 2 days old.... report it
FROM pecXRefData INNER JOIN pecXRefHeader ON xrhID = xr_HeaderID AND xrh_CatalogID=1045
--ROB ADDED 11/11/2016 check to see if rgt_Portal_BenefitAccumulatorPeriods has been updated
UNION ALL
SELECT
'Benefit Table Last Refresh Date',
CAST(CAST(MAX(last_user_update) AS DATE) AS NVARCHAR(20)),
CASE WHEN MAX(last_user_update )<DATEADD(day, -1, getdate()) THEN 0 ELSE 1 end
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'pec_prod')
AND OBJECT_ID=OBJECT_ID('pec.rgt_Portal_BenefitAccumulatorPeriods')
--END
See my inline comments in the code... I don't know where the grief is coming from, but I've never liked using any form of UNION without aliasing ALL columns in ALL queries, or at least aliasing ALL columns in the first query and NONE of the rest.
I don't know if that's going to help here or not....
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 11, 2016 at 10:47 am
Thanks . I figure it out. It really was a NULL.
I am querying in an A/G group and needed to add a linked server to reference my primary to get it to be correct.
Thanks again.
November 11, 2016 at 11:11 am
I also just realized that you might need to add the database name as a prefix to the system view name, as otherwise the default database might override which database that view applies to. Additionally, your OBJECT_ID function might also need the database name prefixed to the table name specified. I say that because you reference other databases earlier in your UNION ALL construction. Give that a try and see if you still actually get a NULL value back.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply