3 statements in one - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

3 statements in one

Author

Topic

constantinos1987
Starting Member

25 Posts

Posted - 2008-12-11 : 20:54:47

create view times as
select substring(Convert(char(19),a.transdatetime,120),12,2) as
T1,avg(ivalue*quantity) as average
from transactions a,transactionItems b
where a.transid=b.transid
group by substring(Convert(char(19),a.transdatetime,120),12,2)

select avg(average) morning from Times
where T1>=8 and t1<11
select avg(average) midday from Times
where T1>=11 and t1<17
select avg(average) afternoon from Times
where T1>=17 and t1<20

this give me the average of every time_group (8-11,11-17,17-20) in three different tables with one value each. i want to find a way to make them all in one table if it is possible... the two select statements following find the average per store and the average per date. i want modify the above and find the average per day (per monday, per tuesday......per sunday) and include all the three statements in one

select transStore,avg(ivalue*quantity) as avgPerStore
from transactions as A,transactionItems as B
where A.transID=B.transID
group by transStore

select distinct convert(varchar,transdatetime,101) as thedate,avg(ivalue*quantity) as avgPerdate
from transactions a,transactionItems b
where a.transID=b.transID
group by convert(varchar,transdatetime,101)
order by avgPerDate desc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-11 : 22:57:24

[code]select avg(case when T1>=8 and t1<11 then average else null end) morning ,
avg(case when T1>=11 and t1<27 then average else null end) midday ,
avg(case when T1>=17 and t1<20 then average else null end) afternoon from Times[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-11 : 23:09:49

[code]select dateadd(dd,datediff(dd,0,transdatetime),0) as thedate,
avg(ivalue*quantity) as avgPerdate,
avg(case when datepart(hh,transdatetime) >=8 and datepart(hh,transdatetime)<11 then ivalue*quantity else null) as avgmorning,
avg(case when datepart(hh,transdatetime) >=11 and datepart(hh,transdatetime)<17 then ivalue*quantity else null) as avgmidday,
avg(case when datepart(hh,transdatetime) >=17 and datepart(hh,transdatetime)<20 then ivalue*quantity else null) as avgafternoon
from transactions a
inner join transactionItems b
on a.transID=b.transID
group by dateadd(dd,datediff(dd,0,transdatetime),0)
order by avgPerDate desc[/code]

constantinos1987
Starting Member

25 Posts

Posted - 2008-12-12 : 11:09:34

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ')'.

i received this syntax error but i think that the () are all correct. is there anything wrong??

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-12 : 23:04:24

After case u have use end keyword

select dateadd(dd,datediff(dd,0,transdatetime),0) as thedate,
avg(ivalue*quantity) as avgPerdate,
avg(case when datepart(hh,transdatetime) >=8 and datepart(hh,transdatetime)<11 then ivalue*quantity else null END) as avgmorning,
avg(case when datepart(hh,transdatetime) >=11 and datepart(hh,transdatetime)<17 then ivalue*quantity else null END ) as avgmidday,
avg(case when datepart(hh,transdatetime) >=17 and datepart(hh,transdatetime)<20 then ivalue*quantity else null END ) as avgafternoon
from transactions a
inner join transactionItems b
on a.transID=b.transID
group by dateadd(dd,datediff(dd,0,transdatetime),0)
order by avgPerDate desc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-12 : 23:25:27

quote:
Originally posted by constantinos1987

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ')'.

i received this syntax error but i think that the () are all correct. is there anything wrong??

i missed end after case. so use the query bklr provided.

constantinos1987
Starting Member

25 Posts

Posted - 2008-12-13 : 12:43:41

is it possible to have the day instead of the date??
i need to find the average per monday, per tuesday...
the result i need has to be like this....

store 100, monday, 8-11, 24 euro
store 100, monday, 11-17, 100 euro
store 100, monday, 17-20, 150 euro
store 100, tuesday, 8-11, 24 euro
store 100, tuesday, 11-17, 100 euro
store 100, tuesday, 17-20, 150 euro
.....
store 100, friday, 8-11, 24 euro
store 100, friday, 11-17, 100 euro
store 100, friday, 17-20, 150 euro
........
store 200, monday, 8-11, 244 euro
store 200, monday, 11-17, 160 euro
store 200, monday, 17-20, 180 euro
...........

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-13 : 13:34:38

[code]
select transtore,DATENAME(dw,dateadd(dd,datediff(dd,0,transdatetime),0)) as thedate,
avg(ivalue*quantity) as avgPerdate,
avg(case when datepart(hh,transdatetime) >=8 and datepart(hh,transdatetime)<11 then ivalue*quantity else null end) as avgmorning,
avg(case when datepart(hh,transdatetime) >=11 and datepart(hh,transdatetime)<17 then ivalue*quantity else null end) as avgmidday,
avg(case when datepart(hh,transdatetime) >=17 and datepart(hh,transdatetime)<20 then ivalue*quantity else null end) as avgafternoon
from transactions a
inner join transactionItems b
on a.transID=b.transID
group by transtore,dateadd(dd,datediff(dd,0,transdatetime),0)
order by avgPerDate desc
[/code]

constantinos1987
Starting Member

25 Posts

Posted - 2008-12-13 : 21:30:51

this is the solution i think.... thank you all guys!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-13 : 22:11:41

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources