Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Difference between first and last record

Status
Not open for further replies.

arodri

Technical User
Jul 9, 2010
121
US
Hello,

I posted this same post on the crystal reports forums but in the end I had too many records for it to work in Crystal. So I think what I need to do needs to be done first in SQL server.

I get milage records (listed below) from a View I created in SQL.
They are ordered by Code and Date. I need to get the difference between the last and first milage record for each Code (not min and max, but first and last according to the date).
For example, for code 084C it would be (26,832-3,594)= 23,238.
I also need to add the condition that if there's only one record for a code (i.e. code: 01BZ), then the difference is just 0.

I will then need to sum all of the 'difference' values, but I should be able to do this in crystal. Below is a smple of my data:

Code Date Item Milage
01BZ 1/6/2011 FS Lube 63,251

01KW 4/26/2010 FS Lube 72,883
01KW 10/19/2010 FS Lube 82,226

084C 2/13/2010 Synthetic Lube 3,594
084C 6/7/2010 Synthetic Lube 11,014
084C 9/24/2010 Synthetic Lube 20,669
084C 12/21/2010 Synthetic Lube 26,832

15JY 2/17/2010 FS Lube 209,198
15JY 4/9/2010 FS Lube 212,578
15JY 6/2/2010 FS Lube 216,148
15JY 8/12/2010 FS Lube 22,688
15JY 9/25/2010 FS Lube 224,229
15JY 10/27/2010 FS Lube 228,162

Thanks again for any suggestions!
 
Try this out... I did use Min and max date but I think that still gives you what you want. Unless they would be out of order.

Simi




Create table #milage (
Code char(4),
Date datetime,
Item varchar(40),
Milage int)


insert into #milage (Code, Date, Item, Milage)
values ('01BZ', '1/6/2011', 'FS Lube', 63251)
insert into #milage (Code, Date, Item, Milage)
values ('01KW', '4/26/2010', 'FS Lube', 72883)
insert into #milage (Code, Date, Item, Milage)
values ('01KW', '10/19/2010', 'FS Lube', 82226)
insert into #milage (Code, Date, Item, Milage)
values ('084C', '2/13/2010', 'Synthetic Lube', 3594)
insert into #milage (Code, Date, Item, Milage)
values ('084C', '6/7/2010', 'Synthetic Lube', 11014)
insert into #milage (Code, Date, Item, Milage)
values ('084C', '9/24/2010', 'Synthetic Lube', 20669)
insert into #milage (Code, Date, Item, Milage)
values ('084C', '12/21/2010', 'Synthetic Lube', 26832)
insert into #milage (Code, Date, Item, Milage)
values ('15JY', '2/17/2010', 'FS Lube', 209198)
insert into #milage (Code, Date, Item, Milage)
values ('15JY', '4/9/2010', 'FS Lube', 212578)
insert into #milage (Code, Date, Item, Milage)
values ('15JY', '6/2/2010', 'FS Lube', 216148)
insert into #milage (Code, Date, Item, Milage)
values ('15JY', '8/12/2010', 'FS Lube', 22688)
insert into #milage (Code, Date, Item, Milage)
values ('15JY', '9/25/2010', 'FS Lube', 224229)
insert into #milage (Code, Date, Item, Milage)
values ('15JY', '10/27/2010', 'FS Lube', 228162)


with datelist as (
select code, max(date) as maxd, min(date) as mind
from #milage
group by code
)
select d.code, d.maxd, d.mind, m1.milage, m2.milage, m1.milage-m2.milage as Total
from datelist d
join #milage m1
on m1.code=d.code and m1.date=d.maxd
join #milage m2
on m2.code=d.code and m2.date=d.mind
 
Using that code I have a slightly different query

Code:
;with List as (select *, ROW_NUMBER() over (PARTITION by Code order by Date) as RowMin,
ROW_NUMBER() over (PARTITION by Code order by Date DESC) as RowMax from #milage)

select c1.Code, c1.Date as StartDate, c2.Date as EndDate, c2.Milage - c1.Milage as [Difference]
from List c1 inner join List c2 on c1.Code = c2.Code and c1.RowMin = 1 and c2.RowMax = 1

It will be nice if you can run both of them and let us know which performs better.

PluralSight Learning Library
 
Markros code like mine relies on the date for max and Min values... But if you order his code by Milage you would get the same results regardless of date. Just an option... :)

Simi


;with List as
(
select *,
ROW_NUMBER() over (PARTITION by Code order by milage) as RowMin,
ROW_NUMBER() over (PARTITION by Code order by milage DESC) as RowMax
from #milage
)
select c1.Code, c1.Date as StartDate, c2.Date as EndDate,
c2.Milage - c1.Milage as [Difference]
from List c1
inner join List c2
on c1.Code = c2.Code
and c1.RowMin = 1
and c2.RowMax = 1
 
Hello,

Thanks so much for your responses! I have to admit that I'm not used to doing very complex queries in SQL so I have never used "with" or "Partition by." I'm not even sure I completley understand what your codes are doing but I have a rough idea.

I tried Simian336's code but it kept giving me syntax errors that I wasn't sure how to fix. I played around with it a little bit but no luck. Markros's code is what ended up working for me - I need to order by Date or I'll get the wrong values. If I order by Milage it would be the same as using min/max for milage which I can't do. Here is the code I used (instead of #milage I just used the view I had created before "LubeMilage":

;with List as (select *, ROW_NUMBER() over (PARTITION by code order by Date) as RowMin,
ROW_NUMBER() over (PARTITION by Code order by date DESC) as RowMax from LubeMilage)

Code:
select c1.Code, c1.Date as StartDate, c2.Date as EndDate, 
c2.milage - c1.milage as [Difference]
from List c1 inner join List c2 on c1.Code = c2.Code and c1.RowMin = 1 and c2.RowMax = 1

I have another issue that I'm not sure how to solve. Now that I have this query working, I need to do a calculation (I can do the calculation part in Crystal reports) but what I need from this query is the COUNT of Code's. For example, right now my results look like this:

Code StartDate EndDate Difference
01BZ 2011-01-06 2011-01-06 0
01KW 2010-04-26 2010-10-19 9343
084C 2010-02-13 2010-12-21 23238
15JY 2010-02-17 2010-10-27 18964

What I need is a column that tells me how many records there are for each Code, so my results would look like this:

Code StartDate EndDate Difference CountCode
01BZ 2011-01-06 2011-01-06 0 1
01KW 2010-04-26 2010-10-19 9343 2
084C 2010-02-13 2010-12-21 23238 4
15JY 2010-02-17 2010-10-27 18964 6

I don't know if this would help at all but I don't really need the start or end date for anything, so that can go if it needs to.

thanks!
 
Simply add COUNT(Code) OVER (partition by Code) as [CountCode]
to the List cte, e.g.
Code:
;with List as (select *, ROW_NUMBER() over (PARTITION by code order by Date) as RowMin, 
ROW_NUMBER() over (PARTITION by Code order by date DESC) as RowMax,
COUNT(*) over (partition by Code) as [CountCode]

 from LubeMilage)

...

PluralSight Learning Library
 
That's exactly what I needed. Thank you guys so much! It was a HUGE help.
I can finally build the report that I needed..thanks again.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top