ChrisM@Work

Forum Replies Created

Viewing 15 posts - 4,021 through 4,035 (of 10,143 total)

RE: Stored procedure to run results of view

You may get better performance if you detach the SELECT from the INSERT by running the results of the SELECT into a temp table.
Also, since your SELECT reads local and...
^{“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

September 5, 2013 at 4:20 am

#1647639
RE: case when looping through column values and applying conditions depending on value

Luis Cazares (9/4/2013)
And why don't you create it? It's a very powerful tool.
Absolutely. But if you can't (your DBA won't let you) then you can hack the code into your...
^{“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

September 5, 2013 at 3:38 am

#1647631
RE: Project Plan

Project plan for setting up the server?
Project plan for moving data to it from a different server?
Can you be more specific?
^{“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

September 5, 2013 at 2:32 am

#1647614
RE: Stored procedure to run results of view

I've no idea, because it's in the original query:
Original:
WHERE
(dbo.Mobitech_Filtered_Task.PIMS_IMPORTED IS NULL) OR
(dbo.Mobitech_Filtered_Task.PIMS_IMPORTED = 0)
New:
SELECT t.task_id
FROM dbo.Mobitech_Filtered_Task t
WHERE (t.PIMS_IMPORTED IS NULL OR t.PIMS_IMPORTED = 0)
AND t.completed = 1
AND...
^{“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

September 5, 2013 at 2:28 am

#1647613
RE: Stored procedure to run results of view

Which I think is the same as this:
INSERT INTO Mobitech_Filtered_Task_Step (
task_step_id,task_id,task_step_type_Id, display_order,int_data, varchar_data,image_data,bit_data,decimal_data,
float_data,lat_data,lon_data,notes,completed,deleted,date_added, server_date_modified,added_by,modified_by,
meter_formula,number_of_dials,additional_info,web_date_modified,mobile_date_modified)
SELECT
task_step_id,task_id,task_step_type_Id, display_order,int_data, varchar_data,image_data,bit_data,decimal_data,
float_data,lat_data,lon_data,notes,completed,deleted,date_added, server_date_modified,added_by,modified_by,
meter_formula,number_of_dials,additional_info,web_date_modified,mobile_date_modified
FROM [23.23.209.111].[mobitechGeneric_qa].[dbo].[filtered_task_step] f
WHERE f.task_id IN (
SELECT t.task_id
FROM dbo.Mobitech_Filtered_Task t...
^{“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

September 5, 2013 at 2:01 am

#1647606
RE: Stored procedure to run results of view

I reckon, what you are looking for is this:
INSERT INTO Mobitech_Filtered_Task_Step (
task_step_id,task_id,task_step_type_Id, display_order,int_data, varchar_data,image_data,bit_data,decimal_data,
float_data,lat_data,lon_data,notes,completed,deleted,date_added, server_date_modified,added_by,modified_by,
meter_formula,number_of_dials,additional_info,web_date_modified,mobile_date_modified)
SELECT
task_step_id,task_id,task_step_type_Id, display_order,int_data, varchar_data,image_data,bit_data,decimal_data,
float_data,lat_data,lon_data,notes,completed,deleted,date_added,server_date_modified,added_by,modified_by,
meter_formula,number_of_dials,additional_info,web_date_modified,mobile_date_modified
FROM [23.23.209.111].[mobitechGeneric_qa].[dbo].[filtered_task_step]
WHERE task_id IN (
SELECT t.task_id
FROM dbo.Mobitech_Filtered_Task t
LEFT...
^{“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

September 5, 2013 at 1:54 am

#1647604
RE: Recalculate "Amount" Column

Almost clear enough to make a stab at it...could you post the expected result set, or, alternatively, add an extra column say 'NewAmount' to your sample data which contains the...
^{“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

September 5, 2013 at 1:31 am

#1647594
RE: Create table of unique alpha numbers to use as sku's

How are your products uniquely identified, Greg?
^{“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

September 5, 2013 at 1:25 am

#1647590
RE: Are the posted questions getting worse?

Sean Lange (9/4/2013)
Well this post is my 10,000th point. I guess I have proved I too can waste lots of time answering forums posts instead of doing my actual job....
^{“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

September 5, 2013 at 1:15 am

#1647586
RE: Need a Query where with case statement

Sometimes it's easier to work with the expression in an APPLY block, dropping the results down to the WHERE clause like this:
SELECT e.*,
x.Datefilter,
x.LookupFilter
FROM tbl e
INNER JOIN LKUP...
^{“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

September 4, 2013 at 9:16 am

#1647368
RE: TSQL QUERY HELP

PearlJammer1 (9/4/2013)
Yeah sorry about the messy query results. I did preview it and it looked ok but when i posted it got out of line.
Incidentally how do you copy 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

September 4, 2013 at 8:53 am

#1647350
RE: TSQL QUERY HELP

PearlJammer1 (9/4/2013)
Thanks for the quick reply, but its now back to showing 0% for rows that have some data and 100% for the field that is fully populated.
Example there are...
^{“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

September 4, 2013 at 8:44 am

#1647343
RE: TSQL QUERY HELP

sturner (9/4/2013)
Try it like this:
select coll,
count(case when coll is not null and value is not null then 1 else 0 end) CntCol,
count(case when coll is not...
^{“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

September 4, 2013 at 8:38 am

#1647340
RE: How do I convert column data into row data?

PiMané (9/4/2013)
ChrisM@Work (9/4/2013)
Or CROSS APPLY VALUES:
-- sample data
;WITH gennum (tollnum, n1, n2, n3) AS (
SELECT 800123, 1234, 1235, 1236 UNION ALL
SELECT 999123, 9876, 9875, 9874
)
-- solution
SELECT g.tollnum, d.code
FROM gennum...
^{“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

September 4, 2013 at 8:06 am

#1647327
RE: How do I convert column data into row data?

Or CROSS APPLY VALUES:
-- sample data
;WITH gennum (tollnum, n1, n2, n3) AS (
SELECT 800123, 1234, 1235, 1236 UNION ALL
SELECT 999123, 9876, 9875, 9874
)
-- solution
SELECT g.tollnum, d.code
FROM gennum g
CROSS APPLY...
^{“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

September 4, 2013 at 7:46 am

#1647309

Viewing 15 posts - 4,021 through 4,035 (of 10,143 total)