ChrisM@Work

Forum Replies Created

Viewing 15 posts - 3,121 through 3,135 (of 10,143 total)

RE: Partition and order the records

ramrajan (7/18/2014)
And her is how i want to get the output. Based on the sample data mentioned in my POst
ID ReadingDate SeqID
1 2014-07-18 07:48:30.200 1
1 2014-07-17 07:48:30.200 2
2 2014-07-17 07:48:30.200...
^{“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

July 18, 2014 at 8:48 am

#1730650
RE: calculate the sum of dates minus repetitive dates

pmadhavapeddi22 (7/18/2014)
Thank you J Livingston and David Burrows.
I appreciate your help and time
<<cough>>
----------------------------------------------------------------------------
JLS
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 8...
^{“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

July 18, 2014 at 8:41 am

#1730645
RE: Partition and order the records

ramrajan (7/18/2014)
Please someone help me
Can you provide Ed with the information he needs to complete your query for you?
^{“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

July 18, 2014 at 8:03 am

#1730619
RE: Partition and order the records

Use ROW_NUMBER(). Partition by ID, order by date descending, pick the first two rows.
Then check the date difference between row 2 and row 1 of each partition, using a CTE...
^{“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

July 18, 2014 at 7:13 am

#1730589
RE: Partition and order the records

Latest reading date
Current reading
Are they the same thing?
^{“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

July 18, 2014 at 7:07 am

#1730586
RE: Complex query - converting Excel to SQL Server query - would prefer a view

Try this for a performance lift:
;WITH Aggregates AS (
SELECT
caseid,
[total]= SUM(benefitpaid),
[unprocessed]= SUM(CASE WHEN unprocessed = 1 THEN benefitpaid ELSE 0 END),
[sentout]= SUM(CASE WHEN statusid = 2 THEN benefitpaid ELSE 0...
^{“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

July 18, 2014 at 6:11 am

#1730560
RE: calculate the sum of dates minus repetitive dates

This is a truly wild guess but seems to fit the sketchy description:
;WITH DedupedData AS (
SELECT tbl_id, cs_id, USERID, assgn_dtm, complet_dtm,
rn = ROW_NUMBER () OVER (PARTITION BY cs_id, USERID,...
^{“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

July 18, 2014 at 5:45 am

#1730555
RE: Exclude rows where value in column not found in another row

DROP TABLE #Temp
CREATE TABLE #Temp (KEY1 INT, Key2 INT, [Type] VARCHAR(5))
INSERT INTO #Temp VALUES
(1,NULL, 'TypeA'),
(2,5, 'TypeA'),
(3,1, 'TypeB'),
(4,NULL, 'TypeA'),
(5,NULL, 'TypeB'),
(6,26, 'TypeC'),
(7,NULL, 'TypeD'),
(8,NULL, 'TypeD')
SELECT *
FROM #Temp ot
WHERE NOT EXISTS (
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

July 18, 2014 at 3:58 am

#1730531
RE: Ranking rows on basis of SUM of records

rhd110 (7/17/2014)
Hi Techies any reply for this.
Yep, but haven't heard anything since.
^{“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

July 18, 2014 at 2:22 am

#1730516
RE: T-SQL: Cumulative SUM

John Roy Ellis (7/17/2014)
That article offers very little, in terms of anything along the lines of examples or knowledge transfer.
I await another response.
Thank you.
John
Here's another response: read the article then...
^{“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

July 18, 2014 at 2:18 am

#1730515
RE: Duplicate record count

pilla.sree85 (7/17/2014)
Till now i am good.then how would i check the condition duplicate count >1 then i need to execute query for further processing in order to display output ...
^{“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

July 17, 2014 at 7:34 am

#1730242
RE: Ranking rows on basis of SUM of records

rhd110 (7/17/2014)
Hi Techies any reply for this.
DROP TABLE #Temp
CREATE TABLE #Temp (ID int, [RowCount] int)
INSERT INTO #Temp (ID, [RowCount]) VALUES
(1, 448),(2, 267),(3, 297),(4, 216),(5, 405),(6, 254),(7, 107),(8, 102),(9,...
^{“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

July 17, 2014 at 7:28 am

#1730239
RE: Duplicate record count

-- Next, check to see if table ReceivedFiles is required in the query,
-- perhaps you can get away with checking to see if there is a value or 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

July 17, 2014 at 6:21 am

#1730223
RE: Duplicate record count

-- Next, a little reformatting. Note that I've changed the expression for totalcount,
-- to indicate that you are counting rows. Aggregate functions COUNT, SUM, AVG etc ignore
-- rows 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

July 17, 2014 at 6:10 am

#1730219
RE: Duplicate record count

-- Next I've commented out the join to Legacy L2.
-- It appears to be redundant for this purpose, but
-- will add to the cost of running the query, i.e. 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

July 17, 2014 at 5:57 am

#1730214

Viewing 15 posts - 3,121 through 3,135 (of 10,143 total)