I have a problem. In my database I have the following numbers available:
101
104
105
110
111
112
113
114
What I need is to get a select query with records and sequentials numbers after it like:
101 0
104 1 (the number 105)
105 0
110 4 (the numbers 111,112,113,114)
111 3 (the numbers 112,113,114)
112 2 (the numbers 113,114)
113 1 (the numbers 114)
114 0
How can I do It?
I have a query which returns information about transactions similar to this:
Select account, trans_code, quantity
from ledger_table
This returns something like:
acct trans_code quantity
----- ---------- ------------
2 2 1000
2 3 500
2 3 300
3 2 100
3 2 500
etc.
What I need to do is add a lot number for each acct/trans code type. This is merely a sequential number for the transaction. This changes the output as follows:
acct trans_code quantity lot
----- ---------- ------------ ---
2 2 1000 1
2 3 500 1
2 3 300 2
3 2 100 1
3 2 500 2
3 3 1000 3
3 5 200 1
etc.
The lot number is reset for each grouping.
Does anyone have a way to do this outside of a cursor or temp table?
TIA, any help greatly appreciated.
I have a database that is pre-populated with sequential part numbers.As people reserve the parts I update a flag to show the # is no longeravailable. Now they want the ability to take out a block of "x"number of sequential part numbers - say for example 5.If my database had the following numbers available:101104105110111112113114It should return 110 thru 114 and then I would write an update queryto change the flags to 1 (checked out).I have only been able to return the first "x" number of records - havenot been able to make sure they are stepped sequentially - with thefollowing:SELECT ID_ITEM From PARTNO_CHKOUT_SPECIAL M Where (Select Count(*)FROM PARTNO_CHKOUT_SPECIAL NWHERE N.ID_ITEM <= M.ID_ITEM) >= 0 AND TYPE_REC=1 ANDFLAG_CHECKED_OUT=0 {maxrows 5}The above would return 101, 104, 105, 110, 111I tried using an (N.ID_ITEM+1)-M.ID_ITEM=0 to try stepping and geterrors, probably incorrect syntax. Can I do this in an SQL statement?
View 6 Replies View RelatedHi,
Can anyone tell me offhand the simplest/most elegant way of updating an integer column to a sequential column of numbers with a query?
e.g given
intval | Description| Cost
0 | Descvalue0| 4.32
2 | Descvalue2| 4.33
3 | Descvalue3| 4.34
8 | Descvalue8| 4.35
change it to:
intval | Description| Cost
0 | Descvalue0| 4.32
1 | Descvalue2| 4.33
2 | Descvalue3| 4.34
3 | Descvalue8| 4.35
I think it might need a stored proc..
Many Thanks
greg
How to identify different fields with in a group of records?
Example:
create table #test
(ID int, Text varchar(10))
insert into #test
select 1, 'ab'
union all
select 1, 'ab'
[Code] ...
I want to show additional field as Matched as ID 1 has same Text field on both the records, and for the ID 2 I want to show Unmatched as the Text fields are different but with the same ID.
I have a table with a column ID of ContentID. The ID in that column is all NULLs. I need a way to change those nulls to a number. It does not matter what type of number it is as long as they are different. Can someone point me somewhere with a piece of T-SQL that I could use to do that. There are over 24000 rows so cursor change will not be very efficient.
Thanks for any help
I am trying to automatically insert records into my existing customer table. Is there a way when I insert these new records and assign the customer number that it can sequentially pick the next available unique customer number for each record that is inserted? for example the first record would be customer number 100, the next 101, and so on? Please advise.
View 4 Replies View RelatedI have a table with 13,000 rows, in one column called Prioirty each row has a value of 1.
Is it possible to use SQL to replace all of these '1' values with a sequential list. Example 1,2,3,4,5,6,7....all the way to 13,000?
I am using sybase aSE12.5. I have a table with only one column withdatatype char(2). when i query this table to select all the records, ishould get these records in the ascending order and they should be numbered, i.e, the o/p should look something like thiscolumn_name------ --------1 AB2 AC3 ADand so on.I cannot add an extra column and i need this to be done in a single query.--Message posted via http://www.sqlmonster.com
View 2 Replies View RelatedI have a temp table that's populated with an insert query in as toredprocedure. The temp table has a uniqueID as the primary key.In that table I have a column SortOrder.What I want to do is to create a sequential number in SortOrder butonly for records matching a WHERE statement, for example:(pardon the shorthand...)Insert *.tblPermanent into tblTempIf myField = 1 thenSortOrder = 1(2,3,4,5,.....etc.)elseSortOrder = 0Thankslq
View 1 Replies View RelatedI have a column within a table which is already truncated/deleted all records within (Microsoft SQL 2008). I have to now populate the column with sequential numbers up to 50,000 records arbitrary numbers (doesn't mater) up to 7 characters.
what SQL statement I need to write that will automatically polulate the newly empty table with A000001,A0000002,A0000003, or any form for that matter etc so that I can sort number the records within the table.
I have approximately 50000 records which I need to sequentially entered and I really dont want to number the column manually via hand editing.
Here is my problem. I have a table with 4 columns id1,id2,id3,boxnum. Here is some sample data.
id1 id2 id3 boxnum
1 1 1
1 1 1
1 2 1
1 2 1
1 2 1
2 2 2
2 3 4
What I need is to be able to update boxnum with sequential numbers based on the unique set of values from the id columns. So my output would be this.
id1 id2 id3 boxnum
1 1 1 1
1 1 1 2
1 2 1 1
1 2 1 2
1 2 1 3
2 2 2 1
2 3 4 1
I have a product table and my task is to select only the products that had been paid for, but later their status has been changed.
I need to report the original paid date, the most recent status, and most recent updated date. Here is the sample table:
CREATE TABLE #Products (primKey int, productId int, productName varchar(100), productStatus varchar(50), logDate datetime )
Insert into #Products(primKey, productId, productName, productStatus, logDate)
Values
(1, 201, 'pen', 'received', '01/01/2011'),
(2, 201, 'pen', 'sold', '01/02/2011'),
(3, 201, 'pen', 'paid', '01/03/2011'),
(4, 201, 'pen', 'returned', '01/04/2011'),
(5, 201, 'pen', 'refurbished', '01/05/2011'),
(6, 202, 'pencil', 'received', '01/06/2011'),
(7, 202, 'pencil', 'sold', '01/07/2011'),
(8, 202, 'pencil', 'paid', '01/08/2011'),
(9, 201, 'pen', 'sold', '01/09/2011'),
(10, 201, 'pen', 'paid', '01/10/2011')
/* temp table records */
Select * From #Products order by productId
/* The desired outcomes would be showing only the Record 3 and 10.
This is a "fake" query to get the results:
*/
Select * From #Products Where primKey in (3, 10) order by productId
I have 2 tables in a 1: n relation. How can i get a select statement that the field in the n-relation with outputs, separated by a semicolon; Example: One person have many Job Titles
Table1 (tblPerson)
Table2 (tblTitles)
1, "John", "Miller", "Employee; Admin; Consultant"
2, "Joan", "Stevens", "Employee, Software Engineer, Consultant"
and so on .... 1 in select statement:
create a sequential aggregate of a column using sum in tsql.
I want a column that will sum up all hours work by transaction date.
for example
Monday my hours work will be 8 and therefore my total transaction hours will be 8.
Tuesday my hours work will be 8 but I want the total hours of my total transaction hours to 16.
Wed my total hours work will be 5 and I want the total hours of my total transaction hours to 21. etc.
Select myname, weekday, hours worked, Totalhours_ToDate =Sum(hours worked) from table
I have a SP that manipulates data for picking products and puts them into a temp table "#PickList" which is used for the basis of printing a picking note report.
I have also added code at the end of the SP to take the "#PickList" data and insert into a permanent table called "BWT_Lift_Transaction" and then use the bcp command to query it out to a text file. All this works fine, until the bcp command runs. Although the records are in the table, bcp returns nothing. Here is the code:
DECLARE @strLocation VARCHAR(50)
DECLARE @TransNum VARCHAR(50)
DECLARE @strFileLocation VARCHAR(1000)
DECLARE @strFileName VARCHAR(1000)
DECLARE @bcpCommand VARCHAR(8000)
[code].....
The earlier parts of the code create the filename for the text file and location to store it. If I insert a SELECT on the dbo.BWT_Lift_Transaction directly after the insert, I can see the data in there, but although the bcp command creates the file correctly, it returns no data:
output
NULL
Starting copy...
NULL
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1
NULL
If I remove the delete statement at the end and run the code twice, it will insert the data into the table twice. On the first run, nothing is returned by bcp. On the second run, the first set is returned by bcp, not both sets.
I don't understand why this is, but I guess it's something to do with transaction commitment and the way bcp works.
I'm working on a report where my table is as follows:
WITH SampleData (ID,NAME,[VALUE]) AS
(
SELECT 170983,'DateToday','6/04/2014'
UNION ALL SELECT 170983,'DateToday','6/04/2014'
UNION ALL SELECT 170983,'employee','1010'
UNION ALL SELECT 170983,'employee','1010'
[Code] .....
Here is my query against the table above:
SELECT
ID
,MAX(CASE WHEN NAME = 'employee' THEN VALUE END) AS PERSON
,MAX(CASE WHEN NAME = 'DateToday' THEN VALUE END) AS REQUEST_DATE
,MAX(CASE WHEN NAME = 'LeaveStartDate' THEN VALUE END) AS REQUEST_START_DATE
,MAX(CASE WHEN NAME = 'LeaveEndDate' THEN VALUE END) AS REQUEST_END_DATE
,MAX(CASE WHEN NAME = 'HoursPerDay' THEN VALUE END) AS REQUESTED_HOURS
,MAX(CASE WHEN NAME = 'LeaveType' THEN VALUE END) AS REQUEST_TYPE
FROM SampleData
Here is the result from the above query, I'm not sure how to get the desired results (listed at the end):
IDPERSONREQUEST_DATEREQUEST_START_DATE REQUEST_END_DATE REQUESTED_HOURS REQUEST_TYPE
170983NULL6/04/2014NULL NULL NULL NULL
1709831010NULL NULL NULL NULL NULL
170983NULLNULL NULL NULL 8:00 NULL
170983NULLNULL NULL 6/16/2014 NULL NULL
[Code] .....
My Desired results are as follows:
IDPERSONREQUEST_DATEREQUEST_START_DATE REQUEST_END_DATE REQUESTED_HOURS REQUEST_TYPE
17098310106/04/20146/16/2014 6/16/2014 8:00 Personal
17102416/04/20146/17/2014 6/17/2014 8:00 Bereavement
I need to group records and assign a setid to the group. I have atable with data that looks like thisColA ColB94015 0106594016 0106594015 0108594015 0108633383 0091232601 00912I need to create a resultset using just sql to look like thisColA ColB GRP94015 01065 194016 01065 194015 01085 194015 01086 133383 00912 232601 00912 2The tricky part is resolving the many to many issue. A value in ColAcan belong to multiple values in ColB and a value in ColB can havemultiple values in ColA.
View 6 Replies View Relatedi have Two tables... with both the table having LastUpdated Column. And, in my Select Query i m using both the table with inner join.And i want to show the LastUpdated column which has the maxiumum date value. i.e. ( latest Updated Column value).
View 5 Replies View Relatedrewrite the below two queries (so that i can avoid duplicates) i need to send email to everyone not the dup[right][/right]licated ones)?
Create table #MyPhoneList
(
AccountID int,
EmailWork varchar(50),
EmailHome varchar(50),
EmailOther varchar(50),
[Code] ....
--> In this table AccountID is uniquee
--> email values could be null or repetetive for work / home / Other (same email can be used more than one columns for accountid)
-- a new column will be created with name as Sourceflag( the value could be work, Home, Other depend on email coming from) then removes duplicates
SELECT AccountID , Email, SourceFlag, ROW_NUMBER() OVER(PARTITION BY AccountID, Email ORDER BY Sourceflag desc) AS ROW
INTO #List
from (
SELECTAccountID
, EmailWorkAS EMAIL
, 'Work'AS SourceFlag
FROM#MyPhoneList (NoLock) eml
WHEREIsOffersToWorkEmail= 1
[code]....
Hello--I need to create a SQl statement (SQL SERVER 2000) which will do the following:
read table1 sequentially
select every "nth" record
write each selected record to and output table
Your help is greatly appreciated.
thanks,
MB
Why does M$ Query Analyzer display all numbers as positive, no matterwhether they are truly positive or negative ?I am having to cast each column to varchar to find out if there areany negative numbers being hidden from me :(I tried checking Tools/Options/Connections/Use Regional Settings bothon and off, stopping and restarting M$ Query Analyer in betwixt, butno improvement.Am I missing some other option somewhere ?
View 7 Replies View RelatedI would like to have records in my Absences table split up into multiple records in my query based on a start and end date.
A random record in my Absences table shows (as an example):
resource: 1
startdate: 2014-08-20 09:00:00.000
enddate: 2014-08-23 13:00:00.000
hours: 28 (= 8 + 8 + 8 + 4)
I would like to have 4 lines in my query:
resource date hours
1 2014-08-20 8
1 2014-08-21 8
1 2014-08-22 8
1 2014-08-23 4
Generating the 4 lines is not the issue; I call 3 functions to do that together with cross apply.One function to get all dates between the start and end date (dbo.AllDays returning a table with only a datevalue column); one function to have these dates evaluated against a work schedule (dbo.HRCapacityHours) and one function to get the absence records (dbo.HRAbsenceHours) What I can't get fixed is having the correct hours per line.
What I now get is:
resource date hours
...
1 2014-08-19 NULL
1 2014-08-20 28
1 2014-08-21 28
1 2014-08-22 28
1 2014-08-23 28
1 2014-08-24 NULL
...
... instead of the correct hours per date (8, 8, 8, 4).
A very simplified extract of my code is:
DECLARE @startdate DATETIME
DECLARE @enddate DATETIME
SET @startdate = '2014-01-01'
SET @enddate = '2014-08-31'
SELECTh.res_id AS Resource,
t.datevalue,
(SELECT ROUND([dbo].[HRCapacityHours] (h.res_id, t.datevalue, t.datevalue), 2)) AS Capacity,
(SELECT [dbo].[HRAbsenceHours] (9538, h.res_id, t.datevalue, t.datevalue + 1) AS AbsenceHours
FROMResources h (NOLOCK)
CROSS APPLY (SELECT * FROM [dbo].[AllDays] (@startdate, @enddate)) t
p.s.The 9538 value in the HRAbsenceHours function refers to the absences-workflowID.I can't get this solved.
SELECT DISTINCT S.EnrollNo
,S.Name
,ET.Descriptions AS EventName
,SA.Name AS AttendStudent
,'' AS AttendFaculty
FROM StudentEvent SE
INNER JOIN SStudent S ON SE.PresentatorID = S.StudentID
[code]....
Expected OutPut
EnrollNo NameEventName AttendStudentAttendFaculty
PH1201ASHKARALIPresentationKUMARA Sawadkar
PH1201ASHKARALIPresentationNILESHRAJ. GNaveen
PH1201ASHKARALIPresentationSEENIVASAN Nitya
PH1201ASHKARALIPresentation-----------Ramesh
PH1201ASHKARALIPresentation-----------Sakthi
I have the got below T SQL query to fetch the matching record
DECLARE @MappingTable TABLE
(
Productname nvarchar(10),
identification_key1 int,
identification_key2 int,
identification_key3 int
[Code] .....
-- result - 'Orange'
This is an exact matching record and straight forward
Is it possible to identify the record using T SQL query based on the following scenarios
1) return the record - If all the three where conditions match
2) if record not found check and return the record where 2 columns values in the where condition match
-- Expected Result for below query: 'Orange', because 2 of the columns in where condition have matching values
SELECT Productname
FROM @MappingTable where identification_key1=1 or identification_key2 =2 or identification_key3 =1
-- result - 'Orange'
How to include row values as columns in my select query. I have a table that stores comments for different sections in a web application. In the table below, I would like display each comment as a new column. I only want one row for each record_ID.
Existing table layout
table name - tblcomments
Record_ID Comment_Section_ID Comment
1 5 Test 5 comment
1 7 Test 7 comment
2 5 New comment
2 7 Old comment
3 5 Stop
3 7 Go
Desired table layout
table name - #tempComment
Record_ID Comment_Section_5 Comment_Section_7
1 Test 5 comment Test 7 comment
2 New comment old comment
3 Stop Go
Once I figure out how to get the data in the layout above, I will need to join the table with my record table.
table name - tblRecord
Record_ID Record_Type_ID Record_Status
1 23 Closed
2 56 Open
3 67 Open
4 09 Closed
5 43 In progress
I would like to be able to join the tables in the query below for the final output.
Select r.Record_ID, r.Record_Type_ID, r.Record_Status,
c.Comment_Section_5, c.Comment_Section_7
from tblRecord r
left outer join #tempComment c
on r.record_ID = c.record_ID
How I can get the data in the desired #tempComment table layout mentioned above?
I am having query where i am calulating datediff value, but i want to display resultser as below:
if datediff = 4 then resultset would be
0
1
2
3
4
if datediff = 9 then
0
1
2
3
4
5
6
7
8
9
how can i implement this?
I have three tables EmpIDs,EmpRoles and LatestRoles. I need to write a select Query to get roles of all employees present in EmpIDs table by referring EmpRoles and LatestRoles.
Where I stuck : The condition is first look into table EmpRoles and if it has more than one entry for a particular Employee ID than only need to get the Role from LatestRoles other wise consider the role from EmpRoles .
Example:
Create Table #EmpIDs (
EmplID int
)
Create Table #EmpRoles (
EMPID int,
[Code] ....
Employee ID 2 is having two roles defined in EmpRoles so for EmpID 2 need to fetch Role from LatestRoles table and for remaining ID's need to fetch from EmpRoles .
My Final Output of select query should be like below.
EmpID Role
1 Role1
2 Role2
3 Role1
I have an Orders table which has the following fields:
OrderID (PK, int, auto increment, not null)
CustomerID (FK, int, null)
PaymentDate (datetime, null)
UserID (uniqueidentifier)
(and other irrelevant fields)
Basically, for a specific PaymentDate range (29th July 2014 - 26th August 2014, inclusive) I want to select all orders where they only appear once in the orders table based on the CustomerID, so I only want to know about them if they have a paid order (decided by PaymentDate not being null) in that date range, but also taking into account if they have ever had a paid order outside of that date range. I'll also be joining on to the aspnet_Users table to get the username assigned to that order.
I am trying to find a way to lock rows of data used in a SELECT query from being read by another SELECT query.
I could do a "begin tran - select - update - rollback" sequence but was wondering if there is a cleaner way to do this??
Tried UPDLOCK, ROWLOCK, TABLOCK, HOLDLOCK in multiple variations but none seem to block the select.
Large design flaw, I know, but we have a table with a varchar field which contains alpha - numeric values. I need to change just the numerics, which can be in an position on the field:
Sample data of the field:
Rec1: Sally Morgan 201-555-1212
Rec2: 555-4040 John Smith
Rec3: Jane Houstin 201-555-6452 ext1223
Desired result:
Rec1: Sally Morgan 999-999-9999
Rec2: 999-9999 John Smith
Rec3: Jane Houstin 999-999-9999 ext9999
There's a bunch of UDFs out there for selecting just the numerics, but I'm having trouble throwing it into a viable script for repeatable execution.
I want to show cumulative numbers, but don't know how to calculate them.
Here is an example of the source and the wanted result:
Source:
[Week] [Count]
1 15
2 5
3 6
4 10
(until 52)
Result:
[Week] [Count]
1 15
2 20
3 26
4 36
Is this possible, and how?
TestData:
USE TestDb /*SqlServer 2005*/
CREATE TABLE Test(
[Week] [int] NOT NULL,
[Count] [int] NOT NULL
)
GO
INSERT INTO Test ([Week], [Count]) VALUES (1, 15)
INSERT INTO Test ([Week], [Count]) VALUES (2, 5)
INSERT INTO Test ([Week], [Count]) VALUES (3, 6)
INSERT INTO Test ([Week], [Count]) VALUES (4, 10)
I want to get "all combinations of the same count" for provided list of numbers.As an example, if I have a string 'NA,T1,T1a'
For this string, I want to generate following:
NA,T1,T1a
NA,T1a,T1
T1,NA,T1a
T1,T1a,NA
T1a,NA,T1
T1a,T1,NA
query to achieve the same or if the solution is already available, provide me with the links.