| Author |
 Topic  |
|
|
satheesh
Posting Yak Master
United Kingdom
124 Posts |
 Posted - 09/27/2012 : 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
Flowing Fount of Yak Knowledge
India
1419 Posts |
Posted - 09/27/2012 : 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
United Kingdom
124 Posts |
Posted - 09/27/2012 : 06:46:49
|
I tried but not working.Thanks for your reply chandu.
|
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1419 Posts |
Posted - 09/27/2012 : 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
United Kingdom
124 Posts |
Posted - 09/27/2012 : 07:06:24
|
| Its working perfectly!!Thanks chandu |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1419 Posts |
Posted - 09/27/2012 : 07:10:42
|
Welcome
--
Chandu |
|
|
| |
Topic |
|
|
|
Default Value
