July 15, 2016 at 6:24 am
Hi,
I have a small data set that I have created and inserted into a temp table by looking for duplicate records.
See code.
DECLARE @startdate DATETIME = '2016-06-01'
DECLARE @enddate DATETIME = '2016-06-30'
SELECT B.[Building Name], B.[Date] INTO ##SSTemp2
FROM (
SELECT [Building Name], [Date], COUNT(*) AS 'Count' FROM [Main Table]
WHERE [Date] BETWEEN @startdate AND @enddate
GROUP BY [Building Name], [Date]
HAVING COUNT(*) > 1 ) B
Output:
Building NameDate
Building 102/06/2016
Building 205/06/2016
Building 306/06/2016
Building 408/06/2016
Building 510/06/2016
Building 610/06/2016
Building 710/06/2016
Building 812/06/2016
Building 915/06/2016
Building 1016/06/2016
Now I want to be able to drill back down to the original [Main Table] to see what the duplicate records are. Is anyone able to show me how to code this. I was thinking there needs to be a sub query in the where clause but I can make it look at both.
Thanks for any help.
July 15, 2016 at 6:32 am
So to make it clear I want to query the main table based on the temp table I have created to show more detail.
July 15, 2016 at 6:35 am
dramaqueen (7/15/2016)
So to make it clear I want to query the main table based on the temp table I have created to show more detail.
No need for a temp table. Try something like this:
DECLARE @startdate DATETIME = '2016-06-01';
DECLARE @enddate DATETIME = '2016-06-30';
WITH dupes
AS (SELECT [Building Name]
FROM [Main Table]
WHERE Date BETWEEN @startdate AND @enddate
GROUP BY [Building Name]
HAVING COUNT(*) > 1
)
SELECT t.*
FROM [main table] t
JOIN dupes ON t.[Building Name] = dupes.[Building Name];
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 15, 2016 at 6:47 am
That's not working for me as it brings back 25k rows when in my test it should only bring back 22 rows.
I think its me being really bad at explaining.
So I have a big table with multiple columns... I need to run a report that basically looks to see if there are any buildings that have rows recorded more than once for a certain date. That bit I have perfectly.
DECLARE @startdate DATETIME = '2016-06-01'
DECLARE @enddate DATETIME = '2016-06-30'
SELECT [Building Name], [Date], COUNT(*) AS 'Count' FROM [Main Table]
WHERE [Date] BETWEEN @startdate AND @enddate
GROUP BY [Building Name], [Date]
HAVING COUNT(*) > 1
Say this is the output
Building NameDate Count
BUILD12016-06-02 01:00:00.0002
BUILD22016-06-05 01:00:00.0002
BUILD22016-06-06 01:00:00.0002
I want to create a new query referencing the original query hence putting the output into a temp table to show the 6 records relating to BUILD 1 & 2 on the three dates in June.
I hope this makes more sense.
July 15, 2016 at 7:08 am
Phil Parkin (7/15/2016)
dramaqueen (7/15/2016)
So to make it clear I want to query the main table based on the temp table I have created to show more detail.No need for a temp table. Try something like this:
DECLARE @startdate DATETIME = '2016-06-01';DECLARE @enddate DATETIME = '2016-06-30';
WITH dupes
AS (SELECT [Building Name]
FROM [Main Table]
WHERE Date BETWEEN @startdate AND @enddate
GROUP BY [Building Name]
HAVING COUNT(*) > 1
)
SELECT t.*
FROM [main table] t
JOIN dupes ON t.[Building Name] = dupes.[Building Name];
Phil, you missed the Date column from the grouping ...
Select B.[Building Name], B.[Date]
Well you can do in two ways:
1 is showing below, corrected the Phil Error:
WITH dupes
AS (SELECT [Building Name], [Date]
FROM [Main Table]
WHERE Date BETWEEN @startdate AND @enddate
GROUP BY [Building Name], [Date]
HAVING COUNT(*) > 1
)
SELECT t.*
FROM [main table] t
JOIN dupes ON t.[Building Name] = dupes.[Building Name]
AND t.[Date] = dupes.[Date]
Other one is using a window function that goes like this:
SELECT *
FROM
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY [Building Name], [Date] ORDER BY [Building Name], [Date]) as DupRows
FROM [Main Table]
) Dupes
--- uncomment the below line if you only want to see the duplicates.
---Where DupRows > 1
July 15, 2016 at 7:30 am
Thank you very much for your help.
July 15, 2016 at 7:38 am
you are very much welcome 🙂
July 15, 2016 at 7:51 am
Phil, you missed the Date column from the grouping ...
Thanks for picking that up. It was untested, pre-coffee, and it's a Friday. A dire combination, when it comes to my coding skills!
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 15, 2016 at 8:18 am
Phil Parkin (7/15/2016)
Phil, you missed the Date column from the grouping ...
Thanks for picking that up. It was untested, pre-coffee, and it's a Friday. A dire combination, when it comes to my coding skills!
no worries Phil, Happens to everyone. 😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply