Error in Aggregate function in sqlserver

Posted by Klbaiju under Sql Server on 6/16/2014 | Points: 10 | Views : 339 | Status : [Member] | Replies : 1
Hi.

following is my query

create table #temp(busid int,tourdate smalldatetime,fare int,balance int, dsal int,tourtype char,startdate nvarchar(15))
insert into #temp values(100,'2014-06-09',30000,20000,0,'M','09-jun-2014')
insert into #temp values(100,'2014-06-10',0,0,0,'M','09-jun-2014')
insert into #temp values(100,'2014-06-11',0,0,0,'M','09-jun-2014')
insert into #temp values(100,'2014-06-12',0,0,0,'M','09-jun-2014')
insert into #temp values(100,'2014-06-13',0,0,0,'M','09-jun-2014')
select busid,tourdate,(fare-dsal) as profit,tourtype,startdate from #temp group by tourdate,fare,dsal,busid,tourtype ,startdate
drop table #temp


and the output is

busid tourdate profit tourtype startdate
100 2014-06-09 30000 M 09-jun-2014
100 2014-06-10 0 M 09-jun-2014
100 2014-06-11 0 M 09-jun-2014
100 2014-06-12 0 M 09-jun-2014
100 2014-06-13 0 M 09-jun-2014

this is a tour related application.and sample data is a multiple day tour which starts from 2014-06-09 and ends on 2014-06-13.

I stored the fare,balance and dsal on first record(record with startdate).

above query is correct and i need to check balance and dsal.

my requirement is if dsal and balance =0 i need following output

busid tourdate profit tourtype startdate
100 2014-06-09 0 M 09-jun-2014
100 2014-06-10 0 M 09-jun-2014
100 2014-06-11 0 M 09-jun-2014
100 2014-06-12 0 M 09-jun-2014
100 2014-06-13 0 M 09-jun-2014

how it is possible.

Regards

Baiju




Responses

Posted by: Kirthiga on: 6/16/2014 [Member] Starter | Points: 25

Up
0
Down
Hi Baiju,

You can use case statement for your requirement

create table #temp(busid int,tourdate smalldatetime,fare int,balance int, dsal int

,tourtype char,startdate nvarchar(15))
insert into #temp values(100,'2014-06-09',30000,0,0,'M','09-jun-2014')
insert into #temp values(100,'2014-06-10',0,0,0,'M','09-jun-2014')
insert into #temp values(100,'2014-06-11',0,0,0,'M','09-jun-2014')
insert into #temp values(100,'2014-06-12',0,0,0,'M','09-jun-2014')
insert into #temp values(100,'2014-06-13',0,0,0,'M','09-jun-2014')

select busid,tourdate,case when balance=0 and dsal=0 then 0 else (fare-dsal) end as profit
,tourtype,startdate
from #temp
group by busid,tourdate,case when balance=0 and dsal=0 then 0 else (fare-dsal) end
,tourtype ,startdate


Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response