view(not solved ) - 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 2008 Forums

Transact-SQL (2008)

view(not solved )

Author

Topic

Arun.G
Yak Posting Veteran

81 Posts

Posted - 2010-07-01 : 06:15:34

i have one table like below:

emp_id year leave_eligible leave_avialed
1 2010 10 5
1 2009 15 14

like that,

but now in view i want result like bleow:

emp_id year leave_eligible leave_availed leave_balance
1 current year 10 5 5
1 past year 15 14 1

where leave_balance is (leave_eligible minus leave_availed) should calculate automatically

and instead of 2010 i want to add as current year
and for 2009 as past year

how to do it?

Sachin.Nand

2937 Posts

Posted - 2010-07-01 : 06:18:29

What if year is less that 2009?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Arun.G
Yak Posting Veteran

81 Posts

Posted - 2010-07-01 : 06:32:14

ITS NOT A PROBLEM , V R DISPLAYING THAT RESULTS BY TAKING @YEAR AS INPUT PARAMETER

@YEAR=2010 MEANS 2010 IS CURRENTYEAR AND 2009 IS PASTYEAR

Sachin.Nand

2937 Posts

Posted - 2010-07-01 : 06:44:08

select emp_id,
case year when '2010' then 'current year'
when '2009' then ' past year' end as year,
(leave_eligible - leave_avialed)as leave_balance
from yourtable

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Arun.G
Yak Posting Veteran

81 Posts

Posted - 2010-07-01 : 06:59:53

ONE MORE DOUBT,

(LEAVE_ELIGIBLE-LEAVE_AVAILED) AS LEAVE_BALANCE

BUT THERE IS NO COLUMN AS LEAVE_ELIBILE AND LEAVE_AVAILED

LEAVE_ELIGIBLE IS ALSO CALCULATED FIELD LABLELLED AS LEAVE_ELIGIBLE
AND LEAVE_AVAILED ALSO CALCULATED FIELD AND LABLEED AS LEAVE_AVIALED

FOR EXAMPLE:

EMP_CARRYFORWARD AS LEAVE_ELIGIBLE
EMP_CARRYFORWARD/12)*(MONTH) AS LEAVE_AVAILED

THEN HOW TO CALCULATED DIFFERNCE BETWEEN LEAVE_ELIGIBLE-LEAVE_AVAILED?

Sachin.Nand

2937 Posts

Posted - 2010-07-01 : 07:03:11

Please post some sample data.Like this we will just keep guessing on how your data is structured.Also post the query if you are using any.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Arun.G
Yak Posting Veteran

81 Posts

Posted - 2010-07-01 : 07:09:25

my query:

SELECT
B.LEAVETYPE_NAME AS LEAVETYPE,
CASE A.CARRYFORWARD_YEAR WHEN '2010' THEN 'CURRENT YEAR'
WHEN '2009' THEN ' PAST YEAR' END YEAR ,
A.EMP_CARRYFORWARD AS LEAVE_ELIGIBLE,
(A.EMP_CARRYFORWARD/12)*(C.MONTH) AS LEAVE_ACCRUED,
C.NO_DAYS AS LEAVE_AVAILED

FROM
LEAVE_CARRYFORWARD A,
MASTER_LEAVETYPE B,
LEAVE C

WHERE
A.EMP_ID=1
AND C.LEAVE_STATUSID=1
AND A.EMP_ID=C.EMP_ID
AND A.LEAVETYPE_ID=B.LEAVETYPE_ID
AND A.LEAVETYPE_ID=C.LEAVETYPE_ID
AND C.YEAR=A.CARRYFORWARD_YEAR
AND A.CARRYFORWARD_YEAR IN(2010,2009)
AND C.DEL_FLAG=0

now i want leave_balance = (LEAVE_ELIGIBLE-LEAVE_ACCRUED)

Sachin.Nand

2937 Posts

Posted - 2010-07-01 : 07:12:16

quote:
Originally posted by Idera

select emp_id,
case year when '2010' then 'current year'
when '2009' then ' past year' end as year,
(leave_eligible - leave_avialed)as leave_balance
from
(

Your Original Query

)
T

See the red part marked above.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Arun.G
Yak Posting Veteran

81 Posts

Posted - 2010-07-01 : 07:20:09

its not working.

Sachin.Nand

2937 Posts

Posted - 2010-07-01 : 07:25:16

quote:
Originally posted by Arun.G

its not working.

What is the error?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Arun.G
Yak Posting Veteran

81 Posts

Posted - 2010-07-01 : 07:30:31

SELECT (LEAVE_ELIGIBLE-LEAVE_ACCRUED) AS LEAVE_BALANCE
from
(
SELECT
B.LEAVETYPE_NAME AS LEAVETYPE,
CASE A.CARRYFORWARD_YEAR WHEN '2010' THEN 'CURRENT YEAR'
WHEN '2009' THEN ' PAST YEAR' END YEAR ,
A.EMP_CARRYFORWARD AS LEAVE_ELIGIBLE,
(A.EMP_CARRYFORWARD/12)*(C.MONTH) AS LEAVE_ACCRUED,
C.NO_DAYS AS LEAVE_AVAILED

FROM
LEAVE_CARRYFORWARD A,
MASTER_LEAVETYPE B,
LEAVE C

WHERE
A.EMP_ID=1
AND C.LEAVE_STATUSID=1
AND A.EMP_ID=C.EMP_ID
AND A.LEAVETYPE_ID=B.LEAVETYPE_ID
AND A.LEAVETYPE_ID=C.LEAVETYPE_ID
AND C.YEAR=A.CARRYFORWARD_YEAR
AND A.CARRYFORWARD_YEAR IN(2010,2009)
AND C.DEL_FLAG=0
)

BUT ITS DISPLAYING:

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

Sachin.Nand

2937 Posts

Posted - 2010-07-01 : 07:35:58

quote:
Originally posted by Arun.G

SELECT (LEAVE_ELIGIBLE-LEAVE_ACCRUED) AS LEAVE_BALANCE
from
(
SELECT
B.LEAVETYPE_NAME AS LEAVETYPE,
CASE A.CARRYFORWARD_YEAR WHEN '2010' THEN 'CURRENT YEAR'
WHEN '2009' THEN ' PAST YEAR' END YEAR ,
A.EMP_CARRYFORWARD AS LEAVE_ELIGIBLE,
(A.EMP_CARRYFORWARD/12)*(C.MONTH) AS LEAVE_ACCRUED,
C.NO_DAYS AS LEAVE_AVAILED

FROM
LEAVE_CARRYFORWARD A,
MASTER_LEAVETYPE B,
LEAVE C

WHERE
A.EMP_ID=1
AND C.LEAVE_STATUSID=1
AND A.EMP_ID=C.EMP_ID
AND A.LEAVETYPE_ID=B.LEAVETYPE_ID
AND A.LEAVETYPE_ID=C.LEAVETYPE_ID
AND C.YEAR=A.CARRYFORWARD_YEAR
AND A.CARRYFORWARD_YEAR IN(2010,2009)
AND C.DEL_FLAG=0
)T

BUT ITS DISPLAYING:

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

You missed the table aliase T marked in red above.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Arun.G
Yak Posting Veteran

81 Posts

Posted - 2010-07-01 : 07:48:24

THX AMN, ITS DISPLAYNG,

but the problem is

i want results to dsiplay like below:

year leave_eligible leave_accrued leave_availed leave_balance

currentyear 30 24 5 21
past year - 5 - 5

like that i want, how to do it?

Sachin.Nand

2937 Posts

Posted - 2010-07-01 : 07:51:27

[code]
select emp_id,
case year when '2010' then 'current year'
when '2009' then ' past year' end as year,
leave_eligible,
leave_avialed,
(leave_eligible - leave_avialed)as leave_balance
from
(

Your Query

)
T
[/code]

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Arun.G
Yak Posting Veteran

81 Posts

Posted - 2010-07-02 : 06:37:46

its displaying the results, but instead of 7 records its displaying 49 records.

dont able to trace whats missing

again im sending the query which i used:

SELECT
B.LEAVETYPE_NAME AS LEAVETYPE,
CASE A.CARRYFORWARD_YEAR WHEN '2010' THEN 'CURRENT YEAR'
WHEN '2009' THEN 'PAST YEAR' END YEAR,
C.NO_DAYS AS LEAVE_AVAILED,
LEAVE_ELIGIBLE,
LEAVE_ACCRUED,
(LEAVE_ELIGIBLE - LEAVE_ACCRUED)AS LEAVE_BALANCE
FROM
(

SELECT
B.LEAVETYPE_NAME AS LEAVETYPE,
A.EMP_CARRYFORWARD AS LEAVE_ELIGIBLE,
(A.EMP_CARRYFORWARD/12)*(C.MONTH) AS LEAVE_ACCRUED,
C.NO_DAYS AS LEAVE_AVAILED

FROM
LEAVE_CARRYFORWARD A,
LEAVETYPE B,
LEAVE C

WHERE
A.EMP_ID=1
AND C.LEAVE_STATUSID=1
AND A.EMP_ID=C.EMP_ID
AND A.LEAVETYPE_ID=B.LEAVETYPE_ID
AND A.LEAVETYPE_ID=C.LEAVETYPE_ID
AND C.YEAR=A.CARRYFORWARD_YEAR
AND A.CARRYFORWARD_YEAR IN(2010,2009)
AND C.DEL_FLAG=0
) AS TEMP,

LEAVE_CARRYFORWARD A,
LEAVETYPE B,
LEAVE C,
LEAVESTATUS D

WHERE
A.EMP_ID=1
AND C.LEAVE_STATUSID=1
AND A.EMP_ID=C.EMP_ID
AND C.LEAVE_STATUSID=D.LEAVESTATUS_ID
AND A.LEAVETYPE_ID=B.LEAVETYPE_ID
AND A.LEAVETYPE_ID=C.LEAVETYPE_ID
AND C.YEAR=A.CARRYFORWARD_YEAR
AND C.YEAR IN (2010,2009)
AND C.DEL_FLAG=0

pls help me to solve this

Sachin.Nand

2937 Posts

Posted - 2010-07-02 : 09:52:31

Why have you again repeated the joins?


SELECT
B.LEAVETYPE_NAME AS LEAVETYPE,
CASE A.CARRYFORWARD_YEAR WHEN '2010' THEN 'CURRENT YEAR'
WHEN '2009' THEN 'PAST YEAR' END YEAR, 
C.NO_DAYS AS LEAVE_AVAILED, 
LEAVE_ELIGIBLE,
LEAVE_ACCRUED,
(LEAVE_ELIGIBLE - LEAVE_ACCRUED)AS LEAVE_BALANCE
FROM 
(

SELECT 
B.LEAVETYPE_NAME AS LEAVETYPE,
A.EMP_CARRYFORWARD AS LEAVE_ELIGIBLE,
(A.EMP_CARRYFORWARD/12)*(C.MONTH) AS LEAVE_ACCRUED,
C.NO_DAYS AS LEAVE_AVAILED

FROM 

LEAVE_CARRYFORWARD A,
LEAVETYPE B,
LEAVE C 

WHERE 
A.EMP_ID=1
AND C.LEAVE_STATUSID=1
AND A.EMP_ID=C.EMP_ID
AND A.LEAVETYPE_ID=B.LEAVETYPE_ID
AND A.LEAVETYPE_ID=C.LEAVETYPE_ID
AND C.YEAR=A.CARRYFORWARD_YEAR
AND A.CARRYFORWARD_YEAR IN(2010,2009)
AND C.DEL_FLAG=0 

) AS TEMP,

LEAVE_CARRYFORWARD A,
LEAVETYPE B,
LEAVE C,
LEAVESTATUS D

WHERE 
A.EMP_ID=1
AND C.LEAVE_STATUSID=1
AND A.EMP_ID=C.EMP_ID
AND C.LEAVE_STATUSID=D.LEAVESTATUS_ID
AND A.LEAVETYPE_ID=B.LEAVETYPE_ID
AND A.LEAVETYPE_ID=C.LEAVETYPE_ID
AND C.YEAR=A.CARRYFORWARD_YEAR
AND C.YEAR IN (2010,2009)
AND C.DEL_FLAG=0

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources