Question Relating Conversion And Calculations
Nov 27, 2007
Hi,
I have couple of DBF files.. and when i get them into my scrub database the datatype for all the fields in char. but after i scrub the data i put them into a money feild.. so that if so that i can do any calculations that i want in the report...
Suppose if i keep as a char or varchar in my production database... is it possible to any calculation on it in my report.. just simple additions... and if yes... do i need to convert them into anything before doing the additions... The reason i am asking this is Some of the fields in the scrub database are empty and i want to keep them the same in my production database instead of showing 0.00 as a default.
Any help will be appreciated.
Regards
Karen
View 1 Replies
ADVERTISEMENT
Mar 18, 2013
Is it possible to relate two tables (in the same db) using the PK (int) from one table to a UK (int, allownulls=No) from another?
I've tried, but had no success. Both tables are empty, so there is no way of having a null in the UK column that would prevent relating it to a PK. Just wanted to know if such relation is even possible before spending additional time on figuring out how to do it...
View 14 Replies
View Related
Nov 29, 2006
Hi,
I am trying to relate two tables but both are from different databases. I have a database 'Current' which has a table 'case' and another database 'Org' which has a table 'employee'. I want to establish a one-to-many relationship between employee and case. I created a coulmn 'employee_key' in the case table & entered values matching the pkey of the employee table. Then I wrote a query using joins to access the table records.
select Org..employee.pkey, Current..case.assignedengineer, Current..case.pkey from Current..case join Org..employee on Current..request.employee_key=Org..employee.pkey
Although the above query works fine, there is no relation between the two tables of the two databases, I wanted to know if this is the right way to achieve what i want to or is there a way in which i can actually create a relationship between tables of two different datbases. Can anyone suggest/help???
Also, I wanted to know if i can relate 1 table of SQL Server db with another table of Oracle DB. Please help ...........
Thanks
View 1 Replies
View Related
May 9, 2008
hello experts
i have two sql select statements that selects data from two different tables. I want to concatenate these two sql queries into one singl query (through programming only not through joins). After concatenation i want to sent the single created query to my transection function so that it can select data from two different tables.
How can i acomplish this in vb .net or c#
thanks
fine regards
ali
View 2 Replies
View Related
Jun 10, 2008
I am attempting to determine if a record for TODAY has been written. If one, I update it. If no records for TODAY have been created yet, I INSERT a new one. I am basing my "if" (C#) on a Select count(*) WHERE as shown below. My problem is that I do not know how to obtain the result. The debugger displays a -1 as the count and the IF falls through to its companion ELSE. Can anyone please set me straight? Oh, I build 'TestDate' from getdate and it ends up as a str like "6/10/2008". The Date column is also a varchar with the same kind of string as its contents.String fpsqlA = ("SELECT count(*) from DailyCounts WHERE Date = '"+testDate+ "'");
SqlCommand fpcmdA = new SqlCommand(fpsqlA, connStringDaily);fpcmdA.CommandType = CommandType.Text;
int iCount = fpcmdA.ExecuteNonQuery();SqlDataAdapter ad1 = new SqlDataAdapter(fpcmdA);
ad1.Fill(dtA);
//Test to see if a record for 'today' has already been written to the DailyCounts table. if (iCount <= 0)
{
View 6 Replies
View Related
Apr 9, 2008
Hi all,
I am still relatively new to the SQL world, and have just started looking at purging data from my databases which is older than 8 years old. I am using a routine from another forum on here to get a before Table size pre purge, and then running it again to get the new size after.
The thing i have found is that even though i have deleted over 1.5 million rows, there is actually no difference in the table size.
I have been looking at the UPDATE STATISTICS on the msdn web pages to see if i need to run this to update the information i am using for size comparisons, but it doesnt really tell me if this is one of the things that it updates, and was wondering if i was being dumb and missing something completely obvious here. The other thing i can think of is that even though the data has been deleted, the space has been retained in the indexes on the tables, and until i do a re-index i will not realise the space savings?
Many thanks in advance, and feel free to point and snigger if i have just missed something staring me in the face
Mark
View 1 Replies
View Related
Aug 13, 2007
hi!
i have some what a definition table of the fields of the other table
name as DefinitionTable with data as follows
FieldType FieldName1 FieldDesc TxtColor FontStyle
8 FIELD1 ABC #1111 [fsbold]
8 FIELD2 CDE #2222 [fsitalic]
8 FIELD3 EFG #3333 [fsbold]
OrigTable
OrigKey FieldType FIELD1 FIEDL2 FIELD3 Results
1 8 test1 test2 test3 ok
so the e.g. FIELD1 is a data in DefinitionTable and a field name in OrigTable..
my problem is how to get the TxtColor in merging this table.. my sp in fetching the data but only the fielddescription is
create procedure Definition
@SearchKey int,
@FieldTypeint
as
begin
DECLARE @FieldName varchar(10),
@FieldDesc varchar(30),
@TextColor int,
@mysql varchar(4000)
SET NOCOUNT ON
SELECT @mysql = 'select o.origkey, '
DECLARE MyCursor CURSOR READ_ONLY FOR
SELECT FieldName1, FieldDesc FROM DefinitionTable
WHERE FieldType = @ResultType
Open MyCursor
FETCH NEXT FROM MyCursor INTO @FieldName, @FieldDesc
WHILE @@FETCH_STATUS = 0
BEGIN
select @mysql = @mysql + 'o.' + @FieldName + ' as ' + @FieldDesc + ', '
FETCH NEXT FROM MyCursor INTO @FieldName, @FieldDesc
END
CLOSE MyCursor
DEALLOCATE MyCursor
select @mysql = @mysql + ' o.results from OrigTable o where
FieldType = ' + @ResultType
execute(@mysql)
end
the result is Ok as
OrigKey ABC CDE EFG Result
8 test1 test2 test3 ok
But the problem is how to include the TxtColor or concatenate TxtColor with this output below
OrigKey ABC CDE EFG Result
8 test1+##1111 test2+#222 test3+#3333 ok
thanx,
mygt
View 1 Replies
View Related
May 20, 2008
EDIT: Please skip over this and go to the 2nd post. After writing this, I realized it may be too complicated to comprehend, so I tried to simplify with another example which is found in the 2nd post. Or if you want you can read this one and try to understand it.
I would like to go over a design we came up with to handle a certain reporting requirement from an OLAP cube to see if this is a good approach. Also, I would like to hear about other designs that we can consider.
Here is the condensed version of the requirement.
We are measuring the performance of a bunch of portfolios. So among other dimensions, we have a portfolio dimension that holds the list of portfolios we are tracking. In a typical portfolio analysis, an analyst almost always looks at a portfolio's measures along side a benchmark portfolio's measures. A benchmark is another portfolio against which a selected portfolio is compared. So, for a given portfolio, there is a determined benchmark portfolio.
For example, an analyst might want to look at a measure called Annual_Yield for a portfolio called Portfolio_A. There will be an attribute on the member Portfolio_A that tells us what its benchark portfolio is. That attribute will basically be another member from the portfolio dimension. So, as a function of what an analyst chooses as the first portfolio (the subject portfolio), the cube should be smart enought to know what to select as the second portfolio (the benchmark portfolio).
To implement this, I am proposing the following structure for the table that will create the portfolio dimension.
PortfolioID, PortfolioName, Attr1, Attr2, ..., AttrN, BenchMarkID
1, Portfolio_A, blah, blah, ...., blah, 3
2, Porfolio_B, blah, blah, ...., blah, 3
3, Portfolio_C, blah, blah, ...., blah, null
4, Portfolio_D, blah, blah, ...., blah, 3
5, Portfolio_E, blah, blah, ...., blah, 6
6, Portfolio_F, blah, blah, ...., blah, null
So, the above data says that Portfolios A, B, and D uses Portfolio C as their benchmarks. And Portfolio E uses Portfolio F as its benchmark. Lastly, if a portfolio is used as a benchmark for other portfolios, then its BenchmarkID is null, meaning it doesn't get compared to anything, thus Portfolios C and F have null BenchmarkIDs. With this data, I would create a hierarchy in the portfolio dimension that has two levels. At the top level, there will be a portfolio and if that portfolio has a benchmark, then it will drill down to it.
With a portfolio dimension designed as such, I envision a fact table that looks like the following.
PortfolioID, SomeOtherDimIDs,....., Annual_Yield
1, , blah, ,....., 10%
2, , blah, ,....., 7%
3, , blah, ,....., 0%
4, , blah, ,....., 15%
5, , blah, ,....., 20%
6, , blah, ,....., 9%
So, going back to the original report which was looking at Annual_Yield by Portfolio_A, I would do an MDX statement such as
With Set [PortfolioAndBenchmark] As
DESCEDANTS(Portfolio_A)
Select
[Annual_Yield], [PortfolioAndBenchmark] On Columns,
Whatever Other Dimension On Rows
From
[Cube]
Does this make sense? The only concern I have is that I don't want the measures to aggregate along the portfolio hierarchy.
View 1 Replies
View Related
Nov 23, 2007
Our mobile application using VS2005 with VB.net is ready and we are trying to set up the SQL CE on the server to test the remote connection procedures. Sql 2005 is based on a dedicated server and is accessed via the Web server which does not have Sql installed on it. We have installed the Sql Server Me Server Tools on the Web server and configured the Virtual directory with IIS. What we cannot get our heads around is how we link the database on the separate Sql server via the web server. When the Pda contacts the Web server via IIS we somehow need to redirect to the dedicated Sql server as it cannot be contacted outside of our network. We have been told that Sql Ce can link with an existing Sql 2005 database, how can we do this?
View 6 Replies
View Related
Apr 2, 2006
I've been searching everywhere for a solution to this problem and no answers exist anywhere. When I try to create a new maintenance plan I get the following error. I've been told it may be related to SSIS but nobody has a solution. How do I fix this issue so I can create a maintenance plan.
Exception has been thrown by the target of an invocation. (mscorlib)
ADDITIONAL INFORMATION:
An OLE DB error 0x80004005 (Client unable to establish connection) occurred while enumerating packages. A SQL statement was issued
and failed.
An OLE DB error 0x80004005 (Client unable to establish connection) occurred while enumerating packages. A SQL statement was issued
and failed.
View 1 Replies
View Related
Nov 18, 2015
I'm currently setting up a Tabular Model to do some research between several fact tables. In this example i have two fact tables (table 1 and table 2) which I've created a 1 to 1 relationship on phone number. Typically I create a relationship between these tables to find common data between the two. However, in this case I am trying to figure out the best way to model the data so that I can easily surface data from one table that does not exist in the other. I would liken this to a LEFT JOIN or a WHERE NOT EXISTS in SQL.
Table 1 has all of the data and Table 2 Only has a subset of the data from Table 1. What I'm trying to do here is display what attributes in Table 1 may play a part in records not existing in Table 2. What is the best way to model this?
View 3 Replies
View Related
May 18, 2007
Hello all. I am trying to do a calculation within an SQL script, however it doesnt seem to be working and i'm a little bit lost. If anyone could shed some light on where i'm going wring it would be much appreciated. The code I have is:
select
EMPLOYEE.EMPLOY_REF AS EDIT_REF,
SV_EMPLOYEE_CURRENT_HOLIDAY.ENTITLEMENT,
SV_EMPLOYEE_CURRENT_HOLIDAY.CARRIED_FWD,
SV_EMPLOYEE_CURRENT_HOLIDAY.TAKEN,
SV_EMPLOYEE_CURRENT_HOLIDAY.REMAINING,
SV_EMPLOYEE_CURRENT_HOLIDAY.SOLD,
SV_EMPLOYEE_CURRENT_HOLIDAY.PURCHASED,
SV_EMPLOYEE_CURRENT_HOLIDAY.ENTITLEMENT + SV_EMPLOYEE_CURRENT_HOLIDAY.SOLD - SV_EMPLOYEE_CURRENT_HOLIDAY.PURCHASED AS TOTAL_ENTITLEMENT
from
EMPLOYEE
left outer join
SV_EMPLOYEE_CURRENT_HOLIDAY
on
EMPLOYEE.EMPLOY_REF = SV_EMPLOYEE_CURRENT_HOLIDAY.EMPLOY_REF
where
EMPLOYEE.EMPLOY_REF = = 027
Incidentaly SV_EMPLOYEE_CURRENT_HOLIDAY is a view which currently exists.
Thanks in advance people.
View 2 Replies
View Related
Jul 1, 2007
In order to find out if an event is late or not I need to do some time calculations in SQL as a Stored procedure.
I have a DateTime variable called Due
I also have an Allowance variable which is an integer and is an extra allowance for that day and a third variable Now which is set with GETDATE()
If I compare Now to Due I can decide if the task is late or not - but I need to take itno account the Allowance.
I tried :
IF @Due + (@Allowance /24) < @Now ......
However I find that @Allowance/24 always equates to zero so this doesn't work.
I'd appreciate any advice.
Regards
Clive
View 2 Replies
View Related
Mar 21, 2008
Hello,
I ran into a little problem. My problem is: i need to substract 2 variabeles from 2 different tables in the database
TitleTimes left todayTimes left
My first excercise!15
My second excercise!19
The fields times left are a calculation... the number of times that the admin entered minus a count in the table scores.
Has anyone an idea how i can solve this?
An example excercise would be great!
Thanks in advance
View 5 Replies
View Related
Jun 5, 2008
I am attempting to construct a SELECT statement which incorporates some variables. The variables begin life as strings (not String objects) looking like :"6/08/2008" and "06/10/2008" for example. The first is a start date which was retrieved using an AJAX calendar object and the second is an end date retrieved in the same manner. My records are all timestamped by MS SQL (2003) including the clock time. I am stumbling on the syntax. "CallStartTime" is the record's timestamp. The "TraversalString" is something else but I am not attacking that yet. Can anyone make a suggestion or two?
SELECT count(*)FROM RealTime WHERE CallStartTime >= '@starttime' AND CallStartTime <= '@endtime' AND TraversalString LIKE '%1.0%'
View 2 Replies
View Related
Jun 5, 2006
Timespan = 30 days
Start Date = January 1st
Last Processed Day = NULL
Next Cycle Day = IF(Last Processed Day IS NULL) Start Date + TimeSpan ELSE Last Processed Day + TimeSpan
Is it possible to setup a column to do this from sql?
View 1 Replies
View Related
Jul 8, 2002
I have a timesheet table and I am having trouble getting a calculation to work correctly. I'd like to subtract the punch-in times from the punch out times for a specific period, such as 1 week, and then add the time together and get the number of minutes worked altogether during that time.
The table is simply
Employee ID (int)
PunchIn (datetime)
PunchOut (datetime)
Thanks for any help.
EL
View 2 Replies
View Related
Jan 4, 1999
I am trying to do a calculation to find rows which have a date which is 2 days older the the getdate().
i.e Select documentdate from table where documentdate < (getdate() - 2)
Any ideas on how I can perform this operation.
Thanks
Vinny
View 1 Replies
View Related
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
Jul 7, 2004
Hi
I sometimes find myself in the situation where I want to insert a row into a table using the following form:
insert table ( <field list> ) select <field list> from .. etc .. Where <conditions>
My question is to do with where one or more of the fields in the select field list are calculations and where I also want to use some/all of these derived fields as Where conditions. [ Eg: only insert if the calculated value is > 0]
I currently either repeat the calculation in the Where clause or move it to a function and use the function call in both places. (I always get a pang of guilt using either option - repeating the calculation feels like bad practice - & using the function twice seems inefficient (does this get optimised?)).
I could get a life & stop worrying - but is there a better/neater way of doing this?
Many thanks.
View 3 Replies
View Related
Aug 24, 2005
Vikram writes "Hi,
I need some help in date manipulations on SQL server data.
I am required to calculate to see
1. If anyone is absent one day within a 30 day period. if they are then upon their next absence in the same rolling 30 day period they need to be move to step I of disciplinary stage.
this information has to be checked for hundreds of employees.
I tried many ways but am not getting the right results. Moreover upon defaulting they will have to be moved to differnt steps.
Any suggestions will be greatly appreciated.
thanks in advance
Vik"
View 3 Replies
View Related
Nov 25, 2005
Hello,
I am a student and I have an assignment in for next week and we are having a few problems... below is some code we have created to enter in payment details for a customer....
INSERT into Payments values(Payments_seq.nextval, initcap('&Payment_Method'), '&Amount_Payable',
Date_Due = (select date_of_order from orders where order_no = (date_of_order+7));
I am having trouble with the last line, I want the date due to be calculated from the date the order was made in another table (orders) and I want a week to be added to this so that in the field it will display (date ordered plus 7 days)...
Any help you can give would be great, I've only been using isql plus for about 4 weeks...
Thanks
Pam :)
View 2 Replies
View Related
May 17, 2006
Users,
My question is about the number of calculation made with my database and my query.
The situation:
Table1: Contains 900 projects
Table2: Contains 13500 timesheets
Table3: Contains 516 Periods
Then there are 3 queries:
Query 1: Calculating the total for the periods
Query 2: Calculating working hours + working hours * total of a period
Query 3: Calculating loancosts of a project.
Query 3 is using 2 and 2 is using 1.
How many calculations are made in total????
Thanks!
Willio
View 4 Replies
View Related
Aug 10, 2006
Hello all.
I am looking to perform a calculation and enter the reult into a field within my table. The fields that I need to base the calculation on are all in one table (SALARY). The fields are: SALARY and BASIC_HOURS and the result is to be entered into field HOURLY_RATE. The actualy calculation to be preformed is:
hourly rate = ()salary / 52)/basic hours)
Thanks for reading :)
View 4 Replies
View Related
Mar 28, 2007
where should be the calculations be made based on performance, in SQL or in front end?
thanks.
-Ron-
View 7 Replies
View Related
Jul 23, 2005
Hi,I have a select query that returns three integer fields from a table thevalues range from 0 to 5. On each row I would like to calculate the averagevalue in the three fields however, the difficulty is that only the rowswhere the value is greater than 0 should be included in the calculation.To make this clearer please consider the following example:Col1 Col2 Col2 Average Calculation1 2 3 2 (Col1+Col2+Col3)/32 2 0 2 (Col1+Col2+Col3)/2The first row should be divided by 3 because each of the three columns has avalue greater than 0, however the second row should be divided by 2 becauseonly two of the value are greater than 0.Is this possible?Also is is possible to pass the results of one calculation into anothercalculation.Thanks in advance,Steve
View 3 Replies
View Related
Jul 20, 2005
hi,i ran the follow command:select #tempra.ranumber,#tempra.amountdue,#tempquickrec.t otalrec,#temparap.amountpaid,bal=amountdue - totalrec+amountpaidfrom #tempraleft join #tempquickrec on #tempquickrec.hrr = #tempra.ranumberleft join #temparap on #temparap.ranumber = #tempra.ranumberresult:ranumber amountdue totalrec amountpaid bal1222 $1200 $1000Null null2222 $3000 $3000 Null null3333$3000 $5000 $2000 0i know null is not zero thus bal column is null.this is because there is no corresponding record in the #temparap tableso how do i obtain the following result:(ie Ranumber bal is not 0)ranumber amountdue totalrec amountpaid bal1222 $1200 $1000Null 2002222 $3000 $3000 Null 03333 $3000$5000 $2000 0i am using ms sql2000thanks in advancerashid
View 2 Replies
View Related
Jul 20, 2005
I am looking to calculate the difference between and event time and a sampletime of Now. This is the query that I thought would do it, however I'mreturning DIFFERENCE values that look the same when the calcuation is beingmade on different EVENT_TIME values....I thought I knew how DateDiff worked, but apparently not.select GetDate()as NOW,event_time,Datediff(s,(Cast(event_time asNumeric)),(Cast(GetDate() as Numeric))) as DIFFERENCEFROM events-----------NOW----------|-----EVENT_TIME------|-DIFFERENCE2004-06-30 11:22:38.560 1999-10-30 23:51:37.000 1472256002004-06-30 11:22:38.560 1999-10-30 23:23:47.000 1472256002004-06-30 11:22:38.560 1999-10-30 06:49:38.000 1473120002004-06-30 11:22:38.560 1999-10-30 06:50:00.000 1473120002004-06-30 11:22:38.560 1999-10-30 06:50:41.000 1473120002004-06-30 11:22:38.560 1999-10-30 06:49:59.000 1473120002004-06-30 11:22:38.560 1999-10-30 06:49:58.000 1473120002004-06-30 11:22:38.560 1999-10-30 06:50:53.000 1473120002004-06-30 11:22:38.560 1999-10-30 06:50:46.000 1473120002004-06-30 11:22:38.560 1999-10-30 06:49:42.000 1473120002004-06-30 11:22:38.560 1999-10-30 06:50:36.000 1473120002004-06-30 11:22:38.560 1999-10-30 06:50:07.000 1473120002004-06-30 11:22:38.560 1999-10-30 10:54:37.000 1473120002004-06-30 11:22:38.560 1999-10-30 11:40:15.000 1473120002004-06-30 11:22:38.560 1999-10-30 09:52:51.000 1473120002004-06-30 11:22:38.560 1999-10-30 12:12:46.000 1472256002004-06-30 11:22:38.560 1999-10-30 12:32:45.000 1472256002004-06-30 11:22:38.560 1999-10-30 12:32:45.000 1472256002004-06-30 11:22:38.560 1999-10-30 12:32:46.000 1472256002004-06-30 11:22:38.560 1999-10-30 12:46:30.000 1472256002004-06-30 11:22:38.560 1999-10-30 15:31:25.000 1472256002004-06-30 11:22:38.560 1999-10-30 23:08:25.000 1472256002004-06-30 11:22:38.560 1999-10-30 16:35:51.000 147225600Can someone help?TIA!Joe..
View 2 Replies
View Related
Jul 20, 2005
I have a field that contains date information, and sometimes timeinformation as well. I would like to be able to take that date and do acalculation on it. Here are some examples of what is in the field:01/12/2003 5:04:00 PM24/11/200319/05/2003 6:30:00 AMHow can I take that date, then do a calculation like minus 5 days from thedate. I understand that I am to use the GETDATE() function, but below isthe SQL I have implemented.SELECT Field1, Field2, Field3FROM Table1WHERE (convert(char(10),Field1) like convert(char(8), GETDATE()-5))For some reason this works, and it will return results that occur on thisday, but it disregards the year. Now someone will probably ask "Whyconvert, char(10), etc". To be honest, I do not know and I ended upimplementing it from some other Usenet posts that are out there. I wastrying to figure this out and I ended up with that working until I laterrealized it was only caring about the day and month. Any ideas what I amdoing wrong here? I just want to return results that have the day being 5minus the current day. I am not interested in time information.Thanks if anyone can help, I am by far not experienced in SQL.
View 1 Replies
View Related
Jul 20, 2005
I'm running SQL query to caluclate projected food costs. Thecalculation is this:(ReportedFoodSales / PlanFoodSales) * FullPlanFoodSalesSeems simple enough to me.Using the following numbers, SQL comes up with a different answer thanwhat I do with a calulator. The data types are money. I'm sure thereis some reasonable explanation....right?Reported Food Sales: 28096.4500Plan Food Sales: 28608.4167Full Plan Food Sales: 137702.0000SQL Answer: 135237.1342Calculator Answer: 135237.7308Any ideas?Thanks,Jennifer
View 3 Replies
View Related
May 28, 2008
Hi -
I'm working in Visual Studio 2005 and created an Analysis Services Project.
Using the Business Intelligence Wizard, I created a "Year To Date" calculation. The Year to Date calcualtion appears to work fine.
My problem is that I'd like to edit the calcualtion but most of the icons on the Calculations Tool Bar are disabled. How does this happen and how can I enable them?
Thanks, Mark
View 1 Replies
View Related
Nov 2, 2007
Hi--
I am relatively new to SQL Reporting Services and have a couple basic questions (I think they are basic).
First, lets say that I have the following fields in the body of my report: Sales, Cost, Profit, and Percent Profit. Percent Profit is a calculated field of profit/sales.
Then, I have a grouping by customer, with a footer with the sum of sales, sum of cost, sum of profit. And I also want percent profit, but I do not want a sum or average of percent profit. Instead I want percent profit calculated as sum of profit/sum of sales. I cannot seem to get this to work with a calculated field. I must have the syntax wrong (as I was just taking sum(fields!profit.value)/sum(fields!sales.value)*100.
Also, I understand the format of #,# can be used to print a blank instead of zero. But this format also suppresses any decimals. So if I have a field that needs two decimals when there is a value, but need it to be blank when 0.00 is returned, what is the appropriate format?
Any help that can be provided would be much appreciated. As books are great, but sometimes do not explain everything. Thanks!
-Christina
View 8 Replies
View Related
Aug 21, 2007
Hi All,
I'm a Geomechanics student and newcomer to SQL. I'm currently working on a project and using SQL as my data analysis tool. Below are some of the sample results from instrument reading. I would really like to have your assistance to create a summary from the data (table) provided below. Further info about summary table is also provided.
=========
create table dbo.t1 (
[PK] varchar (20),
[date] datetime,
[Location] varchar(20),
[Weigth] float,
[Readings] float,
[Days_inc] float
)
go
set nocount on
insert into dbo.t1 values( ' DAFN25S ' , ' 1/1/2004 12:00:00 AM ' , ' A-23 ' , ' 22.6 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' DANS37J ' , ' 1/2/2004 12:00:00 AM ' , ' A-23 ' , ' 27.3 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' FKFH34U ' , ' 1/3/2004 12:00:00 AM ' , ' A-23 ' , ' 29 ' , ' 0.0083 ' , '3' )
insert into dbo.t1 values( ' DRCY55O ' , ' 1/16/2004 12:00:00 AM ' , ' A-23 ' , ' 23.2 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' DRKR13T ' , ' 1/17/2004 12:00:00 AM ' , ' A-23 ' , ' 31.9 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' TRKR54Y ' , ' 1/18/2004 12:00:00 AM ' , ' A-23 ' , ' 29.6 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' RTYT24T ' , ' 1/19/2004 12:00:00 AM ' , ' A-23 ' , ' 23.8 ' , ' 0.0244 ' , '4' )
insert into dbo.t1 values( ' RKCB26T ' , ' 1/24/2004 12:00:00 AM ' , ' A-23 ' , ' 18.3 ' , ' -0.0061 ' , '1' )
insert into dbo.t1 values( ' DKRH51R ' , ' 1/28/2004 12:00:00 AM ' , ' A-23 ' , ' 10.8 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' DRGT77U ' , ' 1/29/2004 12:00:00 AM ' , ' A-23 ' , ' 25.3 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' USNT80T ' , ' 1/30/2004 12:00:00 AM ' , ' A-23 ' , ' 27.3 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' TDNF71Q ' , ' 1/31/2004 12:00:00 AM ' , ' A-23 ' , ' 26.9 ' , ' 0.0014 ' , '4' )
insert into dbo.t1 values( ' DKAH23Y ' , ' 1/2/2004 12:00:00 AM ' , ' B-60 ' , ' 15.4 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' DAKD44T ' , ' 1/3/2004 12:00:00 AM ' , ' B-60 ' , ' 21.7 ' , ' 0.0229 ' , '2' )
insert into dbo.t1 values( ' PDNR56Y ' , ' 1/4/2004 12:00:00 AM ' , ' B-60 ' , ' 19.2 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' DBTG87K ' , ' 1/5/2004 12:00:00 AM ' , ' B-60 ' , ' 17.6 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' KDHR43I ' , ' 1/6/2004 12:00:00 AM ' , ' B-60 ' , ' 19.3 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' QJKD00F ' , ' 1/7/2004 12:00:00 AM ' , ' B-60 ' , ' 20.5 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' KKKR83I ' , ' 1/8/2004 12:00:00 AM ' , ' B-60 ' , ' 18.3 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' KDDR90R ' , ' 1/9/2004 12:00:00 AM ' , ' B-60 ' , ' 20.5 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' TTRC87R ' , ' 1/10/2004 12:00:00 AM ' , ' B-60 ' , ' 19.3 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' RDTD67E ' , ' 1/11/2004 12:00:00 AM ' , ' B-60 ' , ' 19.3 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' ZDHR44I ' , ' 1/31/2004 12:00:00 AM ' , ' B-60 ' , ' 24.6 ' , ' 0.0428 ' , '9' )
insert into dbo.t1 values( ' SRHT23T ' , ' 2/1/2004 12:00:00 AM ' , ' B-60 ' , ' 29 ' , ' 0.0413 ' , '1' )
set nocount off
go
=============
Summary table:
PK
Location
Sum Weight
reading var.
FKFH34U
A-23
78.9
0.0083
RTYT24T
A-23
108.5
0.0161
RKCB26T
A-23
18.3
-0.0305
TDNF71Q
A-23
90.3
0.0075
DAKD44T
B-60
37.1
0.0229
ZDHR44I
B-60
178.6
0.0199
SRHT23T
B-60
29
-0.0015
Note:
1.'Sum weight' is the accumulation of weight between two readings. The weight of the previous reading is not included in the calculation. Example: the 'sum weight' of location A-23 between the reading on 1/24/2004 to 1/31/2004 is (10.8+25.3+27.3+26.9)= 90.3
2.'Reading var.' is the substraction value of the most recent reading value to the previous date reading values. Example: the 'reading var' for location A-23, between the latest reading on 01/31/2004 and previous reading on 01/24/2004 is 0.0075. If there is no value on previous reading, then the latest date reading is substracted by zero (0). The substraction can only be done on the records of similar reading location (A-23 or B-60, etc).
3. 'Location' is obtained from the most recent date of reading, which is used for the calculation.
4. 'PK' is obtained similarly than procedure at point 3.
Thank you in advance.
WP
View 10 Replies
View Related
