Is there any way to achieve the following code?

I have an sql statement like..

Select
Res1,
Res2,
Res3,
Res4
from tbl1
where Res1=1
Group by Res2
Having Res4>0

I wanna hav the final column but that column is based on Res1 and Res3. The individual statement would be

Select
Res5
from tbl1
where Res1=1 and max(Res3)
Group by Res2
Having Res4>0

I wanna merge that Res5 into the first statement.

Res3 is the total value which can b got by executing the first statement.

I wanna do 开发者_运维问答like..

Select
Res1,
Res2,
Res3,
Res4
(Select
Res5
from tbl1
where Res1=1 and max(Res3)
Group by Res2
Having Res4>0)
from tbl1
where Res1=1
Group by Res2
Having Res4>0

But apperantly that is not correct.

How to achieve that?

The exact code would be

declare @noshow int, @target int; 
set @noshow=20;
set @target=1200;

select
CONVERT(VARCHAR(10), visit.regdate, 103) as 'Date',
datename(weekday, visit.regdate) as 'Day',
count(queueno) as 'Total_Served',
sum(case when datediff(second, starttime, nexttime)<= @target then 1 else 0 end) as 'Less_Target',
isnull((sum(case when datediff(second, starttime, nexttime)<= @target then 1 else 0 end)*100)/count(queueno),0) as 'Less_Target_Per',
sum(case when datediff(second, starttime, nexttime)> @target then 1 else 0 end) as 'More_Target',
isnull((sum(case when datediff(second, starttime, nexttime)> @target then 1 else 0 end)*100)/count(queueno),0) as 'More_Target_Per',
isnull(CONVERT(varchar(6), avg(datediff(second,nexttime,endtime))/3600)+ ':' + RIGHT('0' + CONVERT(varchar(2), (sum(datediff(second,nexttime,endtime)) % 3600) / 60), 2)+ ':' + RIGHT('0' + CONVERT(varchar(2), sum(datediff(second,nexttime,endtime)) % 60), 2),0) as 'Avg_Serving_Time',
isnull(CONVERT(varchar(6), max(datediff(second,starttime,nexttime))/3600)+ ':' + RIGHT('0' + CONVERT(varchar(2), (max(datediff(second,starttime,nexttime)) % 3600) / 60), 2)+ ':' + RIGHT('0' + CONVERT(varchar(2), max(datediff(second,starttime,nexttime)) % 60), 2),0) as 'Max_Waiting_Time',
CONVERT(VARCHAR(26), getdate(), 108) as 'Cus_Arrival'
from visit 
where visit.branchno in (  '1007'    )  and  visit.wstation in ('1'  ,'10'  ,'11'  ,'15'  ,'2'  ,'20'  ,'21'  ,'23'  ,'24'  ,'28'  ,'29'  ,'3'  ,'30'  ,'31'  ,'32'  ,'33'  ,'4'  ,'5'  ,'6'  ,'7'  ,'8'  ,'9'    )  and visit.catname in ('BY PASS'  ,'REG STORE'  ,'REGISTRATION'  ,'ROOM 1 to 4'  ,'ROOM A1 & A2'  ,'ROOM A3 & A4'  ,'ROOM A5-A7&A9-A11'  ,'ROOM A8-BMD'  ,'ROOM B20'  ,'ROOM B21 B23 B24'  ,'Ward Cases'    )  and visit.btnname in ('BY PASS'  ,'REG STORE'  ,'REGISTRATION'  ,'ROOM 1 to 4'  ,'ROOM A1 & A2'  ,'ROOM A3 & A4'  ,'ROOM A5-A7&A9-A11'  ,'ROOM A8-BMD'  ,'ROOM B20'  ,'ROOM B21'  ,'B23'  ,'B24'  ,'Ward Cases'  )   and (CONVERT(VARCHAR(10), visit.tmstamp, 111) in(  '2010/11/01'     ,'2010/11/02'     ,'2010/11/03'     ,'2010/11/04'     ,'2010/11/05'     ,'2010/11/06'     ,'2010/11/07'     ,'2010/11/08'     ,'2010/11/09'     ,'2010/11/10'     ,'2010/11/11'     ,'2010/11/12'     ,'2010/11/13'     ,'2010/11/14'     ,'2010/11/15'     ,'2010/11/16'     ,'2010/11/17'     ,'2010/11/18'     ,'2010/11/19'     ,'2010/11/20'     ,'2010/11/21'     ,'2010/11/22'     ,'2010/11/23'     ,'2010/11/24'     ,'2010/11/25'     ,'2010/11/26'     ,'2010/11/27'     ,'2010/11/28'     ,'2010/11/29'     ,'2010/11/30'     ,'2010/12/01'     ,'2010/12/02'     ,'2010/12/03'     ,'2010/12/04'     ,'2010/12/05'     ,'2010/12/06'     ,'2010/12/07'     ,'2010/12/08'     ,'2010/12/09'     ,'2010/12/10'     ,'2010/12/11'     ,'2010/12/12'     ,'2010/12/13'     ,'2010/12/14'     ,'2010/12/15'     ,'2010/12/16'     ,'2010/12/17'     ,'2010/12/18'     ,'2010/12/19'     ,'2010/12/20'     ,'2010/12/21'     ,'2010/12/22'     ,'2010/12/23'     ,'2010/12/24'     ,'2010/12/25'     ,'2010/12/26'     ,'2010/12/27'     ,'2010/12/28'     ,'2010/12/29'     ,'2010/12/30'     ,'2010/12/31' )) and  datediff(second,nexttime,endtime)>@noshow 
group by regdate
having sum(case when datediff(second, starttime, nexttime)> @target then 1 else 0 end) >0

Ignore the where clause. It is too long.

I wanna replace Cus_Arrival with the second statement.

It is actually the arrival time for the Max_Waiting_Time.

---

I assume you are using MS SQL Server.

Select
    Res1,
    Res2,
    Res3,
    Res4
from tbl1
where Res1=1
Group by Res2
Having Res4>0

is not valid.

You can not select columns that are not part of the group by.

Read about group by here http://msdn.microsoft.com/en-us/library/ms177673.aspx

Each table or view column in any nonaggregate expression in the list must be included in the GROUP BY list: