Click here to Skip to main content
15,882,063 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I want to get date wise data from sql query.
Please help me.
my table definition like:

Create table #tempTbl
(	id int, 
	Note varchar(200),
	stDate Datetime NULL,
	crAmt Decimal(12,2),
	endDate Datetime NULL,
	dbAmt Decimal(12,2)
)

Insert into #tempTbl (id,Note,stDate,crAmt,dbAmt)values(1,'Test1','2012-06-01',1000,0);
Insert into #tempTbl (id,Note,crAmt,endDate,dbAmt)values(2,'Test2',0,'2012-06-01',1000);
Insert into #tempTbl (id,Note,stDate,crAmt,dbAmt)values(3,'Test3','2012-06-02',2000,0);
Insert into #tempTbl (id,Note,stDate,crAmt,dbAmt)values(4,'Test4','2012-06-03',3000,0);
Insert into #tempTbl (id,Note,crAmt,endDate,dbAmt)values(5,'Test5',0,'2012-06-02',1000);
Insert into #tempTbl (id,Note,crAmt,endDate,dbAmt)values(6,'Test6',0,'2012-06-06',1000);
Insert into #tempTbl (id,Note,stDate,crAmt,dbAmt)values(7,'Test7','2012-06-05',2000,0);
 Select  * from #tempTbl 
Drop table #tempTbl 
/* i want to Out put like:
id Note    stDate       crAmt   endDate   dbAmt      
1	Test1	2012-06-01 	1000.00	NULL	   0.00
2	Test2	NULL	    0.00	2012-06-01 1000.00
3	Test3	2012-06-02 	2000.00	NULL	   0.00
5	Test5	NULL	    0.00	2012-06-02 1000.00
4	Test4	2012-06-03  3000.00	NULL	   0.00
7	Test7	2012-06-05  2000.00	NULL	   0.00
6	Test6	NULL	    0.00	2012-06-06 1000.00

*/
Posted
Updated 7-Jun-12 22:36pm
v2

You could use coalesce in your select to get the first non-null date value, something like: COALESCE(stDate, endDate) and order on that.
http://msdn.microsoft.com/en-us/library/aa258244%28v=sql.80%29.aspx[^]

Good luck!
 
Share this answer
 
Comments
Savalia Manoj M 8-Jun-12 6:21am    
Thanks, But how to apply it in two different date field? any idea?
E.F. Nijboer 8-Jun-12 17:07pm    
How do you mean? The coalesce will be a column in the result dataset with either the stDate or endDate. Give it a name and use it to order the result, something like:
SELECT *, COALESCE(stDate, endDate) AS seDate ORDER BY seDate;
hi,

Select * from #tempTbl where stdate is not null


what type of o/p is needed.
 
Share this answer
 
Comments
Savalia Manoj M 8-Jun-12 7:29am    
Please read question, i already add comment for output.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900