locked
Update Table based on specific condition RRS feed

  • 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,

    • Changed type Tom Phillips Tuesday, February 9, 2016 2:07 PM
    Tuesday, February 9, 2016 12:58 PM

Answers

  • 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

    Wednesday, February 10, 2016 2:35 AM

All replies

  • 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


    Tuesday, February 9, 2016 1:12 PM
  • 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





    Tuesday, February 9, 2016 1:24 PM
  • 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 

    Tuesday, February 9, 2016 2:40 PM
  • 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

    Wednesday, February 10, 2016 2:35 AM
  • 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')
    Wednesday, February 10, 2016 6:38 AM