locked
sql RRS feed

  • Question

  • i would like to ask how can i select "id" from a sql script
    ,which return each custid with max(createdate) and createdate<='2014-7-14'

     

    createdate      id   custid
    2014-7-14 15:00   001   001
    2014-7-14 12:00   002   001
    2014-4-11 11:00   003   002
    2014-8-11 00:00   004   002

    result i want to return as below:

    id  custid
    001   001 (max(createdate) is 2014-7-14 15:00 and createdate<='2014-7-14')
    002   003 (max(createdate) is 2014-4-11 11:00 and and createdate<='2014-7-14')

    • Moved by Dan GuzmanMVP Tuesday, August 5, 2014 11:25 AM Move to appropriate forum (Transact-SQL)
    Tuesday, August 5, 2014 10:19 AM

Answers

  • If I got your question correctly, does below code fulfil your requirement?

    DECLARE @DataTable TABLE (createdate DATETIME, id VARCHAR(3), custid VARCHAR(3))

    INSERT INTO @DataTable (createdate,id, custid)
    VALUES
    ('2014-7-14 15:00', '001','001'),
    ('2014-7-14 12:00', '002','001'),
    ('2014-4-11 11:00', '003','002'),
    ('2014-8-11 00:00', '004','002')

    ;WITH cte_table AS (
    SELECT custid,MAX(createdate) AS createdate
    FROM @DataTable f
    WHERE CONVERT(DATE,createdate) <= '2014-07-14'
    GROUP BY custid
     )
     
     SELECT cte.custid AS id, t.id AS custid
     FROM @DataTable t
     INNER JOIN cte_table cte ON cte.custid = t.custid AND cte.createdate = t.createdate
     ORDER BY cte.custid
     

    • Proposed as answer by kumarvik Tuesday, August 5, 2014 6:58 PM
    • Marked as answer by Elvis Long Wednesday, August 13, 2014 3:12 AM
    Tuesday, August 5, 2014 6:38 PM

All replies

  • i would like to ask how can i select "id" from a sql script
    ,which return each custid with max(createdate) and createdate<='2014-7-14'

    Try the query below (assuming createdate is a date data type).  I'll move this thread to the T-SQL forum since this question is related to T-SQL.

    SELECT MAX(id) AS id, custid
    FROM @table1
    WHERE
    	createdate <= '2014-07-14'
    GROUP BY
    	custid;


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Tuesday, August 5, 2014 11:25 AM
  • Hi,

    Try this..

    WITH cte
    AS
    (
    SELECT id, custid

            , ROW_NUMBER() OVER (PARTITION BY custid ORDER BY createddate DESC) rnumber

    FROM table
    )

    SELECT * FROM cte WHERE rnumber = 1

    Regards,

    Brindha.

    Tuesday, August 5, 2014 11:35 AM
  • i would like to ask how can i select "id" from a sql script
    ,which return each custid with max(createdate) and createdate<='2014-7-14'

    Try the query below (assuming createdate is a date data type).  I'll move this thread to the T-SQL forum since this question is related to T-SQL.

    SELECT MAX(id) AS id, custid
    FROM @table1
    WHERE
    	createdate <= '2014-07-14'
    GROUP BY
    	custid;


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com


    i think you misunterstand the question. i want to retrieve max createdate's ID , NOT max(ID)
    Tuesday, August 5, 2014 4:51 PM
  • Your columns are flipped in your DDL.

    You will never get the result you're asking for as custID 4 has a larger createDate than custID 3.

    This might be close:

    DECLARE @forumTable TABLE (createDate DATETIME, ID VARCHAR(3), custID VARCHAR(3))
    INSERT INTO @forumTable (createDate, CustID, ID)
    VALUES
    ('2014-7-14 15:00', '001','001'),
    ('2014-7-14 12:00', '002','001'),
    ('2014-4-11 11:00', '003','002'),
    ('2014-8-11 00:00', '004','002')
    
    SELECT ID, custID
      FROM @forumTable f
     GROUP BY ID, custID
    HAVING MAX(createDate) >= '2014-07-14'


    Tuesday, August 5, 2014 5:01 PM
  • 2014-7-14 15:00   is not <= 2014-7-14. Because 2014-7-15 is equivalent to 2014-07-15 00:00:00

    This is what you are looking for? Your requirement description is vague. So the following is just a guess:

    DECLARE @forumTable TABLE (createDate DATETIME, ID VARCHAR(3), custID VARCHAR(3))
    INSERT INTO @forumTable (createDate, CustID, ID)
    VALUES
    ('2014-7-14 15:00', '001','001'),
    ('2014-7-14 12:00', '002','001'),
    ('2014-4-11 11:00', '003','002'),
    ('2014-8-11 00:00', '004','002')
    
    
    
    
    SELECT *
    FROM 
    (
    	SELECT ID, custID, createDate, ROW_NUMBER() OVER (PARTITION BY custID ORDER BY createDate DESC) AS RowNumber
    	FROM @forumTable f
    	WHERE createDate < '2014-07-14'
    ) T
    WHERE RowNumber = 1


    EntityLite: A Lightweight, Database First, Micro ORM

    Tuesday, August 5, 2014 6:20 PM
  • >> i think you misunterstand the question <<

    That is a sign that you should improve your question.

    Asking questions is a skill


    EntityLite: A Lightweight, Database First, Micro ORM


    Tuesday, August 5, 2014 6:23 PM
  • Hello!

    Try this:

    SELECT A.custid, A.createdate, B.id FROM
    	(SELECT custid, MAX(createdate) AS createdate FROM TABLE
    	GROUP BY custid
    	HAVING createdate <= '2014-7-14') A
    INNER JOIN
    	(SELECT id, custid, createdate FROM TABLE where createdate <= '2014-7-14') B
    ON
    	A.custid = B.custid
    	AND A.createdate = B.createdate





    • Edited by Rafael Juca Tuesday, August 5, 2014 6:29 PM
    Tuesday, August 5, 2014 6:28 PM
  • If I got your question correctly, does below code fulfil your requirement?

    DECLARE @DataTable TABLE (createdate DATETIME, id VARCHAR(3), custid VARCHAR(3))

    INSERT INTO @DataTable (createdate,id, custid)
    VALUES
    ('2014-7-14 15:00', '001','001'),
    ('2014-7-14 12:00', '002','001'),
    ('2014-4-11 11:00', '003','002'),
    ('2014-8-11 00:00', '004','002')

    ;WITH cte_table AS (
    SELECT custid,MAX(createdate) AS createdate
    FROM @DataTable f
    WHERE CONVERT(DATE,createdate) <= '2014-07-14'
    GROUP BY custid
     )
     
     SELECT cte.custid AS id, t.id AS custid
     FROM @DataTable t
     INNER JOIN cte_table cte ON cte.custid = t.custid AND cte.createdate = t.createdate
     ORDER BY cte.custid
     

    • Proposed as answer by kumarvik Tuesday, August 5, 2014 6:58 PM
    • Marked as answer by Elvis Long Wednesday, August 13, 2014 3:12 AM
    Tuesday, August 5, 2014 6:38 PM
  • kumarvik is the first one to see that your DateTime value vs Date only filter is the problem. Several others would have worked with this included in their proposed answer.
    Tuesday, August 5, 2014 8:20 PM