is this possible? [RESOLVED] - 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

SQL Server 2000 Forums

Transact-SQL (2000)

is this possible? [RESOLVED]

Author

Topic

cjonline
Yak Posting Veteran

55 Posts

Posted - 2009-11-23 : 10:06:37

I have the following query


Select blah,blah,blah,case when untildate > '31-march-2009' then datediff(d,fromdate,'31-march-2009') else totaldays end as sickdays
from absences
where blah = blah etc etc.

this works correctly, however, I require to do a bit more of a calculation with the sickdays within the select statement.

ie.


Select blah,blah,blah,case when untildate > '31-march-2009' then datediff(d,fromdate,'31-march-2009') else totaldays end as sickdays,
case when sickdays < 27 then sickdays else 0 end as shorttermsick

from absences
where blah = blah etc etc.

Now, this doesnt work as I cannot evaluate the sickdays field at this point.. any ideas what I can do for this?

thanks
C.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-23 : 10:15:44

it is with small modification


select *,case when sickdays < 27 then sickdays else 0 end as shorttermsick
from
(
Select blah,blah,blah,case when untildate > '31-march-2009' then datediff(d,fromdate,'31-march-2009') else totaldays end as sickdays
from absences
where blah = blah etc etc.
)t

cjonline
Yak Posting Veteran

55 Posts

Posted - 2009-11-23 : 10:24:22

error :
Server: Msg 8155, Level 16, State 2, Line 1
No column was specified for column 2 of 't'.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-11-23 : 10:34:50

Show us your actual query as the error is coming from a part you haven't shown us.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

cjonline
Yak Posting Veteran

55 Posts

Posted - 2009-11-23 : 10:42:48

[code]
select *,case when sickdays < 27 then sickdays else 0 end as shorttermsick
from
(
Select e.division,CONVERT(varchar,d.fromdate,103),e.employ_ref,
case when d.untildate > '31-march-2009' then datediff(d,d.fromdate,'31-march-2009') else totaldays end as sickdays,
case when d.totaldays < 27 then totaldays else 0 end as days_lost_nonlts,
case when d.totaldays >= 27 then totaldays else 0 end as days_lost_lts,
count(distinct (case when d.totaldays < 27 then d.absence_ref end)) as no_of_nonlts,
count(distinct (case when d.totaldays >= 27 then d.absence_ref end)) as no_of_lts,
count(distinct d.absence_ref) as no_of_absences
from
absence d,
employee_table e,
where
d.fromdate >= '1-april-2008'
and d.fromdate <= '31-march-2009'
and d.employ_ref = e.employ_ref
and e.employ_ref = j.employ_ref
group by e.division, d.fromdate,e.employ_ref,e.department,d.totaldays,d.untildate
) t
[/code]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-11-23 : 11:19:59

You need to alias the second column in your derived table: CONVERT(varchar,d.fromdate,103) as fromdate.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-23 : 11:21:19

quote:

Originally posted by cjonline


select *,case when sickdays < 27 then sickdays else 0 end as shorttermsick
from
(
Select e.division,CONVERT(varchar,d.fromdate,103) as fromdate,e.employ_ref,
case when d.untildate > '31-march-2009' then datediff(d,d.fromdate,'31-march-2009') else totaldays end as sickdays,
case when d.totaldays < 27 then totaldays else 0 end as days_lost_nonlts,
case when d.totaldays >= 27 then totaldays else 0 end as days_lost_lts,
count(distinct (case when d.totaldays < 27 then d.absence_ref end)) as no_of_nonlts,
count(distinct (case when d.totaldays >= 27 then d.absence_ref end)) as no_of_lts,
count(distinct d.absence_ref) as no_of_absences
from 
	absence d, 
	employee_table e,
where 	
	d.fromdate >= '1-april-2008'
	and d.fromdate <= '31-march-2009'
	and d.employ_ref = e.employ_ref
	and e.employ_ref = j.employ_ref
group by e.division, d.fromdate,e.employ_ref,e.department,d.totaldays,d.untildate
) t

give 2nd field as an alias

cjonline
Yak Posting Veteran

55 Posts

Posted - 2009-11-23 : 11:25:16

Thanks folks, that was the problem right enough!!!.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-23 : 11:32:23

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources