?
Solved

Trend line problem, divide by zero

Posted on 2014-08-18
17
Medium Priority
?
381 Views
Last Modified: 2014-08-24
Hi
I Attached a query in which I need to calculate a linear trend line.
I found this solution, "Calculate linear trend line in TSQL",  and hoped that I could use the very fine solution in my query.
Unfortunatly I get a "Divide by zero" error. See lines 83 and 84 i the attached query
Can anyone see what is wrong. And is this the way to make a trend line.
SQLQueryTrendLine.sql
0
Comment
Question by:Henrik Klindt
  • 9
  • 5
  • 2
16 Comments
 
LVL 15

Expert Comment

by:Vikas Garg
ID: 40267095
Hi,

You can use CASE statement to avoid Divide by zero

,Case when Iweek  > 0 then (Avg(Iweek * 1.0000 * opened) - Avg(Iweek * 1.0000) * Avg(opened)) / VarP(Iweek * 1.0000) else 0 end AS RegressCoeff
,Case when Iweek  > 0 then  Avg(opened) - ((Avg(Iweek * 1.0000 * opened) - Avg(Iweek * 1.0000) * Avg(opened)) / VarP(Iweek * 1.0000)) * Avg(Iweek * 1.0000) 
else 0 end AS Intercept
FROM tickets2

Open in new window


It may be the case then if iweek value = 0 then this error can come
0
 

Author Comment

by:Henrik Klindt
ID: 40267133
I still get the divide by zero error.

Msg 8134, Level 16, State 1, Line 26
Divide by zero error encountered.

This is my result without the two trendline lines:
Iyear        Imonth     Iweek       Iday          opened    closed
2014	7	29	18	21	24
2014	7	29	14	8	23
2014	7	29	16	22	39
2014	7	29	15	26	19
2014	7	29	17	28	29
2014	7	30	23	25	40
2014	7	30	25	24	31
2014	7	30	24	17	22
2014	7	30	21	24	30
2014	7	30	22	23	22
2014	7	31	28	17	22
2014	7	31	31	21	25
2014	7	31	29	12	16
2014	7	31	30	24	22
2014	8	31	1	28	23
2014	8	32	4	19	24
2014	8	32	6	21	30
2014	8	32	8	15	12
2014	8	32	5	16	23
2014	8	32	7	21	26
2014	8	33	12	14	24
2014	8	33	11	14	17
2014	8	33	14	8	25
2014	8	33	15	10	15
2014	8	33	13	19	19

Open in new window

0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40267144
Try this:
SELECT
      Iyear
    , Imonth
    , Iweek
    , Iday
    , opened
    , closed
    , VARP(Iweek * 1.0000) varp_iweek
    --, (AVG(Iweek * 1.0000 * opened) - AVG(Iweek * 1.0000) * AVG(opened)) / VARP(Iweek * 1.0000)                                       AS RegressCoeff
    --, AVG(opened) - ((AVG(Iweek * 1.0000 * opened) - AVG(Iweek * 1.0000) * AVG(opened)) / VARP(Iweek * 1.0000)) * AVG(Iweek * 1.0000) AS Intercept
FROM tickets2
GROUP BY
      Iyear
    , Imonth
    , Iweek
    , Iday
    , opened
    , closed

Open in new window

It produces this from your sample:
| IYEAR | IMONTH | IWEEK | IDAY | OPENED | CLOSED | VARP_IWEEK |
|-------|--------|-------|------|--------|--------|------------|
|  2014 |      7 |    29 |   14 |      8 |     23 |          0 |
|  2014 |      7 |    29 |   15 |     26 |     19 |          0 |
|  2014 |      7 |    29 |   16 |     22 |     39 |          0 |
|  2014 |      7 |    29 |   17 |     28 |     29 |          0 |
|  2014 |      7 |    29 |   18 |     21 |     24 |          0 |
|  2014 |      7 |    30 |   21 |     24 |     30 |          0 |
|  2014 |      7 |    30 |   22 |     23 |     22 |          0 |
|  2014 |      7 |    30 |   23 |     25 |     40 |          0 |
|  2014 |      7 |    30 |   24 |     17 |     22 |          0 |
|  2014 |      7 |    30 |   25 |     24 |     31 |          0 |
|  2014 |      7 |    31 |   28 |     17 |     22 |          0 |
|  2014 |      7 |    31 |   29 |     12 |     16 |          0 |
|  2014 |      7 |    31 |   30 |     24 |     22 |          0 |
|  2014 |      7 |    31 |   31 |     21 |     25 |          0 |
|  2014 |      8 |    31 |    1 |     28 |     23 |          0 |
|  2014 |      8 |    32 |    4 |     19 |     24 |          0 |
|  2014 |      8 |    32 |    5 |     16 |     23 |          0 |
|  2014 |      8 |    32 |    6 |     21 |     30 |          0 |
|  2014 |      8 |    32 |    7 |     21 |     26 |          0 |
|  2014 |      8 |    32 |    8 |     15 |     12 |          0 |
|  2014 |      8 |    33 |   11 |     14 |     17 |          0 |
|  2014 |      8 |    33 |   12 |     14 |     24 |          0 |
|  2014 |      8 |    33 |   13 |     19 |     19 |          0 |
|  2014 |      8 |    33 |   14 |      8 |     25 |          0 |
|  2014 |      8 |    33 |   15 |     10 |     15 |          0 |

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Expert Comment

by:Vikas Garg
ID: 40267181
Hi

The issue is with the VAR / VARP function which is returning 0

Try this one

,
Case when VarP(Iweek * 1.0000) > 0 then (Avg(Iweek * 1.0000 * opened) - Avg(Iweek * 1.0000) * Avg(opened)) / VarP(Iweek * 1.0000) else 0 end AS RegressCoeff
,Case when VarP(Iweek * 1.0000) > 0 then  Avg(opened) - ((Avg(Iweek * 1.0000 * opened) - Avg(Iweek * 1.0000) * Avg(opened)) / VarP(Iweek * 1.0000)) * Avg(Iweek * 1.0000) 
else 0 end AS Intercept
FROM tickets2

Open in new window

0
 

Author Comment

by:Henrik Klindt
ID: 40267194
I got the same result. But why is VARP(Iweek * 1.000) always 0.
0
 

Author Comment

by:Henrik Klindt
ID: 40267215
Hi Vikas
With your solution the "divide by zero" error is gone.
But the regress and intercept is always 0.

I need to find out why VarP(Iweek * 1.000) is 0

The two trendline lines is taken from another solution. Maybe another way to calculate trendline is the solution?
0
 

Author Comment

by:Henrik Klindt
ID: 40267247
This is how it looks from where I took it.
I cannot see what is wrong with my query. Can anyone help?


CREATE TABLE afr (Month int, Value numeric(8, 2))

INSERT INTO afr (Month, Value) VALUES (1, 16.89)
INSERT INTO afr (Month, Value) VALUES (2, 15.87)
INSERT INTO afr (Month, Value) VALUES (3, 11.23)
INSERT INTO afr (Month, Value) VALUES (4, 14.82)
INSERT INTO afr (Month, Value) VALUES (5, 13.74)
INSERT INTO afr (Month, Value) VALUES (6, 7.62)
INSERT INTO afr (Month, Value) VALUES (7, 17.51)
INSERT INTO afr (Month, Value) VALUES (8, 13.96)
INSERT INTO afr (Month, Value) VALUES (9, 20.00)
INSERT INTO afr (Month, Value) VALUES (10, 11.38)
INSERT INTO afr (Month, Value) VALUES (11, 10.67)
INSERT INTO afr (Month, Value) VALUES (12, 11.76)

SELECT (Avg(Month * Value) - Avg(Month) * Avg(Value)) / VarP(Month) AS RegressCoeff,
    Avg(Value) - ((Avg(Month * Value) - Avg(Month) * Avg(Value)) / VarP(Month)) * Avg(Month) AS Intercept
FROM afr
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40267422
there isn't sufficient statistical variation in Iweek

Returns the statistical variance for the population for all values in the specified expression.
http://msdn.microsoft.com/en-us/library/ms188735.aspx

Let's face it Iweek isn't very interesting (except as an X axis perhaps)
0
 

Author Comment

by:Henrik Klindt
ID: 40267455
What is sufficient statistical variation? I tried with other fields, always with the same result.

Have I missed something in this article ?
http://www.experts-exchange.com/Database/MS-SQL-Server/Q_26713647.html
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40267678
sorry, it was meant as a question "there isn't sufficient statistical variation in Iweek ?"

If you use VARP(Iweek) OVER(partition by Imonth)   you get results, e.g.:
SELECT
      Iyear
    , Imonth
    , Iweek
    , Iday
    , opened
    , closed
    , VARP(Iweek * 1.0) over(partition by Imonth)
    , VARP(Iday * 1.0) over(partition by Iyear)

    --, VARP(Iweek * 1.0000)

    --, (AVG(Iweek * 1.0000 * opened) - AVG(Iweek * 1.0000) * AVG(opened)) / VARP(Iweek * 1.0000)                                       AS RegressCoeff
    --, AVG(opened) - ((AVG(Iweek * 1.0000 * opened) - AVG(Iweek * 1.0000) * AVG(opened)) / VARP(Iweek * 1.0000)) * AVG(Iweek * 1.0000) AS Intercept
FROM tickets2
;


| IYEAR | IMONTH | IWEEK | IDAY | OPENED | CLOSED |       COLUMN_6 | COLUMN_7 |
|-------|--------|-------|------|--------|--------|----------------|----------|
|  2014 |      7 |    29 |   18 |     21 |     24 | 0.637755102041 |  71.4304 |
|  2014 |      7 |    29 |   14 |      8 |     23 | 0.637755102041 |  71.4304 |
|  2014 |      7 |    29 |   16 |     22 |     39 | 0.637755102041 |  71.4304 |
|  2014 |      7 |    29 |   15 |     26 |     19 | 0.637755102041 |  71.4304 |
|  2014 |      7 |    29 |   17 |     28 |     29 | 0.637755102041 |  71.4304 |
|  2014 |      7 |    30 |   23 |     25 |     40 | 0.637755102041 |  71.4304 |
|  2014 |      7 |    30 |   25 |     24 |     31 | 0.637755102041 |  71.4304 |
|  2014 |      7 |    30 |   24 |     17 |     22 | 0.637755102041 |  71.4304 |
|  2014 |      7 |    30 |   21 |     24 |     30 | 0.637755102041 |  71.4304 |
|  2014 |      7 |    30 |   22 |     23 |     22 | 0.637755102041 |  71.4304 |
|  2014 |      7 |    31 |   28 |     17 |     22 | 0.637755102041 |  71.4304 |
|  2014 |      7 |    31 |   31 |     21 |     25 | 0.637755102041 |  71.4304 |
|  2014 |      7 |    31 |   29 |     12 |     16 | 0.637755102041 |  71.4304 |
|  2014 |      7 |    31 |   30 |     24 |     22 | 0.637755102041 |  71.4304 |
|  2014 |      8 |    31 |    1 |     28 |     23 | 0.413223140496 |  71.4304 |
|  2014 |      8 |    32 |    4 |     19 |     24 | 0.413223140496 |  71.4304 |
|  2014 |      8 |    32 |    6 |     21 |     30 | 0.413223140496 |  71.4304 |
|  2014 |      8 |    32 |    8 |     15 |     12 | 0.413223140496 |  71.4304 |
|  2014 |      8 |    32 |    5 |     16 |     23 | 0.413223140496 |  71.4304 |
|  2014 |      8 |    32 |    7 |     21 |     26 | 0.413223140496 |  71.4304 |
|  2014 |      8 |    33 |   12 |     14 |     24 | 0.413223140496 |  71.4304 |
|  2014 |      8 |    33 |   11 |     14 |     17 | 0.413223140496 |  71.4304 |
|  2014 |      8 |    33 |   14 |      8 |     25 | 0.413223140496 |  71.4304 |
|  2014 |      8 |    33 |   15 |     10 |     15 | 0.413223140496 |  71.4304 |
|  2014 |      8 |    33 |   13 |     19 |     19 | 0.413223140496 |  71.4304 |

http://sqlfiddle.com/#!3/eb0ff/10		

Open in new window

0
 

Author Comment

by:Henrik Klindt
ID: 40267770
Okay now we are getting somewhere :-)

But what does "over(partition by Imonth)" do? I dont know how it has to look like in my two trendline lines.
Can you build the two lines?
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40267809
well the evidence is in front of you isn't it?

column_6 and column_7 are BOTH calculated by an aggregate function VARP() but also using OVER()
( and there is no GROUP BY clause ! )

so the addition OVER() produces (and/or, repeats)  an aggregating value for each row without needing a group by clause.

the PARTITION BY is similar to a group by, it controls the clusters of records that are included in the calculations.
0
 

Author Comment

by:Henrik Klindt
ID: 40269573
Yes im sure the evidence is right in front of me :-) I just can't see it.


I tried with this:

,tickets3 AS
(
SELECT
Iyear
,Imonth
--,Iweek
,Iday
,opened
,closed
, VARP(Iweek * 1.0) over(partition by Imonth) as Iweek
--, VARP(Iday * 1.0) over(partition by Iyear) as testday

,(Avg(Iweek * 1.0000 * opened) - Avg(Iweek * 1.0000) * Avg(opened)) / VarP(Iweek * 1.0000) AS RegressCoeff
,Avg(opened) - ((Avg(Iweek * 1.0000 * opened) - Avg(Iweek * 1.0000) * Avg(opened)) / VarP(Iweek * 1.0000)) * Avg(Iweek * 1.0000) AS Intercept
FROM tickets2
Group BY
Iyear,Imonth,Iweek,Iday,opened,closed
)


select *
from tickets3
order by Iyear,Imonth

I still get the divide by zero.

Then I tried this version:

,tickets3 AS
(
SELECT
Iyear
,Imonth
--,Iweek
,Iday
,opened
,closed
--, VARP(Iweek * 1.0) over(partition by Imonth) as Iweek
--, VARP(Iday * 1.0) over(partition by Iyear) as testday

,(Avg(Iweek * 1.0000 * opened) - Avg(Iweek * 1.0000) * Avg(opened)) / VarP(Iweek * 1.0000) over(partition by Imonth) AS RegressCoeff
,Avg(opened) - ((Avg(Iweek * 1.0000 * opened) - Avg(Iweek * 1.0000) * Avg(opened)) / VarP(Iweek * 1.0000) over(partition by Imonth)) * Avg(Iweek * 1.0000) AS Intercept
FROM tickets2
Group BY
Iyear,Imonth,Iweek,Iday,opened,closed
)


select *
from tickets3
order by Iyear,Imonth

Which returns this. Is this the wrong way to use over(partition by Imonth)


Iyear      Imonth      Iday      opened      closed      RegressCoeff      Intercept
2014      7      18      21      24      0      21
2014      7      24      17      22      0      17
2014      7      25      24      31      0      24
2014      7      30      24      22      0      24
2014      7      16      22      39      0      22
2014      7      21      24      30      0      24
2014      7      29      12      16      0      12
2014      7      31      21      25      0      21
2014      7      23      25      40      0      25
2014      7      17      28      29      0      28
2014      7      22      23      22      0      23
2014      7      15      22      13      0      22
2014      7      28      17      22      0      17
2014      8      4      19      24      0      19
2014      8      18      10      30      0      10
2014      8      1      28      23      0      28
2014      8      5      16      23      0      16
2014      8      7      23      26      0      23
2014      8      8      16      12      0      16
2014      8      12      14      24      0      14
2014      8      14      10      25      0      10
2014      8      13      21      19      0      21
2014      8      6      22      30      0      22
2014      8      11      15      17      0      15
2014      8      15      16      14      0      16
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 300 total points
ID: 40269606
mmm, I shall have to be less subtle.

I don't know the answer to your problem - but I was able to identify why you were getting a divide by zero. That is really all I was trying to offer.

Since then I have attempted to display other possible ways of getting a non-zero result from VARP()
but I have to admit I have never used that function
nor do I fully understand the maths behind it

so I'm NOT in a position to be assertive about what way you use VARP() or what options you could use to partition.

VARP() is one of those aggregating functions that also allows use of the OVER() clause

OVER() you can use
PARTITION BY -- this is similar to group by but not the same
or
ORDER BY

bottom line: if using OVER() helps you meet the overall needs that's great - but I can't tell you much more than I have.
0
 

Accepted Solution

by:
Henrik Klindt earned 0 total points
ID: 40271886
Hi
It was the group by that caused the error. Here is what I did to make it work.

But thank you for your kind help.



,tickets3 AS
(
SELECT
(Avg(Iday * 1.0000 * (closed+opened)) - Avg(Iday * 1.0000) * Avg((closed+opened))) / VarP(Iday * 1.0000) AS RegressCoeff
,Avg((closed+opened)) - ((Avg(Iday * 1.0000 * (closed+opened)) - Avg(Iday * 1.0000) * Avg((closed+opened))) / VarP(Iday * 1.0000)) * Avg(Iday * 1.0000) AS Intercept
FROM tickets2
)

,tickets3 AS
(
SELECT
(Avg(Iday * 1.0000 * (closed+opened)) - Avg(Iday * 1.0000) * Avg((closed+opened))) / VarP(Iday * 1.0000) AS RegressCoeff
,Avg((closed+opened)) - ((Avg(Iday * 1.0000 * (closed+opened)) - Avg(Iday * 1.0000) * Avg((closed+opened))) / VarP(Iday * 1.0000)) * Avg(Iday * 1.0000) AS Intercept
FROM tickets2
)




Year      Month      Week      Day      Open      Closed      RegressCoeff      Intercept      Netto
2014      7      29      16      13      20      0,158504346267587      40,3561475042567      -7
2014      7      29      17      28      29      0,158504346267587      40,3561475042567      -1
2014      7      29      18      21      24      0,158504346267587      40,3561475042567      -3
2014      7      30      21      24      30      0,158504346267587      40,3561475042567      -6
2014      7      30      22      23      22      0,158504346267587      40,3561475042567      1
2014      7      30      23      25      40      0,158504346267587      40,3561475042567      -15
2014      7      30      24      17      22      0,158504346267587      40,3561475042567      -5
2014      7      30      25      24      31      0,158504346267587      40,3561475042567      -7
2014      7      31      28      17      22      0,158504346267587      40,3561475042567      -5
2014      7      31      29      12      16      0,158504346267587      40,3561475042567      -4
2014      7      31      30      24      22      0,158504346267587      40,3561475042567      2
2014      7      31      31      21      25      0,158504346267587      40,3561475042567      -4
2014      8      31      1      28      23      0,158504346267587      40,3561475042567      5
2014      8      32      4      20      24      0,158504346267587      40,3561475042567      -4
2014      8      32      5      18      23      0,158504346267587      40,3561475042567      -5
2014      8      32      6      22      30      0,158504346267587      40,3561475042567      -8
2014      8      32      7      23      26      0,158504346267587      40,3561475042567      -3
2014      8      32      8      16      12      0,158504346267587      40,3561475042567      4
2014      8      33      11      15      17      0,158504346267587      40,3561475042567      -2
2014      8      33      12      14      24      0,158504346267587      40,3561475042567      -10
2014      8      33      13      24      19      0,158504346267587      40,3561475042567      5
2014      8      33      14      12      25      0,158504346267587      40,3561475042567      -13
2014      8      33      15      16      14      0,158504346267587      40,3561475042567      2
2014      8      34      18      21      30      0,158504346267587      40,3561475042567      -9
2014      8      34      19      9      28      0,158504346267587      40,3561475042567      -19
0
 

Author Closing Comment

by:Henrik Klindt
ID: 40281354
It was the group by that caused the divide by zero error.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question