I have three columns in my table with the following datatypes:
Date - DateTime
Height - Decimal
Change_From_last_Height - Decimal
I am using "SQL Server 2005 Express Edition". I'm fairly new to SQL and would greatly appreciate any help or advice I can get.
The "date" column increases by an extra day in every new row and I then enter the new height of the plant. What I want to know is how I can get SQL server express to automatically enter the difference in height between the current row's height and that of the previous row.
Is it possible to automate the entry in the Change_From_Last_Height column in SQL?
Put another way, I know how to find the difference between two values in the same row but different columns, but how do I calculate the difference between values in adjacent Rows (ie. Rows next to each other)?
I am working with a data set containing several years' of monetary values. I have entries for past dates and the associated values, and I also have entries for future dates. I need to populate the values of the future date records with the values from the same date the previous year. Is there any way this can be done in Power Pivot?
Hi, this is my problem. The query listed below gets a list of employees asigned to a project, to a customer and to a business unit (the Dimension field). The last two fields are the start date and the end date.
select P.DIMENSION, P.CUSTACCOUNT, C.NAME as CUSTOMERNAME, PE.PROJID, P.NAME as PROJNAME, PE.EMPLID, E.NAME as EMPLNAME, PE.FECHAASIGNACION, PE.FECHADESASIGNACION from PROJVALEMPLPROJSETUP PE, EMPLTABLE E, PROJTABLE P, CUSTTABLE C where PE.PROJID = P.PROJID and PE.EMPLID = E.EMPLID and P.CUSTACCOUNT = C.ACCOUNTNUM and (PE.FECHAASIGNACION >= '01/01/2007' and PE.FECHADESASIGNACION >= '01/01/2007') order by P.DIMENSION, P.CUSTACCOUNT, PE.PROJID, PE.EMPLID
I need to get a listing showing how many employees started or ended their projects every months in the following format:
Business UNIT CLIENT PROJECT STARTING TOTAL JANUARY FEBRUARY .. TOTAL VARIATION -------------------------------------------------------------------------------------------------------- 1 1 1 25 3 -2 26 1
Starting total refers to all the employees that have a starting date before 1/1/2007 and a ending date after 1/1/2007
The monthly columns would be the total of employees with a starting date (+1) or ending date (-1) in that month.
The total column is the starting total plus the monthly totals.
And the variation is the difference of the total with the starting total.
Hello, I have 2 tables, first is the mineral values, 2nd is ore composition... Minerals table:
Mineral Value
Mineral1 10
Mineral2 20Ore composition table:
Ore Mineral1 Mineral2
Ore1 100 0
Ore2 0 200
Ore3 10 10Now what I need to do is to have a view (or something else that gerenates a table) that will give me the value of each ore. So for example ore 3 would be 10 X mineral1 value plus 10 X mineral2 value. So the result should be:
Ore Value
Ore1 1000
Ore2 4000
Ore3 300Can anyone point me to some tutorial or example of a similar calculation with sql views?
I'm in the process of building various queries and I'm having issue calculation values in the following code. I need to calculate the maximum and minimum salary for exempt employees and include a column that calculates the difference in these numbers. I have no issue creating views and outputing data. When I try to calculate the values I receive errors:
My code is:
GO IF OBJECT_ID ('Salaries') IS NOT NULL DROP VIEW Salaries ; GO CREATE VIEW Salaries AS select max(Salary) from EMPLOYEE join JOB_TITLE on EMPLOYEE.JobID = JOB_TITLE.JobID where ExemptStatus='Exempt' Group By Salary, ExemptStatus; GO SELECT * FROM [Salaries]
I'm receiving the following Msg 4511, Level 16, State 1, Procedure Salaries, Line 3 Create View or Function failed because no column name was specified for column 1. Msg 208, Level 16, State 1, Line 1 Invalid object name 'Salaries'.
1. Is it possible to use the subtotal as a field for calculating values? 2. Can I add another row to the subtotal area, having two subtotal rows?
I need to achieve the following output:
Months 1 2 3 101200 Cust1 2008 50 40
2007 45 45 Subtotal +5 -5 102300 Cust2 2008 70 80
Subtotal 0 0
What I want is to use the subtotal as a variance-field for the two selected years by the user. It could happen that a customer doesn't generate turnover in one year, then the subtotal has to be 0 or 100 or whatever.
If my suggestions aren't applicable then I'd like to know If there is another possibility to display the desired results.
Out of this table what I need to do is calculate the average value for each same [Name]. As you can see from the sample set above, the Diameter1 has 2 entries value hich gets store at different time of course.
So I I take the example of Diameter1 I need to calculate and display in a field the average results. The result would be
Name Average ===================== Diameter1 ..... Diameter2 .... Temperature2 .... Temperature1 ....
How can I perform this ?
Or could it be better to get a view of the table above which gets display as follow :
Hi, I'm using scalar UDFs in SQL server to return computed values. I've been trying to use the same udfs to perform the same computations but from different tables, but I'm not sure how. Can someone please help???
Here's an example of a counter that I'm using to return the number of days.
CREATE FUNCTION [dbo].[MarketPulse_fn_Counter] (@date smalldatetime, @date2 smalldatetime, @osid int)
RETURNS int AS
BEGIN
return (select count(*) from MarketPulse_0ndqc where stockosid = @osid and createdate <= @date and createdate >=@date2 )
END
How can I use this same UDF to do the same computation but to SELECT from another table and return that value?
I need to calculate a median on a column in a table. The code I have is:
Code: Select gender, CASE when gender = 'F' then 'Female' when gender = 'M' then 'Male' else 'Unknown' end as test, datediff(day, [admit_date], getdate()) as 'datediffcal', from [tbl_record] How do I calculate the median on the datediffcal column?
It doesn't matter if the resultset only shows the median result. So if the output shows:
I have two tables that look like this (below). One tells me the name of my product, the Amazon Category it is in & the amount that I want to sell it for. The other tells me the Category & the fee for that category. So far so good. Though it gets tricky in the sense that some categories have two tiers. So in Electronics, the fee for $0.00 - $100.00 is 15%. But from $100 and up it is 8%.
Since it has two columns & both of the new columns pertain to the fee of my product, I can't figure out how to use both at once. For my $599.99 example it would be ($100 * 0.15) + ($499.99 * 0.08) = $55.00. Would I pivot the data? If not, how would I group it to be considered together?
I have to Lists filtered by the same field. One List shows all records with the field IsClokedIn value = TRUE and the other list =FALSE. I have only one dataset that retrieve the ClockIn and Clock out for one user. In a perfect world, the user MUST clock in and always clock out. So, when i created my dataset i ordered by UserClockingID (whick is primary key). So, this MUST retrieve a dataset with alternate records IN / OUT (TRUE or FALSE)
Ex.
UserClokingID UserID Time IsClockedIN
2323 34 8:32:03 TRUE
2324 34 12:07:02 FALSE
2325 34 13:05:03 TRUE
2326 34 14:53:02 FALSE
2327 34 15:10:03 TRUE
2328 34 17:32:02 FALSE
All i need is to calculate (sum then divide by 60) the minutes returned by the method DateDiff between the first row in my TRUE filtered list and the first row in my FALSE filetered list... and so on for the 2nd, 3rd ..... I can get the minutes by taking the values directly from the Fields!IsClockedIn.Value and using a condition to "grab" the previous record if the current record ClockedIn.Value=FALSE. I already did that.
my problem is that when i try to perform a calculation of textboxes values(those with the ammount of minutes for every pair of records from the datalist) out of the group. It says you can only do that inside the group or dataregion.
I am trying to create a SSRS report that needs to show average for both parent and child rows. In the example below i need to show an average of ErrorRecAge column for Company, then for cic_Group and one grand total/avg. How can this be done from single query (as below) and using SSRS built in functions/codes? I am also curious to know if this can be done from TSQL directly (ex. something similar to running sum/total). I appreciate the help.
Select
CustomerCorrection.Id As ParentId,
cc_company As cc_company2,
ccError.Id As ErrorId,
COALESCE(cic_Group,'Other Errors') As cic_Group,
COALESCE(cic_Code,'Unknown') As ErrorCode,COALESCE(cic_Description,'Unknown') As ErrorDescription,
DateDiff(hh,cc_entrydt,getdate())/24.00 AS ErrorRecAge
I have a script that I use after some amount of data massaging (not shown). I would like to be able to change the
1) denominator value (the value 8 in line 32 of my code) based on how many columns are selected by the where clause:
where left(CapNumber,charindex('_', CapNumber)-1) = 1where capNumber is a value like [1_1], [1_4], [1_6]...[1_9] capNumber can be any values from [1_1]...[14_10] depending upon the specialty value (example: Allergy) and the final number after the equal sign is a number from 1 to 14)
2) I'd like to dynamically determine the series depending upon which values correspond to the specialty and run for each where: left(CapNumber,charindex('_', CapNumber)-1) = n. n is a number between 1 and 14.
3) finally I'd like to dynamically determine the columns in line 31 (4th line from the bottom)
If I do it by hand it's 23 * 14 separate runs to get separate results for each CapNumber series within specialty. The capNumber series is like [1_1], [1_2], [1_3],[1_4], [1_5], [1_6], [1_7], [1_8],[1_9] ... [8_4],[8_7] ... [14_1], [14_2],...[14_10] etc.
Again, the series are usually discontinuous and specific to each specialty.
Here's the portion of the script (it's at the end) that I'm talking about:
--change values in square brackets below for each specialty as needed and change the denom number in the very last query.
if object_id('tempdb..#tempAllergy') is not null drop table #tempAllergy select * into #tempAllergy from dbo.#temp2 T
[Code] ....
If I were to do it manually I'd uncomment each series line in turn and comment the one I just ran.
I have the following variables VehicleID, TransactDate, TransactTime, OdometerReading, TransactCity, TransactState.
VehicleID is the unique vehicle ID, OdometerReading is the Odometer Reading, and the others are information related to the transaction time and location of the fuel card (similar to a credit card).
The records will be first grouped and sorted by VehicleID, TransactDate, TransactTime and OdometerReading. Then all records where the Vehicle ID and TransactDate is same for consecutive rows, AND TransactCity or TransactState are different for consecutive rows should be printed.
I also would like to add two derived variables.
1. Miles will be a derived variable that is the difference between consecutive odometer readings for the same Vehicle ID.
2. TimeDiff will be the second derived variable that will categorize the time difference for a particular vehicle on the same day.
My report should look like:
VehID TrDt TrTime TimeDiff Odometer Miles TrCity TrState 1296 1/30/2008 08:22:42 0:00:00 18301 000 Omaha NE 1296 1/30/2008 15:22:46 7:00:04 18560 259 KEARNEY NE
I'm stuck. I have a table that I want to pull some info from that I don''t know how to.
There are two colomuns, one is the call_id column which is not unique and the other is the call_status column which again is not unique. The call_status column can have several values, they are ('1 NEW','3 3RD RESPONDED','7 3RD RESOLVED','6 PENDING','3 SEC RESPONDED','7 SEC RESOLVED').
The call_id could be any number, I only want the 6 PENDING rows where there are other rows for that call_id which have either 3 3RD RESPONDED or 7 3RD RESOLVED. If someone knows how it would be a great help.
I'm working on a database for a financial client and part of what i need to do is calculate a value from two separate rows in the same table and insert the result in the same table as a new row. I have a way of doing so but i consider it to be extremely inelegant and i'm hoping there's a better way of doing it. A description of the existing database schema (which i have control over) will help in explaining the problem:
id metric_id metric_type_id metric_name 1 80 2 Fiscal Enterprise Value Historic Year 1 2 81 2 Fiscal Enterprise Value Current Fiscal Year 3 82 2 Fiscal Enterprise value Forward Fiscal year 1 4 83 2 Fiscal Enterprise Value Forward Fiscal Year 2 5 101 3 Calendar Enterprise value Historic Year 1 6 102 3 Calendar Enterprise Value Current Fiscal Year 5 103 3 Calendar Enterprise value Forward Year 1 6 104 3 Calendar Enterprise Value Forward Year 2
Table Name: metric_type_details
id metric_type_id metric_type_name 1 1 Raw 2 2 Fiscal 3 3 Calendar 4 4 Calculated
The problem scenario is the following: Because a certain number of the securities have a fiscal year end that is different to the calendar end in addition to having fiscal data (such as fiscal enterprise value and fiscal earnings etc...) for each security i also need to store calendarised data. What this means is that if security with security_id = 3 has a fiscal year end of October then using rows with ids = 1, 2, 3 and 4 from the metrics_ladder table i need to calculate metrics with metric_id = 83, 84, 85 and 86 (as described in the metric_details table) and insert the following 4 new records into metrics_ladder:
Metric with metric_id = 101 (Calendar Enterprise value Historic Year 1) will be calculated by taking 10/12 of the value for metric_id 80 plus 2/12 of the value for metric_id 81.
Similarly, metric_id 102 will be equal to 10/12 of the value for metric_id 81 plus 2/12 of the value for metric_id 82,
metric_id 103 will be equal to 10/12 of the value for metric_id 82 plus 2/12 of the value for metric_id 83 and finally
metric_id 104 will be NULL (determined by business requirements as there is no data for forward year 3 to use).
As i could think of no better way of doing this (and hence the reason for this thread) I am currently achieving this by pivoting the relevant data from the metrics_ladder so that the required data for each security is in one row, storing the result in a new column then unpivoting again to store the result in the metrics_ladder table. So the above data in nmetrics_ladder becomes:
-- Dummy year variable to make it easier to use MONTH() function -- to convert 3 letter month to number. i.e. JAN -> 1, DEC -> 12 etc... DECLARE @DUMMY_YEAR VARCHAR(4) SET @DUMMY_YEAR = 1900;
with temp(security_id, metric_id, value) as ( select ml.security_id, ml.metric_id, ml.value from metrics_ladder ml where ml.metric_id in (80,81,82,83,84,85,86,87,88,etc...) -- only consider securities with fiscal year end not equal to december and ml.security_id in (select security_id from company_details where fiscal_year_end <> 'dec') ) insert into @calendar_averages select temppivot.security_id -- Net Income ,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[80]) +((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[81]) as [101] ,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[81]) +((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[82]) as [102] ,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[82]) +((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[83]) as [103] ,NULL as [104] -- Share Holders Equity ,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[84]) +((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[85]) as [105] ,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[85]) +((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[86]) as [106] ,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[86]) +((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[87]) as [107] ,NULL as [108] -- Capex -- Sales -- Accounts payable etc... .. .. from temp pivot ( sum(value) for metric_id in ([80],[81],[82],[83],[84],[85],[86],[87],[88],etc...) ) as temppivot inner join company_details cd on temppivot.security_id = cd.security_id
********* END SQL *********
The result then needs to be unpivoted and stored in metrics_ladder.
And FINALLY, the question! Is there a more elegant way of achieving this??? I have complete control over the database schema so if creating mapping tables or anything along those lines would help it is possible. Also, is SQL not really suited for such operations and would it therefore be better done in C#/VB.NET.
Suppose a very basic question, but how can I easily update values in a record. I can easily READ the value with this code.
Set Rsx = Server.CreateObject("ADODB.RecordSet") sSQL= "SELECT * from prodfeatures" Rsx.Open sSQL, sDSN, adOpenStatic, adLockReadOnly, adCmdText
While Not Rsx.eof i=i+1 mte(i)=Rsx("id") mfnum(i)=Rsx("featureother1") Rsx.movenext Wend
..now I try an UPDATE sql to insert the values from the array into another field in the corresponding records - I can get it to work, but it is very stupid done. Actually I must open and close the database for every record to get it to work:
For j=1 To i
Set Rsx = Server.CreateObject("ADODB.RecordSet") sSQL= "UPDATE prodfeatures SET featurenum=" & mfnum(j) & " WHERE id=" & mte(j) Rsx.Open sSQL, sDSN, adOpenStatic, adLockReadOnly, adCmdText next
...I suppose this is silly programming, but it actually works well, but it takes a really lot of time to execute...
I have a pivot transform that pivots a batch type. After the pivot, each batch type has its own row with null values for the other batch types that were pivoted. I want to group two fields and max() the remaining batch types so that the multiple rows are displayed on one row. I tried using the aggregate transform, but since the batch type field is a string, the max() function fails in the package. Is there another transform or can I use the aggragate transform another way so that the max() will work on a string?
What the right table setup is when you have a table with values that will never change.
I'm building a small project for our sales team so they can track their leads & customers. I have a table called "Contacts", which contains the names of every prospect they've ever reached out to. In that table is a field called "ContactTypeId", which is a reference to a table called "ContactTypes".
My ContactTypes table looks like this:
ContactTypeId | Name | Description ---------------------------------------------- 1 | Lead | A contact who has shown interest in our service 2 | Qualified Lead | A more deeply engaged, sales-ready contact 3 | Customer | A contact paying for our services
So, I set it up this way because I figured it was cleaner to associate a ContactTypeID# to each Contact in the Contacts table, rather than actually spelling out "Lead", "Customer", etc for each record in the Contacts table.
But is this right? If these values in my ContactTypes table are NEVER going to change, should I still have this table at all or is it better to just insert "Lead", "Qualified Lead", or "Customer" into the Contacts table? Just looking to take the correct approach.
The columns fro which we do not enter any values.Fro eg in my table i did not insert any values to Balance col,in my table. In SQL 2005,management studio,when i said modify table and changed the default to 0 from NULL and also unchecked the checkbox saying Allow Nulls.
I did the above changes and said save.It gave an error saying
'AccountsBalance' table - Unable to modify table. Cannot insert the value NULL into column 'Balance', table 'AccountBalanceSheet.dbo.Tmp_AccountsBalance'; column does not allow nulls. INSERT fails. The statement has been terminated.
How would I copy entire rows within a table and change one value?
insert into user (user_id,account_id,user_type_cd,name,e_mail_addr,login_failure_cnt,admin_user,primary_user) select * from pnet_user where account_id='DDD111'
but now I want to change DDD111 to DDD222 on the inserted entries.
Hi, all, does anyone know of a way to change default values inside of a table without affecting existing values?
I have a table with default = 0 in usermask column. Some rows already have non 0 values in them. I now need to change the default to 1 but still want the non 0 values be there. I also want the new default to take effect when new rows are entered. What's the best and quickest way to do it?
I had created a database named "Company-Data" that contains a table named "tblStock". This table contains several columns such as ID, Product, Quantity. The datatype for the column "Quantity" is INT.
I had entered 100 records to the table, now I want to change the datatype for the column "Quantity" from INT to MONEY.
How can we do this without loosing the data that has been entered the the column previously.
When I use calculation in generating reports, I get "NaN" value in preview , I wanted to avoid this and display "0" if the result of calculation is null or infinity..
I used the below calculation
=CDbl(Fields!POST_TEST_COUNT.Value/Fields!ENROLL_Actual.Value), here i taken input as percentage for every field.
Please let me know how to avoid "NaN" and "infinity", while displaying in reports
when ever i see the prview NAN values are displayed and remaining fields are shows appropriate query results like percentage values Like 98%.00 some thing but i have a problem with NAN values i need to change those NAN values to 0
