We have a version system which essentially creates duplicate records and relates them since our users are essentially working with a "working version" of their version. I did not create this and our DBA was crazy he is not here no more.
The table layouts and sample records are in the excel file I attached. Essentially, I need help with the PricingDrugGroup Insert statement that populates the pricingdruggroup table with the correct data. All of the records in red are the working version records and it is the desired output I am looking for.
here is the code from the stored procedure, everything works except for inserting into the pricingdruggroup table. I am having problems with the multiple joins using declared tables.
The table layouts and sample records are in the excel file I attached. Essentially, I need help with the PricingDrugGroup Insert statement that populates the pricingdruggroup table with the correct data. All of the records in red are the working version records and it is the desired output I am looking for.
here is the code from the stored procedure, everything works except for inserting into the pricingdruggroup table. I am having problems with the multiple joins using declared tables.
Code:
DECLARE @OldPricingDrug TABLE
(
RowNumber SMALLINT
, PricingDrugID INT
);
-- @@@JHO Used for creating the new PricingDrug entries
DECLARE @NewPricingDrug TABLE
(
RowNumber SMALLINT
, PricingDrugID INT
);
-- @@@JHO Used for creating the new PricingDrugGroup entries
DECLARE @OldPricingDrugGroup TABLE
(
RowNumber SMALLINT
, PricingDrugGroupID INT
);
-- @@@JHO Used for creating the new PricingDrugGroup entries
DECLARE @NewPricingDrugGroup TABLE
(
RowNumber SMALLINT
, PricingDrugGroupID INT
);
-- @@@JHO Used for creating the new PricingDrugGroup entries
DECLARE @NewVersionGeography TABLE
(
RowNumber SMALLINT,
VersionGeographyID INT
);
DECLARE @OldVersionGeography TABLE
(
RowNumber SMALLINT,
VersionGeographyID INT
);
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
---- Version
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
INSERT INTO
dbo.Version
(
ForecastID
, ShortName
, [Description]
, CreatedBy
, CreatedDate
, ModifiedBy
, ModifiedDate
, IsDeleted
)
SELECT
@ForecastID
, dbo.Version.ShortName
, dbo.Version.Description
, @UserName
, @DateTime
, @UserName
, @DateTime
--By default, a version is not deleted: set to 0
, 0
FROM
dbo.Version
WHERE
dbo.Version.VersionID = @VersionID
SELECT @WorkingVersionID = @@IDENTITY;
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
---- VersionGeography
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
INSERT INTO
dbo.VersionGeography
(
VersionID
, GeographyID
)
SELECT
@WorkingVersionID
, dbo.VersionGeography.GeographyID
FROM
dbo.VersionGeography
WHERE
dbo.VersionGeography.VersionID = @VersionID
---- ---- ---- ---- ---- ---- ----- ---- ---- ---- ---- ---- ---- ---- ---- ---
---- PricingDrug
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
INSERT INTO
dbo.PricingDrug
(
VersionID
, PricingDrugName
, SequenceNumber
)
SELECT
@WorkingVersionID
, dbo.PricingDrug.PricingDrugName
, dbo.PricingDrug.SequenceNumber
FROM
dbo.PricingDrug
WHERE
dbo.PricingDrug.VersionID = @VersionID
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
---- VersionGeography
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
INSERT INTO
@OldVersionGeography (
RowNumber,
VersionGeographyID
)
SELECT
ROW_NUMBER() OVER
(
ORDER BY
dbo.VersionGeography.VersionGeographyID
) AS RowNumber
, dbo.VersionGeography.VersionGeographyID
FROM
dbo.VersionGeography
WHERE
dbo.VersionGeography.VersionID = @VersionID
INSERT INTO
@NewVersionGeography (
RowNumber,
VersionGeographyID
)
SELECT
ROW_NUMBER() OVER
(
ORDER BY
dbo.VersionGeography.VersionGeographyID
) AS RowNumber
, dbo.VersionGeography.VersionGeographyID
FROM
dbo.VersionGeography
WHERE
dbo.VersionGeography.VersionID = @WorkingVersionID
---- ---- ---- ---- ---- ---- ----- ---- ---- ---- ---- ---- ---- ---- ---- ---
---- PricingDrug
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
INSERT INTO
@OldPricingDrug
SELECT
ROW_NUMBER() OVER
(
ORDER BY
dbo.PricingDrug.PricingDrugID
) AS RowNumber
, dbo.PricingDrug.PricingDrugID
FROM
dbo.PricingDrug
WHERE
dbo.PricingDrug.VersionID = @VersionID
INSERT INTO
@NewPricingDrug
SELECT
ROW_NUMBER() OVER
(
ORDER BY
dbo.PricingDrug.PricingDrugID
) AS RowNumber
, dbo.PricingDrug.PricingDrugID
FROM
dbo.PricingDrug
WHERE
dbo.PricingDrug.VersionID = @WorkingVersionID
---- Need Help HERE with Populating the PricingDrugGroupTable
---- PricingDrugGroup
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
INSERT INTO
dbo.PricingDrugGroup (
PricingDrugGroupTypeID
, PricingDrugID
, BrandGeneric
, VersionGeographyID
)
SELECT
PricingDrugGroupTypeID
, NewGroups.PricingDrugID
, dbo.PricingDrugGroup.BrandGeneric
, NVG.VersionGeographyID
FROM
@NewPricingDrug NewGroups
INNER JOIN @OldPricingDrug OldGroups ON NewGroups.RowNumber = OldGroups.RowNumber
INNER JOIN dbo.PricingDrugGroup ON OldGroups.PricingDrugID = dbo.PricingDrugGroup.PricingDrugID
INNER JOIN @OldVersionGeography OVG ON @NewVersionGeography.RowNumber = OVG.RowNumber
--INNER JOIN @NewVersionGeography NVG ON @OldVersionGeography.RowNumber = NVG.RowNumber
INNER JOIN dbo.PricingDrugGroup ON @NewVersionGeography.VersionGeographyID = dbo.PricingDrugGroup.VersionGeographyID
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
---- WorkingVersion
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
INSERT INTO
dbo.WorkingVersion
(
ForecastID
, VersionID
, WorkingVersionID
)
VALUES
(
@ForecastID
, @VersionID
, @WorkingVersionID
)