help needed in solving this query error

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

help needed in solving this query error

Author

Topic

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2010-03-18 : 06:35:27

This is my query

select day1,SUM(ELIGIBILITYVOLUME),Elig from(
select dateName(weekDay,LOADDATE)as day1,SUM(ELIGIBILITYVOLUME) AS ELIG from streamS081_CareComm_SummaryDailyForTrendTransaction_All a,streamS081_CareComm_SummaryDailyForTrendTransaction_All b
where LOADDATE between dateadd(ww, -4, '02/27/2010')and dateadd(day, 1, '02/27/2010') group by dateName(weekDay,LOADDATE)) where
LOADDATE between dateadd(day, 6, '02/27/2010')and dateadd(day, 1, '02/27/2010')

i am sure that i made some mistake in this query. please could any one help me to solve this error

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'where'.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-18 : 06:51:06

duplicate where clause

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-18 : 06:54:31

and derived table is not closed: ) is missing before b.

Your code is ugly formatted it hurts my eyes!

No, you're never too old to Yak'n'Roll if you're too young to die.

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-18 : 06:55:07

and also this,
select day1,SUM(ELIGIBILITYVOLUME),Elig

remove this SUM(ELIGIBILITYVOLUME) from the select list.so
select day1,Elig

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2010-03-18 : 06:55:37

Hi webfred, please help me to remove the duplicate where clause.

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-18 : 06:57:03

y u need this?i don't see any reason for this?reomove it.

streamS081_CareComm_SummaryDailyForTrendTransaction_All b

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-18 : 06:57:37

Try

select 
	day1,
	Elig 
from(
	select dateName(weekDay,LOADDATE)as day1,SUM(ELIGIBILITYVOLUME) AS ELIG from streamS081_CareComm_SummaryDailyForTrendTransaction_All a,streamS081_CareComm_SummaryDailyForTrendTransaction_All b
	where LOADDATE between dateadd(ww, -4, '02/27/2010')and dateadd(day, 1, '02/27/2010') group by dateName(weekDay,LOADDATE)
) as t
where
	LOADDATE between dateadd(day, 6, '02/27/2010')and dateadd(day, 1, '02/27/2010')

Madhivanan

Failing to plan is Planning to fail

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2010-03-18 : 07:13:29

Hi madhi,

these are my two queries which gives two resluts. i need to join the query. please help me in this.

select dateName(weekDay,LOADDATE)as day1,SUM(ELIGIBILITYVOLUME) AS ELIG from streamS081_CareComm_SummaryDailyForTrendTransaction_All
where LOADDATE between dateadd(ww, -4, '02/27/2010')and dateadd(day, 1, '02/27/2010') group by dateName(weekDay,LOADDATE)

select dateName(weekDay,LOADDATE),ELIGIBILITYVOLUME as elig from streamS081_CareComm_SummaryDailyForTrendTransaction_All
where LOADDATE between dateadd(day, -6, '02/27/2010')and dateadd(day, 1, '02/27/2010')

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-18 : 07:15:57

Is this?

select dateName(weekDay,LOADDATE)as day1,SUM(ELIGIBILITYVOLUME) AS ELIG from streamS081_CareComm_SummaryDailyForTrendTransaction_All
where LOADDATE between dateadd(ww, -4, '02/27/2010')and dateadd(day, 1, '02/27/2010') group by dateName(weekDay,LOADDATE)
union all
select dateName(weekDay,LOADDATE),ELIGIBILITYVOLUME as elig from streamS081_CareComm_SummaryDailyForTrendTransaction_All
where LOADDATE between dateadd(day, -6, '02/27/2010')and dateadd(day, 1, '02/27/2010')

Madhivanan

Failing to plan is Planning to fail

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2010-03-18 : 07:33:38

No madhi,

The first query will return the following result

day1 Elig
Friday 2150
Monday 1800
Saturday 2800
Sunday 2200
Thursday 2550
Tuesday 2100
Wednesday 2700

the second query will return
day1 Elig2
Saturday 300
Friday 300
Thursday 400
Wednesday 250
Tuesday 150
Monday 450
Sunday 550

I need result as

day1 Elig Elig2
Friday 2150 300
Monday 1800 300
Saturday 2800 400
Sunday 2200 250
Thursday 2550 150
Tuesday 2100 450
Wednesday 2700 550

could you please help me in this

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2010-03-18 : 07:36:06

concept is i want to group the day1 from query1 and query 2 .

my final out put as

day1 elig elig2

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-18 : 07:39:09

select rowno=row_number()over(order by (select 1)),day1 Elig,t2.elig2 from tbl as t1 inner join
(
select rowno=row_number()over(order by (select 1)),day1 Elig2 from tbl
) as t2
on
t1.rowno=t2.rowno

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources