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?