Logs Table - Assign Serial Number To Techs

Jul 30, 2014

I have Logs table and want to assign a serial number to the techs using the following query

Select
Date,
Case_ID,
Site,
Dept,
Tech,
Start_Time,
ROW_NUMBER () OVER (PARTITION BY Date, Site, Dept, Tech ORDER BY Start_Time) as Row_Num
FROM
Logs
Where Date = Getdate()

I get the following results.

Date Case ID Site DeptTechStart TimeRow_Num
7/28/14 10023 TartvilleMaintcAmy P.7:301
7/28/14 56789 TartvilleMaintcRem W.8:051
7/28/14 23098 TartvilleMaintcAmy P.8:352
7/28/14 70004 TartvilleMaintcAmy P.9:103
7/28/14 12708 TartvilleMaintcMag O.10:001
7/28/14 10004 TartvilleMaintcAmy P.12:304
7/28/14 40056 TartvilleServiceJoe F.7:301
7/28/14 23458 TartvilleServiceJoe F.7:552
7/28/14 69200 TartvilleServiceRus T. 7:301

Please notice the cases in Maintc department. See how Amy P.'s shift is broken by Rem W. and Mag O. But the Row Number does not recognize this, it still says Amy P's case as 2 and 4 the even though Rem's and Mag's cases were in between.

This is what I really wanted.

Date Case ID Site DeptTechStart TimeRow_Num
7/28/14 10023 TartvilleMaintcAmy P.7:301
7/28/14 56789 TartvilleMaintcRem W.8:051
7/28/14 23098 TartvilleMaintcAmy P.8:351
7/28/14 70004 TartvilleMaintcAmy P.9:102
7/28/14 12708 TartvilleMaintcMag O.10:001
7/28/14 10004 TartvilleMaintcAmy P.12:301
7/28/14 40056 TartvilleServiceJoe F.7:301
7/28/14 23458 TartvilleServiceJoe F.7:552
7/28/14 69200 TartvilleServiceRus T. 7:301

I tried many combination of columns for Partition by () and Order by () and the best I can get is the result at the top. How should I achieve it.

View 1 Replies


ADVERTISEMENT

Urgent - Serial Number..

Apr 10, 2001

Hey folks,

Please give me the correct function name, otherwise please ignore in sending reply. I had used all the functions like identity, ident and so on. I need while selecting a querry, i need to generate serial numbers. I dont have identity column in my table. But, i need to generate serial numbers..

In Sybase SQL Any where, we have a function called Number (*) which will in turn generate serial numbers like ex..

Select Number (*) from x

The output will be

Number(*)
1
2
3
4
5
6
and so on..


I need a equivalent function in SQL Server 7.0 in which if i do select on that particular function with a table i should return above values..

Can any one solve this issue...

Please help me in this....

Urs
VJ

View 1 Replies View Related

Find Max Serial Number From 2 Tables

Feb 11, 2008

I need a query to find max serial number by comparing two different tables. Here is my requirementI am having two tables named Table1 and Table2. Each tables having more than 30,000,000 records.I want a simple query to find Max srno from two tables.For exampleIf Table1 max is 245 where partno=2 and ano=2and Table2 max is 343 where partno=2 and ano=2Then 343 is max serial noIf Table1 max is 435 where partno=2 and ano=2and Table2 max is 34 where partno=2 and ano=2Then 435 is max serial noI used this query but its taking more time  select max(v.MaxSrNo) from ((select max(MaxSrNo) as MaxSrNo from Table1 where partno=@partno and ano=@ano)union all (select max(MaxSrNo) from Table2 where partno=@partno and ano=@ano)) as v Pls give me a simple query to find max srno.

View 3 Replies View Related

Change SQL Server Serial Number

Jul 23, 2005

Good morning !Anyone knows how can i change a SQL Server Serial number without uninstallit ?Thanks in advancedJLuis

View 1 Replies View Related

SQL 2012 :: Add Serial Number Column And Sno 1 To 40000

Mar 28, 2014

Add sno 1 to end of the coloum

I have table like ...

400000 laks rows in my table

name address id location
raju hyd 6789 hyd
vamshi vizag 9876 hashinapur

o/p like

I want insert sno coloum and sno 1 to 400000

sno name address id location
1 raju hyd 6789 hyd
2 vamshi vizag 9876 hasthinapur
3
4
5
6
..
.
.
.
.
40000

View 2 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

SQL 2012 :: Add Serial Number Based On Group By

Mar 23, 2015

I need a new field added 'Field1' which will add SEQUENCE number 1,2,.. based ON GROUP BY MasterID..AND another field TotalCount which will COUNT total masterID (here it will be 2)

CREATE TABLE #Temp1
( MasterID INT,
ClientName VARCHAR(10),
ProductName VARCHAR(50)

[code]...

View 0 Replies View Related

Serial Number In Query, In MS Access And SQL Server

Jan 16, 2008

im trying to run a query, my first column should be serial number based on results, how can i do that for example

qry = "Select products, sum(prod_price), sum(prod_sellingprice) from sales group by products"

or

qry = "Select products, sellingperson from sales where seldate=#" & date() & "#"

i want first column of both queries as serial number based on results of query.

View 3 Replies View Related

Find Average Marks At Various Intervals Of Serial Number

Nov 24, 2013

I have this table of Marks as shown below. All I need is to find the average Marks at various intervals of S.no. That is I need averages at every 3rd S.No. as shown.

S.No. Marks
1 ------ 5
2 ------ 5
3 ------ 6 1st Average Value here (16/3)
4 ------ 5
5 ------ 6
6 ------ 7 2nd Average Value here (18/3)
7 ------ 7
8 ------ 7
9 ------ 8 3rd Average Value here (22/3)
10 ----- 8
11 ----- 9
12 ----- 8 4th Average Value here (26/3)

So basically I need a new table which will have 4 average values for the table above. Of-course the table can be much bigger and the average values can be at any nth value of S.No.

View 12 Replies View Related

Transact SQL :: Two Row Serial Number Date Compare And Take More Than 5 Hours Between Two Rows

Jul 1, 2015

A vehicle loading confirm after that what time its gated out so i want to take the time duration between finish loading and gate out, find sample table records , i want to take more than 5 hrs difference between finish loading and gate out.

tld_tripno
tld_sno
tld_activitycode
tld_location
tld_actualdate

TLM3004242015

[Code] .....

I want to take the result like this 

Tld_tripno
Finish Loading
Gate Out
Date and Time difference

TLM3004242015
2015-05-11 19:58:00
2015-05-12 08:42:00
12:44:00

View 10 Replies View Related

Transact SQL :: Retrieve Latest Record Of Serial Number With Multiple Entries

Sep 30, 2015

I work for an organization that repairs serialized devices. Each time a device is repaired it's serial number is recorded in a database table along with the date it was repaired along with other information about the device. There are multiple cases where a unit has been repaired more than once.

I am trying to write a query that will return the serial only once and that record will be the record of the latest repair date. To sum it up,

Return a list of serials where if a serial exists more than once in the table, return only the instance of the serial record(s) with the max(created_dt). The end result will be a list of distinct serial numbers.

Here is my Query. The problem I believe is in my sub-query but I am not sure how to structure it.

SELECT
S.Id
, RMA
, PinSerial
, L4Serial
, L4Model

[Code] ....

View 3 Replies View Related

Loop Through Record Set And Assign Number

Nov 7, 2013

I am new to SQL Server (coming from Oracle background) and have a large table I need to loop thru and assign a number 1 thru 5 on each record.

below is an example of how I would do it in Oracle.

declare
num number := 1;
cursor c_rec is SELECT rowid, t.* FROM temp t order by t.column;
begin
for d_rec in c_rec
loop
update temp set column = to_char(num)

[Code] ....

View 7 Replies View Related

How To Assign Random Number To One Column For 500 Records?

Aug 4, 2005

I try to set up a testing sample table which contain one  integer project_ID field for table Sample around 500 records, and want the project_ID to be random number within 1 to 99, how to implement script todo it?
thanks!

View 1 Replies View Related

Transact SQL :: Assign Unique Number To Records?

Nov 15, 2015

Lets say I have a table - tblProducts

View 4 Replies View Related

Transact SQL :: How To Assign Random Number Between 0 And 1 To A Record

Nov 17, 2015

I need to assign a random number between 0 and 1 to all eligible cases of cancer. 

I have a file of records like:
patientid
tumid
site

How can I assign a random number to each record?

View 10 Replies View Related

Creating Serial Numbers In A Table.

Apr 2, 2001

In microsoft sqlserver 7.0 i like to what is function name to generate serial numbers in a table.

For ex: in sybase we have number* which will automatically generate unique
numbers for each row.

It will be great if you could reply at the earliest

Tanks
vijay

View 2 Replies View Related

NEtwork Techs Versus DBA's

Jun 25, 2004

I gotta network tech that I work with from time to time. Hes gonna migrate a access database over to sql. He says it should be easy its a flat file can just do it through enterprise manager. I warned him that datatypes can become an issue (kinda have to know your db) he looked at me like I'm an idiot and proceeded to migrate the tables over to sql...Needless to say he got alot of error messages and is now totally confused. Now let me ask some experts who really Know Databases, do you ever have problems with Network Techs who think they know all

View 14 Replies View Related

Transposing Repetitive Serial Fields Into Table Structure

Nov 27, 2006

Dear All,I'm attempting to create a query that will transpose repeated fieldsinto a single table structure. Can anyone think of how this can be doneas I'm stumped at the minute? I'd like to do this without having tocreate a cursor due to the overheads and performance issues associatedwith cursors. The table may also include additional fields which I'mnot interested in.Serial Data is like this.............IkeyIval-----------------------------------------------RAF_EMAILJoin Bytes!RAF_FIRSTNAMEtestFirstName1RAF_LASTNAMEtestLastname1RAF_EMAILJoin Bytes!RAF_FIRSTNAMEtestFirstName2RAF_LASTNAMEtestLastname2....Transposed into table like this ..............EmailFirstnameLastname--------------------------------------------------------------------------Join Bytes!testFirstName1testLastname1Join Bytes!testFirstName2testLastname2....Any help, much appreciated ...Kind Regards,Tim-------------------------------------------------------------------------------------NOTE: these create temporary tables ....DECLARE @XML TABLE(ikey VARCHAR(200),ival VARCHAR(1000))INSERT INTO @XMLSELECT 'RAF_EMAIL', 'testemail1@hotmail.com'UNION ALL SELECT 'RAF_FIRSTNAME', 'testFirstName1'UNION ALLSELECT 'RAF_LASTNAME', 'testLastname1'UNION ALLSELECT 'RAF_EMAIL', 'testemail2@hotmail.com'UNION ALL SELECT 'RAF_FIRSTNAME', 'testFirstName2'UNION ALLSELECT 'RAF_LASTNAME', 'testLastname2'UNION ALLSELECT 'FORM_CATEGORY', 'nothing'UNION ALLSELECT 'NO_DOGS', '1'DECLARE @RESULTS(EMAIL,FIRSTNAME,LASTNAME)

View 1 Replies View Related

Default Number Of Error Logs=6 - What Is Best Practice?

May 8, 2007

What is best practise for what this number should be?



I have seen guidance saying set to a number greater than 25000, but not from any source I particularly know or trust. (I checked SANS, NIST-CIS and the NSA, but I couldn't find anything.

View 1 Replies View Related

How Can I Assign Data Returned From A Stored Procedure Into The Return Table Of A Table Valued Function

Apr 18, 2007

Here is the scenario,
I have 2 stored procedures, SP1 and SP2

SP1 has the following code:

declare @tmp as varchar(300)
set @tmp = 'SELECT * FROM
OPENROWSET ( ''SQLOLEDB'', ''SERVER=.;Trusted_Connection=yes'',
''SET FMTONLY OFF EXEC ' + db_name() + '..StoredProcedure'' )'

EXEC (@tmp)

SP2 has the following code:

SELECT *
FROM SP1 (which won't work because SP1 is a stored procedure. A view, a table valued function, or a temporary table must be used for this)

Views - can't use a view because they don't allow dynamic sql and the db_name() in the OPENROWSET function must be used.
Temp Tables - can't use these because it would cause a large hit on system performance due to the frequency SP2 and others like it will be used.
Functions - My last resort is to use a table valued function as shown:

FUNCTION MyFunction
( )
RETURNS @retTable
(
@Field1 int,
@Field2 varchar(50)
)
AS
BEGIN
-- the problem here is that I need to call SP1 and assign it's resulting data into the
-- @retTable variable

-- this statement is incorrect, but it's meaning is my goal
INSERT @retTableSELECT *FROM SP1

RETURN
END

View 2 Replies View Related

Assign Function To Table Var??

Sep 19, 2007

I keep getting a declare error on this:
DECLARE @SplitTable TABLE(Idx BIGINT IDENTITY(1,1), SplitData VARCHAR(20), Size INT)
SET @SplitTable = dbo.SplitToTable('|', '1|One|0:26:20|541|abc|xxx|6:26:33|21705')

All I want to do is assign the table that my function returns to a variable. Should be easy, but I tried the above and assigning with 'as':

select * from dbo.SplitToTable('|','1|One|0:26:20|541|abc|xxx|6:26:33|21705') as Split

that didn't work either.

Also tried:
DECLARE @SplitTable TABLE(Idx BIGINT IDENTITY(1,1), SplitData VARCHAR(20), Size INT)
select @SplitTable= from dbo.SplitToTable('|','1|One|0:26:20|541|abc|xxx|6:26:33|21705')

what am I missing?

--PhB

View 3 Replies View Related

Assign Permissions For Individual Table.

Jan 15, 2008

A login named UserLogin1 has db_dataReader and db_dataWriter roles, and three schemas which are db_owner, dbUser1, and dbUser2 in a database named Database1. I wanted to have some tables for read-only, so I right clicked on the table and select properties. In the Permissions tab, I added UserLogin1 and checked Alter, Delete, Insert, Take Ownership, and Update under Deny. It worked for schema dbo only. I did the same steps for the tables that have schema dbUser1 or dbUser2, but UserLogin1 I added didn't stay. Why? How can I make those tables read-only?

Thanks.
DanYeung

View 3 Replies View Related

Assign Table To A Specific File Group

Aug 22, 2002

How can we use the SQL INSERT statement in the stored procedure to create a table in a user specified filegroup? Table1 will be created by using the following SQL script and will be placed in a default filegroup. The question is can this table been created in a specified filegroup other than default filegroup.

INSERT INTO Table1(col1,col2)
SELECT col1,col2
FROM Table2

Example, userA table assign to userA filegroup. userB table assign to userB filegroup. Usually by using CREATE statement you can put the table on a specific filegroup as long as a predefined filegroup has been hard coded in the script. I appreciate any inputs.

View 1 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

Integration Services :: Assign Variables To Multiple Table Results From Stored Procedure

Sep 21, 2015

If I have a stored procedure that returns 15 tables, how do I distinguish the tables to assign variables to each table in c#?

View 6 Replies View Related

Table LOGS

Apr 28, 2008

Hi,

Is there any System TABLE or VIEW or any other thing that logs a tables data in SQL2005? For example that logs the rows of a table, when they were created...

Thanks

View 3 Replies View Related

DB Engine :: Write Logs To Windows Event Logs?

Aug 6, 2015

OS: Windows 2012 Enterprise

SQL Server: 2012 Enterprise

I was wondering if there is any way all SQL Server error log entries could be automatically written to Windows Event Log. 

View 3 Replies View Related

SSIS Logs Vs Custom Logs Components

Feb 12, 2007

Hi,

In my case I have to log the errors raised by any task in a package to either windows event log, text file or SQL server. Also I need to send an email notifications to a group of people telling them about the error.

Now can I use SSIS package logging for logging the errors into the required destinations. I mean right clicking on the package and selecting Logging, then adding the required log providers and enabling the events for logging into those. I think I have to upfront select the log providers to log the error, I will not have the liberty to log the error to the destination, the name of which is passed as a variable to the package. This is okay with me though.

Now what will a custom log provider help me to do in this case. Also can I somehow configure my package to call the send mail task everytime an error is raised.

Also, one more option can be developing a package that only does the error handling. It will take in the paramters or the error codes and descriptions, the destination to write to and a flag to send mail or not for that particular type of error.

What do you think? Kindly advise.

Thanks in advance for your help and time.

Regards,

$wapnil

View 3 Replies View Related

SQL Server Logs (error Logs)

Aug 28, 2001

When looking at SQL Server error logs, I noticed that the current error
log had grown to 1MB whereas most of the files are only a few KB.

QUESTION 1: Can I set the max size for an error log file? If so, how?

QUESTION 2: If not, is there a workaround?

Thanks

View 1 Replies View Related

Again ! Serial Numbers

Apr 2, 2001

How to generate serial numbers ? I had already tired ident. i am getting the error. Can any people who is willing to write a syntax for me.

In sybase if we use Number * function. It will automatically generates the serial numbers from 1 to n. similarly i need the same function in SQL server 7.0 so that my problem will be solved...

I am converting sybase stored procedure into sql server stored procedure that is why i am asking about that. i am struggling hard to find an answer...

please help me in this...

urs
vj

View 1 Replies View Related

Executing Serial Dts

Feb 1, 2007

hi,

i am new here and interested in how to :

- execute serial dts in one single action or using vb script.

- change the status (enabled to disabled) and schedule using vb script.

Many dts make me tired when i have to change the status and schedule one by one.



regards.

View 1 Replies View Related

Getting Identity/Serial Of Row Just Inserted?

Jul 7, 2007

This isn't so much purely a SQL Server question as a question on ASP.NET VB technique. In particular, I have a situation where I am either inserting a NEW row for a "profile table" (name, email, etc.) or Updating an existing one. In both cases, I need to create a new row in a related table which has the identity/serial column of the parent table as the primary key for the data to be inserted into this subsidiary table (for which there may be many rows inserted, all tying back to the parent).
At the time I do the update, of course, I have the identity/serial of the "parent" so it's easy to update/insert. However, if the profile is NEW, I need to capture the identity/serial which was inserted so as to use it for the child table insert. (I remember a call to an obscure function which was -- essentially -- "give me the identity/serial of that which was just INSERTed" but I am unable to locate equivalent functionality. (I have searched various online help files for "Insert serial", "Insert identity" and the like with no results.
Hints? Mahalos in advance ... :)  KevInKauai

View 14 Replies View Related

Generating Serial Codes

Jul 29, 2001

Hi,

I have a table with a primary key, what I really need is something like an IDENTITY, but with the character 'X' and the last to digits of the year added on the front. Is there another way to update the field automatically like an IDENTITY would do, automatically incrementing as fields are inserted.

View 1 Replies View Related







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