Update Table based on specific condition

Question

Dear Folks,

I need to update my salesinvoice table which have 3 column Year_Mo, SalesInvoiceGenerateFlag and SalesInvoicePaidFlag based on below condition

" update SalesInvoicePaidFlag=1 on where  [SalesInvoiceGenerateFlag]=1 for the current month and for the month prior, [SalesInvoicePaidFlag]=1"

See below example ..

 

Please Help !

Thanks,

Answer 1

Try the code bellow:

UPDATE your_table
SET SalesInvoicePaidFlag = 1
WHERE SalesInvoiceGenerateFlag = 1 
AND DATEPART(MONTH, GETDATE()) = DATEPART(MONTH, Year_mo)

UPDATE your_table
SET SalesInvoicePaidFlag = 1
WHERE SalesInvoicePaidFlag = 1 
OR DATEPART(MONTH, GETDATE()) = DATEPART(MONTH, Year_mo) - 1

Thanks in advance, Ciprian LUPU

Answer 2

Hi,

First off:

Update SalesInvoicePaidFlag to 1 Where SalesInvoicePaidFlag = 1.. defeats the use of the update :)
But as i assume its a dumbed down version of your query and you mostly want to know how to work with the dates..An example based on your data is:

CREATE TABLE #salesinvoice 
(
YearMo Nvarchar(10),
SalesInvoiceGenerateFlag bit,
SalesInvoicePaidFlag bit
)

insert into #salesinvoice values('2016-02', 1, 0)
insert into #salesinvoice values('2016-01', 1, 1)
insert into #salesinvoice values('2015-12', 1, 1)
insert into #salesinvoice values('2015-11', 1, 1)

/* Lazy programmer; i could also have done: Update #salesinvoice set SalesInvoicePaidFlag=1  but as i made the select before... it was easier :)*/
update SI
set SalesInvoicePaidFlag=1 
/*select CAST(SI.YearMo+'-01' as datetime), Datediff(Month, GetDate(), CAST(SI.YearMo+'-01' as datetime)) as TimeDif*/
FROM #salesinvoice as SI
WHERE SI.[SalesInvoiceGenerateFlag]=1
AND SI.[SalesInvoicePaidFlag]=1
AND Datediff(Month, GetDate(), CAST(SI.YearMo+'-01' as datetime)) between -1 and 0
/* 0 = current, -1 = previouse*/

with kind regards,

Sebastian

Answer 3

CREATE TABLE salesinvoice (Year_Mo Nvarchar(7),SalesInvoiceGenerateFlag char(1),SalesInvoicePaidFlag char(1))
insert into salesinvoice values('2016-02', 'Y', 'N'),('2016-01', 'Y', 'Y')
,('2015-12', 'Y', 'Y'),('2015-11', 'Y', 'Y'),('2015-10', 'Y', 'Y'),('2015-01', 'Y', 'Y')

;with mycte as (Select Year_Mo,
SalesInvoiceGenerateFlag,
SalesInvoicePaidFlag
,lead(SalesInvoicePaidFlag)Over(Order by Year_Mo DESC) leadSalesInvoicePaidFlag from salesinvoice)

Update mycte
Set SalesInvoicePaidFlag= 'Y'
Where SalesInvoiceGenerateFlag =  'Y'  and leadSalesInvoicePaidFlag= 'Y'

  
Select * from salesinvoice

drop table salesinvoice 

Answer 4

Hi AshishSingh_DWH,

For versions prior to 2012, you could refer to the following script.

UPDATE a
SET a.SalesInvoicePaidFlag = 'Y'
FROM salesinvoice a
CROSS APPLY (select top 1 SalesInvoicePaidFlag from salesinvoice b where b.Year_Mo < a.Year_Mo order by b.Year_Mo desc) c
WHERE a.SalesInvoiceGenerateFlag = 'Y' and c.SalesInvoicePaidFlag= 'Y'

---

Sam Zha
TechNet Community Support

Answer 5

select 
ROW_NUMBER() over (order by year_mo) r_n
,* into #salesinvoice from salesinvoice

update salesinvoice
set SalesInvoicePaidFlag = 'y'
where Year_Mo in (select st1.Year_Mo from #salesinvoice st
join #salesinvoice st1
on st.r_n = st1.r_n -1
where st.SalesInvoiceGenerateFlag = 'y'
and st.SalesInvoicePaidFlag = 'y')