ChrisM@Work

Forum Replies Created

Viewing 15 posts - 6,631 through 6,645 (of 10,143 total)

RE: goup by on sub query

p.stevens76 (10/3/2011)
No, it's an addition. trying to work through this in stages. This is the last stage.
Sorry for the confusion.
The two queries (subqueries) look the same to me:
select...
^{“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

October 3, 2011 at 8:39 am

#1389573
RE: goup by on sub query

p.stevens76 (10/3/2011)
select COUNT(*) as count_to_be_repeated_for_each, Dept, year(Arrival),month(Arrival)
from dbo.PS_AE_Delete
where (CONVERT(date, arrival, 101) >= '2011-02-01 00:00')
AND (CONVERT(date, arrival, 101) <= '2011-04-30 23:59')
and [Att_Type] = 'New Attender'
group by Dept, year(Arrival),month(Arrival)...
^{“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

October 3, 2011 at 8:25 am

#1389554
RE: goup by on sub query

Add this to your SELECT list:
Another_New_attender_Count = COUNT(*) OVER (PARTITION BY R.Dept, YEAR(Arrival), MONTH(Arrival))
^{“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

October 3, 2011 at 7:53 am

#1389523
RE: How to find the nth record based on specific condition

:blush: shucks it's just a copy of yours Luca!
^{“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

October 3, 2011 at 7:12 am

#1389491
RE: goup by on sub query

Why start a new thread? Try this:
SELECT
New_attender_Count = COUNT(*) OVER (PARTITION BY R.Dept),
--New_attender_Count = (
--select COUNT(*)
--from dbo.PS_AE_Delete
--where (CONVERT(date, arrival, 101) >= '2011-02-01 00:00')
--AND (CONVERT(date, arrival, 101) <=...
^{“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

October 3, 2011 at 5:37 am

#1389425
RE: How to find the nth record based on specific condition

Here's a slightly different method. BTW check the date in your first insert.
;WITH FilteredData AS (
SELECT o.MeterNumber, o.SlotTimeStamp, o.FeederStatus,
seq = ROW_NUMBER() OVER(ORDER BY o.SlotTimeStamp)
FROM (
SELECT d.MeterNumber, d.SlotTimeStamp, d.FeederStatus,...
^{“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

October 3, 2011 at 5:03 am

#1389407
RE: query without using parameter

SELECT MyResult = 0 |CONVERT(INT, RoleID) FROM UsersRoles WHERE UserID = 23
^{“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

October 3, 2011 at 4:09 am

#1389381
RE: Calculating Moving Averages with T-SQL

Sorry, been busy
-- set up some sample data
DROP TABLE #ROCdata
CREATE TABLE #ROCdata (ID INT NOT NULL, Symbol_Code VARCHAR(12), Transaction_date DATE, Close_Price MONEY)
INSERT INTO #ROCdata (ID, Symbol_Code, Transaction_date, Close_Price)
SELECT 1,...
^{“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 27, 2011 at 9:20 am

#1387243
RE: case statement

SELECT K.[test],
CASE
WHEN K.[test] = 'N/A' THEN '0'
WHEN CAST(K.[test] AS DECIMAL(5,1)) BETWEEN 0 AND 19.9 THEN '1'
WHEN CAST(K.[test] AS DECIMAL(5,1)) BETWEEN 20 AND 34.9 THEN '2'
WHEN CAST(K.[test] AS DECIMAL(5,1))...
^{“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 26, 2011 at 10:27 am

#1386648
RE: Calculating Moving Averages with T-SQL

The arithmetic doesn't work as it stands:
--([Close price on 12 day] -[Close price on Thirteenths day(today)] )/ ([Close price on Thirteenths day(today)] *100)
-- = -3.85 [today''s close]
SELECT (10782.95 -...
^{“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 26, 2011 at 10:10 am

#1386635
RE: grouping issue (I think?)

Does this give the same results as your query?
SELECT
[New_attender_Count] = 0, -- maybe COUNT(*) OVER(PARTITION BY something),
F.pat_pid as Patient_ID,
F.Arrival as F_Att_Date,
F.Dept as F_Att_Site,
F.atd_id as F_Att_ID,
F.atd_num as F_Att_Num,
F.Att_Seq_No as...
^{“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 26, 2011 at 10:00 am

#1386629
RE: Calculating Moving Averages with T-SQL

sushilb
Start a new thread.
Write a concise but complete and accurate description of what you want to do. Code which works can be a useful aid to understanding requirements, code which...
^{“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 26, 2011 at 9:18 am

#1386580
RE: Decrementing the rows fro every insertion

USE tempdb
GO
IF OBJECT_ID('tempdb..Test') IS NOT NULL DROP TABLE Test
CREATE TABLE Test (JurisID CHAR(4), CodeID CHAR(4), CodeIDDescr CHAR(4), SrcCodeDescr CHAR(4), PnxCodeId INT IDENTITY (1,1), PnxCodeValue AS (1000-PnxCodeId), IsConverted BIT)
INSERT INTO Test...
^{“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 26, 2011 at 7:24 am

#1386473
RE: Query optimizing 2

Rokh (9/22/2011)
No I didn't.
Like stated before, the inner join is not be removed. It represents a real life situation. I've only skinned it down for this forum. Call it respect...
^{“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 26, 2011 at 5:30 am

#1386387
RE: Alternate rows

wburnett_1 (9/13/2011)
Understood, that actually works better for this case, in that it should be more like drawing names from a hat...
Look up NTILE in BOL, this is what it's...
^{“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 13, 2011 at 10:03 am

#1380778

Viewing 15 posts - 6,631 through 6,645 (of 10,143 total)