ChrisM@Work

Forum Replies Created

Viewing 15 posts - 4,126 through 4,140 (of 10,143 total)

RE: Update table values if corresponding values change in another table

Yes - have a read of this MS article.
^{“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 19, 2013 at 1:40 am

#1642569
RE: Delete datetime record

adonetok (8/16/2013)
This will delete entire row.
What I need is to delete data in [ORDERDATE] cell and keep bbb in [NAME] field.
An UPDATE then.
update mytable set date = NULL where 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 16, 2013 at 7:15 am

#1642156
RE: Delete datetime record

delete from mytable where name = somename and date = somedate.
^{“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 16, 2013 at 7:06 am

#1642150
RE: Update table values if corresponding values change in another table

pwalter83 (8/16/2013)
ChrisM@Work (7/24/2013)
pwalter83 (7/24/2013)
ChrisM@Work (7/24/2013)
Lowell has already asked but the question remains unanswered: why do you want to hold the same data in two places at once?
Hi,
The reason 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

August 16, 2013 at 6:59 am

#1642144
RE: Absent Problem

I think it should look more like this:
SELECT
w.eID, w.name,
al.[status],
COALESCE(al.CheckTime, c.[Date]) AS [Date]
,CASE WHEN al.CheckTime IS NULL THEN 'A' ELSE 'P' END AS Attendance
FROM dbo.employee AS w
CROSS JOIN...
^{“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 16, 2013 at 6:51 am

#1642136
RE: Absent Problem

immaduddinahmed (8/16/2013)
...
actulay this work is above my level thats why i am getting complexity 🙂
You're doing just fine - you've learned a huge amount in the time you've been posting...
^{“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 16, 2013 at 5:57 am

#1642110
RE: Absent Problem

My mistake, coding before thinking. Try this:
SELECT *
FROM Calendar c
CROSS JOIN employee e
LEFT JOIN attendlog a
ON a.eid = e.eid
AND a.CheckTIme = c.Date
WHERE c.[Date] >= CAST('20130801' AS DATETIME)
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

August 16, 2013 at 5:34 am

#1642098
RE: Absent Problem

immaduddinahmed (8/15/2013)
from attendlog a
left outer join employee e on a.eid = e.eid
If an employee has no attendance record for the selected time period then they will be excluded...
^{“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 16, 2013 at 4:39 am

#1642071
RE: Add Check Constraint in Two Columns

As Stefan has pointed out, your spec is full of errors and is very difficult to understand. What I suggest you do is to show with examples what you mean...
^{“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 16, 2013 at 4:14 am

#1642059
RE: Help Required!!

I'd use a CROSS APPLY cascade. This article [/url]has at least one example which is very similar to your requirements.
^{“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 16, 2013 at 3:23 am

#1642039
RE: Query Performance

Stefan_G (8/16/2013)
thava (8/15/2013)
...
This is a good idea - scan the large tables a single time.
To improve performance even more you should include a WHERE condition for GNLFSMID as well:
WHERE ...
^{“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 16, 2013 at 3:02 am

#1642033
RE: Query Performance

I'd guess that's way too complicated for the optimiser to deal with and you've got a plan which is far from ideal. Thava has the right idea - you should...
^{“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 16, 2013 at 1:31 am

#1642003
RE: Is there a better way to do this? SELECTs within a SELECT

The performance would be appalling. Consider Luis' approach, or even APPLY():
SELECT SalesPersonID, FirstName, LastName,
x.Address1,
x.Address2,
x.City,
...etc....
FROM dbo.SalesPersons
OUTER APPLY (
SELECT Address1, Address2, City
FROM dbo.Addresses
WHERE (LookUpID = 1)
AND (RecordTypeID = 3)
)...
^{“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 15, 2013 at 9:25 am

#1641848
RE: Help on inserting results into Table

;WITH src AS
(
SELECT
database_id, db_buffer_pages = COUNT_BIG(*)
FROM sys.dm_os_buffer_descriptors
--WHERE database_id BETWEEN 5 AND 32766
GROUP BY database_id
)
INSERT INTO MyNewTable ([db_name], db_buffer_pages, db_buffer_MB, db_buffer_percent)
SELECT
[db_name] = CASE [database_id] WHEN 32767
THEN 'Resource DB'
ELSE...
^{“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 15, 2013 at 9:19 am

#1641842
RE: Help on inserting results into Table

;WITH src AS
(
SELECT
database_id, db_buffer_pages = COUNT_BIG(*)
FROM sys.dm_os_buffer_descriptors
--WHERE database_id BETWEEN 5 AND 32766
GROUP BY database_id
)
SELECT
[db_name] = CASE [database_id] WHEN 32767
THEN 'Resource DB'
ELSE DB_NAME([database_id]) END,
db_buffer_pages,
db_buffer_MB = db_buffer_pages / 128,
db_buffer_percent...
^{“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 15, 2013 at 9:04 am

#1641836

Viewing 15 posts - 4,126 through 4,140 (of 10,143 total)