none
How to use calculated field in another field of the same query (employing CASE statement)

    Question

  • Hi,

    I'm having trouble with using one of my calculated fields in the same query (in CASE statement). I'm aware that I should somehow use "sub select" option, but as far as I'm a beginner in SQL I got confused with my code... Here is what I have:

    SELECT     Customers.FirstName AS სახელი, Customers.LastName AS გვარი, Customers.PrivateNumber AS [პირადი ნომერი], 
                          Branches.Name AS [გამცემი ფილიალი], Pawns.ID AS [სესხის ნომერი], Pawns.StartDate AS [გაცემის თარიღი], 
                          Pawns.FinishDate AS [დახურვის თარიღი], Items.Name AS [სარეალიზაციო ნივთი], Items.Mass AS [წონა მთლიანი], Items.Quantity AS რაოდენობა, 
                          Pawns.CurrentSum AS [სესხის მიმდინარე თანხა], (CASE WHEN  COUNT(1) OVER (PARTITION BY Pawns.ID)> '1' 
    THEN (Items.MaxPrice/Sum(Items.MaxPrice) OVER (PARTITION BY Pawns.ID))*Pawns.CurrentSum ELSE Pawns.CurrentSum END) AS CALCULATED_FIELD, 
    Transactions.[Percent] AS [დარიცხული %], Currencies.Name AS ვალუტა, 
                          COUNT(1) OVER (PARTITION BY Pawns.ID) AS Cnt, NewDistrPrcnt = (CASE WHEN COUNT(1) OVER (PARTITION BY Pawns.ID) > '1' 
                 THEN (Items.Mass/SUM(Items.Mass) over (partition BY Pawns.ID))* Transactions.[Percent]  
    Else Transactions.[Percent] End)
    --, NewDistrPrcnt2 = (CASE WHEN COUNT(1) OVER (PARTITION BY Pawns.ID) > '1' THEN (CALCULATED_FIELD /SUM(Pawns.CurrentSum) OVER (PARTITION BY Pawns.ID))*Transactions.[Percent] ELSE Transactions.[Percent] END)   
    FROM         Customers INNER JOIN
                          Pawns ON Customers.ID = Pawns.CustomerID INNER JOIN
                          Items ON Pawns.ID = Items.PawnID INNER JOIN
                          Branches ON Pawns.BranchID = Branches.ID AND Pawns.LocationID = Branches.ID INNER JOIN
                          PawnTypes ON Pawns.PawnTypeID = PawnTypes.ID INNER JOIN
                          Currencies ON PawnTypes.CurrencyID = Currencies.ID LEFT OUTER JOIN
                          Transactions ON Pawns.ID = Transactions.PawnID AND Pawns.FinishDate = Transactions.Date
    WHERE     (Pawns.StatusID = 6) AND (Pawns.FinishDate BETWEEN '15Oct2013' AND '24Oct2013')

    Order by Pawns.ID, Branches.Name, Pawns.FinishDate desc

    I'm not able to apply this one to the query:

    NewDistrPrcnt2 = (CASE WHEN COUNT(1) OVER (PARTITION BY Pawns.ID) > '1' THEN (CALCULATED_FIELD /SUM(Pawns.CurrentSum) OVER (PARTITION BY Pawns.ID))*Transactions.[Percent] ELSE Transactions.[Percent] END)

    I've purposely named the field I'm having problem as CALCULATED_FIELD to be more clear about the problem. Maybe anyone can help me correcting the code?

    Many thanks!

    Friday, October 25, 2013 10:05 AM