QQ: identify the first record by date

Question

0.00/5 (No votes)

See more:

Hi,

I want to be able to find the first and last record by date from a date range. I would like to do this in a View rather than a stored proc.

Any ideas?

Pseudo code:

SQL

CREATE VIEW VW_TEST
AS

with TESTDATA AS(
  select
      cast(getdate() as date) as date
  union all
      select dateadd(day, -1, datetime)
      from dates
      where datetime>(dateadd(day,-120,cast(cast(getdate() as date) as datetime)))
)
select 
   t0.date as firstInRange,
   t1.date as lastInRange
from TESTDATA t0 
left outer join TESTDATA t1 on t1.date = cast(getdate() as date) --today
 where t0.date = [first date]

GO

select firstInRange,lastInRange
from VW_TEST
where firstInRange > @RangeStart 
and   lastInRange < @RangeEnd

I'm not even sure this makes sense in my head. I'll write up an SP for it in the mean time, but it would be good to have a view as it would slot nicely into my current framework.

Let me know what you think

Thanks ^_^
Andy

To clarify, the data in the example is fake. The actual data is much more disordered. There may well be a record for every date but in may cases we may only have the last week, or the data may be patchy.

Here is the SP I wrote in it's place:

SQL

ALTER PROCEDURE [dbo].[SP_dashboard_list]
(
@startdate as date,
@enddate as date
)
AS

with records as (
     select r.* ,ROW_NUMBER() over (partition by r.id order by datetime) as row
       from VW_results r
      where r.datetime >= @startdate
        and r.datetime <= @enddate
   ),
first as(
   select * from records where row = 1
) ,
lastrecord as (
	select max(row) as row, id from records group by id
),
last as (
   select r.* from records r
	inner join lastrecord lr on r.id = lr.id and r.row = lr.row
)

SELECT
	k.id,
	k.page_id,
	m.title,
	k.phrase,

	f.avg_r as init,
	l.avg_r as curr
	
FROM  pages m
	  inner join dbo.ks k on m.page_id = k.page_id
	  left outer join first f on k.id = f.id
	  left outer join last l on k.id = l.id
where 
	k.removed_date is null

I hope that clears it up :S

Posted 27-Nov-15 3:46am

Andy Lanng

Updated 27-Nov-15 5:45am

v2

Add a Solution

Comments

Richard Deeming 27-Nov-15 10:36am

Are you trying to get the first and last record by date within the range, or the minimum and maximum dates within the range? Your question suggests the former, but your example suggests the latter.

Andy Lanng 27-Nov-15 11:46am

question updated ^_^

Maciej Los 27-Nov-15 11:32am

As Richard Deeming mentioned, your question is not clear at this moment, but my first thought is to identify min(first) and max(last) record in a range by using custom function:

SELECT dbo_getFirstDateInRange(range_start, range_stop) As First, dbo_getLastDateInRange(range_start, range_stop) As Last

Andy Lanng 27-Nov-15 11:46am

question updated ^_^

1 solution

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)

Richard Deeming · Accepted Answer · 2015-11-27T06:04:00

You can't have a parameterized view, but a table-valued function would work.

Try something like this:

SQL

CREATE FUNCTION dbo.TVF_dashboard_list
(
    @StartDate As date,
    @EndDate As date
)
Returns Table
As
Return
(
    WITH records As
    (
        SELECT
            ROW_NUMBER() OVER (PARTITION BY R.id ORDER BY R.[datetime]) As RowAsc,
            ROW_NUMBER() OVER (PARTITION BY R.id ORDER BY R.[datetime] DESC) As RowDesc,
            R.id,
            R.avg_r
        FROM
            dbo.VW_results As R
        WHERE
            R.[datetime] Between @StartDate And @EndDate
    )
    SELECT
        K.id,
        K.page_id,
        M.title,
        K.phrase,
        F.avg_r As init,
        L.avg_r As curr
    FROM
        dbo.pages As M
        INNER JOIN dbo.ks As K ON K.page_id = M.page_id
        LEFT JOIN records As F ON F.id = K.id And F.RowAsc = 1
        LEFT JOIN records As L ON L.id = K.id And L.RowDesc = 1
    WHERE
        K.removed_date Is Null
);

Using it is fairly simple:

SQL

SELECT
    id,
    page_id,
    title,
    phrase,
    init,
    curr
FROM
    dbo.TVF_dashboard_list(@StartDate, @EndDate)
;