Database_Module1.sql - main - DataTimeModule/DateTime

Database_Module1.sql
526 строк19 KB
Reeltell
upload
27 май 2026, 01:50
27 май 2026, 01:50f282598
IF DB_ID(N'SQLDataDriver') IS NULL
BEGIN
    CREATE DATABASE SQLDataDriver;
END
GO

USE SQLDataDriver;
GO

IF SCHEMA_ID(N'stg') IS NULL
BEGIN
    EXEC(N'CREATE SCHEMA stg');
END
GO

CREATE TABLE dbo.Roles
(
    RoleId INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Roles PRIMARY KEY,
    Title NVARCHAR(100) NOT NULL CONSTRAINT UQ_Roles_Title UNIQUE
);
GO

CREATE TABLE dbo.Users
(
    UserId INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Users PRIMARY KEY,
    RoleId INT NOT NULL,
    Surname NVARCHAR(100) NOT NULL,
    Name NVARCHAR(100) NOT NULL,
    Patronymic NVARCHAR(100) NULL,
    Login NVARCHAR(100) NOT NULL CONSTRAINT UQ_Users_Login UNIQUE,
    Password NVARCHAR(100) NOT NULL,
    CONSTRAINT FK_Users_Roles FOREIGN KEY (RoleId) REFERENCES dbo.Roles(RoleId)
);
GO

CREATE TABLE dbo.Providers
(
    ProviderId INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Providers PRIMARY KEY,
    Title NVARCHAR(150) NOT NULL CONSTRAINT UQ_Providers_Title UNIQUE
);
GO

CREATE TABLE dbo.Manufacturers
(
    ManufacturerId INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Manufacturers PRIMARY KEY,
    Title NVARCHAR(150) NOT NULL CONSTRAINT UQ_Manufacturers_Title UNIQUE
);
GO

CREATE TABLE dbo.ProductCategories
(
    ProductCategoryId INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_ProductCategories PRIMARY KEY,
    Title NVARCHAR(150) NOT NULL CONSTRAINT UQ_ProductCategories_Title UNIQUE
);
GO

CREATE TABLE dbo.Units
(
    UnitId INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Units PRIMARY KEY,
    Title NVARCHAR(50) NOT NULL CONSTRAINT UQ_Units_Title UNIQUE
);
GO

CREATE TABLE dbo.Products
(
    Article NVARCHAR(50) NOT NULL CONSTRAINT PK_Products PRIMARY KEY,
    Title NVARCHAR(200) NOT NULL,
    UnitId INT NOT NULL,
    Price DECIMAL(10,2) NOT NULL,
    ProviderId INT NOT NULL,
    ManufacturerId INT NOT NULL,
    ProductCategoryId INT NOT NULL,
    Discount INT NOT NULL CONSTRAINT DF_Products_Discount DEFAULT(0),
    StockQuantity INT NOT NULL CONSTRAINT DF_Products_StockQuantity DEFAULT(0),
    Description NVARCHAR(MAX) NULL,
    PhotoPath NVARCHAR(260) NULL,
    CONSTRAINT FK_Products_Units FOREIGN KEY (UnitId) REFERENCES dbo.Units(UnitId),
    CONSTRAINT FK_Products_Providers FOREIGN KEY (ProviderId) REFERENCES dbo.Providers(ProviderId),
    CONSTRAINT FK_Products_Manufacturers FOREIGN KEY (ManufacturerId) REFERENCES dbo.Manufacturers(ManufacturerId),
    CONSTRAINT FK_Products_ProductCategories FOREIGN KEY (ProductCategoryId) REFERENCES dbo.ProductCategories(ProductCategoryId),
    CONSTRAINT CK_Products_Price CHECK (Price >= 0),
    CONSTRAINT CK_Products_Discount CHECK (Discount BETWEEN 0 AND 100),
    CONSTRAINT CK_Products_StockQuantity CHECK (StockQuantity >= 0)
);
GO

CREATE TABLE dbo.PickupPoints
(
    PickupPointId INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_PickupPoints PRIMARY KEY,
    [Index] NVARCHAR(20) NULL,
    Address NVARCHAR(300) NOT NULL
);
GO

CREATE TABLE dbo.OrderStatuses
(
    OrderStatusId INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_OrderStatuses PRIMARY KEY,
    Title NVARCHAR(100) NOT NULL CONSTRAINT UQ_OrderStatuses_Title UNIQUE
);
GO

CREATE TABLE dbo.Orders
(
    OrderId INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Orders PRIMARY KEY,
    OrderDate DATE NULL,
    DeliveryDate DATE NULL,
    PickupPointId INT NOT NULL,
    UserId INT NULL,
    ReceiptCode NVARCHAR(20) NOT NULL,
    OrderStatusId INT NOT NULL,
    CONSTRAINT FK_Orders_PickupPoints FOREIGN KEY (PickupPointId) REFERENCES dbo.PickupPoints(PickupPointId),
    CONSTRAINT FK_Orders_Users FOREIGN KEY (UserId) REFERENCES dbo.Users(UserId),
    CONSTRAINT FK_Orders_OrderStatuses FOREIGN KEY (OrderStatusId) REFERENCES dbo.OrderStatuses(OrderStatusId)
);
GO

CREATE TABLE dbo.OrderProducts
(
    OrderId INT NOT NULL,
    Article NVARCHAR(50) NOT NULL,
    Quantity INT NOT NULL,
    CONSTRAINT PK_OrderProducts PRIMARY KEY (OrderId, Article),
    CONSTRAINT FK_OrderProducts_Orders FOREIGN KEY (OrderId) REFERENCES dbo.Orders(OrderId) ON DELETE CASCADE,
    CONSTRAINT FK_OrderProducts_Products FOREIGN KEY (Article) REFERENCES dbo.Products(Article),
    CONSTRAINT CK_OrderProducts_Quantity CHECK (Quantity > 0)
);
GO

CREATE TABLE stg.Products
(
    StageId INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_stg_Products PRIMARY KEY,
    [Артикул] NVARCHAR(50) NULL,
    [Наименование товара] NVARCHAR(200) NULL,
    [Единица измерения] NVARCHAR(50) NULL,
    [Цена] NVARCHAR(50) NULL,
    [Поставщик] NVARCHAR(150) NULL,
    [Производитель] NVARCHAR(150) NULL,
    [Категория товара] NVARCHAR(150) NULL,
    [Действующая скидка] NVARCHAR(50) NULL,
    [Кол-во на складе] NVARCHAR(50) NULL,
    [Описание товара] NVARCHAR(MAX) NULL,
    [Фото] NVARCHAR(260) NULL
);
GO

CREATE TABLE stg.Users
(
    StageId INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_stg_Users PRIMARY KEY,
    [Роль сотрудника] NVARCHAR(100) NULL,
    [ФИО] NVARCHAR(250) NULL,
    [Логин] NVARCHAR(100) NULL,
    [Пароль] NVARCHAR(100) NULL
);
GO

CREATE TABLE stg.Orders
(
    StageId INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_stg_Orders PRIMARY KEY,
    [Номер заказа] NVARCHAR(50) NULL,
    [Артикул заказа] NVARCHAR(MAX) NULL,
    [Дата заказа] NVARCHAR(50) NULL,
    [Дата доставки] NVARCHAR(50) NULL,
    [Адрес пункта выдачи] NVARCHAR(50) NULL,
    [ФИО авторизированного клиента] NVARCHAR(250) NULL,
    [Код для получения] NVARCHAR(20) NULL,
    [Статус заказа] NVARCHAR(100) NULL
);
GO

CREATE TABLE stg.PickupPoints
(
    StageId INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_stg_PickupPoints PRIMARY KEY,
    [Адрес] NVARCHAR(300) NULL
);
GO

INSERT INTO dbo.Roles(Title)
SELECT v.Title
FROM (VALUES (N'Администратор'), (N'Менеджер'), (N'Авторизированный клиент')) v(Title)
WHERE NOT EXISTS (SELECT 1 FROM dbo.Roles r WHERE r.Title = v.Title);
GO

INSERT INTO dbo.OrderStatuses(Title)
SELECT v.Title
FROM (VALUES (N'Новый'), (N'Завершен')) v(Title)
WHERE NOT EXISTS (SELECT 1 FROM dbo.OrderStatuses s WHERE s.Title = v.Title);
GO

CREATE OR ALTER FUNCTION dbo.ParseDate(@value NVARCHAR(50))
RETURNS DATE
AS
BEGIN
    RETURN COALESCE
    (
        TRY_CONVERT(DATE, NULLIF(LTRIM(RTRIM(@value)), N''), 104),
        TRY_CONVERT(DATE, NULLIF(LTRIM(RTRIM(@value)), N''), 120),
        TRY_CONVERT(DATE, NULLIF(LTRIM(RTRIM(@value)), N''), 121),
        TRY_CONVERT(DATE, NULLIF(LTRIM(RTRIM(@value)), N''))
    );
END;
GO

CREATE OR ALTER FUNCTION dbo.SplitOrderArticles(@value NVARCHAR(MAX))
RETURNS @Items TABLE
(
    Article NVARCHAR(50) NOT NULL,
    Quantity INT NOT NULL
)
AS
BEGIN
    DECLARE @source NVARCHAR(MAX) = ISNULL(@value, N'') + N',';

    ;WITH Parts AS
    (
        SELECT
            1 AS RowNumber,
            LTRIM(RTRIM(LEFT(@source, CHARINDEX(N',', @source) - 1))) AS Value,
            SUBSTRING(@source, CHARINDEX(N',', @source) + 1, LEN(@source)) AS Rest
        WHERE CHARINDEX(N',', @source) > 0

        UNION ALL

        SELECT
            RowNumber + 1,
            LTRIM(RTRIM(LEFT(Rest, CHARINDEX(N',', Rest) - 1))) AS Value,
            SUBSTRING(Rest, CHARINDEX(N',', Rest) + 1, LEN(Rest)) AS Rest
        FROM Parts
        WHERE CHARINDEX(N',', Rest) > 0
    ),
    Pairs AS
    (
        SELECT
            ((RowNumber + 1) / 2) AS PairNumber,
            MAX(CASE WHEN RowNumber % 2 = 1 THEN Value END) AS Article,
            MAX(CASE WHEN RowNumber % 2 = 0 THEN TRY_CONVERT(INT, Value) END) AS Quantity
        FROM Parts
        GROUP BY ((RowNumber + 1) / 2)
    )
    INSERT INTO @Items(Article, Quantity)
    SELECT Article, Quantity
    FROM Pairs
    WHERE Article IS NOT NULL AND Quantity IS NOT NULL AND Quantity > 0;

    RETURN;
END;
GO

CREATE OR ALTER PROCEDURE dbo.ImportAllStaging
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRANSACTION;

    INSERT INTO dbo.Roles(Title)
    SELECT DISTINCT LTRIM(RTRIM([Роль сотрудника]))
    FROM stg.Users s
    WHERE NULLIF(LTRIM(RTRIM([Роль сотрудника])), N'') IS NOT NULL
      AND NOT EXISTS (SELECT 1 FROM dbo.Roles r WHERE r.Title = LTRIM(RTRIM(s.[Роль сотрудника])));

    INSERT INTO dbo.Units(Title)
    SELECT DISTINCT LTRIM(RTRIM([Единица измерения]))
    FROM stg.Products s
    WHERE NULLIF(LTRIM(RTRIM([Артикул])), N'') IS NOT NULL
      AND NULLIF(LTRIM(RTRIM([Единица измерения])), N'') IS NOT NULL
      AND NOT EXISTS (SELECT 1 FROM dbo.Units u WHERE u.Title = LTRIM(RTRIM(s.[Единица измерения])));

    INSERT INTO dbo.Providers(Title)
    SELECT DISTINCT LTRIM(RTRIM([Поставщик]))
    FROM stg.Products s
    WHERE NULLIF(LTRIM(RTRIM([Артикул])), N'') IS NOT NULL
      AND NULLIF(LTRIM(RTRIM([Поставщик])), N'') IS NOT NULL
      AND NOT EXISTS (SELECT 1 FROM dbo.Providers p WHERE p.Title = LTRIM(RTRIM(s.[Поставщик])));

    INSERT INTO dbo.Manufacturers(Title)
    SELECT DISTINCT LTRIM(RTRIM([Производитель]))
    FROM stg.Products s
    WHERE NULLIF(LTRIM(RTRIM([Артикул])), N'') IS NOT NULL
      AND NULLIF(LTRIM(RTRIM([Производитель])), N'') IS NOT NULL
      AND NOT EXISTS (SELECT 1 FROM dbo.Manufacturers m WHERE m.Title = LTRIM(RTRIM(s.[Производитель])));

    INSERT INTO dbo.ProductCategories(Title)
    SELECT DISTINCT LTRIM(RTRIM([Категория товара]))
    FROM stg.Products s
    WHERE NULLIF(LTRIM(RTRIM([Артикул])), N'') IS NOT NULL
      AND NULLIF(LTRIM(RTRIM([Категория товара])), N'') IS NOT NULL
      AND NOT EXISTS (SELECT 1 FROM dbo.ProductCategories c WHERE c.Title = LTRIM(RTRIM(s.[Категория товара])));

    ;WITH SourceRows AS
    (
        SELECT
            LTRIM(RTRIM([Артикул])) AS Article,
            LTRIM(RTRIM([Наименование товара])) AS Title,
            LTRIM(RTRIM([Единица измерения])) AS UnitTitle,
            TRY_CONVERT(DECIMAL(10,2), REPLACE(LTRIM(RTRIM([Цена])), N',', N'.')) AS Price,
            LTRIM(RTRIM([Поставщик])) AS ProviderTitle,
            LTRIM(RTRIM([Производитель])) AS ManufacturerTitle,
            LTRIM(RTRIM([Категория товара])) AS CategoryTitle,
            TRY_CONVERT(INT, LTRIM(RTRIM([Действующая скидка]))) AS Discount,
            TRY_CONVERT(INT, LTRIM(RTRIM([Кол-во на складе]))) AS StockQuantity,
            NULLIF(LTRIM(RTRIM([Описание товара])), N'') AS Description,
            NULLIF(LTRIM(RTRIM([Фото])), N'') AS PhotoPath
        FROM stg.Products
        WHERE NULLIF(LTRIM(RTRIM([Артикул])), N'') IS NOT NULL
    )
    MERGE dbo.Products AS target
    USING
    (
        SELECT
            s.Article,
            s.Title,
            u.UnitId,
            ISNULL(s.Price, 0) AS Price,
            p.ProviderId,
            m.ManufacturerId,
            c.ProductCategoryId,
            ISNULL(s.Discount, 0) AS Discount,
            ISNULL(s.StockQuantity, 0) AS StockQuantity,
            s.Description,
            s.PhotoPath
        FROM SourceRows s
        INNER JOIN dbo.Units u ON u.Title = s.UnitTitle
        INNER JOIN dbo.Providers p ON p.Title = s.ProviderTitle
        INNER JOIN dbo.Manufacturers m ON m.Title = s.ManufacturerTitle
        INNER JOIN dbo.ProductCategories c ON c.Title = s.CategoryTitle
    ) AS source
    ON target.Article = source.Article
    WHEN MATCHED THEN
        UPDATE SET
            Title = source.Title,
            UnitId = source.UnitId,
            Price = source.Price,
            ProviderId = source.ProviderId,
            ManufacturerId = source.ManufacturerId,
            ProductCategoryId = source.ProductCategoryId,
            Discount = source.Discount,
            StockQuantity = source.StockQuantity,
            Description = source.Description,
            PhotoPath = source.PhotoPath
    WHEN NOT MATCHED THEN
        INSERT (Article, Title, UnitId, Price, ProviderId, ManufacturerId, ProductCategoryId, Discount, StockQuantity, Description, PhotoPath)
        VALUES (source.Article, source.Title, source.UnitId, source.Price, source.ProviderId, source.ManufacturerId, source.ProductCategoryId, source.Discount, source.StockQuantity, source.Description, source.PhotoPath);

    ;WITH RawUsers AS
    (
        SELECT
            LTRIM(RTRIM([Роль сотрудника])) AS RoleTitle,
            LTRIM(RTRIM([ФИО])) AS FullName,
            LTRIM(RTRIM([Логин])) AS Login,
            LTRIM(RTRIM([Пароль])) AS Password
        FROM stg.Users
        WHERE NULLIF(LTRIM(RTRIM([Логин])), N'') IS NOT NULL
    ),
    ParsedUsers AS
    (
        SELECT
            RoleTitle,
            FullName,
            LEFT(FullName, CHARINDEX(N' ', FullName + N' ') - 1) AS Surname,
            LTRIM(SUBSTRING(FullName, CHARINDEX(N' ', FullName + N' ') + 1, 250)) AS RestName,
            Login,
            Password
        FROM RawUsers
    ),
    SourceRows AS
    (
        SELECT
            r.RoleId,
            p.Surname,
            LEFT(p.RestName, CHARINDEX(N' ', p.RestName + N' ') - 1) AS Name,
            NULLIF(LTRIM(SUBSTRING(p.RestName, CHARINDEX(N' ', p.RestName + N' ') + 1, 250)), N'') AS Patronymic,
            p.Login,
            p.Password
        FROM ParsedUsers p
        INNER JOIN dbo.Roles r ON r.Title = p.RoleTitle
    )
    MERGE dbo.Users AS target
    USING SourceRows AS source
    ON target.Login = source.Login
    WHEN MATCHED THEN
        UPDATE SET
            RoleId = source.RoleId,
            Surname = source.Surname,
            Name = source.Name,
            Patronymic = source.Patronymic,
            Password = source.Password
    WHEN NOT MATCHED THEN
        INSERT (RoleId, Surname, Name, Patronymic, Login, Password)
        VALUES (source.RoleId, source.Surname, source.Name, source.Patronymic, source.Login, source.Password);

    SET IDENTITY_INSERT dbo.PickupPoints ON;

    MERGE dbo.PickupPoints AS target
    USING
    (
        SELECT
            StageId AS PickupPointId,
            LEFT(LTRIM(RTRIM([Адрес])), CHARINDEX(N',', LTRIM(RTRIM([Адрес])) + N',') - 1) AS [Index],
            LTRIM(RTRIM([Адрес])) AS Address
        FROM stg.PickupPoints
        WHERE NULLIF(LTRIM(RTRIM([Адрес])), N'') IS NOT NULL
    ) AS source
    ON target.PickupPointId = source.PickupPointId
    WHEN MATCHED THEN
        UPDATE SET [Index] = source.[Index], Address = source.Address
    WHEN NOT MATCHED THEN
        INSERT (PickupPointId, [Index], Address)
        VALUES (source.PickupPointId, source.[Index], source.Address);

    SET IDENTITY_INSERT dbo.PickupPoints OFF;

    INSERT INTO dbo.OrderStatuses(Title)
    SELECT DISTINCT LTRIM(RTRIM([Статус заказа]))
    FROM stg.Orders s
    WHERE NULLIF(LTRIM(RTRIM([Номер заказа])), N'') IS NOT NULL
      AND NULLIF(LTRIM(RTRIM([Статус заказа])), N'') IS NOT NULL
      AND NOT EXISTS (SELECT 1 FROM dbo.OrderStatuses os WHERE os.Title = LTRIM(RTRIM(s.[Статус заказа])));

    SET IDENTITY_INSERT dbo.Orders ON;

    MERGE dbo.Orders AS target
    USING
    (
        SELECT
            TRY_CONVERT(INT, LTRIM(RTRIM(o.[Номер заказа]))) AS OrderId,
            dbo.ParseDate(o.[Дата заказа]) AS OrderDate,
            dbo.ParseDate(o.[Дата доставки]) AS DeliveryDate,
            TRY_CONVERT(INT, LTRIM(RTRIM(o.[Адрес пункта выдачи]))) AS PickupPointId,
            u.UserId,
            LTRIM(RTRIM(o.[Код для получения])) AS ReceiptCode,
            os.OrderStatusId
        FROM stg.Orders o
        INNER JOIN dbo.OrderStatuses os ON os.Title = LTRIM(RTRIM(o.[Статус заказа]))
        OUTER APPLY
        (
            SELECT TOP (1) UserId
            FROM dbo.Users u
            WHERE CONCAT(u.Surname, N' ', u.Name, N' ', ISNULL(u.Patronymic, N'')) = LTRIM(RTRIM(o.[ФИО авторизированного клиента]))
            ORDER BY UserId
        ) u
        WHERE TRY_CONVERT(INT, LTRIM(RTRIM(o.[Номер заказа]))) IS NOT NULL
    ) AS source
    ON target.OrderId = source.OrderId
    WHEN MATCHED THEN
        UPDATE SET
            OrderDate = source.OrderDate,
            DeliveryDate = source.DeliveryDate,
            PickupPointId = source.PickupPointId,
            UserId = source.UserId,
            ReceiptCode = source.ReceiptCode,
            OrderStatusId = source.OrderStatusId
    WHEN NOT MATCHED THEN
        INSERT (OrderId, OrderDate, DeliveryDate, PickupPointId, UserId, ReceiptCode, OrderStatusId)
        VALUES (source.OrderId, source.OrderDate, source.DeliveryDate, source.PickupPointId, source.UserId, source.ReceiptCode, source.OrderStatusId);

    SET IDENTITY_INSERT dbo.Orders OFF;

    DELETE op
    FROM dbo.OrderProducts op
    INNER JOIN stg.Orders o ON TRY_CONVERT(INT, LTRIM(RTRIM(o.[Номер заказа]))) = op.OrderId;

    INSERT INTO dbo.OrderProducts(OrderId, Article, Quantity)
    SELECT
        TRY_CONVERT(INT, LTRIM(RTRIM(o.[Номер заказа]))) AS OrderId,
        i.Article,
        i.Quantity
    FROM stg.Orders o
    CROSS APPLY dbo.SplitOrderArticles(o.[Артикул заказа]) i
    INNER JOIN dbo.Products p ON p.Article = i.Article
    WHERE TRY_CONVERT(INT, LTRIM(RTRIM(o.[Номер заказа]))) IS NOT NULL;

    COMMIT TRANSACTION;
END;
GO

CREATE OR ALTER VIEW dbo.ProductListView
AS
SELECT
    p.Article,
    p.Title,
    u.Title AS UnitTitle,
    p.Price,
    CAST(p.Price * (100 - p.Discount) / 100.0 AS DECIMAL(10,2)) AS DiscountPrice,
    pr.Title AS ProviderTitle,
    m.Title AS ManufacturerTitle,
    c.Title AS CategoryTitle,
    p.Discount,
    p.StockQuantity,
    p.Description,
    p.PhotoPath
FROM dbo.Products p
INNER JOIN dbo.Units u ON u.UnitId = p.UnitId
INNER JOIN dbo.Providers pr ON pr.ProviderId = p.ProviderId
INNER JOIN dbo.Manufacturers m ON m.ManufacturerId = p.ManufacturerId
INNER JOIN dbo.ProductCategories c ON c.ProductCategoryId = p.ProductCategoryId;
GO

CREATE OR ALTER VIEW dbo.OrderListView
AS
SELECT
    o.OrderId,
    o.OrderDate,
    o.DeliveryDate,
    pp.PickupPointId,
    pp.Address AS PickupPointAddress,
    CONCAT(u.Surname, N' ', u.Name, N' ', ISNULL(u.Patronymic, N'')) AS UserFullName,
    o.ReceiptCode,
    os.Title AS StatusTitle,
    STUFF
    (
        (
            SELECT N', ' + op.Article + N' x ' + CONVERT(NVARCHAR(20), op.Quantity)
            FROM dbo.OrderProducts op
            WHERE op.OrderId = o.OrderId
            FOR XML PATH(N''), TYPE
        ).value(N'.', N'nvarchar(max)'),
        1,
        2,
        N''
    ) AS Products
FROM dbo.Orders o
INNER JOIN dbo.PickupPoints pp ON pp.PickupPointId = o.PickupPointId
LEFT JOIN dbo.Users u ON u.UserId = o.UserId
INNER JOIN dbo.OrderStatuses os ON os.OrderStatusId = o.OrderStatusId;
GO