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 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.
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.
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
I'm looking to identify the people who visited an office more than once and the dates that they visited. For example I have a table that looks like the following:
I have an issue while display the result in the required order. How to get the required output.
Code :
USE tempdb GO
IF OBJECT_ID('tempdb..#VersionFormat_tbl') IS NOT NULL DROP TABLE #VersionFormat_tbl CREATE TABLE #VersionFormat_tbl ( [FormatID] [smallint] NOT NULL, [Description] [varchar](50) NULL,
[Code] ....
Present output : fileExtension FormatID Description fileExtension versionFormatTypeId txt 1 Text txt 1 html 2 HTML html 1 xml 3 XML xml 1 pdf 4 PDF pdf 1 xls 5 Excel xls 1 doc 6 Word doc 1
I have process and people want to know percentile rank (on scale of 100), I'm bit lost as I have cases when I have let say 5 observations, so if use code below I can not got above number of observations:
Is there any way to get that numbers like RankWanted ??
; WITH cte AS ( SELECT 100 Cust_ID , 0.338129 Perc, 20 RankWanted union SELECT 300 Cust_ID , 0.487179 Perc, 40 RankWanted union SELECT 300 Cust_ID, 0.500000 Perc, 60 RankWanted union SELECT 400 Cust_ID, 0.541666 Perc, 80 RankWanted union SELECT 500 Cust_ID, 0.548780 Perc, 100 RankWanted ) SELECT Cust_ID, Perc,
I have a table with a column AttributeNumber and a column AttributeValue. The data is like this:
OrderNo. AttributeNumber AttributeValue 1.-Order_1 2001 A 2.-Order_1 2002 B 3.-Order_1 2003 C 4.-Order_2 2001 A 5.-Order_2 2002 B 6.-Order_2 2003 C
So the logic is as follows:
I need to display in my query the values are coming from Order_1, means AttributreValues coming from AttibuteNumbers: 2001,2002,2003...and Order_2 the same thing.
Not sure how to create my Select here since the values are in the same table
i m creating one google map application using asp.net with c# i had done also now that marker ll be shown from database (lat,long)depends on the lat,long i wanna display customer,sales,total sales for each makers in html table format.
I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.
I already tried to set the value as CDbl which returns error for the cells containing a string.
The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.
How to count the number of values that exist in a row based on the values from an array of numbers. Basically the the array of numbers I want to look for are in row 1 of table [test 1] and I want to search for them and count the "out of" in table [test 2]. Excuse me for not using the easiest way to convey my question below. I guess in short I have 10 numbers and like to find how many of those numbers exist in each row. short example:
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 ?
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[table_Data]') AND type in (N'U')) DROP TABLE [dbo].[table_Data] GO /****** Object: Table [dbo].[table_Data] Script Date: 04/21/2015 22:07:49 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[table_Data]') AND type in (N'U'))
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.
I have 2 identical tables one contains current settings, the other contains all historical settings.I could create a union view to display the current values from table A and all historical values from table B, butthat would also require a Variable to hold the tblid for both select statements.
Q. Can this be done with one joined or conditional select statement?
DECLARE @tblid int = 501 SELECT 1,2,3,4,'CurrentSetting' FROM TableA ta WHERE tblid = @tblid UNION SELECT 1,2,3,4,'PreviosSetting' FROM Tableb tb WHERE tblid = @tblid
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)
In a pretty standard select statement (as shown), i want to return 0 when "dbo.v_AgentOrderTotals.Total - dbo.v_AgentAmmountPaid.total - dbo.v_AgentCommClean.total AS amount_outstanding_commission" is less than 0.
SELECT dbo.t_Agents.agent_code, dbo.v_CurrentParamPaymentTotal.ammount AS weekley_payment_total, dbo.v_AgentNumberOfCustomers.count AS number_of_cust, dbo.v_AgentAmmountPaid.total AS total_paid, dbo.v_AgentOrderTotals.Total AS ytd_order_total, dbo.v_AgentOrderTotals.Total - dbo.v_AgentAmmountPaid.total AS amount_outstanding, ISNULL(dbo.v_AgentAmmountPaid.total / dbo.v_AgentOrderTotals.Total, 0) * 100 AS ytd_percentage, dbo.v_AgentOrderTotals.Total - dbo.v_AgentAmmountPaid.total - dbo.v_AgentCommClean.total AS amount_outstanding_commission, ISNULL(dbo.v_AgentOrderChange.amount, 0) AS net_weekly_order FROM dbo.t_Agents LEFT OUTER JOIN dbo.v_AgentOrderChange ON dbo.t_Agents.AGENT_ID = dbo.v_AgentOrderChange.AGENT_ID LEFT OUTER JOIN dbo.v_AgentCommClean ON dbo.t_Agents.AGENT_ID = dbo.v_AgentCommClean.AGENT_ID LEFT OUTER JOIN dbo.v_AgentNumberOfCustomers ON dbo.t_Agents.AGENT_ID = dbo.v_AgentNumberOfCustomers.AGENT_ID LEFT OUTER JOIN dbo.v_AgentOrderTotals ON dbo.t_Agents.AGENT_ID = dbo.v_AgentOrderTotals.AGENT_ID LEFT OUTER JOIN dbo.v_AgentAmmountPaid ON dbo.t_Agents.AGENT_ID = dbo.v_AgentAmmountPaid.AGENT_ID LEFT OUTER JOIN dbo.v_CurrentParamPaymentTotal ON dbo.t_Agents.AGENT_ID = dbo.v_CurrentParamPaymentTotal.AGENT_ID
I have a sales report that includes dollar amount, tonnage, and profit margin among other things. They are currently sorted by tonnage sold from highest to lowest. I'd like to be able to place a number in a column counting 1 up for tonnage ranking. I'd also like to get a number ranking for sales amount ranking along with profit margin ranking. The most tonnage sold might not have been the biggest sale nor had the highest profit margin.
Does this sound like something that can be done within SSRS?
I should ad I'm runing MDX queries against a cube so I can't use T-SQL for ranking.
Hi, Is it possible to search in columns with a number datatype (I'm using an MS SQL database with bigint columns) with the ICriterion Expression.Like? Normally the Expression.Like is used for varchar columns. However, if there's a bigint column with the value 167829 I want to search for example on %678%. Cheers, koekie