question

bktanwork avatar image
bktanwork asked

How to produce these results using SQL

I have a table which has the following columns: Company Transaction ID Transaction Date The result I want is:
|      COMPANY        |  Transaction ID  |Transaction Date  |     GROUP
|---------------------|------------------|------------------|----------
|     Company A       |      t_0001      |    01-01-2014    |         1
|     Company A       |      t_0002      |    02-01-2014    |         1
|     Company A       |      t_0003      |    04-01-2014    |         1
|     Company A       |      t_0003      |    10-01-2014    |         2
|     Company B       |      t_0004      |    02-01-2014    |         1
|     Company B       |      t_0005      |    02-01-2014    |         1
|     Company C       |      t_0006      |    03-01-2014    |         1
|     Company C       |      t_0007      |    05-01-2014    |         2
where the transactions and dates are firstly group into companies. The transactions within the company are sorted from the earliest to the latest. The transactions are checked, row by row, if the previous transaction was performed less than 3 days ago in a moving window period. For example, t_0002 and t_0001 are less than 3 days apart so they fall under group 1. t_0003 and t_0002 are less than 3 days apart so they fall under group 1 even though t_0003 and t_0003 are >= 3 days apart. I figured the way to go about doing this is to group the data by companies first, following by sorting the transactions by the dates, but I got stuck after this. Like what methods are there I could use to produce this results? Any help on this? P.S. I am using SQL Server 2014.
sqlsql server 2014transaction
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

SELECT * FROM Table GROUP BY Company ORDER BY Transaction Date Thats all i came up with because i'm not sure what functions could sql perform pertaining to this.
1 Like 1 ·
Please post the code code you've attempted to write so far. You're not going to learn much by have other people do all your homework for you. If you can show people you're trying they'll be more inclined to help.
0 Likes 0 ·

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.