DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: find minimum visitime if more purchased date

  1. #1
    Join Date
    Feb 2009
    Posts
    7

    Question find minimum visitime if more purchased date

    Hi guys,

    After run the following query i got the output as,
    select st.visittime,op.datepurchased from alamyStatistics..st_emailTracking st
    join alamyusers..up_visitor up on st.userid=up.userid
    join alamyorders..op_order op on op.userid=st.userid
    where st.userid='F5C2B992-E755-4B2C-AC80-003C0B825CDA' and op.datepurchased between st.visittime and dateadd(dd,30,st.visittime) and st.emailcode like '_%NB'
    and up.isdistrib=0 and op.orderstatusid = 100 and op.order_total!=0 and op.orderstatusid = 100 and op.datepurchased between '09-22-08' and '02-18-09'

    Visittime Datepurchased
    2009-01-15 14:37:24.000 2009-01-15 15:30:44.163
    2009-01-15 14:37:25.000 2009-01-15 15:30:44.163
    2009-01-15 14:30:31.000 2009-01-15 15:30:44.163

    is only for a particular user. But what I need is , u can see this particular user has different Visittime , but same purchased date. So i wish my output should be like
    Visittime Datepurchased
    2009-01-15 14:37:24.000 2009-01-15 15:30:44.163
    That means if a user has different visittimes with same purchased date, take the minimum visittime along with purchased date.

    Can anyone help?
    Are u clear my qn, otherwise just ask me i am happy to discuss more.

    Thanks in advance for any help

  2. #2
    Join Date
    Feb 2009
    Posts
    7
    nobody there, i am waiting for a solution , pls......help me

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    min() function would do the trick, have you tried it?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width