Blocks Of Time - Log Number Of Visits From Clients

Oct 30, 2014

I have the following SQL that I want to log the number of visits from clients. a session is a block of time, for example, any time between 9-12 is a morning sessions, 12-5 is an afternoon session etc. I need to count the number of sessions in a month not the appointments within the session, make sense?

select sr.resourceid, st.TimeStart, datename(m,sr.scheduledate) as sesmonth, datename(yy,sr.scheduledate) as year,
(CASE
WHEN DATEPART(hour, st.TimeStart) BETWEEN 0 AND 12 THEN 'MORNING SESSION'
WHEN DATEPART(hour, st.TimeStart) BETWEEN 12 AND 17 THEN 'AFTERNOON SESSION'
WHEN DATEPART(hour, st.TimeStart) BETWEEN 17 AND 24 THEN 'EVENING SESSION'

[Code] ....

View 2 Replies


ADVERTISEMENT

Table Design - Log Number Of Visits From Clients

Dec 5, 2013

I am creating a simple database to log the number of visits from clients. The table is as follows :

tbl_statistics

--------------------------------------------------
p_key | client_id | client_datetime
--------------------------------------------------
1______4________2013-12-05 21:00:00
2______3________2013-12-05 21:02:11
3______1________2013-12-05 21:07:31
4______3________2013-12-05 21:12:42
.....

It will probably be logging about 2-3 rows in this table per second maximum so there will be some rows ... My question to you experts now is how the design of this table is done in the best way ( performance wise) in terms of "primary keys" , " clustered index" , "non clustered index"...

The only type of query that will retrieve data from this table is a standard "SELECT FROM" where " client_id " is of a certain value and " DATE_TIME " is between a specific date range.

example:

SELECT Count ( client_id ) AS totantal FROM tbl_statistics
WHERE ( client_datetime ) Between '2013 - 01-01 00:00:00 ' And '2014 - 01-01 00:00:00 '

Or is this better?

SELECT Count ( p_key ) AS totantal FROM tbl_statistics
WHERE ( client_datetime ) Between '2013 - 01-01 00:00:00 ' And '2014 - 01-01 00:00:00 '

View 3 Replies View Related

Transact SQL :: Counting Blocks Of Time In 15 Minute Chunks

Feb 16, 2012

We have data that consists of an employee number, a start time and a finish time, similar to the example below

EMP   STARTTIME            ENDTIME

00001 10-Feb-2012 06:00:00 10-Feb-2012 10:00:00

00002 10-Feb-2012 07:15:00 10-Feb-2012 10:00:00

00003 10-Feb-2012 08:00:00 10-Feb-2012 10:00:00

I am trying to come up with a procedure in SQL that will give me each 15 minute block throughout the day and a count of how many employees are expected to be at work at the start of that 15 minute block. So, given the example above I would like to return

10-Feb-2012 00:00:00     0
10-Feb-2012 00:15:00     0
10-Feb-2012 06:00:00     1
10-Feb-2012 06:15:00     1

[code]....

I'm not too worried if the date part is not included in the result as this could be determined elsewhere, but how can I do this grouping/counting?

View 7 Replies View Related

How Many Clients Can Be Served By Query Notification At One Time.

Feb 7, 2008



In order to avoid a large ammount of network traffic we decided to use Query Notification to Update our Clients.
Usually we have 20 up to 200 Clients connected to the DB, and some of the data QN is pointing at, are changed frequently (about 5 times per second) .
Now i heard it is recomended to connect just 10 Clients (max) to get acceptable performance out of SQL Server.

So my questions:

Is Query Notification the rigth technique to handle this kind of Data Changes.?

Is there an other feature or technique to get updated at client-side with less performance and ressource consumtion (without polling)?

Are there improvements in SQL-Server 2008?

How should the Server be configured to handle more than 10 Clients?
Thanks in advance
Raimund

View 5 Replies View Related

How To Enter More Number Of Rows In A Table Having More Number Of Columns At A Time

Sep 24, 2007

Hi

I want to enter rows into a table having more number of columns

For example : I have one employee table having columns (name ,address,salary etc )
then, how can i enter 100 employees data at a time ?

Suppose i am having my data in .txt file (or ) in .xls

( SQL Server 2005)

View 1 Replies View Related

Convert Number To Time

Apr 2, 2008

In my database times are stored as integers. Therefore if someone arrives a 1am the value is 60, 2am 120 etc. Is there a way to convert these integer values to 1:00, 2:00. I've tried converting to a decimal so if somene arrives at 1:30 it returns 1.5, but I still only get the integer value...

CONVERT(decimal(10,2), (90/60)) AS iStart

Thanks in advance
Roy

View 2 Replies View Related

Sum Up Number On The Time Line

Jul 22, 2007

Hello.
I have a query That look like that:

"SELECT COUNT(*),MONTH FROM TABLE GROUP BY MONTH"

The result is:
Jun 2
Feb 2
Mar 3
Apr 0
May 5

What I want to have is that every month will sum up the previous month and him self so in the end I will be able the create a graph wuth those numbers:

Jun 2
Feb 4
Mar 7
Apr 7
May 13

Any ideas on how to start?

View 3 Replies View Related

Sum Up Number On The Time Line

Jul 22, 2007

Hello.
I have a query That look like that:

"SELECT COUNT(*),MONTH FROM TABLE GROUP BY MONTH"

The result is:
Jun 2
Feb 2
Mar 3
Apr 0
May 5

What I want to have is that every month will sum up the previous month and him self so in the end I will be able the create a graph wuth those numbers:

Jun 2
Feb 4
Mar 7
Apr 7
May 13

Any ideas on how to start?

View 1 Replies View Related

Distinct Serial Number And Latest Time

Feb 12, 2015

select SERIALNO, Max(TIME)
from dbo.TASK A
join dbo.Status B
on A.TID=B.TID
where A.ID in ('1111',2222')
and A.TYPE='Pen'
group by B.SERIAL_NO, B.TIME
order by BM.TIME

For this query I may get serial no duplicates but times are unique

For that serial no, I have to find the recent time. But if I use group by, I am getting the wrong no

View 2 Replies View Related

Update Column With Sequence Number Every Time

Sep 25, 2006

hello friends

i want to update one column from my table regularly on sequence number i.e. 0,1,2,3,4,5

i created procedure but it is not working as per my output

declare @IndexIDGen int
declare @ID int
set @ID = 0
update temp_test set indexid = NULL
declare IndexIDGen cursor for select indexid from temp_test
open IndexIDGen
FETCH Next from IndexIDGen into @IndexIDGen
while @@fetch_status = 0
begin
update temp_test set indexid = @ID where indexid is null

set @ID = @ID + 1
print @id
fetch next from IndexIDGen into @IndexIDGen
end

close IndexIDGen
deallocate IndexIDGen


where i am going in wrong direction ??????

T.I.A

View 8 Replies View Related

Time Series: Number Of Previous Values Used In Model

Nov 8, 2006

As the Microsoft Time Series algorithm implementation is based upon the Autoregressive Tree approach described in:

C. Meek, D. M. Chickering, D. Heckerman. Autoregressive Tree Models for Time-Series Analysis. In Proc. 2nd Intl. SIAM Conf. on Data Mining, 2002 (SDM-02). SIAM, pp. 229 244. http://www.siam.org/meetings/sdm02/proceedings/sdm02-14.pdf.

The model estimated is refererred to as an instance of "... autoregressive tree models of length p, denoted ART(p). An ART(p) model is an ART model in which each leaf node of the decision tree contains an AR(p) model, and the split variables for the decision tree are chosen from among the previous p variables in the time series..." (see the last paragraph of p. 2 of the paper).

What is the value of "p" used in the Microsoft Time Series implementation -- specifically, how many previous time series variables are used in estimating the model? It doesn't appear that this value can be specified in the algorithm parameters -- is that correct?

Thanks,

- Paul

View 1 Replies View Related

How To Represent Maximum Number Of Books Borrowed At Same Time Through A Trigger

Feb 20, 2013

I have to build a database model, create tables and use triggers or/and procedures on it. It's about a library. Books are borrowed by students. They can make a reservation of a book.

I built the database model so far. My problem is how to represent following tasks:

- Students can borrow a maximum of 5 books at the same time.
- bookings/book reservations should be later processed in the order they occurred. That is, who first flagged the book also gets the book/books first

I have following entities:

- Book
- Book exemplar
- Borrowed by (a table between the entities Book exemplar and Student)
- Reserved for (a table between the entities Book exemplar and Student)
- Student

How would you represent the tasks I mentioned above in terms of triggers or procedures?...

View 5 Replies View Related

SQL Server Admin 2014 :: How To Get Number Of Execution In Specific Time - Not From First

Apr 7, 2015

I have this query

SELECT top 100 Ltrim([text]),objectid,total_rows,total_logical_reads , execution_count
FROM sys.dm_exec_query_stats AS a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) AS b
where last_execution_time >= '2015-04-07 10:01:01.01'
ORDER BY execution_count DESC

But the result of execution count is from the first. I want to know it only one day.

View 9 Replies View Related

How To Know Number Of Users Hitting A SSRS Report Over A Time Period?

Jan 24, 2008



Hi all,
I want to raise one question which might not relevant to current discussion.
Prob:-- I have some SSRS reports. I want to know for a given time period
1. How many people hit the report.
2. Who are the users hit the report in that time interval.

Is there any query to get that data?
Or,
should I go somewhere to see the data?

NB:- There is not any table in the application which is storing the data

View 10 Replies View Related

How To Return Time && Number Format That Has Set In The Regional Setting Using Stored Procedure

Dec 11, 2007

How to return time & number format that has set in the regional setting using stored procedure.
Following is my sp for getting current date format from Sql Server.

View 1 Replies View Related

Time Slot Which Has Maximum Number Of Purchase Orders Created Or Updated

Jul 15, 2014

I need to write down a sql query wherein in one particular day(user will enter manually), i need to find out a 15 minutes slot wherein purchase order's created or updated are the highest.

i.e. out of 96 slots(15 minute slot each)-I need to find the slot which has maximum number of Purchase orders created or updated.

View 7 Replies View Related

Trigger That Won't Allow A Member To Exceed Certain Number Of Loans At A Time Based On Their Category

Apr 14, 2015

I have an assignment to make a library. Right now I'm at the point of implementing business rules. One is that I need to create a trigger that won't allow a member to exceed a certain number of loans at a time, based on their category (student = 5, Teacher =10 and Researcher = 20).

View 1 Replies View Related

Integration Services :: Iterating Over Object In SSIS With Fix Number Of Results Each Time

Aug 11, 2015

I've got this issue with a query in SSIS. From a table in SQL Server I'm getting over 25000 different identifiers. These identifier are associated to many values in a table in  one Oracle Database. This is the schema that I have implemented for doing this.

The problem is that some days the identifiers can be over 45000, and at this point perform a loop for every one is not the best solution (It can take to much time to get the result). Previously I have performed another query where from the SQL statement.

I am creating and sending a unique row with all the values concatenated and then I have recover this unique string from an object and use it to create the query in the ODBC Source that invoke the table in Oracle: something like this:
'Select * from Oracle_table' + @string_values

with @string_values = 'where value in (........)'. It works good because the number of values is small enough to be used, like 250. But in this case I can not use this approach because the number is really big and obviously the DBA of Oracle is going to cancel the query.

So I wonder, how can I iterate over the object getting only a few number of values everytime, something like 300 or maximum 500, to avoid the cancellation of the query but at the same time doing the minimum number of loops.

View 5 Replies View Related

Blocks And Deadlocks

Jul 25, 2002

I know blocks and Deadlocks are different but how related are they? Seems like when I get reports of deadlocks I always have blocks and the blocks grow as time passes.

View 2 Replies View Related

Lock Blocks

May 24, 2001

Hi, Everyone

Can anyone tell me how to find who blocks the table or record, because the Lock Blocks is extremly high in the performance monitor.

Regards,

View 3 Replies View Related

Blocks On SQL Server

Nov 26, 2001

I need to test a block-alert and would like to test this by causing a block on our test server. Anyone have a script handy that will do this?

View 5 Replies View Related

Logging Blocks

Apr 23, 2008

I would like to log blocks to see what causes it.

One hack of a way to do it is to run code similar to that which populates the blk column in sp_who; on a job every 5 seconds. It would store the results of code similar to the sp_who output whenever any of the rows has a value greater than zero in the blk column.

This way, I can see what was going on at the time there was a problem.
Does anyone have a better strategy for this?

View 17 Replies View Related

History Blocks?

Mar 21, 2007

Is it possiable to go through the history blocks and change field names?...ie

I want to change "PS001_Pump1.RT_Daily" to "PS001_Pump1.Runtime_Daily"

so i can access the historical data thru the new tagname.

If so , is this using an udpate query?

View 1 Replies View Related

Transact SQL :: Adding Count Before And After A Specific Time Doesn't Match Total Number Of Records

Nov 19, 2015

If I just use a simple select statement, I find that I have 8286 records within a specified date range.

If I use the select statement to pull records that were created from 5pm and later and then add it to another select statement with records created before 5pm, I get a different count: 7521 + 756 = 8277

Is there something I am doing incorrectly in the following sql?

DECLARE @startdate date = '03-06-2015'
DECLARE @enddate date = '10-31-2015'
DECLARE @afterTime time = '17:00'
SELECT
General_Count = (SELECT COUNT(*) as General FROM Unidata.CrumsTicket ct

[Code] ....

View 20 Replies View Related

Sending Mail In Blocks

Jun 12, 2007

I need to find a way to send mail in blocks of 100 and 500 at intervals of 120 seconds. Does anyone know of any code for this?

Thanks!

Etymon

View 4 Replies View Related

Xp_sendmail Blocks Trigger ?

Jul 20, 2005

Dear All,we are running SQL2000 Sever and make use of the xp_sendmail.For any reason the mail service can run into problems and it lookslike that the statemnt below gets not finished.EXEC @Status = master..xp_sendmail @recipients=@TOList,@copy_recipients=@CCList,@subject='the subject goeshere',@message=@MailText,@no_output=TRUEUnfortunately the statement is in an update trigger and hence itblocks the table for any further updates.My questions are:Can I achieve a kind of timeout check in my trigger in order to bypassthexp_sendmail call ?In general, sending mail in a trigger may not be a good idea.How can this be solved better ?Any hint is highly welcomeRegardsRolf

View 6 Replies View Related

End Conversation Causing Blocks

Apr 9, 2007

Hi



I was trying to clean up some conversation in Service Broker and caused alot of blocking that I seem to unable to kill. there was 1 conversation that I was not able to end, so I wanted to restart sql service, But I can't even restart the SQL service. I get the following in Event Viewer



Timeout occurred while waiting for latch: class 'SERVICE_BROKER_TRANSMISSION_INIT', id 00000001A2B03540, type 2, Task 0x0000000000C2EDA8 : 0, waittime 5400, flags 0xa, owning task 0x00000002DEBCA5C8. Continuing to wait.



Has anyone come across this



thanks



Paul

View 1 Replies View Related

Using Try Catch Blocks In Stored Procedure

Mar 26, 2008

The following two stored procedures works fine. I need to add try catch blocks in the stored procedures. How to do it? Pls modify my stored procedure with try and catch blocks and in the catch block i need to call the Procedure called ErrorMsg which contains error severity.
Procedure 1------------set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo
-- =============================================-- Author:  C.R.P. RAJAN-- Create date: March 26, 2008-- Description: PROCEDURE FOR IMPLEMENTING SOFT PURGE-- =============================================ALTER PROCEDURE [dbo].[SP_SOFTPURGE] @POID INTASBEGINUPDATE POMASTER SET FLAG=1 WHERE POID=@POIDSELECT * FROM POMASTER WHERE FLAG=0END
Procedure 2-----------set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo
-- =============================================-- Author: C.R.P. RAJAN-- Create date: March 26, 2008-- Description: Stored Procedure for Hard Purge-- =============================================ALTER PROCEDURE [dbo].[SP_HARDPURGE]  @PoId INTAS
BEGIN TRAN INSERT INTO ARCHIVE SELECT * FROM POMASTER WHERE POID = @POID   IF @@ERROR != 0   BEGIN      ROLLBACK TRAN      RETURN   END
   DELETE FROM POMASTER WHERE POID = @POID   IF @@ERROR != 0   BEGIN      ROLLBACK TRAN      RETURN   ENDCOMMIT TRANSELECT * FROM POMASTER

View 1 Replies View Related

Tempdb Is Locked And Blocks Other Users

Oct 18, 1999

When I am running reports on SQL Server 6.5 database which involves creation of temp tables, whole tempdb is locked. All other users are blocked,

The reports involve queries which fetch data into temporary tables, as,

SELECT c1 , c2, c3 ...
INTO #tmp1
FROM T1
WHERE C1 = xxx
AND .........

When any of these queries are run, it blocks all other operations using TEMPDB, like creation of temporary tables or queries using sorts etc.

It appears that the query locks the system tables (sysobjects, syscolumns) in TEMPDB.

However, the above query works fine if it is constructed as,

CREATE TABLE #tmp1 (a1 ..., a2 ...., a3 ..... )

INSERT INTO #tmp1
SELECT (c1, c2, c3)
FROM T1
WHERE c1 = xxx
AND .........

Could someone please tell me what actually is happening in the first scenario. Is there a way avoid the blocking of TEMPDB usage other than the workaround mentioned above (which means I have to change all my queries) ?

Thanks
Nishant

View 1 Replies View Related

Record Groups In Blocks Via A Query

Jun 11, 2007

I'm trying to place records into groups of 100, so I need a query that will return records but not based upon an autonumber type field. For instance, if I had a set of records that had been sequentially numbered (by autonumber) with IDs to greater than 3,600 but there were actually only 300 IDs remaining ranging from 1 to 3600 (say the others were deleted), then I would want to use a query to make three groups of 100 of the remaining IDs.

I can use SELECT TOP 100 for the first 100 records. How do I get subsequent groups of 100?

It's probably obvious and I am thinking too hard.

View 3 Replies View Related

One Ms Sql Server 2000 Instance Blocks The Other (thru A Dts-job)

Jul 23, 2005

Hello,we use two instances of the ms sql server 2000 (Version: 8.00.760) on a4 processor windows 2000 (sp 4) machine.A dts-packet on one instance completly blocks the work on the otherinstance. Both instances have 2 dedicated processors. The twoprocessors of the blocked instance are in idle states.How is this possible? Has someone had the same behavior of the sqlserver and found a solution for this problem?Thanks in advance for answersJrgen Simonsen

View 1 Replies View Related

Best Method For Moving Blocks Of Records From One Db To Another?

Feb 6, 2008

I wanted to set up a mechanism that would transfer blocks of records (a few dozen to in rare cases a few thousand), with slight modification, from one database to another. It's a sort of custom partial archiving process that would be triggered from a web-based admin application. Records in the target db would be identical except:

-- the primary key in the source table, an identity field, would be just an integer in the target table
-- the target table has an extra field, an integer batch ID supplied by the web application that triggers the process

It's a simple, if not efficient matter to do it within the web application: query the source table, suck the records into memory, and insert them one by one into the target db. This will be an infrequent process which can be done at off-hours, so a bit of inefficiency is not the end of the world. But I wondered if there is a more sensible, orthodox approach:

-- Could this process be done, and done efficiently, as a stored procedure with the batch ID passed as a parameter?
-- Is there any way to do a bulk insert from a recordset or array in memory using ADO and SQL? And if so, is that better than inserting records one by one?

Advice on the best general approach would be appreciated, and I will try to figure out the details.

View 2 Replies View Related

SSIS Package BLOCKS At Opening.

Mar 21, 2006

Hallo,

I'am relatively new to SSIS-programming and experiencing a serious problem with a package (6618 KB large) containing 5 dataflows. These dataflows all start from a datareader-origin and flow trough multiple transformations. While editing the fifth dataflow the developmentenvironment suddenly became unworkably slow. After any modification made to the dataflow it takes several minutes before I can continue editing. Meanwhile in the task manager the process devenv takes a very large portion (50 to 100%) of CPU.

Building the package,shutting down SSIS and even rebooting the PC don't make any difference. When you close the package ands reopen it, this takes at least 10 minutes.

We work with Microsoft SQL Server Integration Services Designer
Version 9.00.1399.00.

Does anyone know if there is a limit to the number of transformations in a flow or the size of a package? Could that be the problem?

Before this problem occured I was multiplying transformations by copying ,renaming and altering them within a dataflow. Could SSIS have some problem with that?

Any suggestion for solving this problem, other than chucking it all away an restarting, will be gratefully accepted.



With kind regards,





Paul Baudouin



View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved