Calculating Averages On Integer Fields
Nov 22, 2000
Yesterday we ran into a very surprising problem: a colleague had developed a view which calculated a numbero of domain functions on an integer field (sum, count, standard deviation, average).
We were amazed to find that the view always returned an integer value for the average (not for the std dev, though). This was obviously wrong. We even tried changing the function to Sum(fld)/Count(fld) where "fld" was the fieldname, but we still got this strange curious result.
I got around the problem using the CONVERT function to change the integer value into a floating (e.g. Avg(CONVERT(FLOAT(5),fld)), but this is so strange that I'm sure we'll have the same problem again and again.
Now, can anyone tell me why the SQL engine is so incredibly stupid to presume that the average of a series of integer values is necessarily an integer???
View 3 Replies
ADVERTISEMENT
Nov 9, 2007
Hi. I want to be able to calculate weekly averages of our report data. Here is a sample from our EmplyeeTrends table:
EmployeeID ReportDate TotalCases
2 1/1/2007 77
2 1/2/2007 63
3 1/1/2007 56All the report data is produced daily. However, my boss wants to be able to see weekly/monthly averages for each employee. Is this possible? Thanks so much for your help!
View 9 Replies
View Related
Dec 14, 2006
one of our marketing people downloads the DNC list from the FTC or one of the other F agencies. he then screens his cold calling list against it. he wants to put it into sql to make it faster.
it's a text file and imports 2 fields. The area code and the phone number. I want to import it as integers so i can put some logic to automatically screen his list against it. I can't concontanate two integers into a third column so I'm looking for some advice.
should i import them as strings originally, dump to a flat file and then import the full phone number as an integer? Or is there a better way?
View 3 Replies
View Related
Feb 8, 2007
I am using MS SQL Server 2000.
I have 4 text fields
1. event begin date
2. event begin time
3. event end date
4. event end time
I have to find the day and time difference in months days hours and minutes between the event begin date & time and the event end date & time – The event begin date and event end date are char(8) & event begin time and event end time are char(4) – with military time. I would need to combine the event begin date & event begin time to get the event begin date & time and the same for the event end date & time.
If the values of the 4 text fields are
event begin date = ‘20070207’, event begin time = ‘2015’, event end date = ‘20070208’, event end time = ‘0105’, the difference should be 0 month 0 day 4 hours 50 minutes.
Can somebody please help with MS SQL Server 2000 syntax?
View 1 Replies
View Related
Jul 2, 2007
I have the folliwing two fields, want tp concatenate:
=Fields!sequenceno.Value & =Fields!LogType.Value
Thank you very much for the information.
View 3 Replies
View Related
Jun 18, 2008
Is there anyway to find an average date value in SQL? it may sound like a strange question. Im on the bench as to whether it is possible
View 15 Replies
View Related
Jul 20, 2015
Working on a new database where the Date and Time are stored in a Date Time Field.
Then working on an OLDER database file within the same SQL Database contains these 2 items as integers:
transDate = "71615" (July 16, 2015)
transTime = "12345" (01:23:45 AM)
How do we convert both of them into a single SQL DateTime field such as "2015-07-16 01:23:45.000" so that it can be used in a join restricting to a date time in a different SQL File that properly has the DateTime in it?
This works well for converting the transDate Part in the select statement:
  dbo.IntegerToDate(at.transDate) as transDate
  * That returns: "2015-07-16 00:00:00.000"
* The resulting data must work directly in a Microsoft SQL Server Management Studio Query using either using the "on" statement or part of the "where" clause. In other words, NOT as a stored procedure!
Also must be able to be used as a date difference calculation when comparing the 2 files Within say + or - 5 seconds.
View 3 Replies
View Related
Aug 18, 2006
I am new to Reporting Services on SQL Server 2005 and I need help. I have a report that I have create below using matrices.
Qtr 1
1
2
3
4
5
6
7
8
9
10
11
12
13
Total
2004
80
60
70
95
70
85
60
90
110
55
90
90
50
1005
2005
5
5
10
20
5
5
5
5
5
10
75
The columns represent each week in a quarter and the row represents the year the week is in and the details is the number of new accounts created during that particular week of the year. I have couple of questions.
How do I get and average for the number of account created per week per quarter?
How do I create a percentage of the change of accounts per week per year?
Qtr 1
1
2
3
4
5
6
7
8
9
10
11
12
13
Total
AVG
2004
80
60
70
95
70
85
60
90
110
55
90
90
50
1005
77.31
2005
5
0
5
10
20
5
0
5
5
5
5
0
10
75
5.77
-93.8%
-100.0%
-92.9%
-89.5%
-71.4%
-94.1%
-100.0%
-94.4%
-95.5%
-90.9%
-94.4%
-100.0%
-80.0%
Thanks in Advance.
View 10 Replies
View Related
Dec 21, 2007
Hello all, I have the query below that is returning the difference in time between two scans, I now need to take that time and break it out by month and than break that out by shift and get an average for the 2 shifts for the month. Below is the query with some sample data. Your help is greatly appreciated. THANKS!!!
SELECT LoadID, DATEDIFF(MINUTE, mi, ma) AS DiffMinutes
FROM SELECT LoadID, MIN(ProcessDate) AS mi, MAX(ProcessDate) AS ma
FROM [Scan Detail]
WHERE (ScanSequence = 2 AND LoadSequence = 2) OR
(ScanSequence = 3 AND LoadSequence = 13)
GROUP BY LoadID) d
LoadIDDiffMinutes
80485
308755
226816
1609613
1477910
319006
364355
399487
218020
115616
339479
2838715
1960913
395079
288289
81946
3965310
84897
60017
View 7 Replies
View Related
Aug 3, 2007
Hi,
I have a table with a userid and duration field. I need to contrast and individual against a group of users from the table. I can get the users cumulative hours by date in the select clause.
I am having a problem producing the group's sum of averages in the main query.I have tried it many ways and just cant get it to work. I need to be able to display something like this below:
Date GV IV
1/1/07 .45 .37
1/2/07 .56 .45
1/3/07 .68 .59
--
So Far I have this:
SELECT
D1.Date,
GV=ROUND(CAST(SUM(D2.Duration) AS FLOAT) / 3600,2),
IV=(SELECT ROUND(CAST(SUM(DurationAll)AS FLOAT) / 3600,2)
FROM IR_UserDaily WHERE UserID=@UserID AND Date<=D1.Date)
FROM
( SELECT Date,Duration=AVG(DurationAll)
FROM IR_UserDaily
WHERE
UserID IN(SELECT FilterID FROM IR_Filter WHERE ReportID=@RID)AND(Date BETWEEN @LowDate and @HighDate)
GROUP BY Date
) AS D1
INNER JOIN
( SELECT Date,Duration=AVG(DurationAll)
FROM IR_UserDaily
WHERE
UserID IN(SELECT FilterID FROM IR_Filter WHERE ReportID=@RID)AND(Date BETWEEN @LowDate and @HighDate)
GROUP BY Date
) AS D2
ON D2.Date < = D1.Date
WHERE (D1.Date BETWEEN @LowDate and @HighDate) AND (D2.Date BETWEEN @LowDate and @HighDate)
GROUP BY D1.Date,D1.Duration
ORDER BY D1.Date
The problem is that the avg function is returning the avg for all values up to the inner join condition, I think.
Can someone help me here I know it should be simple.
View 1 Replies
View Related
Aug 20, 2007
A
A
A
A
B
B
Avg.
Name
1
2
3
4
1
2
xxxxxxxxxxx
12/16
90
100
85
10/8
100
--
xxxxxxxxxxx
20/16
93
5/8
100
--
xxxxxxxxxxx
16/16
89
90
16/8
95
--
Avg.
--
--
--
--
--
--
--
Greetings,
I have a matrix that looks similar to the table above with two row groups and one column group. Does anyone know a way to manually do column and row based averaging without using the avg() function -as the data is not numerical?
Thanks
View 2 Replies
View Related
Jul 14, 2015
I've been able to find plenty of info online in relation to calculating moving averages for a given value. I am having a specific problem however.
So because I need to see the average on a daily basis, my forumla (below) works fine:
However, my Calendar dimension (TIME) also has a discreet, non-continuous attribute called DateMonth (which looks like 1-Jan, 2-Jan etc.) which has no reference to the year.
I was hoping this would allow me then to take my year attribute and compare the 28 Day Average for the 1st of Jan across multiple years.
So you could have a line chart where the X axis is 1-Jan to 31-Dec (with no reference to the year) and the series category is by year, so a line for each year.
But when I replace DATE with DateMonth in my pivot, the calc gives some strange numbers.
Rolling28DayAvg :=
CALCULATEÂ (
    [Revenue],
    DATESINPERIOD (
        TIME[DATE],
        LASTDATE ( TIME[DATE] ),
        -28,
        DAY
    )
)
    / 28
View 15 Replies
View Related
Jan 28, 2008
I have just started using SQL Server reporting services and am stuck with creating subreports.
I have a added a sub report to the main report. When I right click on the sub report, go to properties -> Parameters, and click on the dropdown for Parameter Value, I see all Sum and Count fields but not the data fields.
For example, In the dropdownlist for the Parameter value, I see Sum(Fields!TASK_ID.Value, "AppTest"), Count(Fields!TASK_NAME.Value, "CammpTest") but not Fields!TASK_NAME.Value, Fields!TASK_ID.Value which are the fields retrieved from the dataset assigned to the subreport.
When I manually change the parameter value to Fields!TASK_ID.Value, and try to preview the report, I get Error: Subreport could not be shown. I have no idea what the underlying issue is but am guessing that it's because the field - Fields!TASK_ID.Value is not in the dropdown but am trying to link the main report and sub report with this field.
Am I missing something here? Any help is appreciated.
Thanks,
Sirisha
View 3 Replies
View Related
Feb 20, 2008
How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu".
can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.
View 8 Replies
View Related
Jan 26, 2008
sir
I have got this error message to establish connction with recordset vb .net, Can you please rectify this
Too many arguments to 'Public Overridable ReadOnly Default Property Fields() As ADODB.Fields'
my code like this
rs = New ADODB.Recordset
rs.Open("Select * from UserLogin where userid='" & txtUserName.Text & "'", gstrDB, DB.CursorTypeEnum.adOpenStatic)
If txtUserName.Text = rs.Fields.Append(userid) Then
MsgBox("OK", MsgBoxStyle.OKOnly, "Confirmation")
End If
thanks
View 1 Replies
View Related
Jul 23, 2005
Hello !I'm trying to update one table field with another table searched firstdate record.getting some problem.If anyone have experience similar thing or have any idea about it,please guide.Sample case is given below.Thanks in adv.T.S.Negi--Sample caseDROP TABLE TEST1DROP TABLE TEST2CREATE TABLE TEST1(CUST_CD VARCHAR(10),BOOKING_DATE DATETIME,BOOKPHONE_NO VARCHAR(10))CREATE TABLE TEST2(CUST_CD VARCHAR(10),ENTRY_DATE DATETIME,FIRSTPHONE_NO VARCHAR(10))DELETE FROM TEST1INSERT INTO TEST1 VALUES('C1',GETDATE()+5,'11111111')INSERT INTO TEST1 VALUES('C1',GETDATE()+10,'22222222')INSERT INTO TEST1 VALUES('C1',GETDATE()+15,'44444444')INSERT INTO TEST1 VALUES('C1',GETDATE()+16,'33333333')DELETE FROM TEST2INSERT INTO TEST2 VALUES('C1',GETDATE(),'')INSERT INTO TEST2 VALUES('C1',GETDATE()+2,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+11,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+12,'')--SELECT * FROM TEST1--SELECT * FROM TEST2/*Sample dataTEST1CUST_CD BOOKING_DATE BOOKPHONE_NOC12005-04-08 21:46:47.78011111111C12005-04-13 21:46:47.78022222222C12005-04-18 21:46:47.78044444444C12005-04-19 21:46:47.78033333333TEST2CUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.800C12005-04-05 21:46:47.800C12005-04-14 21:46:47.800C12005-04-15 21:46:47.800DESIRED RESULTCUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.80011111111C12005-04-05 21:46:47.80011111111C12005-04-14 21:46:47.80044444444C12005-04-15 21:46:47.80044444444*/
View 3 Replies
View Related
Jul 2, 2015
I am using MS SQL 2012. I have a table that contains all the data that I need, but I need to summarize the data and also add up decimal fields while at it. Then I need a total of those added decimal fields. My data is like this:
I have Providers, a unique ID that Providers will have multiples of, and then decimal fields. Here are my fields:
ID, provider_name, uniq_id, total_spent, total_earned
Here is sample data:
1, Harbor, A07B8, 500.00, 1200.00
2, Harbor, A07B8, 400.00, 800.00
3, Harbor, B01C8, 600.00, 700.00
4, Harbor, B01C8, 300.00, 1100,00
5, LifeLine, L01D8, 700.00, 1300.00
6, LifeLine, L01D8, 200.00, 800.00
I need the results to be just 3 lines:
Harbor, A07B8, 900.00, 2000.00
Harbor, B01C8, 900.00, 1800.00
LifeLine, L01D8, 900.00, 2100.00
But then I would need the totals for the Provider, so:
Harbor, 1800.00, 3800.00
View 3 Replies
View Related
Jul 20, 2005
I would like to search a table for a phrase, or for a partial phrase,eg on table product - for name or description, or name + descprition.How does one say select * from product where name + description like%phrase%or contains phraseCurrently I can get where name, or where descriotion like %phrase%,eg, where name like krups, or where description like coffee makerBut if I search for where name like %krups coffee maker% i get noresults. krups is in the name field, coffee maker is in thedescription field.Thanks,-M
View 1 Replies
View Related
Jun 30, 2000
Pardon me if this question is too elementary. I am trying to create a trigger that will cause certain datafields to be updated with values from other data fields in the same row when a certain column, created specifically to fire the trigger, is updated. The purpose of this is to reduce data entry by field personnel.I think I have the create trigger statement correct, but I'm a little confused on the update statement.
In a nutshell, how can I write something like:
UPDATE "TABLENAME"
SET DATAFIELD1 = DATAFIELD2
WHERE RECORDNUMBER = (THE SAME RECORD NUMBER)
I do know that I have to ensure that sp_dboption Recursive Triggers value is set to false, thanks.
View 2 Replies
View Related
Jul 31, 2013
I have a list of items in one table and a field (pageName) in another table that may contain one of the aforementioned items somewhere within that field. There is no fixed position within the field where the itemNo may be so I can't just use SUBSTRING(pageName,2,5) in(select itemNo from tblItem).
Logically, it's like I need to combine IN and LIKE: select pageName where pageName LIKE IN %select itemNo from tblitemNo%..LIKE can only handle one comparison string.
View 5 Replies
View Related
Jan 21, 2008
Hi people
My users are having troubles with link to default drillthrough report when reports are exported to excel (they REALLY don't like this behavior ), so I decided set all of them disabled in report model, this work fine, but calculated field in reports has this drillthrough link.
Let me show you the situation. Entity Product has an UnitaryCost field, I set the EnableDrillthrough Property in False so when I export a report with this field, no link is shown.
But if I create in the report a calculated field Round(UnitaryCost) this field has a Drillthrough Link
Is this the standard and expected behavior? or its simply a BUG?
Have I done something wrong in my model? and in this case, How I can correct this?
regards.
Julio Diaz.
View 1 Replies
View Related
Sep 26, 2006
I want to sum up a column, but only summing up the top 25 quantites and then I want to subtract the sum of all quantities from the total of the top 25...how would this be done with t-sql?
View 3 Replies
View Related
Jul 2, 2001
SQL7
I am interested in your opinion on the creation of primary keys in a table - simple ones that is. It would be the replacement for an Identity column.
Some say calculate the value off a lookup table, others say use an external object like c++ dll reading/incrementing from a flat file.
Using an identity column reduces portability of the table.
Thoughts ?
Craig
View 2 Replies
View Related
Nov 15, 2001
Hi,
What the best way to calculate the age given two datetimes.
I tried using the DATEDIFF(yyy,startdate,enddate) but that seems to only compare the year and not look at the specific day.
I am looking for something that would return the following output
birthday= 12/6/1973
EndDate = 11/30/2001
then the age should be 27
If birthday = 12/6/1973
enddate = 12/11/1973
then the age should be 28
thanks in advance
Zoey
View 3 Replies
View Related
May 19, 2008
Hi All,
If I run "SELECT 610/100", query analyser returns 6! (wrong)
If I run "SELECT 610.0/100", query analyser returns 6.100000! (correct)
I know I can get the correct result by running "SELECT Cast(610.0 as Float)/100", but why doesn't the first example return 6.1?
Is there some setting on my server which says Integer / Integer = Integer ?
Regards
Xo
View 12 Replies
View Related
Apr 17, 2007
I'm in the process of developing a Kimball-method CRM data warehouse and I'd like to have Recency, Frequency, Monetary "scores" at the customer level. Am I better off creating these in the relational database and feeding them to SSAS or have SSAS do the aggregations and assign the "scores"?
Also, has anyone located any SSIS transform scripts that would calculated RFM scores? This is probably a pretty easy task in VB.net but I haven't gone down that road yet.
Steve
View 1 Replies
View Related
Oct 31, 2006
It may be simple question, but how can i get the sum of column? with conditions like getting the some of clicks one user send and so...
View 5 Replies
View Related
Jul 3, 2007
When users register with my site they give me their DoB, how can I work out an age from it. I was thinkin about doing it from the year, but that isnt very accurate. Can anyone help? thanks si! ps Id only want to work out how many years old they are. si!
View 7 Replies
View Related
Feb 13, 2008
My question is, i guess, a simple one:
When is it more convenient to use a uniqueid Data Type instead of a smallint, tinyint, bigint, etc (any type of int) when the field is gonna be the primary key for the table?
View 1 Replies
View Related
Mar 28, 2008
I needed to come up with an algorithm to create unique user- friendly account numbers such as AC0000000001, AC0000000002, etc...Where they increment by 1. I created a SQL function that retrieves the previous number generated, adds 1 to it, inserts the new value into the table, then returns the new value. I started thinking, what if the function is ran at the same time? What if function # 1 creates the new number, and function #2 creates a new number as well before function #1 inserts it? Is this a possible scenario? If so, how do I lock the process until the function completes to prevent this? Thanks for any help you provide.
View 2 Replies
View Related
May 30, 2008
Hi,
First off, let me state that i am new to ASP.net... and have had to hit the ground running... I have looked on the net but have not found a suitable explanation on how to do this.
Let me explain what i am trying to do: In my aspx page, i have 2 dropdown boxes where the user will select a skill catagory, and then a skill. Based on that selection, i would like to compute what the Service line will be. There is a primary and foreign key on CatID on the skills and SkillCat tables. This data would need to be inserted into the another table aswell, and this would need to go into a report later.
How do i go about doing this? Some examples would be greatly appreciated. And if you know of a good link, please post it for me.
Skills Table
CatID
SkillID
Skills
SL_ID
1
4
Skill 1
3
2
5
Skill 2
2
2
6
Skill 3
2
3
7
Skill 4
4
3
8
Skill 5
4
4
9
Skill 6
NULL
4
10
Skill 7
NULL
5
11
Skill 8
3
6
12
Skill 9
3
7
13
Skill 10
4
8
14
Skill 11
1
9
15
Skill 12
3
9
16
Skill 13
3
10
17
Skill 14
6
11
18
Skill 15
1
Skills Catagory
CATID
Org
Skillcat
1
Org1
Skill Category 1
2
Org2
Skill Category 2
3
Org3
Skill Category 3
4
Org4
Skill Category 4
5
Org5
Skill Category 5
6
Org6
Skill Category 6
7
Org7
Skill Category 7
8
Org8
Skill Category 8
9
Org9
Skill Category 9
Service Line Table
SL_ID
Service_line
1
Service Line 1
2
Service Line 2
3
Service Line 3
4
Service Line 4
5
Service Line 5
6
Service Line 6
7
Service Line 7
Thanks for all your help in advance.
Dash
View 1 Replies
View Related
Oct 7, 2004
hi all,
i have an autonumber field (primary key) and another integer field as part of a table. What i want to do is when a record is created, the default value of the integer field should be the_autonumber+1000 for eg record with pk 82 will have an integer field that's automatically 1082. Would it be possible to do this ? Thanks in advance.
View 2 Replies
View Related
May 19, 2005
Public Function insertReport(ByVal userID As String, ByVal taskID As Integer) As DataSet
Dim ds As New DataSet
Dim da As New SqlDataAdapter("INSERT INTO report(userID, taskID) VALUES ('" + userID + "', " + taskID + ")", cn)
cn.Open()
da.Fill(ds)
cn.Close()
Return ds
End Function
Above is my code I used to invoke when inserting a record. But i receive an error message when i try to insert the integer. I cannot see where the problem lies..my datatype in sql server is int. Can anyone see what is wrong with it. Thank you in advance.
View 2 Replies
View Related
