SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Default Value
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

satheesh
Posting Yak Master

United Kingdom
151 Posts

Posted - 09/27/2012 :  06:30:21  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 09/27/2012 :  06:40:00  Show Profile  Reply with Quote
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
Go to Top of Page

satheesh
Posting Yak Master

United Kingdom
151 Posts

Posted - 09/27/2012 :  06:46:49  Show Profile  Reply with Quote
I tried but not working.Thanks for your reply chandu.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 09/27/2012 :  06:58:30  Show Profile  Reply with Quote



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
Go to Top of Page

satheesh
Posting Yak Master

United Kingdom
151 Posts

Posted - 09/27/2012 :  07:06:24  Show Profile  Reply with Quote
Its working perfectly!!Thanks chandu
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 09/27/2012 :  07:10:42  Show Profile  Reply with Quote
Welcome

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000