Where Should Be The Calculations Be Made?

Mar 28, 2007

where should be the calculations be made based on performance, in SQL or in front end?

thanks.
-Ron-

View 7 Replies


ADVERTISEMENT

I Have Made It This Far...

Sep 6, 2007

Ok I have my login controls working perfectly, my roles function perfect now come time to upload to production server I find I need to convert this to either .sql or .csv file to allow for importation.
 
Is there a S/W Package available for doing this?

View 1 Replies View Related

SQL Calculations

May 18, 2007

Hello all. I am trying to do a calculation within an SQL script, however it doesnt seem to be working and i'm a little bit lost. If anyone could shed some light on where i'm going wring it would be much appreciated. The code I have is:




select
EMPLOYEE.EMPLOY_REF AS EDIT_REF,
SV_EMPLOYEE_CURRENT_HOLIDAY.ENTITLEMENT,
SV_EMPLOYEE_CURRENT_HOLIDAY.CARRIED_FWD,
SV_EMPLOYEE_CURRENT_HOLIDAY.TAKEN,
SV_EMPLOYEE_CURRENT_HOLIDAY.REMAINING,
SV_EMPLOYEE_CURRENT_HOLIDAY.SOLD,
SV_EMPLOYEE_CURRENT_HOLIDAY.PURCHASED,
SV_EMPLOYEE_CURRENT_HOLIDAY.ENTITLEMENT + SV_EMPLOYEE_CURRENT_HOLIDAY.SOLD - SV_EMPLOYEE_CURRENT_HOLIDAY.PURCHASED AS TOTAL_ENTITLEMENT
from
EMPLOYEE
left outer join
SV_EMPLOYEE_CURRENT_HOLIDAY
on
EMPLOYEE.EMPLOY_REF = SV_EMPLOYEE_CURRENT_HOLIDAY.EMPLOY_REF
where
EMPLOYEE.EMPLOY_REF = = 027



Incidentaly SV_EMPLOYEE_CURRENT_HOLIDAY is a view which currently exists.

Thanks in advance people.

View 2 Replies View Related

How To Get Last Payment Made

Jun 20, 2007

I need to get the clientid, last payment date, and last payment amount. I have tried using MAX but this does not work. Can anyone see what I'm doing wrong? I get all payments and all dates. I'm summing the payment amount incase they made two payments on the same day, not likely but...
SELECT     dbo.tblLoan.Client_ID, MAX(dbo.tblPayments.PaymentDate) AS [Last Pay Date], SUM(dbo.tblPayments.AmountPaid) AS [Last Pay Amt]FROM         dbo.tblLoan INNER JOIN                      dbo.tblPayments ON dbo.tblLoan.Loan_ID = dbo.tblPayments.Loan_IDGROUP BY dbo.tblLoan.Client_IDHAVING      (dbo.tblLoan.Client_ID = @Client_ID) 

View 3 Replies View Related

T-SQL Tiem Calculations

Jul 1, 2007

In order to find out if an event is late or not I need to do some time calculations in SQL as a Stored procedure.
 I have a DateTime variable called Due
I also have an Allowance variable which is an integer and is an extra allowance for that day and a third variable Now which is set with GETDATE()
If I compare Now to Due I can decide if the task is late or not - but I need to take itno account the Allowance.
I tried :
IF @Due + (@Allowance /24) < @Now ......
 However I find that @Allowance/24 always equates to zero so this doesn't work.
I'd appreciate any advice.
 Regards
Clive

View 2 Replies View Related

I Think I Have Made Something Way To Complicated...

Dec 27, 2007

All I am trying to do is use a SqlDataSource, Read that Data from the Session Variable, Do a RowFilter off the Data and then return the Data (Currently a DataSet but doesnt matter).  But Since I dont use SqlDataSource that much not sure all the code I have is neccasry:
 protected DataSet GetSearchAppData(string strWhere)
{
DataSet dsSearchAppTable = new DataSet("SearchAppTable");
SqlDataSource ss = new SqlDataSource();
DataView dv = new DataView();
DataSourceSelectArguments dsArgg = new DataSourceSelectArguments();

dsArgg = DataSourceSelectArguments.Empty;
ss = ((SqlDataSource)Session["SearchAppTable"]);
dv = (DataView)ss.Select(dsArgg);
dv.RowFilter = strWhere;
dsSearchAppTable.Tables.Add(dv.Table.Clone());

return dsSearchAppTable;
}
 And to mention it, dsSearchAppTable returns a Table with 0 rows.  Not sure what the heck I'm doing wrong.
 
J

View 1 Replies View Related

Can This Be Made Into A Query

Feb 19, 2008

I am trying to make a query that would list all the rows in a table called tickets. It should order the results based on the amount of rows another table has, called rating, but only the rows where the ticketid (primary key) matches and where the date is a required date or higher. It should also order them based on the osid which is located in a systems table (there is a relationship between tickets and systems). So: Order by rows in ratings table relating to ticketid that happened in last week (or another min date)Only display it where osid in systems matchs (easy enough) 

View 2 Replies View Related

Calculations In A Datagrid?

Mar 21, 2008

Hello,
I ran into a little problem. My problem is: i need to substract 2 variabeles from 2 different tables in the database 



TitleTimes left todayTimes left


My first excercise!15


My second excercise!19


The fields times left are a calculation... the number of times that the admin entered minus a count in the table scores.
Has anyone an idea how i can solve this?
An example excercise would be great!
Thanks in advance

View 5 Replies View Related

DateTime Calculations

Jun 5, 2008

I am attempting to construct a SELECT statement which incorporates some variables.  The variables begin life as strings (not String objects) looking like :"6/08/2008" and "06/10/2008" for example.  The first is a start date which was retrieved using an AJAX calendar object and the second is an end date retrieved in the same manner.  My records are all timestamped by MS SQL (2003) including the clock time.  I am stumbling on the syntax.  "CallStartTime"  is the record's timestamp.  The "TraversalString" is something else but I am not attacking that yet.  Can anyone make a suggestion or two?
SELECT count(*)FROM RealTime WHERE CallStartTime >= '@starttime' AND CallStartTime <= '@endtime' AND TraversalString LIKE '%1.0%'

View 2 Replies View Related

Sql Column Calculations?

Jun 5, 2006

Timespan = 30 days
Start Date = January 1st
Last Processed Day = NULL
Next Cycle Day = IF(Last Processed Day IS NULL)                                 Start Date + TimeSpan                             ELSE                                  Last Processed Day + TimeSpan
Is it possible to setup a column to do this from sql?

View 1 Replies View Related

SQL For Time Calculations

Jul 8, 2002

I have a timesheet table and I am having trouble getting a calculation to work correctly. I'd like to subtract the punch-in times from the punch out times for a specific period, such as 1 week, and then add the time together and get the number of minutes worked altogether during that time.

The table is simply

Employee ID (int)
PunchIn (datetime)
PunchOut (datetime)

Thanks for any help.

EL

View 2 Replies View Related

Datetime Calculations

Jan 4, 1999

I am trying to do a calculation to find rows which have a date which is 2 days older the the getdate().
i.e Select documentdate from table where documentdate < (getdate() - 2)

Any ideas on how I can perform this operation.

Thanks
Vinny

View 1 Replies View Related

SQL Multiple Calculations In One SP

Mar 5, 2007

Hi,

I think that I want to want to build a temporary table in a stored procedure that handles multiple calculations.

I'll try and explain and hopefully you can tell me what i want

First i want to populate a list of all dealerstaff.


Code:

Select * From tblDealerstaff


I Then want to calculate the percentage tasks completed. So i find the number of available awards.


Code:

SELECT TOP (100) PERCENT COUNT(NoPerStaff) AS NoAwardsAvailable
FROM dbo.tblIndivAwards

Then i can find the number awards recieved per person.


Code:

SELECT TOP (100) PERCENT COUNT(dbo.tblIndivAwarded.AwardID) AS AwardCount
FROM dbo.tblIndivAwarded INNER JOIN dbo.tblIndivAwards ON dbo.tblIndivAwarded.IndivAwardID = dbo.tblIndivAwards.IndivAwardID
WHERE (dbo.tblIndivAwarded.staffID = @StaffID)

Trying to get my recordset to look like this >>

Fname + Lname + ((iNoAwarded / iNoAvailableAwards) * 100)

I want to know if it's possible to piece all of this calculation into a single stored procedure. I need to do this sort of thing all the time, as i display overviews of the projects i run to the senior managers.

I always do the maths in the ASP, and consequently i can't sort on the calulated totals.

Is this possible, or am i asking too much ?

Thanks in advance, as any help will be greatly recieved.

Regards,

Chris Brown

View 2 Replies View Related

Insert, Calculations && Where

Jul 7, 2004

Hi

I sometimes find myself in the situation where I want to insert a row into a table using the following form:
insert table ( <field list> ) select <field list> from .. etc .. Where <conditions>

My question is to do with where one or more of the fields in the select field list are calculations and where I also want to use some/all of these derived fields as Where conditions. [ Eg: only insert if the calculated value is > 0]

I currently either repeat the calculation in the Where clause or move it to a function and use the function call in both places. (I always get a pang of guilt using either option - repeating the calculation feels like bad practice - & using the function twice seems inefficient (does this get optimised?)).

I could get a life & stop worrying - but is there a better/neater way of doing this?

Many thanks.

View 3 Replies View Related

Date Calculations

Aug 24, 2005

Vikram writes "Hi,

I need some help in date manipulations on SQL server data.

I am required to calculate to see

1. If anyone is absent one day within a 30 day period. if they are then upon their next absence in the same rolling 30 day period they need to be move to step I of disciplinary stage.

this information has to be checked for hundreds of employees.

I tried many ways but am not getting the right results. Moreover upon defaulting they will have to be moved to differnt steps.

Any suggestions will be greatly appreciated.

thanks in advance

Vik"

View 3 Replies View Related

Date Calculations.. Please Help!

Nov 25, 2005

Hello,

I am a student and I have an assignment in for next week and we are having a few problems... below is some code we have created to enter in payment details for a customer....

INSERT into Payments values(Payments_seq.nextval, initcap('&Payment_Method'), '&Amount_Payable',
Date_Due = (select date_of_order from orders where order_no = (date_of_order+7));

I am having trouble with the last line, I want the date due to be calculated from the date the order was made in another table (orders) and I want a week to be added to this so that in the field it will display (date ordered plus 7 days)...

Any help you can give would be great, I've only been using isql plus for about 4 weeks...

Thanks

Pam :)

View 2 Replies View Related

Number Of Calculations

May 17, 2006

Users,

My question is about the number of calculation made with my database and my query.

The situation:

Table1: Contains 900 projects
Table2: Contains 13500 timesheets
Table3: Contains 516 Periods

Then there are 3 queries:

Query 1: Calculating the total for the periods
Query 2: Calculating working hours + working hours * total of a period
Query 3: Calculating loancosts of a project.

Query 3 is using 2 and 2 is using 1.

How many calculations are made in total????

Thanks!

Willio

View 4 Replies View Related

Perform Calculations

Aug 10, 2006

Hello all.

I am looking to perform a calculation and enter the reult into a field within my table. The fields that I need to base the calculation on are all in one table (SALARY). The fields are: SALARY and BASIC_HOURS and the result is to be entered into field HOURLY_RATE. The actualy calculation to be preformed is:

hourly rate = ()salary / 52)/basic hours)

Thanks for reading :)

View 4 Replies View Related

Who Nos When The Polygon Made Up In

Mar 3, 2008

i need help on this one

View 8 Replies View Related

Column Calculations

Jul 23, 2005

Hi,I have a select query that returns three integer fields from a table thevalues range from 0 to 5. On each row I would like to calculate the averagevalue in the three fields however, the difficulty is that only the rowswhere the value is greater than 0 should be included in the calculation.To make this clearer please consider the following example:Col1 Col2 Col2 Average Calculation1 2 3 2 (Col1+Col2+Col3)/32 2 0 2 (Col1+Col2+Col3)/2The first row should be divided by 3 because each of the three columns has avalue greater than 0, however the second row should be divided by 2 becauseonly two of the value are greater than 0.Is this possible?Also is is possible to pass the results of one calculation into anothercalculation.Thanks in advance,Steve

View 3 Replies View Related

Nulls In Calculations

Jul 20, 2005

hi,i ran the follow command:select #tempra.ranumber,#tempra.amountdue,#tempquickrec.t otalrec,#temparap.amountpaid,bal=amountdue - totalrec+amountpaidfrom #tempraleft join #tempquickrec on #tempquickrec.hrr = #tempra.ranumberleft join #temparap on #temparap.ranumber = #tempra.ranumberresult:ranumber amountdue totalrec amountpaid bal1222 $1200 $1000Null null2222 $3000 $3000 Null null3333$3000 $5000 $2000 0i know null is not zero thus bal column is null.this is because there is no corresponding record in the #temparap tableso how do i obtain the following result:(ie Ranumber bal is not 0)ranumber amountdue totalrec amountpaid bal1222 $1200 $1000Null 2002222 $3000 $3000 Null 03333 $3000$5000 $2000 0i am using ms sql2000thanks in advancerashid

View 2 Replies View Related

HELP With TIME Calculations

Jul 20, 2005

I am looking to calculate the difference between and event time and a sampletime of Now. This is the query that I thought would do it, however I'mreturning DIFFERENCE values that look the same when the calcuation is beingmade on different EVENT_TIME values....I thought I knew how DateDiff worked, but apparently not.select GetDate()as NOW,event_time,Datediff(s,(Cast(event_time asNumeric)),(Cast(GetDate() as Numeric))) as DIFFERENCEFROM events-----------NOW----------|-----EVENT_TIME------|-DIFFERENCE2004-06-30 11:22:38.560 1999-10-30 23:51:37.000 1472256002004-06-30 11:22:38.560 1999-10-30 23:23:47.000 1472256002004-06-30 11:22:38.560 1999-10-30 06:49:38.000 1473120002004-06-30 11:22:38.560 1999-10-30 06:50:00.000 1473120002004-06-30 11:22:38.560 1999-10-30 06:50:41.000 1473120002004-06-30 11:22:38.560 1999-10-30 06:49:59.000 1473120002004-06-30 11:22:38.560 1999-10-30 06:49:58.000 1473120002004-06-30 11:22:38.560 1999-10-30 06:50:53.000 1473120002004-06-30 11:22:38.560 1999-10-30 06:50:46.000 1473120002004-06-30 11:22:38.560 1999-10-30 06:49:42.000 1473120002004-06-30 11:22:38.560 1999-10-30 06:50:36.000 1473120002004-06-30 11:22:38.560 1999-10-30 06:50:07.000 1473120002004-06-30 11:22:38.560 1999-10-30 10:54:37.000 1473120002004-06-30 11:22:38.560 1999-10-30 11:40:15.000 1473120002004-06-30 11:22:38.560 1999-10-30 09:52:51.000 1473120002004-06-30 11:22:38.560 1999-10-30 12:12:46.000 1472256002004-06-30 11:22:38.560 1999-10-30 12:32:45.000 1472256002004-06-30 11:22:38.560 1999-10-30 12:32:45.000 1472256002004-06-30 11:22:38.560 1999-10-30 12:32:46.000 1472256002004-06-30 11:22:38.560 1999-10-30 12:46:30.000 1472256002004-06-30 11:22:38.560 1999-10-30 15:31:25.000 1472256002004-06-30 11:22:38.560 1999-10-30 23:08:25.000 1472256002004-06-30 11:22:38.560 1999-10-30 16:35:51.000 147225600Can someone help?TIA!Joe..

View 2 Replies View Related

Date Calculations...

Jul 20, 2005

I have a field that contains date information, and sometimes timeinformation as well. I would like to be able to take that date and do acalculation on it. Here are some examples of what is in the field:01/12/2003 5:04:00 PM24/11/200319/05/2003 6:30:00 AMHow can I take that date, then do a calculation like minus 5 days from thedate. I understand that I am to use the GETDATE() function, but below isthe SQL I have implemented.SELECT Field1, Field2, Field3FROM Table1WHERE (convert(char(10),Field1) like convert(char(8), GETDATE()-5))For some reason this works, and it will return results that occur on thisday, but it disregards the year. Now someone will probably ask "Whyconvert, char(10), etc". To be honest, I do not know and I ended upimplementing it from some other Usenet posts that are out there. I wastrying to figure this out and I ended up with that working until I laterrealized it was only caring about the day and month. Any ideas what I amdoing wrong here? I just want to return results that have the day being 5minus the current day. I am not interested in time information.Thanks if anyone can help, I am by far not experienced in SQL.

View 1 Replies View Related

SQL Calculations Different Than On Calculator?

Jul 20, 2005

I'm running SQL query to caluclate projected food costs. Thecalculation is this:(ReportedFoodSales / PlanFoodSales) * FullPlanFoodSalesSeems simple enough to me.Using the following numbers, SQL comes up with a different answer thanwhat I do with a calulator. The data types are money. I'm sure thereis some reasonable explanation....right?Reported Food Sales: 28096.4500Plan Food Sales: 28608.4167Full Plan Food Sales: 137702.0000SQL Answer: 135237.1342Calculator Answer: 135237.7308Any ideas?Thanks,Jennifer

View 3 Replies View Related

Calculations Tool Bar

May 28, 2008

Hi -

I'm working in Visual Studio 2005 and created an Analysis Services Project.

Using the Business Intelligence Wizard, I created a "Year To Date" calculation. The Year to Date calcualtion appears to work fine.

My problem is that I'd like to edit the calcualtion but most of the icons on the Calculations Tool Bar are disabled. How does this happen and how can I enable them?

Thanks, Mark

View 1 Replies View Related

How Can I Made SQL Modifications That I Can Do At EM ?

Aug 14, 2006

Hi all, this probably is a stupid question, but here I go.
I have read in some places that you can't set up an already existent column as IDENTITY, but in Enterprise Manager, you can do it. My question is, how EM can do this? Because I need to do this in a lot of databases, and I have to do this by SQL.

Kind Reggards
Dirceu

View 4 Replies View Related

Calculations And Formats In SRS

Nov 2, 2007



Hi--

I am relatively new to SQL Reporting Services and have a couple basic questions (I think they are basic).

First, lets say that I have the following fields in the body of my report: Sales, Cost, Profit, and Percent Profit. Percent Profit is a calculated field of profit/sales.

Then, I have a grouping by customer, with a footer with the sum of sales, sum of cost, sum of profit. And I also want percent profit, but I do not want a sum or average of percent profit. Instead I want percent profit calculated as sum of profit/sum of sales. I cannot seem to get this to work with a calculated field. I must have the syntax wrong (as I was just taking sum(fields!profit.value)/sum(fields!sales.value)*100.

Also, I understand the format of #,# can be used to print a blank instead of zero. But this format also suppresses any decimals. So if I have a field that needs two decimals when there is a value, but need it to be blank when 0.00 is returned, what is the appropriate format?

Any help that can be provided would be much appreciated. As books are great, but sometimes do not explain everything. Thanks!
-Christina

View 8 Replies View Related

How To Do Calculations From This Table?

Aug 21, 2007

Hi All,

I'm a Geomechanics student and newcomer to SQL. I'm currently working on a project and using SQL as my data analysis tool. Below are some of the sample results from instrument reading. I would really like to have your assistance to create a summary from the data (table) provided below. Further info about summary table is also provided.
=========

create table dbo.t1 (

[PK] varchar (20),

[date] datetime,

[Location] varchar(20),

[Weigth] float,

[Readings] float,

[Days_inc] float

)

go

set nocount on

insert into dbo.t1 values( ' DAFN25S ' , ' 1/1/2004 12:00:00 AM ' , ' A-23 ' , ' 22.6 ' , ' 0 ' , '0' )

insert into dbo.t1 values( ' DANS37J ' , ' 1/2/2004 12:00:00 AM ' , ' A-23 ' , ' 27.3 ' , ' 0 ' , '0' )

insert into dbo.t1 values( ' FKFH34U ' , ' 1/3/2004 12:00:00 AM ' , ' A-23 ' , ' 29 ' , ' 0.0083 ' , '3' )

insert into dbo.t1 values( ' DRCY55O ' , ' 1/16/2004 12:00:00 AM ' , ' A-23 ' , ' 23.2 ' , ' 0 ' , '0' )

insert into dbo.t1 values( ' DRKR13T ' , ' 1/17/2004 12:00:00 AM ' , ' A-23 ' , ' 31.9 ' , ' 0 ' , '0' )

insert into dbo.t1 values( ' TRKR54Y ' , ' 1/18/2004 12:00:00 AM ' , ' A-23 ' , ' 29.6 ' , ' 0 ' , '0' )

insert into dbo.t1 values( ' RTYT24T ' , ' 1/19/2004 12:00:00 AM ' , ' A-23 ' , ' 23.8 ' , ' 0.0244 ' , '4' )

insert into dbo.t1 values( ' RKCB26T ' , ' 1/24/2004 12:00:00 AM ' , ' A-23 ' , ' 18.3 ' , ' -0.0061 ' , '1' )

insert into dbo.t1 values( ' DKRH51R ' , ' 1/28/2004 12:00:00 AM ' , ' A-23 ' , ' 10.8 ' , ' 0 ' , '0' )

insert into dbo.t1 values( ' DRGT77U ' , ' 1/29/2004 12:00:00 AM ' , ' A-23 ' , ' 25.3 ' , ' 0 ' , '0' )

insert into dbo.t1 values( ' USNT80T ' , ' 1/30/2004 12:00:00 AM ' , ' A-23 ' , ' 27.3 ' , ' 0 ' , '0' )

insert into dbo.t1 values( ' TDNF71Q ' , ' 1/31/2004 12:00:00 AM ' , ' A-23 ' , ' 26.9 ' , ' 0.0014 ' , '4' )

insert into dbo.t1 values( ' DKAH23Y ' , ' 1/2/2004 12:00:00 AM ' , ' B-60 ' , ' 15.4 ' , ' 0 ' , '0' )

insert into dbo.t1 values( ' DAKD44T ' , ' 1/3/2004 12:00:00 AM ' , ' B-60 ' , ' 21.7 ' , ' 0.0229 ' , '2' )

insert into dbo.t1 values( ' PDNR56Y ' , ' 1/4/2004 12:00:00 AM ' , ' B-60 ' , ' 19.2 ' , ' 0 ' , '0' )

insert into dbo.t1 values( ' DBTG87K ' , ' 1/5/2004 12:00:00 AM ' , ' B-60 ' , ' 17.6 ' , ' 0 ' , '0' )

insert into dbo.t1 values( ' KDHR43I ' , ' 1/6/2004 12:00:00 AM ' , ' B-60 ' , ' 19.3 ' , ' 0 ' , '0' )

insert into dbo.t1 values( ' QJKD00F ' , ' 1/7/2004 12:00:00 AM ' , ' B-60 ' , ' 20.5 ' , ' 0 ' , '0' )

insert into dbo.t1 values( ' KKKR83I ' , ' 1/8/2004 12:00:00 AM ' , ' B-60 ' , ' 18.3 ' , ' 0 ' , '0' )

insert into dbo.t1 values( ' KDDR90R ' , ' 1/9/2004 12:00:00 AM ' , ' B-60 ' , ' 20.5 ' , ' 0 ' , '0' )

insert into dbo.t1 values( ' TTRC87R ' , ' 1/10/2004 12:00:00 AM ' , ' B-60 ' , ' 19.3 ' , ' 0 ' , '0' )

insert into dbo.t1 values( ' RDTD67E ' , ' 1/11/2004 12:00:00 AM ' , ' B-60 ' , ' 19.3 ' , ' 0 ' , '0' )

insert into dbo.t1 values( ' ZDHR44I ' , ' 1/31/2004 12:00:00 AM ' , ' B-60 ' , ' 24.6 ' , ' 0.0428 ' , '9' )

insert into dbo.t1 values( ' SRHT23T ' , ' 2/1/2004 12:00:00 AM ' , ' B-60 ' , ' 29 ' , ' 0.0413 ' , '1' )





set nocount off

go
=============
Summary table:










PK
Location
Sum Weight
reading var.

FKFH34U
A-23
78.9
0.0083

RTYT24T
A-23
108.5
0.0161

RKCB26T
A-23
18.3
-0.0305

TDNF71Q
A-23
90.3
0.0075

DAKD44T
B-60
37.1
0.0229

ZDHR44I
B-60
178.6
0.0199

SRHT23T
B-60
29
-0.0015
Note:
1.'Sum weight' is the accumulation of weight between two readings. The weight of the previous reading is not included in the calculation. Example: the 'sum weight' of location A-23 between the reading on 1/24/2004 to 1/31/2004 is (10.8+25.3+27.3+26.9)= 90.3
2.'Reading var.' is the substraction value of the most recent reading value to the previous date reading values. Example: the 'reading var' for location A-23, between the latest reading on 01/31/2004 and previous reading on 01/24/2004 is 0.0075. If there is no value on previous reading, then the latest date reading is substracted by zero (0). The substraction can only be done on the records of similar reading location (A-23 or B-60, etc).
3. 'Location' is obtained from the most recent date of reading, which is used for the calculation.
4. 'PK' is obtained similarly than procedure at point 3.

Thank you in advance.

WP

View 10 Replies View Related

Keep Track Changes Made In Data

Aug 23, 2006

I want to create a page(using GridView) where it will detect changes made in the database and display it for Administrator observation. I've created a table name history for this purpose.
History
guid (uniqueidentifier)
dateCreated(datetime)
lastDateUpdated(datetime)
changesMade(varchar(50))  ---- eg; dropdownlist
oldValue(varchar(50))  ----- compaq
newValue(varchar(50)) -----dell
updateBy(varchar(10)) <------ username of registered user
comNo(int) = foreign key for Computer table
History data will insert data whenever a changes made. Could anyone advise and direct me how to do this function. I was thinking of using stored procedure to insert the data. Thanks in advance.

View 17 Replies View Related

Table Variables And Calculations

May 4, 2007

 So, I've got a problem with using table variable "fields" and a simple variable in calculations. It ain't workin'. See the bolded code below. When I run the SP, it returns 0 for those values. Anyone got any clues? Is this a table variable limitation?  ALTER PROCEDURE YearlyTotalsInPercentages(@Year int) ASBEGINDECLARE @TotalSum intDECLARE @Totals TABLE
(
CBDCYearlyTotals int, ProductLine varchar(50))INSERT INTO @Totals (CBDCYearlyTotals, ProductLine)SELECT SUM(dbo.Main.Hours), dbo.Project.ProductLineFROM dbo.Main INNER JOIN dbo.Department ON dbo.Main.DeptNo = dbo.Department.DeptNo INNER JOIN dbo.Project ON dbo.Main.ProjectNo = dbo.Project.ProjectNoWHERE dbo.Main.UserID LIKE 'CI%' AND dbo.Project.ControlLocation = 'IND' AND DATEPART(yyyy, dbo.Main.DataDate) = @Year AND dbo.Main.Active = 1GROUP BY dbo.Project.ProductLine SET @TotalSum = (SELECT SUM(dbo.Main.Hours)FROM dbo.Main INNER JOIN dbo.Department ON dbo.Main.DeptNo = dbo.Department.DeptNo INNER JOIN dbo.Project ON dbo.Main.ProjectNo = dbo.Project.ProjectNoWHERE dbo.Main.UserID LIKE 'CI%' AND dbo.Project.ControlLocation = 'IND' AND DATEPART(yyyy, dbo.Main.DataDate) = @Year AND dbo.Main.Active = 1) SELECT t.CBDCYearlyTotals AS CBDCYearlyTotals, t.ProductLine AS ProductLine, @TotalSum AS TotalSum, ROUND((t.CBDCYearlyTotals/@TotalSum) * 100, 1) AS Percentage FROM @Totals tEND
GO
  Thanks Yall

View 1 Replies View Related

Firing Already Made DTS Package -- From Vb.net

Apr 13, 2005

Someone please help me with this. 
I'm trying to fire off an already created DTS package.  This package is stored within SQL Server's -- underneith the Data Transformation Services / Local Packages section. 
HOW CAN I FIRE THIS OFF FROM A VB .NET APPLICATION
I'm familiar with strored procedures and using them in vb.net so if somone could lead me down that road I would be very much appriciated. 
Thanks in advance everyone,
RB

View 4 Replies View Related

Floating Point Calculations...

Nov 9, 2001

Hello all,

I can't see any reason for this error, not having a high level understanding of maths I thought I'd post it and hope someone could share some light on it.

I yesterday got called by a client who said that a payment for £15 + VAT was being passed to their payment gateway as 17.62 when it should be 17.63. The VAT calculation is performed in a SQL Server 2000 stored procedure. In the end I tracked it down and it wasn't a propblem with my calculation.

The price was coming out as 17.63 fine. The stored procedure then had to return this price in pence (17.63 * 100 = 17.63). When I put in a print statement with this calculation it was correct but when I output the variable that the result was assigned to it was coming out as 1762.

The variable that the result was being put into was of real datatype.

I then wrote a udf to test this. Here is the function:

CREATE FUNCTION dbo.POUNDS_TO_PENCE
(
@POUNDVALUE real
)
RETURNS INTEGER
AS
BEGIN

RETURN @POUNDVALUE * 100

END

As you can see nothing very special.

If you run this runction and pass in 17.63 it will return 1762!!!

The bit I don't get is if I change the @POUNDSVALUE intput variable to type float it returns the correct amount.

I've also found that the same problem occurs when passing in £30 + VAT (35.25) + 1pence. So, 35.26 comes out as 3525 instead of 3526. This is the case if you keep doubling the number (and adding a few pence here and there).

Does anyone know why this is or is it a bug in the processor?

The SQL books online say the following about the float and real data types:

--------------------------------------------------------
float and real (T-SQL)
Approximate number data types for use with floating point numeric data. Floating point data is approximate; not all values in the data type range can be precisely represented.

Syntax
float[(n)]
Is a floating point number data from - 1.79E + 308 through 1.79E + 308. n is the number of bits used to store the mantissa of the float number in scientific notation and thus dictates the precision and storage size. n must be a value from 1 through 53.


n is Precision Storage size
1-24 7 digits 4 bytes
25-53 15 digits 8 bytes


The Microsoft® SQL Server™ float[(n)] data type conforms to the SQL-92 standard for all values of n from 1 to 53. The synonym for double precision is float(53).

real
Floating point number data from –3.40E + 38 through 3.40E + 38. Storage size is 4 bytes. In SQL Server, the synonym for real is float(24).


--------------------------------------------------------

Apart from the fact that it says 'Approximate number data types' I can't see any difference between the data type apart from the ranges.

Anyone any ideas?
Thanks
Tom Holder

View 2 Replies View Related

Tracking Changes Made In The Database

Sep 12, 2000

Here is the situation. I have an employee who is making changes but I can't prove it. He thinks he knows more than he does and he's mess'n everything up. I would like to know if SQL or some third party product has the capability to see the change and log what was changed with the persons username attached?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved