none
cannot figure out how to make my sql work RRS feed

  • Question

  • Select AwardAccountName, AccountFundingAmount,AwardTypevalue, AwardAmount, FUndingSOurceName, ProgramAbbrev, ProjectNumber, LoanClosingDate, LoanStatusValue, LoanTypeValue, CLientName,FundingSourceName
    , ISNULL((select SUM(TransactionAmount) from [Transaction] where [Transaction].AccountId = Award.AwardAccountId  and TransactionTypeId = 2 and TransactionEffectiveDate < = 09/23/2019),0) as Draws
    , ISNULL((select SUM(TransactionAmount) from [Transaction] where [Transaction].AccountId = Award.AwardAccountId  and TransactionTypeId = 12 and TransactionPayPIInd = 1 and TransactionEffectiveDate <= 09/23/2019),0) as Overpayments
    , ISNULL ((SELECT [Transaction].TransactionEffectiveDate, [Transaction].TransactionAmount, 
                             CASE WHEN TransactionPayPIInd = 1 THEN 'Principal' WHEN TransactionPayPIInd = 2 THEN 'Interest' WHEN TransactionPayPIInd = 3 THEN 'Fees' WHEN TransactionPayPIInd = 4 THEN 'Depository' ELSE 'Unknown' END AS PIF,
                                 (SELECT        TransactionTypeValue
                                   FROM            TransactionType
                                   WHERE        (TransactionTypeId = [Transaction].TransactionTypeId)) AS TransactionType, Award.AwardAmount
    FROM            [Transaction] INNER JOIN
                             Award ON [Transaction].AccountId = Award.AwardAccountId INNER JOIN
                             AwardType ON Award.AwardTypeId = AwardType.AwardTypeId)
    , (Select ISNULL(SUM(transactionAmount),0) from [Transaction] where TransactionTypeId in (7,9,17,18,20) and [Transaction].AccountId = FundingSource.FundingSourceId and [Transaction].TransactionEffectiveDate <= 09/23/2019) as FundingSourceTotal,
    Draws.Indirect, Repayment.Repays

    From Award left join
    Accountfunding on Award.AwardAccountId = AccountFunding.AccountId left join
    FundingSource on AccountFunding.AccountFundingFundingSourceId = FundingSource.FundingSourceId left join
    Program on FundingSource.ProgramId = Program.ProgramId left join
    AwardType on Award.AwardTypeId = AwardType.AwardTypeId left join
    Project on Award.AwardProjectId = Project.ProjectID left join
    Loan ON Project.ProjectId = Loan.ProjectId left join
    LoanType ON Loan.LoanTypeId = LoanType.LoanTypeId left join
    LoanStatus ON Loan.LoanStatusId = LoanStatus.LoanStatusId left join
    CLient on Project.ClientId = CLient.ClientId left join
    (SELECT [Indirect] = SUM(TransactionFundingAmount),FundingSource.FundingSourceId  
    FROM [TransactionFunding] left Join
    FundingSOurce on TransactionFunding.FundingSourceId = FundingSource.FundingSourceId

      Where [TransactionFunding].TransactionEffectiveDate <= 09/23/2019 and transactionfunding.transactiontypeid in (2)
      Group By FundingSource.FundingSourceId)Draws on Draws.FundingSourceId = FundingSource.FundingSourceId left join

    (SELECT [Repays] = SUM(TransactionFundingAmount),FundingSource.FundingSourceId  
    FROM [TransactionFunding] left Join
    FundingSOurce on TransactionFunding.FundingSourceId = FundingSource.FundingSourceId

      Where [TransactionFunding].TransactionEffectiveDate <= 09/23/2019 and transactionfunding.transactiontypeid in (3)
      Group By FundingSource.FundingSourceId)Repayment on Repayment.FundingSourceId = FundingSource.FundingSourceId
      --order by ProjectNumber

    Msg 156, Level 15, State 1, Line 12
    Incorrect syntax near the keyword 'as'.
    Msg 102, Level 15, State 1, Line 30
    Incorrect syntax near 'Draws'.
    Msg 102, Level 15, State 1, Line 37
    Incorrect syntax near 'Repayment'.

                

    g_cat

    Friday, October 18, 2019 4:17 PM

Answers

  • You are missing quotes around your date strings and a  ) before " AS [FundingSourceTotal]".

    SELECT [AwardAccountName], 
    	  [AccountFundingAmount], 
    	  [AwardTypevalue], 
    	  [AwardAmount], 
    	  [FUndingSOurceName], 
    	  [ProgramAbbrev], 
    	  [ProjectNumber], 
    	  [LoanClosingDate], 
    	  [LoanStatusValue], 
    	  [LoanTypeValue], 
    	  [CLientName], 
    	  [FundingSourceName], 
    	  ISNULL(
    	  (
    	  SELECT SUM([TransactionAmount])
    	  FROM [Transaction]
    	  WHERE [Transaction].[AccountId] = [Award].[AwardAccountId]
    		   AND [TransactionTypeId] = 2
    		   AND [TransactionEffectiveDate] <= 09 / 23 / 2019
    	  ), 0) AS [Draws], 
    	  ISNULL(
    	  (
    	  SELECT SUM([TransactionAmount])
    	  FROM [Transaction]
    	  WHERE [Transaction].[AccountId] = [Award].[AwardAccountId]
    		   AND [TransactionTypeId] = 12
    		   AND [TransactionPayPIInd] = 1
    		   AND [TransactionEffectiveDate] <= '09/23/2019'
    	  ), 0) AS [Overpayments], 
    	  ISNULL(
    	  (
    	  SELECT [Transaction].[TransactionEffectiveDate], 
    		    [Transaction].[TransactionAmount],
    		    CASE
    			   WHEN [TransactionPayPIInd] = 1
    			   THEN 'Principal'
    			   WHEN [TransactionPayPIInd] = 2
    			   THEN 'Interest'
    			   WHEN [TransactionPayPIInd] = 3
    			   THEN 'Fees'
    			   WHEN [TransactionPayPIInd] = 4
    			   THEN 'Depository'
    			   ELSE 'Unknown'
    		    END AS [PIF], 
    		    (
    		    SELECT [TransactionTypeValue]
    		    FROM [TransactionType]
    		    WHERE([TransactionTypeId] = [Transaction].[TransactionTypeId])
    		    ) AS [TransactionType], 
    		    [Award].[AwardAmount]
    	  FROM [Transaction]
    		  INNER JOIN [Award]
    			 ON [Transaction].[AccountId] = [Award].[AwardAccountId]
    		  INNER JOIN [AwardType]
    			 ON [Award].[AwardTypeId] = [AwardType].[AwardTypeId]
    	  ),
    	  (
    	  SELECT ISNULL(SUM([transactionAmount]), 0)
    	  FROM [Transaction]
    	  WHERE [TransactionTypeId] IN(7, 9, 17, 18, 20)
    	  AND [Transaction].[AccountId] = [FundingSource].[FundingSourceId]
    	  AND [Transaction].[TransactionEffectiveDate] <= '09/23/2019'
    	  )) AS [FundingSourceTotal], 
    	  [Draws].[Indirect], 
    	  [Repayment].[Repays]
    FROM [Award]
    	LEFT JOIN [Accountfunding]
    	    ON [Award].[AwardAccountId] = [AccountFunding].[AccountId]
    	LEFT JOIN [FundingSource]
    	    ON [AccountFunding].[AccountFundingFundingSourceId] = [FundingSource].[FundingSourceId]
    	LEFT JOIN [Program]
    	    ON [FundingSource].[ProgramId] = [Program].[ProgramId]
    	LEFT JOIN [AwardType]
    	    ON [Award].[AwardTypeId] = [AwardType].[AwardTypeId]
    	LEFT JOIN [Project]
    	    ON [Award].[AwardProjectId] = [Project].[ProjectID]
    	LEFT JOIN [Loan]
    	    ON [Project].[ProjectId] = [Loan].[ProjectId]
    	LEFT JOIN [LoanType]
    	    ON [Loan].[LoanTypeId] = [LoanType].[LoanTypeId]
    	LEFT JOIN [LoanStatus]
    	    ON [Loan].[LoanStatusId] = [LoanStatus].[LoanStatusId]
    	LEFT JOIN [CLient]
    	    ON [Project].[ClientId] = [CLient].[ClientId]
    	LEFT JOIN
    	(
    	SELECT [Indirect] = SUM([TransactionFundingAmount]), 
    		  [FundingSource].[FundingSourceId]
    	FROM [TransactionFunding]
    		LEFT JOIN [FundingSOurce]
    		    ON [TransactionFunding].[FundingSourceId] = [FundingSource].[FundingSourceId]
    	WHERE [TransactionFunding].[TransactionEffectiveDate] <= '09/23/2019'
    		 AND [transactionfunding].[transactiontypeid] IN(2)
    	GROUP BY [FundingSource].[FundingSourceId]
    	) [Draws]
    	    ON [Draws].[FundingSourceId] = [FundingSource].[FundingSourceId]
    	LEFT JOIN
    	(
    	SELECT [Repays] = SUM([TransactionFundingAmount]), 
    		  [FundingSource].[FundingSourceId]
    	FROM [TransactionFunding]
    		LEFT JOIN [FundingSOurce]
    		    ON [TransactionFunding].[FundingSourceId] = [FundingSource].[FundingSourceId]
    	WHERE [TransactionFunding].[TransactionEffectiveDate] <= '09/23/2019'
    		 AND [transactionfunding].[transactiontypeid] IN(3)
    	GROUP BY [FundingSource].[FundingSourceId]
    	) [Repayment]
    	    ON [Repayment].[FundingSourceId] = [FundingSource].[FundingSourceId];
    --order by ProjectNumber


    • Edited by Tom Phillips Friday, October 18, 2019 6:16 PM
    • Marked as answer by g_cat Friday, October 18, 2019 10:01 PM
    Friday, October 18, 2019 6:16 PM

All replies

  • You are missing quotes around your date strings and a  ) before " AS [FundingSourceTotal]".

    SELECT [AwardAccountName], 
    	  [AccountFundingAmount], 
    	  [AwardTypevalue], 
    	  [AwardAmount], 
    	  [FUndingSOurceName], 
    	  [ProgramAbbrev], 
    	  [ProjectNumber], 
    	  [LoanClosingDate], 
    	  [LoanStatusValue], 
    	  [LoanTypeValue], 
    	  [CLientName], 
    	  [FundingSourceName], 
    	  ISNULL(
    	  (
    	  SELECT SUM([TransactionAmount])
    	  FROM [Transaction]
    	  WHERE [Transaction].[AccountId] = [Award].[AwardAccountId]
    		   AND [TransactionTypeId] = 2
    		   AND [TransactionEffectiveDate] <= 09 / 23 / 2019
    	  ), 0) AS [Draws], 
    	  ISNULL(
    	  (
    	  SELECT SUM([TransactionAmount])
    	  FROM [Transaction]
    	  WHERE [Transaction].[AccountId] = [Award].[AwardAccountId]
    		   AND [TransactionTypeId] = 12
    		   AND [TransactionPayPIInd] = 1
    		   AND [TransactionEffectiveDate] <= '09/23/2019'
    	  ), 0) AS [Overpayments], 
    	  ISNULL(
    	  (
    	  SELECT [Transaction].[TransactionEffectiveDate], 
    		    [Transaction].[TransactionAmount],
    		    CASE
    			   WHEN [TransactionPayPIInd] = 1
    			   THEN 'Principal'
    			   WHEN [TransactionPayPIInd] = 2
    			   THEN 'Interest'
    			   WHEN [TransactionPayPIInd] = 3
    			   THEN 'Fees'
    			   WHEN [TransactionPayPIInd] = 4
    			   THEN 'Depository'
    			   ELSE 'Unknown'
    		    END AS [PIF], 
    		    (
    		    SELECT [TransactionTypeValue]
    		    FROM [TransactionType]
    		    WHERE([TransactionTypeId] = [Transaction].[TransactionTypeId])
    		    ) AS [TransactionType], 
    		    [Award].[AwardAmount]
    	  FROM [Transaction]
    		  INNER JOIN [Award]
    			 ON [Transaction].[AccountId] = [Award].[AwardAccountId]
    		  INNER JOIN [AwardType]
    			 ON [Award].[AwardTypeId] = [AwardType].[AwardTypeId]
    	  ),
    	  (
    	  SELECT ISNULL(SUM([transactionAmount]), 0)
    	  FROM [Transaction]
    	  WHERE [TransactionTypeId] IN(7, 9, 17, 18, 20)
    	  AND [Transaction].[AccountId] = [FundingSource].[FundingSourceId]
    	  AND [Transaction].[TransactionEffectiveDate] <= '09/23/2019'
    	  )) AS [FundingSourceTotal], 
    	  [Draws].[Indirect], 
    	  [Repayment].[Repays]
    FROM [Award]
    	LEFT JOIN [Accountfunding]
    	    ON [Award].[AwardAccountId] = [AccountFunding].[AccountId]
    	LEFT JOIN [FundingSource]
    	    ON [AccountFunding].[AccountFundingFundingSourceId] = [FundingSource].[FundingSourceId]
    	LEFT JOIN [Program]
    	    ON [FundingSource].[ProgramId] = [Program].[ProgramId]
    	LEFT JOIN [AwardType]
    	    ON [Award].[AwardTypeId] = [AwardType].[AwardTypeId]
    	LEFT JOIN [Project]
    	    ON [Award].[AwardProjectId] = [Project].[ProjectID]
    	LEFT JOIN [Loan]
    	    ON [Project].[ProjectId] = [Loan].[ProjectId]
    	LEFT JOIN [LoanType]
    	    ON [Loan].[LoanTypeId] = [LoanType].[LoanTypeId]
    	LEFT JOIN [LoanStatus]
    	    ON [Loan].[LoanStatusId] = [LoanStatus].[LoanStatusId]
    	LEFT JOIN [CLient]
    	    ON [Project].[ClientId] = [CLient].[ClientId]
    	LEFT JOIN
    	(
    	SELECT [Indirect] = SUM([TransactionFundingAmount]), 
    		  [FundingSource].[FundingSourceId]
    	FROM [TransactionFunding]
    		LEFT JOIN [FundingSOurce]
    		    ON [TransactionFunding].[FundingSourceId] = [FundingSource].[FundingSourceId]
    	WHERE [TransactionFunding].[TransactionEffectiveDate] <= '09/23/2019'
    		 AND [transactionfunding].[transactiontypeid] IN(2)
    	GROUP BY [FundingSource].[FundingSourceId]
    	) [Draws]
    	    ON [Draws].[FundingSourceId] = [FundingSource].[FundingSourceId]
    	LEFT JOIN
    	(
    	SELECT [Repays] = SUM([TransactionFundingAmount]), 
    		  [FundingSource].[FundingSourceId]
    	FROM [TransactionFunding]
    		LEFT JOIN [FundingSOurce]
    		    ON [TransactionFunding].[FundingSourceId] = [FundingSource].[FundingSourceId]
    	WHERE [TransactionFunding].[TransactionEffectiveDate] <= '09/23/2019'
    		 AND [transactionfunding].[transactiontypeid] IN(3)
    	GROUP BY [FundingSource].[FundingSourceId]
    	) [Repayment]
    	    ON [Repayment].[FundingSourceId] = [FundingSource].[FundingSourceId];
    --order by ProjectNumber


    • Edited by Tom Phillips Friday, October 18, 2019 6:16 PM
    • Marked as answer by g_cat Friday, October 18, 2019 10:01 PM
    Friday, October 18, 2019 6:16 PM
  • Thank you.

    g_cat

    Friday, October 18, 2019 10:01 PM