We help IT Professionals succeed at work.

Help with Stored Procedure Output

mburk1968
mburk1968 asked
on
279 Views
Last Modified: 2012-02-09
I have the following subquery. I am calculation the remaining balance of PTO to the end of the current month. My issue is that if a user has not yet submitted PTO for this year I receive a NULL value. I want to see the hours that are in the PTOBeginningBalances.

,( SELECT    ( PB.BeginningBalance ) - COALESCE(SUM(PR.TotalHoursUsed), 0)
                  FROM      PTORequest PR
                            LEFT OUTER JOIN PTOBeginningBalances PB ON PR.EmployeeUsername = PB.EmployeeUsername
                  WHERE     PBAL.EmployeeUsername = PB.EmployeeUsername
                            --AND SUBSTRING(Status, 4, LEN(Status) - 3) = 'Approved'
                            AND ( YEAR(Year) = YEAR(GETDATE()) )
                            AND ( YEAR(StartDateAndTime) = YEAR(GETDATE())
                                  OR YEAR(EndDateAndTime) = YEAR(GETDATE())
                                )
                  GROUP BY  PB.BeginningBalance
                ) AS [Remaining Hours]
Comment
Watch Question

Rajkumar GsSoftware Engineer

Commented:
Try by interchaning the tables in the query
,( SELECT    ( PB.BeginningBalance ) - COALESCE(SUM(PR.TotalHoursUsed), 0)
                  FROM     PTOBeginningBalances PB                            LEFT OUTER JOIN PTORequest PR ON PR.EmployeeUsername = PB.EmployeeUsername
                  WHERE     PBAL.EmployeeUsername = PB.EmployeeUsername
                            --AND SUBSTRING(Status, 4, LEN(Status) - 3) = 'Approved'
                            AND ( YEAR(Year) = YEAR(GETDATE()) )
                            AND ( YEAR(StartDateAndTime) = YEAR(GETDATE())
                                  OR YEAR(EndDateAndTime) = YEAR(GETDATE())
                                )
                  GROUP BY  PB.BeginningBalance
                ) AS [Remaining Hours]
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
FROM      PTORequest PR
                            LEFT OUTER JOIN PTOBeginningBalances PB

this looks like it's wrong ...
please try:

FROM      PTORequest PR
                            RIGHT OUTER JOIN PTOBeginningBalances PB
Rajkumar GsSoftware Engineer

Commented:
Angel's suggestion is also good. Both suggestions are same in functionality.

Author

Commented:
Still receive Nulls tried both solutions.
Rajkumar GsSoftware Engineer

Commented:
It seems that your query is not returning any values for the WHERE conditions you have given.
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
please split up the subselect.

, COALESCE(  ( SELECT    ( PB.BeginningBalance ) 
                  FROM     PTOBeginningBalances PB  
                WHERE PBAL.EmployeeUsername = PB.EmployeeUsername
                   ) , 0 
- COALESCE( ( SELECT    SUM(PR.TotalHoursUsed) 
                  FROM   PTORequest PR 
               WHERE PR.EmployeeUsername = PB.EmployeeUsername
                ) , 0 )
  AS [Remaining Hours]

Open in new window


the question is, on which table(s) are the time conditions? ... you need to add those back
Rajkumar GsSoftware Engineer

Commented:
If there is no matching records like in this case, you can convert the NULL returned to 0 as shown below
,ISNULL(( SELECT    ( PB.BeginningBalance ) - COALESCE(SUM(PR.TotalHoursUsed), 0)
                  FROM      PTOBeginningBalances PB
                            LEFT OUTER JOIN PTORequest PR ON PR.EmployeeUsername = PB.EmployeeUsername
                  WHERE     PBAL.EmployeeUsername = PB.EmployeeUsername
                            --AND SUBSTRING(Status, 4, LEN(Status) - 3) = 'Approved'
                            AND ( YEAR(Year) = YEAR(GETDATE()) )
                            AND ( YEAR(StartDateAndTime) = YEAR(GETDATE())
                                  OR YEAR(EndDateAndTime) = YEAR(GETDATE())
                                )
                  GROUP BY  PB.BeginningBalance
                ), 0) AS [Remaining Hours]
Rajkumar GsSoftware Engineer

Commented:
That's a nice logic Angel. Thanks for that

Commented:
Off topic:
1)
If you put a table-prefix before thoscolumns it will remain working if other table of join gets a new column with same name and it is better documented where those columns come from.

2) Index
Applying functions on columns in WHERE will prevent index with these columns from being used.

AND ( YEAR(Year) = YEAR(GETDATE()) )
                            AND ( YEAR(StartDateAndTime) = YEAR(GETDATE())
                                  OR YEAR(EndDateAndTime) = YEAR(GETDATE())
                                )

See for more info
https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/A_2531-SARGable-functions-in-SQL-Server.html

Author

Commented:
Angel,

Below is the code I placed back into my SP. I am getting the following

Msg 102, Level 15, State 1, Procedure PXQ0001_PTOSummary, Line 58
Incorrect syntax near ','.
Msg 156, Level 15, State 1, Procedure PXQ0001_PTOSummary, Line 71
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Procedure PXQ0001_PTOSummary, Line 77
Incorrect syntax near the keyword 'AS'.
Msg 102, Level 15, State 1, Procedure PXQ0001_PTOSummary, Line 91
Incorrect syntax near ','.



, COALESCE(  ( SELECT    ( PB.BeginningBalance )
                  FROM     PTOBeginningBalances PB  
                WHERE PBAL.EmployeeUsername = PB.EmployeeUsername
                   ) , 0
                        - COALESCE( ( SELECT    SUM(PR.TotalHoursUsed)
                                                  FROM   PTORequest PR
                                             WHERE PR.EmployeeUsername = PB.EmployeeUsername
                                             AND ( YEAR(StartDateAndTime) = YEAR(GETDATE())
                                             OR YEAR(EndDateAndTime) = YEAR(GETDATE())
                                                ) , 0 )
                          AS [Remaining Hours]
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
sorry, I missed a ) for the first coalesce function, just before the -

Author

Commented:
Closer...
,COALESCE(( SELECT   ( PB.BeginningBalance )
                           FROM     PTOBeginningBalances PB
                           WHERE    PBAL.EmployeeUsername = PB.EmployeeUsername
                         ), 0)
                - COALESCE(( SELECT SUM(PR.TotalHoursUsed)
                             FROM   PTORequest PR
                             WHERE  PR.EmployeeUsername = PB.EmployeeUsername
                                    AND ( YEAR(StartDateAndTime) = YEAR(GETDATE())
                                          OR YEAR(EndDateAndTime) = YEAR(GETDATE())
                                        )
                           ), 0) AS [Remaining Hours]

ERROR

Msg 4104, Level 16, State 1, Procedure PXQ0001_PTOSummary, Line 61
The multi-part identifier "PB.EmployeeUsername" could not be bound.
Msg 4104, Level 16, State 1, Procedure PXQ0001_PTOSummary, Line 61
The multi-part identifier "PB.EmployeeUsername" could not be bound.
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
ok:
,COALESCE(( SELECT   ( PB.BeginningBalance )
                           FROM     PTOBeginningBalances PB
                           WHERE    PBAL.EmployeeUsername = PB.EmployeeUsername
                         ), 0)
                - COALESCE(( SELECT SUM(PR.TotalHoursUsed)
                             FROM   PTORequest PR
                             WHERE  PR.EmployeeUsername = PBAL.EmployeeUsername
                                    AND ( YEAR(StartDateAndTime) = YEAR(GETDATE())
                                          OR YEAR(EndDateAndTime) = YEAR(GETDATE())
                                        )
                           ), 0) AS [Remaining Hours] 

Open in new window

Author

Commented:
Angel,

The query compiled however now I get a new error when I execute the query.

Msg 512, Level 16, State 1, Procedure PXQ0001_PTOSummary, Line 18
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
You are correct. I had to tweak my Where Statement.

Successful

,COALESCE(( SELECT   ( PB.BeginningBalance )
                           FROM     PTOBeginningBalances PB
                           WHERE    PB.EmployeeUsername = PBAL.EmployeeUsername
                                    AND ( YEAR(PB.Year) = YEAR(GETDATE()) )
                         ), 0)
                - COALESCE(( SELECT SUM(PR.TotalHoursUsed)
                             FROM   PTORequest PR
                             WHERE  PR.EmployeeUsername = PBAL.EmployeeUsername
                                    AND ( YEAR(StartDateAndTime) = YEAR(GETDATE())
                                          OR YEAR(EndDateAndTime) = YEAR(GETDATE())
                                        )
                           ), 0) AS [Remaining Hours]
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.