Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2012
    Posts
    9

    Unanswered: Combining two selects (budget/actual)

    All, I have the below two queries. The top query calculates the ACTUAL HOURS by [Cottage] The bottom query calculates the AUTHORIZED HOURS by [Cottage]

    My goal is to create a report that compares AUTHORIZED HOURS to ACTUAL HOURS. My understanding of reports is that you can not put in one tablex more than one data source so the only way I can see to do this would be to make the query contain both budget and actual.





    The relationship between these two SELECT statements is between location description, and not any other key. ([CRM_CSLDB].[dbo].[CSL_CS+AWAY_Transactions].[Cottage] = [CRM_CSLDB].[dbo].[CSL_MAS_CSLPayrollDivisions].[Description])




    SELECT div.Description as [Cottage]
    ,sum([HoursReceiptAmtWages])as ActualHours
    FROM [Cinglenet-PC].[MAS90DB].[dbo].[View_CSL_AuthorizedStaffHours] as auth LEFT JOIN
    [CRM_CSLDB].[dbo].[CSL_MAS_CSLPayrollDivisions] as div ON auth.Dept = div.divisionID

    WHERE CheckDate = @checkdate
    GROUP BY div.Description
    ORDER BY div.Description




    SELECT Cottage, sum(DATEDIFF(day
    ,(CASE WHEN StartDate <= @paystart
    THEN @paystart
    ELSE StartDate END)
    ,(CASE WHEN StopDate >= @payend
    THEN @payend
    ELSE StopDate END)) * [HrsPerDay1]) as AuthorizedHours

    FROM [CRM_CSLDB].[dbo].[CSL_CS+AWAY_Transactions]

    WHERE caresttep1 <=StartDate AND (movein1 IS NOT NULL) AND
    (movein1 <= @paystart AND (StartDate <= @paystart) AND
    (moveout1 IS NULL OR moveout1 >= @payend) AND
    (StopDate >= @payend))

    GROUP BY Cottage
    ORDER BY Cottage


    Corinth 980.5000
    CSL 953.5000
    Decatur 1104.7500
    Florence 1627.7500
    Hartselle 1056.2500
    Hoover 1834.5000
    Huntsville 1646.0000
    Lawrenceburg 951.5000
    Montgomery 2235.7500
    Mountain Brook 1386.2500
    Russellville 841.5000


    Corinth 564.48
    Decatur 645.82
    Florence 1170.82
    Hartselle 758.24
    Hoover 748.16
    Huntsville 1088.08
    Lawrenceburg 615.02
    Montgomery 1414.70
    Mountain Brook 720.58
    Russellville 537.32

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT au.Cottage
         , au.AuthorizedHours
         , ac.ActualHours
      FROM ( SELECT Cottage
                  , SUM(DATEDIFF(DAY
                                ,CASE WHEN StartDate <= @paystart
                                      THEN @paystart
                                      ELSE StartDate END
                                ,CASE WHEN StopDate >= @payend
                                      THEN @payend
                                      ELSE StopDate END ) 
                      * [HrsPerDay1]) as AuthorizedHours
               FROM [CRM_CSLDB].[dbo].[CSL_CS+AWAY_Transactions]
              WHERE caresttep1 <=StartDate 
                AND movein1 IS NOT NULL 
                AND movein1 <= @paystart 
                AND StartDate <= @paystart 
                AND ( moveout1 IS NULL OR moveout1 >= @payend ) 
                AND StopDate >= @payend
             GROUP 
                 BY Cottage ) AS au
    LEFT OUTER
      JOIN ( SELECT div.Description as [Cottage]
                  , SUM([HoursReceiptAmtWages]) as ActualHours 
               FROM [Cinglenet-PC].[MAS90DB].[dbo].[View_CSL_AuthorizedStaffHours] as auth 
             LEFT 
               JOIN [CRM_CSLDB].[dbo].[CSL_MAS_CSLPayrollDivisions] as div 
                 ON div.divisionID = auth.Dept 
              WHERE CheckDate = @checkdate
             GROUP 
                 BY div.Description ) AS ac
        ON ac.Cottage = au.Cottage
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •