August 24, 2009 at 7:48 am
Looking for some help here. I have a table that consists of Month/YR as columns and ID numbers as rows. The table is populated with 1's and 0's, 1 being months when an event occurred and 0 meaning no event occurred. I'm trying to determine the average number of months between purchases. I have no problem when there were only 2 months with events, but when a row is populated like this:
2005/12005/22005/32005/42005/52005/62005/72005/82005/92005/10
1000000000
0000000000
0000000010
0011001100
0000000000
0000000000
0000000000
0000000000
1010101100
1000000000
1010000110
1000000001
0000001011
1110111121
0100001010
0000000010
0100010100
0000000000
0000000000
0000000000
0000110000
0010011110
0000100000
0000001001
0000000000
0000000000
0000000000
1101101100
I can't figure out how to write a formula to give me the average counts between 1's per row. I have 30,000 rows and 56 columns.
Can anybody rescue me?
August 24, 2009 at 7:49 am
Oops, that didn't post quite like it looked. Each instance of 1's and zero's goes under the month columns.
August 24, 2009 at 8:16 am
If proper design had been used it would of been fairly easy.
But Ill simulate it with view.
I bet you have fun on the first of each month with the 'ALTER TABLE' statement fails to add the column 🙂
There may be a few syntax errors and typos but should be easily sortable
Create View SalesMonths
as
Select Id,
Month = convert(smalldatetime,20050101),
SalesCount = [2005/1]
from Yourtab
where Sales 0
union all
Select Id,
Month = convert(smalldatetime,20050201),
SalesCount = [2005/2]
from Yourtab
where Sales 0
union all
Select Id,
Month = convert(smalldatetime,20050301),
SalesCount = [2005/3]
from Yourtab
where Sales 0
union all
........
with SalesProc(Id,Month,PrevMonth)
as
(
Select Id,Month,(Select Top 1 Month
from SalesMonths PrevMonths
where PrevMonths.Id = SalesMonths.Id
and PrevMonths.Month < SalesMonths.Month)
from SalesMonths
)
,
SalesDiff(Id,MonthDiff)
as
(
Select Id,DateDiff(mm,PrevMonth,Month)
from SalesProc
)
Select Id,avg(MonthDiff)
from SalesDiff
August 24, 2009 at 8:42 am
Using Dave's idea of renormalizing the data (i.e unpivoting the data), it's actually a lot easier than it looks:
Select ID,
datediff(month, min(month), max(month)) /
(count(nullif(salescount,0))-1) AvgMonthsSale
from SalesMonths
group by sales
In order to make his view work, be sure to enclose the month values in Quotes. Frankly I would likely have done it as a temp tabl that gets indexed, but the view might still do the trick.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 24, 2009 at 11:33 am
Guys, thanks for the help. I renormalized the table and converted the data type and then I'm using
"Select ID,
datediff(month, min(month), max(month)) /
(count(nullif(salescount,0))-1) AvgMonthsSale
from SalesMonths
group by sales"
Every row is returning a 1. I am grouping by ID. There's no column labeled sales. What am I missing?
August 24, 2009 at 11:44 am
JP Sabin (8/24/2009)
Guys, thanks for the help. I renormalized the table and converted the data type and then I'm using"Select ID,
datediff(month, min(month), max(month)) /
(count(nullif(salescount,0))-1) AvgMonthsSale
from SalesMonths
group by sales"
Every row is returning a 1. I am grouping by ID. There's no column labeled sales. What am I missing?
ouch - that's my fault. You should be grouping by ID (i.e. the row id from the initial denormalized data)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 24, 2009 at 11:47 am
lol! No sweat.
But any idea why every row returns a 1?
August 24, 2009 at 12:03 pm
Here's my full test set. Looks pretty close to right.
One additional adjustment I made in the calculation of the average is to make sure that it was float (not integer, which makes a LOt of the results look the same).
drop table mths
drop view salesmonths
go
create table mths (id int identity(1,1), mth1 int,mth2 int,mth3 int,mth4 int,mth5 int,mth6 int,mth7 int,mth8 int,mth9 int,mth10 int)
insert mths(mth1 ,mth2 ,mth3 ,mth4 ,mth5 ,mth6 ,mth7 ,mth8 ,mth9 ,mth10 )
SELECT 1,0,0,0,0,0,0,0,0,0 UNION ALL SELECT
0,0,0,0,0,0,0,0,0,0 UNION ALL SELECT
0,0,0,0,0,0,0,0,1,0 UNION ALL SELECT
0,0,1,1,0,0,1,1,0,0 UNION ALL SELECT
0,0,0,0,0,0,0,0,0,0 UNION ALL SELECT
0,0,0,0,0,0,0,0,0,0 UNION ALL SELECT
0,0,0,0,0,0,0,0,0,0 UNION ALL SELECT
0,0,0,0,0,0,0,0,0,0 UNION ALL SELECT
1,0,1,0,1,0,1,1,0,0 UNION ALL SELECT
1,0,0,0,0,0,0,0,0,0 UNION ALL SELECT
1,0,1,0,0,0,0,1,1,0 UNION ALL SELECT
1,0,0,0,0,0,0,0,0,1 UNION ALL SELECT
0,0,0,0,0,0,1,0,1,1 UNION ALL SELECT
1,1,1,0,1,1,1,1,2,1 UNION ALL SELECT
0,1,0,0,0,0,1,0,1,0 UNION ALL SELECT
0,0,0,0,0,0,0,0,1,0 UNION ALL SELECT
0,1,0,0,0,1,0,1,0,0 UNION ALL SELECT
0,0,0,0,0,0,0,0,0,0 UNION ALL SELECT
0,0,0,0,0,0,0,0,0,0 UNION ALL SELECT
0,0,0,0,0,0,0,0,0,0 UNION ALL SELECT
0,0,0,0,1,1,0,0,0,0 UNION ALL SELECT
0,0,1,0,0,1,1,1,1,0 UNION ALL SELECT
0,0,0,0,1,0,0,0,0,0 UNION ALL SELECT
0,0,0,0,0,0,1,0,0,1 UNION ALL SELECT
0,0,0,0,0,0,0,0,0,0 UNION ALL SELECT
0,0,0,0,0,0,0,0,0,0 UNION ALL SELECT
0,0,0,0,0,0,0,0,0,0 UNION ALL SELECT
1,1,0,1,1,0,1,1,0,0
go
Create View SalesMonths
as
Select Id,
Month = convert(smalldatetime,'20050101'),
SalesCount = mth1
from mths
where mth1 0
union all
Select Id,
Month = convert(smalldatetime,'20050201'),
SalesCount = mth2
from mths
where mth2 0
union all
Select Id,
Month = convert(smalldatetime,'20050301'),
SalesCount = mth3
from mths
where mth3 0
union all
Select Id,
Month = convert(smalldatetime,'20050401'),
SalesCount = mth4
from mths
where mth4 0
union all
Select Id,
Month = convert(smalldatetime,'20050501'),
SalesCount = mth5
from mths
where mth5 0
union all
Select Id,
Month = convert(smalldatetime,'20050601'),
SalesCount = mth6
from mths
where mth6 0
union all
Select Id,
Month = convert(smalldatetime,'20050701'),
SalesCount = mth7
from mths
where mth7 0
union all
Select Id,
Month = convert(smalldatetime,'20050801'),
SalesCount = mth8
from mths
where mth8 0
union all
Select Id,
Month = convert(smalldatetime,'20050901'),
SalesCount = mth9
from mths
where mth9 0
union all
Select Id,
Month = convert(smalldatetime,'20051001'),
SalesCount = mth10
from mths
where mth10 0
go
Select ID,
datediff(month, min(month), max(month))*1.0 /
(nullif(count(nullif(salescount,0))-1,0)) AvgMonthsSale
from SalesMonths
group by ID
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 24, 2009 at 12:48 pm
Got it! You ROCK.
Thanks for all of your help!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply