Getting The Average Results Based On Time.
Hello~,
The table has columns like this.
________________________________
time smalldatetime
value1 int
value2 int
----------------------------------------------------------
for example,
....
'2006-11-16 12:00:00',100,200
'2006-11-16 13:00:00',110,210
'2006-11-16 14:00:00',120,220
....
The record is inserted at every hour.
I want get daily,monthly,yearly average and display the result ordered by time.
View Complete Forum Thread with Replies
Related Forum Messages:
How To Convert UTC Time (retrieved From SQL) To Local Time In Reporting Services Based On Time Zone
Hi all, I have created a report in SSRS 2005 which is being viewed by users from different Time Zones. I have a dataset which has a field of type datetime (UTC). Now I would like to display this Date according to the User Time Zone. For example if the date is August 07, 2007 10:00 AM UTC, then I would like to display it as August 07, 2007 03:30 PM IST if the user Time Zone is IST. Similarly for other Time Zones it should display the time accordingly. Is this possible in SSRS 2005? Any pointers will be usefull... Thanks in advance sudheer racha.
View Replies !
Moving Average Using Select Statement Or Cursor Based?
ID DATE(dd/mm/yy) TYPE QTYIN COST_IN_AMT COST_OUT_AMT(MOVING AVERAGE) 1 01/01/2007 PURCHASE 10 1000 2 01/01/2007 PURCHAES 5 1100 3 01/01/2007 SALES -5 *TobeCalculated 4 02/01/2007 Purchase 20 9000 5 02/01/2007 SALES -10 *TobeCalculated 5 02/01/2007 purchase 50 8000 6 03/01/2007 Sales -10 *TobeCalculate 7 01/01/2007 Purchase 20 12000 I have a table when user add new sales or puchase will be added to this table ITEM_TXNS. The above date is part of the table for a ProductID . (The field is removed here) In order to calculate the balance amount using moving average, I must calculated the cost_out_amt first on the fly. When user add new sales I also need to determine the cost/unit for a product id using moving average. The problem is I can not just use sum, because i need to determine cost_out_amt for each sales first which will be calculated on the fly. The reason i dont store the cost_out_amt (instead calculate on the fly) because User could Edit the previous sales/purchase txn or Insert new sales for a previous date. Example THe record with ID 9. By Adding this txn with ID 9, would cause all the cost_out_amt will be incorrect (Using moving Average) if i store the cost_amout_out on entrying txn and need to be recalculated. Instead I just want to calculate on the fly and able to determine the cost avr for a specific point of time. Should I just use Cursor and loop all the record and calculate the cost or maybe I can just use on Select Statement?
View Replies !
Matrix Report:Adding Average Column/Sorting Based On Last Month/Conditional Formating
I have a matrix report with 2 column SaleAmount and ProfitAmounts by Month like Sale Profit Dealer 5/1/2007 6/1/2007 7/1/2007 5/1/2007 6/1/2007 7/1/2007 A 100 200 300 20 25 15 B 200 250 50 30 45 19 how can i do following 3 things 1)Add Total column for Sale and Average column for Profit 2)Sort report by lastMonth of Sale (here 7/1/2007) High to low 3)if last month of sale(here 7/1/2007) is less than second last month here (6/1/2007) whole row should be red thanks
View Replies !
Average Turnaround Time - Datediff() Help
Hi everyone, I need some help with creating a report that calculates the average turnaround time in days that it takes for units to return from trips destined to a location. The database that I am working with lists a trip each time a unit is dispatched to a destination, and then another trip is created for the units return. In the example below I am trying to calculate the number of days that it takes for a unit to return to Vancouver by calculating the difference between the departure date from Vancouver and the arrival date back into Vancouver. I then need to calculate the average number of days that it takes for a unit to return from a trip. See sample data below. UNIT -- TRIP -- START LOCATION --START DATE--FIN LOCATION--FIN DATE ================================================== ======= U12 ----001 --- VANCOUVER -------FEB 10 ------ ONTARIO ----- FEB 15 U10 ----002 --- VANCOUVER -------FEB 13 ------ ONTARIO ----- FEB 18 U12 ----003 --- ONTARIO ----------MARCH 13 --- VANCOUVER -- MARCH 18 U10 ----004 --- ONTARIO ----------MARCH 1 ---- VANCOUVER ---MARCH 6 Unit U12 took 36 days to return back to Vancouver Unit U10 took 21 days to return back to Vancouver Therefore based on the two trips it takes an average of aproximately 28.5 days for a unit to return from trips destined to Ontario.
View Replies !
Average Processing Time With Your Cubes
I am running into a barrier and need to understand the average length of time that a fully optimized data cube should take to process. We are currently running an average of 15 to 20 minutes per cube, with average of 2000 aggregations, 25% performance increase, and approximately 2 million rows, with around 40 dimensions and 30 measures. I personally think this is a pretty good time to process. However, I am being challenged to reduce this time frame. In theroy I can't possibly see it getting below where we currently are. SO I am reaching out to the group of guru's... What is your average length of time to process your Data Cubes? Please respond to me at ken.kolk@medcor.com I would greatly appreciate it and need the averages from the field.
View Replies !
Determine Average Time Between Events
I am trying to figure out how to calculate the average time between phone calls for a user. The initial requirement is to calcualte this on all calls for an entire month but I would guess that would lead to other periods as well, such as daily, weekly, etc. One hurdle is what to do when going from one day to the next. I could possibly just week out any times between calls that are greater than a certain amount of time to address that. Any way, here is a small sample of what I'll be dealing with. Any ideas on how to approach this or get it to work would be greatly appreciated. Code Block CREATE TABLE #avetime (origdate datetime, duration_seconds int, duration_minutes decimal(9,2), phoneuser varchar(20), calltype varchar(15)) INSERT into #avetime VALUES ('Jan 7 2008 9:19AM', 21, 0.4, 'Coleman', 'Long Distance') INSERT into #avetime VALUES ('Jan 7 2008 9:19AM', 48, 0.8, 'Coleman', 'Long Distance') INSERT into #avetime VALUES ('Jan 7 2008 9:33AM', 81, 1.4, 'Coleman', 'Long Distance') INSERT into #avetime VALUES ('Jan 7 2008 9:35AM', 87, 1.5, 'Coleman', 'Internal') INSERT into #avetime VALUES ('Jan 7 2008 9:37AM', 27, 0.5, 'Coleman', 'Internal') INSERT into #avetime VALUES ('Jan 7 2008 9:42AM', 13, 0.2, 'Coleman', 'Long Distance') INSERT into #avetime VALUES ('Jan 7 2008 9:43AM', 84, 1.6, 'Coleman', 'Long Distance') INSERT into #avetime VALUES ('Jan 7 2008 12:00PM', 914, 15.2, 'Coleman', 'Long Distance') INSERT into #avetime VALUES ('Jan 7 2008 2:24PM', 3, 0.1, 'Coleman', 'Internal') INSERT into #avetime VALUES ('Jan 8 2008 10:13AM', 21, 0.4, 'Coleman', 'Long Distance') INSERT into #avetime VALUES ('Jan 8 2008 10:33AM', 482, 8.0, 'Coleman', 'Long Distance') INSERT into #avetime VALUES ('Jan 8 2008 11:49AM', 56, 0.9, 'Coleman', 'Long Distance')
View Replies !
User-Defined Functions To Calculate Average And Relative Percent Difference(RPD):How To Designate The Variables Of Average/RPD
Hi all, This is my first time to do T-SQL User-Defined Functions programming. I just learned some basic DDL and DML statements. But I do not know how to designate the variables of the User-Defined Function. I have 3 dbo tables in SQL Server Management Studio Express (SSMSE): dbo.Projects: ProjectID ProjectName LabName 1 Blueriver ALSI 2 Greentree GPL 3 Redrock STL-NJ NULL NULL NULL dbo.Samples: SampleID SampleName Matrix SampleType Chemical ProjectID 1 Blueriver01 Water Primary VOCs 1 2 Blueriver02 Water Duplicate VOCs 1 3 Blueriverr03 Water QA VOCs 1 4 Greentree11 Soil Primary VOCs 2 5 Greentree12 Soil Duplicate VOCs 2 6 Greentree13 Soil QA VOCs 2 NULL NULL NULL NULL NULL NULL dbo.LabTests: AnalyteID AnalyteName Result Unit SampleID 1 Acetone 120.80 ug/L 1 2 Benzene 25.60 ug/L 1 3 Trichloroethene 13.00 ug/L 1 4 Xylenes 0.00 ug/L 1 5 Acetone 90.70 ug/L 2 6 Benzene 31.40 ug/L 2 7 Trichloroethene 19.20 ug/L 2 8 Xylenes 2.00 ug/L 2 9 Acetone 140.30 ug/L 3 10 Benzene 21.50 ug/L 3 11 Trichloroethene 22.20 ug/L 3 12 Xylenes 0.00 ug/L 3 13 Acetone 222.10 ug/Kg 4 14 Benzene 10.30 ug/Kg 4 15 Trichloroethene 30.20 ug/Kg 4 16 Xylenes 50.70 ug/Kg 4 17 Acetone 211.90 ug/Kg 5 18 Benzene 16.40 ug/Kg 5 19 Trichloroethene 34.70 ug/Kg 5 20 Xylenes 60.00 ug/Kg 5 21 Acetone 220.30 ug/Kg 6 22 Benzene 13.20 ug/Kg 6 23 Trichloroethene 32.00 ug/Kg 6 24 Xylenes 55.50 ug/Kg 6 NULL NULL NULL NULL NULL The Average of chemical/analyte in 2 samples is defined as: Average = Abs(the result of primary sample + the result of duplicate sample)/2. Average = Abs(the result of primary sample + the result of QA sample)/2. Average = Abs(the result of duplicate sample + the result of QA sample)/2. The RPD of chemical/analyte in 2 samples is defined as: RPD = Abs(the result of primary sample - the result of duplicate sample)/ Average RPD = Abs(the result of primary sample - the result of QA sample)/ Average RPD = Abs(the result of Duplicate sample - the result of QA sample)/ Average I want to calculate the average and RPD of each chemical/analyte for the following 3 pairs of the related samples: (i) Between the primary and duplucate samples (ii) Between the primary and QA samples (iii) Between the duplucate and QA samples. In the Table "dbo.Projects", ProjectID is the primary key. In the Table "dbo.Samples", SampleID is the primary key and ProjectID is the foreign key. In the Table "dbo.LabTests", AnalyteID is the primary key and SampleID is the foreign key. I do not know how to designate the variable of each result of analyte in a sample and use it to calucalte the Average and RPD for each pair (i.e. for (i), (ii), or (iii)). For example: I do not know how to designate the following 2 results: dbo.LabTests: AnalyteID AnalyteName Result Unit SampleID 1 Acetone 120.80 ug/L 1 5 Acetone 90.70 ug/L 2 Please help and advise me how to designate the 2 results in T-SQL User Defined Functions to calculate the Average and RPD. Thanks in advance, Scott Chang
View Replies !
DISTINCT Results Based On The Value Of One Col?
Here's one thats had me and a coworker puzzled. Hopefully it's something simple: TABLE A: (log of messages) userid, int date, datetime message, varchar(50) Table A Data: 1, 6/18/2008 @ 2:32:41, This is my message 1, 6/18/2008 @ 2:31:02, This is my message 1, 6/17/2008 @ 7:34:26, This is another message 2, 6/18/2008 @ 2:32:41, This is not his message 2, 6/16/2008 @ 11:21:32, This is my message TABLE B: (List of users) userid, int name, varchar(100) Table B Data: 1, John 2, Mike I want to extract the most recent message logged per user, i.e.: name | date | message --------|---------------------|------------------------- John | 6/18/2008 @ 2:32:41 | This is my message Mike | 6/18/2008 @ 2:32:41 | This is not his message. I have been unable to come up with a query that can return just the one value. I've tried variants of: SELECT DISTINCT b.name, a.date, a.message FROM a INNER JOIN b ON a.userid = b.userid including sub-queries and even played with the visual diagrams trying to design this in Enterprise Manager but none of the combinations I tried work. Is there an easy way to do this via a query? I don't have experience with stored procedures. Would that be necessary? TIA
View Replies !
UPDATE Based On Results?
Hello, I'm trying to update FOR_SORTING to 1, for all instances of a RESPONSE_ID when QUESTION_ID = YESNO and RESPONSE = Yes. So, for below I'm trying to update FOR_SORTING to 1, for RESPONSE_ID that has Question_ID that is equal to YESNO and Response equals Yes. No. RESPONSE_ID QUESTION_ID RESPONSE FOR_SORTING 1. 1 RATING Incredible 2. 1 YESNO Yes 1 3. 2 RATING Incredible 4. 2 YESNO No The Table is called LSN_RESPONSE_DETAILS I tried the following, but it only updates the one row. UPDATE LSN_RESPONSE_DETAIL SET FOR_SORTING = '1' WHERE QUESTION_ID = 'YESNO' and 'RESPONSE = 'Yes' Any idea's?
View Replies !
Latches - Average Waits, Average Duration?
I have been monitoring the average number of latch waits and the average duration of each latch wait on my primary SQL box. On average, I see around 30 latch waits per second with an average duration of under 1 second. What type of average are you seeing on your production machines? What is a normal average? I have tried researching for suggested values on these two counters, but I haven't turned up any information. Thanks, MV
View Replies !
Display Results Based On Query
Hi, I don't know if anyone has encountered this before but here goes: I've a select clause below: result = "Select * from person where LocalName LIKE N'" + queryLocalName + "'" queryLocalName is an input field that allows the user to search for non-English characters in the database. What I'm wondering is what kind of effect is the N in the where clause is having? I can't seem to get it to work when doing it via the web. I've tested in the database itself, got it to work using the SQL Analyser but when testing on the web, it can't find because ? are appearing in the result.
View Replies !
Entity Results Based On Parameter
In the report designer cant we createb report paramter to create an table using new named query. when I deploy the report model and when the power user try to access the entity it should prompt an prompt asking for which quarter information does he need. Thats is when an end user click on the entity it should prompt for paramter and based on the paramter the results of entity should be avaialble. In some cases based on user i want to limit the number of fields in an entity can an user see. Regards, Navin
View Replies !
Sql Query Results Based On Column Value?
I have a stored procedure which selects results based on some date calculations. In my table I have a status column and two date fields (Approval Date and Signature Date) If the value in the status column says approved I want to select results where approval date - signature date is less than a certain number of days. If the status is naything other than approved i want to select results where sysdate - signature date is less than the given number of days. How can i achieve this?
View Replies !
Returning Results Based On Dates
I want to write a stored procedure that takes two three paramters based on a case statement determining which values are null something like this Select InformationDate From thisTAble Where If @dateValue IsNot @Null Informationdate = @dateValue Else Where In this second where cluase I want to be able to pull out all the results based on a date range and i am not sure how the syntaz would go InfomationDate IsBettween @daterangeFrom @dateRangeTwo Any help on this i hope i was clear...thank you!
View Replies !
Unique Results Based On Popularity Voting
I've managed to get my query to this: Product | Color | Votes ======== ======= ======= Bus Red 10 Bus Blue 5 Train Blue 1Car Red 1 Car Blue 1 I need the most popular color of the product based on the votes for each product, so MAX(Votes) GROUP BY Product solves the Bus and the Train, but I still need a result for the Car? Not really that bothered which Color is picked up, do I need to run another query, because I need another table to join to the results. Any help would be greatly appreciated. Thanks, Pete.
View Replies !
Assign A Variable Based Upon Query Results...how To Do It?
I have the following code which is incomplete. Where it says: txtVendorID = I need it to equal the results of the field VendorID from my query...here is my code. What do I need to add there? Dim cmdSelect As SqlCommandDim intRecordIDintRecordID = Request.QueryString("RecordID")strConn = ConfigurationManager.AppSettings("conn")conn = New SqlConnection(strConn)cmdSelect = New SqlCommand("spMfgRepListAddaspxByRecordID", conn)cmdSelect.CommandType = CommandType.StoredProcedurecmdSelect.Parameters.AddWithValue("@RecordID", intRecordID)conn.Open()cmdSelect.ExecuteReader()txtVendorID.Text = conn.Close()
View Replies !
Display Results Based On Percentage Match
Dear Pals, I have small requirement in my project. I need to display the results of the WHERE clause based on percentage/ranking of exact match. I mean the result set should be displayed based on percentage match. For example i have the below table. create table test ( id int identity(1,1) primary key, ename varchar(10) ) insert into test(ename) select 'REG' insert into test(ename) select 'xyz' insert into test(ename) select 'abc' insert into test(ename) select 'Reg' insert into test(ename) select 'Regsxysn' insert into test(ename) select 'psReg' I need the output something similar as below REG Reg Regsxysn psReg I have tried out with full text indexing but i could'nt get the required output. Any suggestions would be appreciated. Thanks in Advance.
View Replies !
Lookup Based On Results Of Previous Lookups
i'd like to use ssis on a certain project but am concerned that one of my transformations needs lookup results to be based on actions taken on previous lookups and that the toolkit doesnt really offer something like that. so, i have a dataflow whose first component extracts certain kinds of data from an xml document. each row returned by the latter needs a lookup but the results of that lookup may dictate a certain kind of update. The next row's lookup may need to be influenced by the previous row's update. So I think I have two challenges, 1) combining a lookup and update, 2) making sure the buffer architecture completes one lookup and update before the next lookup begins.
View Replies !
Listing Results Based On The Starting Alphabet
Hi, I have a report which lists out the employees' details. I need A-Z links above the report, On clicking on an alphabet, say "C", should display all the employee details whose name starts with the selected alphabet. In the stored procedure we can accept the character and return back those results. But it is not a drill report and we need the result in the same report. Is there any way so that on clicking each link, the output will be shown in the same report. Any help is appreciated. Thanks in advance, Sonu
View Replies !
Display Results Based On Percentage Match
Dear Pals, I have small requirement in my project. I need to display the results of the WHERE clause based on percentage/ranking of exact match. I mean the result set should be displayed based on percentage match. For example i have the below table. create table test ( id int identity(1,1) primary key, ename varchar(10) ) insert into test(ename) select 'REG' insert into test(ename) select 'xyz' insert into test(ename) select 'abc' insert into test(ename) select 'Reg' insert into test(ename) select 'Regsxysn' insert into test(ename) select 'psReg' I need the output something similar as below REG Reg Regsxysn psReg I have tried out with full text indexing but i could'nt get the required output. Any suggestions would be appreciated. Thanks in Advance.
View Replies !
Fixing My Table Based On Dbcc Showcontig Results
Can someone please help me interpret this result set below and suggeston way I can speed up my table? What changes should I make?DBCC SHOWCONTIG scanning 'tblListing' table...Table: 'tblListing' (1092914965); index ID: 1, database ID: 13TABLE level scan performed.- Pages Scanned................................: 97044- Extents Scanned..............................: 12177- Extent Switches..............................: 13452- Avg. Pages per Extent........................: 8.0- Scan Density [Best Count:Actual Count].......: 90.17% [12131:13453]- Logical Scan Fragmentation ..................: 0.86%- Extent Scan Fragmentation ...................: 2.68%- Avg. Bytes Free per Page.....................: 1415.8- Avg. Page Density (full).....................: 82.51%DBCC execution completed. If DBCC printed error messages, contact yoursystem administrator.Thank you.
View Replies !
Need An Average By Year Of An Average By Month
I have a temp_max column and a temp_min column with data for every day for 60 years. I want the average temp for jan of yr1 through yr60, averaged... I.E. the avg temp for Jan of yr1 is 20 and the avg temp for Jan of yr2 is 30, then the overall average is 25. The complexity lies within calculating a daily average by month, THEN a yearly average by month, in one statement. ?confused? Here's the original query. accept platformId CHAR format a6 prompt 'Enter Platform Id (capital letters in ''): ' SELECT name, country_cd from weather_station where platformId=&&platformId; SELECT to_char(datetime,'MM') as MO, max(temp_max) as max_T, round(avg((temp_max+temp_min)/2),2) as avg_T, min(temp_min) as min_temTp, count(unique(to_char(datetime, 'yyyy'))) as TOTAL_YEARS FROM daily WHERE platformId=&&platformId and platformId = platformId and platformId = platformId and datetime=datetime and datetime=datetime GROUP BY to_char(datetime,'MM') ORDER BY to_char(datetime,'MM'); with a result of: NAME_________________CO -------------------- -- OFFUTT AFB___________US MO______MAX_T _____AVG_T__MIN_TEMTP_TOTAL_YEARS -- ---------- ---------- ---------- ----------- 01_________21______-5.31________-30__________60 02_________26______-2.19______-28.3__________61 03_______31.1_______3.61______-26.1__________60 04_______35.6______11.07______-12.2__________60 05_______37.2_______17.2_______-3.3__________60 06_______41.1______22.44__________5__________60 07_______43.3______24.92________7.2__________60 08_______40.6______23.71________5.6__________60 09_________40______18.84_______-2.2__________59 10_______34.4_______12.5_______-8.9__________59 11_________29_______4.13______-23.9__________60 12_________21______-2.52______-28.3__________60
View Replies !
Filtering Out 1 Time Dimension Based On The Selection In Another Time Dimension
Hi! Need some help building a query that does the following : I have 2 Time Dimensions ; Time (Transdate) and ClosedDate (ClosedDate) In my report/query, if [Time].CurrentMember = [Time].[YMD].[YMD].[2006].[200610].[20061031] I want to FILTER out all ClosedDate < [ClosedDate].[YMD].[YMD].[2006].[200610].[20061031] Both Time Dimensions are Year -> Month -> Day and have the same Members. I have every option available, using calculated Members and/or Measures to do this. The report I'm creating is Aging of Receivables : Balance / 30 days / 60 days / etc.. But for the Aging, I need to filter like explained above. Appreciate all help! Regards, Stian Bakke
View Replies !
Filtering Based On Time Of Day
While using a DateTime field, is there an easy way of filtering based on time of day? Ex. Anything that happened after 3:00 PM on any given day? The easiest way I've found of doing so is : Code: SELECT * FROM MyTable WHERE ({ fn MOD(DATEDIFF(ss, '01/01/2000 00:00:00', [Date]), 3600 * 24) } >= DATEDIFF(ss, '00:00:00', '15:00:00')) To me that seems a bit complicated... but it works... Anyone that knows of a better way any help would be appreciated (or if you don't think there's a better way knowing that would help as well) -MBirchmeier
View Replies !
Dynamiclly Remove Duplicate Rows From Results Table Based On Column Data?
I have a results table that was created from many different sources in SSIS. I have done calculations and created derived columns in it. I am trying to figure out if there is a way to remove duplicate rows from this table without first writing it to a temp sql table and then parsing through it to remove them. each row has a like key in a column - I would like to remove like rows keeping specific columns in the resulting row based on the data in this key field. Ideas? Thanks, Ad.
View Replies !
Results Produce A Single Record Based Off Of Parameters. Want To Change It So It Returns Multiple Records.
I have a query that will return one record as its results if you provide two variables: @login and @record_date. This works great if you only want one result. However, now what I want to do is not provide those variables and get the result set back for each login and record_date combination. The hitch is that there are several other variables that are built off of the two that are supplied. Here is the query: DECLARE @login char(20), /*This sets the rep for the query.*/ @record_date datetime, /*This is the date that we want to run this for.*/ @RWPY decimal(18,2), /*This is the required wins per year.*/ @OCPW decimal(18,2), /*This is the opportunities closed per week.*/ @OACW decimal(18,2), /*This is opportunities advanced to close per week.*/ @TOC decimal(18,2), /*This is the total number of opportunities in close.*/ @OANW decimal(18,2), /*This is opportunities advanced to negotiate per week.*/ @TON decimal(18,2), /*This is the total number of opportunities in negotiate.*/ @OADW decimal(18,2), /*This is the opportunities advanced to demonstrate per week*/ @TOD decimal(18,2), /*This is the total number of opportunities in demonstrate.*/ @OAIW decimal(18,2), /*This is the opportunities advanced to interview per week.*/ @TOI decimal(18,2), /*This is the total number of opportunities in interview.*/ @OCW decimal(18,2), /*This is the opportunities created per week.*/ @TOA decimal(18,2) /*This is the total number of opportunities in approach.*/ SET @login = 'GREP' SET @record_date = '12/18/2007' SET @RWPY = (SELECT ((SELECT annual_quota FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)/(SELECT target_deal FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date))) SET @OCPW = (SELECT @RWPY/weeks FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date) SET @OACW = (SELECT @OCPW/cls_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date) SET @TOC = (SELECT @OACW*(cls_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date) SET @OANW = (SELECT @OACW/neg_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date) SET @TON = (SELECT @OANW*(neg_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date) SET @OADW = (SELECT @OANW/dem_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date) SET @TOD = (SELECT @OADW*(dem_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date) SET @OAIW = (SELECT @OADW/int_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date) SET @TOI = (SELECT @OAIW*(int_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date) SET @OCW = (SELECT @OAIW/app_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date) SET @TOA = (SELECT @OCW*(app_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date) SELECT loginname, CAST(@TOA AS decimal(18,1)) AS [Opps in Approach], app_time AS [Approach Average Time], app_perc_adv AS [Approach Perc Adv], CAST(@TOI AS decimal(18,1)) AS [Opps in Interview], int_time AS [Interview Average Time], int_perc_adv AS [Interview Perc Adv], CAST(@TOD AS decimal(18,1)) AS [Opps in Demonstrate], dem_time AS [Demonstrate Average Time], dem_perc_adv AS [Demonstrate Perc Adv], CAST(@TON AS decimal(18,1)) AS [Opps in Negotiate], neg_time AS [Negotiate Average Time], neg_perc_adv AS [Negotiate Perc Adv], CAST(@TOC AS decimal(18,1)) AS [Opps In Close], cls_time AS [Close Average Time], cls_perc_adv AS [Close Perc Adv] FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date Here is some sample data to use with this. With this sample data what I want to get back is a total of 30 records in the result set each with its data specific to the login and record_date of that returned record. CREATE TABLE #pipelinehist ( glusftboid int IDENTITY(1,1) NOT NULL, record_date datetime NOT NULL, loginname char(20) NOT NULL, app_new float NOT NULL, app_time float NOT NULL, app_perc_adv float NOT NULL, int_time float NOT NULL, int_perc_adv float NOT NULL, dem_time float NOT NULL, dem_perc_adv float NOT NULL, neg_time float NOT NULL, neg_perc_adv float NOT NULL, cls_time float NOT NULL, cls_perc_adv float NOT NULL, target_deal money NOT NULL, annual_quota money NOT NULL, weeks int NOT NULL ) ON [PRIMARY] INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'AREP', 56.8, 26.9, 0.57, 29.5, 0.47, 20, 0.67, 80.7, 0.53, 2.1, 0.97, 2194.93, 575000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'BREP', 33.2, 0.5, 0.9, 7.7, 0.77, 8, 0.77, 9.2, 0.6, 7.7, 0.64, 971.1, 330000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'CREP', 210.2, 0.3, 0.87, 6.6, 0.5, 13.7, 0.4, 16.3, 0.43, 1.5, 0.91, 461.25, 330000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'DREP', 47.6, 5, 0.53, 33.3, 0.6, 57.5, 0.53, 50, 0.7, 1.5, 1, 2045.7, 575000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'EREP', 75.3, 110.9, 0.47, 36, 0.5, 17.4, 0.87, 20.3, 0.6, 7.2, 0.83, 2021.74, 775000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'FREP', 17.2, 23.3, 0.73, 6.8, 0.8, 6.3, 0.93, 29.7, 0.67, 15.5, 0.83, 2218.95, 575000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'GREP', 105.4, 67, 0.2, 32.9, 0.43, 18.5, 0.67, 8.9, 0.77, 3.5, 0.93, 1838.91, 400000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'HREP', 116.4, 118.5, 0.33, 30.9, 0.77, 46.3, 0.77, 46.3, 0.6, 0.9, 0.97, 1735.13, 1150000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'IREP', 143.3, 9, 0.77, 96, 0.17, 21.6, 0.77, 39.9, 0.43, 0.9, 0.93, 1385.43, 400000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'JREP', 179.4, 66.7, 0.7, 67.6, 0.1, 41.4, 0.6, 20.2, 0.8, 14, 0.7, 1563.76, 330000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'KREP', 107.6, 38.2, 0.23, 47.5, 0.47, 21.3, 0.77, 9.6, 0.73, 2.1, 0.83, 2120, 575000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'LREP', 18.6, 8.3, 0.87, 23.2, 0.57, 2.6, 0.87, 12.2, 0.67, 1, 1, 1229.02, 330000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'MREP', 4, 46.2, 0.6, 26.7, 0.57, 8.1, 0.87, 1.7, 0.9, 1.4, 1, 1091.22, 350000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'NREP', 54, 21.6, 0.57, 1.7, 0.77, 11, 0.8, 7.4, 0.9, 49, 0.47, 3240.68, 1300000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'OREP', 37.6, 24.4, 0.57, 50.1, 0.43, 6.7, 0.87, 15.6, 0.73, 0.9, 0.97, 1163.48, 330000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'AREP', 57.2, 32.5, 0.6, 29.5, 0.47, 20, 0.67, 85.6, 0.5, 2.1, 0.97, 2194.93, 575000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'BREP', 33.9, 0.5, 0.93, 7.8, 0.73, 8.3, 0.77, 9.2, 0.6, 7.7, 0.64, 971.1, 330000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'CREP', 152.1, 0, 0.87, 4.3, 0.67, 9.7, 0.47, 15.7, 0.47, 1.8, 0.85, 396.43, 330000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'DREP', 80.5, 9.8, 0.5, 40.7, 0.57, 68.3, 0.43, 64.2, 0.57, 1.5, 1, 2045.7, 575000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'EREP', 61, 92.1, 0.5, 31, 0.53, 16.9, 0.83, 17.7, 0.6, 7.3, 0.83, 2318.04, 775000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'FREP', 19.4, 21.1, 0.7, 5.3, 0.77, 2.2, 0.93, 33.3, 0.7, 9.7, 0.87, 1937.17, 575000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'GREP', 81.7, 40.5, 0.3, 33, 0.37, 18.5, 0.67, 8.9, 0.77, 3.5, 0.93, 1838.91, 400000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'HREP', 128.6, 115.7, 0.3, 30.9, 0.77, 46.3, 0.77, 48.8, 0.6, 0.9, 0.97, 1728.29, 1150000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'IREP', 100.9, 3.4, 0.77, 86.2, 0.27, 18, 0.8, 54.7, 0.37, 0.9, 0.93, 1385.43, 400000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'JREP', 179.4, 66.7, 0.7, 63.5, 0.1, 41.4, 0.6, 20.2, 0.8, 14, 0.7, 1563.76, 330000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'KREP', 285.2, 36.5, 0.1, 46, 0.43, 24.2, 0.73, 9.6, 0.73, 2.1, 0.83, 2120, 575000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'LREP', 17.6, 7.3, 0.9, 21.5, 0.57, 1.7, 0.87, 12.2, 0.67, 1, 1, 1250.54, 330000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'MREP', 26.7, 46.2, 0.6, 26.7, 0.57, 8.1, 0.87, 1.7, 0.9, 1.3, 1, 979.7, 350000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'NREP', 61.6, 20.8, 0.5, 1.7, 0.77, 11, 0.8, 7.4, 0.9, 49, 0.47, 3240.68, 1300000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'OREP', 31.6, 16.9, 0.63, 50.1, 0.43, 7.2, 0.87, 19.5, 0.7, 0.9, 0.97, 1303.48, 330000, 50)
View Replies !
How To Delete Records Based On Time?
Hello I want to delete my record after ten days of the Posted date(field)..how do I do it? I'm able to insert date(short date , long time) to the database. I'm using sql server and C#. this is what I have so far.I would appreciate your help.. Thanks!!!<%@ Page Language="C#"%> <%@ Import Namespace="System.Data.SqlClient" %> <script runat=server> void Page_Load(Object sender , EventArgs e) { SqlConnection conPubs; string strDelete; SqlCommand cmdDelete; conPubs = new SqlConnection( @"Server=localhost;Integrated Security=SSPI;database=Book" ); strDelete = "Delete tblbook Where Posted_Date =???????????"; cmdDelete = new SqlCommand( strDelete, conPubs ); conPubs.Open(); cmdDelete.ExecuteNonQuery(); conPubs.Close(); Response.Write("Records Deleted!"); } </script> // </script> <html> <head> </head> <body> <form runat="server"> <!-- Insert content here --> </form> </body> </html>
View Replies !
Time Based Performance Decay
I am working on an application with a sql server backend. the sqlperformance always begins great, but decays quickly over time. if irestart the sql service the performance shoots back up. Thedegredation is gradual and slows to a crawl after about 8 days. Duringthis time I monitor several values from the sysperfinfo table and seesome interesting results. for example, i see the page splits/sec risefrom single digits to ~8000 after 6 days. i have tried rebuildingindexes to reduce this number but it does not go down--ever. i havetried dozens of solutions (including new hardware) none of which seemto fix the problem.i can provide more details if anyone is willing to helpthx.Noah Peters
View Replies !
Time Based Backup && Recovery
hello, i have taken a backup at 3pm , some data was entered in an xx table at 5pm, which was lost due to some reasons at 530pm, now if i want to get back the data what i should do.. as we have a concept of time based recovery in oracle what is the method used here to get my data back i have taken my second backup at 7pm its an imm. requirement for me just help me out... thnx & regards pavan
View Replies !
Time Based Access Rights...
Hi All, Is there a way in SQL Server 7.0 or 2000, where I can grant/deny/revoke access rights on a database objects like Table, Stored Procedures for a particular time of the day. Example: I want to prevent user A from acessing Table x and Stored Procedure Y from 9Am to 12 noon everyday. After 12 Noon till 8.59 AM he can have access to Table x and Stored Procedure Y. Is there a way to do this at SQL Server level. Thanks Sri
View Replies !
Time Based Data Type
Right now i'm developing a time attendance system. So in that case i'll be playing with time where that time represents what time an employee come to office and go home. I'm a little bit confuse which data type i'll be used in this system. Because there will be a lot of calculation according time data inside the database, such as: how many times an employee comes before 08.00, how much hours that he/she spend in the office that they, etc. Can somebody help me with this problem, what data type most suitable with this kind of system and how can I do the calculation more easily? thanx before,
View Replies !
Grouping Based On Time Series
In SQL Server 2005, I want to do a set query on the following data that results in 3 groups: Id EventName EventTime 1 First 41:40.2 2 First 41:41.6 3 First 41:43.1 4 First 41:44.4 5 Second 41:46.4 6 Second 41:48.3 7 Second 41:49.7 8 First 41:51.2 9 First 41:53.3 10 First 41:55.0 So, I want to have a query that returns one aggregate row for each of rows 1-4, 5-7 and 8-10 based on the EventName. Every time EventName 'changes' in the order that I sort it, I want to start a new grouping: Group EventName Count 1 First 4 2 Second 3 3 First 3 With this query, I could also get the Min() and Max() EventTime for each group, etc. However, this is proving difficult to do in set SQL. Obviously, if I group on EventName, then rows 1-4 *and* 8-10 will be rolled into my 'First' group. However, there is no other partitioning information that I can factor in that splits this data into *only* 3 groups, based on the order of the Event Time. I have tried the various ranking functions, but the problem persists through any combination of function, PARTITION BY and ORDER BY that I can find. Any insights would be appreciated!
View Replies !
Remove The Time In The Results
i have this query: SELECT top 1 DATEADD(year, -2, @sDate)AS ydate which returns 2005-01-01 00:00:00.000,how can i remove the time values and only display the date? thanks! Funnyfrog
View Replies !
View Of Table Based On Point In Time.
The table in question has three columns: effective_date smalldatetime territory_key int sales_rep_key int the object is to create a view of the which territory belongs to which sales rep based on a user input @date. take the following sample: eff_dt terr rep 19981001 1 1 19981001 2 5 19981101 1 2 19981201 1 3 19981216 2 6 19990101 1 4 If I want a view of the territory-rep assignments for 19981015, I can filter by effective date < @date to know that terr 1 belonged to rep 1 and terr 2 to rep 5. If however I want a view for the current date there are multiple reps for territory when all I want is to know that terr 1 belongs to rep 4 and terr 2 belongs to rep 6. I would like to create a view based strictly on this table alone. It sounds simple enough, but I seem to be missing a piece or two. Suggestions greatly appreciated.
View Replies !
Can I Select Subset Based On Time Of Entry
A table gets data every 4 minutes, I only need spread of every 15 minutes. Can I select only records spread every 15 minutes apart from this table without having to run a scheduled job every 15 minutes and loading one record closest to getdate() at that point into another table(this is how I am doing it now) Is there a better way. Please help Thanks
View Replies !
Selecting Based On A Date Excluding The Time
Hi, I was wondering how you perform a select statement based on a specific date that will show all the records no matter which times belong to the specific date. I have been having trouble with this one when using a single date, I think this is because of the time property as no records are displayed. Thanks for any help.
View Replies !
Select Data Based On Certain Time Period
Hello all, I am using SQL Server in a project where I want to fetch the records that were inserted after a time specified in my query. Suppose 10 records were inserted at 10:00 AM(morning) and 5 were inserted at 10:15 AM( 15 minutes later). I am running a query at 10:20 AM( 5 minutes after the second transaction). I need this query to be such that it selects the records inserted 10 minutes before. So it will show only the records inserted at and after 10:10 AM and willl not show those inserted at 10:00 AM. Please help me in making such a query. I am trying and I think that some Date & Time functions will help but still not able to achieve it. Thanks in advance
View Replies !
What's The Best Approach To Time-based Checking From App. To SQL Server?
Hi I have an VB.NET application connected to a SQL Server Express. I want to let the application to run in either "Normal mode" or "Holiday mode" according to current weekday is normal day or state public holiday. My approach is to find out all the public holidays in a year and enter them into a Holiday Table. Then some code in my application constantly check the current weekday against the one in the holiday table, if matches, the application goes into holiday mode. This approach is not perfect as "State public holidays" are confirmed by the state government in the current year and the coming year. So state public holidays are unconfirmed for the third year afterward. The system is required by client to support public holiday in the next 10 years. I wonder what is the best approach to this problem? Thanks
View Replies !
Calculate Update Time Based On Recordcount
I have a number of databases with large tables. I need to update them from time to time. I want to get the recordcount of the table and calculate based on that the amount of time it would take to update the table. Any idea who I can do this? I'm using coldfusion 8 with sql to do this. Any advice would be appreciate! Thanks Shuvi
View Replies !
Encrypt By Key Gives Different Results Every Call Time
Every time I call Encrypt by key I get a different result, although I am encrypting the same clear text,using the same authenticator and using the same key. I tried calling it twice inside one stored procedure and I got two different results. Declare @Temp varbinary(8000); Declare @Temp2 varbinary(8000); set @Temp = EncryptByKey(Key_GUID('MyKey'),@cleartext,1,@Authenticator); set @Temp2= EncryptByKey(Key_GUID('MyKey'),@cleartext,1,@Authenticator); Select @Temp,@Temp2; when executing this stored procedure, the value of @Temp is differnent from the result of @Temp2, and the same thing happens without using an authenticator. Is there something going behind in the encryption mechanism of SQL server?
View Replies !
Need Help Interpreting Results Of SET STATISTICS TIME ON
Hi, I used SET STATISTICS TIME ON to get execution stats for a query. I found that the CPU Time was sometimes greater than the elapsed time. How is this possible? The query does not use any parallelism since I used the query option MAXDOP 1. Is the elapsed time wait time? Is the total execution time the sum of the CPU time and elapsed time? SQL Server Execution Times: CPU time = 797 ms, elapsed time = 162 ms.
View Replies !
Excluding Time Period From Results
Hello, I would like to exclude the time period after 5.30pm and before 8.30am in my results. The time is in a 13 digit timestamp format which is the same as a standard unix timestamp with 3 digits which are microseconds. I used: dataadd(ss, TTIME/1000, '1970-01-01')AS time to create a column with a readable time in it. Here is a screenshot: http://www.abtecnet.com/timescreenshot.jpg Can anyone help me with this. Thanks very much. Andrew
View Replies !
How To Update Time Series Based On Another Table(or View)??
Hi all, I have two tables (staging and Cdate) and neither objects has any constraints. staging table has ID, date, A, B, and C fields and Cdate has id,date and day fields. I need to update/insert date from Vdate into staging where staging ID=' ' and date is null Here is the code I wrote, however, it seemed the information was updated to one date only instead of time series - Cdate contains time series in column date. Anyone can help to fix it? Thank you for the help! update s set s.date=c.date FROM cdate c join staging s on(s.id=c.id) Where s.date is null and id=2
View Replies !
Split One Row Into Multiple Rows Based On Time Elements
I'm dealing with a problem. The record information example DateTimeStart , DateTimeEnd , action , duration (seconds) 2007-02-02 10:30:22 , 2007-02-02 11:30:22 action1 , 600 what i want is for every half hour between start and end a record 10.30 action1 11.00 action1 11.30 action1 how can i create this, i'm a little stuck on this
View Replies !
Selecting Data From SQL Table Based On A Time Period
I am trying to write a stored procedure that will select information from a SQL table based on a specific time. For example I have a name field and a time field, I want to return just the names that were created between a specific time frame. ex between 3pm and 4pm. Any thoughts?
View Replies !
|
TRACKER
