locked
Finding first and last date RRS feed

  • Question

  • Hello,

    Is there a way to find out the first and last date value of a field?

    Thanks in advance!
    Martin

     

    Monday, May 24, 2010 7:31 AM

Answers

  • You can use MIN & MAX function.

    If not please give your question with examples...


    Bipin. P | ERNST & YOUNG SSC
    • Marked as answer by KJian_ Friday, May 28, 2010 7:28 AM
    Monday, May 24, 2010 7:37 AM
  • Hi, you can use MAX and MIN functions. Take a look at the following script:

    create

     

    table Dates

    (

    id

    int identity(1,1) primary key,

    [Date]

    datetime not null

    )

    go

    --insert 1st,2nd and 3rd of february

    insert

     

    into Dates values('20100101'),('20100102'),('20100103')

    --getting the max(February 3rd) and min(Februrary 1st)

    select

     

     

    MAX(Date) firstDate, MIN(Date) lastDate

    from

     

    Dates

    Monday, May 24, 2010 8:21 AM
  • You're probably adding your Item.Transaction_date into GROUP BY fields list and thus these functions are ignored. You need to show us your current SQL statement to determine the exact problem.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by KJian_ Friday, May 28, 2010 7:28 AM
    Tuesday, May 25, 2010 3:32 PM
  • DECLARE @length int;
    
    IF len(@toloccod) = 0
     SET @length = LEN(@fromloccod) 
    ELSE 
     SET @length = LEN(@toloccod);
    
    SELECT
    swt.whscod AS 'Store',
    swt.loccod AS 'Location', 
    swt.itmcod AS 'Item', 
    itm.itmnam + itm.aitmnm AS 'Item Name', 
    sum(swt.stcqty) AS 'Quantity',
    MIN(idl.credat) AS [First date] 
    MAX(idl.credat) AS [Last date]
    
    
    FROM sunshine.swt AS swt
    INNER JOIN sunshine.itm AS itm ON swt.itmcod = itm.itmcod
    INNER JOIN sunshine.sw1 AS sw1 ON swt.itmcod = sw1.itmcod AND swt.whscod = sw1.whscod
    INNER JOIN sunshine.idl AS idl ON idl.idncod = swt.idncod
    
    WHERE 
       (swt.whscod = @whscod OR @whscod = 'ALL') 
       AND (LEFT(swt.loccod,@length) >= LEFT(@fromloccod,@length) OR @fromloccod = '') AND (LEFT(swt.loccod,@length) <=   @toloccod OR @toloccod = '')
       AND swt.loccod IS NOT NULL
    
    GROUP BY swt.whscod, swt.loccod, swt.itmcod, itm.itmnam, itm.aitmnm
    

    I excluded extra group by conditions (I only group by Store, Location, Item's info) and added sum of Quantity.

    Also, you may want to take a look at this blog

    Do you use Column=@Param OR @Param IS NULL in your WHERE clause? Don't, it doesn't perform

    once you get your select working.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by mp703 Friday, June 11, 2010 6:44 AM
    Wednesday, June 9, 2010 1:42 PM

All replies

  • You can use MIN & MAX function.

    If not please give your question with examples...


    Bipin. P | ERNST & YOUNG SSC
    • Marked as answer by KJian_ Friday, May 28, 2010 7:28 AM
    Monday, May 24, 2010 7:37 AM
  •  Public Shared Function BOM(ByVal argDate As Date) As Date
        Dim result As Date
        result = CheckDate(argDate, "01/MMM/yyyy")
        BOM = result
      End Function
    
      Public Shared Function EOM(ByVal argDate As Date) As Date
        Dim result As Date
        result = BOM(argDate)
        result = DateAdd(DateInterval.Month, 1, result)
        result = DateAdd(DateInterval.Day, -1, result)
        EOM = result
      End Function

    pass any date using EOM(12/May/2010)

    you would get the month end value and to get first date simply do:

    Date="01/" & Date.Month & "/" Date.Year

    Monday, May 24, 2010 7:47 AM
  • Hi, you can use MAX and MIN functions. Take a look at the following script:

    create

     

    table Dates

    (

    id

    int identity(1,1) primary key,

    [Date]

    datetime not null

    )

    go

    --insert 1st,2nd and 3rd of february

    insert

     

    into Dates values('20100101'),('20100102'),('20100103')

    --getting the max(February 3rd) and min(Februrary 1st)

    select

     

     

    MAX(Date) firstDate, MIN(Date) lastDate

    from

     

    Dates

    Monday, May 24, 2010 8:21 AM
  • Thanks for the replies! I´ve tried using MIN and MAX funtions, like so:

    min(stock.regdat) AS 'Registry Date',

    max(stock.regdat)) AS 'Last Transaction'

    This doesn´t work. Both fields seem to show the registyry date of the item. What should I do instead, please?

    Thanks in advance!

     

     

    Monday, May 24, 2010 10:34 AM
  • The above should work. Can you show your exact select statement, please?
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, May 24, 2010 3:05 PM
  • Provide your example tables with data and expected result
    Monday, May 24, 2010 5:11 PM
  • Thanks for the replies everybody!

    This is what I am trying to do. I want to retrieve the first and last transaction date of multiple items that are contained in a field called item.transaction_date. The T-SQL looks like so:

    min(item.transcation_date) AS 'Date item registred',

    max(item.transaction_date) AS 'Date last item transaction',

    The result I am looking for is a report that shows a list gouping the first and last transaction dates of every item of the field item.transcation_date. The result now is however that all transactions of the field are shown. The min and max functions are ignored. I was thinking maybe it is not possible to apply the min and max functions on multiple items like this? Is it only possible to apply these functions on a field containing a single item?

    Thanks in advance!
    Martin

     

    Tuesday, May 25, 2010 2:54 PM
  • You're probably adding your Item.Transaction_date into GROUP BY fields list and thus these functions are ignored. You need to show us your current SQL statement to determine the exact problem.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by KJian_ Friday, May 28, 2010 7:28 AM
    Tuesday, May 25, 2010 3:32 PM
  • Here´s another example of a similar problem. As I mentioned before, the MIN and MAX functions don´t work. Naom is probably correct in that I´m using a GROUP BY but, how should I do instead?

    DECLARE @length int;

    IF @toloccod = '' SET @length = LEN(@fromloccod) ELSE SET @length = LEN(@toloccod);

    SELECT
    swt.whscod AS 'Store',
    swt.loccod AS 'Location',
    swt.itmcod AS 'Item',
    itm.itmnam + itm.aitmnm AS 'Item Name',
    swt.stcqty AS 'Quantity',
    MIN(VARCHAR,idl.credat,23) AS 'First date               --THIS DOESN`T WORK. I want to retrieve the first date
    MAX(VARCHAR,idl.credat,23) AS 'Last date              --THIS DOESN`T WORK  I want to retrieve the last date


    FROM sunshine.swt AS swt
    INNER JOIN sunshine.itm AS itm ON swt.itmcod = itm.itmcod
    INNER JOIN sunshine.sw1 AS sw1 ON swt.itmcod = sw1.itmcod AND swt.whscod = sw1.whscod
    INNER JOIN sunshine.idl AS idl ON idl.idncod = swt.idncod

    WHERE
         (swt.whscod =  @whscod OR @whscod = 'ALL') 
         AND (LEFT(swt.loccod,@length) >= LEFT(@fromloccod,@length) OR @fromloccod = '') AND (LEFT(swt.loccod,@length) <=      @toloccod OR @toloccod = '')
         AND swt.loccod IS NOT NULL

    GROUP BY swt.whscod, swt.loccod, swt.itmcod, itm.itmnam + itm.aitmnm, swt.stcqty, swt.idncod, idl.credat, idl.chgdat

    Wednesday, June 9, 2010 12:16 PM
  • DECLARE @length int;
    
    IF len(@toloccod) = 0
     SET @length = LEN(@fromloccod) 
    ELSE 
     SET @length = LEN(@toloccod);
    
    SELECT
    swt.whscod AS 'Store',
    swt.loccod AS 'Location', 
    swt.itmcod AS 'Item', 
    itm.itmnam + itm.aitmnm AS 'Item Name', 
    sum(swt.stcqty) AS 'Quantity',
    MIN(idl.credat) AS [First date] 
    MAX(idl.credat) AS [Last date]
    
    
    FROM sunshine.swt AS swt
    INNER JOIN sunshine.itm AS itm ON swt.itmcod = itm.itmcod
    INNER JOIN sunshine.sw1 AS sw1 ON swt.itmcod = sw1.itmcod AND swt.whscod = sw1.whscod
    INNER JOIN sunshine.idl AS idl ON idl.idncod = swt.idncod
    
    WHERE 
       (swt.whscod = @whscod OR @whscod = 'ALL') 
       AND (LEFT(swt.loccod,@length) >= LEFT(@fromloccod,@length) OR @fromloccod = '') AND (LEFT(swt.loccod,@length) <=   @toloccod OR @toloccod = '')
       AND swt.loccod IS NOT NULL
    
    GROUP BY swt.whscod, swt.loccod, swt.itmcod, itm.itmnam, itm.aitmnm
    

    I excluded extra group by conditions (I only group by Store, Location, Item's info) and added sum of Quantity.

    Also, you may want to take a look at this blog

    Do you use Column=@Param OR @Param IS NULL in your WHERE clause? Don't, it doesn't perform

    once you get your select working.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by mp703 Friday, June 11, 2010 6:44 AM
    Wednesday, June 9, 2010 1:42 PM