Snippets
Created by
Will Wolff-Myren
last modified
Run the following scripts in order!
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 | SET NOCOUNT ON;
DROP TABLE IF EXISTS #CDSDeliveryData;
DECLARE @ItemTypeIdCustomJournal INT = 11;
-- Gather the data into a temp table
SELECT DISTINCT
ci.CurriculumItemId
,ci.CurriculumItemName
,cid.DetailItemText AS CurriculumItemDescription
,cast(cid1.DetailItemText as varchar(36)) AS PlayerId
,cid2.DetailItemText AS CustomParameters
,cid3.DetailItemText AS PartnerUrl
,cid4.DetailItemText AS PartnerIntegration
,ci.PossibleScore
,ci.AvailableLanguages
,ci.LowGrade
,ci.HighGrade
,ci.[LastUpdateDate]
,ci.[LastUpdateUser]
,ci.[auditCreateDate]
,ci.Valid
,dlo.LearningObjectId as [DeliveryLearningObjectId]
,slo.LearningObjectId as [ScoringLearningObjectId]
INTO #CDSDeliveryData
FROM ContentCatalog.dbo.CurriculumItem AS ci
LEFT JOIN ContentCatalog.dbo.CurriculumItemDetail cid ON ci.CurriculumItemId = cid.CurriculumItemId and cid.DetailItemType = 5
INNER JOIN ContentCatalog.dbo.CurriculumItemDetail cid1 ON ci.CurriculumItemId = cid1.CurriculumItemId and cid1.DetailItemType = 11 and NULLIF(cid1.DetailItemText, '') is not null
LEFT JOIN ContentCatalog.dbo.CurriculumItemDetail cid2 ON ci.CurriculumItemId = cid2.CurriculumItemId and cid2.DetailItemType = 12
LEFT JOIN ContentCatalog.dbo.CurriculumItemDetail cid3 ON ci.CurriculumItemId = cid3.CurriculumItemId and cid3.DetailItemType = 7
LEFT JOIN ContentCatalog.dbo.CurriculumItemDetail cid4 ON ci.CurriculumItemId = cid4.CurriculumItemId and cid4.DetailItemType = 27
LEFT JOIN CDSDelivery.dbo.LearningObject AS dlo ON ci.CurriculumItemId = dlo.LearningObjectId
LEFT JOIN CDSScoring.dbo.LearningObject AS slo on ci.CurriculumItemId = slo.LearningObjectId
WHERE ci.CurriculumItemTypeId = @ItemTypeIdCustomJournal
AND ci.Valid = 1
-- SELECT * FROM #CDSDeliveryData WHERE CurriculumItemId IN (
-- SELECT CurriculumItemId FROM #CDSDeliveryData GROUP BY CurriculumItemId HAVING COUNT(CurriculumItemId) > 1
-- ) ORDER BY CurriculumItemId
DELETE FROM #CDSDeliveryData WHERE CurriculumItemId IN (
SELECT CurriculumItemId FROM #CDSDeliveryData GROUP BY CurriculumItemId HAVING COUNT(CurriculumItemId) > 1
)
SELECT * FROM #CDSDeliveryData
---
--Merge Item records In CDSScoring
MERGE INTO CDSScoring.dbo.LearningObject lo
USING (SELECT
ci.CurriculumItemId
,ci.PossibleScore
,ci.[LastUpdateDate]
,ci.[LastUpdateUser]
,ci.[auditCreateDate]
FROM #CDSDeliveryData ci
WHERE ci.Valid = 1
--AND ci.ScoringLearningObjectId IS NULL
) src
ON lo.LearningObjectId = src.CurriculumItemId
WHEN MATCHED
THEN UPDATE SET
PossibleScore = src.PossibleScore,
auditUpdateDate = src.LastUpdateDate,
auditUpdateUser = src.LastUpdateUser,
auditCreateDate = src.auditCreateDate
WHEN NOT MATCHED BY TARGET THEN
INSERT (LearningObjectId
,PossibleScore
,auditUpdateDate
,auditUpdateUser
,auditCreateDate)
VALUES (src.CurriculumItemId,
src.PossibleScore,
src.LastUpdateDate,
src.LastUpdateUser,
src.auditCreateDate);
--Merge Item records in Delivery
MERGE INTO CDSDelivery.dbo.LearningObject lo
USING (SELECT DISTINCT
ci.CurriculumItemId
,ci.CurriculumItemName
,cid.DetailItemText AS CurriculumItemDescription
,cast(cid1.DetailItemText as varchar(36)) AS PlayerId
,cid2.DetailItemText AS CustomParameters
,cid3.DetailItemText AS PartnerUrl
,cid4.DetailItemText AS PartnerIntegration
,ci.AvailableLanguages
,ci.LowGrade
,ci.HighGrade
,ci.[LastUpdateDate]
,ci.[LastUpdateUser]
,ci.[auditCreateDate]
FROM #CDSDeliveryData ci
LEFT JOIN ContentCatalog.dbo.CurriculumItemDetail cid ON ci.CurriculumItemId = cid.CurriculumItemId and cid.DetailItemType = 5
INNER JOIN ContentCatalog.dbo.CurriculumItemDetail cid1 ON ci.CurriculumItemId = cid1.CurriculumItemId and cid1.DetailItemType = 11 and NULLIF(cid1.DetailItemText, '') is not null
LEFT JOIN ContentCatalog.dbo.CurriculumItemDetail cid2 ON ci.CurriculumItemId = cid2.CurriculumItemId and cid2.DetailItemType = 12
LEFT JOIN ContentCatalog.dbo.CurriculumItemDetail cid3 ON ci.CurriculumItemId = cid3.CurriculumItemId and cid3.DetailItemType = 7
LEFT JOIN ContentCatalog.dbo.CurriculumItemDetail cid4 ON ci.CurriculumItemId = cid4.CurriculumItemId and cid4.DetailItemType = 27
WHERE ci.Valid = 1
--AND ci.DeliveryLearningObjectId IS NULL
) src
ON lo.LearningObjectId = src.CurriculumItemId
WHEN MATCHED
THEN UPDATE SET
LearningObjectName = src.CurriculumItemName,
LearningObjectDescription = src.CurriculumItemDescription,
PlayerId = src.PlayerId,
CustomParameters = src.CustomParameters,
PartnerUrl = src.PartnerUrl,
PartnerIntegration = src.PartnerIntegration,
AvailableLanguages = src.AvailableLanguages,
LowGrade = src.LowGrade,
HighGrade = src.HighGrade,
auditUpdateDate = src.LastUpdateDate,
auditUpdateUser = src.LastUpdateUser,
auditCreateDate = src.auditCreateDate
WHEN NOT MATCHED BY TARGET THEN
INSERT (LearningObjectId
,LearningObjectName
,LearningObjectDescription
,PlayerId
,CustomParameters
,PartnerUrl
,AvailableLanguages
,LowGrade
,HighGrade
,auditUpdateDate
,auditUpdateUser
,auditCreateDate)
VALUES (src.CurriculumItemId,
src.CurriculumItemName,
src.CurriculumItemDescription,
src.PlayerId,
src.CustomParameters,
src.PartnerUrl,
src.AvailableLanguages,
src.LowGrade,
src.HighGrade,
src.LastUpdateDate,
src.LastUpdateUser,
src.auditCreateDate);
|
Comments (0)
You can clone a snippet to your computer for local editing. Learn more.