Excluded Data In A Calculation

Nov 17, 2007

In the following query, when an es.evstrname DOES have data in os.activity in (5,7), then the query returns the expected results. However, if there was no out of service break, then the query will return results, but there will not be any data returned for the last column of the select statement. This
makes sense to me but I was hoping there was some way that I could have data returned. For instance, is there a way for me to make the query read
the - (os.actualdeparttime - os.actualarrivetime) as zero so the column would still consider the first part of the equation? Or is there a way to write a subquery that would work around this?

Select s.ldate,
es.evstrname as 'Run',
d.lastname+', '+d.firstname as 'Driver',
po.actualarrivetime as 'PullOut',
os.actualarrivetime as 'OOS',
os.actualdeparttime as 'IS',
pi.actualdeparttime as 'PullIn',
os.actualdeparttime - os.actualarrivetime as 'Break'
(pi.actualdeparttime - po.actualarrivetime) - (os.actualdeparttime - os.actualarrivetime) as 'PayTime'

From Schedules S

Join eventstrings es
On s.schid=es.schid

Join employees d
On d.employeeid=es.employeeid

Join events po
on po.evstrid=es.evstrid
and po.schid=es.schid
and po.activity=4

Join events pi
on pi.evstrid=es.evstrid
and pi.schid=es.schid
and pi.activity=3

Left Outer Join events OS
on os.evstrid=es.evstrid
and os.schid=es.schid
and os.activity in (5,7)


Where es.evstrname>=?
AND es.evstrname<=?
AND s.ldate>=?
AND s.ldate<=?

Order by s.ldate, es.evstrname, po.actualarrivetime, os.actualarrivetime, os.actualdeparttime, pi.actualdeparttime

All es.evstrname will always have an 'event activity' (pi.activity, po.activity etc) of 3 and 4. Only es.evstrname that have clocked
out of service will have data with os.activity in (5,7) (this is an out of service break).

I'm not sure if this is a format that would help, but here is some sample data. Currently, it returns the following:

Date Run Pullout OOS IS PullIn Break PayTime
10-1 101 10:00 12:00 12:30 16:00 :30 5:30
10-1 102 11:00 ---- ---- 17:00 ---- -----

I would like it to return this:

Date Run PI OOS IS PullIn Break PayTime
10-1 101 700 900 930 945 30 215
10-1 102 700 --- --- 945 -- 215

Thanks! Craig

View 2 Replies


ADVERTISEMENT

Join Query-capture Excluded Data

Sep 26, 2007

I have 2 tables, table one with 772 pieces of compliant data. Table 2 has 435 pieces of data that meet another criteria (all the columns are identical it was just passes through an additional filter). I need to capture the values that are excluded from table 2.

Example Table 1
ID some value
1 x
2 x
3 x
4 x
5 x

Table 2
ID some value
2 x
3 x
5 x

I need to capture the data from ID 1 and 4 and assign a new value to it, it is extra compliant data. Thanks!

View 2 Replies View Related

Bring Back Excluded Item

Jul 20, 2005

Hi all,I have been wrestling with this problem all morning with no success sofar where I have a need to bring back an excluded field.Basically I have a list of order numbers. Each order number can havemany order types attached one of which is a ‘P’ type. Most order typeshave an account number attached in its own field however when a ‘P’ typeis selected the account number is not brought back.Is there someway I can get this brought back for each P type or do Ihave to do some fancy insert in a data warehouse to get this done (i.e.insert account numbers into all P types)?Many thanksSam*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

Excluded A Record From The Select Statment

Feb 20, 2008

Hi I have a table where I am searching for the values between certain numbers. But then I want to exclude a record to be part of the search

so how I could do that.

This is what I am trying to do.

Select * from mytable Where Value Between 10000 and 200000 --this gives me the result

But then I want to exclude a record from the select statment.
For example this is what I really want to do

Select * from mytable Where Value Between 10000 and 20000 and where value != '100o'

It is not a typo '100o' and this not let me test the select statment so I want to exclude that particular record.
The type was in the table and I want to exclude that record so I can get my statment running.

View 5 Replies View Related

Analysis :: SSAS Calculation With Division Combined With A Time Calculation?

Sep 17, 2015

I have created calcalated measures in a SQL Server 2012 SSAS multi dimensional model by creating empty measures in the cube and use scope statements to fill the calculation.

(so I can use measure security on calculations

as explained here  )

SCOPE [Measures].[C];

THIS = IIF([B]=0,0,[Measures].[A]/[Measures].[B]);

View 2 Replies View Related

Converting Oracle Calculation To Sql Server 2005 Calculation

Jul 19, 2007

Hi I am having to convert some oracle reports to Reporting Services. Where I am having difficulty is with the

calculations.

Oracle

TO_DATE(TO_CHAR(Visit Date+Visit Time/24/60/60,'DD-Mon-YYYY HH24:MISS'),'DD-Mon-YYYY HH24:MISS')



this is a sfar as I have got with the sql version

SQLSERVER2005

= DateAdd("s",Fields!VISIT_DATE.Value,Fields!VISIT_TIME.Value246060 )



visit_date is date datatype visit_time is number datatype. have removed : from MI(here)SS as was showing as smiley.



using:

VS 2005 BI Tools

SQLServer 2005



View 5 Replies View Related

Optimizing Data Calculation Inside SP

Oct 1, 2007

Hi,
I created a SP which is supposed to calculate some values for me and return them as a resultset. I have a RequestTime field, and a ResponseTime field; This sp should calculate how much time does it take for us to respond to a customer's request. if it is more than a specific time, this sp should calculate the extra time and its fine base on a constant fine-per-extra-minute value.
It should also calculate total fine for all records.
To do so, I wrote it as this:


Code:

CREATE PROCEDURE up_responsetime
@sdate smalldatetime,
@edate smalldatetime,
@TotalFine int OUTPUT
AS
DECLARE @Fine_Per_Min int
DECLARE @Max_ResponseTime int
SET @Fine_Per_Min = 3
SET @Max_ResponseTime = 120

SELECT ID,RequestDate,RequestTime,ResponseDate,ResponseTime,
-- Calculate exceeded amount of time for each record
DATEDIFF(minute,RequestDate+RequestTime,ResponseDate+ResponseTime) - @Max_ResponseTime as ExtraTime,
-- Calculate fine for each record
(DATEDIFF(minute,RequestDate+RequestTime,ResponseDate+ResponseTime) - @Max_ResponseTime) * @Fine_Per_Min as Fine
FROM CusRequests
WHERE RequestDate BETWEEN @sdate AND @edate
--Calculate sum of all fines and return it in TotalFine variable.
SELECT @TotalFine = SUM((DATEDIFF(minute,RequestDate+RequestTime,ResponseDate+ResponseTime) - @Max_ResponseTime) * @Fine_Per_Min) FROM CusRequests
GO



but I have two concerns about this:
1- Calculated Fine field returns a negative figure if the ResponseTime is in the desired period of time. But I want it to return zero for such cases, and return only a positive figure when extra time was spent on responding to the request.

2- As you can see, there are many redundant calculation in this code, and this will affect its performance. I wanna know if there is any more optimized way to write such a code?

I appreciate any help on this.
Thanks alot

p.s. sorry if the post was lengthy.

View 2 Replies View Related

YTD Calculation When Using SSAS As Data Source

Jun 14, 2007

I have a cube that tracks sales by sales rep. In this cube, I have dimensions for SalesRep, Product, and Region hierarchies. I also have a Time dimension that provides Fiscal Year, Fiscal Quarter, Fiscal Month, and Calendar Date; the Time dimension also has an attribute showing the first day of the year for any given date (our fiscal year starts on a different date every year).



I have a report that passes StartDate and EndDate parameters back to the cube, and provides sales numbers by Rep, Product, and Region for that given date range. What I would also like is a field that provides YTD sales through the EndDate parameter.



This is a piece of cake for me to implement against the SQL tables, but I am pulling my hair out trying to determine the best way to implement with a cube. Does anyone have any suggestions?



TIA



- Steve

View 1 Replies View Related

Doing A Calculation Using Data From Two Different Source Tables

May 6, 2008



I use the following report expression for my SSRS report.


=SUM(Fields!CBNonAccr.Value) / SUM(Fields!TotNonAccr.Value)*ReportItems!HE__Non_Accruals.Value


When I try to preview the report , I get a error massege saying "report item expression can only refer to other report items within the same group grouping scope.

Fields!CBNonAccr.Value and Fields!TotNonAccr.Value come from one table. But HE_Non_Accruals.Value comes from another table which does not have a relationship with the first one. Its a seperate table.
So I'm wondering how do I do this now?

Thanks

View 1 Replies View Related

Power Pivot :: Using Data From Slicers In DAX Calculation

May 13, 2015

I have a formula that should use data from 2 slicers:

Spend per period (changing currencies & dates):=[Spend per period]*CALCULATE([Sum of Value],FILTER(Currency,Currency[Date]=[End Date]),FILTER(Currency,Currency[Attribute]=CurrencySlicer[Attribute]))

I managed to link the [End Date] from the slicer to the formula, however the [Attribute] field is not numeric so I can't duplicate the same methodology. 

{FYI:    End Date:=LASTDATE('Finish Date Slicer'[Column1])    }

I assume that I need to build a formula to extract the data chosen in the slicer, and can't connect it directly to the slicer. 

View 2 Replies View Related

Data Mining :: Calculation Not Working Trigger

Apr 20, 2015

In My Table , I have Three Column like Qty,Price and Total, I create a Trigger for this . 

Table:
CREATE TABLE [dbo].[tbl_SO](
[SoNo] [varchar](50) NULL,
[Qty] [int] NULL,
[price] [numeric](18, 2) NULL,
[total] [numeric](18, 2) NULL
) ON [PRIMARY]

[code]...

Input :
1) UPDATE TBL_SO SET QTY='10',Price='100' Where SONo='10'
2)UPDATE TBL_SO SET QTY='10',Price='100'  Total=Qty*Price Where SONo='10'

Output:
What I get? I tried both 1 and 2, It(Query) does not work on first time,I executed (Query) second time It works.Why?What I need?   How to work on First Time?

View 6 Replies View Related

SQL Server 2012 :: Day Wise And Date Range Calculation With Looping Or Dynamic Data?

May 14, 2015

I am using Sql Server 2012.

This is how I calculate the ratio of failures in an order:

31 Days Table 1 query
sum(CASE
WHEN (datediff(dd,serDATE,'2015-01-21')) >= 31 THEN 31
WHEN (datediff(dd,serDATE,'2015-01-21')) < 0 THEN 0
ELSE (datediff(dd,serDATE,'2015-01-21'))END) as 31days1 .

How do i loop and pass dates dynamically in the Datediff?

31 Failures Table 2 query
SUM(Case when sometable.FAILUREDATE BETWEEN dateadd(DAY,-31,CONVERT(DATETIME, '2015-01-21 23:59:00.0', 102))
AND CONVERT(DATETIME, '2015-01-21 23:59:00.0', 102)Then 1 Else 0 END) As Failures31,31 Day Cal(Formula) combining both Table 1 and Table 2
((365*(Convert(decimal (8,1),T2.Failures31)/T1.31day))) [31dayCal]This works fine when done for a specific order.

I want a similar kind of calculation done for day wise and month wise.

2. what approach should I be using to achieve day wise and month wise calculation?

I do also have a table called Calender with the list of dates that i can use.

View 3 Replies View Related

Where To Do The Calculation In VB.NET Or In SQL?

May 4, 2008



Hi,

My predicament is - where do I do these calculations - in my vb.net code or in an SQL stored procedure?

My manager has handed me a task of converting an excel file she uses in to a web aplication.

While it has been easy to devise what should be the screens and how to capture data, I am struggling over how to code the calculations.


The calculations in excel are pretty simple. These are just sequential calculations (about a 150 calculation for average 500 rows). Mathametical operations include sum, average, max min - regular excel stuff. Some calculations involve vlookup (equvalent to calculation based on value derived from a reference table).


So I am stil wondering - where do I do these calculations - in my vb.net code or in an SQL stored procedure?


Since these calculations are required a produce a result in an online environment, what will be faster?


I tried to do a proof of concept by creating a sample calculation in a .NET class and an in a stored procedure. The choice is still not clear. SQL code execution time was not bad. But SQL code tended to be very messy.VB.net code seemed to be a little slow. But seemed a more organised to look at.


Any views that you can offer will be very helpful.

Thanks in advance.

PMA

View 6 Replies View Related

Help W/ Calculation

Aug 3, 2007

I need to calculate the overall GPA for a student in a particular class.


YEAR SCHOOL STUDENT IDENT GRADE TEACHER CLASS GPA
2007 Snow Canyon High Student1 321649 10 Teacher1 Earth Systems 0.0000
2007 Snow Canyon High Student1 321649 10 Teacher1 Earth Systems 1.6700
2007 Snow Canyon High Student1 321649 10 Teacher1 Earth Systems 3.3300
2007 Snow Canyon High Student1 321649 10 Teacher1 Earth Systems 3.6700
2007 Snow Canyon High Student1 321649 10 Teacher2 Elementary Algebra 0.0000
2007 Snow Canyon High Student1 321649 10 Teacher2 Elementary Algebra 0.6700
2007 Snow Canyon High Student1 321649 10 Teacher2 Elementary Algebra 1.0000


The problem I'm having is that a student may not taken the class for four terms (as in the Elementary Algebra example above). So I can't hard code it to sum the gpa and divide by 4; it needs to be the number of terms the student took the class.


Here's my sql:


select
trnscrpt.schyear as [Year],
school.schname as School,
rtrim(stugrp_active.lastname) + ', ' + rtrim(stugrp_active.firstname) as Student,
trnscrpt.suniq as suniq,
stugrp_active.graden as Grade,
trnscrpt.teachname as Teacher,
trnscrpt.descript as Class,
gpamarks.gpavallvl0 AS GPA

from
dbo.trnscrpt
inner join dbo.stugrp_active on trnscrpt.suniq = stugrp_active.suniq INNER JOIN
school ON stugrp_active.schoolc = school.schoolc INNER JOIN
gpamarks ON trnscrpt.marksetc1 = gpamarks.marksetc AND trnscrpt.markawd1 = gpamarks.mark

where
trnscrpt.graden >= 6 and
trnscrpt.markawd1 not in ('NC','NG','P','W','WA','WF','WI','WP') and
trnscrpt.subjectc in ('LA', 'MA', 'CP', 'CB') and
trnscrpt.schyear = 2007 and
stugrp_active.schoolc = 725

order by
school.schname,
student,
grade,
class

View 3 Replies View Related

MDX Calculation Help

May 28, 2008

Dimensions:

DimPeriod/Year-Quarter-Month
DimProduct/Category-Product
Fact:
FactInventory/PeriodKey-ProductKey-StatusCode


Period = 1
Product = 1
Status Code = InStock


Period = 1
Product = 2
Status Code = InStock



Period = 2
Product = 1
Status Code = OutOfStock

Period = 2
Product = 2
Status Code = InStock


In period = 2, status code change from InStock to OutOfStock: Product 1 (Count=1)
In period = 1, number of products with status code = InStock: product 1 and product 2 (Count=2)


The measure = 1 / 2 or 50%. TIA

View 2 Replies View Related

Calculation

Oct 9, 2007



This is a smple data in table1

sector RefDate price
pharm 22 august 2007 100.21
gap 15 august 2007 10.32
pharm 21 august 2007 99.99
pharm 9 oct 2007 100.99
pharm 2 oct 2007 98.34
pharm 8 oct 2007 96.34
...

I would like to have the result as follows:
sector RefDate price priceChangeSinceYesterday priceChangeSinceLastWeek priceChangeSinceLastMonth
pharm 9 oct 2007 100.99 100.99-96.34 100.99-98.34 100.99-lastmonth's price value

select
sector,
RefDate,
price,
priceChangeSinceYesterday??,
priceChangeSinceLastWeek???,
priceChangeSinceLastMonth??
from
table1

thanks

View 3 Replies View Related

Calculation

Apr 2, 2008



My aim is to do something like what I have explained below and I was planning on building this logic at the Database level only rather than on the frontend code.

There are certain allocations(transactions) that happen on a periodic basis and I am storing these transactions in the PurchaseTranMaster and PurchaseTranDetail table. These transactions are categorized as 'Main' type and the amount could be allocated for one or many categories in a single transaction. Below is how it will be saved in the 2 table


PurchaseTranMaster


TranID TranDate TranType
1 14-March-2008 Main
2 17-March-2008 Main
3 1 9-March-2008 Main


PurchaseTranDetail



TranID Amount Category Debit_TranId
1 1000 A
1 1000 B
2 2000 B
3 300 A
3 400 C


Now what happens is users of my application can make purchases under all these categories only until the Balance under these categories is > than purchase amount. The Balance is calculated as sum of all transactions. It means that w.r.t the above data the balances for each category is(this is not stored in the database)

A 1300
B 3000
C 400

So lets say a user does make a purchase(Trantype is 'SUB') of 300 under A and 400 under B in a single transaction. The data would then be stored in the tables as


PurchaseTranMaster


TranID TranDate TranType
1 14-March-2008 Main
2 17-March-2008 Main
3 19-March-2008 Main
4 20-March-2008 SUB


PurchaseTranDetail


TranID Amount Category Debit_TranId
1 1000 A
1 1000 B
2 2000 B
3 300 A
3 400 C
4 300 A 1
4 400 B 1


In the PurchaseTranDetail the Debit_TranId value means that the amount has been marked against the TranID 1. This TranId is not handpicked by the user and the system should allocate it accordingly based on the amount available for a particualar category for a Main Transaction. It means that before TranId 4 was saved in the database then the system would first check whether the Total available balance for A >=300 and B>=400 (in our example above it is 1300 and 3000 resp)
Then if the Balance is > than the puchase amount then the allocation would be done by the system and this would be done against the TranID whose TranDate was the earliest, so thats why the Debit_TranId column has 1 as TranId 1 was the earliest.so logically now the balance for the categories would be (this is not saved in the database)

A 1000
B 2600
C 400

So next time again when a user would make a purchase(transaction) under A for 800 and under B for 1000 then if the balance is greater than the purchase amount(which in this case it is) the allocation would happen according to the earliest TranId and this time amount would be partly marked against TranId 1 , TranID 2 and TranID 3. The data would look like this


PurchaseTranMaster


TranID TranDate TranType
1 14-March-2008 Main
2 17-March-2008 Main
3 19-March-2008 Main
4 20-March-2008 SUB
5 21-March-2008 SUB



TranID Amount Category Debit_TranId
1 1000 A
1 1000 B
2 2000 B
3 300 A
3 400 C
4 300 A 1
4 400 B 1
5 700 A 1
5 100 A 3
5 600 B 1
5 400 B 2


I need to do the above taking into consideration that there could be multiple users making purchases(concurrency).
Also I was building my logic on doing the above whether to use cursors or loops. I just need to know how do I write my stored procedure and what would be the most efficeint way of doing the above.

The design for creating the above sample tables is below

/*CREATE TABLE PurchaseTranMaster
(
TranID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
TranDate Datetime,
TranType varchar(30)
)


CREATE TABLE PurchaseTranDetail
(
TranID int,
Amount int,
Category Varchar(20),
Debit_TranId int
)

insert into PurchaseTranMaster values(convert(datetime,' 14-March-2008',103),'Main')
insert into PurchaseTranMaster values(convert(datetime,' 17-March-2008',103),'Main')
insert into PurchaseTranMaster values(convert(datetime,' 19-March-2008',103),'Main')
insert into PurchaseTranMaster values(convert(datetime,' 20-March-2008',103),'SUB')
insert into PurchaseTranMaster values(convert(datetime,' 21-March-2008',103),'SUB')


insert into PurchaseTranDetail values(1,1000,'A',0)
insert into PurchaseTranDetail values(1,1000,'B',0)
insert into PurchaseTranDetail values(2,2000,'B',0)
insert into PurchaseTranDetail values(3,300,'A',0)
insert into PurchaseTranDetail values(3,400,'C',0)
insert into PurchaseTranDetail values(4,300,'A',1)
insert into PurchaseTranDetail values(4,400,'B',1)
insert into PurchaseTranDetail values(5,700,'A',1)
insert into PurchaseTranDetail values(5,100,'A',3)
insert into PurchaseTranDetail values(5,600,'B',1)
insert into PurchaseTranDetail values(5,400,'B',2)*/

View 5 Replies View Related

YTD Calculation

Feb 19, 2008

I´m working on SSRS 2005 trying to calculate YTD for the total sale. This is what I got so far:





Code Snippet

WITH MEMBER [Measures].[YTD Amount] AS
'SUM(PeriodsToDate([DATE].[Year]),[Amount])'
SELECT
{[Measures].[Amount],[Measures].[YTD Amount]} ON COLUMNS,
[DATE].[Month].Members ON ROWS
FROM [SKY]
My Date hierarcy is:
Year
Month

When running this query all I get is very small number for Amount column, almost zero for all months and the YTD Amount Column is only showing (null).

View 11 Replies View Related

Query With A Calculation

Mar 1, 2007

Hello Friends
I have 3 tables
1) Product Id, ShortName
2) IncomingStockId, ProductId, Quantity, InDate
3) OutGoingStock Id, OutDate, ProductId, Quantity
I need to get the results like thisProduct name, quantity in stock today
the "quantity in stock today" = sum (quantity recieved) -sum (quantity sent)
Thank you for your timeSara
 

View 1 Replies View Related

Calculation Of Values

Mar 3, 2008

Hi.
i have the code :
cmd = New SqlCommand("SELECT sales,country,year FROM salesTable WHERE (country = " & (CountryBlk) & " AND branch = " & (NameSnif) & " AND datepart(yyyy,year)=" & (YearBlk) & ") order by datepart(mm,year) ", cnn)        cnn.Open()        rdr = cmd.ExecuteReader( _        CommandBehavior.CloseConnection)      
  While x < 12             If rdr.Read = Nothing Then                Exit While            End If                         varcount(x) = rdr("sales")
            TempMonth = rdr("year")            MonthNumber(x)=datepart(DateInterval.Month,TempMonth)            x = x + 1        End While
 
i want to calculate and put into the varcount(x) value all sales of the same month
 
thanks

View 6 Replies View Related

Date Calculation

Oct 12, 2004

I need to create a user defined function to calculation the difference between today and a future date. The result needs to be in days, hours, and minutes formatted as per the following example: 1d / 4h / 30m. I have a moderate level of SQL exprience. however, I would appreciate some expert advice as the best way to approach this.

View 4 Replies View Related

Date Calculation

Mar 14, 2001

hello everyone,
I have a problem of calculating a date, for example, how do i find out the begining date of the week and ending date of the week for certain date,
and how do i find out the beginning date of the month and end date of the month for a certain date,
thanks

View 1 Replies View Related

Date Calculation

Nov 12, 1999

Hi All!
I need a query to find all dates from today to one-year back.
If I start from today day I need find all dates until 11/12/98.
Thanks a lot.
Greg.

View 1 Replies View Related

Bandwidth Calculation

Aug 18, 2003

I have scenerio that find out the Bandwitdh size between clint and server.
I wanted find out howmuch size of data recieveing from server at a time.
Any advice regarding this.
Thanks,
Ravi

View 3 Replies View Related

Age Calculation For Less Than 1 Year?

Oct 7, 2003

HI,
I have the below logic for age calcuation for more than a year.
But I need age calculation for lessthan year.

Note for MAK: As per our previous post that day calucations didn't work.


DECLARE @birthday datetime, @d datetime
SELECT @birthday = '12/31/1998', @d = '12/30/1999'

SELECT datediff(yy, @birthday, @d) -
(case WHEN (datepart(m, @birthday) > datepart(m, @d)) OR
(datepart(m, @birthday) = datepart(m, @d) AND
datepart(d, @birthday) > datepart(d, @d))
THEN 1
ELSE 0
end) AS Age1

View 6 Replies View Related

SQl Date Calculation

Jan 10, 2006

I need to use the first day of previous month, can anybody help me with that please?

Thanks in advance!!

View 6 Replies View Related

How To Do Calculation Of Dates?

Oct 5, 2004

HI,
getdate() gives me today's date. I have a sql query which returns me a date. I want to see if the difference between today's date and the date returned by sql query is 12 months(1 year) or less. If yes I want to print it.
(If the difference is more than 12 months or 1 year i don't want to consider that record.)

Example:
how to do calculate:
10/05/2004-10/3/2003

View 3 Replies View Related

Query Calculation

Sep 27, 2006

how do i calculate something like this if I have the table with names and count?
Name Count Percent
Name1 27 4.69%
Name2 2 0.35%
....
Totals 576 100.00%

View 3 Replies View Related

SQL Calculation Problem

Dec 9, 2004

:eek:

I am performing a calculation in both SQL and MSACCESS. ACCESS is correct and SQL is not. It is off by -.05 Here is the calculation:

Sum((ch.FaceAmount)-(((ch.FaceAmount*Ex.CAN_Exchange_Rate+0.005)*100)/100)) AS TransAmount,


Both FaceAmount and CAN_Exchange_Rate are defined as money in a SQL table. The ACCESS front-end attaches this table, so it uses the same columns in the calculation.

Now, I know SQL looks at money with 4 decimal places. The attached SQL tables in ACCESS looks at these columns as currency which I believe is 2 decimal places. I think this is where the descrepency is. But it still doesn't explain why SQL is calculating the incorrect total.


Does anyone have any ideas?
Gail

View 3 Replies View Related

Percentile Calculation

Oct 11, 2005

Hi All,

I have around 1000 employee records containing employee number and their salary. How do i calculate percentile on these records?

For example
How to calculate 25th Percentile on Salary of these 1000 records?

Hope I am clear with my question.

Thanks in advance.

Regards,
qA

View 7 Replies View Related

Calculation Problem

Apr 21, 2004

In the following calculation I am receiving my output as 000000.9000. I need it to look like 00000000.90. I cant seem to loose the 2 zeros at the end. Can anyone help me with this? Keep in mind that my output has to be varchar.

right('00000000000' + cast((st.rate_dlr * .01) * 10 as varchar),11)

View 3 Replies View Related

Date Calculation

May 20, 2008

I want all dates from DATEADD(DAY,-90,GETDATE()).

1/1/2008
1/2/2008
1/3/2008 .....

and so on. This is probably easy, but I can't figure it out. Any help would be appreciated. Thanks.

View 11 Replies View Related

Daily Calculation Need Help

Nov 14, 2007

Hi: I am having a problem with the Syntax. I am trying to get the daily value for a contract_nbr. The selection below shows that I am selecting by Case statement when day = 2 (2nd Day) and the month is a parameter provider by the user. For this particular example, I am using 2 (February).
Thanks for the help !!!!


[code]
Set @Req_Month = '2'

SELECT Distinct a.contract_nbr,
Case when Day(c.Beg_eff_date) = 2 and month(c.Beg_eff_date)= @Req_Month
Then c.rcpt_nom_vol-c.rcpt_fuel-rcpt_act_vol
Else 0
End As Day_2
from TIES_Gathering.dbo.contract a
Inner Join TIES_Gathering.dbo.NOm b on a.contract_nbr = b.contract_nbr
Inner Join TIES_Gathering.dbo.Nom_vol_detail c on c.Nom_id = b.Nom_id
where (a.contract_sub_type = 'INT') and (a.Contract_type_code ='GTH')
and (DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) < a.current_expirtn_date)
and (c.rcpt_dlvry_ind ='R')
Group by a.contract_nbr
[code]

View 6 Replies View Related







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