ChrisM@Work

Forum Replies Created

Viewing 15 posts - 5,761 through 5,775 (of 10,143 total)

RE: Select table names from queries

Eugene Elutin (8/31/2012)
dwain.c (8/31/2012)
Eugene Elutin (8/31/2012)
If you really like the challenge, this one for you:
draw Mona Lisa using T-SQL (I remember I had seen assembler application which would print out...
^{“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

August 31, 2012 at 6:52 am

#1532359
RE: Select table names from queries

Eugene Elutin (8/31/2012)
...draw Mona Lisa using T-SQL...
Okay.
SELECT *
FROM (
SELECT '......................' UNION ALL
SELECT '......................' UNION ALL
SELECT '......########........' UNION ALL
SELECT '.....##...#.###.......' UNION ALL
SELECT '.....#..#...###.......' UNION ALL
SELECT '.....#..##..###.......' 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

August 31, 2012 at 6:15 am

#1532339
RE: Loop Count in Select Statement

renvilo (8/31/2012)
...So in short, how do I count how many shift there was from breaking time to fixing time with my #Final temp table and my Shifts table?...
Start by setting...
^{“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

August 31, 2012 at 5:58 am

#1532327
RE: Need help with SQL Query

denis.gendera (8/31/2012)
I've got following query
select machine_id, MONTHS =
CASE
WHEN DATEDIFF("m", firstseen, getdate()) = 0 then 'AUG'
...
^{“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

August 31, 2012 at 5:43 am

#1532321
RE: Count aggregate results per day via SQL

Here's another way:
;WITH Locations AS (
SELECT
DATEADD(dd, 0, DATEDIFF(dd, 0, createdate)) AS createdate,
COUNT(DATEADD(dd, 0, DATEDIFF(dd, 0, createdate))) AS totalcount,
sum(case channel when 'website' then 1 else 0 end) as WebsiteViews,
sum(case channel...
^{“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

August 31, 2012 at 4:10 am

#1532281
RE: Query in Having Clause

You will need to set up some sample data for this, there's a link in my sig to a great article which shows you how it's done.
Without knowing how...
^{“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

August 31, 2012 at 3:43 am

#1532271
RE: The data types varchar and varchar are incompatible in the modulo operator.

You don't appear to need dynamic sql for this:
SELECT
--app.id,
edit.project_name
FROM tracker app
INNER JOIN edit edit
ON app.id=edit.id
WHERE edit.project_name LIKE ('%' + REPLACE(@project_name,'*','%') + '%')
^{“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

August 31, 2012 at 3:14 am

#1532262
RE: how to avoid this error in this select query which i have mentioned?

sivajii (8/31/2012)
ti is providing output like this when i execute this proc
[1|cricket, 1| bowling&fielding, 2| batting] [2|football, 4| foul]
but the out put should be like...
^{“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

August 31, 2012 at 2:42 am

#1532257
RE: Update colum and use it in where-statment at same time

fredriksv80 (8/31/2012)
...I have an index on col. Is it bad to update a column used in the where clues?...
No, the entire set of rows matching the search predicates are identified...
^{“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

August 31, 2012 at 2:08 am

#1532249
RE: collation conflicts during merge with bulk insert

Check the collation of the target column - it may be different from the database default.
^{“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

August 30, 2012 at 9:44 am

#1531984
RE: Select table names from queries

RBarryYoung (8/30/2012)
Geez, I go to bed, get up, and there's twenty new posts!
...
Have you checked the date today Barry? 😛
^{“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

August 30, 2012 at 9:15 am

#1531973
RE: Select table names from queries

RBarryYoung (8/30/2012)
The real problem here though is that the OP never confirmed if the table names actually exist in the database. None of these three tricks will work if...
^{“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

August 30, 2012 at 9:09 am

#1531967
RE: Select table names from queries

dwain.c (8/30/2012)
ChrisM@Work (8/30/2012)
SELECT m.[Definition], d.TABLE_NAME FROM sys.sql_modules m OUTER APPLY ( SELECT TABLE_NAME FROM YourDatabase.INFORMATION_SCHEMA.TABLES t WHERE m.definition LIKE '%'+t.TABLE_NAME+'%' GROUP BY TABLE_NAME
) d
Edit: changed db name
A man of few words! I...
^{“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

August 30, 2012 at 8:15 am

#1531920
RE: Select table names from queries

SELECT
m.[Definition],
d.TABLE_NAME
FROM sys.sql_modules m
OUTER APPLY (
SELECT TABLE_NAME
FROM YourDatabase.INFORMATION_SCHEMA.TABLES t
WHERE m.definition LIKE '%'+t.TABLE_NAME+'%'
GROUP BY TABLE_NAME
) d
Edit: changed db name
^{“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

August 30, 2012 at 7:47 am

#1531900
RE: Select table names from queries

dwain.c (8/30/2012)
SQLHeap (8/30/2012)
Pretty clever dwain, thanks for your input!
Hey! Almost missed your thanks there! 😛 You're very welcome.
And don't listen to ChrisM@Work - he's just a grumpy...
^{“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

August 30, 2012 at 6:43 am

#1531840

Viewing 15 posts - 5,761 through 5,775 (of 10,143 total)