T-SQL (SS2K8) :: Creating A Custom Column?
May 14, 2014
creating a custom column that will put a list of 5 values for each unique value(or in this case Employee).
So I have a Employee table were I pull a list of all active employees -
Example -
024Swanson, Ronrswanson@tv.com
026Donaughy, Jackjdonaughy@tv.com
028Scott, Michaelsmichael@tv.com
What I want to do is add a column that has 5 values and create a row for each value
I want it too look like this -
024Swanson, Ronrswanson@tv.com a
024Swanson, Ronrswanson@tv.com b
024Swanson, Ronrswanson@tv.com c
024Swanson, Ronrswanson@tv.com d
024Swanson, Ronrswanson@tv.com e
[Code] ....
Currently all my query looks like is this -
SELECT EmpID, LastFirst, Email
FROM dbo.EmpList
WHERE (Active = 1)
View 4 Replies
ADVERTISEMENT
Apr 9, 2015
I have setup a custom Thesaurus for use with SQL Full Text Index (2008). For example I have the following terms :
<expansion>
<sub>400v</sub>
<sub>400volt</sub>
<sub>400 volt</sub>
<sub>415v</sub>
<sub>415volt</sub>
<sub>415 volt</sub>
<sub>3 phase</sub>
<sub>3phase</sub>
<sub>three phase</sub>
</expansion>
When I issue a search for "415 volt" I am getting matches on records with "3" in the data, not just "3 phase" - example query below :
select tblp.productid, tblp.productcode, tblp.description, KEY_TBL1.RANK
from tblProduct tblp
INNER JOIN FREETEXTTABLE(tblProduct, description, '"415 volt"') AS KEY_TBL1 ON tblP.ProductID = KEY_TBL1.[KEY]
ORDER BY KEY_TBL1.RANK DESC
It is returning items that don't have the word "phase" in at all and just have "3"! I have had to take all numbers out of the stop list as we sell a lot of technical items that will be searched for by size / voltage etc.
View 0 Replies
View Related
Aug 10, 2015
I am trying to SELECT data based on custom groups of that data. For example and in its simplest form:
SELECT COUNT(*)
FROMdbo.People
WHERE Current_Status = ‘A’
GROUP BY People_Code
The People_Code is the difficult part. The code represents the building that they work in. However, some buildings have multiple People_Codes. Kind of like multiple departments within a building.
For example:
Building NamePeople_CodeEmployee Count
Building A617535
Building B985665
Building C529212
Building C529932
Building C419816
Building D326974
Building D781024
Building E25365
Each building has a main People_code which, for this example, could be any one of the codes for the building. For example: Main code for building C can be 5292 and for building D it can be 7810.
Applying a variation (which is what I cannot figure out) of the SELECT statement above to this table, the result set for Building C must be the combined employee count of all three People_codes and must be represented by the main code of 5292 as a single row. Building D would have a row using code 7810 but will combine the employee count of codes 7810 and 3269.
I built a conversion table that would match up the main code with all of its related codes but just couldn’t seem to make it dance the way I want it to.
People_CodeNameGroupNameGroupPeopleCode
6175Building ABuilding A6175
9856Building BBuilding B9856
5292Building CBuildingCGroup5292
5299Building C AnnexBuildingCGroup5292
4198Building C Floor6BuildingCGroup5292
Etc…
The whole query is much more involved than just the simple SELECT statement used here, but if I can get this to work, I’m sure I can apply it to the full query.
View 5 Replies
View Related
Mar 11, 2014
I have the following:
create table testdata
(
StepName varchar(25)
,StepStatus char(25)
,EntryDate datetime)
insert testdata values ('Step1','Starting',getdate())
insert testdata values ('Step2','Starting',getdate())
insert testdata values ('Step3','Starting',getdate())
insert testdata values ('Step4','Starting',getdate())
insert testdata values ('Step1','Finished',getdate())
insert testdata values ('Step2','Finished',getdate())
insert testdata values ('Step3','Finished',getdate())
I need a query that will basically return Step 4 hasn't finished.
I have tried a few ways but can't get it to work...
View 5 Replies
View Related
Mar 27, 2008
Hello everyone!
Even though I am not "new to SQL Server", my experience in working with it has ebbed and flowed, and working with SQL Server is not something that happens for me even on a monthly basis.
I work as an in-house programmer for a financial investment company and I have been asked to work on a database that is to hold financial data.
One of the requirements for the tables has been that the Primary Key should be [name_of_the_table] + [Identity].
So, for example, for table "tblEquities", the values of the Primary Key would be:
PK
-------
tblEquities1
tblEquities2
tblEquities3
tblEquities4
tblEquities5
My questions are:
1) Is it possible to achieve this by using just one column, as in defining a formula for the "Computed Column Specification" , something that would look like "'tblEquities' + Identity()"?
2) If the above is not possible, I assume it's much easier to just define a column as being Identity column and then create a 2nd computed column that would aggregate the name of the table with the value in the Identity column, so that I end up with a column holding the desired values? (I guess it would look something like
"'tblEquities' + [Identity]")
Also, I have been asked to find out what SQL 2005 have/offers, in term of guaranteeing referential integrity. Are the "Database Diagram", as well as the "Relationships" dialog box, the tools that would meet that criteria or does SQL 2005 contain other tools that would help in this regard?
Thank you!
View 6 Replies
View Related
Oct 4, 2006
I have a package that is going to have roughly 20 sequence containers in it. Each of these containers is going to have the same start task and the same end task. The data flow task(s) in each sequence container will differ. So I was thinking I would create a custom sequence container that would implement the common steps I need in each of my containers. I started to create a task that would inherit from Sequence, but I found that it is sealed. Bummer.
What is involved in creating a sequence task by inheriting from Task? I see that Sequence implements IDtsSequence. But looking at this interface, it seems like I would have to build the logic to execute the workflow contained in my sequence.
Any pointers?
-Darrell
View 6 Replies
View Related
Nov 13, 2004
Hi folks, i need ur guidance.
I have a few DDL and DML scripts which i want to be automatically applied during MSDE setup; or an .MDF file be attached automatically. Please suggest an easier way!
Howdy!
View 1 Replies
View Related
Mar 11, 2008
I currently have a website which is using ASP.NET 2.0, C#, and SQLServer 2005. The website will be used to enter grants for auniversity. When a new grant is entered, I need to generate a primarykey. The primary key will need to follow the format: Two digit forfiscal year, then number of the grant for that year. Example:Year 08 and 14th grant of the year would be: 0814How can I implement this. Right now, I have a "New Grant.aspx" pagewith a Submit button. I am guessing the date is going to be formattedin C#. How can I check what the last primary key in the database is?Also, it seems to me that SQL Server insists that the primary key be32 bits long, however my primary key will only be 4. How can Ioverride this? Thanks.
View 3 Replies
View Related
May 19, 2007
I'm trying to create a custom data flow destination, and it has a custom property that needs to get value from variable(similar to the FileNameVariable property of Raw File Destination), how can I do that?
View 5 Replies
View Related
Dec 20, 2007
Hello,
The main problem I am currently facing is that I do not have the following directory to place the DLL file: C:Program FilesMicrosoft SQL Server80ToolsReport Designer. It is the "Report Designer" part that is missing. I tried adding it, but that didn't work.
Any ideas why that is?
Thank you.
-Gumbatman
View 4 Replies
View Related
Jan 15, 2008
I would like to write a custom data source component for SSIS. I was wondering if there are any tutorials / examples for this that are available.
Thanks
View 1 Replies
View Related
Apr 10, 2006
Microsoft published a "Creating a custom transformation component Walkthrough" published on
http://www.microsoft.com/downloads/details.aspx?FamilyID=1c2a7dd2-3ec3-4641-9407-a5a337bea7d3&DisplayLang=en
Does anyone know where to get the Hands-On Lab Files mentioned?
Thanks
Alex
View 4 Replies
View Related
Feb 28, 2006
http://msdn2.microsoft.com/en-us/library/ms345276.aspx
does anyone know where this can be obtained? it did not come with the RTM. it does not seem to be available via download either?
a little help here!
View 5 Replies
View Related
Feb 14, 2006
Hi,
I have a 2 custom components - source and destination.
I want to create an error output for each, to allow the users of my component to handle errors the way they choose.
I only found a property in IDTSOuptut90 named isErrorOut - a boolean property indicating whether this output is an error output or not.
Does anyone have additional documentation / articles / code samples regarding how to really populate the rows in the error output?
Thanks
View 1 Replies
View Related
Jan 17, 2008
Hello all, I am developing my own custom export type via the IRenderingExtension interface, and I am having a fundamental problem getting it to work.
I followed this guide written by James Yip, http://forums.microsoft.com/TechNet/AddPost.aspx?ForumID=82&SiteID=17, and managed to compile my SampleRenderer class and properly configure SRSS so that my 'Sample' export format is now available in the export dropdown in Report Manager.
However, upon actually exporting a report, the result is a blank file. I have absolutely no idea why the stream I create isn't getting through to the final file.
Here is my render method:
Code Block
public bool Render(Microsoft.ReportingServices.ReportRendering.Report report, System.Collections.Specialized.NameValueCollection reportServerParameters, System.Collections.Specialized.NameValueCollection deviceInfo, System.Collections.Specialized.NameValueCollection clientCapabilities, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStream)
{
System.IO.Stream stream = null;
System.IO.StreamWriter streamWriter = null;
try
{
stream = createAndRegisterStream("users", "txt",System.Text.Encoding.ASCII, "text/plain", false, StreamOper.CreateAndRegister);
streamWriter = new System.IO.StreamWriter(stream);
streamWriter.WriteLine("Hello???");
}
finally
{
if (streamWriter != null)
{
//streamWriter.Close();
//closing the stream here results in an exception
}
if (stream != null)
{
//stream.Close();
}
}
return false;
}
Any ideas on what I am doing wrong here?
Thanks,
Dan
View 1 Replies
View Related
Aug 14, 2007
I would like my transformation to automatically create an output column for each input column. Any tips? I can't seem to determine which event to listen to or method to override.
View 3 Replies
View Related
Sep 17, 2015
The other day developer asked me to create a new table that had a FK constraint to an existing largish table on the largish table's primary key.
When i ran the script, it took it about 4 seconds to complete, and in the meantime i had 3 deadlocks show up from other processes attempting to use the table the FK was being created from. The new table was empty at this point.
I've never seen this behavior before so I'm curious if creating a new FK constraint will cause a table lock on the source table for the FK.
View 1 Replies
View Related
Sep 30, 2014
We have a database where many tables have a field that has to be lengthened. In some cases this is a primary key or part of a primary key. The table in question is:-
/****** Object: Table [dbo].[DTb_HWSQueueMonthEnd] Script Date: 09/25/2014 14:05:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DTb_HWSQueueMonthEnd](
[Code] ....
The script I am using is
DECLARE@Column varchar(100)--The name of the column to change
DECLARE@size varchar(5)--The new size of the column
DECLARE @TSQL varchar(255)--Contains the code to be executed
DECLARE @Object varchar(50)--Holds the name of the table
DECLARE @dropc varchar(255)-- Drop constraint script
[Code] ....
When I the the script I get the error message Could not create constraint. See previous errors.
Looking at the strings I build
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] DROP CONSTRAINT PK_DTb_HWSQueueMonthEnd
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] Alter Column [Patient System Number] varchar(10)
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] ADD CONSTRAINT PK_DTb_HWSQueueMonthEnd PRIMARY KEY NONCLUSTERED ([Patient System Number] ASC,[Episode Number] ASC,[CensusDate] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
They all seem fine except the last one which returns the error
Msg 8111, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table 'DTb_HWSQueueMonthEnd'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
None of the fields I try to create the key on are nullable.
View 2 Replies
View Related
Nov 12, 2014
In a Stored Proc I am creating the following temp table and index:
CREATE TABLE [dbo].[#ShipTo](
[Ship_to_Num] [int] NOT NULL,
[Country_key] [nvarchar](3) NULL,
CONSTRAINT [PK_ShipTo] PRIMARY KEY CLUSTERED
(
[ship_to_Num] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
The stored Proc runs fine from "exec", but when you batch into a Job it gives the error that "PK_ShipTo" already exists! I even put in a drop table on #ShipTo, but the same effect.
View 9 Replies
View Related
Apr 26, 2015
On a new project i need to create possible ranges between a specific interval.
suppose i have this main product:
main product : LY E67F
Also, in first level i have a table classify by Group depending on Intensity.
table group
Group intensity
AA 1120
AB 1400
BA 1800
BB 2240
CA 2800
I need to create these diferent options:
1 option : AAAB or AABA or AABB or AACA
2 option : ABBA or ABBB or ABCA
3 option : BABB or BACA or BBCA
or beginning from the end
1.option CABB or CABA...
or beginning from the middle
1.option BBBA or BBAB
and so on.
I fact, i need to find all available options possibles to build article code, like a matrix.
View 7 Replies
View Related
Jan 24, 2015
I am working on creating a timetable app for school. I have been given a requirement as below
1. The weekdays like Monday,Tuesday,...should be displayed on top
2. Timing slot would be 8:00, 9:00,... which would be displayed vertically
the table would display as below
MondayTuesday WednesdayThursdayFriday Saturday
07:00Physics Chemistry Maths BiologySocialScience Craft
08:00MathsValueEducation Chemistry EnglishBiology Yoga
09:00GamesComputerScience Science Chemistry Hindi Maths
to display data like above would be simpler. The above timetable would be similar all through the year.
But my 3rd requirement is that, there is a option wherein the school admin can edit the timetable for any particular day or a week based on presence/absence of a particular teacher. So if a teacher who is teaching Maths is not present for 2 days, they might assign a different subject(eg., Physics) for those 2days to a different teacher.
so the table should look like below for those two days only based on the date selection.
Timings MondayTuesday WednesdayThursday Friday Saturday
07:00Physics Chemistry Physics Biology SocialScience Craft
08:00MathsValueEducation Chemistry English Biology Yoga
09:00GamesComputerScience Science Chemistry Hindi Physics
I am not sure how exactly I can mention the dates wherein I can allocate the days when the periods are changed and how exactly same timetable should be shown for the entire year if there is no change.
suppose the timetable is changed for two days 24/01/2015 and 25/01/2015 wherein Maths is replaced with Physics subject, how to retrieve the changed data for only those two days.
View 9 Replies
View Related
Nov 6, 2014
creating the missing records in a date/time range.
However, I need to return different groups for each span of records.
here's some data....
aaa1
aaa7
bbb2
bbb5
bbb6
The numbers are the hour of the day.
I need to return
aaa 0 0
aaa 1 1
aaa 2 0
aaa 3 0
...
bbb 0 0
bbb 1 0
bbb 2 1
...
and so on.
I've got a numbers table and I can left join with it but I just get nulls for the missing hours instead of having it as above.....I can't think of a way of repeating the groups for each of the 'missing' hours - other than creating a length insert statement to fill in the gaps....unless that is the only way of doing it.
View 6 Replies
View Related
Nov 16, 2014
In the T-SQL below, I retrieved data from two queries and I've tried to join them to create a report in SSRS 2008 R2. The SQL runs, but I can't create a report from it. (I also couldn't get this query to run in an Excel file that connects to my SQL Server data base. I've used other T-SQL queries in this Excel file and they run fine.) I think that's because I am creating temporary tables. How do I modify my SQL so that I can get the same result without creating temporary tables?
/*This T-SQL gets the services for the EPN download from WITS*/
-- Select services entered in the last 20 days along with the MPI number and program code.
SELECT DISTINCT dbo.group_session_client.note, dbo.group_session_client.error_note, dbo.group_session_client.group_session_id,
dbo.group_session_client.group_session_client_id, dbo.group_session.signed_note, dbo.group_session.unsigned_note
into #temp_group_sessions
FROM dbo.group_session_client, dbo.group_session
WHERE dbo.group_session_client.group_session_id = dbo.group_session.group_session_id
-- Select group notes
SELECT DISTINCT
dbo.client_ssrs.state_client_number, dbo.delivered_service_detail.program_name, dbo.delivered_service_detail.start_date,
dbo.delivered_service_detail.start_time,
dbo.delivered_service_detail.service_name, dbo.delivered_service_detail.cpt_code, dbo.delivered_service_detail.icd9_code_primary,
[code]....
-- Form an outer join selecting all services with any group notes attached to them.
select * from #temp_services
LEFT OUTER JOIN #temp_group_sessions
on #temp_services.group_session_client_id = #temp_group_sessions.group_session_client_id
;
-- Drop temporary tables
DROP TABLE #temp_group_sessions;
DROP TABLE #temp_services;
View 9 Replies
View Related
Jun 24, 2014
I have a script that loops through a series of tables to send data to a table from each of the tables. My issue is that not all tables have the columns I need in them. What I would like is to replace the column with another column when it does not exist. Something like below
Select Misisng_Column(A.Name, replace with B.Name) as Name
FROM SomeTable A
Cross Join (Select Name FROM AnotherTable) B
AnotherTable has one record in it. To avoid a Cartesian issue. Like I said just an example
In my real script the table aliased as A is from a list of tables in a sys.tables query that loops through to the end.
View 2 Replies
View Related
Jun 3, 2010
I am trying to find a way to add into a table a flattened (comma seperated list) of email addresses based on the multiple columns of nformation in another table (joined by customer_full_name and postcode.
This is to highlight duplicate email addresses for people under the same customer_full_name and Postcode.
I have done this using a loop which loops through concatenating the email addresses but it takes 1minute to do 1000. The table is 19,000 so this isn't really acceptable. I have tried temp tables, table variables and none of this seems to make any difference. I think that it is becuase i am joining on text columns?
Create table #tempa
(
customer_Full_Name varchar(100),
Customer_Email varchar(100),
Postcode varchar(100),
AlternateEmail varchar(max)NULL
)
insert into #tempa (customer_full_name,customer_email,postcode)
[code]....
View 9 Replies
View Related
Jun 25, 2006
Hi All,
I am not sure if this is a correct forum to discuss on the document posted @ http://www.microsoft.com/downloads/details.aspx?familyid=1c2a7dd2-3ec3-4641-9407-a5a337bea7d3&displaylang=en on SQL Server Integration Services (SSIS) Hands on Training - Creating Custom Components.
I am assuming Microsoft Developers are constantly monitoring this forum.
In the document - SSIS Creating a Custom Transformation Component .doc on Page 2 -
Exercise 1 - Writing the no-op data flow transformation component -
Task 1 - Create a new C# Class Library Project
The textual description talks about creating a new Visual C# Class Library project in VS 2005 but the screenshot accompanying it shows the creation of new "Integration Service Project" in VS 2005.
Please change the screenshot appropriately to avoid confusions.
Thanks,
Loonysan
View 1 Replies
View Related
Feb 27, 2008
Excuse me if my terminlogy is inaccurate, i'm a .NETer that's new to SQL.
I was wondering if it's possible to reference a column in the WHERE CLAUSE that has been customily defined in the SELECT statement
for example
select employeeID, case when JobCode = 'A' then 'Accountant'
case when JobCode = 'C' then 'Consultant'
case when JobCode = 'B' then 'Biller'
End as JobType
from Employee
where JobType is not null
This does not work, and saids JobType is an invalid column name. Basically, what I want is to display the jobtype of the employee but i also want to only display the employees that are Accountants, consultants and billers.
Is this possible and what would be the best way of doing this?
I do not seem to be about to reference JobType in the WHERE statement.
View 10 Replies
View Related
Feb 27, 2008
Excuse me if my terminlogy is inaccurate, i'm a .NETer that's new to SQL.
I was wondering if it's possible to reference a column in the WHERE CLAUSE that has been customily defined in the SELECT statement
for example
select employeeID, case when JobCode = 'A' then 'Accountant'
case when JobCode = 'C' then 'Consultant'
case when JobCode = 'B' then 'Biller'
End as JobType
from Employee
where JobType is not null
This does not work, and saids JobType is an invalid column name. Basically, what I want is to display the jobtype of the employee but i also want to only display the employees that are Accountants, consultants and billers.
Is this possible and what would be the best way of doing this?
I do not seem to be about to reference JobType in the WHERE statement.
View 1 Replies
View Related
Dec 13, 2007
Hi,
I am reading data from a Excel file. The first Row in the Excel file are the column names. The first row is a Time value. The other rows are only numeric values.
Sample of Excel File:
Timestamp SourceColumn1 SourceColumn2
12/12/2007 12:00 AM 30 20.4
12/12/2007 01:00 PM 35 21.2
12/12/2007 02:00 PM 32 22.5
.
.
.
What I now have to do is to extract the numeric values for every column and write the value at each row at the timestamp (given in the first column) to a database.
This means I have to run a SQL command on every column and every row.
Example:
#Processing Row 1
INSERT INTO Table VALUES (destinationcolumn1, destinationcolumn2, timestamp) VALUES (sourcecolumn1, valueatcurrentrow, timestampatcurrentrow)
INSERT INTO Table VALUES (destinationcolumn1, destinationcolumn2, timestamp) VALUES (sourcecolumn2, valueatcurrentrow, timestampatcurrentrow)
#Processing Row 2
INSERT INTO Table VALUES (destinationcolumn1, destinationcolumn2, timestamp) VALUES (sourcecolumn1, valueatcurrentrow, timestampatcurrentrow)
.
.
.
Does anyone know how to implement this in Integration Services?
Regards
Johannes.W
View 8 Replies
View Related
Mar 8, 2007
Hi,
I am creating a custom transformation component, and a custom user interface for that component.
In
my custom UI, I want to show the custom properties, and allow users to
edit these properties similar to how the advanced editor shows the
properties.
I know in my UI I need to create a "Property Grid".
In
the properties of this grid, I can select the object I want to display
data for, however, the only objects that appear are the objects that I
have already created within this UI, and not the actual component
object with the custom properties.
How do I go about getting the properties for my transformation component listed in this property grid?
I am writing in C#.
View 5 Replies
View Related
May 22, 2008
We are developing a batabase which is meant for financial domain,so it will import data from different source system..
and data from our data base will be further passed to other applications.
In contex of our system integration with other data sources ,whether is it a good idea to have a auto integer primary key a or to implement some logic to generate primary key?
Can some one guide us to some pratical data base design case studies?or some best practices.?
View 20 Replies
View Related
Feb 7, 2006
Does anyone know how to get destination coulmns to show up in the advanced editor for a custom component? I have a custom flat file destination component that builds the output based on a specific layout. It works as long as the upstream column names match my output names. What I want is to allow non-matching columns to be mapped by the user as they can in a stock flat file destination. The closest that I have been able to come is to get the "column mappings" tab to show up and populate the "Available Input Columns" by setting ExternalmetadataColumnCollection.IsUsed to true on the input. The problem is that the "Available destination columns" box is always empty. I have tried the IsUsed property on the output and pretty much every other property that I could find. On the Input and Output properties all of my columns show up under the output as both External and Output columns. Is there a separate collection for "destination" columns that I can't find? It's getting a little frustrating, is this something that can be done or do I have to write a custom UI to make it happen?
Thanks!
Harry
View 5 Replies
View Related
