very tricky sql problem - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

very tricky sql problem

Author

Topic

raydenl
Starting Member

16 Posts

Posted - 2005-07-19 : 21:58:14

SELECT NZDate - CAST(NZDate - '2005-01-01' AS int) % 7 AS WeekStarting, sum(score)/7
from myTable
where (NZDate between '2005-01-01' and '2005-01-21') and name IN ('John','Bob','Harry')
group by NZDate - CAST(NZDate - '2005-01-01' AS int) % 7
order by weekstarting

Hi, I have a query (above) that given any start date (2005-01-01) and end date (2005-01-21), returns a result set as such:

2005-01-01 56.4 (contains mean of all values between '2005-01-01' and '2005-01-07')
2005-01-08 64.8
2005-01-15 45.7

This works good, except now I have been asked to change this so it returns the mean for 79 years

i.e. in the example above also gets the values for the three 7 day periods from 1927 to 2005 and averages them

to return a result set that looks like this:

2005-01-01 45.8 (contains mean of all values between '****-01-01' and '****-01-07' for 1927 to 2005)
2005-01-08 56.4
2005-01-15 34.9

Please help me!
Thanks

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2005-07-19 : 22:20:42

A bit nasty, but here goes:

SELECT LEFT(CONVERT(varchar, NZDate - CAST(NZDate - '2005-01-01' AS int) % 7, 103), 5) AS WeekStarting, sum(score)/7
from myTable
where name IN ('John','Bob','Harry')
group by LEFT(CONVERT(varchar, NZDate - CAST(NZDate - '2005-01-01' AS int) % 7, 103), 5)
order by weekstarting

The CONVERT and LEFT will ditch the year from the date, so you can do what you need.

HTH,

Tim

raydenl
Starting Member

16 Posts

Posted - 2005-07-19 : 22:50:50

Ahh nope that wont return the result set I showed.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2005-07-19 : 23:19:53

It should do the grouping for you as requested; it's just a matter of formatting the output

raydenl
Starting Member

16 Posts

Posted - 2005-07-19 : 23:43:17

SELECT LEFT(CONVERT(varchar, NZDate - CAST(NZDate - '2005-01-01' AS int) % 7, 103), 5) AS WeekStarting, sum(score)/7
from myTable
where (NZDate between '2005-01-01' and '2005-01-21') and name IN ('John','Bob','Harry')
group by LEFT(CONVERT(varchar, NZDate - CAST(NZDate - '2005-01-01' AS int) % 7, 103), 5)
order by weekstarting

All it does is return my original result set but with the date in a different format with the year stripped of the date.

01/01 56.4 (contains mean of all values between '2005-01-01' and '2005-01-07')
08/01 64.8
15/01 45.7

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2005-07-20 : 01:34:11

Try this:

SELECT LEFT(CONVERT(varchar, DateAdd(d, 1-DatePart(dw, FirstPublished), FirstPublished), 103),5), sum(score)/7
FROM myTable
where name IN ('John','Bob','Harry')
group by LEFT(CONVERT(varchar, DateAdd(d, 1-DatePart(dw, FirstPublished), FirstPublished), 103),5)
order by weekstarting

The

DateAdd(d, 1-DatePart(dw, FirstPublished), FirstPublished)

bit simply returns the date of the preceeding Sunday. So you don't have to muck about with adding/deleting a set date.

HTH

Tim

raydenl
Starting Member

16 Posts

Posted - 2005-07-20 : 18:08:33

I'm not sure what that does, but it's not what I am after.

I will explain it in a different way.

This query gives me:

select NZDate - CAST(NZDate - '1927-01-01' AS int) % 7 WeekStarting, avg(score) Avg_Score
from myTable
where (NZDate between '1927-01-01' and '2005-01-21') and name IN ('John','Bob','Harry')
group by NZDate - CAST(NZDate - '1927-01-01' AS int) % 7
order by weekstarting

1927-01-01 56.78
1927-01-08 34.56
1927-01-15 56.23
1927-01-22 45.34
1927-01-29 23.23
1927-01-05 45.23
...
2005-01-15 56.23

What I want is this:

1927-01-01 56.78
1927-01-08 34.56
1927-01-15 56.23
1928-01-01 56.78 <-new group
1928-01-08 34.56
1928-01-15 56.23
1929-01-01 56.78 <-new group
1929-01-08 34.56
1929-01-15 56.23
...
2005-01-01 56.78 <-new group
2005-01-08 34.56
2005-01-15 56.23

It is then easy for me to strip of the year and group by the day and month.

The user will give a start and an end date, and in the example above it would have been 2005-01-01 to 2005-01-21 (will always be a multiple of 7 days), and it needs to go back 79 years from the given start year, in this example 1927.

I hope this helps more.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources