2364

SQL Server - Find records that have appeared 3 times in last 30 days

Question:

I apologize if this has been answered before, but I looked in every way I knew how and could not find it.

I have a table of weekly reports organized like so: [pcname], [pcnumber], [username], [date]

I am trying to retrieve records where [pcname] has repeated 3 times in the last 30 days. Since these are weekly appearances that means 3 times in the last 4 weeks. Each record should display once, or repeat 3 times and I can filter it in excel later (I'll worry about report and formatting later, just trying to get a working query right now). Everything I try gets me results where some rows have duplicates (except for date of course) but some don't. They should either all have duplicates, or none. There may be multiple appearances if [pcname] is the same, but username is not, but if every column but date is the same, there should either be 3 of everything or 1 of everything, unless there is a change in one of the columns that is not [pcname] or [date] of course. Currently I am getting seemingly random duplicates (where every column but [date] is identical) and I cannot figure out why.

For background: the table is of weekly reports sent out by pc security. Everything that shows up on the list is out of compliance. However, we do not address everything on the list because things go out of compliance for a day or two then resolve themselves all the time. Therefor we need only the offenders that are out of compliance for 3 weeks out of the last 4, to let us know that PC is a problem child.

The SQL query I have so far is:

SELECT [pcname], [Username], [column], --from server2 [column], --from server2 [column], --from server3 [column], --from server3 [DATE] FROM [server1].database.schema.table1 LEFT JOIN [server2].database.schema.table2 ON Usernamet1 = Usernamet2 LEFT JOIN [server3].database.schema.table3 ON table2.[column] = table3.[column] WHERE [DATE] >= DATEADD(day, DATEDIFF(day, 0, GETDATE()) -28, 0) AND [pcname] IN (SELECT [pcname] FROM [server1].database.schema.table1 GROUP BY [pcname] HAVING COUNT(*) = 3) --WHERE [DATE] >= DATEADD(Day, -60, getdate()) GROUP BY [DATE], [pcname], [Username], [column1], [column2], [column3], [column4] --HAVING COUNT([pcname]) >= 3

As you can see, I left in other methods that I commented out just in case you find them useful. Let me know if you need more information. I am working in SQL server 2008.

Answer1:

Your query is close but you should not group by DATE field as this is the field that will happen multiple times.

SELECT [pcname], [Username], [column1], [column2], [column3], [column4] FROM [server1].database.schema.table1 LEFT JOIN [server2].database.schema.table2 ON Usernamet1 = Usernamet2 LEFT JOIN [server3].database.schema.table3 ON table2.[column] = table3.[column] WHERE [DATE] >= DATEADD(day, DATEDIFF(day, 0, GETDATE()) -28, 0) GROUP BY [pcname], [Username], [column1], [column2], [column3], [column4] HAVING COUNT(*) >= 3

Recommend

  • how to create listview to a grid programmatically
  • Firefox : There is no such field called inputType in keydown event object
  • a design to avoid circular reference in this scenario
  • Is it safe to assume sizeof(double) >= sizeof(void*)?
  • Create and load data (objects)
  • How to get strings in parentheses into array with java
  • how to set onItemClickListener() in a dialog listview?
  • Rails Active storage Unable to autoload constant ActiveStorage::Blob::Analyzable
  • Python: Graph using NetworkX and mplleaflet
  • swift UIView animateWithDuration with repeat and autoreverse
  • Html Multiple Input Elements With Same Name
  • Specify the _id field using Bulk.IndexMany in ElasticSearch
  • How to use CoreFoundation in QuickTime SDK for Windows?
  • Arc gradients in Flutter?
  • Nested AJAX Calls using .done
  • Java 11 and E(fx)clipse JavaFX plugin on Eclipse 4.9: An error has occurred - see the log file
  • if you have a DLL creating a bitmap in memory, how to return it to the browser?
  • SyntaxError: expected expression, got '.'
  • android : speech recognition what are the technologies available
  • $this->a->b->c->d calling methods from a superclass in php
  • In metro, get all inherited classes of an (abstract) class?
  • dmtracedump doesn't work, HELP!
  • Signed Java web start application with Glassfish 4.1 and Java7
  • Find all parks for a given zipcode with google maps
  • How to change the host IP sent in emails to new GitLab users to a publicly visible IP, not the local
  • Java Collections.shuffle() weird behaviour [closed]
  • How to use array in autohotkey?
  • How to redirect into different page by user type in php and mysql
  • Ember.js + JQuery-UI Tooltip - Tooltip does not reflect the model / controller changes
  • gnuplot - How to make zmin equal to zmax keeeping autoscale on z axis
  • Google Spreadsheet Script to Blink a range of Cells
  • Problems to understand DXGI DirectX 11 Desktop Duplication to get a Buffer or Array
  • How can I ssh into a server that requires 2 password authentication using python's paramiko mod
  • How do I add a mouse over tooltip to an Image using .DrawImage()
  • Unity3d lost directional light shadows after generate assetBundle (.unity3d file)
  • ssh remote server login script
  • Accessing Arguments, Workflow Variables from custom activities
  • How to mutate multiple variables without repeating codes?
  • convert json to excel in java
  • ReferenceError: TextEncoder is not defined