Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 199
  • Last Modified:

Find Specific Value Within Groups

I need to find a specific value based on two different values in a table.

The table is a date dimension within an Analysis cube; I am attempting to find the last month within each quarter.  Here is some sample data:

MonthNumberOfYear  BillingQuarter  CalendarSemester
1                  1               1
2                  1               1
12                 1               2
3                  2               1
4                  2               1
5                  2               1
6                  3               1
7                  3               2
8                  3               2
9                  4               2
10                 4               2
11                 4               2

Open in new window



The issue lies with the fact that December (MonthNumberOfYear) is equal to 12; trying to do a MAX() on the MonthNumberOfYear and including the BillingQuarter implies that December is the last month, when in reality, February (2) is the last month.

My thought was to find both the MIN(CalendarSemester) and then MAX(MonthNumberOfYear), but am having issues making this work correctly.

What I need in the end is:

MonthNumberOfYear  BillingQuarter
2                  1
5                  2
8                  3
11                 4

Open in new window



Any and all help would be greatly appreciated.  Please let me know if there are any questions.

I'm using SQL 2008 R2.
0
Donovan Moore
Asked:
Donovan Moore
  • 3
  • 3
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
In T-SQL, one simple way to get the results you have is to GROUP BY BillingQuarter, then SELECT MAX(MonthNumberOfYear).

SELECT MonthNumberOfYear = MAX(MonthNumberOfYear)
     , BillingQuarter
FROM your_table
GROUP BY BillingQuarter
;

Open in new window

0
 
Donovan MooreAuthor Commented:
Thanks - but the issue is that December becomes the last month in the quarter, but the way the billing is setup as a December through February quarter.  So, February is the last month in the quarter.  This is why I included using the CalendarSemester as an additional field; finding the minimum CalendarSemester value within the BillingQuarter group would theoretically find February as the maximum MonthNumberOfYear where CalendarYear is equal to '1'.
0
 
Kevin CrossChief Technology OfficerCommented:
Yes, sorry.  I read that and then thought I was over-complicating it.
Okay, you can do this in a couple steps, I will post an example shortly.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Donovan MooreAuthor Commented:
Thanks!
0
 
Kevin CrossChief Technology OfficerCommented:
One way is to use similar GROUP BY statement, but add a filter to make sure that you always get the row with the lowest CalendarSemester by BillingQuarter.
SELECT MonthNumberOfYear = MAX(MonthNumberOfYear)
     , BillingQuarter
FROM your_table a
WHERE NOT EXISTS (
    SELECT 1
    FROM your_table b
    WHERE b.BillingQuarter = a.BillingQuarter
    AND b.CalendarSemester < a.CalendarSemester
)
GROUP BY BillingQuarter
;

Open in new window


Another approach uses windowing function to rank records based on the CalendarSemester and MonthNumberOfYear.  With the correct ORDER BY, the latest month for each BillingQuarter becomes RN = 1.
SELECT MonthNumberOfYear, BillingQuarter, CalendarSemester
FROM (
    SELECT MonthNumberOfYear, BillingQuarter, CalendarSemester
         , RN = ROW_NUMBER() 
             OVER(PARTITION BY BillingQuarter
                  ORDER BY CalendarSemester, MonthNumberOfYear DESC)
    FROM your_table
) derived
WHERE RN = 1
;

Open in new window


There likely are many other ways, but I hope these help.
0
 
Donovan MooreAuthor Commented:
Both excellent examples.  I really appreciate the help!
0
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.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now