ChrisM@Work

Forum Replies Created

Viewing 15 posts - 4,876 through 4,890 (of 10,143 total)

RE: SQL /Procedure - for Transpose and Calculation

sudhirnune (4/9/2013)
Chris. I agree with your Query. My problem is that i need to make it Dynamic.
I can not use the Static way of implimenting this.
Can you please help me...
^{“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

April 9, 2013 at 6:44 am

#1604784
RE: SQL /Procedure - for Transpose and Calculation

sudhirnune (4/9/2013)
there are 3 formula....!
SAL = SAL-ADV
COMM = ALL_1+ALL_2-ALL_3
BONUS = SAL * 1.1
Did you test the query I posted above somewhere?
^{“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

April 9, 2013 at 6:36 am

#1604782
RE: auto foreign key generation

SDG1 (4/9/2013)
Ok, so I recreated the table defining the Project ID as UNIQUE, NOT NULL rather than as a primary key and everything works great now. There are no...
^{“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

April 9, 2013 at 6:12 am

#1604776
RE: Help with tuning stored proc

An index on #d2 supporting this join ([#d2].MARIMSSALESID) = ([DynamicsV5Realtime].[dbo].[SALESTABLE].MARIMSSALESID) would reduce the exaggerated estimated row counts, but that's besides the point. This lot is unlikely to run well unless...
^{“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

April 9, 2013 at 6:04 am

#1604775
RE: SQL /Procedure - for Transpose and Calculation

sudhirnune (4/9/2013)
Data in the Test Formula is
TGT_PAYMENT_TYPEPAYMENT_FORMULA
SAL ...
^{“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

April 9, 2013 at 5:42 am

#1604770
RE: SQL /Procedure - for Transpose and Calculation

vinu512 (4/9/2013)
...
Nicely Done Chris.
Thank you, Vinu. Insufficient information yet to determine whether or not it will be useful...
^{“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

April 9, 2013 at 5:24 am

#1604765
RE: SQL /Procedure - for Transpose and Calculation

How many rows does the table TEST_FORMULA contain?
Can you post a few rows please?
^{“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

April 9, 2013 at 5:22 am

#1604764
RE: split single row into four rows.

abhas (4/9/2013)
Hi Matak/Chris,
I want to use starttime and endtime as varchar. is it possible?
Thanks
Abhas.
Yes:
DECLARE @STARTDATE DATETIME, @StartTimeChar CHAR(5), @EndTimeChar CHAR(5)
SELECT
@STARTDATE = CAST(GETDATE() AS DATE),
@StartTimeChar = '08:00',
@EndTimeChar = '10:00'
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

April 9, 2013 at 4:29 am

#1604747
RE: SQL /Procedure - for Transpose and Calculation

sudhirnune (4/9/2013)
Yes they has SAL.
Thanks.
DROP TABLE #Sampledata
CREATE TABLE #Sampledata (NAME VARCHAR(25), PAYMENT_TYPE VARCHAR(10), PAYMENT DECIMAL(5,2))
INSERT INTO #Sampledata (NAME, PAYMENT_TYPE, PAYMENT)
SELECT 'SUDHIR', 'SAL', 30.3 UNION ALL
SELECT 'SUDHIR', 'ADV', 10.3 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

April 9, 2013 at 4:03 am

#1604740
RE: Write query for Suond like search

SSC has several related articles [/url]which may help. It's tricky and time consuming, and you may find it worthwhile purchasing a commercial application.
^{“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

April 9, 2013 at 2:30 am

#1604719
RE: how to convert the below subquery into join..single update statment with joins

surendiran.balasubramanian (4/9/2013)
I cant use CTE..
because the datawarehouse applicance wont support that....
all i need is single update statement with the required join condition
UPDATE c
SET CmDclnWdwnDt = s.DecWdrwnDt
FROM Claims...
^{“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

April 9, 2013 at 2:28 am

#1604717
RE: Checking to see if a time is between two times

greg.bull (4/9/2013)
Dear All,
Thanks for the posts - this is an old one that I solved a while back (just using case statements as I recall). Didn't expect any further postings...
^{“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

April 9, 2013 at 2:13 am

#1604715
RE: SQL /Procedure - for Transpose and Calculation

I understand that you have multiple payment types, and an employee may have several rows with different payment types.
Do ALL employees have at least one row with payment type...
^{“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

April 9, 2013 at 1:55 am

#1604711
RE: how to convert the below subquery into join..single update statment with joins

DECLARE @DecWdrwn as TABLE(CtryId smallint, CmId int, DecWdrwnDt int);
WITH s AS
(
SELECT
c.Ctryid, c.CmId,
-- Dt, not used
MaxDec = ISNULL(MAX(c.CmHistDtTmId),0),
x.MaxSet
FROM ClaimHistory c
OUTER APPLY (
SELECT MaxSet = ISNULL(MAX(l.CmHistDtTmId),0)
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

April 9, 2013 at 1:52 am

#1604710
RE: SQL /Procedure - for Transpose and Calculation

Is there one payment type which is common to all employees? For instance, 'SAL'? This could be very easy ...
^{“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

April 9, 2013 at 1:38 am

#1604704

Viewing 15 posts - 4,876 through 4,890 (of 10,143 total)