Avatar of fwstealer
fwstealer
Flag 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)
Microsoft SQL ServerMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
Olaf Doschke

8/22/2022 - Mon
Habib Pourfard

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'
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
Habib Pourfard

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

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
fwstealer

ASKER
odd - it returns 475 when the actual value is 675
Habib Pourfard

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

fwstealer

ASKER
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Olaf Doschke

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
Habib Pourfard

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Olaf Doschke

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.