Solved

Report a single record for multiple records within a date range

Posted on 2009-04-07
21
268 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
  • 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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.

706 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now