I have created calcalated measures in a SQL Server 2012 SSAS multi dimensional model by creating empty measures in the cube and use scope statements to fill the calculation.
(so I can use measure security on calculations
as explained here )
SCOPE [Measures].[C];
THIS = IIF([B]=0,0,[Measures].[A]/[Measures].[B]);
i am trying to calculate the time that is spend by user on the internet per site. the file that we receive from the WebProxLog contains the following fields ClientIP, ClientUname,logdate,logTime, ProcessingTime, DestinationHost,DestinationIP. I want to check per user per site how much time the user spend per date. Now my challenge is that if the user connects to the side today and logout and reconnects again to the same side how do i calculate the time spend on the site because the file does not show the logout time
1. I have data something like this: start 07.30 end 16.00 How can i count how many hours and minutes from start to end?
2. Another data start: 20050805 -> August 5, 2005 end: 20050810 How can I return value that when i insert 20050809 it is between start and end, and if 20050811 it will say false that the date is between start and end
I have some difficulties with TSQL where i need to calculate time between two records in seconds. Records look like this: ID IP DATE CHANNEL_ID #CALCTIME 1 10.132.184.226 12/06/2007 08:00:00 406 35 2 10.96.121.230 12/06/2007 08:00:10 1 45 3 10.128.242.214 12/06/2007 08:00:20 4 39 4 10.132.184.226 12/06/2007 08:00:30 2 5 10.96.121.230 12/06/2007 08:00:55 3 6 10.96.121.214 12/06/2007 08:00:59 3 21 7 10.96.121.214 12/06/2007 08:01:20 4 .........................................................................................................
Basically i would need to calculate the time between 2 records with the same IP (next one with the same IP) and calculate the time between the DATE which is basically attached to the first record. As you can see the first #CALCTIME is done between the ID #1 and ID#4 which means that the time difference must be done between the current and the next record.
I am trying to produce a report that will show a duration in minutes of a time when a room was occupied for a category. Whilst I have the start and end dates and times, the end user must be able to specify not only a range of dates, but a start and endtime of hours in the day in which they are interested in (it will be applied to all days in the range - they are not allowed to specify a different start/endtime per day).
The example I have is a date range of 6 to 17 October, but they only want the times from 09:00 to 21:00, so if a room was occupied from 08:00 to 11:00 they would only want to know the duration as 120 minutes (09:00 to 11:00) not 180.
The data is supplied by a third party, and duration in minutes is supplied, but it is not much use when they are not interested in the 'real' duration.
Would like to know if it is possible to calculate the duration of a Datetime Start and End Dates ignoring all overlapps? Eg: 1) StartTime 10:00:00 EndTime 11:00:00 Duration: 01:00:00 2) StartTime 10:30:00 EndTime 11:15:00 Duration: 00:45:00 Total Duration should be 01:15:00 and not 01:45:00
Has anyone been able to get time calculations to show up in Report Builder? I'm using the BI Wizard standard calculations for YTD & Period over period growth, but I can't figure out how to use them within Report Builder. They are available in Report Designer, however. I did find the info about assigning the calculations to a measure group. I tried that, but it didn't have any affect.
Hello Friends I have 3 tables 1) Product Id, ShortName 2) IncomingStockId, ProductId, Quantity, InDate 3) OutGoingStock Id, OutDate, ProductId, Quantity I need to get the results like thisProduct name, quantity in stock today the "quantity in stock today" = sum (quantity recieved) -sum (quantity sent) Thank you for your timeSara
how do i calculate something like this if I have the table with names and count? Name Count Percent Name1 27 4.69% Name2 2 0.35% .... Totals 576 100.00%
I'm trying to run a dyncamic query that returns all records within a specific distance of a certain point. The longitude and latitude of each record is stored in the database. The query is constructed from two dynamic variables $StartLatitude and $StartLongitude with represent the starting point.
It runs fine until I add the 'HAVING (Distance <= 150)' clause, in which I recieve the error: Invalid column name 'Distance' It seems that Distance cannot be referenced in the HAVING clause.
I was trying to calculate GPA and Commulative GPA (CGPA) for this student and the formula of GPA is SUM(Honor)/SUM(CREDIT) and the formula of the CGPA is the Average of the SUM(Honor)/SUM(CREDIT) grouped by semester and I wrote the below query which is calculating the GPA correctly but wrongly calculating the CGPA.
How to get the correct CGPA shown in the below desired result set table.
select x.STUDENT_ID,AVG(x.gpa)as GPA,AVG(y.gpa)AS'COMMULATIVE GPA' from ( select STUDENT_ID,SUM(Honor)/SUM(CREDIT) gpa,ACAD_YEAR,SEMESTER
The Column bDebit has either value '0' or value '1' in it ('0' being debit - positive amount, and '1' - credit, negative amount).
I would like it to show the net amount for each account. Therefore in plain English I would like to take all GLCodes that are the same (eg 6843000701) and sum all amounts that have debit value of '0' and subtract all amounts that have debit value '1'. Therefore I would only see '6843000701' code once, and the amount would be '0' becase 600 - 600 = 0.
The current query is: SELECT dbo.tbGLTransactions.lLedgerCode, dbo.tbGLTransactions.sGLCode, dbo.tbGLTransactions.bDebit, SUM(curAmount)As TotalSum, dbo.tbLedgerCode.sGLDesc FROM dbo.tbGLTransactions INNER JOIN dbo.tbLedgerCode on dbo.tbGLTransactions.lLedgerCode = dbo.tbLedgerCode.lLedgerCode WHERE dbo.tbGLTransactions.lGLExtractRun = '452' Group By dbo.tbGLTransactions.lLedgerCode, dbo.tbGLTransactions.sGLCode, dbo.tbGLTransactions.bDebit, dbo.tbLedgerCode.sGLDesc Order By dbo.tbGLTransactions.bDebit, dbo.tbLedgerCode.sGLDesc
Is someone able to help me as to how i need to modify this query to get the desired result?
i have 3 question : 1. CAN MY MICROSOFT ACCESS Database imported to sql server 2005 ? 2. can i add field for queries with custom calculation like field 1*fiedld 2. like access can do? 3. is that any wizard to do no 1 and 2?
i have 3 question : 1. CAN MY MICROSOFT ACCESS Database imported to sql server 2005 ? 2. can i add field for queries with custom calculation like field 1*fiedld 2. like access can do? 3. is that any wizard to do no 1 and 2?
This is my syntax, and if I print the value that is stored in each variable @goodtries = 120 @badtries = 25 but when I run the syntax below it gives me 0.00
Declare @goodtries as int, @badtries as int select @goodtries = convert(decimal(18,4),count(userID)) from table1 WHERE logintype IN ('Valid', 'Success') select @badtries = convert(decimal(18,4),count(userID))
Hi experts,I am working on SQL server 2005 reporting services and i am getting aproblem in writting a query.Situation is given below.There is one table in database Named ChildNow i have to find the All childrens whoes Age is 13 years Base onSome given parameter.If User select Augus 2007 then It has to calculate the Childs who bornin August 1994 And if he select September Then queryshould show only those child Who born in September 1994 and soon..... And use can select another year month also likeAugust 2009 ...I am writting the following querySelect Child_Name, DOb from Childwhere ((CONVERT(DateTime, A.Date_Of_Birth, 103) >= @ Parameter1And (CONVERT(DateTime, A.Date_Of_Birth, 103) <= @Parameter2)If i know already month and year then i can write easily parameter1and parameter2 But since these are comming from user so i m notfinding how to handle this.Now please suggest me what i have to write in Where statement I thinka lot but not getting any idea about it.Any help wil be appriciated.RegardsDinesh
I have a table which has a few fields, one being "datetime_traded". I need to write a query which returns the row which has the closest time (down to second) given a date/time. I'm using MS SQL.
Here's what I have so far:
Code:
select * from TICK_D where datetime_traded = (select min( abs(datediff(second,datetime_traded , Convert(datetime,'2005-05-30:09:31:09')) ) ) from TICK_D)
But I get an error - "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.".
Does anyone know how i could do this? Thanks a lot for any help!
Select 'PIT_ID' = CASE WHEN Best_BID_DATA.PIT_ID IS NOT NULL THEN Best_BID_DATA.PIT_ID ELSE Best_OFFER_DATA.PIT_ID END, Best_Bid_Data.Bid_Customer, Best_Bid_Data.Bid_Size, Best_Bid_Data.Bid_Price, Best_Bid_Data.Bid_Order_Id, Best_Bid_Data.Bid_Order_Version, Best_Bid_Data.Bid_ProductId, Best_Bid_Data.Bid_TraderId, Best_Bid_Data.Bid_BrokerId, Best_Bid_Data.Bid_Reference, Best_Bid_Data.Bid_Indicative, Best_Bid_Data.Bid_Park, Best_Offer_Data.Offer_Customer, Best_Offer_Data.Offer_Size, Best_Offer_Data.Offer_Price, Best_Offer_Data.Offer_Order_Id, Best_Offer_Data.Offer_Order_Version, Best_Offer_Data.Offer_ProductId, Best_Offer_Data.Offer_TraderId, Best_Offer_Data.Offer_BrokerId, Best_Offer_Data.Offer_Reference, Best_Offer_Data.Offer_Indicative, Best_Offer_Data.Offer_Park
from ( Select PITID PIT_ID, CustomerId Bid_Customer, Size Bid_Size, Price Bid_Price, orderid Bid_Order_Id, Version Bid_Order_Version, ProductId Bid_ProductId, TraderId Bid_TraderId, BrokerId Bid_BrokerId, Reference Bid_Reference, Indicative Bid_Indicative, Park Bid_Park From OrderTable C Where version = (select max(version) from OrderTable where orderid = c.orderid) and BuySell = 'B' and Status <> 'D' and Park <> 1 and PitId in (select distinct pitid from MarketViewDef Where MktViewId = 4) and Price = ( Select max(Price) From OrderTable cc where version = (select max(version) from OrderTable where orderid = cc.orderid) and PitId = c.PitId and BuySell = 'B' and Status <> 'D' and Park <> 1 ) and Orderdate = ( Select min(Orderdate) From OrderTable dd where version = (select max(version) from OrderTable where orderid = dd.orderid) and PitId = c.PitId and BuySell = 'B' and Status <> 'D' and Price = c.Price and Park <> 1 ) and OrderId = (select top 1 OrderId from OrderTable ff Where version = (select max(version) from OrderTable where orderid = ff.orderid) and orderid = ff.orderid and PitId = c.PitId and BuySell = 'B' and Status <> 'D' and Price = c.Price and Orderdate = c.Orderdate and Park <> 1 )
) Best_Bid_Data
full outer join ( Select PITID PIT_ID, CustomerId Offer_Customer, Size Offer_Size, Price Offer_Price, orderid Offer_Order_Id, Version Offer_Order_Version, ProductId Offer_ProductId, TraderId Offer_TraderId, BrokerId Offer_BrokerId, Reference Offer_Reference, Indicative Offer_Indicative, Park Offer_Park From OrderTable C Where version = (select max(version) from OrderTable where orderid = c.orderid) and BuySell = 'S' and Status <> 'D' and Park <> 1 and PitId in (select distinct pitid from MarketViewDef Where MktViewId = 4) and Price = ( Select min(Price) From OrderTable cc where version = (select max(version) from OrderTable where orderid = cc.orderid) and PitId = c.PitId and BuySell = 'S' and Status <> 'D' and Park <> 1 ) and Orderdate = ( Select min(Orderdate) From OrderTable dd where version = (select max(version) from OrderTable where orderid = dd.orderid) and PitId = c.PitId and BuySell = 'S' and Status <> 'D' and Price = c.Price and Park <> 1 ) and OrderId = (select top 1 OrderId from OrderTable ff Where version = (select max(version) from OrderTable where orderid = ff.orderid) and orderid = ff.orderid and PitId = c.PitId and BuySell = 'S' and Status <> 'D' and Price = c.Price and Orderdate = c.Orderdate and Park <> 1 )
) Best_Offer_Data ON Best_Bid_Data.Pit_Id = Best_Offer_Data.Pit_Id
My asp.net application is attached with SQL database, I record only time in my SQL database , field type is nvarchar. Now I want to do qurey by time and pull the result , but datatype of field in nvarchar, query does not giving me right result. some one tell me how Do I do query so I can get proper result. thanks maxmax
I have asp.net applicatin with SQL database communicating. in database I have date field and time field. Now I wan to do SQL query which can pull informatin on particular date between given start time to given end time Can some one show me sample SQL query so I can pull informatin on particular day between two times
How do I find all the records within a time range when all the datetime's are on different days? Example: Show me all the order placed between 12:00 and 15:00 in May
I need to time a query down to hundredths of a second. I can see from the runtime clock in SQL Studio that my query ran for 00:00:00. However, I need to know exactly how long it took.
Hello,I have a table that lists a number of available time slots for a party venue. I want a user to select a particular time slot and have the query show results for that time slot plus two time slots before and two time slots after the user's selection. If the user selects a time slot that is the first of the day, I still want that user to be shown 5 results including the one he selected. The same goes for the last time slot of the day. Can anyone help me with the SQL statement? Thank you very much in advance! Mark
I am using SQL Server and ASP.NET. I am executing a couple of stored procedures and displaying the results in a datagrid. Since these Stored procedures takes around 2-4 minutes each, I want to display a status bar on the web by displaying the approximate time the user needs to wait before seeing the results.
My question is: Is there a way to find out the approximate EXECUTION TIME of the stored procedure before hand. Also, if that is possible, how do i access the same from the ASP.NET code..
I have a table which has a field called time and it has data like '23/04/2004 9:43:40 AM' How would i write a query that would retrieve the last hour of data from now().
i've tried this but does not work,
SELECT [time] FROM table WHERE ([time] = { fn NOW() } - 60)
I have a server with two test instances of a data base. I have a query which creates a temp table, inserts 29 rows, perform 4 update queries to add counts and then dumpps out the results. This entire query script runs 1.33 minutes on one instance and 2.5 minutes on the other. On the production server this query now runs in 9 seconds. If I run any one of the test updates individually they execute under 2 seconds, just like the production server. THe results are repeatable.
All are SQL 7 with all service packs on NT4 sp6. Both test data bases are backups of production from last week. I suspect some kind of caching/buffer problem, but I do not know what to look for. I am not a DBA so I have no idea what role TEMPDB plays may play in this.
Can anyone give us ideas on where to look for the performance difference? Will our impending upgrade to SQL2K solve this problem or make it worse? Any ideas would be appreciated.