sql query with sum but with conditions

this is part of a vb.net project.. the only part that matters there is that the user selects a year from a combobox as selectedyear

I have a table that has the following information (that I care about)
age, NetAc (net acres), Plan_Year, Report year

I have three scenarios

the first is pulling all records summing the NetAc and grouping by Age - no problem there
Select (age), CONVERT(int,ROUND(SUM(NetAc),0)) as TotalAcres from HarvestUnits where IsNull(Harv_Type,'')not like '%2' and (Owner like 'L%' or Owner like 'J%') group by Age order by Age

the next two I have no idea how to pull of... I need to do the same thing, but if selected year < Plan year I need to subtract report year from selected year and add the result to Age before it groups

the last one is if selected year is = or > plan year then subtract plan year from Selected Year and then subtract 1 and that becomes the age

Her it is in a little simpler form - the part I am not sure how to deal with is because I need to do the comparison for the second two for every record
If Selected Year = Report Year then
              Run report as is

If Selected Year < Plan Year then
              Selected year – Report Year = result
              Age + result

If Selected year >= Plan Year then
              Selected year – Plan Year – 1 = result
              Age = result

Open in new window

David ModugnoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David ModugnoAuthor Commented:
any help would be great experts... i am really stuck here
thanks
0
PortletPaulfreelancerCommented:
OK, I have the basic concept of what you want I think, but not a good grasp on the detail, so I'll just try to point you in a direction that will help I think

It is perfectly OK to use a case expression INSIDE the SUM() function, E.G.

SELECT
      (age)
    , CONVERT(int, ROUND(
                  SUM(CASE
                                    WHEN X = 1 THEN NetAc
                                    WHEN X = 2 THEN NetAc
                                    ELSE NetAc
                              END
)
                  , 0)) AS TotalAcres
FROM HarvestUnits ...
0
James0628Commented:
As I understand it, you're trying to change the grouping based on selectedyear.  There may be (probably are :-) better ways to handle it, but I think something like this will work.

Select
CASE
  WHEN SelectedYear = ReportYear THEN
    Age
  WHEN SelectedYear < PlanYear THEN
    Age + SelectedYear - ReportYear
  WHEN SelectedYear >= PlanYear THEN
    SelectedYear - PlanYear - 1
END AS Age,
CONVERT(int,ROUND(SUM(NetAc),0)) as TotalAcres
from HarvestUnits 
where  IsNull(Harv_Type,'')not like '%2'  and (Owner like 'L%' or Owner like 'J%')
group by
CASE
  WHEN SelectedYear = ReportYear THEN
    Age
  WHEN SelectedYear < PlanYear THEN
    Age + SelectedYear - ReportYear
  WHEN SelectedYear >= PlanYear THEN
    SelectedYear - PlanYear - 1
END
order by
CASE
  WHEN SelectedYear = ReportYear THEN
    Age
  WHEN SelectedYear < PlanYear THEN
    Age + SelectedYear - ReportYear
  WHEN SelectedYear >= PlanYear THEN
    SelectedYear - PlanYear - 1
END

Open in new window


 FWIW, you could replace the last WHEN in each CASE with ELSE:

CASE
  WHEN SelectedYear = ReportYear THEN
    Age
  WHEN SelectedYear < PlanYear THEN
    Age + SelectedYear - ReportYear
  ELSE
    SelectedYear - PlanYear - 1
END

 I don't know if it makes a real difference, but an ELSE seems simpler (ie. possibly faster) than doing the SelectedYear >= PlanYear test.

 And, of course, if you wanted to include the original Age column for some reason, you could do that, and either give that column a different name (eg. Age AS Original_Age), or rename the result from the Case (eg. Calculated_Age).

 James
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

David ModugnoAuthor Commented:
James that worked great... any chance you could explain it a bit... I have never seen case statements in the order by or group by... I want to make sure I understand it for the future

thanks so much for your help
0
James0628Commented:
There's not much to explain really.  You can use a Case to choose the value for a column in each row of the query results.  I'm just doing the same thing in the Group By and Order By.  It's just something that I tried once upon a time, when I had a similar situation (a varying column in the results that was also needed for the Group/Order By), and it worked.  That's why I said that there was probably a better way to handle it.  :-)  I just tried that and it worked, with no problems that I could see (in my situation), so I didn't look for another solution.  I suspect that there's a more efficient way to handle that kind of thing.

 And you're welcome.  :-)

 James
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
There is no better way to handle it.

You need case expressions to handle the requirements and you repeat those in the group by and order by clauses.

A case expression,  is called an expression because it supplies a single result.  There is no performance penalty in using case expressions this way either. It just looks a bit ungainly perhaps.

Using my phone... forgive spelling etc please
0
James0628Commented:
Thanks Paul.  It just seemed too simple.  Not necessarily pretty, but simple.  :-)  I thought there might be some "trickier" way to handle it that would be more efficient.

 James
0
PortletPaulfreelancerCommented:
It is only an "apparent lack" of efficiency. You could do this, which looks prettier, but isn't any more efficient:
SELECT
      age
    , CONVERT(int, ROUND(SUM(NetAc), 0)) AS TotalAcres
FROM (
      SELECT
            CASE
                  WHEN SelectedYear = ReportYear THEN Age
                  WHEN SelectedYear < PlanYear THEN Age + SelectedYear - ReportYear
                  WHEN SelectedYear >= PlanYear THEN SelectedYear - PlanYear - 1
            END AS Age
          , NetAc
      FROM HarvestUnits
      WHERE ISNULL(Harv_Type, '') NOT LIKE '%2'
            AND (Owner LIKE 'L%'
            OR Owner LIKE 'J%')
    ) AS D
GROUP BY
      Age
ORDER BY
      Age

Open in new window

In this variant, the alias [Age] carries with it the whole case expression in the background so it is still the same query to your dbms server
0
James0628Commented:
Are you sure about using the alias in the Group By?  I'd read, years ago, that that wasn't allowed, but I haven't tried it in a more recent version of MS SQL.

 James
0
PortletPaulfreelancerCommented:
Using a derived table: the alias can be resolved by all clauses in the outer query
0
James0628Commented:
Oh.  Damn.  Sorry.  Obviously I wasn't paying attention.  :-)  I just looked at the Group By and Order By and didn't really look at the rest of it.

 James
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.