Days Calculation

[jovillanuev]

Hi Expert,

Guys, i need your help on how to do a script calculating the totalDays
using the formula (see below). i have to calculate the totaldays based on
different Supplier and Rtype and also if the totalday is less than the warranty period i have to indicate it as "YES" in warranty decision if greater than i will place a "NO"

Create Table MyTable
(ESN nvarchar(35),
 Supplier nvarchar(35),
 RType nvarchar(10),
 WarrantyPeriod int,
 TodaysDate datetime,
 Returndate datetime,
 Receivedate datetime,	 
 TotalDays int, 
WarrantyDecision nvarchar(5)
)


Insert into Mytable
Values('123','AC8','CRA','180','2011-18-2011','2011-04-29','2011-03-03')
Values('124','BEI','CRA','180','2011-18-2011','2011-04-29','2011-03-22')
Values('125','JAB','CRA','180','2011-18-2011','2011-04-30','2011-03-31')
Values('126','FIH','RA','180','2011-18-2011','2011-04-30','2011-01-17')

RESULT:
123--AC8--CRA--180--'2011-18-2011--2011-04-29--2011-03-03--19--YES
124--BEI--CRA--180--'2011-18-2011--2011-04-29--2011-03-03--19--YES
125--JAB--CRA--180--'2011-18-2011--2011-04-30--2011-03-31--45--YES
125--FIH--CRA--180--'2011-18-2011--2011-04-30--2011-01-17--118--YES

Formula:
AC8-BEI/CRA
Totaldays = TodaysDate - Returdate

JAB/CRA
Totaldays = TodaysDate - ReceiveDate

FIH/CRA
Totaldays = TodaysDate - ReceiveDate

Thank yu in advance.

Regards,

Jonel

[debasisdas]

What is the differnece between Returndate and Receivedate ?

No need to store TotalDays in the DB table, it can be calculated at run time for any requirement like reporting.

[urtrivedi]

Here is your query

select
ESN ,
 Supplier,
 RType ,
 WarrantyPeriod ,
 TodaysDate ,
 Returndate ,
 Receivedate ,	 
DATEDIFF(day,  case when Supplier ='AC8' OR Supplier ='BEI'THEN  returndate ELSE RECEIVEDATE END, todaysdate) TOTALDAYS,
CASE WHEN DATEDIFF(day,  case when Supplier ='AC8' OR Supplier ='BEI'THEN  returndate ELSE RECEIVEDATE END, todaysdate)<WarrantyPeriod THEN 'YES' else 'N0' END WarrantyDecision 
from mytable

[jovillanuev]

Hi Urtrivedi.

Thank you very much for this script...thumbs up...
btw, what if i have another set of formula with different supplier and i have also to include the RMA_TYPE. see the script.
the sample indicate only the AC8 and BEI supplier.

JAB/CRA  Totaldays = TodaysDate - ReceiveDate
FIH/CRA  Totaldays = TodaysDate - ReceiveDate


SELECT
	ESN ,
	Supplier,
	RType ,
	WarrantyPeriod ,
	TodaysDate ,
	Returndate ,
	Receivedate ,	 
	DATEDIFF(day, case when Supplier ='AC8' and Rtype='CRA' OR Supplier ='BEI' and Rtype='CRA' THEN  returndate ELSE RECEIVEDATE END, todaysdate) TOTALDAYS,
    CASE WHEN DATEDIFF(day,  case when Supplier ='AC8' and rtype='CRA' OR Supplier ='BEI' and Rtype='CRA' THEN  returndate ELSE RECEIVEDATE END, todaysdate)<WarrantyPeriod THEN 'YES' else 'N0' END WarrantyDecision 
from #MyTable

Thank you.

Jonel

[urtrivedi]

Use OR very carefully, CRA is applicable in all cases so no need to use it in query.
We must simply find in which case we will need recepitdate then for rest case we will use returndate

find the simplest conditions (only guess cases for returndate) then I will help you more on it.

[jovillanuev]

Hi Urtrivedi,

I got an error when i change the returndate with received date and received date with todays date. the '' what should be in this part.

By the way here is the exact calculation.

For CRA:
AC8/BEI/JAB/TRC = Todays Date – Received Date
FIH = Todays Date – Received Date – 3 days

For RA:
It should be less than warranty period and period is "YES"

SELECT
 	ESN ,
 	Supplier,
 	RType ,
 	WarrantyPeriod ,
 	TodaysDate ,
 	Returndate ,
 	Receivedate ,	
        Shipdate,  
 	DATEDIFF(day, case when Supplier IN ('AC8','BEI','JAB','TRC') and (Rtype='CRA')THEN  Receivedate
                       ELSE Todaysdate END, '') AS TOTALDAYS,
                  CASE WHEN DATEDIFF(day,  case when Supplier IN ('AC8', 'BEI') and (rtype='CRA') THEN  receivedate
                       ELSE todaysdate END, '') < WarrantyPeriod THEN 'YES' else 'N0' END WarrantyDecision 
From #MyTable

Again, Thank you in advance.

Jonel

[urtrivedi]

I have changed code but I think you are not still sure what you want in last 2 columns in case of rtype='RA', so I have used default condition.

select
ESN ,
 Supplier,
 RType ,
 WarrantyPeriod ,
 TodaysDate ,
 Returndate ,
 Receivedate ,	 
DATEDIFF(day,  case when RTYPE='CRA' AND Supplier in ('AC8' ,'BEI','JAB','TRC')   THEN  RECEIVEdate 
                    when Supplier in ('FIH') THEN  dateadd(day,-3,RECEIVEdate)
                    WHEN  RTYPE='RA' THEN RECEIVEdate END, todaysdate) TOTALDAYS

,CASE WHEN DATEDIFF(day,  case when RTYPE='CRA' AND Supplier in ('AC8' ,'BEI','JAB','TRC')   THEN  RECEIVEdate 
                    when Supplier in ('FIH') THEN  dateadd(day,-3,RECEIVEdate)
                    WHEN  RTYPE='RA' THEN RECEIVEdate END, todaysdate)
<WarrantyPeriod THEN 'YES' else 'N0' END WarrantyDecision 
from mytable

I am also not sure why you have today's date columns, I have used current system date below.

select
ESN ,
 Supplier,
 RType ,
 WarrantyPeriod ,
getdate() TodaysDate ,
 Returndate ,
 Receivedate ,	 
DATEDIFF(day,  case when RTYPE='CRA' AND Supplier in ('AC8' ,'BEI','JAB','TRC')   THEN  RECEIVEdate 
                    when Supplier in ('FIH') THEN  dateadd(day,-3,RECEIVEdate)
                    WHEN  RTYPE='RA' THEN RECEIVEdate END, getdate()) TOTALDAYS

,CASE WHEN DATEDIFF(day,  case when RTYPE='CRA' AND Supplier in ('AC8' ,'BEI','JAB','TRC')   THEN  RECEIVEdate 
                    when Supplier in ('FIH') THEN  dateadd(day,-3,RECEIVEdate)
                    WHEN  RTYPE='RA' THEN RECEIVEdate END, getdate())

<WarrantyPeriod THEN 'YES' else 'N0' END WarrantyDecision 
from mytable

[jovillanuev]

Thank you very much! Urtrivedi. Thumbs up guys...