ChrisM@Work

Forum Replies Created

Viewing 15 posts - 2,896 through 2,910 (of 10,143 total)

RE: issue with datetime

spectra (9/26/2014)
>>>>Remove the CONVERT and you'll be doing the date range check you want.
how do you convert 26/09/2014 to Date ?
http://www.sqlservercentral.com/Forums/FindPost1620228.aspx
^{“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, 2014 at 8:04 am

#1748510
RE: issue with datetime

BWFC (9/26/2014)
create table #Test
(
CreatedOn datetime
,ID int identity(1,1)
)
insert into #test
select '2014-09-26 14:25:51.310' union all
select '2014-09-26 12:46:42.647' union all
select '2014-09-24 14:49:35.190' union all
select '2014-09-24 12:39:21.150' union all
select '2014-09-19 15:22:36.233' union all
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

September 26, 2014 at 7:47 am

#1748504
RE: issue with datetime

BWFC (9/26/2014)
What exactly is the problem? You need to be very careful using BETWEEN with datetime. You're much better using >= and <=.
Looks ok to me:
-- Non-SARGable
;WITH MyTable...
^{“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, 2014 at 7:27 am

#1748488
RE: Looking for the advisable sql sentence

;WITH Sampledata AS (
SELECT Incident = 162002982, ID = 9 UNION ALL
SELECT 162002982, 11 UNION ALL
SELECT 162002982, 11 UNION ALL
SELECT 162002982, 11 UNION ALL
SELECT 162002982, 11 UNION ALL
SELECT 162002982, 11...
^{“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, 2014 at 4:35 am

#1748428
RE: Query to split difference (number of nights) between 2 dates into respective "Month" column

hoolash (9/26/2014)
Hi Eirukur,
I'm afraid it's still not working for me. I'm sure I'm doing something wrong with the syntax! Grateful if you could have a look and point out...
^{“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, 2014 at 2:14 am

#1748395
RE: Need to Join two tables based on Nearest Date

Gosh, you made me blush :blush:
^{“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 25, 2014 at 9:56 am

#1748245
RE: Need to Join two tables based on Nearest Date

I'm guessing something like this:
SELECT t.*, x.*
FROM @TRANS t
CROSS APPLY (
SELECT TOP 1 c.D_Costing, c.M_Totl_Cost
FROM @COST c
WHERE c.I_Matl = t.I_Matl
AND c.I_Plant = t.I_Plant
AND c.D_Costing <...
^{“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 25, 2014 at 9:28 am

#1748230
RE: Need to Join two tables based on Nearest Date

What output would you like to see, Shannon?
^{“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 25, 2014 at 9:15 am

#1748224
RE: Need to Join two tables based on Nearest Date

Thanks. There are some excellent hints to make sample data generation quick and easy in this article[/url].
^{“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 25, 2014 at 8:30 am

#1748190
RE: Need to Join two tables based on Nearest Date

shannon.proctor (9/25/2014)
Data from Cost Table - tbl_Stag_Cost
I_MatlI_PlantI_Cal_YrM_Totl_COGSM_Fixed_COGSM_Var_COGSI_CurrD_Costing
95583014120111261.19128.231132.96USD20110101
95583014120111276.85135.161141.69USD20110201
95583014120111305.64135.161170.48USD20111101
95583014120121196.07106.901089.17USD20120101
95583014120131153.2680.241073.02USD20130101
95583014120131153.2680.241073.02USD20130701
95583014120141162.6379.131083.50USD20140101
Data from Primary Transaction Table - tbl_Stag_Trans
I_Bill_NbrI_Bill_ItemD_BillI_MatlI_PlantI_Doc_CurrM_Spec_PriceI_Curr
0091032577420120104955830141USD4096.51USD
0091032565320120104955830141USD13971.27USD
0091017091120111021955830141USD4236.72USD
0091017399420111024955830141USD4096.51USD
0090966415420110228955830141USD2048.26USD
0090967913420110307955830141USD4096.51USD
0090967915320110307955830141USD4096.51USD
0090968213420110308955830141USD4126.99USD
0090968715420110310955830141USD3762.76USD
0090969390420110315955830141USD2048.26USD
0090969446420110315955830141USD4096.51USD
Thanks, but...you're looking for someone to turn this into a CREATE TABLE... plus INSERTs so they can code...
^{“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 25, 2014 at 8:22 am

#1748188
RE: Need to Join two tables based on Nearest Date

shannon.proctor (9/24/2014)
I have two tables where the key of one doesn't not perfectly match the primary table. I have three fields Material, Plant and Date in a Costing table....
^{“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 25, 2014 at 8:00 am

#1748168
RE: Ordering

I'm sure this has already been suggested - anyway, here it is again:
SELECT *
FROM @WorkStation
CROSS APPLY (
SELECT Thingy = CAST(
(SELECT [text()] = RIGHT('000'+Item,3)
FROM [dbo].[DelimitedSplit8K] (IPAddress,'.')
ORDER BY ItemNumber
FOR XML...
^{“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 25, 2014 at 7:44 am

#1748161
RE: Today's Random Word!

BWFC (9/25/2014)
Ed Wagner (9/25/2014)Terminator
Colon
Begininator
^{“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 25, 2014 at 5:51 am

#1748108
RE: SQL SERVER Comma Separated values XML PATH

Two main issues with the original: the date filter isn't SARGable whereas CAST(datetimecolumn AS DATE) is, and you're referencing the tally table twice. Try this. You may have to make...
^{“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 25, 2014 at 2:37 am

#1748062
RE: SORT cost in union query

Have you tried using appropriate date filters on each table?
Something like StorePurchaseDate >= @8.
Posting the actual execution plan of the query with the table insert part commented out might...
^{“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 24, 2014 at 6:49 am

#1747815

Viewing 15 posts - 2,896 through 2,910 (of 10,143 total)