• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

Subqueries

This SP works just fine:

/****** Object:  StoredProcedure [dbo].[usp_FixedExpenses]    Script Date: 05/02/2009 09:17:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
      ALTER PROCEDURE [dbo].[usp_FixedExpenses]
        (
                @iPeriod date
        )
AS
INSERT INTO Transactions (Period,AccountId,CCId,Description,EmpId,Amount,Status,CheckNo)
SELECT @iPeriod, 8, 1, NULL, EmpId , -64, 'Q', NULL
FROM Employees WHERE StatusId = 'A'


This statement is returning an a calculated AMOUNT value based on several conditions (if Employees is not salary, if the income for the current period is greater than 0, and if the employee status is A or T

DECLARE
@iPeriod date
SET @iPeriod = '2009-03-31'
(SELECT subt.Amount * e.BillingFees/100 AS Billing
FROM Transactions subt INNER JOIN Employees e ON subt.EmpId = e.EmpId
WHERE Period = @iPeriod AND subt.AccountId = 5 and subt.CCId = 6 AND subt.Amount > 0 AND (e.StatusId = 'A' OR e.StatusId = 'T')
AND e.IsSalary = 0)

how can I join this second statement to the stored proc so that instead of passing a fixed AMOUNT (in the example -64.00) the amount will automatically calculated reading the percentage (e.BillingFees) for each employee?

I tried using the following code but only the first part of the statements gets executed (each employee is charged with -64.00).

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
      ALTER PROCEDURE [dbo].[usp_FixedExpenses]
        (
                @iPeriod date
        )
AS
INSERT INTO Transactions (Period,AccountId,CCId,Description,EmpId,Amount,Status,CheckNo)
SELECT @iPeriod, 8, 1, NULL, EmpId , -64, 'Q', NULL
FROM Employees WHERE StatusId = 'A'

-- Charging Billing Fees
INSERT INTO Transactions (Period,AccountId,CCId,Description,EmpId,Amount,Status,CheckNo)
SELECT @iPeriod, 3, 2, 'Monthly Charges', EmpId,
(SELECT t.Amount * e.BillingFees/100 AS Billing
FROM Transactions t INNER JOIN Employees e ON t.EmpId = e.EmpId
WHERE Period = @iPeriod AND t.AccountId = 5 and t.CCId = 6),'Q', NULL
FROM Employees WHERE (StatusId = 'A' OR StatusId = 'T') AND IsSalary = 0


Can anyone help? Thanks

Rick


0
rick_it
Asked:
rick_it
  • 6
  • 2
  • 2
  • +1
1 Solution
 
pcelbaCommented:
Not sure if subquery is allowed in this case but you could try following:

-- Charging Billing Fees
INSERT INTO Transactions (Period,AccountId,CCId,Description,EmpId,Amount,Status,CheckNo)
SELECT @iPeriod, 3, 2, 'Monthly Charges', EmpId,
(SELECT TOP 1 t.Amount * e.BillingFees/100 AS Billing
FROM Transactions t INNER JOIN Employees e ON t.EmpId = e.EmpId
WHERE t.Period = @iPeriod AND t.AccountId = 5 and t.CCId = 6),'Q', NULL
FROM Employees WHERE (StatusId = 'A' OR StatusId = 'T') AND IsSalary = 0

Maybe the error reported would be useful.
0
 
rick_itAuthor Commented:
let me try to reproduce the error. However i'm open to any other solutions
0
 
rick_itAuthor Commented:
here's the error:
Msg 116, Level 16, State 1, Line 11
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
pcelbaCommented:
If the subquery is not allowed then you may create temp. table:

SELECT @iPeriod Period, 3 AccountId, 2 CCId, 'Monthly Charges' Description, EmpId,
(SELECT TOP 1 t.Amount * e.BillingFees/100 AS Billing
FROM Transactions t INNER JOIN Employees e ON t.EmpId = e.EmpId
WHERE t.Period = @iPeriod AND t.AccountId = 5 and t.CCId = 6) Amount, 'Q' Status, NULL CheckNo
INTO #tr
FROM Employees
WHERE (StatusId = 'A' OR StatusId = 'T') AND IsSalary = 0

INSERT INTO Transactions (Period,AccountId,CCId,Description,EmpId,Amount,Status,CheckNo)
SELECT * FROM #tr

DROP TABLE #tr
0
 
rick_itAuthor Commented:
If I use your code sql will insert 7 records with the identical amount which is not correct. I should get 7 records with 7 different amount because each employee has a different income and a different percentage.

if I remove ...TOP 1... from your statement i get the following error

Msg 512, Level 16, State 1, Line 5
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

(0 row(s) affected)

(246 row(s) affected)
0
 
SharathData EngineerCommented:
Didn't understand your question clearly. can you explain with an example?
0
 
rick_itAuthor Commented:
Employees e          CostCenters  c         Accounts  a             Transactions t
EmpId                     CCId                          AccountId                TransID
IsSalary                                                                                   Period
BillingFees                                                                               AccountId
StatusId                                                                                   CCId
                                                                                                EmpId
                                                                                                Amount
                                                                                                Status
                                                                                                CheckNo

CASE 1 (already working) - all employees are charged with a fixed amount of money each period (@iPeriod)

CASE 2 (work in progress):
  Step 1: user will enter monthly income for each employee in the table Transactions. AccountId is set to
              5 and CCId is set to 6
  Step 2: user will enter @iPeriod on a form and system will calculate fixed expenses (covered by
              CASE 1)
  Step 3: if possible, using the same parameter entered above (@iPeriod) the system will insert new
              records for other variable expenses.
              Only employees having e.IsSalary = 0,e.StatusId = A or T, t.Amount > 0 will be charged with this
              expense. The t.Amount needs to be read from the table Transactions where Period = @iPeriod,
              AccountId = 8 and CCId = 1 then, to calculate the billing fee, i need to use the following
              formula t.Amount * e.BillingFees/100

Emp   Period     Amount                                     AccountID    CCId                               BillingFees
1        02/28      1000                                              5                 6                                       8.5%
1        02/28         64                                               8                 1
1        02/28       (1000*8.5/100)                             3                 2
2        02/28      2000                                               5                6                                       10%
2        02/28          64                                               8                1
2        02/28      2000*10/100                                   3                2
3        02/28          1000                                           5                 6                                         --
3        02/28             64                                            8                 1
no other expenses for emp 3 because IsSalary = 1 for this employee

I hope this really help.

Thanks

0
 
Anthony PerkinsCommented:
Something like this perhaps:
ALTER PROCEDURE [dbo].[usp_FixedExpenses]
        (
                @iPeriod date
        )

AS

INSERT      Transactions (
            Period,
            AccountId,
            CCId,
            Description,
            EmpId,
            Amount,
            Status,
            CheckNo)
SELECT      @iPeriod,
            8,
            1,
            NULL,
            EmpId,
            t.Amount * e.BillingFees / 100 Billing,
            'Q',
            NULL
FROM      Transactions t
            INNER JOIN Employees e ON t.EmpId = e.EmpId
WHERE      Period = @iPeriod
            AND t.AccountId = 5
            And t.CCId = 6

0
 
Anthony PerkinsCommented:
Actually on second thoughts this might be more appropriate:
ALTER PROCEDURE [dbo].[usp_FixedExpenses]
        (
                @iPeriod date
        )

AS

INSERT      Transactions (
            Period,
            AccountId,
            CCId,
            Description,
            EmpId,
            Amount,
            Status,
            CheckNo)
SELECT      @iPeriod,
            8,
            1,
            NULL,
            e.EmpId,
            d.Billing,
            'Q',
            NULL
FROM      Employees e
            Inner Join (
                  SELECT      p.EmpId,
                              t.Amount * p.BillingFees / 100 Billing
                  FROM      Transactions t
                              INNER JOIN Employees p ON t.EmpId = p.EmpId
                  WHERE      t.Period = @iPeriod
                              AND t.AccountId = 5
                              and t.CCId = 6
                              AND t.Amount > 0
                              AND p.StatusId In ('A', 'T')
                              AND p.IsSalary = 0) d On e.EmpId = d.EmpId
0
 
rick_itAuthor Commented:
Absolutely PERFECT.
0
 
rick_itAuthor Commented:
Thank you so much that worked out beautifully!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now