Solved

Report a single record for multiple records within a date range

Posted on 2009-04-07
21
280 Views
Last Modified: 2012-05-06
I need to write a query to emulate the functionality of a SELECT DISTINCT query which reports a single record where the datetime field has a date within 3 days of a particular record. In the attached spreadsheet I would want the sql statement to report two records:

324329   03/04/2009

and

324329   06/04/2009

as row 17 would be the first record, and the next record outside the 3 day range (in ascending date order) would be row 8.

Any ideas please?
example.xls
0
Comment
Question by:Grffster
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
  • 3
  • +1
21 Comments
 
LVL 6

Expert Comment

by:J_Carter
ID: 24089322
select File_ref, CONVERT(VARCHAR,tran_datetime102)
from TABLE
group by File_ref, CONVERT(VARCHAR,tran_datetime102)
0
 
LVL 6

Expert Comment

by:J_Carter
ID: 24089332
sorry, there was a typo.

select File_ref, CONVERT(VARCHAR,tran_datetime,102)
from TABLE
group by File_ref, CONVERT(VARCHAR,tran_datetime,102)
0
 

Author Comment

by:Grffster
ID: 24089350
That won't group together those within 3 days of each other though. In the example file I would want to group those records from the 3rd and 4th of the month you see.
0
Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

 
LVL 6

Expert Comment

by:J_Carter
ID: 24089445
try this then
select File_ref, CONVERT(VARCHAR,tran_datetime,102)
from TABLE t1
join TABLE t2 on t1.File_ref = t2.File_ref AND DATEDIFF(day, t1.tran_datetime, t2.tran_datetime) < 3
group by File_ref, CONVERT(VARCHAR,tran_datetime,102)

Open in new window

0
 

Author Comment

by:Grffster
ID: 24094940
That doesn't work either unfortunately. It gives me exactly the same results as your previous query i.e. reports 3 records, one for each day. Would need to see one record to cater for those records on the the 3rd and 4th of april, and another one for those on the 6th of april.
CREATE TABLE #TempData1 (
   
File_ref int,
   
tran_datetime datetime
)
 
 
INSERT #TempData1 (File_ref, tran_datetime)
SELECT 324329, '20090406 10:47:00' UNION ALL
SELECT 324329, '20090406 07:41:00' UNION ALL
SELECT 324329, '20090406 07:41:00' UNION ALL
SELECT 324329, '20090406 07:40:00' UNION ALL
SELECT 324329, '20090406 07:40:00' UNION ALL
SELECT 324329, '20090406 07:40:00' UNION ALL
SELECT 324329, '20090406 07:40:00' UNION ALL
SELECT 324329, '20090404 08:34:00' UNION ALL
SELECT 324329, '20090403 07:47:00' UNION ALL
SELECT 324329, '20090403 07:47:00' UNION ALL
SELECT 324329, '20090403 07:45:00' UNION ALL
SELECT 324329, '20090403 07:45:00' UNION ALL
SELECT 324329, '20090403 07:44:00' UNION ALL
SELECT 324329, '20090403 07:44:00' UNION ALL
SELECT 324329, '20090403 07:44:00' UNION ALL
SELECT 324329, '20090403 07:42:00'

Open in new window

0
 
LVL 2

Expert Comment

by:cthulthu
ID: 24099300
Why does the SQL statement have to do all the work?  Can't you get most of the way there and then use your reporting tool or some sort of post processing to clean up the records as you need them?
0
 

Author Comment

by:Grffster
ID: 24105156
I don't think SSRS is that sophisticated really.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24148718
I would love to help, but I don't completely understand your requirement.  Are you displaying a single-record report, or is there a UNION involved?
0
 

Author Comment

by:Grffster
ID: 24149204
I'm trying to obtain a table with all the records that will be created using the interval data above. I've attached a copy of what the results would look like if I only used the first three records of my test file above (changing the interval type from M (months) to D (days) to illustrate the point).  Ignore the formatting in this spreadsheet. I manually typed most of it.
test-data-for-promise-to-pays.xls
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24149404
It looks like you need to have an INSERT INTO, instead of the UNION ALL, in a WHILE loop, with a counter.
0
 

Author Comment

by:Grffster
ID: 24202603
That statement is to populate my test data not the solution.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24202762
I have to admit that I don't understand what kind of help that you need, and all the comments aren't helping either.
0
 

Author Comment

by:Grffster
ID: 24202822
J Carter's solutions weren't working as they were still grouping by date. I need to group records that occur within 3 days of each other as one record. Apologies for the test file called test-data-for-promise-to-pays.xls. This was meant for another open question I had. This may be confusing the issue.  The test data you need to view is the one with ID 24094940. I was getting three records here using J Carter's solution, one for the 3rd of april, one for the 4th and one for the 6th. There should only be 2 records: one for the 3rd of april (which would include those from the 4th as it's within 3 days of the 3rd) and one for the 6th of april.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24203193
So, you are talking about this query:

SELECT File_ref, CONVERT(VARCHAR, tran_datetime, 102) AS tran_datetime
   FROM TABLE t1
   INNER JOIN TABLE t2
        ON t1.File_ref = t2.File_ref AND
             DATEDIFF(day, t1.tran_datetime, t2.tran_datetime) < 3
   GROUP BY File_ref, CONVERT(VARCHAR, tran_datetime, 102)

I have attached a document of my experimentation.
SQL-Server-Query.doc
0
 

Author Comment

by:Grffster
ID: 24205952
Tried your code thanks but it's still reporting three records, one for 3rd of april, one for 4th, and one for the 6th.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24205975
Hmmm...I used your test data, had something like 16 input records, and the output results showed only 2 lines...
0
 

Author Comment

by:Grffster
ID: 24206456
This is how I called it from your function:

SELECT t1.File_ref, CONVERT(VARCHAR, t1.tran_datetime, 102) AS tran_datetime
   FROM dbo.TestData() t1
   INNER JOIN dbo.TestData() t2
        ON t1.File_ref = t2.File_ref AND
             DATEDIFF(day, t1.tran_datetime, t2.tran_datetime) < 3
   GROUP BY t1.File_ref, CONVERT(VARCHAR, t1.tran_datetime, 102)
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24208901
I wasn't using that query since it didn't seem to work.  Are you still trying to make it work?
0
 

Author Comment

by:Grffster
ID: 24212884
Yes, unfortunately. How did you get two records in the end?
0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 500 total points
ID: 24213166
I got those 2 records from the stored procedure.  I saw an anomaly from the query and inner join approach, since it results in too many records (cartesian product), and appears to be a flawed approach.  

I used the approach of selecting just a unique set of records as the basis,

      SELECT file_ref, CONVERT(varchar, tran_datetime, 101) AS tran_datetime
          FROM TestData()
          GROUP BY file_ref, CONVERT(varchar, tran_datetime, 101)

and, then scanning the records, comparing the difference between a previously stored date and the current date, looking for >= 3 day difference.
DECLARE Data_Cursor CURSOR FOR
SELECT file_ref, CONVERT(varchar, tran_datetime, 101) AS tran_datetime
  FROM TestData()
  GROUP BY file_ref, CONVERT(varchar, tran_datetime, 101)
  
DECLARE @ResultTable TABLE (File_ref int, tran_datetime smalldatetime, date_diff smallint)
DECLARE @File_ref int, @tran_datetime smalldatetime
DECLARE @start_file_ref int, @start_tran_datetime smalldatetime
DECLARE @date_diff smallint
DECLARE @Fetch_Status smallint
 
OPEN Data_Cursor;
FETCH NEXT FROM Data_Cursor INTO @start_file_ref, @start_tran_datetime;
SET @Fetch_Status = @@FETCH_STATUS
         INSERT INTO @ResultTable (file_ref, tran_datetime, date_diff) 
            VALUES (@start_file_ref, @start_tran_datetime, 0)
WHILE @Fetch_Status = 0
   BEGIN
      FETCH NEXT FROM Data_Cursor INTO @File_ref, @tran_datetime
      SET @Fetch_Status = @@FETCH_STATUS
      SET @date_diff = DATEDIFF(DAY, @start_tran_datetime, @tran_datetime)
      IF @date_diff >= 3
      BEGIN
         INSERT INTO @ResultTable (file_ref, tran_datetime, date_diff) 
            VALUES (@file_ref, @tran_datetime, @date_diff)
         SET @start_file_ref = @File_ref
         SET @start_tran_datetime = @tran_datetime        
      END
   END;
CLOSE Data_Cursor;
DEALLOCATE Data_Cursor;
 
SELECT * 
   FROM @ResultTable

Open in new window

0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question