Calculation Of Runtim Before Running Statement

Jul 5, 2007

Hallo,
for my project I need to calculate the estimated time an SQL statement will be running. The statements will be composed via a GUI and get become very complex. So the user needs a rough time approximation of how long to wait for the answer. A few seconds, ten minutes or several hours!?

My question:
Does anyone know good links, articles or other source to get familiar with this topic?
I'd also like to start a little discussion. On what does the runtime depend? (Is runtime the right word here?) Number of JOINs Number of different tables Size of the tables Complexity of the WHERE-Condition ("id=5" or rather "name LIKE '%foo%'") Indizes Hardware Number of users on the DBWhat else influences the time and how?



Aside from that I found the "SET SHOWPLAN_XML ON" for SQL Server 2005 to get the execution plan of the query.

Extract:
Code:


<StmtSimple StatementText="SELECT * FROM ContactPerson WHERE LocationID = '4';" StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSubTreeCost="0.0033205" StatementEstRows="17.027" StatementOptmLevel="TRIVIAL">
<StatementSetOptions QUOTED_IDENTIFIER="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" NUMERIC_ROUNDABORT="false" />
...
</StmtSimple>

But I can't find a reasonable listing of the elements and attributes and their meaning.
I think StatementSubTreeCost sound interesting. Is that the time, the statement needs to run or what does the number 0.0033205 say?

Is anyone familiar with the SHOWPLAN_XML? Or can anyone provide me a link about the values of this plan, perhaps in the Microsoft-MSDN area!?
The principle is clear. But what about the values in detail. There must be a documentation somewhere!

Thank you in advance,
Dominik

View 3 Replies


ADVERTISEMENT

Running Balance Calculation - PLEASE HELP

Jun 23, 2008

Hi guys, I've really been struggling with this issue for quite a while and the solution still elludes me.
If anyone can help me I will worship you as a god for ever! [Wink]

The Scenario is as follows:
I have a table where several documents are linked to each other via a foreign key called ReconNum.
Basically this table is used to link Invoices, Credit Notes and Payments to each other.
Thus you get the following layout:

ReconNum LineID DocID DocType ReconAmount
111 0 101 Payment 20 000
111 1 202 Credit Note 12 0000
111 2 303 Payment 5500
111 3 404 Invoice 10 000
111 4 505 Credit Note 22500
111 5 606 Invoice 30 000
111 6 607 Invoice 20 000

What I need to do is assign values from each of the Payment/Credit Note documents to the Invoices. Thus getting the following result:

Inv# DocID DocType AmountApplied DocBal InvBal
404 101 Payment 10 000 10 000 0
606 101 Payment 10 000 0 20 000
606 202 Credit Note 12 000 0 8 000
606 303 Payment 5500 0 2 500
606 505 Credit Note 2500 20 000 0
607 505 Credit Note 20 000 0 0

I've come close, but it seems to be the old chicken or the egg problem.i.e. I need one field to calculate the other and vica-versa.
(Amount Applied, DocBal and Inv Bal are all dependant on each other)

NOTE: The only field I'm really interested in is the AmountApplied field, but amount applied depends on the DocBal and InvBal fields.

I need a running balance on both the Amount Due on the Invoice, and the Amount Availble on the document being assigned to the invoices. The problem is that I'm trhowing the data into a temporary table and cant use fields in the temp table for my calculation.
i.e. AppliedSum = if (Doc.Available amount on Doc < Invoice Balance) then Doc.Available Amount
elseif (Doc.Available amount on Doc > Invoice Balance) then Invoice Balance

Invoice Balance = Invoice Tot - sum(Applied Amounts)

Any help would be breatly appreciated!

View 15 Replies View Related

SQL Server 2012 :: New Column Calculation Based On Running Difference

Aug 15, 2014

New column calculation

CREATE TABLE MAIN
(
ORDERNO VARCHAR(20),
LASTUPDATEDDATE DATE,
ORDERCLIENTINITIALFEE NUMERIC ,

[Code] .....

---OUTPUT
--=======

INSERT INTO MAIN VALUES ('1000', '1/1/2014',3000,1000,700,1500)
INSERT INTO MAIN VALUES ('1000', '3/5/2014',1000,2000,650,200)
INSERT INTO MAIN VALUES ('1000', '5/10/2014',500,5000,375,125)
INSERT INTO MAIN VALUES ('1000', '11/20/2014',100,2000,400,300)
INSERT INTO MAIN VALUES ('1000', '8/20/2014',100,3500,675,1300)

[Code] ....

View 2 Replies View Related

Calculation In Select Statement

Sep 6, 2006

I am trying to do a calculation where I show the [YesEmail] / [TotalCustomers]. It wont let me do it unless i do two queries. Is there a way to put this all in this select statment.


Code:



SELECT customer_lifetime_totals.occurrence_1_store_no as [StoreNo],
COUNT( case when customer.email_address IS NULL then 1 end) as [NoEmail],
COUNT( case when customer.email_address IS NOT NULL then 1 end) as [YesEmail],
COUNT(customer.customer_no) AS [TotalNewCustomers]


FROM customer_lifetime_totals INNER JOIN
customer ON customer_lifetime_totals.customer_id = customer.customer_id
WHERE (occurrence_1_transaction_date > CONVERT(DATETIME, '2006-08-01 00:00:00', 102))
GROUP BY customer_lifetime_totals.occurrence_1_store_no
ORDER BY customer_lifetime_totals.occurrence_1_store_no






This works ... but only when i put the previous query in a temp table and then run another query referring to it.

Code:


cast(100*(cast(YesEmail as float) / TotalNewCustomers) as numeric (5,2)) as [Percent]

View 1 Replies View Related

Help Needed With Select Statement Using Calculation

May 2, 2008

Hi,

Any help with this would be great. I have SQL tables all in the following format:

ID_PRODUCT,
ID_MARKET,
ID_BUCKET,
ID_COLLECTION,
ID_MEASURE,
MEASURE_VALUE

Now each table holds different measures e.g. 326, 229 & 278. I would like to subtract the measure_value of 229 from 278 and then add the measure_value from 278 from this. e.g

(326-229) + 278 = new measure_value.

This is for each id-product where the following 3 conditions meet:

the ID_PRODUCT, ID_MARKET AND ID_BUCKET match. Im lost, any help would be great.

thanks

Jay

p.s this what i have started with at the moment as a test however it dont work.

SELECT ID_PRODUCT,
ID_MARKET,
ID_BUCKET,
ID_COLLECTION,
ID_MEASURE,
MEASURE_VALUE
FROM (SELECT TOP 100 PERCENT dbo.DPOUT_EXCEPTIONS_326.ID_PRODUCT,
dbo.DPOUT_EXCEPTIONS_326.ID_MARKET,
dbo.DPOUT_EXCEPTIONS_326.ID_BUCKET,
dbo.DPOUT_EXCEPTIONS_326.ID_COLLECTION,
dbo.DPOUT_EXCEPTIONS_326.ID_MEASURE,
dbo.DPOUT_EXCEPTIONS_326.MEASURE_VALUE + DBO.DPOUT_LSBP_229.MEASURE_VALUE as 'MEASURE_VALUE'
FROM dbo.DPOUT_EXCEPTIONS_326 INNER JOIN dbo.dpout_lsbp_229 on
dbo.dpout_exceptions_326.id_product = dbo.dpout_lsbp_229.id_product
where dbo.DPOUT_EXCEPTIONS_326.ID_PRODUCT = dbo.dpout_lsbp_229.id_product
and dbo.DPOUT_EXCEPTIONS_326.ID_MARKET = DBO.DPOUT_LSBP_229.ID_MARKET
and dbo.DPOUT_EXCEPTIONS_326.ID_BUCKET = DBO.DPOUT_LSBP_229.ID_BUCKET)

View 3 Replies View Related

Analysis :: YTD Calculation For Multiple Hierarchies With SCOPE Statement

Jul 2, 2015

I am stuck in a situation where I want to use YTD for three different calendars of our company and don't want to create three different YTD calculations. However I want to make this work for any measure not for a particular measure

If I create one YTD and try to use in context of three calendars in SCOPE statements then it does not give my right results. Following is my syntax but It does not work.

SCOPE([Billing Date].[SalesCalendar].MEMBERS);
( [Aggregate].[AGGREGATE CODE].[YTD] )   
          = Aggregate({[Aggregate].[AGGREGATE CODE].DefaultMember}*{PERIODSTODATE([Billing Date].[SalesCalendar].[Sales Calendar Year],[Billing Date].[SalesCalendar].CURRENTMEMBER) } );

[Code] ....

However if I comment on one of SCOPE statement , other one works but both don't work simultaneously in context of different calendars.

View 2 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

Slow Running Insert Statement

Apr 6, 2007

I support a website (ASP.NET 2.0) where recently users have been unable to insert data due to timeout issues. The functionality executes a query that inserts a single row into a SQL Server 2005 db table. I tried running this query from the backend, and it took 4:48 to insert a single row! Interestingly, after that initial agony any similar inserts I tried took no time whatsoever. I have checked the execution plan, but unfortunately don't really know what I'm looking for with inserts, as most of my experience with execution plans is with select statements. Any resources anyone could point me to for troubleshooting this would be much appreciated.



Thanks,



-Dave

View 3 Replies View Related

Running A LIKE Statement When Searching For A Date Field...

Feb 14, 2006

I am trying to run a like statement that has a datetime column and for some reason it does not return any values. I looked in the SQL help files and in states in there that when trying to select using a datetime that the preferred way of doing it is using a like statment. Does anybody know a better way of doing this? Here is my example: (I have dates in this column ie 2006-02-13 11:30:54.220)

SELECT * FROM workorderhistory WHERE wheninstalled LIKE '%2006-02%'

View 7 Replies View Related

Running An UPDATE Statement Only If A Column Exists

Nov 1, 2007

I'm trying to write a script that would only update a column if it exists.

This is what I tried first:

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Enrollment' AND COLUMN_NAME = 'nosuchfield')
BEGIN
UPDATE dbo.Enrollment SET nosuchfield='666'
END

And got the following error:

Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'nosuchfield'.

I'm curious why MS-SQL would do syntax checking in this case. I've used this type of check with ALTER TABLE ADD COLUMN commands before and it worked perfectly fine.

The only way I can think of to get around this is with:

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Enrollment' AND COLUMN_NAME = 'nosuchfield')
BEGIN
declare @sql nvarchar(100)
SET @sql = N'UPDATE dbo.Enrollment SET nosuchfield=''666'''
execute sp_executesql @sql
END

which looks a bit awkward. Is there a better way to accomplish this?

View 3 Replies View Related

Error Running Insert Statement In A Database

Aug 15, 2007

When I run this query against PFGDSMRISSQLQ1 in Server manager 2005:use <database>INSERT INTO dbo.<table> (<column1>, <column2>, <column3>)VALUES (12598,2900,1.00)I get this error:Msg 8624, Level 16, State 1, Line 5Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.Can someone please help. Thanks!

View 1 Replies View Related

Transact SQL :: UPDATE Statement - Returns Correct Results When Running Separately

May 1, 2015

SQL Ver: 2008 (not r2)
Problem:  The following code returns correct results when moving variable declarations and update statement outside a stored procedure, but fails to return a value other than zero for the "COMPANY TOTAL" records.  The "DEPT TOTAL" result works fine both in and outside the sp.This may have to do with handling NULL values since I was getting warning message earlier involving a value being eliminated by an aggregate function involving a NULL.  I only got this message when running inside the sp, not when running standalone.  I wrapped the values inside the SUM functions with an ISNULL, and now return a zero rather than NULL for the "COMPANY TOTAL" records when running inside SP.All variable values are correct when running.

SQL CODE:
DECLARE 
     @WIPMonthCurrent date = (SELECT TOP 1 WIPMonth FROM budxcWIPMonths WHERE ActiveWIPPeriod = 'Y')
  select @WIPMonthCurrent as WIPMonthCurrent
  
[code]....

View 10 Replies View Related

TempDB Log File Running Out Of Free Space While Running DBCC CheckDB On Large Database

May 28, 2015

In my environment, there is maintenance plan configured on one of the server and while running DBCC checkdb on a database of size around 200GB, log file usage of tempdb is increasing and causing the maintenance job to fail.

What can I do to make the maintenance job run successfully, size of the tempdb database is only 50GB and recovery model is set to simple. It cannot be increased as the mount point on which it is residing is 50GB.

View 3 Replies View Related

How To Kill A Long Running Query Running On A Background Thread.

Sep 1, 2006


If I start a long running query running on a background thread is there a way to abort the query so that it does not continue running on SQL server?

The query would be running on SQL Server 2005 from a Windows form application using the Background worker component. So the query would have been started from the background workers DoWork event using ado.net. If the user clicks an abort button in the UI I would want the query to die so that it does not continue to use sql server resources.

Is there a way to do this?

Thanks


View 1 Replies View Related

Stored Proc Is Running Much Slower Than Running The Script Directly

Mar 14, 2008

One of my stored procs, taking one parameter, is running about 2+ minutes. But if I run the same script in the stored proc with the same parameter hardcoded, the query only runs in a couple of seconds. The execution plans are different as well. Any reason why this could happen? TIA.

View 6 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







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