May 9, 2012 at 7:07 am
Hi All,
I am new to SSRS reports. I need to create a report that gives a difference of drivers from last month to this month. The report should look something like this. Any help on this greatly appreciated.
Location1Location2 Location3Totals
Total as of 04/09/2012 124 117 94 335
Driver Category Contractor 120 110 90 320
SubContractor 2 5 7 14
Difference -2 -2 +3 +9
Total as of 05/09/2012 122 115 97 344
I could get everything in this report except the Difference of these totals. How do I really do that. How do I really access the values from these two datasets and find out the difference?
I have created two different data sets with the following queries.
Dataset 1:
SELECT
(CASE WHEN SubContractor = 1 THEN 0 ELSE 1 END) ContractorNumber
,Contractor.LocationID AS [Contractor LocationID]
,Location.Location
,Ethnicity.EthnicityName
,Contractor.ContractTerminationDate,
(CASE WHEN SubContractor = 1 THEN 1 ELSE 0 END) AS SubContractor
FROM
Contractor
INNER JOIN Location
ON Contractor.LocationID = Location.LocationID
INNER JOIN Ethnicity
ON Contractor.EthnicityID = Ethnicity.EthnicityID
WHERE
Contractor.ContractTerminationDate is null AND ContractVersionDate <= GetDate()
order by Location.Location
Dataset 2:
SELECT
Contractor.ContractorNumber
,Location.Location
,Contractor.ContractTerminationDate,
Contractor.SubContractor
FROM
Contractor
INNER JOIN Location
ON Contractor.LocationID = Location.LocationID
WHERE
Contractor.ContractTerminationDate is null AND (ContractVersionDate <= @SnapShotDate)
order by Location.Location.
May 9, 2012 at 7:54 am
Hi
Without DDL's and sample data it is very difficult to help - could you post these?
The first thing that springs to mind is using a CTE to create a UNIONed dataset and then querying that.. (Obviously I'm taking a shot in the dark here however!)
WITH CTE AS
(
SELECT
'DATASET1' AS DATASET
,(CASE WHEN SubContractor = 1 THEN 0 ELSE 1 END) ContractorNumber
,Contractor.LocationID AS [Contractor LocationID]
,Location.Location
,Ethnicity.EthnicityName
,Contractor.ContractTerminationDate,
(CASE WHEN SubContractor = 1 THEN 1 ELSE 0 END) AS SubContractor
FROM
Contractor
INNER JOIN Location
ON Contractor.LocationID = Location.LocationID
INNER JOIN Ethnicity
ON Contractor.EthnicityID = Ethnicity.EthnicityID
WHERE
Contractor.ContractTerminationDate is null AND ContractVersionDate <= GetDate()
order by Location.Location
UNION ALL
SELECT
'DATASET2' AS DATASET
,Contractor.ContractorNumber
,Location.Location
,Contractor.ContractTerminationDate,
Contractor.SubContractor
FROM
Contractor
INNER JOIN Location
ON Contractor.LocationID = Location.LocationID
WHERE
Contractor.ContractTerminationDate is null AND (ContractVersionDate <= @SnapShotDate)
order by Location.Location.
)
SELECT *
FROM CTE
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
May 9, 2012 at 11:10 am
Thanks for your quick response. Following is the format for attached report. I am just taking counts . I am getting everything else except the difference row.
I am not able insert a picture here. I do not have any url for this. Please let me know if the following table is not very clear.
Location1 Location2 Location3 Totals
Total as of 04/09/2012 124 117 94 335
Driver Category Contractor 120 110 90 320
SubContractor 2 5 7 14
Difference -2 -2 +3 +9
Total as of 05/09/2012 122 115 97 344
Now I have changed query as follows
WITH testCTE(Region,ContractorNumber,Location,EthnicityName,SubContractor) AS
(
SELECT
'Region1' AS Region
,(CASE WHEN SubContractor = 1 THEN 0 ELSE 1 END) as ContractorNumber
,Location.Location
,Ethnicity.EthnicityName
,(CASE WHEN SubContractor = 1 THEN 1 ELSE 0 END) AS SubContractor
FROM
Contractor
INNER JOIN Location ON Contractor.LocationID = Location.LocationID
INNER JOIN Ethnicity ON Contractor.EthnicityID = Ethnicity.EthnicityID
WHERE
Contractor.ContractTerminationDate is null AND ContractVersionDate <= GetDate()
UNION ALL
SELECT
'Region2' AS DATASET
,(CASE WHEN SubContractor = 1 THEN 1 ELSE 1 END) as ContractorNumber
,Location.Location
,NULL AS EthnicityName
,NULL AS SubContractor
FROM
Contractor
INNER JOIN Location
ON Contractor.LocationID = Location.LocationID
WHERE
Contractor.ContractTerminationDate is null AND ContractVersionDate <= @SnapShotDate
)
select * from testCTE ORDER BY Region,Location,EthnicityName
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply