June 21, 2016 at 5:24 am
Can some one help me with the query to achieve the result in the format listed in the image attached.
The data is also in the image attached.
June 21, 2016 at 8:24 am
minakshi88 (6/21/2016)
Can some one help me with the query to achieve the result in the format listed in the image attached.The data is also in the image attached.
one way maybe....
WITH cte AS (
SELECT DISTINCT
t2.vcrProgramGroupName,
t2.EclerxOffices,
t1.ECRLevel
FROM table2 t2
CROSS JOIN table1 t1
)
SELECT cte.vcrProgramGroupName,
cte.EclerxOffices,
cte.ECRLevel,
ISNULL(t2.TotalEmployees, 0)
FROM cte
LEFT OUTER JOIN table2 t2 ON cte.ECRLevel = t2.ECRLevel;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 22, 2016 at 1:07 am
Hi,
Thanks for giving me the idea.
but I have modified the 2nd query a bit, for the expected result
SELECT cte.vcrProgramGroupName,
cte.EclerxOffices,
cte.ECRLevel,
ISNULL(t2.TotalEmployees, 0)
FROM cte
LEFT OUTER JOIN table2 t2 ON CTE.vcrProgramGroupName= T2.vcrProgramGroupName AND CTE.EclerxOffices = T2.EclerxOffices AND cte.ECRLevel = t2.ECRLevel
GROUP BY CTE.vcrProgramGroupName, CTE.EclerxOffices,CTE.ECRLevel
June 22, 2016 at 1:10 am
Ignore the pevious query.
the correct query is
SELECT cte.vcrProgramGroupName,
cte.EclerxOffices,
cte.ECRLevel,
SUM(ISNULL(t2.TotalEmployees, 0))
FROM cte
LEFT OUTER JOIN table 2 t2 ON CTE.vcrProgramGroupName= T2.vcrProgramGroupName AND CTE.EclerxOffices = T2.EclerxOffices AND cte.ECRLevel = t2.ECRLevel
GROUP BY CTE.vcrProgramGroupName, CTE.EclerxOffices,CTE.ECRLevel
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply