troubleshooting Question

change view to get one row instead of grid

Avatar of fwstealer
fwstealerFlag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2008
9 Comments1 Solution332 ViewsLast Modified:
the below returns a grid but just need it to return a single month with hrsexpiring such as below:
month                                   hrsexpiring
2012-07-01 00:00:00.000    675
----------

declare @Now AS Date
declare @EndDate AS Date
declare @Status as nvarchar(30)

--fy13    7/12 to 7/13
set @Now = '07/01/2012'
set @EndDate = '07/01/2013'

set @Status = 'Active'

SELECT     CAST(CONVERT(char(8), CASE WHEN datepart([dd], vw_Contracts.EndDate) = 1
                    THEN dateadd(dd, - 1, vw_Contracts.EndDate)
                    ELSE vw_Contracts.EndDate END, 120)
              + '01' AS datetime) AS MONTH,
            sum(vw_Contracts.AnnualAllocatedHours) AS HrsExpiring
FROM         ts_StatusTypes
INNER JOIN
                      vw_Contracts ON ts_StatusTypes.StatusTypeID = vw_Contracts.StatusTypeID
                      LEFT OUTER JOIN
                      tm_Accounts AS tm_Accounts_1
                      INNER JOIN
                      tr_AccountContracts ON tm_Accounts_1.AccountID = tr_AccountContracts.AccountID
                      ON vw_Contracts.ContractID = tr_AccountContracts.ContractID
                      LEFT OUTER JOIN
                      tm_Users AS tm_Users_1 ON vw_Contracts.CurrentSalesPersonUserID = tm_Users_1.UserID
                      LEFT OUTER JOIN
                      tl_Aircraft ON vw_Contracts.AircraftID = tl_Aircraft.AircraftID
WHERE    
(tm_Accounts_1.IsActive = 1)
AND (ts_StatusTypes.StatusType = @Status)
AND (vw_Contracts.ContractName <> 'TEMPLATE')
AND
(NOT (vw_Contracts.ContractID IN (656, 1947))) AND (vw_Contracts.ContractType = 'Fractional Program') AND (DATEADD(dd, - 1, vw_Contracts.EndDate)
 BETWEEN @Now AND @EndDate)
group by
CAST(CONVERT(char(8), CASE WHEN datepart([dd], vw_Contracts.EndDate) = 1 THEN dateadd(dd, - 1, vw_Contracts.EndDate) ELSE vw_Contracts.EndDate END, 120)
                      + '01' AS datetime)
ASKER CERTIFIED SOLUTION
Habib Pourfard
Senior Software Engineer
Join our community to see this answer!
Unlock 1 Answer and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros