Default Value - 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

Default Value

Author

Topic

satheesh
Posting Yak Master

152 Posts

Posted - 2012-09-27 : 06:30:21

Dear All,

I have a simple select statement based on date.I am producing an automated file on daily basis.

I tried fill all the column with some default data when the date has no record!but not working

For ex:

select emailaddress,
Policynumber
saledate
from
policy
where policy.saledate >=DATEADD(dd,DATEDIFF(dd,0,GETDATE())-1,0)AND policy.saledate <DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)

If there was no sale on yesterday now the file is empty.But i need a result as

NOTAVAIL,NOTAVAIL,26.09.2012

How to add this default value when the date has no data.Any help will be highly appreciated!

Thanks

SG

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-09-27 : 06:40:00

hi,
I am not sure.. But try this once

select Coalesce(emailaddress, 'NOTAVAIL'),
coalesce(Policynumber, 'NOTAVAIL')
coalesce(saledate, dateadd(dd, -1, getdate()))
from
policy
where policy.saledate >=DATEADD(dd,DATEDIFF(dd,0,GETDATE())-1,0)AND policy.saledate <DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)

--
Chandu

satheesh
Posting Yak Master

152 Posts

Posted - 2012-09-27 : 06:46:49

I tried but not working.Thanks for your reply chandu.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-09-27 : 06:58:30

 

if exists
 (select emailaddress, Policynumber, saledate from policy
	where policy.saledate >=DATEADD(dd,DATEDIFF(dd,0,GETDATE())-1,0)AND policy.saledate <DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
 )
	select emailaddress, Policynumber, saledate from  policy
	where policy.saledate >=DATEADD(dd,DATEDIFF(dd,0,GETDATE())-1,0)AND policy.saledate <DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
else
	select 'NOTAVAIL', 'NOTAVAIL', DATEADD(dd, -1, getdate())

--
Chandu

satheesh
Posting Yak Master

152 Posts

Posted - 2012-09-27 : 07:06:24

Its working perfectly!!Thanks chandu

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-09-27 : 07:10:42

Welcome