ChrisM@Work

Forum Replies Created

Viewing 15 posts - 6,076 through 6,090 (of 10,143 total)

RE: Msg 102, Level 15, State 1, Line 2 Incorrect syntax near '‘'.

najeeb_kt2000 (7/10/2012)
This Sql give error
Msg 102, Level 15, State 1, Line 2 ,Incorrect syntax near '‘'.
why this problem.
Please respond to Gail's post before we continue with correcting the syntax...
^{“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

July 10, 2012 at 4:44 am

#1510806
RE: Msg 102, Level 15, State 1, Line 2 Incorrect syntax near '‘'.

The problem is with the single-quoted parameters. Use PRINT @sqlcommand instead of EXEC(@sqlcommand) and examine the statement.
^{“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

July 10, 2012 at 4:05 am

#1510780
RE: Fill variable from cursor

eh.shams (7/10/2012)
i know it , but i need it value in cursor
Why do you need a cursor? Surely you just need the value in a variable?
SELECT @Variable = column 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

July 10, 2012 at 3:59 am

#1510777
RE: PIVOT/UNPIVOT Question

sqlzealot-81 (7/10/2012)
Hi ,
I have a req as follows:
My table structure is something like below:
TestIDDistinctionFirstSecond
10185%60%50%
10250%85%NULL
10350%60%85%
I want my result as following:
TestID50%60%85%
101SecondFirstDistinction
102DistinctionNULLFirst
103DistinctionFirstSecond
Appreciate your help.
DDL and DML?
CREATE TABLE #Sample (TestID ...
INSERT INTO #Sample ...
^{“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

July 10, 2012 at 3:38 am

#1510764
RE: Fill variable from cursor

Surely this is sufficient:
SELECT SUM(Avamel)
FROM V_Factor_Total4
WHERE cslPricingFactorRef = '90000119'
^{“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

July 10, 2012 at 3:12 am

#1510754
RE: Running Total

Jay
I don't see anything cumulative yet - the result sets you've posted are simple aggregates:
SELECT
Docket_Category,
[Count],
[Percentage] = CAST(100*[Count]/(Total*1.00) AS NUMERIC(4,2))
FROM (
SELECT
Docket_Category,
[Count] = COUNT(*),
Total = SUM(COUNT(*)) OVER(PARTITION...
^{“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

July 10, 2012 at 3:01 am

#1510748
RE: help with sql query

As long as you will never go beyond four or five columns, the following query works well. Otherwise Google delimitedsplit8k.
-- sample data
;WITH SampleTable AS (
SELECT Stringybit = 'part1 area 2...
^{“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

July 9, 2012 at 9:56 am

#1510419
RE: Running Total

jerome.morris (7/9/2012)
What query do you want me to run for the result you want to see
...
Make up the results. Use the sample data set you've posted as your source, run...
^{“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

July 9, 2012 at 9:24 am

#1510400
RE: Running Total

jerome.morris (7/9/2012)
This is what the end result I am after is but using Category
http://www.brighthub.com/office/project-management/articles/8708.aspx
The link may help you to help me as I cant explain it detailed enough
Jay
Ok, interesting...how...
^{“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

July 9, 2012 at 9:07 am

#1510389
RE: Running Total

Hi Jay, that's great, thanks. How about a table of expected output?
^{“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

July 9, 2012 at 8:52 am

#1510378
RE: Running Total

Hi Jay, try reading the link in my sig "please read this". It will describe to you how to set up some sample data for folks to test solutions against....
^{“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

July 9, 2012 at 8:18 am

#1510360
RE: Running Total

-- sample data. I reckon a [quantity] column is missing.
-- so calculating over [Docket_Id] instead.
DROP TABLE #DocketTB
SELECT *
INTO #DocketTB
FROM (
SELECT Docket_Category = 'Mechanical', Docket_Id = 49 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

July 9, 2012 at 7:34 am

#1510336
RE: Stripping off the time

krypto69 (7/9/2012)
How do I remove the time portion from my date/time results?
SELECT CAST(FLOOR(CAST(EecDateOfLastHire AS FLOAT)) AS DATETIME) from empcomp
gives me:
1984-08-13 00:00:00.000
when what I want is:
1984-08-13
It's not possible without...
^{“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

July 9, 2012 at 7:18 am

#1510329
RE: Running Total

jerome.morris (7/9/2012)
Hi all I have a table with columns in that I want to do a running total on. I want to be able to see how many occurances of...
^{“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

July 9, 2012 at 7:13 am

#1510324
RE: Help me in improving the SQL query,Please

J Livingston SQL (7/8/2012)
...small sidebar
why
CONVERT( datetime , CONVERT( nvarchar , TT.TDDATE , 113 ) , 113 )
TDATE is a datetime...as below
CREATE TABLE [dbo].[INITIAL](
[TN] [numeric](15, 0) NULL,
[WH] [nvarchar](4) NULL,
[TDDATE] [datetime] NULL,
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

July 9, 2012 at 5:27 am

#1510268

Viewing 15 posts - 6,076 through 6,090 (of 10,143 total)