ChrisM@Work

Forum Replies Created

Viewing 15 posts - 3,136 through 3,150 (of 10,143 total)

RE: Duplicate record count

pilla.sree85 (7/17/2014)
...
what should i do next??
Remember what you've observed and give yourself some credit for learning it. Next:
-- Having corrected the filter 'HAVING COUNT(*) > 1',
-- change the table joins...
^{“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 17, 2014 at 5:40 am

#1730205
RE: Duplicate record count

pilla.sree85 (7/17/2014)
yes need to fix the baove query
-- This is the BASELINE QUERY. It's your starting point.
-- I've formatted it and added a new column [RowType] to help you to...
^{“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 17, 2014 at 5:11 am

#1730199
RE: Duplicate record count

pilla.sree85 (7/17/2014)
SELECT count(L1.AcctNo) totalcount,L1.AcctNo,L1.ReceivedFileID from Legacy L1,Legacy L2,ReceivedFiles
where L1.ReceivedFileID = ReceivedFiles.ReceivedFileID
and L1.AcctNo=L2.AcctNo
and L1.MarketCode NOT IN (SELECT MarketCode from Markets where (AllowDupes = '1' OR UPPER(AllowDupes) = 'Y' ))
group by...
^{“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 17, 2014 at 4:50 am

#1730184
RE: Duplicate record count

SELECT
L1.AcctNo,
L1.ReceivedFileID
FROM Legacy_crfcard L1
-- Why this? If you have any dupes, it will double the number of rows output.
-- That's ALL it will do - slow...
^{“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 17, 2014 at 4:08 am

#1730171
RE: Duplicate record count

pilla.sree85 (7/17/2014)
My Query is i want to pull duplicate records from the table and get the count of each duplicate record from the table.i wrote below query
IF EXISTS(
SELECT L1.AcctNo,L1.ReceivedFileID 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 17, 2014 at 3:57 am

#1730170
RE: Duplicate record count

pilla.sree85 (7/17/2014)
i could not understand below query.when i execute below query i am getting errors.
SELECT COUNT(*) -- count the dupesets
FROM (
SELECT n = 1 -- i dont understand this statement
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 17, 2014 at 1:55 am

#1730147
RE: Cross Tab, UNION or Different solution for this problem?

Luis Cazares (7/16/2014)
ChrisM@Work (7/16/2014)
The ISDATE is for NULL checking...
You're right, I didn't see the DDL before I posted.
However, this puzzles me:
[Store_Opens_Baseline] [nvarchar](255) NULL,
Me too, and it will probably...
^{“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 16, 2014 at 9:51 am

#1729912
RE: Cross Tab, UNION or Different solution for this problem?

The ISDATE is for NULL checking...
SELECT
f.Brand,
COUNT(recID),
p.Period,
p.Quarter,
p.Year
FROM forecast_data f
LEFT OUTER JOIN Periods p
ON COALESCE(f.Store_Opens_Actual, f.Store_Opens_Forecast, f.Store_Opens_Baseline) BETWEEN p.PeriodStarts AND p.PeriodEnds
--(CASE
--WHEN ISDATE(f.Store_Opens_Actual) = 1 THEN...
^{“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 16, 2014 at 9:45 am

#1729908
RE: Duplicate record count

SELECT @_Stat_Count = COUNT(*) -- count the dupesets
FROM (
SELECT n = 1 -- any output will do here: a row corresponds to a dupeset
FROM Legacy L1
INNER JOIN Legacy L2
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

July 16, 2014 at 9:18 am

#1729897
RE: Cross Tab, UNION or Different solution for this problem?

Please post the ddl for the tables 'actual sales data', 'projected sales data' and 'periods', along with INSERT scripts to populate them with data.
^{“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 16, 2014 at 9:09 am

#1729892
RE: Distinct and count

SQLSteve (7/14/2014)
Thanks -
2 excels attached. As is is how it stands now (obviously sensitive data removed)
We just need each supplier code to have 1 entry, not multiple.
All 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 14, 2014 at 9:10 am

#1729170
RE: Distinct and count

Here's your query with table aliases applied for readability:
SELECT DISTINCT
ms.SUPPLIER_ACCOUNT,
ms.SUPPLIER_NAME,
ms.ADDRESS1,
ms.ADDRESS2,
ms.ADDRESS3,
ms.ADDRESS4,
ms.ADDRESS5,
ms.ADDRESS6,
ms.POSTCODE,
ms.PAYMENT_TERM,
mb.BANK_SORT_CODE,
mb.BANK_ACCOUNT_NUM,
mb.BANK_REFERENCE,
cd.InvDate
FROM MAGINUS_SUPPLIER ms
INNER JOIN MAGINUS_BANK_DETAILS mb
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

July 14, 2014 at 7:08 am

#1729124
RE: How to add time zone to Date time field

pilla.sree85 (7/11/2014)
sql server 2005
SELECT *
FROM data_table
WHERE CREAT_TM >= @CreatedDate
AND CREAT_TM < DATEADD(DAY,1,@CreatedDate)
^{“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 11, 2014 at 5:08 am

#1728683
RE: How to add time zone to Date time field

pilla.sree85 (7/11/2014)
I was tried with your solution below but no luck.
select * from data_table where cast(CREAT_TM as DATE) = '2012-07-01'
i am getting error as
Type DATE is not 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

July 11, 2014 at 4:43 am

#1728674
RE: Group Column Based On Anouther column

CREATE TABLE #Sample (t_id int, w_id int, t_code CHAR(5), w_name VARCHAR(25))
INSERT INTO #Sample (t_id, w_id, t_code, w_name)
SELECT 35855, 3680, 'A1100', 'EVM Method Project' UNION ALL
SELECT 35856, 3680, 'A1110', 'EVM Method...
^{“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 11, 2014 at 3:16 am

#1728645

Viewing 15 posts - 3,136 through 3,150 (of 10,143 total)