Database

Michele Smith wrote:

Error Source: .Net SqlClient Data Provider
Error Message: Must declare the scalar variable "@Quarter".

Invalid column name 'Quarter'.

SELECT Quarter
FROM
(
    SELECT CASE
        WHEN InvoiceMonth IN (1,2,3) THEN 1
        WHEN InvoiceMonth IN (4,5,6) THEN 2
        WHEN InvoiceMonth IN (7,8,9) THEN 3
        WHEN InvoiceMonth IN (10,11,12) THEN 4
    END As Quarter
    FROM viewSalesTracker_Model1
)
WHERE Quarter = @Quarter;

WITH cteQuarters As
(
    SELECT CASE
        WHEN InvoiceMonth IN (1,2,3) THEN 1
        WHEN InvoiceMonth IN (4,5,6) THEN 2
        WHEN InvoiceMonth IN (7,8,9) THEN 3
        WHEN InvoiceMonth IN (10,11,12) THEN 4
    END As Quarter
    FROM viewSalesTracker_Model1
)
SELECT Quarter
FROM cteQuarters
WHERE Quarter = @Quarter;

SELECT CASE
    WHEN InvoiceMonth IN (1,2,3) THEN 1
    WHEN InvoiceMonth IN (4,5,6) THEN 2
    WHEN InvoiceMonth IN (7,8,9) THEN 3
    WHEN InvoiceMonth IN (10,11,12) THEN 4
END As Quarter
FROM viewSalesTracker_Model1
WHERE CASE
    WHEN InvoiceMonth IN (1,2,3) THEN 1
    WHEN InvoiceMonth IN (4,5,6) THEN 2
    WHEN InvoiceMonth IN (7,8,9) THEN 3
    WHEN InvoiceMonth IN (10,11,12) THEN 4
END = @Quarter;

• For INSERT, there will be records in the inserted virtual table, but none in the deleted virtual table;
• For DELETE, there will records in deleted, but none in inserted;
• For UPDATE, there will be records in both;

CREATE OR ALTER TRIGGER [dbo].[insert_ee_so_email]
    ON [dbo].[import_new_soemailsubmission]
    AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    
    INSERT INTO dbo.EE_SO_MAILING_LISTS 
    (
        [PARTNER], 
        EMAIL_ADDRESS,
        STATEMENT_FLAG, 
        REFRESH_DATE
    )
    SELECT
        SO.new_agentId,
        I.new_email,
        'N',
        GetDate()
    FROM
        inserted As I
        INNER JOIN dbo.import_new_serviceorganization As SO
        ON SO.ID = I.new_agentId
    WHERE
        Exists
        (
            SELECT 1
            FROM dbo.app_users As A
            WHERE A.user_name = 'SO-' + SO.new_agentId
        )
    ;
END
GO
CREATE OR ALTER TRIGGER [dbo].[update_ee_so_email]
    ON [dbo].[import_new_soemailsubmission]
    AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    
    If Updated(new_email)
    BEGIN
        UPDATE
            L
        SET
            EMAIL_ADDRESS = I.new_email
        FROM
            dbo.import_new_serviceorganization As SO
            INNER JOIN inserted As I
            ON I.new_agentId = SO.ID
            INNER JOIN deleted As D
            ON D.new_agentId = SO.ID
            INNER JOIN dbo.EE_SO_MAILING_LISTS As L
            ON L.PARTNER = SO.new_agentId
            And L.EMAIL_ADDRESS = D.new_email
        ;
    END;
END
GO
CREATE OR ALTER TRIGGER [dbo].[delete_ee_so_email]
    ON [dbo].[import_new_soemailsubmission]
    AFTER DELETE
AS
BEGIN
    SET NOCOUNT ON;
    
    DELETE
    FROM
        L
    FROM
        dbo.EE_SO_MAILING_LISTS As L
    WHERE
        Exists
        (
            SELECT 1
            FROM dbo.import_new_serviceorganization As SO
            INNER JOIN deleted As D
            ON D.new_agentId = SO.ID
            WHERE SO.new_agentId = L.PARTNER
            And D.new_email = L.EMAIL_ADDRESS
        )
    ;
END
GO

learning_new wrote:

trying to figure out the best way to assign a primary key in sql

Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.