CODEWITHSUNDEEP
sql-serversubstringconditional-statements
kelvin foo
kelvin foo

Reputation: 5

SUBSTRING with Condition

I would like to have this +3 apply when the paymentterm if is less than 45.

My current code is:

SELECT 
    [course_title],
    sblPOAmount,
    SBLInvoiceDate,
    paymentTerm,
    DATENAME(Day, DATEADD(day, SUBSTRING(paymentTerm, 1, 2) + 3, SBLInvoiceDate)) 
FROM 
    [A_Sys].[dbo].[Eventtbl]
WHERE 
    DATENAME(MONTH, DATEADD(day, SUBSTRING(paymentTerm, 1, 2) + 3, SBLInvoiceDate)) = 'June' 
    AND DATENAME(YEAR, DATEADD(day, SUBSTRING(paymentTerm, 1, 2) + 3, SBLInvoiceDate)) = '2017'
#

WITH SUM Statement (Summary)

SELECT
SUM(sblPOAmount) AS totalPOAmt,
case 
    when paymentTerm = '45 Days'
    then DATENAME(Day, DATEADD(day, SUBSTRING(paymentTerm, 1, 2) + 3, SBLInvoiceDate)) 
    else DATENAME(Day, DATEADD(day, SUBSTRING(paymentTerm, 1, 2) + 0, SBLInvoiceDate))
end 
FROM 
[A_Sys].[dbo].[Eventtbl] 
WHERE DATENAME(MONTH, DATEADD(day,SUBSTRING(paymentTerm, 1, 2)+3,SBLInvoiceDate))='June' 
AND DATENAME(YEAR, DATEADD(day,SUBSTRING(paymentTerm, 1, 2)+3,SBLInvoiceDate))='2017'
Group By paymentTerm,SBLInvoiceDate
#

Currently, i am not able to sum due to the group by

#

Current Output: +-----+---+ |10.60| 23| |0.00 |24 | |10.50|14 | +---------+

#

Expected output: just 21.10

Upvotes: 0

Views: 655

Answers (1)

S3S
S3S

Reputation: 25112

You are looking for a case statement...

SELECT 
    [course_title],
    sblPOAmount,
    SBLInvoiceDate,
    paymentTerm,
    case 
        when paymentTerm < 45 then DATENAME(Day, DATEADD(day, SUBSTRING(paymentTerm, 1, 2) + 3, SBLInvoiceDate)) 
        else DATENAME(Day, DATEADD(day, SUBSTRING(paymentTerm, 1, 2), SBLInvoiceDate))
    end
FROM 
    [A_Sys].[dbo].[Eventtbl]
WHERE 
    DATENAME(MONTH, DATEADD(day, SUBSTRING(paymentTerm, 1, 2) + 3, SBLInvoiceDate)) = 'June' 
    AND DATENAME(YEAR, DATEADD(day, SUBSTRING(paymentTerm, 1, 2) + 3, SBLInvoiceDate)) = '2017'

Based on the edit

SELECT 
    [course_title],
    sum(sblPOAmount),
    SBLInvoiceDate,
    paymentTerm,
    case 
        when paymentTerm < 45 then DATENAME(Day, DATEADD(day, SUBSTRING(paymentTerm, 1, 2) + 3, SBLInvoiceDate)) 
        else DATENAME(Day, DATEADD(day, SUBSTRING(paymentTerm, 1, 2), SBLInvoiceDate))
    end
FROM 
    [A_Sys].[dbo].[Eventtbl]
WHERE 
    DATENAME(MONTH, DATEADD(day, SUBSTRING(paymentTerm, 1, 2) + 3, SBLInvoiceDate)) = 'June' 
    AND DATENAME(YEAR, DATEADD(day, SUBSTRING(paymentTerm, 1, 2) + 3, SBLInvoiceDate)) = '2017'
GROUP BY
    [course_title],
    SBLInvoiceDate,
    paymentTerm,
    case 
        when paymentTerm < 45 then DATENAME(Day, DATEADD(day, SUBSTRING(paymentTerm, 1, 2) + 3, SBLInvoiceDate)) 
        else DATENAME(Day, DATEADD(day, SUBSTRING(paymentTerm, 1, 2), SBLInvoiceDate))
    end

Upvotes: 1

Related Questions