-- Next I've commented out the join to Legacy L2.

-- It appears to be redundant for this purpose, but

-- will add to the cost of running the query, i.e. how...

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...

-- This is the BASELINE QUERY. It's your starting point.

-- I've formatted it and added a new column [RowType] to help you to...

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...

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...

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...

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...

Me too, and it will probably...

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...

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...

Please post the ddl for the tables 'actual sales data', 'projected sales data' and 'periods', along with INSERT scripts to populate them with data.

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...

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...

SELECT *

FROM data_table

WHERE CREAT_TM >= @CreatedDate

AND CREAT_TM < DATEADD(DAY,1,@CreatedDate)

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...