Link to home
Start Free TrialLog in
Avatar of fwstealer
fwstealerFlag for United States of America

asked on

change view to get one row instead of grid

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)
Avatar of Habib Pourfard
Habib Pourfard
Flag of New Zealand image

your query does not return a grid with the same month (2012-07-01 00:00:00.000), does it?
if you want to restrict result to show only (2012-07-01 00:00:00.000) you can add this to your where condition:
WHERE CONVERT(CHAR(7), vw_Contracts.EndDate, 120) = '2012-07'
Avatar of fwstealer

ASKER

no returns a full grid such as:

MONTH      HrsExpiring
2012-07-01 00:00:00.000      675
2012-08-01 00:00:00.000      1275
2012-09-01 00:00:00.000      950
2012-10-01 00:00:00.000      1125
2012-11-01 00:00:00.000      815
2012-12-01 00:00:00.000      1725
2013-01-01 00:00:00.000      975
2013-02-01 00:00:00.000      375
2013-03-01 00:00:00.000      925
2013-04-01 00:00:00.000      675
2013-05-01 00:00:00.000      925
2013-06-01 00:00:00.000      1500

I just want to return 1 row such as:
2012-07-01 00:00:00.000      675
if @Now is the date you need, try the following:

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 )
        AND CONVERT(CHAR(7), vw_Contracts.EndDate, 120) = CONVERT(CHAR(7), @Now, 120)
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)

Open in new window

odd - it returns 475 when the actual value is 675
what about this one:
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)
HAVING  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) = CAST(@Now AS DATETIME)

Open in new window

oh bugger - just forgot that I may need to query per month -- like pass in a parameter --

say i need sept, so i should just switch out the 07 and replace with '09/01/2012'; just the start date right
Well, yes @Now and @EndDate limit the query result. If you only want one date, just set @EndDate = @Now, not one year later as you did.

Bye, Olaf.
ASKER CERTIFIED SOLUTION
Avatar of Habib Pourfard
Habib Pourfard
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
All you will need to change is @EndDate = '08/01/2013', so you get everything counted and grouped being between 07/01 0:00AM and 08/01 0:00AM.

You can stay with your initial code, can't you?

Bye, Olaf.