Incorrect syntax near the keyword 'END'.

I can't find why it's not liking my stored proc

Incorrect syntax near the keyword 'END'.

I started getting this error after I added the PDCs queries after the first comma


ALTER PROCEDURE [dbo].[IT_Get_Dashboard_PDCs_CCs_Monthly_AZ]

@FirstDayMonthDate      datetime

AS
BEGIN
DECLARE @today                  datetime,
            @ProcessDate      datetime,
            @MonthName            varchar(20),
            @Year                  int

SET @today = CONVERT(CHAR(8), GETDATE(), 112) --'Jun 26 2005'
SET @ProcessDate = CONVERT(CHAR(8), @FirstDayMonthDate, 112)
SET @MonthName = DATENAME(m, @ProcessDate)
SET @Year = DATENAME(yy, @ProcessDate)

-------- CC  ----------
SELECT CAST(CCs_IL as Integer) as CCs_IL, CAST(PDCs_IL As Integer) as PDCs_IL, @MonthName as MonthName, @Year as YearValue
FROM
(
SELECT SUM(CQuery.Amount) as CCs_IL
FROM
(SELECT   dc.Amount

FROM Collect2000.dbo.Master m (NOLOCK)
LEFT JOIN Collect2000.dbo.DebtorCreditCards dc ON dc.number = m.number
INNER JOIN Collect2000.dbo.Customer c ON c.Customer = m.Customer

WHERE     dc.IsActive=1
          AND m.Customer IN (SELECT Customer from Customer_DashboardGraphs where Illinois = 1)
          AND dc.DateEntered BETWEEN DATEADD(DAY, -DATEPART(DAY, @ProcessDate) + 1, @ProcessDate) AND DATEADD(DAY, -DATEPART(DAY, @ProcessDate), DATEADD(MONTH, 1, @ProcessDate)) AND dc.DateEntered  IS NOT NULL
          AND dc.DepositDate BETWEEN DATEADD(DAY, -DATEPART(DAY, @ProcessDate) + 1, @ProcessDate) AND DATEADD(DAY, -DATEPART(DAY, @ProcessDate), DATEADD(MONTH, 1, @ProcessDate))
          AND dc.DepositDate  IS NOT NULL
          AND dc.OnHoldDate IS NULL
          AND c.customer <> '9999999'
) as CQuery
) as p1
,

-------- PDCs ----------    PROBLEM STARTED HAPPENING WHEN I ADDED THIS AFTER THE COMMA IN THE PREVIOUS LINE
(
SELECT SUM(PDC_All.Amount) as PDCs_IL
FROM (
   SELECT (Coalesce(pd1_Amount, 0) + Coalesce(PD2_Amount, 0)) as Amount
   FROM    
     (

     SELECT pd.Amount as pd1_Amount, 0 as pd2_Amount
          FROM Master m (NOLOCK)
          LEFT JOIN dbo.pdc pd ON pd.number = m.number
          INNER JOIN dbo.Customer c ON c.Customer = m.Customer

          WHERE     pd.Active = 1
                    AND pd.Entered BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate))
     AND pd.Entered <> '1900-01-01 00:00:00.000'
                    AND pd.Deposit BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate))
                    --AND pd.Deposit IS NOT NULL    
                    --AND pd.OnHold IS NULL
                    AND c.customer <> '9999999'
         
          UNION

          SELECT   0 as pd1_Amount, pdd.Amount as PD2_Amount
          FROM Master m (NOLOCK)
          LEFT JOIN dbo.pdcdeleted pdd ON pdd.number = m.number
          INNER JOIN dbo.Customer c ON c.Customer = m.Customer

          WHERE     pdd.Entered BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate))
              AND pdd.Entered <> '1900-01-01 00:00:00.000'
                    AND pdd.Deposit BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate))
                    --AND pdd.Deposit IS NOT NULL    
                    --AND pdd.OnHold IS NULL
                    AND c.customer <> '9999999'
) as PDC_Main
) AS PDC_All

END
LVL 1
dba123Asked:
Who is Participating?
 
imran_fastCommented:
ALTER PROCEDURE [dbo].[IT_Get_Dashboard_PDCs_CCs_Monthly_AZ]

@FirstDayMonthDate     datetime

AS


BEGIN
DECLARE @today               datetime,
          @ProcessDate     datetime,
          @MonthName          varchar(20),
          @Year               int

SET @today = CONVERT(CHAR(8), GETDATE(), 112) --'Jun 26 2005'
SET @ProcessDate = CONVERT(CHAR(8), @FirstDayMonthDate, 112)
SET @MonthName = DATENAME(m, @ProcessDate)
SET @Year = DATENAME(yy, @ProcessDate)

-------- CC  ----------
SELECT CAST(CCs_IL as Integer) as CCs_IL, CAST(PDCs_IL As Integer) as PDCs_IL, @MonthName as MonthName, @Year as YearValue
FROM
(
SELECT SUM(CQuery.Amount) as CCs_IL
FROM
(SELECT   dc.Amount

FROM Collect2000.dbo.Master m with(NOLOCK)
LEFT JOIN Collect2000.dbo.DebtorCreditCards dc ON dc.number = m.number
INNER JOIN Collect2000.dbo.Customer c ON c.Customer = m.Customer

WHERE     dc.IsActive=1
          AND m.Customer IN (SELECT Customer from Customer_DashboardGraphs where Illinois = 1)
          AND dc.DateEntered BETWEEN DATEADD(DAY, -DATEPART(DAY, @ProcessDate) + 1, @ProcessDate) AND DATEADD(DAY, -DATEPART(DAY, @ProcessDate), DATEADD(MONTH, 1, @ProcessDate)) AND dc.DateEntered  IS NOT NULL
          AND dc.DepositDate BETWEEN DATEADD(DAY, -DATEPART(DAY, @ProcessDate) + 1, @ProcessDate) AND DATEADD(DAY, -DATEPART(DAY, @ProcessDate), DATEADD(MONTH, 1, @ProcessDate))
          AND dc.DepositDate  IS NOT NULL
          AND dc.OnHoldDate IS NULL
          AND c.customer <> '9999999'
) as CQuery
) as p1
,

-------- PDCs ----------    PROBLEM STARTED HAPPENING WHEN I ADDED THIS AFTER THE COMMA IN THE PREVIOUS LINE
(
SELECT SUM(PDC_All.Amount) as PDCs_IL
FROM (
   SELECT (Coalesce(pd1_Amount, 0) + Coalesce(PD2_Amount, 0)) as Amount
   FROM    
     (

     SELECT pd.Amount as pd1_Amount, 0 as pd2_Amount
          FROM Master m (NOLOCK)
          LEFT JOIN dbo.pdc pd ON pd.number = m.number
          INNER JOIN dbo.Customer c ON c.Customer = m.Customer

          WHERE     pd.Active = 1
                    AND pd.Entered BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate))
     AND pd.Entered <> '1900-01-01 00:00:00.000'
                    AND pd.Deposit BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate))
                    --AND pd.Deposit IS NOT NULL    
                    --AND pd.OnHold IS NULL
                    AND c.customer <> '9999999'
         
          UNION

          SELECT   0 as pd1_Amount, pdd.Amount as PD2_Amount
          FROM Master m (NOLOCK)
          LEFT JOIN dbo.pdcdeleted pdd ON pdd.number = m.number
          INNER JOIN dbo.Customer c ON c.Customer = m.Customer

          WHERE     pdd.Entered BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate))
              AND pdd.Entered <> '1900-01-01 00:00:00.000'
                    AND pdd.Deposit BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate))
                    --AND pdd.Deposit IS NOT NULL    
                    --AND pdd.OnHold IS NULL
                    AND c.customer <> '9999999') as trn
) as PDC_Main
) AS PDC_All
END
0
 
ewildeCommented:
you missed an alias :

.
.
.
) as PDC_Main
) AS PDC_All
) as PDC_WhatEver -- i added this line
END
0
 
imran_fastCommented:
hi,
did you get your solution?
0
 
dba123Author Commented:
will get back to this tomorrow.
0
 
dba123Author Commented:
I no longer have the code, not working at that company anymore therefore I will give the points to whoever was closest.  I did solve it but do not have the code to post anymore to tell you how I resolved it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.