Running total of days per Id and per day.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Running total of days per Id and per day.

Author

Topic

crackerbox
Starting Member

21 Posts

Posted - 2007-01-22 : 13:47:24

I'm trying to run a query to return a running count per ID / per day. I've been successful to get a count per ID / per day but not the running count: For example:The report will reset on each day per ID.

declare @t1 table (aircraft int, dt datetime)

insert @t1
select 38, '4/19/06' union all
select 38, '4/20/06' union all
select 38, '4/20/06' union all
select 38, '6/19/06'union all
select 40, '1/20/06' union all
select 40, '8/1/06'union all
select 40, '8/1/06' union all

The report should look like this

Aircraft datetime Daycount
38 4/19/06 1
38 4/20/06 1
38 4/20/06 2
38 6/19/06 1
40 1/20/06 1
40 8/1/06 1
40 8/1/06 2

Any ideas?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-22 : 14:04:28

[code]-- prepare sample data
declare @t1 table (aircraft int, dt datetime)

insert @t1
select 38, '4/19/06' union all
select 38, '4/20/06' union all
select 38, '4/20/06' union all
select 38, '6/19/06'union all
select 40, '1/20/06' union all
select 40, '8/1/06'union all
select 40, '8/1/06'

-- show the result
select x.aircraft,
x.dt,
m.number as daycount
from (
select t.aircraft,
t.dt,
count(*) as daycount
from @t1 as t
group by t.aircraft,
t.dt
) as x
cross join master..spt_values as m
where m.name is null
and m.number > 0
and x.daycount >= m.number
order by x.aircraft,
x.dt,
m.number[/code]

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-22 : 14:05:14

Your sample data has no primary key defined, so there's not much in the way of solid SQL we can write for you to help you out. You need to clearly define your data before we can help you out.

Also, please note that this is very,very easy to do at your client/presentation layer (web page, report, etc) and much more efficient. I strongly recommend calculating this value there, if you don't need to reference it further in your T-SQL code.

- Jeff

crackerbox
Starting Member

21 Posts

Posted - 2007-01-22 : 15:00:31

This worked fine in your sample but when I tried to insert my table the results are incorrect. Most days are returning a 1 even if there were 2 on the same day and it throws in a 2 every now and then even if there was only 1 flight. Here's what I inserted: Did I do something incorrectly?

Select * From v_flight_daily_Count

-- prepare sample data
declare @t1 table (aircraft_doc_no int, Actual_departing_date datetime)

insert into @t1
select aircraft_doc_no, actual_departing_date
from v_flight_daily_count X
--select 38, '4/19/06' union all
--select 38, '4/20/06' union all
--select 38, '4/20/06' union all
--select 38, '6/19/06'union all
--select 40, '1/20/06' union all
--select 40, '8/1/06'union all
--select 40, '8/1/06'

-- show the result
select x.aircraft_doc_no,x.actual_departing_date,
m.number as daycount
from (
select t.aircraft_doc_no,
t.actual_departing_date,
count(*) as daycount
from @t1 as t
group by t.aircraft_doc_no,
t.actual_departing_date
) as x
cross join master..spt_values as m
where m.name is null
and m.number > 0
and x.daycount >= m.number
order by x.aircraft_doc_no,
x.actual_departing_date--,
--m.number

crackerbox
Starting Member

21 Posts

Posted - 2007-01-22 : 15:21:24

I figured out why the 2 was showing up. It had nothing to do with the script. There was an error in the tables. The script is not incrementing when there are two records for the same aircraft on the same day. It's returning a 1. My script is as it was in the previous post.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-23 : 01:11:41

You made me more confused with the post above.
Is it working or not?

Peter Larsson
Helsingborg, Sweden

crackerbox
Starting Member

21 Posts

Posted - 2007-01-26 : 10:30:46

The script worked. I had to change the date format in my original fields to get rid of the time and then everything worked just like your example. It's exactly what I wanted. Thanks so much.

crackerbox
Starting Member

21 Posts

Posted - 2007-01-26 : 11:15:59

I have found a need to add another field that has the date and the time but when I add the additional field, the running count no longer works. It's giving me a one because adding the extra field upset the grouping. The previous script worked before adding the extra field. Any suggestions?

I now need the report to look like this.

Aircraft datetime Daycount
38 4/19/06 2006-04-06 22:07:00.000 1
38 4/20/06 2006-04-20 10:07:00.000 1
38 4/20/06 2006-04-20 11:08:00.000 2
38 6/19/06 2006-06-19 13:07:00.000 1
40 1/20/06 2006-01-20 06:07:00.000 1
40 8/1/06 2006-08-01 22:01:00.000 1
40 8/1/06 2006-08-01 23:07:00.000 2

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-26 : 13:38:26

No.
We have no idea where the datetime information come from nor the relation to the existing data.

Peter Larsson
Helsingborg, Sweden

crackerbox
Starting Member

21 Posts

Posted - 2007-01-26 : 14:05:51

The sample I previously sent was based only on the day. In reality, the dates are entered with a time. I'm trying to get the running total on the day but I also need to see the time. I have another table that will relate to this query where I want to pick up the first record of each day and the running total was a way for me to select just number 1.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-26 : 16:41:51

quote:
Originally posted by crackerbox

The sample I previously sent was based only on the day. In reality, the dates are entered with a time. I'm trying to get the running total on the day but I also need to see the time. I have another table that will relate to this query where I want to pick up the first record of each day and the running total was a way for me to select just number 1.

You are way overcomplicating things, to get the first row for each day, you just use min:

select aircraft, dateadd(dd,0, datediff(dd,0, dt)) as Day, Min(dt) as FirstDateTime
from @t1
group by aircraft, dateadd(dd,0, datediff(dd,0, dt))

That returns the first time per day per aircraft. Sometimes it helps to step back and specify what your entire goal is, instead of focusing on how you've been trying to get it done and how to get that particular technique to work. It is very inefficient and a bit tricky in SQL to calculate running totals or row numbers (at least before 2005) per group, but very easy and quick to get the Min() value per group, which is all you need ... unless, of course, you change the specs on us once again ....

- Jeff

crackerbox
Starting Member

21 Posts

Posted - 2007-01-29 : 11:51:04

That worked! You're wonderful. Thank you so much.

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2007-01-29 : 14:10:01

It sounds to me like you just want to have a WHERE clause that says "where [count] = 1" (or whatever your query is called). After all, your [count] column is effectively a row_number field, right?

Rob Farley
President - Adelaide SQL Server User Group
http://msmvps.com/blogs/robfarley

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2007-01-29 : 14:12:50

And in your original post, you could've just done:

select *, row_number() over (partition by aircraft order by aircraft, dt) as rn
from @t1

And then you can wrap it up into a table expression to use your new rn field.

select * from (
select *, row_number() over (partition by aircraft order by aircraft, dt) as rn
from @t1
) a
where rn = 1

Rob Farley
President - Adelaide SQL Server User Group
http://msmvps.com/blogs/robfarley

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-29 : 21:56:17

Rob -- He only wants the min() time per day.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources