I need to be able to get the maximum value of different calculations.
Example :
i have 5 calculations like this :
(sum(Fields!CountTest1.Value)/sum(Fields!TestCount.Value))*100
(sum(Fields!CountTest2.Value)/sum(Fields!TestCount.Value))*100
(sum(Fields!CountTest3.Value)/sum(Fields!TestCount.Value))*100
(sum(Fields!CountTest4.Value)/sum(Fields!TestCount.Value))*100
(sum(Fields!CountTest5.Value)/sum(Fields!TestCount.Value))*100
these calculations give me a percentage from a test value against the total test values.
what function or expression can i use to get the max value of all the calculations ?
I was looking at the "choose" function but i'm not quite sure how...
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)
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.
The other table stores the priority order of the charge codes
TABLE3
CHGCODE PRIORITY DESCRPTN ACF 1 Court fee ALT 2 Late fee ANS 3 NSF fee ARC 4 Rent ADR 5 Repair AUR 6 Utility
While the forth stores the customer data:
TABLE4
NAMEID RMPROPID FIRSTNAME LASTNAME NAMEGROUP 000001234 A0A01 Jane Doe 000001234 000001235 A0A00 John White 000001235 000001236 A0A02 John Smith 000001236 000001237 A0A02 Jennifer Smith 000001236
This table's importance comes by the inclusion of the NAMEGROUP. This way if an account has multiple NAMEIDs, it can be kept straight by their shared NAMEGROUP.
I am trying to create a report using queries that will:
A) calculate the sum of the OPENAMT per NAMEGROUP per DISTINCT CHGCODE B) count the number of records (DISTINCT CHGCODEs) per DISTINCT NAMEID in ORDER by the CHGCODE PRIORITY Then C) calculate a case query whereas:
CASE WHERE TABLE1.TRANAMT=> the calculated sum of the highest priority CHGCODE THEN 'TABLE1.TRANAMT' ELSE WHERE TABLE1.TRANAMT <= the calculated sum of the highest priority CHGCODE THEN 'the calculated sum of the highest priority CHGCODE' ...then... CASE WHERE
I have a production server log shipping to a secondary server every 30 minutes (both SQL 2000), which the second server is used for both a warm standby server and for reporting from users. Issue: the log shipping locks the DB so reporting can't be done until the load is finished, the load to the second set of databases has taken up to 15 minutes to finish allowing the users only 15 minutes to run reports, this is not acceptable. The server also needs to be used for DR.
I am looking for another solution, I can't use Transactional Log shipping as not all of the tables in the databases have a primary key identified. So, I am looking for a real-time or near real-time reporting server that is more available to running reports and a warm standby server for Disaster recovery. I am trying to figure out what SQL Server 2000 has to provide (or even 2005 or 2008?) or I am also looking at some third party software, but not sure what is the best for a reasonable price.
I have a dilemn: On one side, I have a column C1 which could be a primary key because it is never null, the value is unique and identify the record. The problem is its a char type and its lenght can be close to 30. Then, I've planned to add another column C2 of int type as PK. But then I need to add a unique constraint index on C1. Does it improve performance anyway?
I have a database containing a lot of users and these users can have four different kind of telephone numbers connected to them: "Direct phone", "Switchboard", "Cell phone", "Home phone". The phone numbers are stored in a separate table. Some users have 0 phone numbers, some have 1, some have 3 etc.
Now I have to transfer the data to another database with a strict table structure and here the table that contains the user also should contain the users phone number and an alternative phone number, if the user currently has more than one phone number connected.
This means that if for instance we have three or more phone numbers connected to one user, we can maximum transfer two of them. This is not a big issue though...
We have ranked the importance of the phone numbers in the order as I presented them above. What I do in my T-SQL query is to do a ISNULL() and see if the user has "Direct phone" connected, if not I check for the next type and so on.
Now to my problem! Can anyone give me a suggestion of how to write the code for the extraction of the Alternative phone? What I need to do is to check if there is a "Direct phone" connected to the user, if so I should NOT chose that but the next phone number that I find.
I'm currently developing an ASP.NET website which is using SQL Server 2005 and I couldn't decide between two table designs and I hope you can give me your opinions
The website is for a school and it'll be used to create tests from questions. The teacher will: 1. Select grade (could be multiple selection) 2. Select class 3. Select subject The thing is that same question could be used for multiple grades.
Example query: "Get me questions of trigonometry of math from grades 7,8,9"
(Names used instead of ID's to make it more clear) The first design:
[BigRelationsTable] ID - QuestionID - GradeID - ClassID - SubjectID 1 - Question123 - Grade7 - Math - Trig 2 - Question123 - Grade8 - Math - Trig 3 - Question123 - Grade9 - Math - Trig
This is a simple design but all of the columns will need indexes because all of them will be used for searching and that makes me think about table performance.
Second design: [GradeClassRelations] ID - Grade - Class 1 - 7 - Math 2 - 8 - Math 3 - 9 - Math
I have 4 columns and 3 rows. Columns are Name, Age, Gender and Weight. I have values entered for each column. I need to pick the highest value of weight if Name, Age, Gender are same and put that into new table. how can i do that?
I want to implement the following query using SSIS Data flow Source component :
SELECT * FROM someTable WHERE someColumn = 'H'
How do I restrict the data coming from my data source? By that I mean how do I apply the WHERE clause in SSIS?! Should I use a conditional Split component?! but that would mean retrieving all records first then adding the split component (not the most efficient method surely).
I have created an SSIS package which pushes data from SQL Server (2005) to Oracle (Version 9.2.0.7.0), but having some issues with setting up the connection objects to Oracle.
I have attempted the connection with both the OLE DB Destination connection in SSIS and also with an ODBC connection, but both come up with the same error "ORA-12154: TNS could not resolve service name".
I am using a 32-bit version of SQL Server 2005 and the Oracle DB is 64-bit. Any ideas?
There's a field with Varchar(5) type which contains both alpha and alpha-numeric data. When sorting based on this field 1001 comes before 101. Is there anyway I can sort it appropriately; is there any collation or something which could be used.
I want to choose a column dynamically depending on a session variable. In other words, each customer has a different price level. so I need to choose the column that corresponds to thier price level in thier customer record Any help is appreciated. below is my futile attempt
SelectCommand="SELECT cove.CATALOG.ITEM_NO,cove.catalog."& <%#= session("pl") %> & ", cove.CATALOG.DESCRIPTION, cove.CATALOG.DESCRIPTION2, cove.CATALOG.DESCRIPTION3, cove.PRODLINE.DESCRIPTION AS Expr1, cove.CATALOG.GRP, cove.COMPANY.PRL, cove.COMPANY.COMPANY FROM cove.CATALOG INNER JOIN cove.COMPANY ON cove.CATALOG.WEB_STATUS <>'[I]' cove.COMPANY.I LEFT OUTER JOIN cove.PRODLINE ON cove.CATALOG.PRODLINE = cove.PRODLINE.CODE WHERE (cove.COMPANY.COMPANY = @company) AND (cove.CATALOG.GRP <> '[ACC]')"> <SelectParameters>
I want this...: SELECT [DateEntered], [From], [To], [Company], [Catagory], [Client], [Description], [TotalHours] FROM [JcpowersJobs] WHERE ([From] > 5/20/2008 12:00:00 AM) AND ([To] < 5/21/2008 11:59:59 PM) Obviously this won't work, but can anyone tell me what WOULD work?
I have to write a large data migration script to move data from one SQL Server database to another. Is there any way to dynamically specify the server and database name? I would like to do something like the following, (but this does not seem to work):
Delete From [@ServerName].[@ImportToDatabase].[MyTable]
I'm helping a small company built a new website and intranet in asp.net 2.0 and SQL.
I'm having trouble choosing among vs.net 2005, vwd express, SQL 2005 express or SQL 2005 Developers Edition for the prototype/testing phase.
Any advice? I want to build production-ready versions with easy migrations to production, but I don't want them to have to spend too much $$$ while it's just in alpha and being prototyped.
I have a table with its ID field set as an Identity column with "seed" and "increment" set to 1. Now, I have rows in there that I don't want to change. I'd like the next addtitions to the table to start at a specific value for their ID columns.
Basically, how can I specify a new starting value for an identity column?
I have visual studio 2005 and I am going to create a web application. Which data source or data provider will I choose when I'm connecting to the database(MS SQL Server)? and how about when I create a windows application? will I use the same thing? tnx
Hello all I am trying to create a stored procedure that will either give the user a chance to use the wild cards or the IR_Number, or subjLastName (from the RevisedSubjects_Table) or a begin Date and an End Date.
I am up against a condition where my control flow should be divided into threads and depending on value of a variable i have to choose 1 of the threads.
Hello all, I got a problem when using Sql Service 2005. I have two parameters @ID and @Name. I want to choose either and only one parameter to generate my report at one time. Could anyone tell me how to do it? I tried the "or" tab after filter tab, but it only generate the report with both @ID and @name values I gave. Thanks in advance.
I have an ActiveX Script Task in SQL Server 2000. It chooses one of two possible success-paths depending on if a file exist or not. (Part of the old ActiveX Script for choosing next step is below)
I need to rewrite this for a Script Task in SQL Server 2005 but it seems like it doesn't have this functionality and objects.
Does anyone know how to write the code for choosing the next step in a Script Task or knows another way to solve my problem?
The package is not suppose to fail if the file is missing, it's must succeed and that's why I need two success-paths.
Regards, Sara
'********************************************************************** ' Visual Basic ActiveX Script '************************************************************************ Function Main ()
Dim pkg Dim stpContinuePkg Dim stpExit
SET pkg = DTSGlobalVariables.Parent
SET stpContinuePkg = pkg.Steps("DTSStep_DTSActiveScriptTask_4") SET stpExit = pkg.Steps("DTSStep_DTSActiveScriptTask_21")
If ...... Then Main = DTSStepScriptResult_ExecuteTask stpContinuePkg.DisableStep = False stpExit.DisableStep = True Else ....... End If
How to choose the right RAID level for a database server based on disk performance counters? Lets say I have the collected performance counters data for Disk Transfers/sec, Disk Writes/sec and Disk Reads/sec.
Or is there any other best method to decide the RAID level?
We have two tables (one has DTS import of external spreadsheet - the other is the online table) The scenario is that I need to add items (from the first table) to the second table, when they don't exist. However, it's harder than that - the second table, in some fields has integers (ID field)), referring to another table (let's call it the 3rd table), but the first table has text (which is in the description, in the third table). I have to run through the first table, row by row, checking the part number if it already exists in the second table - nothing happens, and it goes to the next line. In each line, if the part number does NOT exist, I need to insert the data in that row. BUT - remember that third table, for the item(s) in the first table with text, I need to dynamically query the third table, to get the ID, in order to complete the insert correctly (Select ID from Third Table where Description=@FirstTableFieldText) I've never done anything like this, and I have no idea how to put this together - especially if it's all in one Stored Procedure. I need some help here on recommended ways to do it (and how)..... Even the base structure (pseudo code- whatever) would help
I've got a page with a SQLDataSource control successfully populating a Dropdownlist... However, now I find I need extended functionality - so I've got another page with links to this page, using querystrings - I need to hit the page, check the querystring, and if it's blank, just continue, but if the querystring is populated, then choose that item. I've tried the page_load and the page_prerender, but so far, it's not working:CC = Request.QueryString("center")If CC <> "" Then' DropDownList1.Items.FindByText(CC).Selected = TrueDropDownList1.SelectedIndex = DropDownList1.Items.IndexOf(DropDownList1.Items.FindByText(CC))End if I've double checked, and yes, every time CC is populated, that item is definitely in the dropdownlist - it just doesn't get changed to that item. ideas?
Hello everyone I'm really new to Data Presentation Controls and I already hate them couse I think they are way too complicated. Becouse of that i have already met wall many times. My last one sounds like this. I'm triing to put a default value from, lets say SqlDataSource1 in a DropDownList that is created by SqlDataSource2. I know how to get value from SqlDataSource1 but I don't know how to use exactly that value as my default value in DropDownList.
Two more question. 1. Is it possible to have two different select statements in a SqlDataSource? 2. A SqlDataSource may contain an insert statement, select statement, update and delete. If I bind this SqlDataSource to a control how can I specify which of those statements will be used? Thanks in advance
I would like to understand the algorithm that the linear regression method uses to choose the regressors in the model from a list of possible regressors.
I think that it is different from the common methods used in statistics like stepwise, forward or backward.
Hi there - I have a longish bit of MDX which enable some MTD reporting. I'd like to power up the report so that users can select to report by WTD, QTD, YTD by clicking hyperlinks / buttons / whatever [I'm quite new to reporting services - not quite sure what's possible.]
I could create 4 separate reports with 4 separate datasets, or one report with 4 datasets, or is there some reasonable way I can tweak the MDX for a dataset on the fly, e.g. "search and replace, find MTD replace with QTD" &c &c.
Cheers,
JG
WITH
MEMBER [MEASURES].[WT TO MTD]
AS SUM(MTD([Time].[Year - Quarter - Month - Date].CURRENTMEMBER)
,[Measures].[TURNOVER])
MEMBER [MEASURES].[WT TO MTD PrEq]
AS SUM(MTD(parallelperiod([Time].[Year - Quarter - Month - Date].[Month] ,1
I have 100k + records in table usr_table and I want to select all of them that do not have the same ID as the ID's indicated in table Usr_Type_Data Select * From usr_table As t1 Join Company_Sales.dbo.Usr_Type_Data As t2 ON t2.user_id = t1.user_id Where CustomerTypeId <> 7 I get 0 returned. If I change it to: Select * From usr_table As t1 Join Company_Sales.dbo.Usr_Type_Data As t2 ON t2.user_id = t1.user_id Where CustomerTypeId = 7 I get all records from table Usr_Data_Type returned. Do I need to specify a special Join type? Or perhaps change the T-SQL around?
