is null statement - 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

SQL Server 2000 Forums

Transact-SQL (2000)

is null statement

Author

Topic

nt4vn
Yak Posting Veteran

98 Posts

Posted - 2008-11-19 : 07:12:24

hello,

Could you please let me know what is wrong with below statement. I got error message: "Wrong number of parameter to function IsNUll"

IIf(IsNull([dba.IT_LETTER_2008].[UPDATE_DATE]),0,
[dba.IT_LETTER_2008].[UPDATE_DATE]-[dba.IT_LETTER_2008].[LETTER_DATE]) AS [Days to Response]

Thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-19 : 07:29:24

may be this
IIf([dba.IT_LETTER_2008].[UPDATE_DATE] Is Nothing,0,
[dba.IT_LETTER_2008].[UPDATE_DATE]-[dba.IT_LETTER_2008].[LETTER_DATE]) AS [Days to Response]

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-19 : 07:30:31

you have 3 parameters there.
1)([dba.IT_LETTER_2008].[UPDATE_DATE])
2)0
3)[dba.IT_LETTER_2008].[UPDATE_DATE]-[dba.IT_LETTER_2008].[LETTER_DATE])

ISNULL should have 2 parameters only,ISNULL ( check_expression , replacement_value )

nt4vn
Yak Posting Veteran

98 Posts

Posted - 2008-11-19 : 08:40:40

I rewrite like below, but still got syntax error. Any suggestions. Thanks,

'Days to Response' =
Case(dba.IT_LETTER_2008.UPDATE_DATE)
when isnull((dba.IT_LETTER_2008.UPDATE_DATE),0) then
DATEDIFF(DAY, (dba.IT_LETTER_2008.UPDATE_DATE), (dba.IT_LETTER_2008.LETTER_DATE) )
else 0
END,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-19 : 08:53:23

quote:
Originally posted by nt4vn

I rewrite like below, but still got syntax error. Any suggestions. Thanks,

'Days to Response' =
Case(dba.IT_LETTER_2008.UPDATE_DATE)
when isnull((dba.IT_LETTER_2008.UPDATE_DATE),0) then
DATEDIFF(DAY, (dba.IT_LETTER_2008.UPDATE_DATE), (dba.IT_LETTER_2008.LETTER_DATE) )
else 0
END,

no need of case here you just need the below

ISNULL(DATEDIFF(DAY, (dba.IT_LETTER_2008.UPDATE_DATE), (dba.IT_LETTER_2008.LETTER_DATE) ),0)

nt4vn
Yak Posting Veteran

98 Posts

Posted - 2008-11-19 : 09:10:33

no need of case here you just need the below

ISNULL(DATEDIFF(DAY, (dba.IT_LETTER_2008.UPDATE_DATE), (dba.IT_LETTER_2008.LETTER_DATE) ),0)

[/quote]

Thank you Visakh16, it work well. However, I want to show the value of date to response in decimal, not round to even number. For example, right now it show datediff=3, but it actually show 3.56 days. Can you help?

Thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-19 : 09:17:29

datediff returns only integer values. probably what you want is this

ISNULL(DATEDIFF(HOUR, (dba.IT_LETTER_2008.UPDATE_DATE), (dba.IT_LETTER_2008.LETTER_DATE) )/24.0,0)

nt4vn
Yak Posting Veteran

98 Posts

Posted - 2008-11-19 : 09:21:20

quote:
Originally posted by visakh16

datediff returns only integer values. probably what you want is this

ISNULL(DATEDIFF(HOUR, (dba.IT_LETTER_2008.UPDATE_DATE), (dba.IT_LETTER_2008.LETTER_DATE) )/24.0,0)

Very appreciated your help. Thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-19 : 10:10:04

Welcome

nt4vn
Yak Posting Veteran

98 Posts

Posted - 2008-11-19 : 11:30:43

Hi Visakh16 or someone:

Here is my final query. It worked now. Thank you you all for identify problem.

SELECT Count(y.CC_FILE_NO) AS 'Total Returns Filed',
Avg(y.Days_to_Return_Filed) AS 'Average Days to File Return'
FROM
(

SELECT a.CC_FILE_NO,
a..LETTER_DATE,
ISNULL(DATEDIFF(Day, (a.LETTER_DATE), (b.RET_STATUS_DATE) ),0) AS Days_to_Return_Filed,
b.ret_status_date
FROM
(
SELECT dba.IT_LETTER_2008.LETTER_DATE,
dba.IT_LETTER_2008.Lawyer_ID, dba.IT_LETTER_CASE_2008.CC_FILE_NO
FROM dba.IT_LETTER_CASE_2008
INNER JOIN dba.IT_LETTER_2008 on dba.it_letter_2008.lawyer_id=dba.it_letter_case_2008.lawyer_id
inner join (SELECT dba.IT_LETTER_CASE_2008.LAWYER_ID
FROM
(SELECT distinct a.case_id, a.status_id, a.reason_id
from dba.tkcase a
inner join dba.tkstage b on b.stage_id=a.stage_id and a.status_id=b.case_status_id
where a.status_id =1 and a.case_type_id=1
and a.reason_id=1
and b.stage_id not in (3,4,5,26,28,29,37,42,64,74,115,116,118,119,121,122,124,125,127,194,217,236,237,238,239,240,245,256,276,277,282,292,
312,313,318,319,320,321,322,323,324,325,363) )p
INNER JOIN dba.IT_LETTER_CASE_2008 ON p.case_id = dba.IT_LETTER_CASE_2008.CASE_ID
GROUP BY dba.IT_LETTER_CASE_2008.LAWYER_ID
HAVING Count(dba.IT_LETTER_CASE_2008.CASE_ID)>2
) a on a.lawyer_id=dba.it_letter_2008.lawyer_id
and dba.IT_LETTER_CASE_2008.LAWYER_ID = dba.IT_LETTER_2008.Lawyer_ID
WHERE dba.IT_LETTER_2008.LETTER_DATE Is Not Null
) a
inner join (
SELECT *
FROM DBA.ITRETGEN
WHERE RET_STATUS_DATE >= '2008-09-02'
AND ACN IN ('101','102','201','202')
) b on b.rootkey=a.cc_file_no
WHERE ISNULL(DATEDIFF(DAY, (a.LETTER_DATE), (RET_STATUS_DATE) ),0) >0
) y

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-11-19 : 11:49:01

What error message are you getting?

It looks like you are missing some parenthesis for one. And I see another issue. See below. I remormatted the query, maybe that will help to see the error:

SELECT
	COUNT(y.CC_FILE_NO) AS 'Total Returns Filed',
	AVG(y.Days_to_Return_Filed) AS 'Average Days to File Return'
FROM
	(
		SELECT
			Y.CC_FILE_NO,
			Y.LETTER_DATE,
			ISNULL(DATEDIFF(D,(Y.LETTER_DATE),(Y.RET_STATUS_DATE))) AS Days_to_Return_Filed,
			Y.RET_STATUS_DATE
		FROM
			(
				SELECT
					dba.IT_LETTER_2008.LETTER_DATE,
					dbA.IT_LETTER_2008.Lawyer_ID,
					dba.IT_LETTER_CASE_2008.CC_FILE_NO,
				FROM
					dba.IT_LETTER_CASE_2008
				INNER JOIN 
					dba.IT_LETTER_2008
					ON DBA.IT_LETTER_2008.LAWYER_ID = DBA.IT_LETTER_CASE_2008.LAWYER_ID
				INNER JOIN
					(
						SELECT
							dba.it_letter_case_2008.lawyer_id
							(
								SELECT DISTINCT
									a.case_id,
									a.status_id,
									a.reason_id
								FROM
									dba.tkcase a
								INNER JOIN 
									dba.tkstage b
									ON b.stage_id = a.stage_id
									AND a.status_id = b.case_status_id
								WHERE
									a.status_id = 1
									AND a.case_type_id = 1
									AND a.reason_id = 1
									AND b.stage_id NOT IN(3, 4, 5, 26, 28, 29, 37, 42, 64, 74, 115, 
							116, 118, 119, 121, 122, 124, 125, 127, 194, 
							217, 236, 237, 238, 239, 240, 245, 256, 276, 
							277, 282, 292, 312, 313, 318, 319, 320, 321, 
							322, 323, 324, 325, 363) 
							AS a ON a.case_id = dba.IT_LETTER_CASE_2008.CASE_ID		
								GROUP BY 
									dba.IT_LETTER_CASE_2008.LAWYER_ID
								HAVING
									COUNT(dba.IT_LETTER_CASE_2008.CASE_ID) > 2
							) AS b 
							ON dba.IT_LETTER_2008.Lawyer_ID = b.LAWYER_ID
						WHERE
							dba.IT_LETTER_2008.LETTER_DATE IS NOT NULL
						INNER JOIN
							(
								SELECT
									*
								FROM
									dba.itretgen
								WHERE
									ret_status_date >= '2008-09-02'
									AND acn IN('101', '102', '201', '202')
							) AS x
							ON [qry_letter's_sent_2008].CC_FILE_NO = x.ROOTKEY
						WHERE
							ISNULL(DATEDIFF(DAY,(X.LETTER_DATE),(RET_STATUS_DATE) > 0))
					) Y

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-19 : 11:59:41

lots of problem.. ive corrected some. it seems like you're missing a internediate step as paranthesis dont match

SELECT Count(y.CC_FILE_NO) AS 'Total Returns Filed',
Avg(y.Days_to_Return_Filed) AS 'Average Days to File Return'
FROM
(
 SELECT Y.CC_FILE_NO,
 Y.LETTER_DATE,
 ISNULL(DATEDIFF(D, (Y.LETTER_DATE),       (Y.RET_STATUS_DATE)),0) AS Days_to_Return_Filed,
 Y.RET_STATUS_DATE
 FROM 
(
SELECT dba.IT_LETTER_2008.LETTER_DATE,
dbA.IT_LETTER_2008.Lawyer_ID,
dba.IT_LETTER_CASE_2008.CC_FILE_NO    ,
FROM dba.IT_LETTER_CASE_2008
INNER JOIN dba.IT_LETTER_2008 ON DBA.IT_LETTER_2008.LAWYER_ID=DBA.IT_LETTER_CASE_2008.LAWYER_ID
inner join 
 (select dba.it_letter_case_2008.lawyer_id
FROM
  (
  SELECT distinct a.case_id, a.status_id, a.reason_id
  from dba.tkcase a
  inner join dba.tkstage b on b.stage_id=a.stage_id 
  and    a.status_id=b.case_status_id
  where a.status_id =1 and a.case_type_id=1
  and a.reason_id=1
  and b.stage_id not in  
(3,4,5,26,28,29,37,42,64,74,115,116,118,119,121,122,124,125,127,194,217
 ,236,237,238,239,240,245,256,276,277,282,292,
312,313,318,319,320,321,322,323,324,325,363)
  )p) AS a ON a.case_id = dba.IT_LETTER_CASE_2008.CASE_ID
GROUP BY dba.IT_LETTER_CASE_2008.LAWYER_ID
HAVING Count(dba.IT_LETTER_CASE_2008.CASE_ID)>2
) as b ON dba.IT_LETTER_2008.Lawyer_ID = b.LAWYER_ID
WHERE dba.IT_LETTER_2008.LETTER_DATE Is Not Null
INNER JOIN (select *
from dba.itretgen
where ret_status_date>='2008-09-02'
and acn in ('101', '102', '201', '202')
) as x ON [qry_letter's_sent_2008].CC_FILE_NO = x.ROOTKEY
)something here
WHERE ISNULL(DATEDIFF(DAY, (X.LETTER_DATE), (RET_STATUS_DATE)>0))
) Y

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources