Error in Aggregate function in sqlserver

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

Latest Posts