July 4, 2014 at 4:07 am
Hi All,
Please help me to calculate Average sal foe below scenario.
I am having tables with 12 columns as jan,feb,.......dec.
Now I want to calculate average salary for each record, but condition is that if any month salary is zero then that column also exclude from average calculation.
For example : if jan and feb column values are zero then i want to calculate (mar+apr+...+dec)/10.
please help.
Thanks
Abhas.
July 4, 2014 at 4:18 am
Its not a good table design. Change the design if possible.
Please post some sample data and the DDL of the table involved.
This would help people to come up with good solutions faster.
Check the link in my signature if you are not sure on how to do this.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 4, 2014 at 4:19 am
do you have the data before it was pivoted/crosstabbed?
eg
emp1,jan,200
emp1,feb,220
emp2,jan,300
emp2,mar,305
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 4, 2014 at 4:27 am
Hi J Livingston
Exactly same...
Thanks
Abhas
July 4, 2014 at 4:32 am
As there was no DDL available , I assumed the DDL based on the first post
DECLARE@Salary TABLE
(
EmpIdINT,
JanNUMERIC(18,2),
FebNUMERIC(18,2),
MarNUMERIC(18,2),
AprNUMERIC(18,2),
MayNUMERIC(18,2),
JunNUMERIC(18,2),
JulNUMERIC(18,2),
AugNUMERIC(18,2),
SepNUMERIC(18,2),
OctNUMERIC(18,2),
NovNUMERIC(18,2),
[Dec]NUMERIC(18,2)
)
INSERT@Salary
VALUES( 1, 100.00, 200.00, 100.00, 200.00, 100.00, 200.00, 100.00, 200.00, 100.00, 200.00, 100.00, 200.00 ),
( 2, 100.00, 200.00, 100.00, 200.00, 100.00, 200.00, 100.00, 200.00, 100.00, 0.00, 100.00, 200.00 )
SELECTEmpId, AVG( NULLIF( Salary, 0 ) ) AS Average_Salary
FROM@Salary
UNPIVOT(
Salary FOR Months IN ( Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, [Dec] )
) AS UP
GROUP BY EmpId
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 4, 2014 at 9:51 am
Thanks Kingston Dhasian 🙂
its working for me. Thank you very much for your quick help
🙂
🙂
🙂
Thanks
Abhas
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply