i have 3 tables, each with a date(it has daily dates) column(column name is same in all tables)
Each table has columns say "value1","value2", "value3"
i want data from all these tables together.such that my first column will have data weeks and other 3 columns count1,count2,count3 will have average of next 4 weeks count..placed infront of week.
weeks count(value1) count(value2) count(value3 )
1/1/2005 101 88 221
1/8/2005 100 81 151
1/15/2005 87 96 301
Average calculations Here :
week 1 2 3 4
Count1: 101 = ( 99 + 105 + 110 + 87 )/4
100 = (105 + 110 + 87 + 98 )/4
Plz lemme know if u have any suggestions..
Sumit:
I put this together. It parameterized to allow for variation of (1) a "from date", (2) a "to date", and (3) the "beginning day of the week" [here I am assuming Sunday]. This routine uses a "small_iterator" table to flash through and summarize the records that occur during the date range. I am assuming that what you want are 28-day averages from the date named through the 28 days that follow. My "small_iterator" table consists of the integers 1-32768 and is intended as a utility table that we generally make avaible to all application databases. Our standards for this table stress the use of the NOLOCK optimizer hint for this table to avoid lock contention. This simple table is defined as:
create table dbo.SMALL_ITERATOR
( iter smallint not null
constraint PK_SMALL_ITERATOR primary key
)
I hope the following is of use; I am not sure of all the requirements you have:
-- -
-- First, create a fake table with some fake data
-- -
set nocount on
create table ##xample
( xDate datetime not null,
value1 integer not null,
value2 integer not null,
value3 integer not null,
constraint pk_##xample primary key (xDate)
)
declare @.rootDate datetime
set @.rootDate = '11/19/2005' -- selecting a non-distinct date
declare @.iter integer
set @.iter = 0
while @.iter <= 250
begin
insert into ##xample
select dateadd (day, @.iter, @.rootDate),
1 + 60 * rand (),
1 + 40 * rand () + 40 * rand(),
1 + 50 * rand() + 50 * rand() + 50 * rand ()
set @.iter = @.iter + 1
end
--select * from ##xample -- To show the fake data if you want to see it
-- -
-- Establish some parameters to this report summary
--
-- In this example, we are going to assume that a week begins on Sunday
--
-- We are going to run this report from 1/1/2006 to the present; note
-- that since this uses an iterator table that the start date is set
-- to 12/31/2005 because dates are derived by using the iterator to
-- increment through the dates and the lowest iteration value is 1.
--
-- The @.baseWeekDate var is used to store the date on which the first
-- full week of the year begins minus one week (because of iterator table)
--
-- I am not sure about how the ranges are to run so maybe this helps,
-- maybe it doesn't
--
-- Notice that the "4-week" average rapidly shrinks for the data
-- at the end of the table; this is because we are taking a "4 week"
-- average with less than 28 days of data; you might want this handled
-- differently
-- -
declare @.fromDate datetime
declare @.toDate datetime
declare @.firstWeekDay integer
declare @.baseWeekDate datetime
declare @.maxIterator integer
set @.firstWeekDay = 1 -- Assume that Sunday is the beginning of the week
set @.fromDate = '12/31/5' -- The beginning of the year minus 1 day
set @.toDate = ( select max (xDate) from ##xample ) -- The highest date in the table
set @.maxIterator = 1 + datediff (day, @.fromDate, @.toDate) / 7 -- upper bound for iterator
-- -
-- Stuff is beginning to get more tricky here. I am looking for the
-- first Sunday the occurs at or after the "from date"; however, because
-- I am going to be using an iterator to bang throug the data, I must
-- back the that first Sunday date by a week.
-- -
select @.baseWeekDate = dateadd (day, -7 ,dateadd (day, iter, @.fromDate))
from small_iterator (nolock)
where iter <= 7
and datepart (dw, dateadd (day, iter, @.fromDate)) = @.firstWeekDay
-- Just used when I was debugging
/*
select @.fromDate as [@.fromDate],
@.toDate as [@.toDate],
@.firstWeekDay as [@.firstWeekDay],
@.baseWeekDate as [@.firstWeekDate],
@.maxIterator as [@.maxIterator]
*/
-- -
-- Heavy into it here:
--
-- This routine uses an iterator table to flash through all of the
-- starting week dates that occur between the from date and the to date
--
-- Compute the 4-week average for the data that begins with the listed
-- date and runs for the next 28 days
-- -
select convert (varchar (12), weekDate, 101) as [Week Date],
avgVal_1 as [Avg Val 1],
avgVal_2 as [Avg Val 2],
avgVal_3 as [Avg Val 3]
from ( select dateadd (day, 7*iter, @.baseWeekDate) as weekDate,
sum (value1) / 4 as avgVal_1,
sum (value2) / 4 as avgVal_2,
sum (value3) / 4 as avgVal_3
from small_iterator (nolock) -- don't want contention on an iterator
inner join ##xample
on xDate >= dateadd (day, 7*iter, @.baseWeekDate) -- bangs through all the sundays
and xDate < dateadd (day, 7*iter + 28, @.baseWeekDate) -- sets up a 4-week interval
where iter <= @.maxIterator
group by dateadd (day, 7*iter, @.baseWeekDate) -- Group the data by the week
) xx
order by weekDate
-- -
-- All done; let's drop the table and go home
-- -
go
drop table ##xample
|||Could you please post a sample schema, data and expected results?|||I am so so thankful of u. i really wanted somthing of this type.
Now only problem is tat if the End Ref Date doesnt fall in the 4th week then the query will still give the average of 4 weeks, which is actually wrong.
i guess it should be like this
Last week --> no average
1 week b4 last week-->average of 2
2 weeks b4 last week --> avg of last 3 weeks
for other its as usual.
if u could reply me .it ll be really gr8..
Thanks & regards
Sumit
|||Sumit:
In the comments I had:
--
-- Notice that the "4-week" average rapidly shrinks for the data
-- at the end of the table; this is because we are taking a "4 week"
-- average with less than 28 days of data; you might want this handled
-- differently
Is what you are seeking a solution to this problem that occurs over the last 28 days?
|||Dave
Sorry for late reply..din see the Alert.
Actually ya u r rite..i was looking for average for last 28 days.
I had to make some reports on SQL Server2K Reporting Services.
The code which you sent, which included DDL n DML statements worked fine individually in Business Intelligence Studio but the dataset couldnt generate any particular fields. So i had to remove lot of things from the query, once i understood the login.it finally worked. Chart is coming fine.Thank u.
i have another question:
I have 3 fields say :
JOb Inactive Returned
ID1 2 3
ID2 5 1
ID3 2 6
ID4 1 5
ID5 5 4
ID6 2 6
ID7 1 5
i want data in such a way tat
Days_Count jobs_inactive Jobs_Returned
1 2 1
2 3 0
3 0 1
4 0 1
5 2 8 ( for 5 and Above days)
sql