• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 277
  • Last Modified:

t-sql: group by statement

Techies--
 This statement ALMOST works.  It works for conditions where the the max(BusinessDate) is the same for all the Facilities -- however, there may be a condition where the max(BusinessDate) for FacilityId  1 = '2012-03-20' , but for FacilityId 2 = '2012-02-28'.  How do I pick up the max(BusinessDate) for each facility?

select x.FacilityId,
       x.BusinessDate as StartOfPeriodDate,
       x.Value as ValueofActiveEmpl,
       y.BusinessDate as EndOfPeriodDate,
       y.Value as ValueofActiveEmpl
  from aggregated.DailyReportCategoryDataByFacility x
    inner join aggregated.DailyReportCategoryDataByFacility y on x.FacilityId = y.FacilityId
 where x.reportcategoryid = 239
   and x.BusinessDate = '2011-12-21'
   and y.reportcategoryid = 239
   and y.BusinessDate =
     (select max(z.BusinessDate)
        from aggregated.DailyReportCategoryDataByFacility z
         where z.reportcategoryid = 239
      )
   order by x.FacilityId

Open in new window

0
Paula DiTallo
Asked:
Paula DiTallo
1 Solution
 
amit_gCommented:
select x.FacilityId,
       x.BusinessDate as StartOfPeriodDate,
       x.Value as ValueofActiveEmpl,
       y.BusinessDate as EndOfPeriodDate,
       y.Value as ValueofActiveEmpl
  from aggregated.DailyReportCategoryDataByFacility x
    inner join aggregated.DailyReportCategoryDataByFacility y on x.FacilityId = y.FacilityId
    inner join (select z.FacitlityId, max(z.BusinessDate) MaxBusinessDate
        from aggregated.DailyReportCategoryDataByFacility z
         where z.reportcategoryid = 239
         group by z.FacitlityId
      ) T on x.FacitlityId = T.FacitlityId and y.BusinessDate = T.MaxBusinessDate
 where x.reportcategoryid = 239
   and x.BusinessDate = '2011-12-21'
   and y.reportcategoryid = 239
   order by x.FacilityId
                                  

Open in new window

0
 
Paula DiTalloIntegration developerAuthor Commented:
Awesome!:-)
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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