Je dois définir toutes les lignes sur 1 (vrai) dans la colonne PrimaryInvoiceFile lorsque mon InvoiceFileId n'a qu'un seul InvoiceId .
Cependant, si InvoiceId a plusieurs a plusieurs InvoiceFileId , je dois définir toutes les lignes PrimaryInvoiceFile sur 0 (faux) sauf pour le InvoiceFileId le plus récent ajouté en fonction de la date ajoutée.
Par exemple, il devrait ressembler à ceci:
|CreatedDate|InvoiceId|InvoiceFileId|PrimaryInvoiceFile| +-----------+---------+-------------+------------------+ |2019-01-16 | 1 | 1 | 1 | |2019-01-17 | 2 | 2 | 1 | |2019-01-18 | 3 | 3 | 0 | |2019-01-19 | 3 | 4 | 0 | |2019-01-20 | 3 | 5 | 1 | |2019-01-21 | 4 | 6 | 1 |
Je viens de a ajouté la migration de la colonne PrimaryInvoiceFile et a défini la valeur par défaut sur 0.
Toute aide à ce sujet serait grandement appréciée! Je me suis creusé la tête avec cela en essayant d'obtenir mes instructions de mise à jour pour effectuer cette mise à jour.
3 Réponses :
Veuillez essayer ceci:
DROP TABLE IF EXISTS #YourTableName;
CREATE TABLE #YourTableName(CreatedDate DATETIME2,InvoiceId INT, InvoiceFieldId INT,PrimaryInvoiceFile BIT);
INSERT INTO #YourTableName(CreatedDate,InvoiceId,InvoiceFieldId)VALUES
('2019-01-16',1,1),('2019-01-17',2,2),('2019-01-18',3,3),('2019-01-19',3,4),('2019-01-20',3,5),('2019-01-21',4,6)
;WITH Data AS (
SELECT t.CreatedDate,t.InvoiceId,t.InvoiceFieldId,t.PrimaryInvoiceFile,COUNT(*)OVER(PARTITION BY t.InvoiceId) AS [cnt]
FROM #YourTableName t
)
UPDATE d SET d.PrimaryInvoiceFile = CASE WHEN d.cnt = 1 THEN 1 ELSE 0 END
FROM Data d
;
SELECT t.CreatedDate,t.InvoiceId,t.InvoiceFieldId,t.PrimaryInvoiceFile
FROM #YourTableName t
;
DROP TABLE IF EXISTS #YourTableName;
Requête à jouer:
;WITH Data AS (
SELECT t.CreatedDate,t.InvoiceId,t.InvoiceFieldId,t.PrimaryInvoiceFile
,COUNT(*)OVER(PARTITION BY t.InvoiceId) AS [cnt]
FROM [YourTableName] t
)
UPDATE d SET d.PrimaryInvoiceFile = CASE WHEN d.cnt = 1 THEN 1 ELSE 0 END
FROM Data d
;
Vous pouvez utiliser rownumber tout en faisant votre mise à jour pour obtenir les résultats souhaités. De plus, triez par ordre décroissant pour obtenir la date la plus récente.
Permet de créer une table en gardant PrimaryInvoiceFile comme nul et en la mettant à jour ultérieurement.
CreatedDate invoiceID Invoicefield PrimaryInvoiceFile 2019-01-16 1 1 1 2019-01-17 2 2 1 2019-01-18 3 3 0 2019-01-19 3 4 0 2019-01-20 3 5 1 2019-01-21 4 6 1
Sortie:
select '2019-01-16' as CreatedDate, 1 as invoiceID, 1 as Invoicefield, null
as PrimaryInvoiceFile
into #temp
union all
select '2019-01-17' as CreatedDate, 2 as invoiceID, 2 as Invoicefield, null
as Primaryinvoicefile union all
select '2019-01-18' as CreatedDate, 3 as invoiceID, 3 as Invoicefield, null
as Primaryinvoicefile union all
select '2019-01-19' as CreatedDate, 3 as invoiceID, 4 as Invoicefield, null
as Primaryinvoicefile union all
select '2019-01-20' as CreatedDate, 3 as invoiceID, 5 as Invoicefield, null
as Primaryinvoicefile union all
select '2019-01-21' as CreatedDate, 4 as invoiceID, 6 as Invoicefield, null
as Primaryinvoicefile
update t
set Primaryinvoicefile = tst.Rownum
from #temp t
join
(Select invoiceID, Invoicefield,CreatedDate,
case when ROW_NUMBER() over (partition by invoiceID order by createddate desc) = 1
then 1 else 0 end as Rownum from #temp) tst
on tst.CreatedDate = t.CreatedDate
and tst.invoiceID = t.invoiceID
and tst.Invoicefield = t.Invoicefield
Case statement would make sure that you are value as 1 for only the rows where you have 1 row for invoice ID or for the most recent data.
select * from #temp
Incroyable! Merci de votre aide!
@tylern heureux que cela ait aidé :)
Si le PrimaryInvoiceFile par défaut est défini sur 0, vous devez mettre à jour le PrimaryInvoiceFile de la valeur CreatedDate maximale regroupée par InvoiceId.
UPDATE inv1
SET inv1.PrimaryInvoiceFile = 1
FROM invoices inv1 JOIN
(SELECT max(CreatedDate) as maxDate, InvoiceId
FROM invoices
GROUP BY InvoiceId ) as inv2
WHERE inv1.CreatedDate=inv2.maxDate and inv1.InvoiceId= inv2.InvoiceId