ChrisM@Work

Forum Replies Created

Viewing 15 posts - 4,006 through 4,020 (of 10,143 total)

RE: concatenate with leading zeros

dwain.c (9/9/2013)
Jeff Moden (9/9/2013)
PRINT '========== Another Integer Math Method =========='
SET STATISTICS TIME ON;
SELECT @BitBucket = RIGHT(1000000000 + C1*1000 + C2, 9)
FROM #TestTable;
SET STATISTICS TIME...
^{“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 10, 2013 at 1:26 am

#1648734
RE: Recalculate Wages (Reposted for SQL 2008 with additional columns)

I'm sure we can work it out if you can knock up a suitable sample data set. The problem you are facing is that the data you have is already...
^{“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 10, 2013 at 1:11 am

#1648729
RE: Best way to do this kind of group by

Mark-101232 (9/9/2013)
Probably not the best way, but seems to work
SELECT DISTINCT
Grp_A, Grp_B, Grp_C,
SUM(Val_A) OVER(PARTITION BY Grp_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

September 9, 2013 at 6:46 am

#1648516
RE: A Hazard of Using the SQL Update Statement

Jeff Moden (9/9/2013)
ChrisM@Work (9/9/2013)
Prior to the introduction of MERGE I'd have agreed with this - we learned to ensure that multiple source rows for a target row had the same...
^{“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 9, 2013 at 6:19 am

#1648509
RE: Recalculate Wages (Reposted for SQL 2008 with additional columns)

Try this - but beware, some of your original data is incorrect:
SELECT
EmpID, EarningCode, PayPerPeriod, EQHourlyRate, HoursWorked, Amount, Dept,
nRate = d.NewRate, nAmount = d.NewAmount,
NewRate = CAST(ROUND(sw.NewAmount/HoursWorked,4) AS DECIMAL(10,4)),
NewAmount = CAST(ROUND(ISNULL(sw.NewAmount,d.Amount),2,0)...
^{“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 9, 2013 at 2:18 am

#1648462
RE: Query error

opc.three (9/9/2013)
SELECT FORMAT(GETDATE(), 'MM/dd/yyyy HH:mm:ss')
AS [try the FORMAT function available in SQL 2012 and above];
Thanks Orlando, I'll have a play with this at home...
^{“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 9, 2013 at 1:30 am

#1648451
RE: A Hazard of Using the SQL Update Statement

natalie.ignatieva (9/9/2013)
Jeff Moden (9/8/2013)
I, however, do not consider the fact that it won’t give you an error for an attempted multiple-update on a single row a fault. Nay. ...
^{“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 9, 2013 at 1:28 am

#1648450
RE: Strange query gives strange results

odepriester (9/6/2013)
GilaMonster (9/6/2013)
The filter is on the join and it's an outer join. So the filter is done before the join then the left join joins in the matching rows,...
^{“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 6, 2013 at 7:32 am

#1648072
RE: Strange query gives strange results

odepriester (9/6/2013)
Thanks for answers but the fact is that I don't need this "o.type = 'abcd'" and if I had done this query by myself I would never have written...
^{“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 6, 2013 at 7:31 am

#1648069
RE: get value from a function in a Stored Procedure

Are you sure you need a function? I think you need a little nudge in the right direction with APPLY:
SELECT
OFC.CompanyID,
PRAD.ProjectID,
PRAD.ProposalID,
P.ProposalNo,
P.ProposalName,
PRAD.DateAdded,
x.TSP,
x.QSP,
DiscountPercent = x.TSP + x.QSP
FROM ProposalRequestApprovalDetails PRAD
INNER JOIN ProcessProposalApprovalDetailsCLC PDC
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

September 6, 2013 at 6:14 am

#1648030
RE: Query help to show a column

This link might help.
In your first query, the left join to ActivitiesAdditionalFieldsValues AS TAFV is converted into an inner join when CboValues TCBOV is inner joined to it.
Changing 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 6, 2013 at 6:09 am

#1648028
RE: Strange query gives strange results

It's a nice little setup for augmenting Gail's fine explanation with some tables. Is id_object the PK of the object table, by any chance?
^{“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 6, 2013 at 5:27 am

#1648017
RE: Recalculate Wages (Reposted for SQL 2008 with additional columns)

nfpacct (9/5/2013)
Chris,
I added 2 new columns - NewAmount and NewRate (The newamount is based on the calculated newrate)
The desired result is the existing Amount Column values to be replaced...
^{“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 6, 2013 at 1:58 am

#1647942
RE: Recalculate Wages (Reposted for SQL 2008 with additional columns)

ChrisM@Work (9/5/2013)
Almost clear enough to make a stab at it...could you post the expected result set, or, alternatively, add an extra column say 'NewAmount' to your sample data which 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

September 5, 2013 at 9:23 am

#1647766
RE: Recalculate "Amount" Column

Sean Lange (9/5/2013)
This thread has now moved to a new one...not sure why.
http://www.sqlservercentral.com/Forums/Topic1491832-1292-1.aspx
Thanks Sean - I'll post the same question there. Cheers.
^{“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 5, 2013 at 9:23 am

#1647765

Viewing 15 posts - 4,006 through 4,020 (of 10,143 total)