none
SQL Grouping Datediff query

    问题

  • Hi,

    I have a table with the following columns:

    ID, ProjectName, Created, StatusReportingPeriod

    This table contains multiple reports for each reporting period (generally weekly). Typically we have hundreds of reports for each project.

    How would I write the SQL to return the last three most recent reports (based on created date) for each project?

    Thanks!

    2012年7月5日 18:14

答案

全部回复

  • Try the below one:

    ;WITH CTE AS
    (
    	SELECT	*
    	,	ROW_NUMBER() OVER(PARTITION BY ProjectName ORDER BY Created DESC) AS Ranking
    	FROM	YourTable	-- replace with your table name
    )
    SELECT	*
    FROM	CTE
    WHERE	Ranking	<= 3

    Thanks!
    2012年7月5日 18:18
  • >> I have a table with the following columns: <<

    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. 

    Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. Here is my guess, without a magical universal vague “id” and some other basic clean ups. Why did you keep the project creation date? Why did you have a something_status reporting period, but not the status? This is like having baseball scores without team names. 

    CREATE TABLE Projects
    (project_name CHAR(15) NOT NULL,
     report_period_name CHAR(10) NOT NULL 
      REFERENCES Report_Periods (report_period_name)
       CHECK(report_period_name LIKE '[12][0-9][0-9][0-9]W[0-5][0-9]'),
      PRIMARY KEY(project_name, report_period_name),
     project_status ???  NOT NULL) ;

    >> How would I write the SQL to return the last three most recent reports (based on created date) for each project? <<

    The dates are implied by the reporting periods. A useful idiom is a report period calendar. It gives a name to a range of dates. 

    CREATE TABLE Report_Periods
    (report_period_name CHAR(10) NOT NULL PRIMARY KEY
       CHECK(report_period_name LIKE '[12][0-9][0-9][0-9]w[0-5][0-9]'),
     report_start_date DATE NOT NULL,
     report_end_date DATE NOT NULL,
     CONSTRAINT date_ordering
     CHECK (report_start_date <= report_end_date),
     etc);

    These report periods can overlap; a fiscal quarter will be contained in the range of its fiscal year. There can be gaps between them; we have to wait a year between each “Annual Going out Of Business Sale!” and there might be long stretches of time without any special sales. But we want to know their ranges so that the table is fairly constant once it is created. 

    Look up the ISO-8601 Standard for week-within-year and use it as the report period name. I put that in the DDL I had to do for you. 

    WITH Date_Seq_Reports
    AS
    (SELECT , ROW_NUMBER() 
              OVER(PARTITION BY project_name 
              ORDER BY report_period_name DESC) AS report_seq
            FROM Projects )
    SELECT project_name, report_period_name, project_status 
      FROM Date_Seq_Reports 
     WHERE report_seq <= 3;

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    2012年7月5日 19:48
  • Thanks both - understood. I'll try both approaches and feed back my results.
    2012年7月5日 22:21
  • Thanks Deepak.
    2012年7月6日 11:12
  • Although one more question - I am going to need to use the CTE Select statement as part of an INSERT INTO... is this possible, I imagine there would be syntax issues with having the semi-colon right after the insert into?

    Thanks

    2012年7月6日 11:15
  • Although one more question - I am going to need to use the CTE Select statement as part of an INSERT INTO... is this possible, I imagine there would be syntax issues with having the semi-colon right after the insert into?

    Thanks

    You may try as below:

    ;WITH CTE AS
    (
    	SELECT	*
    	,	ROW_NUMBER() OVER(PARTITION BY ProjectName ORDER BY Created DESC) AS Ranking
    	FROM	YourTable	-- replace with your table name
    )
    INSERT	INTO TableName	(
    col1, col2 --, colN
    )			-- replace with list of required columns
    SELECT	*	-- replace with list of required columns and order should match with column list of INSERT INTO clause
    FROM	CTE
    WHERE	Ranking	<= 3
    Thanks!
    2012年7月10日 5:25