Click here to Skip to main content
14,970,656 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Here is an SQL Queryand its result. How can I get the First and Last VoucherDate for each LoanID

SQL
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
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
Posted
Updated 9-Jul-13 20:38pm
v4
Comments
[no name] 10-Jul-13 2:17am
   
either sort the result by order by clause or use min,and max functions
Sumon562 10-Jul-13 2:19am
   
min and max dues not work

Try this:
SQL
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

The inner SQL statement is yours.
   
Hey,

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

SQL
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

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



Regards,
   

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