Choose Max Value Of Multiple Calculations
Aug 8, 2007
Hi,
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...
anybody ? help ....
Vinnie
View 1 Replies
ADVERTISEMENT
Mar 5, 2007
Hi,
I think that I want to want to build a temporary table in a stored procedure that handles multiple calculations.
I'll try and explain and hopefully you can tell me what i want
First i want to populate a list of all dealerstaff.
Code:
Select * From tblDealerstaff
I Then want to calculate the percentage tasks completed. So i find the number of available awards.
Code:
SELECT TOP (100) PERCENT COUNT(NoPerStaff) AS NoAwardsAvailable
FROM dbo.tblIndivAwards
Then i can find the number awards recieved per person.
Code:
SELECT TOP (100) PERCENT COUNT(dbo.tblIndivAwarded.AwardID) AS AwardCount
FROM dbo.tblIndivAwarded INNER JOIN dbo.tblIndivAwards ON dbo.tblIndivAwarded.IndivAwardID = dbo.tblIndivAwards.IndivAwardID
WHERE (dbo.tblIndivAwarded.staffID = @StaffID)
Trying to get my recordset to look like this >>
Fname + Lname + ((iNoAwarded / iNoAvailableAwards) * 100)
I want to know if it's possible to piece all of this calculation into a single stored procedure. I need to do this sort of thing all the time, as i display overviews of the projects i run to the senior managers.
I always do the maths in the ASP, and consequently i can't sort on the calulated totals.
Is this possible, or am i asking too much ?
Thanks in advance, as any help will be greatly recieved.
Regards,
Chris Brown
View 2 Replies
View Related
Apr 20, 2014
I have 4 tables involved here. The priority table is TABLE1:
NAMEID TRANDATE TRANAMT RMPROPID TOTBAL
000001235 04/14/2014 335 A0A00 605
000001234 04/14/2014 243 A0A01 243
000001236 04/14/2014 425 A0A02 500
TRANAMT being the amount paid & TOTBAL being the balance due per the NAMEID & RMPROPID specified.
The other table includes a breakdown of the total balance, in a manner of speaking, by charge code (thru a SUM(OPENAMT) query of DISTINCT CHGCODE
TABLE2
NAMEID TRANDATE TRANAMT RMPROPID CHGCODE OPENAMT
000001234 04/01/2014 400 A0A01 ARC 0
000001234 04/05/2014 -142 A0A01 ARC 228
000001234 04/10/2014 15 A0A01 ALT 15
000001235 04/01/2014 400 A0A00 ARC 400
000001235 04/05/2014 50 A0A00 ALT 50
000001235 04/10/2014 105 A0A00 ACF 105
000001235 04/11/2014 50 A0A00 ADR 50
000001236 04/01/2014 500 A0A02 ARC 500
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
[Code] ....
The results should be something like:
NAMEID TRANDATE TRANAMT RMPROPID CHGCODE APPLAMT
000001235 04/14/2014 335 A0A00 ACF 105
000001235 04/14/2014 335 A0A00 ALT 15
000001235 04/14/2014 335 A0A00 ARC 215
000001234 04/14/2014 243 A0A01 ALT 15
000001234 04/14/2014 243 A0A01 ARC 228
000001236 04/14/2014 425 A0A02 ARC 425
Also with a remaining balance (per CHGCODE) column.I can't figure out how to word the queries.
View 1 Replies
View Related
Oct 24, 2007
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.
Any help is appreciated.
Thanks....JB
View 8 Replies
View Related
Feb 16, 2005
Hi All,
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?
Thanks
View 14 Replies
View Related
Jul 6, 2006
good day, everyone
if i have a transaction table with fields below :
transaction_no, product_id, product_desc, product_qty, product_txn, transaction_date
can some expert here point out to me , which is the best cluster-index and non-clsuter index ?
and possible kindly please explain why is it so? i'm not good in database so just explain like to beginner
thank you very much for guidance
View 3 Replies
View Related
May 6, 2008
(Hard to put a good subject on this one...)
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.
View 7 Replies
View Related
May 14, 2008
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
[ClassSubjectRelations]
ID - GradeClassRelationsID - SubjectID
1 - GradeClassRelations1 - Trig
2 - GradeClassRelations2 - Trig
3 - GradeClassRelations3 - Trig
[SubjectQuestionRelations]
ID - ClassSubjectRelationsID - QuestionID
1 - ClassSubjectRelations1 - 1
2 - ClassSubjectRelations2 - 1
3 - ClassSubjectRelations3 - 1
This one is more normalised but this time the need of doing multiple joins makes me wonder.
What do you think? Which one should I use? Or if you have any other suggestions I'm all ears
View 2 Replies
View Related
Dec 18, 2006
hi guys,
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?
View 6 Replies
View Related
Jun 7, 2006
Greetings SSIS friends
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).
Any suggestions would be much appreciated.
View 6 Replies
View Related
Mar 18, 2008
Hi there,
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?
View 7 Replies
View Related
Jan 21, 2008
Hi there,
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.
Result of the sort:
1001
101
102
1022
103
Thanks,
LOA
View 7 Replies
View Related
Mar 3, 2008
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>
View 3 Replies
View Related
May 22, 2008
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?
View 8 Replies
View Related
Jun 21, 2004
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]
Any help appreciated
View 2 Replies
View Related
Mar 22, 2006
Hi,
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.
thanks,
Bruce
View 1 Replies
View Related
Dec 12, 2000
I have a job that selects alot of data from one database into another.
Can I choose not to log this operation (doesn't need to be and the log fills up before it's done)
Thanks..
type of code:
Insert into database_1
Select * from database_2
View 1 Replies
View Related
Jun 3, 2004
Hi guys!
I need to create a table always that a new register is add in TABLE_ONE
The name of this new table must by 'TABLE_' + TABLE_ONE.Cod
It will be:
TABLE_1
TABLE_2
TABLE_3
...
I know this code must be in a trigger of insert... but how? I did some tests but didnt work.
Thanks in advance.
View 2 Replies
View Related
Aug 6, 2004
Hello, everyone:
There are some explanations on this topic from books and BOL. Could you offer the real experience? Thanks.
ZYT
View 2 Replies
View Related
Oct 28, 2004
Hi,
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?
Thanks,
Skip.
View 1 Replies
View Related
Mar 17, 2007
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
View 9 Replies
View Related
Dec 18, 2007
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.
CREATE PROCEDURE [dbo].[AdvSrchWildCardsIR]
(@Enter_IR_Number nvarchar(100))
AS
SET @Enter_IR_Number = REPLACE(@Enter_IR_Number, '*', '%')
SELECT dbo.RevisedSubjects_Table.SubjFirstName, dbo.RevisedSubjects_Table.SubjLastName, dbo.Revised_MainTable.[Violation Type],
dbo.RevisedSubjects_Table.[IR Number], dbo.Revised_MainTable.[I/RDocument], dbo.Revised_MainTable.Date, dbo.Revised_MainTable.Action
FROM dbo.Revised_MainTable INNER JOIN
dbo.RevisedSubjects_Table ON dbo.Revised_MainTable.[IR Number] = dbo.RevisedSubjects_Table.[IR Number]
IF patindex( '*', @Enter_IR_Number) > 0
BEGIN
SET @Enter_IR_Number = replace( @Enter_IR_Number, '*', '%' )
SELECT dbo.RevisedSubjects_Table.SubjFirstName, dbo.RevisedSubjects_Table.SubjLastName, dbo.Revised_MainTable.[Violation Type],
dbo.RevisedSubjects_Table.[IR Number], dbo.Revised_MainTable.[I/RDocument], dbo.Revised_MainTable.Date, dbo.Revised_MainTable.Action
FROM dbo.Revised_MainTable INNER JOIN
dbo.RevisedSubjects_Table ON dbo.Revised_MainTable.[IR Number] = dbo.RevisedSubjects_Table.[IR Number]
END
View 8 Replies
View Related
Sep 19, 2014
How Choose function in SQL is useful in a table. Any example with a simple table and how it can be useful for any particular column in a table
View 1 Replies
View Related
Dec 20, 2006
Hi there
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.
Its something like
if a =1 then execute this
else execute this one.
Thanks and Regards
Rahul Kumar, Software Enggineer
View 3 Replies
View Related
Apr 22, 2008
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.
View 7 Replies
View Related
Jun 28, 2006
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
Main = DTSTaskExecResult_Success
End Function
View 3 Replies
View Related
Nov 23, 2007
Hi there,
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?
Please let me know.
Regards,
mkarumuru
View 3 Replies
View Related
Jan 19, 2007
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
View 1 Replies
View Related
Jul 9, 2007
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?
End If
View 2 Replies
View Related
Apr 15, 2006
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
View 1 Replies
View Related
Apr 22, 2007
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.
Laura Lerner
View 8 Replies
View Related
Jun 7, 2007
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
,[Time].[Year - Quarter - Month - Date].CURRENTMEMBER))
,[Measures].[TURNOVER])
MEMBER [MEASURES].[WT TO MTD Vs PrEq]
AS IIF([MEASURES].[WT TO MTD PrEq]=0, null
,([MEASURES].[WT TO MTD] / [MEASURES].[WT TO MTD PrEq])-1), format='0.00%'
MEMBER [MEASURES].[WT TO MTD Rank]
AS RANK([SECTOR3].[SECTOR3 ID].CurrentMember
,[SECTOR3].[SECTOR3 ID].[SECTOR3 ID].ALLMEMBERS,
[Measures].[WT TO MTD])
MEMBER [MEASURES].[WT TO MTD PrEq Rank]
AS RANK([SECTOR3].[SECTOR3 ID].CurrentMember
,[SECTOR3].[SECTOR3 ID].[SECTOR3 ID].ALLMEMBERS,
[Measures].[WT TO MTD PrEq])
MEMBER [MEASURES].[WT TO MTD Vs PrEq Rank]
AS RANK([SECTOR3].[SECTOR3 ID].CurrentMember
,[SECTOR3].[SECTOR3 ID].[SECTOR3 ID].ALLMEMBERS,
[Measures].[WT TO MTD Vs PrEq])
SELECT NON EMPTY { [Measures].[WT TO MTD]
, [Measures].[WT TO MTD PrEq]
, [MEASURES].[WT TO MTD Vs PrEq]
, [MEASURES].[WT TO MTD Rank]
, [Measures].[WT TO MTD PrEq Rank]
, [MEASURES].[WT TO MTD Vs PrEq Rank]
} ON COLUMNS,
NON EMPTY { order([SECTOR3].[SECTOR3 ID].[SECTOR3 ID].ALLMEMBERS,[MEASURES].[WT TO MTD Vs PrEq], bdesc) } ON ROWS
FROM [WmCube4]
where [TIME].[Date].&[2007-06-04T00:00:00]
View 1 Replies
View Related
Aug 9, 2007
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?
View 4 Replies
View Related
