从SQL查询中检索第一个日期和最后日期 [英] Retrieve First Date and Last Date From SQL Query

问题描述

这是一个SQL Query及其结果。如何获得每个贷款的第一个和最后一个凭证日期

Here is an SQL Queryand its result. How can I get the First and Last VoucherDate for each LoanID

select distinct TD.LoanID, TI.VoucherDate from dbo.tblTransactionInformation TI
  inner Join dbo.tblTransactionDetails TD on TI.TRCode = TD.TRCode 
  Where TI.CompanyCode='CMP-007'
  and TI.FyrCode='Fyr-014'
  and  TI.VoucherDate between '01 - Jul - 2011' and '30-Jun-2012' 
  and (TI.StateStatus='Approved' or TI.StateStatus ='Checked') 
  And TD.AccountCode='ACC-000577' 
  Group by TD.LoanID, TI.VoucherDate

0029390026095 2011-08-08 00:00 ：00.000

0029390026095 2011-10-23 00：00：00.000

0029390026095 2011-10-31 00：00：00.000

0029390026095 2011 -11-20 00：00：00.000
$ b $ 00 0029390026095 2011-12-05 00：00：00.000

0029390026095 2011-12-28 00：00：00.000
0029390026095 2012-02-15 00：00：00.000

0029390026185 2011-08-25 00：00：00.000

002939002 6185 2011-11-20 00：00：00.000

0029390026185 2011-12-02 00：00：00.000

0029390026185 2011-12-22 00：00：00.000

0029390026185 2012-02-15 00：00：00.000

0029390026185 2012-03-07 00：00：00.000

0029390026185 2012-03- 22 00：00：00.000
$ b $ 00 0029390026185 2012-03-28 00：00：00.000

0029390026383 2011-10-31 00：00：00.000

0029390026383 2012-01-24 00：00：00.000

0029390026383 2012-02-15 00：00：00.000

0029390026383 2012-02-29 00:00： 00.000

0029390026383 2012-03-07 00：00：00.000

0029390026383 2012-03-13 00：00：00.000

0029390026383 2012- 03-22 00：00：00.000
$ b $ 00 0029390026383 2012-03-28 00：00：00.000

0029390026383 2012-04-08 00：00：00.000

0029390026383 2012-04-16 00：00：00.000

0029390026392 2011-10-31 00：00：00.000

0029390026392 2011-12-28 00： 00：00.000

0029390026392 2012-01-24 00：00：00.000

0029390026392 2012-02-06 00：00：00.000

0029390026392 2012-02-29 00：00：00.000

0029390026392 2012-03- 13 00：00：00.000
$ b $ 00 0029390026392 2012-03-28 00：00：00.000

0029390026392 2012-04-16 00：00：00.000

0029390026401 2011-10-31 00：00：00.000

0029390026401 2012-01-18 00：00：00.000

0029390026401 2012-03-07 00:00： 00.000

0029390026401 2012-03-13 00：00：00.000

0029390026401 2012-03-28 00：00：00.000

0029390026401 2012- 04-16 00：00：00.000

0029390026401 2012-05-16 00：00：00.000

0029390026401 2012-05-21 00：00：00.000

0029390026482 2011-12-07 00：00：00.000

0029390026482 2012-02-09 00：00：00.000

0029390026482 2012-02-23 00： 00：00.000
$ b $ 00 0029390026482 2012-04-08 00：00：00.000

0029390026947 2012-03-07 00：00：00.000

0029390026947 2012-04-08 00：00：00.000

0029390026947 2012 -05-17 00：00：00.000

0029390026947 2012-05-21 00：00：00.000

0029390026947 2012-06-14 00：00：00.000

0029390026095 2011-08-08 00:00:00.000
0029390026095 2011-10-23 00:00:00.000
0029390026095 2011-10-31 00:00:00.000
0029390026095 2011-11-20 00:00:00.000
0029390026095 2011-12-05 00:00:00.000
0029390026095 2011-12-28 00:00:00.000
0029390026095 2012-02-15 00:00:00.000
0029390026185 2011-08-25 00:00:00.000
0029390026185 2011-11-20 00:00:00.000
0029390026185 2011-12-02 00:00:00.000
0029390026185 2011-12-22 00:00:00.000
0029390026185 2012-02-15 00:00:00.000
0029390026185 2012-03-07 00:00:00.000
0029390026185 2012-03-22 00:00:00.000
0029390026185 2012-03-28 00:00:00.000
0029390026383 2011-10-31 00:00:00.000
0029390026383 2012-01-24 00:00:00.000
0029390026383 2012-02-15 00:00:00.000
0029390026383 2012-02-29 00:00:00.000
0029390026383 2012-03-07 00:00:00.000
0029390026383 2012-03-13 00:00:00.000
0029390026383 2012-03-22 00:00:00.000
0029390026383 2012-03-28 00:00:00.000
0029390026383 2012-04-08 00:00:00.000
0029390026383 2012-04-16 00:00:00.000
0029390026392 2011-10-31 00:00:00.000
0029390026392 2011-12-28 00:00:00.000
0029390026392 2012-01-24 00:00:00.000
0029390026392 2012-02-06 00:00:00.000
0029390026392 2012-02-29 00:00:00.000
0029390026392 2012-03-13 00:00:00.000
0029390026392 2012-03-28 00:00:00.000
0029390026392 2012-04-16 00:00:00.000
0029390026401 2011-10-31 00:00:00.000
0029390026401 2012-01-18 00:00:00.000
0029390026401 2012-03-07 00:00:00.000
0029390026401 2012-03-13 00:00:00.000
0029390026401 2012-03-28 00:00:00.000
0029390026401 2012-04-16 00:00:00.000
0029390026401 2012-05-16 00:00:00.000
0029390026401 2012-05-21 00:00:00.000
0029390026482 2011-12-07 00:00:00.000
0029390026482 2012-02-09 00:00:00.000
0029390026482 2012-02-23 00:00:00.000
0029390026482 2012-04-08 00:00:00.000
0029390026947 2012-03-07 00:00:00.000
0029390026947 2012-04-08 00:00:00.000
0029390026947 2012-05-17 00:00:00.000
0029390026947 2012-05-21 00:00:00.000
0029390026947 2012-06-14 00:00:00.000

推荐答案

试试这个：

Try this:

Select LoanID, Max(VoucherDate) as VoucherDateMax, Min(VoucherDate) as VoucherDateMin from
(
select distinct TD.LoanID as LoanID, TI.VoucherDate as VoucherDate from dbo.tblTransactionInformation TI
  inner Join dbo.tblTransactionDetails TD on TI.TRCode = TD.TRCode 
  Where TI.CompanyCode='CMP-007'
  and TI.FyrCode='Fyr-014'
  and  TI.VoucherDate between '01 - Jul - 2011' and '30-Jun-2012' 
  and (TI.StateStatus='Approved' or TI.StateStatus ='Checked') 
  And TD.AccountCode='ACC-000577' 
  Group by TD.LoanID, TI.VoucherDate
)
Group by LoanID

内部SQL语句是你的。

The inner SQL statement is yours.

嘿，



我在这里给出一般数据的简单示例。 Plz根据您的需要进行修改。

Hey,

I am giving here simple example with general data. Plz modify as per your need.

SELECT FIRST.ID,FIRST.DATE FROM
    (SELECT TOP 1 FC AS DATE,ID
    FROM APPLICATIONTABLE
    ORDER BY FC ASC) AS FIRST
UNION
SELECT LAST.ID,LAST.DATE FROM
    (SELECT TOP 1 FC AS DATE,ID
    FROM APPLICATIONTABLE
    ORDER BY FC DESC) AS LAST

它会显示如下的输出

It will shows ouput like below

ID	DATE
2	2009-08-03 10:59:15.977
26	2011-02-15 09:57:15.733

问候，

Regards,

这篇关于从SQL查询中检索第一个日期和最后日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！