Click here to Skip to main content
15,077,494 members
Please Sign up or sign in to vote.
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
Updated 27-Nov-15 5:45am
v2
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

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)
;
   
v2
Comments
Andy Lanng 27-Nov-15 12:14pm
   
I'm not sure what I was hoping for. It's late Friday. That's my excuse and I'm sticking to it :P
Thanks for helping me clean up the query ^_^
Maciej Los 27-Nov-15 13:30pm
   
Great! This was my first thought.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900