ChrisM@Work

Forum Replies Created

Viewing 15 posts - 3,841 through 3,855 (of 10,143 total)

RE: Problem with a query

Wayne, it may look like your query is close, but I'd still recommend starting again. Here's a good point from which to start:
DECLARE @School int, @Term int, @Score varchar(2), @Task...
^{“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

December 30, 2013 at 9:44 am

#1676421
RE: Running total in recursion

You're welcome. Any issues, questions?
^{“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

December 30, 2013 at 8:53 am

#1676400
RE: T-SQL Help

PSB (12/30/2013)
Got it. Thanks 🙂
Top job! Anytime 🙂
^{“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

December 30, 2013 at 7:38 am

#1676380
RE: T-SQL Help

Sure, pretty straightforward - use ROW_NUMBER() as for Scenario 2. Can you give it a shot?
^{“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

December 30, 2013 at 7:27 am

#1676375
RE: T-SQL Help

DROP TABLE #NewTempTable
CREATE TABLE #NewTempTable
(
StartTime DATETIME,
EndTime DATETIME,
ServerName VARCHAR(10),
Databasename VARCHAR(10),
STATUS VARCHAR(10)
)
INSERT INTO #NewTempTable (StartTime, EndTime, ServerName, Databasename, [STATUS]) VALUES
( '2013-12-23 15:00:48.847' , '2013-12-23 15:00:48.852' , 'Server A' , 'DB 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

December 30, 2013 at 7:10 am

#1676371
RE: how to calculate no.of .months billed customer

raghuldrag (12/30/2013)
ChrisM@Work,
can't process ur code becz i ve more than 5 lakh data 's from year wise of '2007 to 2013' date wise bills
Then how are you - or we...
^{“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

December 30, 2013 at 5:35 am

#1676355
RE: how to calculate no.of .months billed customer

raghuldrag (12/30/2013)
here i can such example
create table estr
(
custo int,
product varchar(20),
val int,
billed_date datetime
)
insert into estr(custo,product,billed_date,value) values('10','Milk','2013-04-11','2000')
insert into estr(custo,product,billed_date,value) values('10','Milk','2013-04-21','500')
insert into estr(custo,product,billed_date,value) values('10','Milk','2013-05-05','100')
insert into estr(custo,product,billed_date,value) values('10','Milk','2013-05-11','2000')
insert into estr(custo,product,billed_date,value) values('10','fruits','2013-05-11','500')
insert into estr(custo,product,billed_date,value) values('30','fruits','2013-04-11','2000')
insert...
^{“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

December 30, 2013 at 5:17 am

#1676352
RE: Running total in recursion

;WITH rCTE (TaskID, BasedOn, Interval, TaskLevel, TotalInterval) AS (
SELECT TaskID, BasedOn, Interval,
1 AS TaskLevel,
TotalInterval = Interval
FROM #tblTasks
WHERE TaskID = 1
UNION ALL
SELECT e.TaskID, e.BasedOn, e.Interval,
c.TaskLevel + 1,
TotalInterval...
^{“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

December 30, 2013 at 5:16 am

#1676351
RE: how to calculate no.of .months billed customer

Lowell (12/27/2013)
I've got teh same answer ad Ed Wagner does, i just processed your post through a macro so i had some consumable data.
if you want solid help like that...
^{“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

December 30, 2013 at 5:04 am

#1676348
RE: Memories of 2013

Nottingham - being mistaken for one of the entertainment crew. Making holes in the lawn with Hugo K and hearing his MERGE talk. Hearing most of Grant Fritchey's talk 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

December 30, 2013 at 4:09 am

#1676341
RE: How yo make these two queires to one please

Here are a couple of potential solutions. They are only "potential" because there isn't any sample data to interrogate. I've knocked up a sample table to assist you and others....
^{“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

December 30, 2013 at 3:56 am

#1676335
RE: TSQL Help to run OpenQuery using vairables

DECLARE
@AcquisitionServerAlias sysname = 'AcquisitionServer3' ,
@AcquisitionDBName sysname = 'Mydb',
@begintime datetime = '2013-12-29 19:52:27.760',
@FromSQL nvarchar(300),
@FromParam nvarchar(50),
@ReturnFromLSNValue binary(10),
@fromLSN binary(10)
SET @FromSQL = 'SELECT @FromLSNOUT = fromLSN FROM OPENQUERY ('+@AcquisitionServerAlias+', ''SELECT '+@AcquisitionDBName+'.sys.fn_cdc_map_time_to_lsn('+@begintime+')as fromLSN'')'
PRINT @FromSQL
'Msg...
^{“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

December 30, 2013 at 3:02 am

#1676330
RE: Fetching value of previous record

saum70 (12/30/2013)
Hi all,
Thanks for your replies. Yes Group by was missing but it was not the concern. Anyways, i figured out and got the performance. I used clustered index on...
^{“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

December 30, 2013 at 2:48 am

#1676328
RE: Are the posted questions getting worse?

GilaMonster (12/30/2013)
Ok, after another frustrating thread of 'why do you want to remove that operator from the query, is it causing problems', the next article on the to-do list (after...
^{“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

December 30, 2013 at 2:39 am

#1676326
RE: Are the posted questions getting worse?

dwain.c (12/26/2013)
I hate to be the bearer of bad tidings during this holiday season, but this just came to my attention this morning.
Steven Willis, whom some of you may know...
^{“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

December 30, 2013 at 2:32 am

#1676325

Viewing 15 posts - 3,841 through 3,855 (of 10,143 total)