ChrisM@Work

Forum Replies Created

Viewing 15 posts - 4,501 through 4,515 (of 10,143 total)

RE: Inserting result of multiple query into a row in a table

SELECT
transactiondate = ISNULL(a.transactiondate,b.transactiondate),
a.TotalwebSales,
b.TotalInHouseSales
FROM (
select transactiondate, count(distinct transactionid) as TotalwebSales
from saletransactions
where transactionstationnumber = web -- ?
and transactiondate = <today> -- ?
group by transactiondate
) a
FULL OUTER JOIN (
select transactiondate,...
^{“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

June 13, 2013 at 9:54 am

#1623789
RE: Generate N sequential numbers (fast)

jw.lankhaar (6/13/2013)
For a limited number of rows (< 100) using a recursive common table expression is an alternative:
DECLARE @lowerbound int
DECLARE @increment int
DECLARE @upperbound int
SET @lowerbound = -5
SET @upperbound...
^{“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

June 13, 2013 at 9:02 am

#1623759
RE: If one of two values exists they take priority over other values, otherwise select random

.Netter (6/13/2013)
Hi,
Im still confused on why negative numbers are used because from my eyes when u look at
WHEN Ordertype = 3 THEN -2
WHEN Ordertype = 1 THEN -1
if its 3...
^{“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

June 13, 2013 at 6:50 am

#1623688
RE: If one of two values exists they take priority over other values, otherwise select random

You're welcome - thanks for the feedback 🙂
^{“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

June 13, 2013 at 6:34 am

#1623673
RE: If one of two values exists they take priority over other values, otherwise select random

select top(1) oh.OrderType
from OrderHandsets oh
where oh.OrderId = co.OrderId
ORDER BY CASE
WHEN Ordertype = 3 THEN -2
WHEN Ordertype = 1 THEN -1
ELSE oh.OrderType END
^{“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

June 13, 2013 at 6:31 am

#1623668
RE: Separate one column in many columns

-- sample data
;WITH students (ID_Student, Name, rate) AS (
SELECT 1, CAST('Chipper Jones' AS nchar(30)), CAST('678*9' AS nchar(5)) UNION ALL
SELECT 2, 'Mike Piazaa', '98***' UNION ALL
SELECT 3, 'Barry Bonds', '678**' UNION...
^{“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

June 13, 2013 at 3:06 am

#1623604
RE: The Cascading (CROSS) APPLY

Amy.G (6/12/2013)
Chris, You've probably forgotten all about this, but after some other projects finished, I finally got back to this. I ran into a problem I hadn't thought of. There...
^{“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

June 13, 2013 at 1:05 am

#1623578
RE: using case in where clause having other conditions

ssurekha2000 (6/12/2013)
the whole sp cannot be posted as there are many joins
to get the result & check i prepared 3 different SPs with conditions as stated above
SP1 contains...
^{“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

June 13, 2013 at 1:00 am

#1623575
RE: Identify postcode from multiple fields

Here's a pure tsql way which performs reasonably well:
SELECT *
FROM (
SELECT Postcode = 'BS16 1EJ' UNION ALL
SELECT 'OX8 1TP' UNION ALL
SELECT 'GL20 8LZ' UNION ALL
SELECT 'JUNK'
) d
CROSS APPLY (...
^{“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

June 12, 2013 at 9:38 am

#1623405
RE: Converting a Non Standard Format Number to a Date

SELECT
CYYMMDD,
SQLDate1 = CONVERT(DATE,STUFF(CYYMMDD,1,1,CASE WHEN LEFT(CYYMMDD,1) = '0' THEN '19' ELSE '20' END),112),
SQLDate2 = cast(cast(cast(CYYMMDD as int) + 19000000 as char(8)) as date),
SQLDate3 = CONVERT(DATE,CAST('1130531' + 19000000 AS CHAR(8)),112),
SQLDate4 =...
^{“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

June 12, 2013 at 9:22 am

#1623395
RE: Converting a Non Standard Format Number to a Date

SELECT
CYYMMDD,
SQLDateTime = CONVERT(DATETIME,STUFF(CYYMMDD,1,1,CASE WHEN LEFT(CYYMMDD,1) = '0' THEN '19' ELSE '20' END),112)
FROM (SELECT CYYMMDD = '1130531') t
^{“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

June 12, 2013 at 8:37 am

#1623374
RE: select not working

Krishna1 (6/10/2013)
Dear All
In procedure i am using Remark table to capture all the steps executed (instead of print). From another session when I try to
select * from...
^{“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

June 12, 2013 at 5:52 am

#1623290
RE: can any one give me the answer of my query why I am getting the error message

The table's two constraints cannot have the same name. Change one of them.
^{“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

June 12, 2013 at 5:05 am

#1623278
RE: Select in multiple levels...

Your query looks fine to me apart from the syntax errors. SQL Server is quite adept at optimising out query nesting.
SELECT
DistinctCompanyCount,
DenseGroup, CatName, CatId, CompanyId, CompanyName, PropertyId,
Status, Day,...
^{“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

June 12, 2013 at 5:02 am

#1623277
RE: Error on Subquery

@nitha jen
Please start a new thread for your issue. Hijacking other folks threads causes confusion. 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

June 12, 2013 at 4:32 am

#1623269

Viewing 15 posts - 4,501 through 4,515 (of 10,143 total)