Does anyone know how many consecutive users can be logged into a MSSQL database? I have a database online and need to know how many users can be logged on at a time. someone told me 150 users and others say 200 and stillothers say it's unlimited based on licensing. I tried Microsoft's homepage but got nothing so please don't suggest it. (I spent two hours there)
Thank You in advance
Joey*
Hello,
I have a stored procedure that inserts 5 rows into a table. The execution of the SP is inside a transaction like in the code below:
Code Block
SqlConnection conn = new SqlConnection(ConnectioString);
conn.Open();
SqlTransaction trans = conn.BeginTransaction();
try
{
// execute stored procedure... insert 5 rows
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
}
finally
{
conn.Close();
}
Suppose that N users are executing the code, one independent of the other, in the same time and they both commit the transaction at the same time.
Can I suppose that the rows inserted in the table by one user will be consecutive?
Thanks!
Hi,I'm
trying to insert records from one table into another table. The
destination table has a ROWID field which cannot be an identity key,
but needs to 'act like' an identity key and have its value populated
with (Max(ROWID) + 1) for each row added to the table.To my
thinking, simply using (Max(ROWID) + 1) in my SELECT statement will not
work as it will only be evaluated once so if I am adding 1000 records
and Max(ROWID) is 1234, all 1000 entries will end up having a ROWID of
1235.Is there a way to accomplish this?Thanks
Hi,
I am in need of a query which would find the same customer coming in for three or more consecutive dates. To elaborate
I have a details table where I capture the following details
CustID, DateofPurchase, PurchaseDetails
I need a query to find how many customers have come in everyday consecutive day and count of the same for the a given period, say a month. Can anyone help me with a query for the same.
Thanks for your help in advance.
Regards
Dinesh
Hi there.
I work for a charitable organization, am new to this form (and sql programming) and trying to create a flag for unique records indicating the number of consecutive years a donor has given.
I have create a sample db idenifying donor, giving year and total pledges with multiple donor records existing for multiple years having donated.
CREATE TABLE mygifts06 (Donor_id varchar (10), Gift_yr nvarchar (4), Tot_pledges numeric (16,2))
INSERT INTO mygifts06 (Id,Gift_yr,Pledges)
SELECT 155758,2005,15.00 UNION ALL
SELECT 155759,2004,25.00 UNION ALL
SELECT 155758,2004,40.00 UNION ALL
SELECT 155757,2005,100.00 UNION ALL
SELECT 155758,2002,30.00 UNION ALL
SELECT 155758,2001,120.00 UNION ALL
SELECT 155755,2003,15.00 UNION ALL
SELECT 155758,2006,80.00 UNION ALL
SELECT 155757,2003,65.00 UNION ALL
SELECT 155759,2005,400.00
For the above dataset, I am trying to create the following output
Donor_id 2_consec_gifts 3_consec_gifts 4 consec_gifts
--------- -------------- -------------- --------------
155755000
155757000
155758110
155759100
Do I need to use a cursor for this task? I lack experienced in using cursors is there an alternative method someone could suggest?
Thanks in advance.
I have a table with
EmpNum, Date, Abstype
What I want is to pull a list of all the employees and the Monday date of employees who have an absence on a Monday --> Friday consecutively.
eg Table
EmpNum, Date, Abstype
001 07/23/2012 VAC *Monday
001 07/24/2012 VAC
001 07/25/2012 VAC
001 07/26/2012 VAC
001 07/27/2012 VAC
003 07/23/2012 VAC * Monday
[code]...
As these are the 2 that run from monday-friday
I have a table with the following :
Cust id Address
1234 25 main street apt 78961
5678 25 ohio dr apt 567891
7890 25 lee lane apt 6789
I want to pull only the 6 consecutive numbers from my address , so only cust id 5678 should be displayed.
I have the following table which shows the location of a person at 1 hour intervals
IdEntityIDEntityNameLocationIDTimexdelta
11MickeyClub house03001
21MickeyClub house04001
31MickeyPark05002
41MickeyMinnies Boutique06003
51MickeyMinnies Boutique07003
61MickeyClub house08004
71MickeyClub house09004
81MickeyPark10005
91MickeyClub house11006
The delta increments by +1 every time the location changes.I would like to return an aggregate grouped by delta as per example below.
EntityNameLocationIDStartTimeEndTime
MickeyClub house03000500
MickeyPark05000600
MickeyMinnies Boutique06000800
MickeyClub house08001000
MickeyPark10001100
MickeyClub house11001200
I am using the following query (which works fine):
select
min(timex) as start_date
,end_date
,entityid
,entityname
,locationid
[code]....
However I would like to not use the delta (it takes effort to calculate and populate it); instead I am wondering if there is any way to calculate it as part / whilst running the query.
Problem 2
I have the following table which shows the location of different people at 1 hour intervals
IdEntityIDEntityNameLocationIDTimexDelta
11MickeyClub house09001
21MickeyClub house10001
31MickeyPark11002
42DonaldClub house09001
52DonaldPark10002
62DonaldPark11002
73GoofyPark09001
83GoofyClub house10002
93GoofyPark11003
I would like to return an aggregate grouped by person and location.For example
EntityIDEntityNameLocationIDStartTimeEndTime
1MickeyClub house09001100
1MickeyPark11001200
2DonaldClub house09001000
2DonaldPark10001200
3GoofyPark09001000
3GoofyClub house10001100
3GoofyPark11001200
What modifications do I need to the above query (Problem 1)?
I'm trying extract a count of consecutive numbers, or "unbroken" years inthis case, at any particular given time.For example (simplified):CREATE TABLE #Customers(CustNo INT,YearNo INT,IsCust CHAR(1))INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2006, 'Y')INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2005, 'Y')INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2004, 'Y')INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2003, 'N')INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2002, 'N')INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2001, 'Y')INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2000, 'Y')SELECT * FROM #CustomersCustNo YearNo IsCust----------- ----------- ------999 2006 Y999 2005 Y999 2004 Y999 2003 N999 2002 N999 2001 Y999 2000 YIn 2006 CustNo 999 would have been active for 3 years, 2004 for 1, 2001 for2, etc. Ideally I'd feed it a single year to lookupI'm resisting the urge to create cursor here -- anyone have any hints?....Chris.
View 10 Replies View RelatedHello,Can someone please help me with a query?The table looks like this:BookedRooms===========CustomerID RoomID BookDateID1 1 200507011 1 200507021 1 200507031 1 200507091 1 200507101 1 200507111 1 20050712Desired result:CUSTOMER STAYS==============CustomerID RoomID ArriveDateID DepartDateID1 1 20050701 200507031 1 20050709 20050712Basically, this is for a hotel reservation system. Charges varynightly, and customer changes (shortening/extending stay, changingrooms, etc) happen quite often. Therefore, the entire stay is bookedas a series of nights.The length of the stay is never known, so it needs to be derived viathe Arrive and Depart Dates, based on the entries in the table.Notice, customers often stay in the same room, but with gaps between,so a simple MIN and MAX doesn't work. The output needs to showconsecutive nights grouped together, only.I've researched this quite a bit, but I just can't seem to make itwork.Any help would greatly be appreciated.Thanks!
View 15 Replies View RelatedWe have an existing SSRS server, and have just created a new child domain. We'll be migrating users from the parent to the child, and want to add the users of that new domain with access to SSRS. In the parent domain they are able to access, but after migration with the child domain account, they cannot.
I have added the group CHILDDomain Users with a system user role on SSRS, and PARENTDomain Users was already there.
Is there any additional step I should/could take to get this active?
I have had this issue just pop up. I have local users who can connect fine, but my users that require connection by VPN cannot connect. I get the server not available or access denied error. I did confirm that the VPN'ers are connected to the network correctly and can see that their shares and mappings are correct. Any ideas? Thanking you all in advance!!
View 6 Replies View RelatedI am trying to revert back to Windows 7 after upgrading to Windows 10, however it will not let me and the following message occurs: "Remove new accounts.Before you can go back to a previous version of Windows, you'll need to remove any user accounts you added after the most recent upgrade. The accounts need to be completely removed, including their profiles.You created one account (NT SERVICEMSSQLSERVER) Go to Settings> Accounts> Other users to remove these accounts and then try again".However I did not create any new users and there are no other users listed in the Accounts section.
View 2 Replies View RelatedHi everyone,
I'm hoping someone can help me with some sql statements. I have a temp table that contains 30 dates that a student has missed in the last year. I also have a holiday table of when training was not available. I want to find out if there are 6 consecutive days missed excluding weekends and holidays (from the holiday table). I know this is some nasty looping statement but I can't get my brain around it.
I would like do this in a stored proc but I could use C# if necessary.
Thanks in advance, Jessica
SQL Server 2008.
From individual event logs I have generated a table where arrivals and departures at a location are registered per device. As there are multiple registration points, there might be multiple consecutive registrations per location.
If this is the case I need to filter those out and have one registration per location and in the result I need to get the earliest arrival and the latest departure of these consecutive rows.
Part of the table:
logIDdeviceIDArrivedDepartedLocationIDGrp1Grp2Grp
3485441082013-02-07 17:51:05.0002013-02-07 17:51:15.0005110
3492041082013-02-07 17:51:15.0002013-02-07 17:51:26.0005220
3500241082013-02-07 17:51:27.0002013-02-07 17:51:37.0003312
3508941082013-02-07 17:51:41.0002013-02-07 17:51:54.0004413
[Code] ....
So as long the field LocationID is the same in the next row, it needs to be grouped.
I have added the rows Grp1, Grp2, Grp in an attempt to get an unique grouping number with the following script in the select statement:
,ROW_NUMBER() OVER(PARTITION BY DeviceID
ORDER BY logID) AS Grp1
,ROW_NUMBER() OVER(PARTITION BY DeviceID, LocationID
ORDER BY logID) AS Grp2
,ROW_NUMBER() OVER(PARTITION BY DeviceID
ORDER BY logID)
-
ROW_NUMBER() OVER(PARTITION BY DeviceID, LocationID
ORDER BY logID) AS Grp
By subtracting Grp2 from Grp1 (Grp = Grp1 - Grp2) I hoped to get an unique group number for each set of equal consecutive locations, however the Grp2 column does not restart from 1 each time the LocationID changes: Grp2 in line 7 should have been 1 again, but it is 2 because this is the second row with LocationID = 3 in the list.
Problem 1: I have the following table which shows the location of a person at 1 hour intervals
IdEntityIDEntityNameLocationIDTimexdelta
11MickeyClub house03001
21MickeyClub house04001
31MickeyPark05002
41MickeyMinnies Boutique06003
51MickeyMinnies Boutique07003
61MickeyClub house08004
71MickeyClub house09004
81MickeyPark10005
91MickeyClub house11006
The delta increments by +1 every time the location changes.
I would like to return an aggregate grouped by delta as per example below.
EntityNameLocationIDStartTimeEndTime
MickeyClub house03000500
MickeyPark05000600
MickeyMinnies Boutique06000800
MickeyClub house08001000
MickeyPark10001100
MickeyClub house11001200
I am using the following query (which works fine):
select
min(timex) as start_date
,end_date
,entityid
,entityname
,locationid
[code]....
However I would like to not use the delta (it takes effort to calculate and populate it); instead I am wondering if there is any way to calculate it as part / whilst running the query.
Problem 2:I have the following table which shows the location of different people at 1 hour intervals
IdEntityIDEntityNameLocationIDTimexDelta
11MickeyClub house09001
21MickeyClub house10001
31MickeyPark11002
42DonaldClub house09001
52DonaldPark10002
62DonaldPark11002
73GoofyPark09001
83GoofyClub house10002
93GoofyPark11003
I would like to return an aggregate grouped by person and location.
For example
EntityIDEntityNameLocationIDStartTimeEndTime
1MickeyClub house09001100
1MickeyPark11001200
2DonaldClub house09001000
2DonaldPark10001200
3GoofyPark09001000
3GoofyClub house10001100
3GoofyPark11001200
What modifications do I need to the above query (Problem 1)?
Hello,
I have a table with 3 columns: Item# | Date | ItemAmount.
Everyday there is a number of transactions entered. An Item# can only be entered once par day (if it has occurred that day).
What I want to do is to :
retrieve the number of total days where an Item has been entered for more than 2 consecutive days (for the month).
Example: if item I022 has been entered Monday and wed, then ignore, but if it's been entered Mon, Tues then return 2, if Mon, Tues, Wed then return 3 because the days are consecutive.
Does anyone have an idea.
thanks in advance,
I have the following variables VehicleID, TransactDate, TransactTime, OdometerReading, TransactCity, TransactState.
VehicleID is the unique vehicle ID, OdometerReading is the Odometer Reading, and the others are information related to the transaction time and location of the fuel card (similar to a credit card).
The records will be first grouped and sorted by VehicleID, TransactDate, TransactTime and OdometerReading. Then all records where the Vehicle ID and TransactDate is same for consecutive rows, AND TransactCity or TransactState are different for consecutive rows should be printed.
I also would like to add two derived variables.
1. Miles will be a derived variable that is the difference between consecutive odometer readings for the same Vehicle ID.
2. TimeDiff will be the second derived variable that will categorize the time difference for a particular vehicle on the same day.
My report should look like:
VehID TrDt TrTime TimeDiff Odometer Miles TrCity TrState
1296 1/30/2008 08:22:42 0:00:00 18301 000 Omaha NE
1296 1/30/2008 15:22:46 7:00:04 18560 259 KEARNEY NE
Can someone please help me here?
Thanks,
Romakanta
Rajarajan writes "Kindly don't ignore this as regular case.
This is peculiar.
I need to delete one of duplicate records only if they occurs consecutively.
eg.
1. 232
2. 232
3. 345
4. 567
5. 232
Here only the first record has to be delete. Kindly help me out.
Thank you.
Regards,
R.Rajarajan"
Hi Fellows
I am trying to organize these information.the data come form two tables that are not relating, but I did a join and my primary key is the filed polygon. I have a list of points(geomseq) for each polygon but the number of points(geomseq) can change. I have this inofrmation in a data base.
geomseq polygon xc yc x1 y2
0 17 21 22.5 0 0
3 17 21 22.5 40 40
2 17 21 22.5 0 20
4 17 21 22.5 20 0
1 17 21 22.5 0 10
5 17 21 22.5 10 10
1 18 40 40.5 0 20
4 18 40 40.5 20 30
0 18 40 40.5 0 0
3 18 40 40.5 10 20
2 18 40 40.5 5 15
5 18 40 40.5 30 35
6 18 40 40.5 40 40
9 18 40 40.5 80 80
7 18 40 40.5 45 45
8 18 40 40.5 50 60
I want something like this
geomseq polygon xc yc x1 y2
0 17 21 22.5 0 0
1 17 21 22.5 0 10
2 17 21 22.5 0 20
3 17 21 22.5 40 40
4 17 21 22.5 20 0
5 17 21 22.5 10 10
0 18 40 40.5 0 0
1 18 40 40.5 0 20
2 18 40 40.5 5 15
3 18 40 40.5 10 20
4 18 40 40.5 20 30
5 18 40 40.5 30 35
6 18 40 40.5 40 40
7 18 40 40.5 45 45
8 18 40 40.5 50 60
9 18 40 40.5 80 80
regards and thanks in advance
Edwin
hi alli've got two tables called "webusers" (id, name, fk_country) and "countries" (id, name)
at the meantime, i've a search-page where i can fill a form to search users. in the dropdown to select the country i included an option which is called "all countries".
now the problem is: how can i make a stored procedure that makes a restriction to the fk_country depending on the submitted fk_country parameter?it should be something like
SELECT * FROM webusers(if @fk_country > 0, which is the value for "all countries"){ WHERE fk_country = @fk_country}
who has an idea how to solve this problem?
I'm trying to write an algorithm that returns the most recent and longest consecutive streak of positive or negative price changes in a given stock. The streak can extend over null weekends, but not over null weekdays (presumably trading days).
For example, lets say Google had end of day positive returns on (any given) Tuesday, Monday, and previous Friday, but then Thursday, it had negative returns. That would be a 3 day streak between Friday and Tuesday. Also, if a date has a null value on a date that is NOT a weekend, the streak ends.
In the following code sample, you can get a simplified idea of what the raw data will look like and what the output should look like.
set nocount on
set datefirst 7
go
if object_id('tempdb.dbo.#raw') is not null drop table #raw
create table #raw
[Code] .....
I should also mention that this has to be done over about half a million symbols so something RBAR is especially unappealing.
How can i calculate datediff (in minutes) between two consecutive rows without using CTE & Temp tables?
I was using this successfully, but i need something without CTE and temp tables (both of them are not supported in the tool i am trying to generate a report using custom query).
WITH CTE AS (SELECT ROW_NUMBER() OVER (ORDER BY Id desc) AS RowNo, * FROM MyTable)
SELECT t1.*, ISNULL(DATEDIFF(mi, t2.CreateDate, t1.CreateDate), 0) AS Duration
FROM CTE t1
LEFT JOIN CTE t2 ON t1.RowNo = t2.RowNo - 1
ORDER BY t1.Id desc
We have customer accounts that we measure usage. We want to run a report for all customers whose current usage is 0 and a count of how many months it has been zero consecutively. Here is an example.
declare @YourTable table (
CustomerID int,
ReadDate datetime,
usage int
)
insert into @YourTable select 1,' 1 mar 2014',0
insert into @YourTable select 1,' 1 feb 2014',0
[Code] ....
This should return
1,3
2,1
This is what I am currently using but it isn't working right
WITH cte
AS
(
SELECT *,COUNT(1) OVER(PARTITION BY cnt,CustomerID) pt FROM
(
SELECT tt.*
[Code] .....
I've;
Id.........|......type....|.....Value
2001................1...............20
2001................2...............32
2002................1...............19
2002................2...............21
2003................1............... 3
2003................2...............30
I want;
Id........|.......Value
2001.................12
2002..................2
2003.................27
Hi,
The following query should return a list of clr_id's that have a match in at least 1 of the other fields mentioned in the joins.
declare @keyWord varchar(40)
set @keyWord = 'merc'
set NOCOUNT on
SELECT distinct clr.clr_id
FROM CLR
LEFT OUTER JOIN CO ON CLR.CO_ID = CO.CO_ID
LEFT OUTER JOIN CLR_NM ON CLR.CLR_ID = CLR_NM.CLR_ID
LEFT OUTER JOIN CLR_USE_YR ON CLR.CLR_ID = CLR_USE_YR.CLR_ID
LEFT OUTER JOIN MODL ON CLR_USE_YR.MODL_ID = MODL.MODL_ID
LEFT OUTER JOIN PAINT_CD ON CLR.CLR_ID = PAINT_CD.CLR_ID
WHERE co.long_nm like '%'+@keyWord+'%'
OR clr_nm.clr_nm like '%'+@keyWord+'%'
OR clr_use_yr.yr_num like '%'+@keyWord+'%'
OR paint_cd.paint_cd like '%'+@keyWord+'%'
OR modl.modl_nm like '%'+@keyWord+'%'
The query runs at 3secs.
Could I improve the query somehow?
I was thinking that, since I actually need a distinct set of clr_id's, I should somehow check only the clr_id's that don't have a match in any of the previous joins.
I am fairly new to SQl and PHP and have a question. I have a database that we enter in when a person buys a season ticket for our baseball team. I want to check and see who bought in 2013 but has not yet bought in 2014. How do I do that?
THID is the ticket holder ID and there is a year. I can do the select * from Passes where Year = '2014' and get everyone who has bought in 2014. How do I compare that with the 2013 and show people who have not bought yet in 2014?
Example entry:
THID PassType Year
15 2 2013
21 1 2013
10 1 2013
21 1 2014
With this - Ticket holder 21 has bought in both years so I wouldn't want his name to come up. I would want to see that ticket holders 15 and 10 haven't yet bought in 2014.
How to create a row number for a consecutive action. Example: I have a listing of people who have either completed a goal or not. I need to count by person the number of consecutively missed goals.
My sql table is this:
PersonId, GoalDate, GoalStatus (holds completed or missed)
My first thought was to use the rownumber function however that doesn’t work because someone could complete a goal, miss a goal, then complete one and when they complete a goal after a missed goal the count has to start over.
I want to assign consecutive numbers to a block of data, where block of data is based on days consecutive to each other i.e., one day apart.
Date format is: YYYY-MM-DD
Data:
TestId TestDate
----------- -----------------------
1 2011-07-21 00:00:00.000
1 2011-07-22 00:00:00.000
1 2011-07-27 00:00:00.000
[Code]....
The OrderId is the column I am trying to obtain using my following cte code, but I can't work around it.
I usually do this through Access so I'm not too familiar with the string functions in SQL. My question is, how do you remove characters from the middle of a string?
Ex:
String value is 10 characters long.
The string value is X000001250.
The end result should look like, X1250.
I've tried mixing/matching multiple string functions with no success. The only solution I have come up with removes ALL of the zeros, including the tailing zero. The goal is to only remove the consecutive zeroes in the middle of the string.
I have a table like this.
CREATE TABLE Table1
([S_ID] varchar(7), [S_ACTV_CODE] varchar(4), [S_USER] varchar(5), [S_DATETIME] varchar(19), [S_ACT_IND] int)
;
INSERT INTO Table1
([S_ID], [S_ACTV_CODE], [S_USER], [S_DATETIME], [S_ACT_IND])
VALUES
('AAA-111', NULL, 'USER1', '2015-06-15 00:21:06', 0),
('AAA-111', '2', 'USER1', '2015-06-15 00:21:07', 0),
[code]....
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
So the result should be as below:
S_ID S_ACTV_CODE_PREV S_ACTV_CODE_CURR S_USER S_START_TIME
S_END_TIME TIME_SPENT (in Sec)
AAA-111 NULL NULL USER1 2015-06-15 00:21:06
2015-06-15 00:21:07 1
AAA-111 NULL 2 USER1 2015-06-15 00:21:07
2015-06-17 03:20:33 183566
AAA-111 2 4 USER2 2015-06-17 03:20:33
[code]....
I'm trying to come up with an elegant, simple way to compare twoconsecutive values from the same table.For instance:SELECT TOP 2 datavalues FROM myTable ORDER BY timestamp DESCThat gives me the two latest values. I want to test the rate ofchange of these values. If the top row is a 50% increase over the rowbelow it, I'll execute some special logic.What are my options? The only ways I can think of doing this arepretty ugly. Any help is very much appreciated. Thanks!B.
View 22 Replies View RelatedHi Team,
In SQL Enterprise Manager, when we expand "Database -->Users", we see the
users there. When we expand "Security --> logins" we see the same users there.
Can you differentiate these two.
Thanks
Santhosh