calculating period of time

Question

calculating period of time

Viewing 12 posts - 16 through 26 (of 26 total)

You must be logged in to reply to this topic. Login to reply

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 1

astrid 69000 (3/26/2013)
but if i create the date table dont i have to add the dates manually?
to calculate the first period i go
select sum(Count) from #table where recordday >= '2008-01-01' and recordday < '2008-01-06'
what i dont want is to write rows from 2008 till today every 5 days.
that is basically my problem

You need not write the dates manually

Check the query I have provided in the previous post

I did a few changes to Chris's query to suit your requirements

I used DENSE_RANK() instead of ROW_NUMBER() and ORDER BY should be based on only the DATE part of the Day column.

You can group based on the column "rn" in the CTE which will be same for 5 consecutive days

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

astrid 69000 (3/26/2013)
but if i create the date table dont i have to add the dates manually?
to calculate the first period i go
select sum(Count) from #table where recordday >= '2008-01-01' and recordday < '2008-01-06'
what i dont want is to write rows from 2008 till today every 5 days.
that is basically my problem

You won't have to write out the days. Please provide a sample of the data that you have, so we can choose for you the best way of approaching this. At the moment we're all guessing and missing, and it's frustrating for everybody.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

astrid 69000 Hall of Fame Points: 3208 More actions · Answer 3

I am sorry I didn’t plan to frustrate anyone, specially not people from the forum who are always willing to help, specially to a newbie in sql like me. I was just trying to do it on my own with some guidance.

So here it is all.

This is the table I have.

declare @Date datetime;

set @date = 'jan 01 2008'

while @date < 'jan 01 2009'

begin

insert into #totals(recordday, Tcount)

select @date, abs(checksum(cast(newid() as varchar(36))))%10

set @date = dateadd(hour, 11, @date)

end

go

and this is the result I need

DateFrom DateTo Tcount

2008-01-01 00:00:00.000 2008-01-08 00:00:00.000 79

2008-01-09 00:00:00.000 2008-01-16 00:00:00.000 78

2008-01-17 00:00:00.000 2008-01-24 00:00:00.000 51

2008-01-25 00:00:00.000 2008-02-01 00:00:00.000 76

2008-02-02 00:00:00.000 2008-02-09 00:00:00.000 79

........

2008-12-18 00:00:00.000 2008-12-25 00:00:00.000 88

2008-12-26 00:00:00.000 2009-01-02 00:00:00.000 72

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 4

Did you check the query I had posted 2 posts back?

http://www.sqlservercentral.com/Forums/FindPost1435316.aspx

This almost has what you require

I had prepared that query based on the assumption of 5 days grouping which you can change to suit your current 8 days grouping

Edit: Added link to the post

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

astrid 69000 Hall of Fame Points: 3208 More actions · Answer 5

yes i did, but i get confused, the "SELECT '2008-01-01 00:00:00.000', 3 UNION ALL" is not the one i need to add one by one manually?

the query returns around 800 rows i think.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

astrid 69000 (3/26/2013)
yes i did, but i get confused, the "SELECT '2008-01-01 00:00:00.000', 3 UNION ALL" is not the one i need to add one by one manually?
the query returns around 800 rows i think.

"SELECT '2008-01-01 00:00:00.000', 3 UNION ALL..." sets up a mimic of your tables, that's all. Any solution requires data to run/test against. Apologies for any confusion.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 7

astrid 69000 (3/26/2013)
yes i did, but i get confused, the "SELECT '2008-01-01 00:00:00.000', 3 UNION ALL" is not the one i need to add one by one manually?
the query returns around 800 rows i think.

I don't get what you trying to add manually.

The SELECT part that you are mentioning is just to create a CTE to temporarily work with.

If you have a table you can replace the CTE with you actual table

Can you give us the structure of the table you are working with?

We will change the query according to your table name.

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

-- Create a sample data set to work with

-- A #temporary table called #AstridsSampleData

IF object_id('TempDB..#AstridsSampleData') IS NOT NULL

DROP TABLE #AstridsSampleData

;WITH CTE_SomeStuffIMadeUp ([day], [Count]) AS (

SELECT '2008-01-01 00:00:00.000', 3 UNION ALL

SELECT '2008-01-01 11:00:00.000', 9 UNION ALL

SELECT '2008-01-01 22:00:00.000', 3 UNION ALL

SELECT '2008-01-02 09:00:00.000', 7 UNION ALL

SELECT '2008-01-02 20:00:00.000', 7 UNION ALL

SELECT '2008-01-03 07:00:00.000', 4 UNION ALL

SELECT '2008-01-03 18:00:00.000', 0 UNION ALL

SELECT '2008-01-04 05:00:00.000', 3 UNION ALL

SELECT '2008-01-04 16:00:00.000', 6 UNION ALL

SELECT '2008-01-05 03:00:00.000', 2 UNION ALL

SELECT '2008-01-05 14:00:00.000', 0 UNION ALL

SELECT '2008-01-06 01:00:00.000', 1 UNION ALL

SELECT '2008-01-06 12:00:00.000', 8 UNION ALL

SELECT '2008-01-06 23:00:00.000', 9 UNION ALL

SELECT '2008-01-07 10:00:00.000', 0 UNION ALL

SELECT '2008-01-07 21:00:00.000', 4 UNION ALL

SELECT '2008-01-08 08:00:00.000', 1 UNION ALL

SELECT '2008-01-08 19:00:00.000', 8 UNION ALL

SELECT '2008-01-09 06:00:00.000', 3 UNION ALL

SELECT '2008-01-09 17:00:00.000', 2 UNION ALL

SELECT '2008-01-10 04:00:00.000', 3 UNION ALL

SELECT '2008-01-10 15:00:00.000', 8 UNION ALL

SELECT '2008-01-11 02:00:00.000', 3 UNION ALL

SELECT '2008-01-11 13:00:00.000', 4 UNION ALL

SELECT '2008-01-12 00:00:00.000', 9 UNION ALL

SELECT '2008-01-12 11:00:00.000', 9 UNION ALL

SELECT '2008-01-12 22:00:00.000', 2 UNION ALL

SELECT '2008-01-13 09:00:00.000', 7 UNION ALL

SELECT '2008-01-13 20:00:00.000', 4 UNION ALL

SELECT '2008-01-14 07:00:00.000', 1 UNION ALL

SELECT '2008-01-14 18:00:00.000', 7 UNION ALL

SELECT '2008-01-15 05:00:00.000', 5 UNION ALL

SELECT '2008-01-15 16:00:00.000', 5 UNION ALL

SELECT '2008-01-16 03:00:00.000', 4 UNION ALL

SELECT '2008-01-16 14:00:00.000', 6 UNION ALL

SELECT '2008-01-17 01:00:00.000', 9 UNION ALL

SELECT '2008-01-17 12:00:00.000', 8 UNION ALL

SELECT '2008-01-17 23:00:00.000', 6 UNION ALL

SELECT '2008-01-18 10:00:00.000', 7 UNION ALL

SELECT '2008-01-18 21:00:00.000', 5 UNION ALL

SELECT '2008-01-19 08:00:00.000', 9 UNION ALL

SELECT '2008-01-19 19:00:00.000', 7 UNION ALL

SELECT '2008-01-20 06:00:00.000', 0 UNION ALL

SELECT '2008-01-20 17:00:00.000', 7 UNION ALL

SELECT '2008-01-21 04:00:00.000', 9 UNION ALL

SELECT '2008-01-21 15:00:00.000', 2 UNION ALL

SELECT '2008-01-22 02:00:00.000', 4 UNION ALL

SELECT '2008-01-22 13:00:00.000', 7 UNION ALL

SELECT '2008-01-23 00:00:00.000', 0 UNION ALL

SELECT '2008-01-23 11:00:00.000', 2 UNION ALL

SELECT '2008-01-23 22:00:00.000', 9 UNION ALL

SELECT '2008-01-24 09:00:00.000', 8 UNION ALL

SELECT '2008-01-24 20:00:00.000', 8 UNION ALL

SELECT '2008-01-25 07:00:00.000', 7 UNION ALL

SELECT '2008-01-25 18:00:00.000', 6 UNION ALL

SELECT '2008-01-26 05:00:00.000', 8

)

SELECT *

INTO #AstridsSampleData

FROM CTE_SomeStuffIMadeUp

-- Use the sample data set to test a potential solution.

-- This solution omits the aggregate part

-- so you can see which rows would get grouped up

SELECT

a.[day],

a.[Count],

gp = DATEDIFF(day, x.BaseDate, a.[day])/5

FROM #AstridsSampleData a

CROSS APPLY (

SELECT BaseDate = MIN([day])

FROM #AstridsSampleData

) x

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

astrid 69000 Hall of Fame Points: 3208 More actions · Answer 9

ohhhhhhhhhhhhhhhhhhhh

now i understand... i am slow but i will get there lol.

so basically what i needed was the last two selects.

on my defense i am a sql on the making.... but on my first steps. 😀

thanks so much to all of you :-):-):-)

and it worked 🙂

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

Astrid, please look very carefully at the results of Kingston's solution and mine. They look the same but they are not. One of them will be correct (probably!), the other will not. Please let us know which solution is correct, and why.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

astrid 69000 Hall of Fame Points: 3208 More actions · Answer 11

Chris, i did what i asked. your query added groups to the dates. from 1 to 8 group 1, from 9 to 16 group 2 and so on and so on.

which is actually very good and is something else i learned today 🙂

if i group the groups by number and do a start date and an end date (that will probably take me two hours to write :w00t:) i will get the same results (i did it on excel :-D)

thanks!!!!!!:-):-):-):-)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

astrid 69000 (3/26/2013)
Chris, i did what i asked. your query added groups to the dates. from 1 to 8 group 1, from 9 to 16 group 2 and so on and so on.
which is actually very good and is something else i learned today 🙂
if i group the groups by number and do a start date and an end date (that will probably take me two hours to write :w00t:) i will get the same results (i did it on excel :-D)
thanks!!!!!!:-):-):-):-)

Kingston's solution works in a very similar way to obtain the groups, however the groups differ. My query increments the group id every 5 calendar days, Kingston's increments every 5 days encountered in the data - missing calendar dates are not taken into consideration.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden