TOP 10 Query For Each User

Mar 26, 2004

I have a table that keeps a log of al user inquiries. The business wants to a report that shows the last ten inquiries each user has made:

A sample result would look like:

User, Date, InquiryID
George, 10/1/2003, 1000
George, 10/2/2003, 1001
George, 10/3/2003, 1002
George, 10/4/2003, 1003
George, 10/5/2003, 1004
George, 10/6/2003, 1005
George, 10/7/2003, 1006
George, 10/8/2003, 1007
George, 10/9/2003, 1008
George, 10/10/2003, 1009
Harold, 11/1/2003, 1010
Harold, 11/2/2003, 1011
Harold, 11/3/2003, 1012
Harold, 11/4/2003, 1013
Harold, 11/5/2003, 1014
Harold, 11/6/2003, 1015
Harold, 11/7/2003, 1016
Harold, 11/8/2003, 1017
Harold, 11/9/2003, 1018
Harold, 11/10/2003, 1019



DDL:

CREATE TABLE UserInquiries (
InqiuryID INT IDENTITY(1,1),
UserName Varchar(10),
InquiryDate datetime)


INSERT INTO UserInquiries (UserName, InquiryDate)
SELECT 'George', '10/1/2003'
UNION SELECT 'George', '10/2/2003'
UNION SELECT 'George', '10/3/2003'
UNION SELECT 'George', '10/4/2003'
UNION SELECT 'George', '10/5/2003'
UNION SELECT 'George', '10/6/2003'
UNION SELECT 'George', '10/7/2003'
UNION SELECT 'George', '10/8/2003'
UNION SELECT 'George', '10/9/2003'
UNION SELECT 'George', '10/10/2003'
UNION SELECT 'George', '10/11/2003'
UNION SELECT 'George', '10/12/2003'
UNION SELECT 'Harold', '11/1/2003'
UNION SELECT 'Harold', '11/2/2003'
UNION SELECT 'Harold', '11/3/2003'
UNION SELECT 'Harold', '11/4/2003'
UNION SELECT 'Harold', '11/5/2003'
UNION SELECT 'Harold', '11/6/2003'
UNION SELECT 'Harold', '11/7/2003'
UNION SELECT 'Harold', '11/8/2003'
UNION SELECT 'Harold', '11/9/2003'
UNION SELECT 'Harold', '11/10/2003'
UNION SELECT 'Harold', '11/11/2003'
UNION SELECT 'Harold', '11/12/2003'


I'm brain dead on other things right now. Any help would be gratefully accepted.

Regards,

hmscott

View 1 Replies


ADVERTISEMENT

SQL Server 2012 :: Query To Find User Who Last Modified User Roles / Access?

Dec 6, 2013

I would like to know if there is a way to find out who changed a users roles/access WITHOUT using the audit function. For example, if a user account was created and given SA access then changed to read only, how can I find out who made that change? I tried searching for an answer, but kept getting no results. I'm thinking this may tie into the sys.sysusers view?

View 3 Replies View Related

Failed To Generate A User Instance Of SQL Server Due To Failure In Retrieving The User's Local Application Data Path. Please Make Sure The User Has A Local User Profile On The Computer. The Connection Will Be Closed

Dec 7, 2006

This is my first time to deploy an asp.net2 web site. Everything is working fine on my local computer but when i published the web site on a remote computer i get the error "Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed" (only in pages that try to access the database)
Help pleaseee

View 3 Replies View Related

How Do We Determine Which User Database Tables Are Mostly Retrieved By User Or Modified By User?

May 22, 2008



Hi,
Please give the T-SQL script for this ? Thanks

Shanth


View 4 Replies View Related

User Input In SQL Query

Nov 24, 2004

Hi,

I am new to ASP.NET so pordon me if my questions seems to be stupid.

I was given an assign to develop a ASP that would return results from a SQL server.

The problem is that part of my query to the SQL server comes from the webform


select a.invnum, a.invdate, a.duedate, a.invamt,a.payamt from vpshead a inner join vendors
b on (a.vendnum = b.vendnum) where (a.vendnum = " & user.text & " ) and (b.vendpass = "
& Pass.text & " ) and (a.payflag <> 'V') and ( (a.invamt <> 0) or (a.payamt <> 0) )and (a.chkno = '') order by a.invdate"



As your can tell I am using the user.text and Pass.text in my query which will come from the web form.

I know this is wrong but how else can I do it?

Thanks

View 3 Replies View Related

User Activity Query

Jan 7, 2007

Hey!

I'm trying to produce a user activity table out of a table that contains rows on when users log in or out. However, I can't seem to get the query to 'pick' the right date time in the join.

So, I have a table like this:

ChangeIDUserIDLoggedInDateTime
7107/30/06 12:30PM
6117/30/0612:20PM
5107/29/06 11:00AM
4117/29/06 10:30AM
3205/03/06 6:30PM
2215/03/065:30PM
1205/01/06 9:30AM
0215/01/06 9:15AM


And I want to make it into this table:


UserIDLoggedInDateLoggedOutDateDuration
17/29/06 10:30AM7/29/06 11:00AM30
17/30/06 12:20PM7/30/06 12:30PM10
25/01/06 9:15AM5/01/06 9:30AM15
25/03/065:30PM5/03/06 6:30PM60



Here is what I came up with, but it doesn't seem to be working right :eek:


select
distinct (userin.changeid),
userin.userid,
userin.createdate as LoggedInDate,
userout.createdate as LoggedOutDate,
DateDiff(mm,userin.createdate,userout.createdate) as Duration
from userChange userin
inner join (
select * from userChange
where loggedin = 0
) userout on userin.userid = userout.userid
where and userin.loggedin = 1 and userin.createdate < userout.createdate
order by userin.createdate desc

View 1 Replies View Related

Query For Db User Creation In MS SQL

Jan 24, 2007

HI everybody...

Can anyone help me to know the

Query to Create SQL database Users in MS SQL

through the query engine of MS SQL Server Management Studio..

I am struggling with the same and I tried the one

create user <user_name> for login <login_name>.

But it seems that it will create the windows user itself and not the sql user.

I want a query similar to create user <user_name> identified by <password> which is used in ORACLE.

Please help me in this situation.

Thanks in advance


dance

View 1 Replies View Related

How To Get User Input In Query

Nov 30, 2006

I m new to SQL Server. Before this I was using access. In access one can have user inputs easily .. for example

where receivedate=[enter date]



how I can get userinputs in SQL server Query....



Forgive me if it is a dumb question

View 6 Replies View Related

Query That Adds Data For Every User

Feb 13, 2007

Dear Masters;
I have a Messages table; I use this table to post System Messages to my users. But what I don't know is how can I add data for multiple users. I mean I want to add same message for multiple users (ex: Please update your infos). In the belove table I have some messages for users 100 and 200; How can I add same (please update your infos) messages to both users?
Ex: Messages Table
ID    UserID    Msg
1      100         "Hello"
2      100         "Hi"
3      200         "Hello"
 
Thanks..

View 8 Replies View Related

Pass User Name As A Parameter In A Query

May 13, 2007

just getting started with my first db driven web project...
I am using a MySql database with membership and roles and got that working fine. I have tables containing details of courses that users are enrolled on etc. and want to display a list of courses for the user that is signed in, so he can continue lessons on the one of his choice.
How do I pass the users name to the database query for a DataList control. So far I have tried lots of variations of the following:
<asp:SqlDataSource ID="dsCourses" runat="server" ConnectionString="<%$ ConnectionStrings:xxx %>"    ProviderName="<%$ ConnectionStrings:xxx.ProviderName %>"    SelectCommand="SELECT c.CourseName FROM courses c, enrolments e        WHERE c.CourseID=e.CourseID AND e.Username='<% =User.Identity.Name %>'"></asp:SqlDataSource>
<asp:DataList ID="DataList1" runat="server" DataSourceID="dsCourses">    <ItemTemplate>        <asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl="Lesson.aspx">'<%# Eval("CourseName") %>'</asp:HyperLink><br />    </ItemTemplate></asp:DataList>&nbsp;</p>
However, the <% =User.Identity.Name %> doesn't work here (but it does elsewhere on the page) - not sure why?? The code works fine if I hard code a user name into the select statement.
Suggestions pleeeeeese!!
 

View 1 Replies View Related

Query To Retreive User Tables In Sql Db

Jan 2, 2004

Is there a way to create a query that will return all user tables inside a sql db

Thanx

View 4 Replies View Related

Using A SQL Query To Create A User Account.

Sep 17, 2005

I need to be able to have users run a query under the 'sa' account that will create another account that will just be used for reading and writing to a specific database.  Is there a way to do this with just a sql script or can you only set up logins and accounts from the enterprise manager?

View 1 Replies View Related

Can I Query The NT User Manager For UserName?

Jun 23, 2000

I have a need to identify the name of a user and would like to access the NT userlist. Or failing a realtime access, can I achieve a periodic update of a SQL table with userid, username from the NT userlist?

TIA
Bob Morrow - Tennessee General Assembly

View 2 Replies View Related

Retrieving User Names Through SQL Query

Feb 20, 2000

I am trying to retrieve the user names of all users within a database. The problem is, that I have only created a login within SQL 7.0 for the NT group that the users belong to.

As such, when I try to query the syslogins table I only get the NT group.

Is there any way of retrieving the users that belong to this NT group.

Any help or suggestions on this would be greatly appreciated.

Thanks,

Karl

View 1 Replies View Related

User Permissions Through Query Analyzer!

Aug 23, 2001

Hi Everybody,

For a particular user, how to find what are all the tables he has been given the grant permission,etc., thru' query analyzer.

Can you please tell me, is there any command or system stored procedure is available to find this problem.


regards,
John

View 1 Replies View Related

User Input In Query Analyzer

Jul 15, 2004

Hi folks, i am lookin for an option in the QA to take user input while executing a query. just like we do in SQLPLUS:
SELECT * FROM dept WHERE deptno=$abc
here it would ask me for the deptno. I guess it's a very handy feature and we could test our variables and calculations withing the procedures etc.

Is there a way, or any substitute?


Howdy!

View 14 Replies View Related

Database Role/User Query

Sep 21, 2006

Anyone have a tsql query that will give me a listing of database roles and their users already put together?

View 1 Replies View Related

Query - Most Recent Entry Per User

Jul 16, 2013

There is a large data table called emp_history on an SQL Server, which contains the employment history of each employee. The significant columns are as follows:

entry_id employee_id start_date position

where entry_id is the primary key and auto-increments.What I need, is to create a query to extract the employee_id and position, based on the most recent record (most recent start_date) for each employee.

View 6 Replies View Related

Query Trigger Email To User

Aug 6, 2013

I need to trigger an email to user when a work order due date is approaching 1 day prior to the due date. Also I need to trigger when a user adds, delete, or modify work order to check on work order due.

Not sure how to query it in SQL.

Due Date = NeedByDate
user = AssignedTo

View 1 Replies View Related

Running Query From User Input

Jan 11, 2006

I would like to setup a query where the user is firstly prompted to input the information e.g. if they are trying to locate a particular person from a telephone database they would be prompted to enter the surname for that person.

View 4 Replies View Related

Query To Get All User Tables With Columns

Jul 20, 2005

Hi,I tried to create a simple view as followsCREATE VIEW V_ALL_USERTABLE_COLUMNSAS(SELECTOBJ.NAME as TableName,COL.NAME as ColName,TYP.NAME AS TYPEFROMSYSOBJECTS OBJ,SYSCOLUMNS COL,SYSTYPES TYPWHEREOBJ.TYPE = 'U'AND OBJ.ID = COL.IDAND COL.TYPE = TYP.TYPE)Combined with consistent naming conventions I will use this view toeasily find foreign keys; a laSELECT *FROM V_ALL_USERTABLE_COLUMNSWHERE ColName LIKE ('%user_id')There is something wrong with my view definition that I don't getthough; it doesn't return all the columns. I have a table with thefollowing definitionCREATE TABLE [dbo].[c_messages]([cid] [int] IDENTITY (1, 1) NOT NULL ,[touser_id] [int] NULL ,[tosession_id] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[fromuser_id] [int] NOT NULL ,[message] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[message_read] [bit] NOT NULL ,[logout] [bit] NULL) ON [PRIMARY]GOThe problem is that the select I used to define the view doesn'treturn the touser_id column. I have sort of a sneaking suspicion thatthe problem has to do with joining syscolumns.type to systypes.type,but I don't know what to do instead (I'd really like to include thetype; it's useful if I ever changed the type of a primary key and wantto check that I also changed all the foreign keys).Any help would be appreciated!

View 1 Replies View Related

Best Way To Set User Vars Based On Query

Mar 1, 2008

what's the best way to set user vars in ssis from a query. I have some components that set a from and to date in a table. I'd like to select those values right into some user vars in the pkg.

View 1 Replies View Related

Query To Connect To A Database With Different User

Jul 27, 2007

Hello:

Is there a way through T-SQL to run a store procedure and inside the store procedure, change the user, execute a select, and get back the original user that run the store procedure?

Thanks

View 4 Replies View Related

Need To Pass User Input Through To SQL Query

May 20, 2008

Hello Everybody,

I am sure this has been asked a thousand times before in a thousand different ways, but here goes...

I am in need of the ability to prompt users for a From Date and To Date to insert into a WHERE clause in SQL queries. What is the easiest least painful way of accomplishing this? A great many of our customers utilized SQL Server Express or MSDE, so they do not have the full gambit of SQL tools handy. Believe me when I say that I have searched high and low across the internet for this solution. I was hoping I might be able to accomplish this through VB Script or J Script. Or, anything reasonable.

Thanks in advance for your time and patience,

Rocky Mountain Tech

View 1 Replies View Related

Query Building Based On User Selections

Sep 22, 2006

I currently have a form that has different options in a list box such as:Status Codes

View 3 Replies View Related

Query Database User Input From ListBox

Jan 25, 2006

I have a problem selecting fields from a table where fields are equal to user input from a listbox. example
listbox of zip codes:
33023[red]22300[/red]39844[red]29339[/red]23883[red]38228[/red]
user wants to retreive highlight zip codes from database.connection working perfect.Thank you for your help.
 
        

View 2 Replies View Related

My Query And User Defined Fuction Problem

Feb 22, 2006

I have a table which has six fields ID, dateDue, dateReceived, dueday. month, ContactFYEmy query looks likeselect ID, DateDue, dateReceived, dueday, month, ContactFYEfrom Reportwhere (dbo.Report.DateDue BETWEEN dbo.udfDisplayTime(dueday, month, ContactFYE) AND DateDue )user defined functionCREATE FUNCTION dbo.udfDisplayTime ( @dueday int, @month int, @ContactFYE smalldatetime) RETURNS smalldatetime AS BEGIN DECLARE @ReturnString AS smalldatetime Declare @dueday1 as int if (@month =1)beginset @dueday1 = @dueday -7endelse if (@month =2)begin set @dueday1 = @dueday -14end else if (@month =3)begin set @dueday1 = @dueday -60endselect @ReturnString = DATEADD ( dd, @dueday1, @ContactFYE) Return @ReturnStringENDI got a incorrect result when using this query and user defined function.the result that I got was out of between displaytime and datdue. any idea for this , Thanks.

View 2 Replies View Related

T-SQL (SS2K8) :: User Wise Query Tracking

Apr 30, 2015

In my production server i created sql server user for limited permission. Now I want details of what are all the Query executing in user wise .

View 1 Replies View Related

SQL Sever Side Query To Filter For User ID

Apr 23, 2008

I currently have a query in an access database that filters the records in a Sql Linked table for their user ID. So basically i use Environ("UserName") to filter for a field that contains their user name. I want to secure this and put it on the SQL Server so there is no way anyone can see any records other then the ones that are filtered for them. Does anyone know how to do this? I think it will be some SQL query.

I'm still learning sql server 2005.

Thanks.

View 3 Replies View Related

Query To Add Windows Authenticated User Via Remote

Jun 17, 2007

I was able to do this on a Windows XP machine but I am not able to get this to work on a Windows 2003 Server machine.
I am connecting to the Windows 2003 Server and MS SQL Express via remote desktop on the lan.

This is what worked for me before:
EXEC sp_grantlogin '4800X2ASPNET'
USE MyDB
EXEC sp_grantdbaccess '4800X2ASPNET'
EXEC sp_addrolemember 'db_owner', '4800X2ASPNET'

But for some reason now I am getting this error:
Msg 15401, Level 11, State 1, Procedure sp_grantlogin, Line 49
Windows NT user or group '4800X2ASPNET' not found. Check the name again.
Msg 15401, Level 16, State 1, Line 1
Windows NT user or group '4800X2ASPNET' not found. Check the name again.
Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 75
User or role '4800X2ASPNET' does not exist in this database.

I thought what I needed to add was the name of this pc that will connect remotely, but that doesn't seem to be working.
What weird is the old copy of this query I used had the local pc as the computer name. If I exchange the computer name from the remote pc name to the local 2003 Server machine name, the query works fine. Although that still doesn't let me add connections via Web Developer Express without error. It doesn't make sense to me anyhows, how could I have used the local machine's name and not the remote machine's name to get this to work previously. So confused.

Thank you in advance for help.

View 1 Replies View Related

Finding The User Who Do Update In Query Analyser?

Nov 20, 2007

Hi there!Is there any way to find the user who did some transaction of updatein query analyser in certain time ?The update was done manually in query analyser and is not throughsystem.Is there any way to check it?Any command or software to check this data as when certain table wasupdated?Thanks for the help!

View 7 Replies View Related

Query Only Freezing When Put Into A User Defined Function?

Apr 30, 2008

I'm experiencing quite an interesting issue with our database. We're currently running SQL Server 2005 Standard (x64) (v9.00.3054.00) and I have a block of code that just insists on not working within a UDF. Everything I've read seems to point that I'm doing this corerctly but apparently I'm missing something.

Executing the query below:

DECLARE @sp DATETIME
DECLARE @ep DATETIME
DECLARE @trackType INT

SET @sp = '4/21/2008'
SET @ep = '4/28/2008'
SET @trackType = 1

SELECT
c.unqempid, isnull(count(c.contactid),0) AS contacts, isnull(bookings.count,0) AS bookings, isnull(showed.count,0) AS showed, isnull(ow.totalPurchased,0) AS purchases, isnull(ow.totalSold,0) AS volume
FROM
contacts c WITH (NOLOCK)
LEFT JOIN
(
SELECT
b.unqempid, ISNULL(count(bookingid),0) AS count
FROM
bookings b WITH (NOLOCK)
INNER JOIN
contacts c WITH (NOLOCK)
ON c.contactid = b.relcontactid
WHERE
c.contactdt BETWEEN @sp AND @ep
GROUP BY
b.unqempid
) AS bookings
ON bookings.unqempid = c.unqempid
LEFT JOIN
(
SELECT
b.unqempid, ISNULL(count(bookingid),0) AS count
FROM
bookings b WITH (NOLOCK)
INNER JOIN
contacts c WITH (NOLOCK)
ON c.contactid = b.relcontactid
WHERE
c.contactdt BETWEEN @sp AND @ep AND didshow > 0
GROUP BY
b.unqempid
) AS showed
ON showed.unqempid = c.unqempid
LEFT JOIN
(
SELECT
c.unqempid, count(leadid) TotalPurchased, sum(saleprice) AS TotalSold
FROM
ordermgmt.dbo.orders o WITH (NOLOCK)
INNER JOIN
ordermgmt.dbo.appointments a WITH (NOLOCK)
ON a.weborderid = o.orderid
INNER JOIN
contacts c WITH (NOLOCK)
ON c.personid = a.leadid
INNER JOIN
bookings b WITH (NOLOCK)
on b.relcontactid = c.contactid
WHERE
c.contactdt BETWEEN @sp AND @ep
GROUP BY
c.unqempid
) as ow
ON ow.unqempid = c.unqempid
WHERE
c.contactdt BETWEEN @sp AND @ep
GROUP BY
c.unqempid, bookings.count, showed.count, ow.totalPurchased, ow.totalSold


Yields no issues however if I put this into a UDF, it runs and never finishes executing. I'm not quite sure what my issue was so I'm hoping someone could point out where my goof is.

Here is the function:

CREATE FUNCTION [dbo].[fnEmployeeSchedulingResults]
(
@sp DATETIME,
@ep DATETIME
)
RETURNS TABLE
AS
RETURN
(

SELECT
c.unqempid, isnull(count(c.contactid),0) AS contacts, isnull(bookings.count,0) AS bookings, isnull(showed.count,0) AS showed, isnull(ow.totalPurchased,0) AS purchases, isnull(ow.totalSold,0) AS volume
FROM
contacts c WITH (NOLOCK)
LEFT JOIN
(
SELECT
b.unqempid, ISNULL(count(bookingid),0) AS count
FROM
bookings b WITH (NOLOCK)
INNER JOIN
contacts c WITH (NOLOCK)
ON c.contactid = b.relcontactid
WHERE
c.contactdt BETWEEN @sp AND @ep
GROUP BY
b.unqempid
) AS bookings
ON bookings.unqempid = c.unqempid
LEFT JOIN
(
SELECT
b.unqempid, ISNULL(count(bookingid),0) AS count
FROM
bookings b WITH (NOLOCK)
INNER JOIN
contacts c WITH (NOLOCK)
ON c.contactid = b.relcontactid
WHERE
c.contactdt BETWEEN @sp AND @ep AND didshow > 0
GROUP BY
b.unqempid
) AS showed
ON showed.unqempid = c.unqempid
LEFT JOIN
(
SELECT
c.unqempid, count(leadid) TotalPurchased, sum(saleprice) AS TotalSold
FROM
ordermgmt.dbo.orders o WITH (NOLOCK)
INNER JOIN
ordermgmt.dbo.appointments a WITH (NOLOCK)
ON a.weborderid = o.orderid
INNER JOIN
contacts c WITH (NOLOCK)
ON c.personid = a.leadid
INNER JOIN
bookings b WITH (NOLOCK)
on b.relcontactid = c.contactid
WHERE
c.contactdt BETWEEN @sp AND @ep
GROUP BY
c.unqempid
) as ow
ON ow.unqempid = c.unqempid
WHERE
c.contactdt BETWEEN @sp AND @ep
GROUP BY
c.unqempid, bookings.count, showed.count, ow.totalPurchased, ow.totalSold
)



Any help would be greatly appreciated!

-Justin

View 19 Replies View Related

Performance, User Defined Function Or Sub Query

Mar 11, 2008

What is better for performance, using a user defined function or a sub query to perform an aggregate calculation in a select statement. for eg would it be best to call a user defined function which performs the below calculation of the sub query shown. I'm speaking purely from a performance point of view.


SELECT o.ordersid,


o.orders,

(SELECT SUM(i.total) FROM items AS i WHERE i.ordersid = o.ordersid) AS [total]

FROM order AS o

View 15 Replies View Related







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