January 30, 2011 at 10:21 pm
select B.RegionName,
avg(D.Answer) as Answer
from dbo.Mst_Location A,Mst_Region B,
Master_Map_RegionWithLocation C,Voc_Trans_Details D,
Voc_Mst_Survey_Details E,Voc_Mst_Feedback_Question F
where A.LocationID=D.Location_Id and A.LocationID=C.LocationID and B.RegionID=C.RegionID
and C.RegionID=1
AND D.Survey_Id=E.Survey_ID
AND F.Question_Id=D.Question_ID
AND D.Month_Value=8 and D.Year_Value=2010 and
D.Location_Id IN
(
10205,
10206,
10207,
10210,
120,
101
) AND D.Question_ID IN
(
1,
2,
3,
4,
5,
6
)
GROUP BY B.RegionName
OUTPUT:
RegionName Answer
____________________
Central 53.3333
Now i want OUTPUT like
RegionName PreviousmonthAns Answer
----------------------------------------
Central 61.88 53.3333
January 30, 2011 at 10:28 pm
Ramya, sample data will be quiet useful here 🙂
January 30, 2011 at 10:51 pm
create a temp table with three column
option a)
create three temp variable and set the two values (region and answer) using first query.
set the third values with second query.
finally insert into temp table
option b)
set the third values with second query.
finally update the row iwth third values using region column
last
select * from temp table.
January 31, 2011 at 7:01 am
WITH x (RegionName, Month_Value, Answer) AS (
SELECT B.RegionName, D.Month_Value, AVG(D.Answer) AS [Answer]
FROM Mst_Location A
JOIN Voc_Trans_Details D ON A.LocationID = D.Location_Id
JOIN Master_Map_RegionWithLocation C ON A.LocationID = C.LocationID
JOIN Mst_Region B ON C.RegionID = B.RegionID
JOIN Voc_Mst_Survey_Details E ON D.Survey_Id = E.Survey_ID
JOIN Voc_Mst_Feedback_Question F ON D.Question_ID = F.Question_Id
WHERE C.RegionID = 1
AND D.Month_Value BETWEEN 7 AND 8
AND D.Year_Value = 2010
AND D.Location_Id IN (10205, 10206, 10207, 10210, 120, 101)
AND D.Question_ID IN (1, 2, 3, 4, 5, 6)
GROUP BY B.RegionName ,D.Month_Value )
SELECT x.RegionName,x2.Answer AS [PreviousMonthAns],x.Answer
FROM x
WHERE x.Month_Value = 8
LEFT JOIN x x2 ON x2.RegionName=x.RegionName AND x2.Month_Value=x.Month_Value-1
Far away is close at hand in the images of elsewhere.
Anon.
January 31, 2011 at 7:16 am
This probably better than the join
SELECT RegionName,
CASE WHEN Month_Value = 7 THEN Answer END AS [PreviousMonthAns],
CASE WHEN Month_Value = 8 THEN Answer END AS [Answer]
FROM x
GROUP BY RegionName
Far away is close at hand in the images of elsewhere.
Anon.
January 31, 2011 at 7:20 am
Or even this
SELECT RegionName,
AVG(CASE WHEN Month_Value = 7 THEN Answer END) AS [PreviousMonthAns],
AVG(CASE WHEN Month_Value = 8 THEN Answer END) AS [Answer]
FROM Mst_Location A
JOIN Voc_Trans_Details D ON A.LocationID = D.Location_Id
JOIN Master_Map_RegionWithLocation C ON A.LocationID = C.LocationID
JOIN Mst_Region B ON C.RegionID = B.RegionID
JOIN Voc_Mst_Survey_Details E ON D.Survey_Id = E.Survey_ID
JOIN Voc_Mst_Feedback_Question F ON D.Question_ID = F.Question_Id
WHERE C.RegionID = 1
AND D.Month_Value BETWEEN 7 AND 8
AND D.Year_Value = 2010
AND D.Location_Id IN (10205, 10206, 10207, 10210, 120, 101)
AND D.Question_ID IN (1, 2, 3, 4, 5, 6)
GROUP BY RegionName
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply