Click here to Skip to main content
14,929,864 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi

I have a problem with the following query.

I need to use a case select in a sub query that has a group by, but i can't seem to correctly link it to the main query, so that the amount display correctly.

Please have a look:

SQL
Select SiteID,SiteName,Score as CurrentScore,
(select case @datrange
        WHEN 'Daily'
        THEN (select ss.score from sites ss where Date between 
              dateadd(dd,@Datefrom,-1) and
               DateAdd(dd,@dateTo,-1) 
               Group By ss.SiteID)
       WHEN 'Monthly'
       THEN (select ss.score from sites ss where datefrom between 
              dateadd(mm,@Datefrom,-1) and
               DateAdd(mm,@dateTo,-1) 
               Group By ss.SiteID)
) As previous1,
(select case @datrange
        WHEN 'Daily'
        THEN (select ss.score 
              from sites ss where Date between 
              dateadd(dd,@Datefrom,-2) and
               DateAdd(dd,@dateTo,-2)
               Group By ss.SiteID)
       WHEN 'Monthly'
       THEN (select ss.score 
               from sites ss where datefrom between 
              dateadd(mm,@Datefrom,-2) and
               DateAdd(mm,@dateTo,-2) 
               Group By ss.SiteID)
) As previous2

From Site
Where Date Between @Datefrom and @DateTo
Group by
SiteID


So when I do this I obviously get the error of the subquery returns more than one value, I just need to join the Sub query correctly to the main query.

so the values display correctly as follows:

SiteID | Sitename | CurrentScore | Previous1 | Previous2
1 | Jerico | 3.62 | 2.27 | 2.31
2 | Hut | 4.26 | 5.21 | 5.22

Thank you in advance for any help and efforts.
Posted
Updated 23-Apr-14 0:44am
v2

1 solution

It is called Pivot query.

Have a look:
Using PIVOT and UNPIVOT[^]
Pivot and Unpivot table in SQL SERVER[^]
How to UNPIVOT Data Using T-SQL [^]

Using many subqueries in a single query is inefficient!
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900