T-SQL (SS2K8) :: Function To List Distinct Pax Name And Ticket Numbers?
Mar 14, 2014
I have data in a table Item_TB that I need to extract in a way that pulls out the distinct pax name and all the ticket numbers associated with the passenger per booking reference.
The data is:
Branch Folder ID Pax TktNo BookingRef
HQ 123 1 Jim 4444 ABCDE
HQ 123 2 Bob 5555 ABCDE
HQ 123 3 Jim 6666 ABCDE
HQ 123 4 Bob 7777 ABCDE
HQ 124 1 Jenny 8888 FGHIJ
HQ 124 2 Jenny 9999 FGHIJ
HQ 124 3 Jenny 3333 FGHIJ
I somehow need to get a function to pull the data out for each booking ref like so
--BookingRef ABCDE
Jim 4444/
6666
Bob 5555
7777
--BookingRef FGHIJ
Jenny 8888/
9999/
3333
I know I can get a simple function to return the all data, but I do not know how to only include the pax name once.
View 4 Replies
ADVERTISEMENT
Sep 10, 2015
Have a need to let the user select the year and month to use for filtering results from an MDX query that populates a report.I think all I should need is a list of years and a Month name / number.What would the filter look like if say I had a Date dimension that included the year number in a 4-digit and month in a 2-digit number? In TSQL it would be something like ... where Month = @month AND Year = @year...How do I get a distinct list of member values for the month numbers from the Date dimension?
View 6 Replies
View Related
Feb 21, 2007
I have an 'ID' column. I'm up to about ID number 40000, but not all are in use, so ID 4354 might not be in any row. I want a list of all numbers which aren't in use. I want to write something like this:
select [numbers from 0 to 40000] where <number> not in (select distinct id from mytable)
but don't know how. Any clues?
View 1 Replies
View Related
Jun 17, 2015
I have data in the below format
Service Numbers start datetime enddate time part number Calc(in hrs (endate - startdate)
223344 2014-05-05 2014-05-07 12345 48
223344 2014-05-05 2014-05-07 56789 48
223355 2014-05-06 2014-05-07 17865 24
223355 2014-05-06 2014-05-07 76543 24
345627 2014-05-09 2014-05-013 76543 72
I want measure like sum of hours of distinct service numbers.I want to use it in a reporting tool.
it means my sum will be 144 and my Average of per time closed will be 144/3 = 48.
How to create a measure like that ?
View 4 Replies
View Related
Apr 28, 2008
I am trying to Strip off the Numbers witha Delimited List and just retain the Name of the Persons. but unable to do it. is there any function or code to do that in SQL
932908` James Fleser,935992` Prakash Sinha
Stripping off Numbers for the Above and Just retain the Names..
thanks
View 4 Replies
View Related
Mar 20, 2014
A table I'm working with has a varchar column containing a comma-delimited string of numbers, which match up to smallint codes in another table. I gather this is someone's implementation of a many-to-many relationship. :)
Using SQL Server 2008 and wondering if there's a special trick to using a string list of numbers with IN() URL...converting the string into a temp table[/url] but I'd just like to make sure there isn't a quicker, easier approach, or if that's it.
View 3 Replies
View Related
Jul 20, 2005
Hi. I am searching for a reference with all error numbers an descriptionsfor ms sql odbc.Has anybody something for me?Thanks a lot.Greeting Jan Entzminger
View 1 Replies
View Related
Aug 8, 2014
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?
View 2 Replies
View Related
May 13, 2014
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.
View 8 Replies
View Related
Feb 6, 2015
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)
View 8 Replies
View Related
Jun 19, 2015
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.
View 7 Replies
View Related
Feb 28, 2014
// C - Categories, P - Parameters, CP_Link - linkage table between C and P
// Basically, it is a list of features belonging to a particular category.
DROP TABLE [C];
CREATE TABLE [C] (
ID_CAutoinc,
Name varchar(32),
PRIMARY KEY( ID_C )
[code]....
OK, this works fine, but I want to get a list of parameters (from P table) which holds only those items that linked to all categories requested. In other words, I want only 'leg' and 'hand', but not the 'beard', because 'girls' not linked to 'beard'.Only common features for all of categories listed "IN (,,,)" wanted.
View 3 Replies
View Related
Apr 5, 2014
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?
View 2 Replies
View Related
Feb 5, 2015
Say you have a table that has records with numbers sort of like lottery winning numbers, say:
TableWinners
num1, num2, num3, num4, num5, num6
33 52 47 23 17 28
... more records with similar structure.
Then you have another table with chosen numbers, same structure as above, TableGuesses.
How could you do the following comparisons between TableGuesses and TableWinners:
1. Compare a single record in TableGuesses to a single record in TableWinners to get a count of the number of numbers that match (kind of a typical lottery type of thing).
2. Compare a single record in TableGuessess to ALL records in TableWinners to see which record in TableWinners is the closest match to the selected record in TableGuesses.
View 8 Replies
View Related
Aug 1, 2007
Hi..
I m working on MS SQL Server 2000.
I am trying to pass a list of numbers to a stored procedure to be used with 'IN()' statement.
I was doing something like..
Create Procedure proc
(
@Items varchar(100) --- List of numbers
)
AS Begin
Declare @SQL varchar(8000)
Set @SQL =
'
Select Query......
Where products IN (' + @items + ') '
'
Exec (@SQL)
This stored procedure is working fine, but when i m adding more required stuff to that, the size exceeds 8000, & it gives the error "Invalid operator for data type. Operator equals add, type equals text."
Can any1 please help me out with this ASAP??
View 4 Replies
View Related
Aug 11, 2014
i've been looking at moving one of our processed from excel (+vba) into t-sql to make life easier but am stuck.
We have lots of tasks that are assigned to work groups which we want to distribute evenly across the work groups. This is a simple task for ntile.. However when these tasks are no longer required they are removed which leaves the groups uneven. When new tasks are added we want to still try to keep these groups balanced.
EG Existing groups :
GroupName - Task Count
Group1 - 1000
Group2 - 999
Group3 - 998
If we were to add 6 new tasks they would have more assigned to Group 2 & 3 as they have less than group 1.
Task 1 - Group3
Task 2 - Group3
Task 3 -Group2
Task 4 - Group1
Task 5 - Group2
Task 6 - Group3
Sample tables
Create table GroupTable
(GroupID int, Name varchar(200) )
Insert into GroupTable values (1,'Group1')
Insert into GroupTable values (2,'Group2')
Insert into GroupTable values (3,'Group3')
Create table Jobs(jobid int identity(1,1), name varchar(100),Groupid int)
--Existing tasks
Insert into Jobs(name,Groupid) values ('Task1',1)
Insert into Jobs(name,Groupid) values ('Task2',1)
Insert into Jobs(name,Groupid) values ('Task3',1)
Insert into Jobs(name,Groupid) values ('Task4',1)
Insert into Jobs(name,Groupid) values ('Task5',2)
Insert into Jobs(name,Groupid) values ('Task6',2)
Insert into Jobs(name,Groupid) values ('Task6',2)
Insert into Jobs(name,Groupid) values ('Task7',3)
-- New tasks
Insert into Jobs(name) values ('TaskA')
Insert into Jobs(name) values ('TaskB')
Insert into Jobs(name) values ('TaskC')
Insert into Jobs(name) values ('TaskD')
Insert into Jobs(name) values ('TaskE')
Insert into Jobs(name) values ('TaskF')
This gives us 6 unassigned tasks and a uneven group assignment
GROUPNAME TASK_COUNT
<none> 6
Group1 4
Group2 3
Group3 2
This means the new tasks will be assigned like this
TaskA - Group3
TaskB - Group3
TaskC - Group2
TaskD - Group1
TaskE - Group2
TaskF - Group3
View 5 Replies
View Related
Apr 14, 2014
Suppose I have 2 table
1)Main
2)History
Main table maintain all the records having columns MAIN_SKU,DEDUCTIBLE_AMT,model_id,catagory,ModifiedDate
IF DEDUCTIBLE_AMT is changes it will place entry in history table ,columns are same with history_id
i want to display distinct main_sku from history table(all columns) with last DEDUCTIBLE_AMT changed from history table
table structure
main table
MAIN_SKUDEDUCTIBLE_amtmodel_idcatagory
1100100phone
2150101phone
3200109smartphone
4100202smartphone
History table
History_idMAIN_SKUDEDUCTIBLE_amtmodel_idcatagoryModifiedDate
11150100phone4/14/2014
21200101phone4/13/2014
34109202smartphone4/14/2014
44101202smartphone4/13/2014
52200101phone4/13/2014
63100109smartphone4/12/2014
View 3 Replies
View Related
Apr 30, 2014
T1
-------
a1
a2
datetime
a4
a5
.....
i need distinct max between a1&a2 which i can get no problem but i cant get that unique datetime that correspond to a1&a2 in 1 query because this is will a subquery in a big query. Creating another temp table etc is not an option for me. for every specific a1 there is many entries of a2 + timedate etc.
create table abc_test (
id int
,runs int
,date1 datetime
[code]....
I can either get distinct ID + latest date or ID + largest #ofRuns, both will do but also need the third column.
View 5 Replies
View Related
May 21, 2015
The below TSQL query takes about 19 minutes to run
SELECT count(distinct SessionID) ) FROM SessionTracker
Sessionid is a varchar(138)
Through SQL profiler - I notice implicit conversion taking place when running distinct option.
Select count(sessionid) from sessiontracker
- runs in milliseconds.
View 1 Replies
View Related
Jun 4, 2014
I have 3 tables:
News:
bigint NewId
nvarchar NewTitle
datetime NewDate
nvarchar NewBrief
--------------------------
Category:
int CatId
nvarchar CatName
--------------------------
NewsRelCategory:
bigint Id
int CategoryIdFk
bigint NewsIdFk
--------------------------
I want to select NewId, NewDate and Distinct NewTitle
I tried this but NewTitle doesn't distinct:
SELECT
FROM dbo.Category INNER JOIN
NewsRelCategory ON dbo.Category.CatId = NewsRelCategory.NrcCategoryIdFk INNER JOIN
dbo.News ON NewsRelCategory.NrcNewsIdFk = dbo.News.NewId
View 9 Replies
View Related
Jun 2, 2008
Greetings,
Is there anything new on returning max of two values in SQL 05? There seems to be nothing I have searched everywhere. Only option might be to create my own UDF or do a CASE statement, but I could not believe my eyes there is no such thing?
Thanks
View 1 Replies
View Related
Dec 23, 2014
Trying to build a list of order numbers based on stock availability.
The data looks something like this:
OrderNumber Stockcode quantityordered quantityinstock
123 code1 10 5
123 code2 5 10
124 code3 15 20
124 code4 10 10
In this case I would like to output a single result for each order, but based on stock availability order 123 is not a complete order and 124 is so the results will need to reflect this.
View 1 Replies
View Related
Oct 14, 2015
I am trying to create a comma delimited list of InvNo along with the JobNo .
CREATE TABLE #ListString
(
JobNo VARCHAR(10),
InvNo VARCHAR(MAX)
)
INSERT INTO #ListString ( JobNo, InvNo )
SELECT '3079', 'abc'
[Code] ....
View 6 Replies
View Related
Apr 18, 2014
--------------------------------------------------
SalesOrder-ItemName-Price-Category
-------------------------------------------------
01-Camera-100-Electronic
01-Memory-4GB-10-Memory
01-Battery-5-Battery
02-Keyboad-10-Accessories
02-Mouse-5-Accessories
03-CPU-300-Hardware
03-Motherboad-400-Hardware
From above rows i would like to select rows based on one distinct column SalesOrder, i want output like below.
-----------------------------------
SalesOrder-ItemName-Price-Category
-----------------------------------
01-Camera-100-Electronic
02-Keyboad-10-Accessories
03-CPU-300-Hardware
CREATE TABLE Table1 (SalesOrder varchar(10), ItemName VARCHAR(100), Price INT, Category VARCHAR(100))
[Code] ......
View 2 Replies
View Related
Mar 29, 2008
Im reading some guys code and I see the following code
"select distinct(State) from listOFschools;select * from listOFschools"
He is trying to query out states and the schools that are within the state. What exactly is the distinct() function do. and if anyone can tell me, what exactly is he asking to do in this statement
View 6 Replies
View Related
Oct 11, 2005
I have a query that returns employee names and reservations they updated for a set date. Here's a simplified version:
Select Employee.Name, Reservation.ResID
From Employee INNER JOIN Reservation
ON Employee.EmpID = Reservation.EmpID
Where Reservation.DateModified BETWEEN '200510010000' AND '200510050000'
The problem is each employee can update the same reservation several times but I only want it to display that record once. The distinct statement doesn't work because I want the EmpID to be listed more than once. Thanks.
View 6 Replies
View Related
Dec 14, 2007
Hi,
I have a query with DISTINCT option. If i use DISTINCT option with aggregate function like SUM will it eliminate duplicate values and then sum the values of a column and what is the syntax for it.
EX I have a column for budget
BUDGET
100
350
275
350
100
so if i use DISTINCT SUM(BUDGET) will it eliminate duplicate values and sum the total.
View 3 Replies
View Related
Feb 27, 2008
I need to return a single, unique record for each director. In my example here, I'm getting back 5 records for Bossidy because he has 5 records in the Directorships table each with a unique CompID.
SELECT TDirectors.IDDir, TDirectors.DirFName, TDirectors.DirLName, TDirectors.DirLName + ', ' + TDirectors.DirFName AS DirFullName, TDirectors.ExecutiveTitle, TDirectors.DirGender, TDirectors.DirAge, TDirRace.DirRace,
TDirectors.PrincipalCompany AS CompanyName, TDirectorships.CompID
FROM TDirRace RIGHT OUTER JOIN
TDirectors ON TDirRace.IDDir = TDirectors.IDDir RIGHT OUTER JOIN
TDirectorships ON TDirectors.IDDir = TDirectorships.IDDir
WHERE (TDirectors.DirLName='Bossidy')
I thought I could do this but it doesn't work.
SELECT DISTINCT(TDirectors.IDDir)...
Thanks
View 5 Replies
View Related
Sep 12, 2015
@BoxesNeeded will change every time it runs. This method works.
declare @i as int,
@BoxesNeeded as int
select @i = 0,
@BoxesNeeded = 15
--drop table #temp
select @i as DDL
into #temp where 1=2
while @BoxesNeeded > @i begin
set @i = @i + 1
insert into #temp
select @i
end
select DDL
from #temp
View 3 Replies
View Related
Jan 12, 2015
I need to know how many widgets are located at each factory.
I have a table called "Widgets". The pertinent column(s) are:
Factory UID
By using only this table I can group the results by the FactoryUID to get the answer. However, this table does not tell me the factory name.
I have a table called "Factories". The pertinent column(s) are:
FactoryUID
FactoryName
I can join these two tables by the FactoryUID. But I don't know how to write this query so that my results will look like the following table:
FactoryName Widgets
Factory1 100
Factory2 200
Factory3 300
View 6 Replies
View Related
Mar 22, 2014
I get the following error , when I execute my CTE on SQL Server 2008 R2.CTE1 has more columns than were specified in the column list
--SQL CODE IS AS BELOW
WITH CTE1 (AUCTIONID,[Open Time], [Response SLA Broken], [Response SLA Deadline], [Response SLA Actual], [Responsetime SLA Broken Before Transfer], [Resolution SLA Broken], [Resolution SLADeadline], [Resolutiontime SLA Broken
[code]...
View 2 Replies
View Related
May 27, 2015
My requirement is to count the customer order number for premium order type orders which has some order quantity.I am using below MDX
count
(
Filter
(([Customer Order].[Dim Customer Orderkey].[Dim Customer Orderkey].members,
[Outbound Order Attributes].[Order Type].&[P]),[Measures].[Ordered, pcs]>0 ) ,
)
The result is accurate but the query execution time is 3-4 minutes for 10 fact records, when i use multiple dimension. it is showing me 0 valus for this measure for all the members for the dimesion attribute which doen't have any customer order. example it shows all the member of date dimension. is there any way to reduce the rows. i think this is the reason to take more execution time.when i use EXCCLUDEEMPTY the result is NULL
count
(
Filter
(([Customer Order].[Dim Customer Orderkey].[Dim Customer Orderkey].members,
[Outbound Order Attributes].[Order Type].&[C]),[Measures].[Shipped, pcs]>0 ) ,
EXCLUDEEMPTY)
View 3 Replies
View Related
Jul 14, 2014
I'm trying to pull up a report of restored databases from our bug tracking software to audit and see if any of them can be taken down, and having some trouble figuring out how to pull the list of databases out of the entire request text, since they usually come in via email. Some sample data is below with the paltry beginning of a solution that I came up with.
WITH bug (BugID, BugComment) AS (
SELECT 1, 'Hello DB_001000, DB_001000, DB_001000 Blick'
UNION ALL
SELECT 2, 'Hi DB_001000 DB_001000 DB_001000 DB_001000 Flick'
UNION ALL
SELECT 3, 'Urgent DB_001000 DB_001000 Glick'
[Code] ....
View 6 Replies
View Related
