January 18, 2011 at 9:25 am
I have a situation where I need to calculate the avg sal only when I have a recent value.
i.e,
table:
id reportingperiod sal
1 jan 2000
1 aug 2200
2 jan 0
2 aug 2000
I want the result to be:
id avg(sal)
1 2100
2 0
I tried alot using case statements but I think I am missing some thing there..
Please help me...
Thanks.
January 18, 2011 at 9:47 am
Why not using AVG() ... GROUP BY?
Edit: and what do you consider as being a "recent value"? You'd need a date to compare against, but I can't find one: The month name by itself can be from any year...
January 18, 2011 at 9:50 am
try this
select id, avg(sal) from @temp
where sal <> 0
group by id
having count(reportingperiod) = 2
declare variable to store reportingperiod. in above example i have used constant 2
January 18, 2011 at 9:54 am
I can but I have a condition involved which is, when reportingperiod is jan and sal=0 then avg(sal) should be zero even if the reportingperiod apr sal has some value..
January 18, 2011 at 9:57 am
SQLSmasher (1/18/2011)
try thisselect id, avg(sal) from @temp
where sal <> 0
group by id
having count(reportingperiod) = 2
declare variable to store reportingperiod. in above example i have used constant 2
WHa?
non - zero Salary. I'm with ya.
Group on the ID, I'm with ya.
count( reportingperiod) = 2 with a variable to store ... nono, stop there. What? This gets, at best, an incredibly limited subset of the data. You want to go look deeper into the having clause and its usage.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 18, 2011 at 9:59 am
sravanb (1/18/2011)
I can but I have a condition involved which is, when reportingperiod is jan and sal=0 then avg(sal) should be zero even if the reportingperiod apr sal has some value..
Can you post the real DDL to your table, as well as the full business rules as you know it?
So far we have a vague reference to 'recent' entries, with no definition nor enforcable by the original ddl.
Now we have a new business rule you're trying to solve, which wasn't mentioned at all originally.
Take a breath, take a step back, and go through what you need from start to finish. It will be much easier for us to help you from that point with examples of the real structure and queries you've created so far.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 18, 2011 at 10:03 am
additionally, we have a duplicate thread.
After reevaluating the issues as Craig recommended, please take the time and read the first article referenced in my (and Craigs) signature on how to ask questions on a forum.
And, please, don't start another thread for the very same issue. Thank you.
January 18, 2011 at 12:34 pm
DDL for the table:
sal(id int,reportingperiod varchar(10),salary int)
Business rule:
Average salary should be calculated only when if the employees have salary in reportingperiod month january.
Table values:
id reportingperiod sal
1 jan 2000
1 aug 2200
2 jan 0
2 aug 1000
Result:
id avg(sal)
1 2100
2 0
Thank you.
January 18, 2011 at 1:05 pm
sravanb (1/18/2011)
DDL for the table:sal(id int,reportingperiod varchar(10),salary int)
Business rule:
Average salary should be calculated only when if the employees have salary in reportingperiod month january.
This is untested since the ddl and data aren't consumable, but you want something like:
SELECT
id,
CASE WHEN drvNonZeroLimiter.id IS NOT NULL
THEN AVG( sal.salary)
ELSE 0
END AS AvgSalary
FROM
sal
LEFT JOIN
(SELECT DISTINCT
id
FROM
sal
WHERE
reportingPeriod = 'Jan'
AND salary <> 0
) AS drvNonZeroLimiter
ON sal.id = drvNonZeroLimiter.id
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 18, 2011 at 2:13 pm
Thanks craig!!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply