i need help!! sql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

i need help!! sql

Author

Topic

kuku sql
Starting Member

6 Posts

Posted - 2012-10-25 : 03:10:41

SELECT T0.DocNum, T0.DocDate, T1.DocNum, T2.DocDate , t2.docduedate, datediff(day, T0.DocDate, T2.DocDate)-
(select case when MONTH(T0.docdate)= 2 then 28-day(T0.docdate) else (CASE when (MONTH(T0.docdate)= 4 OR MONTH(T0.docdate)= 6 OR MONTH(T0.docdate)= 9 OR MONTH(T0.docdate)= 11) then 30-day(T0.docdate) else 31-day(T0.docdate)end) end as 'yom' FROM OINV T0 INNER JOIN RCT2 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN ORCT T2 ON T0.ReceiptNum = T2.DocNum WHERE T0.DocStatus ='C') FROM OINV T0 INNER JOIN RCT2 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN ORCT T2 ON T0.ReceiptNum = T2.DocNum WHERE T0.DocStatus ='C'

what is the problem? please!!

I'm trying to calculate the days of late payment without the days in the month of invoice.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-25 : 03:22:44

TRY THIS..........

SELECT T0.DocNum, T0.DocDate,
T1.DocNum, T2.DocDate , t2.docduedate,
datediff(day, T0.DocDate, T2.DocDate)
- ~~(SELECT~~ case when MONTH(T0.docdate)= 2 then 28-day(T0.docdate)
~~ELSE(CASE~~ when (MONTH(T0.docdate)= 4 OR MONTH(T0.docdate)= 6 OR MONTH(T0.docdate)= 9 OR MONTH(T0.docdate)= 11)
then 30-day(T0.docdate)
else 31-day(T0.docdate)~~end)~~ end as 'yom'
FROM OINV T0
INNER JOIN RCT2 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN ORCT T2 ON T0.ReceiptNum = T2.DocNum
WHERE T0.DocStatus ='C')
FROM OINV T0 INNER JOIN RCT2 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN ORCT T2 ON T0.ReceiptNum = T2.DocNum WHERE T0.DocStatus ='C'

After Modification,

SELECT T0.DocNum, T0.DocDate,
T1.DocNum, T2.DocDate , t2.docduedate,
datediff(day, T0.DocDate, T2.DocDate)
- case when MONTH(T0.docdate)= 2 then 28-day(T0.docdate)
WHEN MONTH(T0.docdate) IN (4, 6, 9, 11) then 30-day(T0.docdate)
else 31-day(T0.docdate) end as 'yom'
FROM OINV T0 INNER JOIN RCT2 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN ORCT T2 ON T0.ReceiptNum = T2.DocNum WHERE T0.DocStatus ='C'

--
Chandu