Improve company productivity with a Business Account.Sign Up

x
?
Solved

t-sql: group by statement

Posted on 2012-03-20
2
Medium Priority
?
279 Views
Last Modified: 2012-03-20
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
Comment
Question by:Paula DiTallo
2 Comments
 
LVL 58

Accepted Solution

by:
amit_g earned 2000 total points
ID: 37743239
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
 

Author Closing Comment

by:Paula DiTallo
ID: 37743641
Awesome!:-)
0

Featured Post

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.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

607 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