Concatenate nuimbers

I have two fields that have numbers company and employee, I want to
concatenate these numbers so it it takes the comapny number (9000) and
employee number (116258) and makes 9000116258.
When i use this query
(select company+employee as uid from cr_staging) it adds the numbers.
I also tried
SELECT Company, Employee, Company & ' ' & Employee AS uid
FROM cr_staging
but it gives me an error invalid operator for data type.
Is there any suggestions on this problem?
Thanks,
This will work:
select CONVERT(varchar(10),company) + convert(varchar(10),employee) from
cr_staging
"Eric" wrote:

> I have two fields that have numbers company and employee, I want to
> concatenate these numbers so it it takes the comapny number (9000) and
> employee number (116258) and makes 9000116258.
> When i use this query
> (select company+employee as uid from cr_staging) it adds the numbers.
> I also tried
> SELECT Company, Employee, Company & ' ' & Employee AS uid
> FROM cr_staging
> but it gives me an error invalid operator for data type.
> Is there any suggestions on this problem?
>
> Thanks,
>
|||+ adds numbers when they are numbers (int, numeric...)
+ combines varchar data
You need to convert the columns to varchar before you "add" them.
SELECT CONVERT(varchar(20),company) + CONVERT(varchar(20),employee)
FROM cr_staging
Keith
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:9862CFBF-3B7A-43B0-9A33-136F2B228A05@.microsoft.com...
> I have two fields that have numbers company and employee, I want to
> concatenate these numbers so it it takes the comapny number (9000) and
> employee number (116258) and makes 9000116258.
> When i use this query
> (select company+employee as uid from cr_staging) it adds the numbers.
> I also tried
> SELECT Company, Employee, Company & ' ' & Employee AS uid
> FROM cr_staging
> but it gives me an error invalid operator for data type.
> Is there any suggestions on this problem?
>
> Thanks,
>
|||Assuming that your fields are defined as INT and that you want to keep the
same number of digits (4 + 6), try this:
select
(
RIGHT('0000' + CAST(Company AS varchar(4)), 4)
+
RIGHT('000000' + CAST(Employee AS varchar(6)), 6)
)
as UID
from cr_staging
This will give you the result: '9000116258'
HTH,
Robert

Concatenate nuimbers

I have two fields that have numbers company and employee, I want to
concatenate these numbers so it it takes the comapny number (9000) and
employee number (116258) and makes 9000116258.
When i use this query
(select company+employee as uid from cr_staging) it adds the numbers.
I also tried
SELECT Company, Employee, Company & ' ' & Employee AS uid
FROM cr_staging
but it gives me an error invalid operator for data type.
Is there any suggestions on this problem?
Thanks,This will work:
select CONVERT(varchar(10),company) + convert(varchar(10),employee) from
cr_staging
"Eric" wrote:
> I have two fields that have numbers company and employee, I want to
> concatenate these numbers so it it takes the comapny number (9000) and
> employee number (116258) and makes 9000116258.
> When i use this query
> (select company+employee as uid from cr_staging) it adds the numbers.
> I also tried
> SELECT Company, Employee, Company & ' ' & Employee AS uid
> FROM cr_staging
> but it gives me an error invalid operator for data type.
> Is there any suggestions on this problem?
>
> Thanks,
>|||+ adds numbers when they are numbers (int, numeric...)
+ combines varchar data
You need to convert the columns to varchar before you "add" them.
SELECT CONVERT(varchar(20),company) + CONVERT(varchar(20),employee)
FROM cr_staging
--
Keith
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:9862CFBF-3B7A-43B0-9A33-136F2B228A05@.microsoft.com...
> I have two fields that have numbers company and employee, I want to
> concatenate these numbers so it it takes the comapny number (9000) and
> employee number (116258) and makes 9000116258.
> When i use this query
> (select company+employee as uid from cr_staging) it adds the numbers.
> I also tried
> SELECT Company, Employee, Company & ' ' & Employee AS uid
> FROM cr_staging
> but it gives me an error invalid operator for data type.
> Is there any suggestions on this problem?
>
> Thanks,
>|||Assuming that your fields are defined as INT and that you want to keep the
same number of digits (4 + 6), try this:
select
(
RIGHT('0000' + CAST(Company AS varchar(4)), 4)
+
RIGHT('000000' + CAST(Employee AS varchar(6)), 6)
)
as UID
from cr_staging
This will give you the result: '9000116258'
HTH,
Robert

Computing change from first sales by employee

I'll use AdventureWorks to frame my question, as then I can extend any suggestions to the possible applications I need.

I want to create a calculation which finds the change of each employee's monthly sales amount from their first month's sales.

e.g. for each month it will show how their sales for that month differs from the first month they ever made a sale.

What I'm struggling with is that one employee may have made their first sale in 2001, while another made their first sale in 20003. I'd like to be able to show how each employee's sales change relative to their first month, i.e. in month 2, month 3, etc.

I am thinking that I will need to define a new calculated measure which is the number of months elapsed for each employee since their first sale and then define a calculation for their deltas per month, but I'm not sure.

I've read up on OpeningPeriod() and the "Opening Period Balance" template, but I'm stuck. Using AS 2005.

Thanks for any suggestions.

-Leif Kirschenbaum

Leif,

Here is an example that I think will give you what you are looking for. I included a measure called "First Months Sales" which is just to show how the calculation is working and is not really needed for the end result.

HTH,

Steve

WITH

MEMBER [Date].[Calendar].[First Month With Sales]

AS

Filter([Date].[Calendar].[Month].Members, [Measures].[Reseller Sales Amount] > 0)(0)

MEMBER [Measures].[First Months Sales]

AS

([Date].[Calendar].[First Month With Sales],

[Measures].[Reseller Sales Amount]),

FORMAT_STRING = "currency"

MEMBER [Measures].[Difference]

AS

IIF(Exists({[Date].[Calendar].CurrentMember},{Filter([Date].[Calendar].[Month].Members, [Measures].[Reseller Sales Amount] > 0)(0).Lead(1):NULL}).Count = 1 AND

[Measures].[Reseller Sales Amount] > 0,

[Measures].[Reseller Sales Amount] - ([Date].[Calendar].[First Month With Sales],[Measures].[Reseller Sales Amount]),

NULL),

FORMAT_STRING = "currency"

SELECT

{[Date].[Calendar].[Month].Members} ON COLUMNS,

{[Reseller].[Reseller].[Bike Rims Company],

[Reseller].[Reseller].[Certified Sports Supply]} *

{[Measures].[Reseller Sales Amount],

[Measures].[First Months Sales],

[Measures].[Difference]} ON ROWS

FROM

[Adventure Works]

|||Great!
Thanks.
I do need one other thing, I need a dimension which counts the months from the first month for each employee. Right now in the browser when I drag Employee hierarchy to the rows field and Calendar to the columns field the difference for each employee starts in a different month. It would be useful to be able to drag "Months of Employment" to the columns field.
Would I do:

CREATE MEMBER CURRENTCUBE.[Date].[Calendar].[Months of Employment]
AS
[Date].[Calendar].[Month] - [Date].[Calendar].[First Month With Sales],
FORMAT_STRING = "Standard",
NON_EMPTY_BEHAVIOR = { [Reseller Sales-Sales Amount] },
VISIBLE = 1 ;

That doesn't work.

CREATE MEMBER CURRENTCUBE.[Measures].[Months of Employment]

AS

[Date].[Calendar].[Month] - [Date].[Calendar].[First Month With Sales],

FORMAT_STRING = "Standard",

NON_EMPTY_BEHAVIOR = { [Reseller Sales-Sales Amount] },

VISIBLE = 1 ;

also doesn't work, as I can't drag "Months of Employment" to the columns field.|||

Leif,

Try the following:

MEMBER [Measures].[Months of Employment]

AS

IIF(Exists({[Date].[Calendar].CurrentMember},{Filter([Date].[Calendar].[Month].Members, [Measures].[Reseller Sales Amount] > 0)(0).Lead(1):NULL}).Count = 1,

{Filter([Date].[Calendar].[Month].Members, [Measures].[Reseller Sales Amount] > 0)(0):[Date].[Calendar].CurrentMember}.Count - 1,

NULL),

FORMAT_STRING = "#,#"

Complex ORDER BY - possible?

I want to sort recrords by two columns, but would like to order them by a fixed value in the first column.

Example.

I have an employee database, and want to sort by SKILL, YEARS_EXPERIENCE.

But I want a specific skill listed first, then all other skills.

Such as (I just made this up):

SELECT * from emp_master order by (SKILL='C#', YEARS_EXPERIENCE DESC), (SKILL <> 'C#', YEARS_EXPERIENCE DESC).

So my results would be:

C#, 10
C#, 7
C#, 5
ASP.NET, 10
ASP.NET, 9
ASP.NET, 5
SQL, 5
SQL, 4
VB, 5
VB, 3

This is handy for 'near' matches where I want a preferred result to filter to the top, but all results in some order.

Is this possible?One thing you might consider is returning the results for C# and sorting those, then UNION joining that to a result set that does NOT contain C#.|||UNION still just sorts by the common sort criteria, unless I am doing something wrong.

If I use two SQL statements, SQL A chooses 'C#' and years DESC, the SQL B chooses <> 'C#' and years DESC, and then UNION, they all come back in order of the years DESC without the C# being first on the list.|||You should be able to use a CASE statement:

SELECT * from emp_master
ORDER BY
CASE [Skill] WHEN 'C#' THEN 0 ELSE 1 END,
Skill,Years_Experience DESC

|||Yup, that'll definitely do it! :)|||EOM

Complex join

I have a C# application for tracking training. When I need to get the employee's missed training (exception report) it works fine on a one-by-one basis. Our HR folks can't sit and spin through 100s of screens, so I want to provide a comprehensive report for all employees.

Here is the first hack which places everything into a temporary table. Problem is I can't get a while loop to work with it as is. All three "sub queries" build the exception (missing) topics into the temp table.

I can loop in my application, but it makes the user click the print button for each report. I'm trying to get one report for all employees and all exceptions, breaking on employeeID. I figure they will run this quarterly to make sure everyone has their required training.

Any help is greatly appreciated. I didn't find anything in my ref books to help.

_E

DECLARE @.EmployeeID int
DECLARE @.MaxCount int

-- example only, real count will be +/- 200 and = to the number of active employees
set @.MaxCount = 10

-- would like to exclude inactive employees
SET @.EmployeeID =1

-- can use the employee table as input/limit?
WHILE @.EmployeeID < (select employeeID from employee)

DECLARE @.MyTable TABLE (TopicID INT)
INSERT INTO @.MyTable

-- topics based on primary role
SELECT rt.TopicID FROM ROLETOPICS rt
LEFT OUTER JOIN Employee e ON e.EmployeeID = @.EmployeeID
WHERE rt.RoleID = e.PrimaryRoleID
UNION
-- optional topcs
SELECT TopicID
FROM EmployeeTopics et
WHERE et.EmployeeID = @.EmployeeID
-- required topics (all employees)
UNION
SELECT TopicID
FROM Topic t
WHERE t.CategoryID = 3
SELECT mt.TopicID, Topic FROM @.MyTable mt
INNER JOIN Topic t ON mt.TopicID = t.TopicID
WHERE mt.TopicID NOT IN(SELECT tr.TopicID FROM Training tr WHERE tr.EmployeeID = @.EmployeeID)

--increment counters

I know I've left out some counter and variable initializers, but I can't get the basis loop to work at all for more than one record and that's specifice to an EmployeeID.

You don't need a loop, not sure of the best way to do the mandatory training, but this is a starter for 10.

SELECT ReqdTraining.employeeId, ReqdTraining.TopicId

FROM(

SELECT e.employeeId, rt.TopicID

FROM ROLETOPICS rt

JOIN Employee e ON rt.RoleID = e.PrimaryRoleID

UNION

SELECT et.employeeid, TopicID

FROM EmployeeTopics et

UNION

-- required topics (all employees)

SELECT E.EmployeeId, T.TopicID

FROM Topic t

CROSS JOIN Employee E

WHERE t.CategoryID = 3) ReqdTraining

LEFT JOIN Training T ON T.TopicId = ReqdTraining.TopicId

AND T.EmployeeId = ReqdTraining.EmployeeId

WHERE T.TopicId IS NULL

|||

Thank you, SimonSa. Cross join. D'Oh! Guess I should stick to object code, my DBA skills need work (which I'll get in the MCSD program.)

_E