Sql-server – How long has it been since last card top-up

sql serversql-server-2008

We have a table for our stored-value cards:

CardNo   TransactionType   Amount    TransactionDate(datetime2)
         (1 for Top-up, 0 for purchasing products)
=========================================================
0001        1              1000.00    20170809(only date is displayed for brevity)
0001        0              200.00     20170810
0001        0              300.00     20170811
0001        1              200.00     20170811
0001        0              200.00     20170812

We would like to know for each card (customer), the time between each purchase and the most recent top-up at that time. So the intended result is:

RowID   GapDays
================
1       Null (or 0)
2       1
3       2
4       Null
5       1

Why is there a gap of 2 days for a row id of 3?

The purchase happens before the top-up.

The date column is of type datetime2, has a "time" portion (omitted here for brevity), and the data is ordered by that. You may utilize any index. Non-existent index will be added if necessary.

Best Answer

I've added time part to the TransactionDate just to get the correct order for the transactions:

create table cards(CardNo varchar(10), TransactionType int, Amount money, TransactionDate datetime2);
create index ix_cards on cards(CardNo, TransactionType, TransactionDate);
insert into cards values
('0001',        1,              1000.00,    '20170809 00:00:00'),
('0001',        0,              200.00,     '20170810 00:00:00'),
('0001',        0,              300.00,     '20170811 00:00:00'),
('0001',        1,              200.00,     '20170811 00:00:01'),
('0001',        0,              200.00,     '20170812 00:00:00');

GO

5 rows affected

Then you can get it by using an APPLY operator with all records where TransactionType = 1. Note you must limit to one record the rows returned by the subquery.

set statistics xml on;

SELECT t1.CardNo, t1.TransactionType, t1.Amount, t1.TransactionDate, t3.TransactionDate,
       DATEDIFF(DAY, t3.TransactionDate, t1.TransactionDate) GapDays
FROM   cards t1
OUTER APPLY (SELECT TOP 1 t2.TransactionDate
             FROM   cards t2
             WHERE  t2.CardNo = t1.CardNo
             AND    t2.TransactionType = 1
             AND    t2.TransactionDate <= t1.TransactionDate
             ORDER BY t2.CardNo, t2.TransactionDate DESC) t3
ORDER BY t1.CardNo, t1.TransactionType, t1.TransactionDate
set statistics xml off;
GO

CardNo | TransactionType | Amount    | TransactionDate     | TransactionDate     | GapDays
:----- | --------------: | :-------- | :------------------ | :------------------ | ------:
0001   |               0 | 200.0000  | 10/08/2017 00:00:00 | 09/08/2017 00:00:00 |       1
0001   |               0 | 300.0000  | 11/08/2017 00:00:00 | 09/08/2017 00:00:00 |       2
0001   |               0 | 200.0000  | 12/08/2017 00:00:00 | 11/08/2017 00:00:01 |       1
0001   |               1 | 1000.0000 | 09/08/2017 00:00:00 | 09/08/2017 00:00:00 |       0
0001   |               1 | 200.0000  | 11/08/2017 00:00:01 | 11/08/2017 00:00:01 |       0

dbfiddle here

Related Solutions

Sql-server – How to schedule a weekly backup of a database only if it has been changed since last backup

Welcome to MS SQL :-)

To start with your question:

Yes there is. First you make a full database backup:

BACKUP DATABASE yourdatabasename
TO DISK = "pathtobackupdir\backupfilename.bak"

Now from that moment on a so called differential bitmap is keeping track of all pages changed.

From that moment every time you create a DIFFERENTIAL backup, only the pages that changed since your last FULL backup will be backupped.

 BACKUP DATABASE yourdatabasename
    TO DISK = "pathtobackupdir\backupfilename.bak" WITH DIFFERENTIAL

So every sunday, asuming you had changes every week, the diff backup will be bigger then the previous one. Untill one sunday, it's just as big as a full backup would be. That would imply that all data pages had changed. At that moment you want to do a new full backup. Since that will reset the differential bitmap and your next diff backup will be small again. (depending on the amount of changes)

You could automate the process of making diff backups untill a certain percentage of the data has changed. Have a look at this blog post: http://www.sqlskills.com/BLOGS/PAUL/post/New-script-How-much-of-the-database-has-changed-since-the-last-full-backup.aspx It explains a way on how to programatically look up the percentage of database changes. That way you could say.. make diff backups if percentage changed < 75% otherwise make a full backup.

Now could you tell me why you want this? Because this isn't a setup I would advise a starter (with exceptions). So please elaborate a bit and we can see if this is the best solution for your setup. How big is your database? how much changes do you have? is batch based changes? Which version and Edition of SQL do you have do you have?

Sql-server – Know when a database has been restored

In the msdb database, there is a table named restorehistory:

declare @DB sysname = 'MyDB';
select * from msdb.dbo.restorehistory where destination_database_name = @DB;

This is a table that people with sufficient privileges can clear out, but if the restore was recent and you don't have a job which clears out this table (or if it hasn't run between then and now), you should be able to see the login which performed the restore.

EDIT

You can also join it to a couple of other tables (if they have relevant data): backupset and backupmediafamily. If there are records there--with the same retention caveats as before--they'll tell you more about the backup file(s) used to restore:

declare @DB sysname = 'MyDB';
select
    rh.destination_database_name,
    rh.user_name,
    bs.name as backup_set_name,
    bs.user_name as backup_set_username,
    bs.backup_start_date,
    bs.backup_finish_date,
    bs.database_name as backup_set_database_name,
    bs.server_name,
    bs.machine_name,
    bmf.physical_device_name,
    bmf.device_type,
    case bmf.device_type
        when 2 then 'Disk'
        when 5 then 'Tape'
        when 7 then 'Virtual Device'
        when 105 then 'Permanent backup device'
        else 'UNKNOWN'
    end as device_type_desc
from 
    msdb.dbo.restorehistory rh
    left outer join msdb.dbo.backupset bs on rh.backup_set_id = bs.backup_set_id
    left outer join msdb.dbo.backupmediafamily bmf on bs.media_set_id = bmf.media_set_id
where
    rh.destination_database_name = @DB;

That way, you could also filter on device type or file location.

Best Answer

Related Solutions

Sql-server – How to schedule a weekly backup of a database only if it has been changed since last backup

Sql-server – Know when a database has been restored

Related Question