Counting Field Totals As A Formula
Jul 23, 2005
Hi,
I'm trying to count field login totals for users which updates another field
in another table. Which is the most efficient method?
I don't want to use a standard query as it will take too long if there are
1000 users per company each with 1000 plus logins.
I was thinking in terms of either a function, or a formula (using the built-
in formula field within mssql).
the query though (as its the only way which i'm familiar) is:
SELECT SUM(NumberOfLogons) AS TotalLogons
FROM EmployerProfileDB39
WHERE (CompanyName = x) AND (EmployerID = y)
how would i write this as a formula or as a function?
View 1 Replies
ADVERTISEMENT
Dec 15, 2007
Hi Guy's,
New to servers,
I'm trying to change my Access query into a Server View but I am having trouble counting my Yes/No field that has become a bit field [RevReq].
This is what I have tried but [RevReq] gives me the same count as the [PayNumber] field which is counting the records in the table:
SELECT TOP (100) PERCENT StaffListSQL.dbo.StaffList.Level1, StaffListSQL.dbo.StaffList.[Group], StaffListSQL.dbo.StaffList.Discipline AS Disc,
COUNT(dbo.tblReviews.PayNumber) AS Staff, COUNT(dbo.tblReviews.RevReq) AS Rev, COUNT(dbo.tblReviews.AppStatus) AS RevApp
FROM dbo.tblReviews RIGHT OUTER JOIN
StaffListSQL.dbo.StaffList ON dbo.tblReviews.PayNumber = StaffListSQL.dbo.StaffList.[Pay Number]
GROUP BY StaffListSQL.dbo.StaffList.[Group], StaffListSQL.dbo.StaffList.Discipline, StaffListSQL.dbo.StaffList.Level1
ORDER BY StaffListSQL.dbo.StaffList.Level1
Here is what is returned:
Acute Clyde Acute Services PTB 16 16 0
Acute Clyde Acute Services S&P 0 0 0
Acute Diagnostic Services A&C 16 16 0
Acute Diagnostic Services AFC 15 15 0
Acute Diagnostic Services AHP 482 482 0
Acute Diagnostic Services ASC 338 338 0
Acute Diagnostic Services MAINT 1 1 0
Acute Diagnostic Services MED 0 0 0
Acute Diagnostic Services MISC 0 0 0
Acute Diagnostic Services NURSE - Q 765 765 0
Acute Diagnostic Services NURSE - U 62 62 0
Acute Diagnostic Services PTB 85 85 0
Acute Diagnostic Services S&P 10 10 0
Acute Emergency Care & Medical Specialties NULL 1 1 0
Acute Emergency Care & Medical Specialties A&C 48 48 0
Hope this makes sense and thanks in advance.
View 5 Replies
View Related
Oct 20, 2004
Does anyone know how to use Formula Field in SQL Server 2000. I have googled a lot, but haven't been able to find out how to use it..
Thx
View 2 Replies
View Related
Dec 22, 2005
Hello Everyone,
Here is my situation.
I have a field in a table (named QuestionPosition)(type Decimal) that contains values like this:
1 2 3 4 5.1 5.2 5.3 ...
I want to have another field in the same table that stores just the remainder (in the above case a 0 for integer values and the 1 of the 5.1, 2 of the 5.2 etc.
I created the field in the table (named QuestionSubPosition) and tried to set the formula for it to QuestionPosition % 1 but it does not accept the formula.
The part that i do not understand is that i can set the formula to QuestionPosition / 1 it works (but not the results i want).
Any ideas on how i can get the results i want?
Corey
View 3 Replies
View Related
Dec 5, 2007
I'm trying to only count the first instance of a field value for one specific field in a table. Example:
ColA ColB ColC
John Smith xyz
John Smith abc
John Smith xyz
John Smith abc
Larry Smith abc
Larry Smith xyz
The correct count would be 4 because we only count "John Smith xyz" and "John Smith abc" once each, but we count every instance thats unique. Seems simple enough but I can't seem to put it together. Thanks in advance!
View 5 Replies
View Related
Jul 23, 2005
Having a brainfart....I need a query that returns a record count, based on two distinct fields.For example:Order Revision Customer001 1 Bob001 2 Bob002 1 John003 1 John004 1 John005 1 Bob006 1 Bob006 2 BobThe query on the above data should return a count of orders, regardless ofthe revision numbers (each order number should only be counted once).So WHERE Customer = 'Bob', it should return OrderCount = 3TIA!Calan
View 2 Replies
View Related
Oct 27, 2003
Finding numbers of occurrences of a string of characters
in a column of TEXT datatype.
DDL of involved table txt:
create table txt (pk int, txtcol text) -- datatype of pk doesn't matter
declare @word varchar(80) set @word='help'
declare @pk int, @count int, @i int, @dl int, @wl int
set @wl=len(@word)
declare abc cursor for select pk from txt
where patindex('%'+@word+'%',txtcol)>0 order by pk
open abc fetch next from abc into @pk
while @@fetch_status=0
begin
select @dl=datalength(txtcol) from txt where pk=@pk
select @i=patindex('%'+@word+'%',txtcol)+@wl from txt where pk=@pk
set @count=1
while @i<@dl
begin
select @count=@count+(len(substring(txtcol,@i,8000))-
len(replace(substring(txtcol,@i,8000),@word,'')))/@wl
from txt where pk=@pk
set @i=@i+8001-@wl
end
select pk=@pk, occurrences=@count
fetch next from abc into @pk
end
close abc deallocate abc
pk occurrences
----------- -----------
1 1
pk occurrences
----------- -----------
2 2
pk occurrences
----------- -----------
3 11
Edit: as suggested-reminded by jsmith8858.
View 7 Replies
View Related
Jul 20, 2005
Hi,I am trying to convert string entered in a field to uppercase usingits formula property.I know it can be done using trigger but still I want to use formulaproperty to achieve the same.Any help will be greatly appreciated.-Max
View 3 Replies
View Related
Jul 3, 2014
I have a table with three columns: UniqID, Latitude, and Longitude.
I need to write a query to identify when the latitude has more than 6 decimal places past the decimal. Same with Longitude. Values in these attributes can be a negative number. These fields are FLOAT.
View 7 Replies
View Related
Oct 1, 2015
In report builder 2.0, I cannot determine how to display the number of times a field has been filled out when a one to many relationship is involved. I get an error every time I use two aggregates.
ID, Symptom_ID
1, 111
2, NULL
3, 222
3, 333
3, 444
4, NULL
5, 666
The result should be 3 out of 5 records... Or even better displayed as a percentage of the total 60%
View 2 Replies
View Related
May 9, 2015
I have some data grouped in a table by a certain criteria, and for each group it is computed a subtotal for the group. Of the values from each of the group, I want to create a grand total on the report by adding every subtotal from each group.
Example:
...
....
Group1 Value
10
20
Sub Total 1: 30
Group2 Value
15
25
Sub Total 2: 40
Now, I would like to be able to add subtotal 1 (30) to subtotal 2 (40) and my grand total would be 70. Can I accomplish this task in SSRS?
View 5 Replies
View Related
Sep 1, 2015
In SQL reporting, How do I add the formula in the Formula bar?All the data is coming from a sproc.
View 3 Replies
View Related
Oct 2, 2006
I have been providing sales data for a few months now from a table that is set up like this:
Date WorkDay GasSales EquipmentSales
9/1/2006 1 100.00 200.00
9/4/2006 2 50.00 45.00
etc.
As can be seen, the data is daily, i.e., on the first workday of September we sold one hundred dollars in gas and two hundred dollars in equipment. On the second workday of September we sold fifty dollars in gas and forty-five dollars in equipment.
Now, however, the data I have to pull from is cumulative. So, using the last table as an example it would look like this:
Date_WorkDay_GasSales_EquipmentSales
9/1/2006 1 100.00 200.00
9/4/2006 2 150.00 245.00
etc.
To make things more complicated, the powers that be wanted this data presented in this fashion:
Total Sales:
1_2_etc.
300.00 95.00 etc.
So, I have been doing a pivot on a CRT to get the data to look like I want. The code is like this:
with SalesCTE (Month, WorkDay, [Total Sales])
as
(
SELECT
datename(month, cag.date),
cag.WorkDay AS [Work Day],
sum(cag.sales_gas + cag.sales_hgs) AS [Total Sales]
FROM CAG INNER JOIN
Branch ON CAG.[Oracle Branch] = Branch.OracleBranch
group by cag.date, cag.WorkDay
)
select * from SalesCTE
pivot
(
sum([Total Sales])
for WorkDay
in ([1],[2],[3],[4],[5],,[7],,[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23])
) as p
So, my question is:
How do I get the data to give back daily totals instead of the cumulative amounts for each workday? If the query was a simple one, I'd do something like
select [1] as [Day 1], [2]-[1] as [Day 2], [3]-[2] as [Day 3], etc.
but the query is far from normal, with the CRT and the pivot. I can't seem to get it to work how I'd like.
Any advice/answers? Thanks in advance!!!
P.S. I don't know how to get it to quit with the freakin' smileys.... I suppose you can figure out what my code is really supposed to look like above. Needless to say, it doesn't include a devil face and a damn music note...
View 12 Replies
View Related
Jan 18, 2008
Having a hard time writing a formula in RS...
Trying to say if the funding date is equal to today or before the beginning of the month then sum the loan amount.
Looking for something like this SUM(IF(Funding Date,>=Date(),Loan Amount))
Know it not right but someone please help!
View 1 Replies
View Related
Jan 26, 2008
I am create a database and want to store some value automatically in some field i.e say i have 3 column 1) salary(int),2)tax(int) and 3rd field total salary(float) . i want to automatically fill total salary field as Column1-Column2 while i daont have any Idea How to do that?
please help me?
any idea or tutorial..example anything...
View 3 Replies
View Related
Nov 17, 2007
hi,my users can make posts in my web application, i mean they fill a form and the information they filled will be saved in sql server 2000 and can be shown in web application,now i want to give each post an Id and save it in the database, how can i do that? does sql server have the abilities or i should do sth in my c# application
thanx
View 1 Replies
View Related
Oct 2, 2005
i wonder what is the best approach to use !!!i have creditLimit column in Customer Table the default value will be 500 and this limit to allow users to send sms from my website ..... every month they will be allowed to send 500 sms referring to the credit limit column.... now !!if the user sent today some sms and after few days sent another 20 and after one week he sent 150 SMS as a total so that means he has only 350 SMS as credit to use this month!!!so what is the best approach to implement this solution ? shall i have another field in the table called 'CreditUsed' and that will be updated each time the user will send SMS and this value will be compared with the credit limit ORi use the formula for the column to calculate the credit left and do the maths !!!what is the best approach as you think ???thnaks for reading this question !!
View 7 Replies
View Related
Mar 30, 2006
hi there.
I have asked this question before in a different section of the forum without much reply, and its probably because i was asking the wrong people.
I need to calculate a qouta for an election.
SqlCommand SqlCmd1 = new SqlCommand("SELECT count(vote)FROM PRTest", SqlCon1);
int quota = (count(Vote) + 1) / ((11) + 1);
My problem is this: how to i get the count(vote) value from the Sql Statement to the formula.
View 2 Replies
View Related
May 10, 2006
This does nothing:([rush24] + [rush6] + [addLocationsTotal] + 50)
This gives me my total correctly:([rush24] + [rush6] + 50)
All column are numeric(9) except the "total" column which is numeric(13).
Why is this happening?
View 1 Replies
View Related
Jun 13, 2002
I want to use the proprity Formula related to a column propreties :
Exempel : table XFRS i want to pupulate the field XF2 (int 4) using the value of another Field XF1 (int 4) Which is an identity field.
But When i try to insert into the table XFRS then i had an error
"Insert faild because the following set options have incorrect settings"
'ARITHABORT'"
Anyone to help me ? Thinks
View 1 Replies
View Related
Nov 3, 2004
Can anyone tell me how to find out if a column is a 'formula' or computed column - and what the formula is?
sp_columns @table_name = 'table' gives me most of the stuff I want but it doesn't show what the formula is for any formula columns...
Cheers in advance,
View 1 Replies
View Related
Apr 10, 2008
I have a quick question. created a report with this formula below. It works when I put a date range from 1-1-2007 to 2-1-2007, but when I put in a date range of 03-01-2008 through 03-31-2008. i get an error message of arithmetic overflow error converting numeric to data type numberic.
So instead of haveing the formula below be (4,2) I put it as (5,2) and now it work. Why is that?
CAST(clm_sppo / clm_tchg * 100 AS decimal(4, 2)) AS PercentSavings
View 3 Replies
View Related
May 26, 2008
in front end application,iam using pivot table,there is no option to create the growth rate calcuation/formula in FE.
my table data consists like below:
country_name Revenue PERIOD_TYPE_OUT Amount_out
UK Solutions01 Apr 2007 To 31 Mar 200856.83000000
UK Solutions01 Apr 2006 To 31 Mar 2007116.07000000
while iam using the cross tab in front end application data view is coming as
country_name
01 Apr 2006 To 31 Mar 2007 01 Apr 2007 To 31 Mar 2008
solution solution
uk 116.07 56.83
Actual o/p should be:
01 Apr 2006 To 31 Mar 2007 01 Apr 2007 To 31 Mar 2008 Growth
solution solution
uk 116.07 56.83 -0.51
can anybody tell how to calcualate growth in stored procedure level formala for calculating growth rate
is :
(56.83-116.07)/116.07
([current year]-[previous year])/[previous year]
View 2 Replies
View Related
Apr 18, 2007
i have two tables .Table 1 contains certain columns with values.Table 2 shud get those data from table1 into its column and display the calculated result. Any idea how do i go abt it,bcuz table2 shud contain a formula so as to calculate.
View 5 Replies
View Related
Mar 8, 2008
Hi,
I want to store, mathematical formulas, in SQL, now how can i store integration signs, fractions, and various types of other symbols.
The output must be on html page using ASP. and also on VB frontend.
Pls. assist.
View 5 Replies
View Related
Mar 31, 2008
SELECT ID, CalibScoreAVGQA,
COUNT(CalibScoreAVGQA) AS Expr1
FROM dbo.TechPhonesCalibScoreAVGQA
Any idea why I'm getting "dbo.TechPhonesCalibScoreAVGQA.ID is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause
View 8 Replies
View Related
Jan 26, 2006
Hi,I have three tables in the following structure (simplified):Table 1: Containing the customers-------------------------------------------------create table Customers([cusID] int identity(1, 1) not null,[cusName] varchar(25) not null)Table 2: Containing the customer data fields---------------------------------------------------------------create table Data([datID] int identity(1, 1) not null,[datName] varchar(25) not null,[datFormula] varchar(1500))Table 3: Containing the customer data values-----------------------------------------------------------------create table Values([cusID] int not null,[datID] int not null,[valValue] sql_variant)In this structure the user can add as many data fields to a customer ashe wants (e.g. Country, City, Email, Phone, ...). I have added triggerswhich create a view similar to a pivot (I am working in SQL 2000) andadd triggers to the view so it is insertable, deletable and updateable.What I would like to do, is allow the user to create new fields wherethe values are based upon a calculation. This calculation would be donethrough a formula similar to what he would do e.g. in excel (thisformula is stored in the dimFormula field then).An example might help. Let's assume the user created a field 'Sales'(containing last year's sales) and 'Invoices' (containing the number ofinvoices that were created for him last year). Now, he wants to createa field 'AvgSales' with the formula '[Sales]/[Invoices]'.(Note that through adding these data fields, the above view was created(let's assume it is called vw_Customers and contains the columns [ID],[Name], [Sales], [Invoices], [AvgSales]).What I am looking for is a function which can parse this formula into at_sql query which runs the calculation. So, the formula'[Sales]/[Invoices]' would be translated into (let's assume there areno records with NULL or zero invoices):update vw_Customersset [AvgSales] = [Sales]/[Invoices]from vw_CustomersI am able to do the above with simple calculations (where you can evenuse sql functions e.g. year, len, ...). Now I would like to take thisone step forward into the possibility of using functions with morevariables.For example. Let's assume, the user wants to add a rating (field called'Rating') to his customers based upon the result of 'AvgSales. Heenters the formula 'if([AvgSales] > 2500, 'A', 'B')'.If anyone could help me on this, I would be very grateful. Thanks.M
View 3 Replies
View Related
Feb 17, 2006
env: sql server 2000objective:add a formula for an INT column to the FORMULA field or the DEFAULTVALUE field in DESIGN VIEW.all of the following attempts failedIIF (columnName = 0, "1", columnName + 1)IIF (columnName = 0, 1, columnName + 1)IIF (0, "1", columnName + 1)caveat: cannot use IDENTITYREASON: I'd like have sets of repeatable values for this row, e.g.set a1 -- row 12 -- row 2345set b1 -- row 623set c1 -- row 9234Underlying rationale is to support OO design I understand probably it'sgoing to be huge headache for lots of people down the road but ...TIA.
View 3 Replies
View Related
Jul 20, 2005
HI,I have a problem in formula column.I have 8 1 bit varibles in a tablefor ex: Flag1, falg2, flag3 ...Flag8Now I want to create another variable as a small integer and copy allthe flgas to that field.For ex:(flag1 << 0x80) | (flag2 << 0x40) | ..... | flag8I tried all possible ways?Let me know how to write the formula for this column.Thanks,Venkat.
View 1 Replies
View Related
Jul 20, 2005
Hi,I would like to create a calculated column using the formulasection for a table. I am having some trouble doing this.The table's name is ReportParameter. The calculated column's name istbcalculatedcolumn and tb1 and tb2 are boolean columns in the table.I would like to use an If then statement such as the following (inpsuedo code):If tb1 = 1 then tbcalculatedcolumn = 1Elseif tb2 = 1 then tbcalculatedcolumn = 2EndifThanks for the help,Bill
View 6 Replies
View Related
Oct 30, 2007
fellows
i am working in this formula
((4 * dbo.Drillability.WEIGHTONBIT)
/ (POWER(PD_Data.dbo.db_drill_hole_base.loaded_diameter, 2) * PI()) + (8 * dbo.Drillability.RPM * dbo.Drillability.TORQUE)
/ (POWER(PD_Data.dbo.db_drill_hole_base.loaded_diameter, 2) * dbo.Drillability.ROP)) * 0.006894757 AS SPEC_ENERGY_MPa
but my problem is that i have ceros in some records, i was thinking in to use ISNULL but when i tryed to run the query I got error close to 'as'........someone can help me whit this.
cheers
edwin
View 12 Replies
View Related
Apr 4, 2008
hy guys
i am trying to calculate a formula in sql, but
in the field lbs1 sometimes has ceros and the lbs2 sometimes has ceros as well
the resul of the formula is null, but this is supposed to be a value. the rest of fields always have data.
SQRT((((db_linear_density_1.[Linear density_1] * dbo.Assay_acquire.[lbs1(Kg)] + PD_Data.dbo.db_linear_density.[Linear density_2] * dbo.Assay_acquire.[lbs2(Kg)]) / (dbo.Assay_acquire.[lbs1(Kg)] + dbo.Assay_acquire.[lbs2(Kg)])) * ((dbo.HOLELOCATION.DEPTH - PD_Data.dbo.drillhole_base_MOR_MET_2004.stem) * 0.3048)) / (((dbo.Assay_acquire.[lbs1(Kg)] + dbo.Assay_acquire.[lbs2(Kg)]) / dbo.Assay_acquire.holetons) * (dbo.Assay_acquire.holetons / (dbo.HOLELOCATION.DEPTH * 0.3048 * dbo.Assay_acquire.[holearea(m2)]))) / 15) * 0.9325
cheers
View 3 Replies
View Related
Feb 12, 2008
Hi . My database seems to be somehow encrypted by the software I used. When I try to export my data some fields are encrypted like this:
1 => 31911
2 => 63758
3 => 95540
4 => 127258
5 => 158912
6 => 190504
7 => 222032
8 => 253498
I'm really sure it's not some complicated formula (because I managed to recover other fields) . I just want to reverse the formula used to "encrypt" this data.
Thank you. Excuse my english.
View 1 Replies
View Related
