SQL Server - Query to return records with max date within same month

1

Hi, I'm new to this forum and new to SQL Server!
I have read privilege only to the database. I need to pull the most recent 13 months of data each month, and get only the records with the max date within the same month. This is the original code without attempt at getting the max HealthCardDate:

Select
hc.MainCategory as "Category",
hc.CustomerName as "Customer",
DATENAME(Month,hc.MonthYearDate) as "Month of Health Card Date",
DATEPART(yyyy,hc.MonthYearDate) as "Year of Health Card Date",
hc.HealthCardDate as "Health Card Date",
case hc.CustomerName
when 'AVIVA CANADA INC' THEN 'ON'
when 'INNOVAPOST INC - CGI GROUP INC FOR CANADA POST' THEN 'TQ'
ELSE cc.RegionCode
END as "Region",
nullif(convert ( int,hc.HCRatingName), 0) as "Rating"

from dbo.internalhealthcard hc

left outer join
(select c.RegionCode, c.CustomerID
from dbo.Customer c) cc
on cc.CustomerID = hc.CustomerID

where hc.HealthCardDate >= Dateadd(Month, Datediff(Month, 0, DATEADD(m, -12, current_timestamp)), 0)
and hc.HCItemName in ('Billing', 'Governance and Service Management', 'Likelihood to Recommend',
'Request Fulfillment', 'Service Design', 'Service Reliability', 'Service Restoration', 'Overall satisfaction level with TEL as a service provider')

group by hc.CustomerName, hc.MainCategory, hc.ResourceName, DATENAME(Month, hc.MonthYearDate), DATEPART(yyyy, hc.MonthYearDate), cc.RegionCode, hc.HCRatingName, hc.HCItemName, hc.HealthCardDate
order by hc.CustomerName, hc.MainCategory

I've tried substituting hc.HealthCardDate with max(hc.HealthCardDate) but that doesn't seem to work. Can anyone help?

2

Please post consumable SQL code, like create table statement(s) or declare table variable(s) and insert(s) with sample data.
Thanks

3

Hi, sorry! Let me try this again. Thank you!

Here is sample raw data:
Table name = internalhealthcard
Customer Category MonthYearDate HealthCardDate RatingName Item CustomerID
AAA Billing March 2017 28/03/2017 12:00:00 AM 4 Billing 100
AAA Billing March 2017 28/03/2017 12:00:00 AM 5 Billing 100
AAA Billing March 2017 31/03/2017 12:00:00 AM 5 Fulfill 100
AAA Design April 2017 01/04/2017 12:00:00 AM 3 Joe 100
AAA Design April 2017 30/04/2017 12:00:00 AM 4 Joe 100
BBB Billing April 2017 02/04/2017 12:00:00 AM 5 Andy 200
BBB Billing April 2017 30/04/2017 12:00:00 AM 4 Dan 200

Table name = customer
CustomerID Customer RegionCode
100 AAA ON
200 BBB BC
300 CCC ON

Desired results:
Customer Category MonthYearDate HealthCardDate RatingName Item CustomerID
AAA Billing March 2017 31/03/2017 12:00:00 AM 5 Billing 100
AAA Design April 2017 30/04/2017 12:00:00 AM 4 Joe 100
BBB Billing April 2017 30/04/2017 12:00:00 AM 4 Dan 200

Here is the query without attempt to get max date within each month for the same customer::

Select
hc.Category as "Category",
hc.Customer as "Customer",
DATENAME(Month,hc.MonthYearDate) as "Month of Health Card Date",
DATEPART(yyyy,hc.MonthYearDate) as "Year of Health Card Date",
hc.HealthCardDate as "Health Card Date",
case hc.Customer
when 'DDD' THEN 'ON'
when 'EEE' THEN 'TQ'
ELSE cc.RegionCode
END as "Region",
nullif(convert ( int,hc.RatingName), 0) as "Rating"

from dbo.internalhealthcard hc

left outer join
(select c.RegionCode, c.CustomerID
from dbo.Customer c) cc
on cc.CustomerID = hc.CustomerID

where hc.HealthCardDate >= Dateadd(Month, Datediff(Month, 0, DATEADD(m, -12, current_timestamp)), 0)
and hc.HCItemName in ('Billing', 'Governance and Service Management', 'Likelihood to Recommend',
'Request Fulfillment', 'Service Design', 'Service Reliability', 'Service Restoration', 'Overall satisfaction level with TEL as a service provider')

group by hc.Customer, hc.Category, DATENAME(Month, hc.MonthYearDate), DATEPART(yyyy, hc.MonthYearDate), cc.RegionCode, hc.RatingName, hc.HealthCardDate
order by hc.Customer, hc.Category

4

With the sample data you provided, you'd get:

Customer Category MonthYearDate HealthCardDate         RatingName Item    CustomerID
AAA      Billing  March 2017    28/03/2017 12:00:00 AM 4          Billing 100
AAA      Billing  March 2017    28/03/2017 12:00:00 AM 5          Billing 100

Which one do you want displayed? or do you want both?

5

Hi bitsmed, thx for your reply. I must have made a mistake when I typed out the sample data. The 2nd row of my sample data should not have been there--i.e. for Customer AAA, Category Billing, there should have been just 2 records--one dated 28/03/2017 and one dated 31/03/2017. So sorry!

6

Try this:

select Customer
      ,Category
      ,[Month of Health Card Date]
      ,[Year of Health Card Date]
      ,[Health Card Date]
      ,Region
      ,Rating
  from (select hc.Customer
              ,hc.Category
              ,datename(month,hc.HealthCardDate) as [Month of Health Card Date]
              ,datepart(year,hc.HealthCardDate) as [Year of Health Card Date]
              ,hc.HealthCardDate as [Health Card Date]
              ,case hc.Customer
                  when 'DDD' then 'ON'
                  when 'EEE' then 'TQ'
                  else             cc.RegionCode
               end as Region
              ,nullif(convert(int,hc.RatingName),0) as Rating
              ,row_number() over(partition by hc.Customer
                                             ,hc.Category
                                     order by hc.HealthCardDate desc
                                 ) as rn
          from dbo.internalhealthcard as hc
               left outer join dbo.Customer as cc
                            on cc.CustomerID=hc.CustomerID
         where hc.HealthCardDate>=dateadd(month,datediff(month,0,current_timestamp)-12,0)
           and hc.ItemName in ('Billing'
                              ,'Governance and Service Management'
                              ,'Likelihood to Recommend'
                              ,'Request Fulfillment'
                              ,'Service Design'
                              ,'Service Reliability'
                              ,'Service Restoration'
                              ,'Overall satisfaction level with TEL as a service provider'
                              )
       ) as a
 where rn=1
;
1 Like
7

Hi bitsmed, thx again for your reply! The query runs, but it doesn't give
me the most recent 13 months of data for each customer (expect to get data
from Jun 2016 to Jun 2017). I think the new query might be grabbing only
the most recent record for each customer/category, which in most cases is a
date in May 2017. However, I am looking to get the most recent record for
each customer/category within each Month/Year period during the last 13
months, including current month. Is that possible? Should I upload
something to help clarify?

8

Try changing this:

              ,row_number() over(partition by hc.Customer
                                             ,hc.Category
                                     order by hc.HealthCardDate desc
                                 ) as rn

to this:

              ,row_number() over(partition by hc.Customer
                                             ,hc.Category
                                             ,datediff(month,0,hc.HealthCardDate)
                                     order by hc.HealthCardDate desc
                                 ) as rn
1 Like
9

Thank you bitsmed! This worked beautifully! Is there any way you can help me understand what the "as a" line does? I've been trying to find a reference within your code to "a" to try and see how it ties in with the rest of the script but haven't been able to.

10

When using a subquery, it is required that you name the subquery, and that is what I do, by creating the alias "a".

11

OIC. So we have to name the subquery even though we don't refer to the subquery name anywhere else in the script. Thanks again for all your help!

12

Well ... you do refer to it elsewhere ... it's just that all the alias / column names in the subquery are UNambiguous / unique, so there is no need to explicitly use the subquery name ... but you could:

select a.Customer
      ,a.Category
      ,a.[Month of Health Card Date]
      ,a.[Year of Health Card Date]
      ,a.[Health Card Date]
      ,a.Region
      ,a.Rating
  from (select hc.Customer
  ...
       ) as a
 where rn=1

and you would have to if there were multiple table / subqueries that had the same exposed column / alias names because otherwise they would be ambiguous :slight_smile:

1 Like
13

Ah yes of course! Thanks so much for the explanation!

1 Like