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.
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.
Col1 Col2 Col3 Col4
54763.00 21 0 0
--Row1
59574.00 23 0 0
--Row2
64085.00 20 0 0
--Row3
0.0 0.00 0 0 0
--Row4
I'm trying to calculate Average of Col1 of above table based on below scenario:
CASE WHEN all the columns in the above table are “0” (as highlighted) THEN I want AVERAGE of Col1 as (Row1+Row2+Row3)/3
ELSE if at least one of the column of highlighted row has value other than “0”, THEN I want the AVERAGE of Col1 to be (Row1+Row2+Row3+Row4)/4
I work for a school district and new requirement we were just given for scoring some student scores.
Everything will currently be in one table to keep track of students test scores for various things. This table will contain students information and a student will be shown more than once in the table. The Student ID is what we will key off of to find the multiple instances of the student. The table contains the following columns: studentName, StudentId, teacherName, focus1, controllingIdeas1, reading1, development1, organization1, conventions1, and contentUnderstanding1. All of the columns with a 1 at the end will be numeric values with possible decimal values.
What we need to be able to do is some how perform a search for these multiple entries of each student and when found, average the 2 scores for each 7 test categories. The result needs to be a single line for each student that gives the student name, student id, and the 7 test category averages exported to an csv file.
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?
My table has two datetime columns (TheatreArivalDate and TheatreDepartDate). Can I get an average value of the time differences of them? Thanks
View 1 Replies View RelatedI have the following code which returns day of week name, position and date and time of surgeries.
SELECT
DATENAME(WEEKDAY,OPE_START_TIME) AS DOW,
[OPE_ORDER_IN_SESS_ASC] AS POSITION,
OPE_START_TIME AS [TIME]
FROM table1The data looks like thisHow can i group this data so I get the average start time by day of week and position?
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.
I keep getting an alert from a Third-Party Tool stating my server is reporting failed components with the following message
Components Reporting Problems:
Average Lock Wait Time(ms)(Critical)
When I look at sp_who2 or sp_lock I don't see anything blocking processes or anything else out of the ordinary.
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')
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.
I have a dataset as such:
Student TestTypeDate TestCnt
111-22-1111English2015-09-01 10:00:00 1
111-22-1111Math2015-09-02 11:00:00 2
111-22-1111Geo2015-09-03 12:00:00 3
222-11-2222English2015-09-01 10:00:00 1
333-22-1111English2015-09-01 10:00:00 1
[Code] ...
So some have just 1 test and some have multiple. I have a count for each. What I need to do is use that count and get an average time between each test per student.
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
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
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?
Calculation of an average using DAX' AVERAGE and AVERAGEX.This is the manual calculation in DW, using SQL.In the tabular project (we're i've noticed that these 4 %'s are in itself strange), in a 1st moment i've noticed that i would have to divide by 100 to get the same values as in the DW, so i've used AVERAGEX:
Avg_AMP:=AVERAGEX('Fct Sales';'Fct Sales'[_AMP]/100)
Avg_AMPdollar:=AVERAGEX('Fct Sales';'Fct Sales'[_AMPdollar]/100)
Avg_FMP:=AVERAGEX('Fct Sales';'Fct Sales'[_FMP]/100)
Avg_FMPdollar:=AVERAGEX('Fct Sales';'Fct Sales'[_FMPdollar]/100)
The results were, respectively: 701,68; 2120,60...; -669,441; and finally **-694,74** for Avg_FMPdollar.i can't understand the difference to SQL calculation, since calculations are similar to the other ones. After that i've tried:
test:=SUM([_FMPdollar])/countrows('Fct Sales') AND the value was EQUAL to SQL: -672,17
test2:=AVERAGE('Fct Sales'[_Frontend Margin Percent ACY]), and here, without dividing by 100 in the end, -696,74...
So, AVERAGE and AVERAGEX have a diferent behaviour from the SUM divided by COUNTROWS, and even more strange, test2 doesn't need the division by 100 to be similar to AVERAGEX result.
I even calculated the number of blanks and number of zeros on each column, could it be a difference on the denominator (so, a division by a diferente number of rows), but they are equal on each row.
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.
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!
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?
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()
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.
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.
I am trying to take the results of a query and re-orient them into separate columns.
select distinct
W_SUMMARYDETAILS.FACILITY_ID,
W_SUMMARYDETAILS.REPORTING_YEAR, (2011 - 2014, I want these years broken out into columns for each year)
W_SUMMARYDETAILS.FACILITY_NAME,
W_DEF_SUMMARYDETAILS.REPORTING_PERIOD (2011 - 2013, I want these years broken out into columns for each year)
From W_SUMMARYDETAILS
full outer join W_DEF_SUMMARYDETAILS
on W_SUMMARYDETAILS.FACILITY_ID=W_DEF_SUMMARYDETAILS.FACILITY_ID and
W_SUMMARYDETAILS.REPORTING_YEAR=W_DEF_SUMMARYDETAILS.REPORTING_PERIOD
As of now the query puts all the years into a single column -- one for DEF_SUMMARY and another for SUMMARY.
I am looking to create 7 additional columns for all the individual years in the results instead of just two columns.
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.
I am trying to write a report that gives me a list of all enquiries based on top 10 people who received it the most.
The fields are:
enquiry.enquiry_number
enquiry.enquiry_time
officer.officer_name
What I want to do is run the report which details each enquiry each officer has received but stop running once top 10 officers have the most enquiries are in the results.
The officers are identified using the officer.officer_name field, enquiry.enquiry_number field shows each enquiry record.
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.
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
Update statement based on the results from this SELECT:
SELECT RELQ_REL_VERSION_NM,
RELQ_RELEASE_Q_ID,
RELQ_STATUS_CD,
RELSP_RELEASE_STEP_ID,
RELSP_STEP_TYPE_CD,
RELSP_STATUS_CD
FROM RELEASE_QUEUE WITH (NOLOCK)
[Code] ....
I need to update all records where the
RELEASE_STEPS.RELSP_STATUS_CD = 'SKPD'TORELEASE_STEPS.RELSP_STATUS_CD = 'CMPS'
I imagine that the UPDATE statement will be something like:
UPDATE RELEASE_STEPS SET dbo.RELEASE_STEPS.RELSP_STATUS_CD = 'CMPS'
WHERE (
SELECT RELQ_REL_VERSION_NM,
RELQ_RELEASE_Q_ID,
RELQ_STATUS_CD,
RELSP_RELEASE_STEP_ID,
RELSP_STEP_TYPE_CD,
[Code] .....
My source table has two columns... Policynum and PolicyStartdate and data looks like..
.
Policynum PolicyStartdate
123G 01/01/2012
456D 02/16/2012
789A 01/21/2012
163J 05/25/2012
Now my output should return based on 3 parameters..
First two parameters are date range... let say @fromdt and @todt
Third parameter is @policynum
Scenario-1: Enter dates in date range param and leave policynum param blank
Ex: policystartdate between '01/01/2012 and '01/31/2012'.... It returns 1st and 3rd rows from above in the output
Scenario-2: enter policy num in policynum param and don't select any dates
Ex: policynum ='456D' It returns 2nd row in the output
Scenario-3: Select dates in date range param and enter policynum in param
Ex: policystartdate between '01/01/2012 and '01/31/2012' and policynum
='163J'. it should return only 4th row even though dates were selected(Override date range when policynum is entered in param and just return specified policynum row in the output)
I need t-sql code to get above results.
I have a table that has multiple transactions for stock items.
This table holds all records relating to items that are inducted onto the system and there movement. For each stock item i am interested in getting the drop destination, if it has one, and only when it follows the sequential order of "Inducted>OnTransport>Dropped" (this sequence isn't always the case). Also note the CreatedDate for the Inducted and OnTransport records for the valid sequences are always the same. Below is a valid sequence for a stock item so i would want to return 'Lane01' for the Destination of this occurrence of the stock item, if this item didn't have a valid drop location then destination would be blank. Also note each stock item can be inducted more than one time per-day.
I think i have managed to build the below sql but it will only do one item at a time, so would have to wrap it in a function. Is there a way of writing a set based select statement that gets all the inducted items and for the ones that do follow the "Inducted>OnTransport>Dropped" return the destination it was dropped at? I've attached scrips below:
DECLARE @StockItemID nVarchar(128)
DECLARE @CreateDate DATETIME
Set @StockItemID='8cbe17da-6079-4170-b27a-41c0d38830f6'
Set @CreateDate = CAST('2015-08-31 13:52:39.890' AS datetime)
[Code] ....
I have a table of attributes set up as follows:
ID, Value, Group
1, Football, Sports
1, Baseball, Sports
1, Basketball, Sports
2, Baseball, Sports
3, Football, Sports
1, Lambda Sigma, Greeks
2, Delta Delta, Greeks
etc.
I want a query that will combine that values for each ID into one field per group. So if ID 1 has multiple sports but also a greek attribute, they end up with two rows; the first row containing the combined sports values and the second row the greek valued not combined, because there was only one value in that group for that ID. For example:
ID, Combined Values, Group
1, Football Baseball Basketball, Sports
2, Baseball, Sports
3, Football, Sports
1, Lambda Sigma, Greeks
2, Delta Delta, Greeks
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 2 Replies View Relatedwhat is the easiest way to obtain 10 records at a time using a SQL query in SQL server ?
View 5 Replies View Related