ChrisM@Work

Forum Replies Created

Viewing 15 posts - 1,936 through 1,950 (of 10,143 total)

RE: TREATMENT GAPS OF 2 OR MORE DAYS USING SERVICE DATES

DROP TABLE #MyBigTable
SELECT * INTO #MyBigTable
FROM (VALUES
('JOHN DOE',CAST('20150423' AS DATE),''),
('JOHN DOE','20150424',''),
('JOHN DOE','20150425',''),
('JOHN DOE','20150426','<- 1 EPISODE'),
('JOHN DOE','20150503','<- 1 EPISODE'),
('JOHN DOE','20150510','<- 1 EPISODE'),
('JOHN DOE','20150616',''),
('JOHN DOE','20150617',''),
('JOHN DOE','20150618','<- 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

November 5, 2015 at 2:11 am

#1837886
RE: UPDATE with OUTPUT CLAUSE

drew.allen (11/4/2015)
The OUTPUT clause is very limited. You can either output the data directly to the client application or you can output your data to a table/table variable. Period....
^{“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

November 4, 2015 at 8:30 am

#1837688
RE: Question/Advice needed Sliding Window partitioning range right,what the effecient way to implement/are staging tables needed

Resender (11/4/2015)
OK I'll try to summarise
The company I work for produces a certain type of machinery used from factories to common households around the world.
The database is where data being...
^{“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

November 4, 2015 at 7:27 am

#1837655
RE: CASE Assistance

mcoleman15902 (11/3/2015)
I want it to say, okay, here is a business class "L", now let's see if there are any other sub classes along with it being a large business....
^{“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

November 3, 2015 at 10:17 am

#1837419
RE: Are the posted questions getting worse?

GilaMonster (11/3/2015)
ChrisM@Work (11/3/2015)
Any folks out there with a little time to spare with this thread - it's just exceeded my comfort zone. Thanks.
No spare time this week, but given the...
^{“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

November 3, 2015 at 9:32 am

#1837410
RE: CASE Assistance

For each CASE, the column lists look the same to me.
(SELECT V.VEND_NAME_EXT
,LNHS.CST_AMT
,VCHR.VEND_ID
,V.CL_VET_FL
,V.CL_SD_VET_FL
,V.CL_ANC_IT_FL
,V.CL_DISADV_FL
,V.CL_WOM_OWN_FL
,V.CL_LAB_SRPL_FL
,V.CL_HIST_BL_CLG_FL
,V.CL_ANC_IT_FL
,VCHR.VCHR_KEY )
I'm lost now - you've gone to some length to tell us that they...
^{“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

November 3, 2015 at 9:32 am

#1837409
RE: CASE Assistance

mcoleman15902 (11/3/2015)
I think it needs to be separate columns for reporting purposes because it's going in SSRS. I tried using a case statement like so:
select case when V.S_CL_SM_BUS_CD = 'L'...
^{“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

November 3, 2015 at 8:51 am

#1837390
RE: Question/Advice needed Sliding Window partitioning range right,what the effecient way to implement/are staging tables needed

Resender (11/3/2015)
In the meantime 123883 tables (yes 30 new tables got added) the total recordcount is
89,372,968,620
ChrisM@Work (11/3/2015)and how is this lot typically interrogated?
I wish I knew but I'm frequently being...
^{“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

November 3, 2015 at 7:49 am

#1837368
RE: Are the posted questions getting worse?

Any folks out there with a little time to spare with this thread - it's just exceeded my comfort zone. Thanks.
^{“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

November 3, 2015 at 7:42 am

#1837366
RE: Question/Advice needed Sliding Window partitioning range right,what the effecient way to implement/are staging tables needed

ChrisM@Work (11/3/2015)
Resender (11/3/2015)
ChrisM@Work (11/3/2015)
Resender (11/3/2015)
Total number of machine tables (3/11/2015 13:44 CET) 123851
Those tables are the same
[MachineRegisterId] [int] NOT NULL,
[UtcTime] [datetime] NOT NULL,
[Value] [float] NOT NULL
With primary key 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

November 3, 2015 at 7:16 am

#1837357
RE: Question/Advice needed Sliding Window partitioning range right,what the effecient way to implement/are staging tables needed

Resender (11/3/2015)
ChrisM@Work (11/3/2015)
Resender (11/3/2015)
Total number of machine tables (3/11/2015 13:44 CET) 123851
Those tables are the same
[MachineRegisterId] [int] NOT NULL,
[UtcTime] [datetime] NOT NULL,
[Value] [float] NOT NULL
With primary key on the...
^{“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

November 3, 2015 at 7:15 am

#1837356
RE: Question/Advice needed Sliding Window partitioning range right,what the effecient way to implement/are staging tables needed

Resender (11/3/2015)
Total number of machine tables (3/11/2015 13:44 CET) 123851
Those tables are the same
[MachineRegisterId] [int] NOT NULL,
[UtcTime] [datetime] NOT NULL,
[Value] [float] NOT NULL
With primary key on the combination of...
^{“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

November 3, 2015 at 5:52 am

#1837331
RE: Question/Advice needed Sliding Window partitioning range right,what the effecient way to implement/are staging tables needed

Each machine gets it own table which is the warehouse name followed by sequence number that's unique for that machine.
How many of these tables are there in the db?
Are...
^{“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

November 3, 2015 at 5:12 am

#1837320
RE: Question/Advice needed Sliding Window partitioning range right,what the effecient way to implement/are staging tables needed

Resender (11/3/2015)
Before proceeding any further,
ChrisM@Work (10/30/2015)how many rows are in the table?
The largest table contains above 1.5 million records,the smallest about 16, and their hundreds on hundreds that are currently...
^{“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

November 3, 2015 at 4:01 am

#1837308
RE: CASE Assistance

mcoleman15902 (11/2/2015)
Dynamic query may take too much time given my skill set. Could you provide one of the queries and I can work from that method?
Thanks, Sir.
MC
Actually, I was...
^{“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

November 2, 2015 at 9:56 am

#1837143

Viewing 15 posts - 1,936 through 1,950 (of 10,143 total)