Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Find Specific Value Within Groups

Posted on 2014-01-31
6
182 Views
Last Modified: 2014-01-31
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
Comment
Question by:Donovan Moore
  • 3
  • 3
6 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39825008
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
 

Author Comment

by:Donovan Moore
ID: 39825067
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39825087
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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

Author Comment

by:Donovan Moore
ID: 39825107
Thanks!
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 39825150
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
 

Author Closing Comment

by:Donovan Moore
ID: 39825182
Both excellent examples.  I really appreciate the help!
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question