如何获得日期明智的记录 [英] How to get date wise record

问题描述

我想从sql查询中获取按日期排列的数据.
请帮帮我.
我的表格定义如下:

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

*/

推荐答案

您可以在选择中使用合并来获取第一个非空日期值，例如:COALESCE(stDate, endDate)并对其进行排序. /> http://msdn.microsoft.com/en-us/library/aa258244％28v = sql.80％29.aspx [ ^ ]

祝你好运！

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!

从#tempTbl中选择*，其中stdate不为null


需要哪种类型的O/P.

Select * from #tempTbl where stdate is not null


what type of o/p is needed.

这篇关于如何获得日期明智的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！