get median max and group by weekly

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)

get median max and group by weekly

Author

Topic

soorma
Yak Posting Veteran

52 Posts

Posted - 2009-10-21 : 14:12:51

I am trying to get the report like this

WeekStartDate average min max median Totalrecords Attmrec
1/1/2009 3 5 5 4 200 300
1/7/2009 5 8 9 1 300 500

This is how the data looks like in the temp table
totalattempts leadid
50 5000
12 45
15 40007

This is what i have done until now

ALTER PROCEDURE [dbo].[test]
-- Add the parameters for the stored procedure here
@StartDate varchar(12),
@EndDate varchar(12)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
IF object_id('tempdb..#TmpData') IS NOT NULL
Begin
Drop Table #TmpData
End

CREATE TABLE #TmpData (
TotalAttempts int NULL,
[LeadID] VARCHAR(100) NULL

)

INSERT INTO #TmpData (TotalAttempts, leadid)

select count(I3_RowID) as total,I3_RowID
From test where
CallDate between @StartDate and @EndDate Group by i3_rowid
order by total

SELECT [avg] = Avg(TotalAttempts),
[min] = Min(TotalAttempts),
[max] = Max(TotalAttempts),
TotalRecords=count(leadid),
AttemptedTotalRecords=sum(Totalattempts),
startdate=@StartDate
FROM #TmpData

when i run the above SP
it looks like
WeekStartDate average min max median Totalrecords Attmrec
1/1/2009 3 5 5 4 200 300

I how can i add the median and group by weeks in the above query

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-21 : 15:32:36

Here's one way. You will need to add a column to #tmpData: [RowID] int identity(1,1)
And be sure to INSERT to #tmpData ORDER BY [callDate]


select [avg]
       ,[min]
       ,[max]
       ,avg(t.TotalAttempts) as median
       ,totalRecords
       ,AttemptedTotalRecords
       ,startDate
       ,datename(weekday, startDate)
from   (
       SELECT [avg] = Avg(TotalAttempts)
              ,[min] = Min(TotalAttempts)
              ,[max] = Max(TotalAttempts)
              ,TotalRecords = count(leadid)
              ,AttemptedTotalRecords = sum(Totalattempts)
              ,startDate = dateadd(week, datediff(week, 0, callDate), 0)

              --helpers for Median
              ,min(rowid) + convert(int, ceiling(count(*) / 2.0)) - 1           medID
              ,1 - (count(*)%2)                                              evenOdd

       FROM #TmpData
       group by dateadd(week, datediff(week, 0, callDate), 0)
       ) d
inner join #tmpData t
       on t.rowid >= d.medid 
       and t.rowid <= medid+evenOdd
group by [avg]
       ,[min]
       ,[max]
       ,totalRecords
       ,AttemptedTotalRecords
       ,startDate

Be One with the Optimizer
TG

soorma
Yak Posting Veteran

52 Posts

Posted - 2009-10-22 : 12:44:48

This is still no working. i changed a little bit of query. but i am still not able to calculate the median. i am not using a temp table anymore. i am just querying against the table now.

here is the sql

Select Week, avg(total) as average, min(total) as minimum, max(total) as maximum, SUM(Total) as AttemptedTotalRecords, sum(lead) as TotalRecords
FROM
(
select week=DATEADD(wk, DATEDIFF(wk, 7, calldate), 7), count(I3_RowID) as total, 1 As Lead
From
test
where
(CallDate between '10/1/09' and '10/22/09')
and (reason<>'deleted' or reason<>'wrongparty' or reason<>'Success' or reason<>'Failure')
Group by DATEADD(wk, DATEDIFF(wk, 7, calldate), 7), i3_rowid
) a
Group by Week order by week

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-22 : 13:01:05

Did you try the solution I provided? What specifically was the problem?

Be One with the Optimizer
TG

soorma
Yak Posting Veteran

52 Posts

Posted - 2009-10-22 : 18:00:51

if i run the query that inserts the data in the temp table
the results is like this
total calldate
1 2009-10-01 07:00:29.000
1 2009-10-01 07:00:29.000
1 2009-10-05 17:48:12.000
1 2009-10-05 17:48:12.000

This is the query
select count(I3_RowID) as total,calldate
From test where
CallDate between '10/1/09' and '10/22/09' group by I3_RowID,calldate
order by calldate

If i run the diff query like this
select count(I3_RowID) as total,I3_RowID
From CallHistory where
CallDate between '10/1/09' and '10/22/09' Group by i3_rowid
order by total

total i3_rowid
1 5
4 45
2 14

In ur solution ur are using calldate which i am not inserting in the temp table.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-23 : 09:53:45

Ok - if you still want help, please post the structure of [test] and include a little sample data. If i3_rowid is an int column with a contiguous series of sequential values then you're right, you don't need the temp table.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources