I have a SQL Server hosted on a Win2003 server. I have a monitor on the server letting me know the average download speeds. When I do a large select from a remote connection, it looks like the download rate maxes out at around 10 kb/sec.
Is this an average download rate for a server hosting sql server?
I have attached some test data for you that has two temp tables "#worker" and "#worker_rate".
The issue is to find all workers who are sharing SAME SET of rate_codes.
I'm able to get the output as "workers sharing same rate_codes", but unfortunately I could not get the list of workers sharing same SET of rate_codes. Also definition of SAME SET is not defined.
I don't know what I'm missing.
I setup data collection on a production server to capture growth rates.
When I run the dis usage report, it shows a daily growth rate of over 500 megs. This seems excessive to me.
As a troubleshooting step I then ran sp_space used and got these results:
database_namedatabase_sizeunallocated space
rgc_prod 273442.63 MB3648.48 MB
reserved data index_size unused
265345488 KB164385384 KB99826072 KB1134032 KB
What should my next steps be to try and determine why there is so much growth? And isn't the index size rather large?
I am trying to build a query which will be used in an automated report to calculate failure rates of systems based on cases opened through support. Here is where I am stuck. Some systems may have multiple cases opened within the same span of another cases however we would consider this one failure:
System ACase12013-07-11 13:17:09.0002013-07-15 12:05:03.000
System ACase22013-07-12 16:27:50.0002013-07-12 16:29:12.000
System ACase32013-07-12 17:30:32.0002013-07-12 17:40:11.000
System ACase42013-07-12 19:00:24.0002013-07-12 19:04:14.000
System ACase52013-10-01 18:02:23.0002013-10-01 18:11:26.000
Lets say System A generated those 5 cases however Case 2,3 and 4 all happened within the same period as Case 1 so those 4 cases should count as one failure so my end result should be
System ACase12013-07-11 13:17:09.0002013-07-15 12:05:03.000
System ACase52013-10-01 18:02:23.0002013-10-01 18:11:26.000
And that system should show me 2 failures. I was thinking of using a temp table but not sure if that is possible as I am stumped on how to compare the dates to be able to validate if they fall within the range of an older case and whether or not to include them into the new Temp Table.
Calculation of an average using DAX' AVERAGE and AVERAGEX.This is the manual calculation in DW, using SQL.In the tabular project (we're i've noticed that these 4 %'s are in itself strange), in a 1st moment i've noticed that i would have to divide by 100 to get the same values as in the DW, so i've used AVERAGEX:
Avg_AMP:=AVERAGEX('Fct Sales';'Fct Sales'[_AMP]/100)
Avg_AMPdollar:=AVERAGEX('Fct Sales';'Fct Sales'[_AMPdollar]/100)
Avg_FMP:=AVERAGEX('Fct Sales';'Fct Sales'[_FMP]/100)
Avg_FMPdollar:=AVERAGEX('Fct Sales';'Fct Sales'[_FMPdollar]/100)
The results were, respectively: 701,68; 2120,60...; -669,441; and finally **-694,74** for Avg_FMPdollar.i can't understand the difference to SQL calculation, since calculations are similar to the other ones. After that i've tried:
test:=SUM([_FMPdollar])/countrows('Fct Sales') AND the value was EQUAL to SQL: -672,17
test2:=AVERAGE('Fct Sales'[_Frontend Margin Percent ACY]), and here, without dividing by 100 in the end, -696,74...
So, AVERAGE and AVERAGEX have a diferent behaviour from the SUM divided by COUNTROWS, and even more strange, test2 doesn't need the division by 100 to be similar to AVERAGEX result.
I even calculated the number of blanks and number of zeros on each column, could it be a difference on the denominator (so, a division by a diferente number of rows), but they are equal on each row.
I have a temp_max column and a temp_min column with data for every day for 60 years. I want the average temp for jan of yr1 through yr60, averaged...
I.E. the avg temp for Jan of yr1 is 20 and the avg temp for Jan of yr2 is 30, then the overall average is 25.
The complexity lies within calculating a daily average by month, THEN a yearly average by month, in one statement.
?confused?
Here's the original query.
accept platformId CHAR format a6 prompt 'Enter Platform Id (capital letters in ''): '
SELECT name, country_cd from weather_station where platformId=&&platformId;
SELECT to_char(datetime,'MM') as MO, max(temp_max) as max_T, round(avg((temp_max+temp_min)/2),2) as avg_T, min(temp_min) as min_temTp, count(unique(to_char(datetime, 'yyyy'))) as TOTAL_YEARS
FROM daily
WHERE platformId=&&platformId and platformId = platformId and platformId = platformId and datetime=datetime and datetime=datetime
GROUP BY to_char(datetime,'MM')
ORDER BY to_char(datetime,'MM');
with a result of:
NAME_________________CO
-------------------- --
OFFUTT AFB___________US
MO______MAX_T _____AVG_T__MIN_TEMTP_TOTAL_YEARS
-- ---------- ---------- ---------- -----------
01_________21______-5.31________-30__________60
02_________26______-2.19______-28.3__________61
03_______31.1_______3.61______-26.1__________60
04_______35.6______11.07______-12.2__________60
05_______37.2_______17.2_______-3.3__________60
06_______41.1______22.44__________5__________60
07_______43.3______24.92________7.2__________60
08_______40.6______23.71________5.6__________60
09_________40______18.84_______-2.2__________59
10_______34.4_______12.5_______-8.9__________59
11_________29_______4.13______-23.9__________60
12_________21______-2.52______-28.3__________60
Hi,Is there an easy way to determine the rate of change for table? I realizethat I could do periodic select count(*) from XXX, but that would onlydetermine the size of the table. It would ignore updates.What I'd really like to do would be able to determine the number of non-readoperations over a table so that I can determine the rate of change for thattable.Thanks for any help..
View 1 Replies View RelatedHi All,
I would like to print out a dataflow's tarnsfer rate (row/sec) to the output window and refresh it in each 5. second. Could anybody help me?
Thanks in advance.
m
Hi,
I have this specific problem, that has been causing me alot of headaches for the last couple of days, and I can't seem to fiure it out on my own.
Basically i am working on a many to one currency conversion setup, where I have a fact table with exchange rates for periods and for all input currencies. The reporting currency is USD. Here is a demonstration of the data in it:
Valid Date Source Currency Rate
2008-01-01 GBP 2.0563
2008-01-01 EUR 1.4634
2008-04-01 GBP 1.9968
2008-04-01 EUR 1.5197
Normal period by period conversion is easy enough, but I want to convert the measures at the latest known rate, based on the period selected. Lets say I run an analysis without slicing on time, then it want it to convert at the last entered rate of all time, and if the user wants to run the analysis as of March 08, then it should convert all data to the rate as of January, since this is the latest one known.
I have tried to set the measure to a semi-additive behaviour, with "lastnonempty", but the problem with that is the conversion over time. Here is an example:
I want to show a measure summed "all to date" as of June 08. What I want is it to convert everything at the rates entered in April 08, but what I get is that it convert everything up until Jan 08 and the rates entered in January, and thereafter it starts to use the rated entered in April.
Period Amount LCY Currency Code Exchange Rate
Dec 07 100 GBP 2.0563 (Wrong, should be 1.9968)
Dec 07 100 EUR 1.4634 (Wrong, should be 1.5197)
Jan 08 100 GBP 2.0563 (Wrong, should be 1.9968)
Jan 08 100 EUR 1.4634 (Wrong, should be 1.5197)
Feb 08 100 GBP 1.9968
Feb 08 100 EUR 1.5197
Mar 08 100 GBP 1.9968
Mar 08 100 EUR 1.5197
Apr 08 100 GBP 1.9968
Apr 08 100 EUR 1.5197
May 08 100 GBP 1.9968
May 08 100 EUR 1.5197
Jun 08 100 GBP 1.9968
Jun 08 100 EUR 1.5197
How do I create a measure containing the latest known rates based on time selection?
Thanks in advance
I have a task to provide users with a list of currency rates in the following format:
Base Currency
Hedged Currency
Base to Hedge Rate
Hedged to Base Rate
E.g.
"Base Currency"..."Hedged Currency"..."Base to Hedge Rate"..."Hedged to Base Rate"
GBP....................USD.......................1.70.........................0.59
GBP....................EUR.......................1.25..........................0.80
(the dots are for the visual only)
The table I have been provided in SQL has the following structure:
Currency Code
Currency Rate (Rate against the Base Currency)
Base Currency (BIT to recognise which currency is the base one as it can vary from site to site. Only once currency can be Base per a given site)
I have started to implement, but ended up with cursor and temporary table, where I insert the data.
I have this query to select the highest currency rate from a currency table:
SELECT
Target_currency AS [Currency]
,rate_exchange AS [Rate]
,date_L AS [Date]
FROM
rates
WHERE
target_currency IN ('USD','GBP','JPY')
ORDER BY
target_currency desc
,date_L desc
My output is something like this:
CurrencyRateDate
USD 1,13572015-06-20 00:00:00.000
USD 1,37952014-03-31 00:00:00.000
USD 1,28352014-03-24 00:00:00.000
USD 1,28522013-04-05 00:00:00.000
JPY142,37552014-03-31 00:00:00.000
JPY1202014-03-24 00:00:00.000
JPY119,32013-04-05 00:00:00.000
GBP 0,82872014-03-31 00:00:00.000
GBP 0,08482014-03-24 00:00:00.000
GBP 0,084392013-04-05 00:00:00.000
How can I get the top 3 of the most recent rates from every rate in the WHERE filtering?
So:
USD 1,13572015-06-20 00:00:00.000
JPY142,37552014-03-31 00:00:00.000
GBP 0,82872014-03-31 00:00:00.000
I have tried many solutions, but I can't get it to work.
I would like a single SQL to return all employee's total billablecompensation for a year. Their billable rates change throughout the year sounder the employee table (one), there is a compensation table (to many)which has the employee id, effective date, billable hourly rate. So in agiven year calendar year they could have many different (though usually 2 atmost) rates. These rates then have to correspond to and e multiplied bytheir corresponding billable hours from the time sheet table.I know I could create a series of UNIONs and hard code the effective dates,i.e.select from time sheets where employee=john and timesheet.task_date betweenjan 1 and jun 1, compensation.billable rate * timesheet.billable hoursUNIONselect from time sheets where employee=john timesheet.task_date between jun1 and dec 31 compensation.billable_rate * timesheet.billable_hoursI'd have to do that for every employee in a very large SQL.Is there an easier way using straight SQL? If not could it be done with astored procedure?Thanks for any insight.
View 2 Replies View RelatedOk so I created a SSIS package to get currency exchange rates from web service - http://www.webservicex.net/CurrencyConvertor.asmx . Everything works fine I can run it successfully - i uploaded to SSIS package store - runs successfully./
BUT when I put it in a job - it FREAKING fails every *dem time.
In addition to run it the JOB as SSIS package from store - also straight from the File System = still same freagging error - and SQL 2005 has gotten more stupid than SQL 2000 & DTS - they provide no clue as to y you get an error
Help please?
I'm trying to find a way to import data from this data xml feed to get daily exchange rate. I' tried:
select *
from
openrowset(bulk 'http://www.bankofcanada.ca/stats/assets/xml/fx-noon.xml',single_blob) as x
Which is a feeble attempt at a start; however, am getting this error message:
Cannot bulk load because the file "http://www.bankofcanada.ca/stats/assets/xml/fx-noon.xml" could not be opened. Operating system error code 123(The filename, directory name, or volume label syntax is incorrect.).
How to parse this file using SQL.
I have created a spreadsheet using powerquery and powerpivot. Loading the data on powerquery from different spreadsheets was a charm, the problem is calculating the exchange rates per month.The structure is as following:I have 3 tables, they are:
Dates– Just a dates (loaded directly on PowerPivot).
Invoice – Details of invoices for all customers, gross totals are in different rates (loaded through power query)
Customer – Detailed information of customers (loaded directly on PowerPivot).
Currency – exchange rates (loaded directly on PowerPivot).
Note: I mentioned where they were loaded because I do not know if that really matters, I guess it does not... but extra information will not hurt. Now, let's go to how these tables are connected between each other and their key columns.The Invoice table has information on invoices dates, invoice gross total and customer code. This table is related to Customer table, which has the actual name of customers and other details. This link is done through the Customer Code column. The Currency table have a relationship with the Customer Table through the Country Column.
What I want to achieve is a formula to do the exchange rate based on the Invoice Date column present on the Invoice Table. So I have created a column on the Currency Table named at each month of the year and populated it with the exchange rates for all countries (countries listed using ISO3 format).My problem is while I am able to select a column from the Currency table to do the exchange rate calculation using the below formula, I fail to realize how to do it per month:=CALCULATE(sum(invoice[Gross Value])*SUM(Currency[Reverse]))*Reverse is the column with the exchange rate for the country money to USD.What I am failing to do is how to make that calculation to also consider the month of the invoice and pull the exchange rate for the same month from the Currency table
Get the invoice date then find the column on Currency[Month] and multiply, providing me the USD amount.I was unable to figure out a solution using the filters parameters from Calculate. If some of you ask why I am using SUM inside Calculate, is because if I only use SUM and try to do the math between the two tables I get an error saying that it was not possible to determine the value on Currency[Reverse]. Same happens with SUMX. It worked out with Calculate, but then I cannot create other formulas using CALCULATE on that table because it will give that error on redundancy, unless I add a filter on it, which worked last time (but I do not remember which thing /parameter used to filter, so do not ask it right now.I do not really need that level of accuracy, but my Virgo perfectionist side demands me to find a solution to that, that will not use a single exchange rate, but consider which is the correct exchange rate for the month of the invoice and calculate it correctly for all invoices. Just so you know, the Invoices tables has more than 1million lines.
ok so i have this table:
Code:
Products_Sold
priceSold - money
itemsSold - int
An example of 4 rows on my table would be like this
$1400 80
$1500 85
$1560 82
$1700 81
to calculate the average of the price sold related to the number of sold items just have to do
Select avg(priceSold*itemsSold)
But sometimes i just want the average price of the first 100 sold items, so how can i make my query to just use the first 100 sold items?
in math it would be like this
average= ( (1400*80) + (1500*20) ) / 100
but if i wanted the first 200 it would be like this
average= ( (1400*80) + (1500*85) + (1560*35)) / 200
and if i wanted the first 300 would be like this
average= ( (1400*80) + (1500*85) + (1560*82) + (1700*53)) / 300
but of course the number i want will always be a variable which is less than the total of the products sold. So, how the heck do i program this query where the number of the items sold is variable and it will take the rows of the database depending on how many items were sold.
I hope i didnt wrote my explanation too confusing and that i can get any help from you guys. thank you a lot for the help and byye
Hello people,
Do you know how can do for downloading a file stored in a database?. I'm using a table with a FILE field for storing the file.
I know i have to create a special aspx page for downloading, that receives parameters to locate the proper record in the table and then retrieve the file in memory to start downloading.
I have done this with file located at specific folders but not a database's field.
Another thing... the file may be big.
Dou you have any idea about retrieving from sql and sending the file back to the final user?
I really appreciate your support.
Larry.
The application can decrypt stored procedure ,views ,trigger ,defined function. Download at http://www.qwerks.com/Product.asp?ProductID=4867
View 1 Replies View RelatedI am developing an application that needs to connect to a MS SQL 7 database.
Is there anywhere I can download a developer or trial version of MS SQL 7 for such a purpose?
I've looked everywhere and can't find anything online!
Dear All,
is there any free download of sql server 2008 download avialable in the net?
i've googled a lot.....it is taking to the download of sql server 2008 CTP.....even after downloading finished, i'm getting error while installing error...
the error is kernel32.dll
please provide me links
Vinod
Even you learn 1%, Learn it with 100% confidence.
Where can I download SQL Server Express SP2?
This page still refers to SP1:
http://msdn.microsoft.com/vstudio/express/sql/download/
Thanks.
Hi!
I am confused ...downloaded Microsoft SQL server 2005 (for reporting services) to my Windows 2002 (32-bit systems), but it asks me to install the service packs as well...
So Windows XP Service Pack 2 is already installed.
And I need to download Windows server 2000 or 2003 R2, but where could I find a free trial version?
Do I also need Asp.net and IIS?
I would be very grateful for some help... to clarify which components needed.
Greetings...
Can someone please point me to where i can download the MS SQL Server 2000 API SDK? Am having the dangdest time finding it at the obvious places...
Thanks!
gmone
We are needing to apply the Sql Server 2000 Service Pack 1 to a computer that will replace the current server. The SP 1 listed in the technet sql server 2000 service packs page, is unavailable on the download center.
Where can I download the SP 1?
PS. We can't apply a superior patch, since we can't afford any risks related to the upgrade during the limited time window allowed for the migration.
Thks,
Freddy Ríos.
From where can i download the SQL server 2000 s/w ?
(Personal edition or Evaluation edition)
thankx
Hello!
Can't download Sql Server 2005 Express,althouth i downloaded without any problem all other express products: c#, c++, visual studio from the same page.
The download even start ! I click on the download button and nothing happen.
Does somebody have an idea ?
Thanx
info: i use mozilla as browser on XP pro, because since i upgraded IE6 to IE7, IE is KO, even after uninstal IE7 and re-instal IE6.
Is there anywhere on MSDN I can still download the SQL Server 2000 Enterprise edition - I heard that Microsoft will stop supporting SQL Server 2000 from April 2008 - it's still January
View 3 Replies View RelatedI just upgraded my Sql Server to 2008 and I was hoping that the link found in this page would provide the download to SSMSE for Sql Server 2008, but it redirects me to 2005 version. I don't any instances running with Sql Server 2005 that I could access through SSMSE 2005, so I can't use it. Does anyone have the link for download to SSMSE 2008? Or should I downgrade? And if, how can I downgrade?
Thank you very much, sorry by my misspellings.
I am trying to calculate cumulative GPA which is currently it is being done via a cursor and i was looking to change it to set based if possible.
The cumulative GPA is basically the average marks of all courses in a term and all previous terms. Where I am having an issue is if the course was taken in a previous term and the mark was lower, then the lower mark in not factored in to the GPA. If a course was taken in a previous term and the mark was higher then the marks for the current term are not factored into the GPA.
In the example below, math is taken in all three terms and the current term's mark is included in the GPA but not the previous terms because current terms mark is higher. Two classes (bio,chem) are taken in third term but not included due to marks being higher in second term.
CREATE TABLE [gpa]([term] int,
[course] varchar(10),
[mark] float);
INSERT INTO [gpa]
VALUES (1,'math',3),
(1,'eng',4),
[Code] .....
Hi,
I was wondering if I could download the Enterprise Manager for SQL Server. I am aware of some 3rd party tools but I want to use what comes w/ SQL Server.
I would like to design a content-management feature on a website, in particularbe able to upload a binary file and to store it into a MS Sql Server DB.be able to download it afterwards. The sizes of the files are very limited (less than 100Kb) so I do not need any special feature to handle the upload/download.Does anyone know how to achieve such feature?Thanks in advance,Joannes
View 5 Replies View RelatedDoes anyone hava the URL?
View 2 Replies View Relateddear friends please provide me some free downloadable books for sql server DBA's.
i tried i google. but not got any book to download.