Click here to Skip to main content
15,891,204 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

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.
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;

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