Retrieve First Date and Last Date From SQL Query

Question

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 9-Jul-13 19:56pm

Sumon562

Updated 9-Jul-13 20:38pm

Naz_Firdouse

v4

Add a Solution

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

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

thanh_bkhn · Accepted Answer · 2013-07-09T20:47:00

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.

Kajal204 · Accepted Answer · 2013-07-09T21:18:00

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,

Retrieve First Date and Last Date From SQL Query

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0