Amour22015
asked on
SQL Server 2012 r2 - Query results have 2 seperate results instead of 1
Hi Experts,
I have this query these are Temp Tables:
And
When I execute my query I get:
Notice that there are 2 result's I need this to be only 1 result with Above Baseline/Special/etc.. together. This is causing SSRS report to only have Above Baseline and all Special/etc... are not included in the report. I need all records to be included in only 1 result. Seems to be treating the "Above Base Line" Section separately then the rest of the query. The rest of the query comes out on the 2nd result starting with Specialized. Please help....
Thank You for helping me...
I have this query these are Temp Tables:
-- Temp Table Create/Drop Section:
-- Above Baseline
IF OBJECT_ID('TempDB..#tmpbaseline') IS NOT NULL
DROP TABLE #tmpbaseline;
CREATE TABLE #tmpbaseline(
Scandate Date NULL,
Category [varchar](255) NULL,
Plugin int,
IPLong Bigint NULL,
)
-- Specialized Catagory
IF OBJECT_ID('TempDB..#tmpSpecialized') IS NOT NULL
DROP TABLE #tmpSpecialized;
CREATE TABLE #tmpSpecialized(
Scandate Date NULL,
Category [varchar](255) NULL,
Plugin int,
IPLong Bigint NULL,
)
-- Marker-File (Java)
IF OBJECT_ID('TempDB..#tmpMarkerFile') IS NOT NULL
DROP TABLE #tmpMarkerFile;
CREATE TABLE #tmpMarkerFile(
Scandate Date NULL,
Category [varchar](255) NULL,
Plugin int,
IPLong Bigint NULL,
)
And
Select Distinct
AB.Scandate,
AB.Category,
AB.Plugin,
AB.IPLong,
Count(*) as Total
From #tmpbaseline AB -- Above Base line
Group By AB.Scandate,
AB.Category,
AB.Plugin,
AB.IPLong
Select Distinct
SL.Scandate,
SL.Category,
SL.Plugin,
SL.IPLong,
Count(*) as Total
From #tmpSpecialized SL -- Specialized
Where
NOT EXISTS
(
SELECT NULL FROM #tmpbaseline AB Where (SL.Plugin = AB.Plugin And SL.IPLong = AB.IPLong And SL.Scandate = AB.Scandate)
)
Group By SL.Scandate,
SL.Category,
SL.Plugin,
SL.IPLong
UNION ALL
--- Temp Table
Select Distinct
MF.Scandate,
MF.Category,
MF.Plugin,
MF.IPLong,
Count(*) as Total
From #tmpMarkerFile MF -- Marker File (Java)
Where
NOT EXISTS
(
SELECT NULL FROM #tmpSpecialized SL Where (MF.Plugin = SL.Plugin And MF.IPLong = SL.IPLong And MF.Scandate = SL.Scandate)
UNION
SELECT NULL FROM #tmpbaseline AB Where (MF.Plugin = AB.Plugin And MF.IPLong = AB.IPLong And MF.Scandate = AB.Scandate)
)
Group By MF.Scandate,
MF.Category,
MF.Plugin,
MF.IPLong
UNION ALL
-- Temp Table
Select Distinct
MS.Scandate,
'Microsoft' as Category,
MS.Plugin,
MS.IPLong,
Count(*) as Total
etc....
When I execute my query I get:
Notice that there are 2 result's I need this to be only 1 result with Above Baseline/Special/etc.. together. This is causing SSRS report to only have Above Baseline and all Special/etc... are not included in the report. I need all records to be included in only 1 result. Seems to be treating the "Above Base Line" Section separately then the rest of the query. The rest of the query comes out on the 2nd result starting with Specialized. Please help....
Thank You for helping me...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER