ChrisM@Work

Forum Replies Created

Viewing 15 posts - 6,976 through 6,990 (of 10,143 total)

RE: Assign Group ID To Rows

cimbom_kid (11/30/2010)
Hi Chris Morris,
Many thanks for your help, but the query doesn't return the results i need. The resulting query should be:
linenum, fileline, group_id
1 a, 1
2 b, 1
3 c, 1
4...
^{“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

November 30, 2010 at 6:52 am

#1256762
RE: Assign Group ID To Rows

DROP TABLE #TMP_LOADER
CREATE TABLE #TMP_LOADER (
linenum int IDENTITY (1, 1),
fileline varchar(max),
group_id int)
INSERT INTO #TMP_LOADER (fileline)
SELECT 'a' UNION ALL
SELECT 'b' UNION ALL
SELECT 'c' UNION ALL
SELECT '------' UNION ALL
SELECT 'd' 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

November 30, 2010 at 5:41 am

#1256717
RE: How do you create a view to select data from table & force static data into alternating rows

You're welcome Dan, thanks for the generous feedback.
Don't forget in future to always include sample data.
^{“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

November 30, 2010 at 4:30 am

#1256690
RE: How do you create a view to select data from table & force static data into alternating rows

Thanks Dan.
SELECT rn = ROW_NUMBER() OVER(ORDER BY JobNo, Line),
Line, JobNo, PropRef, Cont, RepDate, RepTime, LogDate, LogTime, ShortDesc, Access, JobDesc, ClientJob
FROM (
SELECT Line, JobNo, PropRef, Cont, RepDate, RepTime, LogDate, LogTime,...
^{“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

November 30, 2010 at 4:13 am

#1256682
RE: How do you create a view to select data from table & force static data into alternating rows

Try this, untested of course - no sample data to test against!
DROP TABLE #Table
CREATE TABLE #Table (
JobNo CHAR(5),
...
^{“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

November 30, 2010 at 3:28 am

#1256666
RE: How do you create a view to select data from table & force static data into alternating rows

Dan, if you set up the sample data like I suggested, it will be instantly usable. Not many folks will be inclined to do the formatting for you.
SELECT 'JobNo', 'Ref',...
^{“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

November 30, 2010 at 3:07 am

#1256661
RE: How do you create a view to select data from table & force static data into alternating rows

Dan
Copy the following code and paste it into an SSMS window. Correct the DDL to match your table then amend the INSERT script to provide say 10 rows of data...
^{“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

November 30, 2010 at 2:36 am

#1256655
RE: SUB QUERY INSIDE CASE STATEMENT

darunsurey (11/29/2010)
I need to know whether we can use corelated sub query in case satatements.
Arun
Yes you can but it's rarely a good idea.
darunsurey (11/29/2010)
I have a student table, employee table,...
^{“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

November 29, 2010 at 7:15 am

#1256240
RE: Passed 70-450 exam today

RichardDouglas (11/27/2010)
Duplicate post due to refresh error
Haha! That's how I ended up posting "What's next"!
^{“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

November 29, 2010 at 2:12 am

#1256143
RE: Getting Last 12 Month-Year from present date

Sorry my bad - this matches your original requirement in your first post.
You would benefit from experimenting with the code and understanding how it works:)
DECLARE @Today DATETIME, @nMonths TINYINT
SET...
^{“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

November 25, 2010 at 10:05 am

#1255621
RE: Getting Last 12 Month-Year from present date

Which query are you running? If you altered the code, best post it here.
^{“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

November 25, 2010 at 9:43 am

#1255608
RE: cross apply

kevin4u06 (11/24/2010)
This is the table and sample data (Attached below)
my out put shoud be something like this.
My function does the splitting of the address field .
Thank you in advance
You might...
^{“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

November 25, 2010 at 8:55 am

#1255591
RE: Best way to write a sum query.

Kelvin Phayre (11/25/2010)
One last thing.
From your data.
INSERT INTO @Table2 VALUES (01,'P1','01/01/2010',100),
...
^{“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

November 25, 2010 at 6:55 am

#1255561
RE: Best way to write a sum query.

-- Make a couple of sample tables
CREATE TABLE #Table1 (wh CHAR(2), item CHAR(2), stock INT)
INSERT INTO #Table1 (wh, item, stock)
SELECT '01','P1',1 UNION ALL
SELECT '02','P1',1 UNION ALL
SELECT '03','P1',1
CREATE TABLE #table2 (wh...
^{“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

November 25, 2010 at 6:53 am

#1255560
RE: Header table display count of detail table per row

-- use an INNER JOIN if the data permits
-- i.e. if every header has at least one line, and always will.
-- Option 2
SELECT
h.GroupHeaderID,
h.GroupName,
MemberCount = COUNT(*)
FROM table_A h
LEFT JOIN table_B l...
^{“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

November 25, 2010 at 6:37 am

#1255556

Viewing 15 posts - 6,976 through 6,990 (of 10,143 total)