ChrisM@Work

Forum Replies Created

Viewing 15 posts - 4,966 through 4,980 (of 10,143 total)

RE: Creation of auto Increment row

winmansoft (3/26/2013)
Hi,
I am using Sql server 2008 R2 express.I want a column id with auto incrementing value in my table.But i don't want to use built in Auto increment property...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

March 26, 2013 at 7:12 am

#1600772
RE: Creation of auto Increment row

Edit: duplicate post.
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

March 26, 2013 at 7:07 am

#1600768
RE: how to insert datetime column into table

select top 5 machine.name, machine.model, machine.scantime
'INSERT INTO [SMS_000].[dbo].[BMCMachines] ([ComputerName],[MachineModel],[stime]) VALUES (' + '''' + machine.name + ''',' + '''' + machine.model + ''',' + convert(datetime,machine.scantime,112) + ''')' from...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

March 26, 2013 at 6:58 am

#1600761
RE: how to insert datetime column into table

Steve JP (3/26/2013)
...The issue with the convert is that the 3rd arguement is the style and is used to set the style when you convert from a datetime to a...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

March 26, 2013 at 6:52 am

#1600757
RE: calculating period of time

astrid 69000 (3/26/2013)
Chris, i did what i asked. your query added groups to the dates. from 1 to 8 group 1, from 9 to 16 group 2 and so on...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

March 26, 2013 at 6:00 am

#1600735
RE: Lock only one row in update (lock a row)

abitguru (3/26/2013)
GilaMonster (3/26/2013)
abitguru (3/26/2013)
SELECT * FROM Reque_pa WITH (UPDLOCK) WHERE EMPRE=1 AND CEREQ = 1
UPDATE Reque_pa SET Proximo=107918, Pendiente=0 WHERE EMPRE=1 AND CEREQ=1
You're selecting, then updating the table with...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

March 26, 2013 at 5:33 am

#1600720
RE: Assistance with this query

You will need to apply similar logic to all tables which have duplicate rows per employeeid. I'd guess that you need the most recent rows containing org.ORGANIZATIONID and job.JOBTITLEID. Can...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

March 26, 2013 at 5:21 am

#1600717
RE: calculating period of time

Astrid, please look very carefully at the results of Kingston's solution and mine. They look the same but they are not. One of them will be correct (probably!), the other...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

March 26, 2013 at 4:41 am

#1600703
RE: Reorder a custom order column

Is this what you're looking for?
DROP TABLE #SampleData
;WITH SampleData AS (
SELECT *
FROM (VALUES ('A',1), ('B',2), ('C',3), ('D',4), ('E',5), ('F',6)) d ([SomeStuff], [OrderlineID])
)
SELECT *
INTO #SampleData
FROM SampleData
-- check
SELECT * FROM...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

March 26, 2013 at 4:36 am

#1600698
RE: Assistance with this query

tmac25 (3/26/2013)
This is really odd, Chris. ...
And so... Any ideas?
Yes - one or both of the other two tables has multiple rows per employee. Can you check both tables to...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

March 26, 2013 at 4:04 am

#1600687
RE: calculating period of time

-- Create a sample data set to work with
-- A #temporary table called #AstridsSampleData
IF object_id('TempDB..#AstridsSampleData') IS NOT NULL
DROP TABLE #AstridsSampleData
;WITH CTE_SomeStuffIMadeUp ([day], [Count]) AS (
SELECT '2008-01-01 00:00:00.000', 3 UNION...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

March 26, 2013 at 3:59 am

#1600685
RE: Assistance with this query

Run this, post the first ten or so rows returned:
SELECT rn = ROW_NUMBER() OVER(PARTITION BY employeeid ORDER BY ACTIONORDER DESC),
employeeid,
FIRSTNAME,
LASTNAME,
EMAILADDRESS,
STARTDATE,
ACTIONORDER
FROM linkedServer.linkedDB.dbo.AUEMPLOYEE as employee
WHERE employee.ENDDATE IS...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

March 26, 2013 at 3:43 am

#1600677
RE: calculating period of time

astrid 69000 (3/26/2013)
yes i did, but i get confused, the "SELECT '2008-01-01 00:00:00.000', 3 UNION ALL" is not the one i need to add one by one manually?
the query...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

March 26, 2013 at 3:39 am

#1600675
RE: Assistance with this query

D'Oh!
;WITH Employees AS (
SELECT rn = ROW_NUMBER() OVER(PARTITION BY employeeid ORDER BY ACTIONDATE DESC),
employeeid,
FIRSTNAME,
LASTNAME,
EMAILADDRESS,
STARTDATE
FROM linkedServer.linkedDB.dbo.AUEMPLOYEE
WHERE employee.ENDDATE IS NULL -- still active
)
SELECT
e.*,
org.ORGANIZATIONID,
job.JOBTITLEID
FROM...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

March 26, 2013 at 3:01 am

#1600661
RE: calculating period of time

astrid 69000 (3/26/2013)
but if i create the date table dont i have to add the dates manually?
to calculate the first period i go
select sum(Count) from #table where recordday >= '2008-01-01'...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

March 26, 2013 at 2:47 am

#1600655

Viewing 15 posts - 4,966 through 4,980 (of 10,143 total)