The values in the final table are the days used by each ID transferring from status i to status i-1. E.g., ID uses 8 days (10-May-13 - 2-May-13) to go to status 3 from status 4.
It is hard for me to come up with a table like the final table, although I know that the difference between two adjacent rows can be computed by using self-join and timediff().
For displaying data on the report I am using the following query
SELECT ReferenceNumber, ActivityID, ActivityTimeStamp, ActivityType, ActivityPerformedBy FROM ActivityDetails ORDER BY ReferenceNumber, ActivityID
The result set is
Issue Reference #
Activity ID
Activity Date/Time
Activity Type
100819
4521404
11/4/07 2:06 PM
INIT
100819
4521405
11/4/07 2:07 PM
LOG
100819
4521406
11/4/07 2:07 PM
LOG
100819
4521473
11/4/07 2:28 PM
TR
100819
4521501
11/4/07 2:33 PM
WIP
100819
4521839
11/4/07 3:25 PM
RE
100819
4521844
11/4/07 3:27 PM
RE_Method
100819
4522575
11/4/07 8:53 PM
CL
100820
4521412
11/4/07 2:10 PM
INIT
100820
4521419
11/4/07 2:13 PM
ATTACHTDOC
100820
4525856
11/5/07 2:49 PM
ATTACHTDOC
100820
4525859
11/5/07 2:49 PM
LOG
100820
4525869
11/5/07 2:49 PM
CL
100821
4521423
11/4/07 2:14 PM
INIT
100821
4521425
11/4/07 2:14 PM
LOG
100821
4521429
11/4/07 2:14 PM
TR
100821
4521432
11/4/07 2:14 PM
ACK
100821
4522219
11/4/07 4:58 PM
RE
100821
4522221
11/4/07 4:58 PM
RE_Method
100821
4522447
11/4/07 6:51 PM
CL
On the report I have used the grouped by clause on 'Issue Reference #'. I want one more column which would calculate the difference between two consecutive Activity Date/Time of the same reference #.
e.g. Time difference between 4521404 and 4521405, 4521405 and 4521406, 4521406 and 4521473 etc. Please note that the difference between 4521412 and 4522575 will NOT be calculated since they are from different Reference Numbers.
I have a table named Orders and this table has two relevant fields: CustomerId and OrderDate. I am trying to construct a query that will give me the difference, in days, between each customer's order so that the results would be something like: (using Northwind as the example)
At the moment, I have the following query that I think is on the right track: €¦ SELECT dbo.Orders.CustomerID, dbo.Orders.OrderDate AS LowDate, Orders_1.OrderDate AS HighDate, DATEDIFF([day], dbo.Orders.OrderDate, Orders_1.OrderDate) AS Difference FROM dbo.Orders INNER JOIN dbo.Orders Orders_1 ON dbo.Orders.CustomerID = Orders_1.CustomerID AND dbo.Orders.OrderDate < Orders_1.OrderDate GROUP BY dbo.Orders.CustomerID, dbo.Orders.OrderDate, Orders_1.OrderDate, DATEDIFF([day], dbo.Orders.OrderDate, Orders_1.OrderDate) ORDER BY dbo.Orders.CustomerID, dbo.Orders.OrderDate, Orders_1.OrderDate €¦
So, do any of you have any ideas how I might achieve this? I know how to do it using a stored procedure, but I am trying to avoid that; I€™d like to do this in a single query.
i have a matrix, and in that matrix i need to have one column which calculates the percentage change between a value on the current row and the same value on the previous row.
Is this possible? The RunningValue() function isn't of help as it can't help me calculate the change between two rows, and Previous() doesn't work in a matrix (why???!!!!!). Also calculating this as part of the query isn't possible as there is a single row group on the matrix, and the query is MDX.*
Thanks,
sluggy
*for those who are curious, the matrix is showing data an a per week basis, the row group is snapshot date, i am trying to measure the change in sales at each snapshot.
I have here a query which delivers me the user data from the last month. The problem what I have is, if employee have more then one rows in this month, they will be also deliverd. But exactly this is not needed. I need only the last record from last month.
SELECT a.FIRMA, a.PSNR, a.FELDNR, a.PFLFDNR, a.INHALT AS FTE, a.PFGLTAB,
As you can see, PSNR=364 has two rows and i need only the row from last month and last date.Maybe we can use Field PFLFDNR as counter. get only one row for every employee?
how to measure a change in inventory over various stores.  My sql2008R2 express db gets a new row of data everyday from each store(about 40 stores) for a single product stock count "OnHand" and if there is any new stock on order.  When the new stock arrives it is added to the "OnHand" count.  I want to measure the delta change per day,per store.  I'm stuck on how to separate the stores and how to query the delta of stock.My data base looks like this                 TimeStamp Store OnHand OnOrder 2015/04/22 18   1 - Concord 12    0 2015/04/23 11   1 - Concord 11  [code]....
I am trying to create an exception report that will show the difference between two versions of the same row. (Combination of two different sources in sql, with source 1 having childID = 0 and the other source having childID = 1; parentID is the link between them)
The results are as follows:
ParentID - ChildID - Col1 - Col2 - Col3 1 - 0 - AA - BB - CC 1 - 1 - AA - BF - CC 2 - 0 - GG - NN - TT 2 - 1 - DE - NN - TA 3 - 0 - etc 3 - 1 - etc 4 - etc
id     type    timestamp 1001   start1   10:34:23:545 1001   start2   10:34:24:545 1001   end2   10:34:24:845 1001   end1   10:34:25:545 1002   start1   10:34:25:645 1002   start2   10:34:25:745 1002   end2   10:34:25:945 1002   end1   10:34:25:965
I need the result as follows
id        millisecond diff start1end1               millisecond diff start2end2 1001   end1 timestamp-start1 timestamp   end2 timestamp-start2 timestamp 1002   end1 timestamp-start1 timestamp  end2 timestamp-start2 timestamp
Given a table that has three columns that together create a key and two columns that together define NameValue pairs, how can the difference between instances of values be calculated and displayed?One table is used to contain periodic dumps of data from various sources. Because this is an early stage of development for the software project instead of having explicit columns that contain specific data the table contains name/value pairs. This allows the software to export anything to the database table. When this data is imported, earch row shares the same key (three columns containing a machine type, serial number and a timestamp), a name that identifies the data and a string that contains the actual data. While this arrangement makes it trivial to support the addition of any data that the software developers want to export, it makes it less obvious as to how to generate reports.Let's make an example. Lets assume that there are two vending machines, each of which has just 3 snacks and each of which generates two separate reports.
Type Sn Timestamp Name Value A 1 2015-08-15 12:34 Snick 5 A 1 2015-08-15 12:34 Mars 10 A 1 2015-08-15 12:34 MandM 0B 2 2015-08-15 15:31 Snick 1 B 2 2015-08-15 15:31 Mars 9 B 2 2015-08-15 15:31 MandM 0A 1 2015-08-21 09:12 Snick 11 A 1 2015-08-21 09:12 Mars 18
[code]...
So, the names of the values become the report's columns. The reports are sorted by timestamp, then by type, then by serial number.The value associated with the previous row that shares the same name is subtracted from the value of the next row in which the same name occurs and that becomes the displayed value in the report.
I have found execution plan with significant difference between actual and estimated number of rows (roughly actual/2=estimated) in non-clustered index seek.Statistics are updated.
Basically I want to calculate the time spent by S_Users on a particular S_ACTV_CODE:
- S_ACTV_CODE_PREV means the previous active records.
- S_START_TIME is the time of S_DATETIME when a S_ACTV_CODE starts
- S_END_TIME is the time before a S_ACTV_CODE changes to another S_ACTV_CODE
- For the first record, S_ACTV_CODE is null, so there is no S_ACTV_CODE_PREV, so S_ACTV_CODE_PREV is NULL
- For the second record S_ACTV_CODE has some value, but S_ACTV_CODE_PREV is NULL for first record. So second record S_ACTV_CODE_PREV is also NULL
- For the last record (means S_ACTV_IND = 1), the user is currently working on it and S_ACTV_CODE is not changed. So S_END_TIME is a open time and we want to keep it as NULL
There is a stored procedure. It uses linked server. As we will be migrating to amazon cloud, our architect instructed not to replace linked server with openquery.
I have the table with the similar set of records which mentioned below, find the time difference between two rows of record. By Using the MsgOut column i have to find time taken b/w PS & PV and some record doesnt have PV .
equipmentid downtimestartdate downtimeenddate  dowtime a3er 2015-03-15 02:00 2015-03-17 23:00       69 b6e4 2015-03-18 13:00 2015-03-20 04:00       39
i have many rows(in our production table, thousands of rows are there) like above in a table and i want like below output(in output total 6rows only)
equipmentid downtimestartdate downtimeenddate dowtime a3er      2015-03-15 02:00 2015-03-15 24:00       22 a3er      2015-03-16 00:00 2015-03-15 24:00       24 a3er      2015-03-17 00:00 2015-03-15 23:00       23
I'm having a DB designed for me, and I'm inspecting it and wondering what in general is the better way to do this.
We have a product, which we are counting "product views". The DB designer has created columns called "view_today" and "views_alltime".
I specified I wanted a normalized database, I'm thinking this is technically not normalized ? Am I correct ?
Wouldn't it be better to have a query that counted the views off the logging table ? I can't see any advantage to doing it the way its been designed except to save time.
hi please help me,i have a table queried using this sql, select name,(select count(*) from myTable a where a.name = r.name ) as Total, (select count(*) from myTable b where b.name = r.name and dnum > '1') as Used, (select count(*) from myTable c where c.name = r.name and dnum < '1') as remainingfrom myTable r group by namebut i need one more thing in this table that should look like this,nameTotalUsedRemainingPercentageA126650%B2021810%C150150% this is to add the BOLD field from the above table, but my problem is that the computation is "Used / Total = Percentage%"so how can i do this, please help methanks
hi everybody, i'm trying to calculate the 'SUM' of time spent in hrs. n min. How can i do this using SQL Server? What i mean is, i've a column 'TIME_SPENT' that has 'datetime' datatype. This column saves time spent for an activity in format 'hh:mm'. Suppose a user spends 45min for activity 'A' and say 1hr 25 min for activity 'B' then i want to calculate the 'SUM' of 'TIME_SPENT' for the user which should appear as 'Total time spent =2:10'
I am attempting to compute Service Levels for an interaction based upon business hours. For example, an email arrives at 4pm and is handled the following day at 10am. Call Center Hours are 8-5.
Essentially I have a number of different alternatives, and have found some potential solutions, including:
www.dbforums.com/arch/7/2003/9/914261
However, my situation has a couple of additional twists to the standard 8hrs of business M-F. The call center is open different hours depending upon the day of the week. For example, 8-5 M, 10-7 T, 8-5 W Th F, 10-2 Sat, 10-12 Sun
Additionally, I would like to remove Holiday's from the calculation for service level as well.
I have explored a number of different table DTD's, but none seem to be a perfect fit for determining the number of "open" hours between when an interaction arrived, and when it was handled.
The DTD I have for the Holiday table is as follows:
CREATE Table Holidays (HolidayDate DateTime) GO Insert Into Holidays (HolidayDate) Values ('12-25-2004')
Please let me know what you feel would be the DTD for storing the business hours and also the query for extracting the number of Open hours between two dates
Dear All. I'm a fairly new SQL programmer so apologies if this is a silly question.
I'm trying to create a new column/variable from 3 other variables where the new column = column 1 unless column 1 is blank, then = column 2, unless column 2 is blank, then = column 3.
But I don't know where in my query to begin building this. Should I build it in a subquery? Thanks in advance for any replies.
I want to create a view where I can calculate the sum of a couple of bit value columns, aswell as keeping track of the total number of bits set to true.
Here is an example:
dbo.Band
BandID int Name nvarchar(50) Country nvarchar(50)
dbo.Record
ID int Name nvarchar(50) BandID int Label nvarchar(50) InProduction bit InSkodne bit From these tables I created this view:
dbo.TestView
SELECT dbo.Band.Name, dbo.Band.Country, dbo.Record.Name AS Recordname, dbo.Record.Label, CONVERT(int, dbo.Record.InProduction) AS InProduction, CONVERT(int, dbo.Record.InSkodne) AS InSkodne, CONVERT(int, dbo.Record.InProduction) + CONVERT(int, dbo.Record.InSkodne) AS Total FROM dbo.Band INNER JOIN dbo.Record ON dbo.Band.BandID = dbo.Record.BandID I use the convert function to be able to use SUM() across my bit columns, which works fine. Problems is I´m not sure that the way I´m creating the TOTAL column is the best way to go. Any other ideas? I´m having some problems using this view and the TOTAL column in particular when referencing this view from applications outside SQL Server...
I´m currently devoloping an application where users can register errors related to recieved purchase orders. I store these values in i table where the purchase order id i PK, and the possible errors that can exist are stored as bit.
Now I want to be able to put a price on these errors. I´m thinking about adding another table, containing all possible errors as columns, and then storing the cost of each error as an integer, and probably also a datetime for keeping track of when the costs was last updated.
I´m pretty sure this problem has been solved alot of times before, so I don´t wanna do something stupid here :-)
I´m also wondering about how it would be best to show the computed values? Should I use a view for this?
For example:
SELECT (dbo.Orders.QuantityError * dbo.Costs.QuantityError ) FROM dbo.Costs CROSS JOIN dbo.Record
assuming now that the Costs table only contains one row.
Is this the right way to go, or can you guys give me hints to a better solution?
Now my problem is that I want to set a primary key constraint on actorID but it doesn't let me because it the NULL check mark is automatically checked and I cannot check it off ... and I can't set a primary key on something which is allowed to be NULL....
I don't understand why 'actorName' column which is also calculated doesn't have that default NULL checked and locked ....
"Oracle's purported Grid enablement in 10g is based on its Oracle Real Application Clusters (RAC) technology that is no more than a local cluster. RAC is a local cluster of computers with no geographic distribution capabilities. This marketing campaign relabeled existing features to exploit current industry trends. "
My question is how can I support the above paragraph? I would like to known more reasons about why oracle grid is a local cluster instead of grid computring oriented.
I am new to SSRS, so perhaps its a trivial question. I was wondering that since all controls have names in the report, is it possible to programatically access values of different textboxes, do some computation and then assign to another text box? I know how to do it using the Aggregate functions and operators, but am not sure if I can access values from textboxes within two different tables and assign the computed value to a third text box on the page (not belonging to any table or other control).
Hello,I am in the process of making a very simple stats page that will show us how many tasks we've completed. Here is what I have so far: Here is the SQL that makes it work: SelectCommand="SELECT Count(TicketID), Category FROM Tickets GROUP BY Category ORDER BY Count(TicketID) DESC ">My problem is with how the totals seem to go on forever. Instead of being in proportion to each other as a percentage of a the total amount of tickets.. they just increase in size with each additonal entry. Can someone help me restructure this so that I can calculate the totals individually and as a whole and then apply the totals to create a proportional bar graph?Thank you greatly for your help,Mark
