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.

Super Lloyd wrote:

create index index2 on Table (id0, id1, id2)

Super Lloyd wrote:

create index Index1 on Table (id2) include (id0, id1)

Super Lloyd wrote:

What would be the better index Index1, or Index2?

MSDN[^] wrote:

Redesign nonclustered indexes with a large index key size so that only columns used for searching and lookups are key columns. Make all other columns that cover the query into nonkey columns. In this way, you will have all columns needed to cover the query, but the index key itself is small and efficient.

MSDN[^] wrote:

List the columns to be included in the composite index, in sort-priority order

MSDN[^] wrote:

The preferred way to build indexes on large tables is to start with the clustered index and then build any nonclustered indexes

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.

Super Lloyd wrote:

Mm.. you seem to recommend Index2 so I am confused by your "Index3" on the first line...

Super Lloyd wrote:

basically this is a table that aggregate a summary from multiple data sources. id0 and id1 identify the datasource and are small number (between 1 to 9 each) and id2 is the original record id in the foreign datasource.

Super Lloyd wrote:

Thanks for your feedback and MSDN quotes though!

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.

create index index2 on Table (id0, id1, id2 DESC)

• Index 1 sorts the addresses by house number, and includes the first and last name. You have to start at the end and scan backwards until you find an entry for "John Smith".
• Index 2 sorts the addresses by first name, then last name, then house number. You can jump straight to the end of the "John Smith" records, and see the highest house number.