ChrisM@Work

Forum Replies Created

Viewing 15 posts - 5,686 through 5,700 (of 10,143 total)

RE: Union Query with 'duplicates'

SELECT
f1, CountOfQ6_BRANCH = SUM(CountOfQ6_BRANCH)
FROM (
SELECT format(WEEK_DATE, "YYYYMM") as f1, Count(Q6_BRANCH) AS CountOfQ6_BRANCH
FROM TBL_AML_ERRORS
GROUP BY format(WEEK_DATE, "YYYYMM")
UNION ALL -- the keyword ALL is important if you don't want to eliminate...
^{“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 11, 2012 at 7:56 am

#1536253
RE: Error converting data type varchar to float

sqlnaive (9/11/2012)
I've a table with one column as varchar(50). This table is hugely populated. I want to alter this column to datatype float. While altering it gives me the following...
^{“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 11, 2012 at 7:31 am

#1536222
RE: Filling Buckets

Using the sample data provided, a simple calculation rCTE works fine with both positive and negative numbers.
DECLARE @AmountToAllocate INT = 21
;WITH Calculator AS (
SELECT
BucketID, TotalSize, Amount,
AmountLeftToAllocate = CASE...
^{“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 11, 2012 at 7:24 am

#1536217
RE: Conversion failed when converting character string to smalldatetime data type.

shani19831 (9/11/2012)
Eugene Elutin (9/11/2012)
One of the column you return in one of your "selects" (I think it's a first one
) is of smalldatetime data type (we don't know which column...
^{“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 11, 2012 at 3:47 am

#1536093
RE: Looping through table

jadelola (9/10/2012)
Hi,
Below is my revised table. Customers 1 and 2 will have 0 left but customer 3 will have 23 left has could only buy 27 units from him. 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

September 11, 2012 at 3:30 am

#1536082
RE: Performance of Joins over Updates

dwilliscp (9/10/2012)
Alexander Suprun (9/7/2012)
dwilliscp (9/7/2012)
To write new data using three outer joins or to use one outer join, and get most of the data, then use three updates to load...
^{“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 11, 2012 at 2:06 am

#1536040
RE: SSIS and replication - foxpro database - ideas?

jared 11265 (9/7/2012)
ChrisM@Work (9/7/2012)
Yvan Bouchard (9/7/2012)
How about just keeping the front-end part of the Foxpro application and have all the data in SQL Server? Lose the Foxpro database part? This...
^{“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 7, 2012 at 9:19 am

#1535128
RE: Check that a specific Group By condition does not exist

SELECT b.*
FROM #tblBlocks b
WHERE NOT EXISTS (
SELECT 1
FROM #tblBlocks bi
WHERE bi.BlockID = b.BlockID
AND bi.BaseStage = 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

September 7, 2012 at 9:15 am

#1535127
RE: SSIS and replication - foxpro database - ideas?

Yvan Bouchard (9/7/2012)
How about just keeping the front-end part of the Foxpro application and have all the data in SQL Server? Lose the Foxpro database part? This would require pointing...
^{“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 7, 2012 at 9:00 am

#1535113
RE: Performance of Joins over Updates

dwilliscp (9/7/2012)
Michael Valentine Jones (9/7/2012)
You haven't provided enough information for anyone to begin to answer that question.
You should just try each way to see which is faster.
The problem with...
^{“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 7, 2012 at 8:38 am

#1535090
RE: Weird Requirement... Multiple Left Joins? Am I missing something?

GSquared (9/7/2012)
Sorry for the delay in replying. Been recovering from surgery.
...
Get well soon, GGeezer.
^{“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 7, 2012 at 7:32 am

#1535059
RE: Coversion of below value and storing into int datatype failing

KumarSQLDBA (9/7/2012)
Hi ,
getting error while running this statement to store YYYYMM eg.201209 in database int column todatyearmonth.
eg,
convert(varchar(4),year([getdate()])) + convert(varchar(4),month([getdate()]),105) as todayyearmonth
Please help me how to proceed..
error:
Msg 241, Level 16,...
^{“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 7, 2012 at 7:27 am

#1535053
RE: Order by a column keeping the families together

CELKO (9/6/2012)
.. it doesn't mean that I don't know any basics of data modeling, RDBMS or Date fields [sic] in SQL. I believe this forum is not just for...
^{“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 7, 2012 at 7:22 am

#1535047
RE: Help Needed

DELETE aa
FROM aa
INNER JOIN bb
ON bb.ID = aa.ID
SET IDENTITY_INSERT aa ON
INSERT INTO aa (Id,Policynumber,date)
SELECT Id,Policynumber,date
FROM bb
SET IDENTITY_INSERT aa OFF
^{“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 7, 2012 at 6:43 am

#1535031
RE: Datatype truncation

sqlnaive (9/7/2012)
For such kind of reasons we should avoid float and real datatypes ? And rather use decimal and numeric ?
Have a look in BOL under CONVERT to see which...
^{“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 7, 2012 at 5:50 am

#1535014

Viewing 15 posts - 5,686 through 5,700 (of 10,143 total)