Number of records created during an indefinate timespan 2

[mutley1]

Hi Experts,

Baffled on this one. I have 2 tables, one contains the size of the database and the datetime stamp the data was taken

40GB 2001-12-31
41GB 2002-03-10
45GB 2002-03-19

(date is a datetime field for sure).

Second table holds individual records of items created and when they were created, e.g.

Item 1      2001-12-31
Item 2      2002-03-11
Item 3      2002-03-17
Item 4      2002-03-18

What I want to try to do is get the sum of how many records were added (T2) between the data collection for T1. Example, if T1 is backup size from msdb.backuphistory and T2 is a working table I have, how can I define how many entries were made between each backup, using the date fields? I am looking at implementing a historical DB at the moment, but need to produce a 1 off and have no history to go on except the msdb and the dates of additions in the user table. Any ideas chaps (and SqlSister!).

cheers,

M.

 

[SQLSister]

JUst brainstorming here, may or may not be the most efficent way to go, but for a one-off should work.

Take the backup dates you are concerned with into a temptable sorted by date that has an identity field on it.
This should now give you the backups in order so that the start identity is one fewer than the next the backup. Now you can do a self-jon to that table onthe identity field
like so:
from #temp t1
join #temp t2 on t1.idfield = t2.idfield+1

Now it is just a matter of joining to the table you want to get records for where startdate is from t1 and enddate is t2.

"NOTHING is more important in a database than integrity." ESquared

 

[mutley1]

Thanks SQL Sister,

I get where you're coming from, but still baffled. See sample data below:

 Backups

DatabaseName	43127.02	10/08/2008
DatabaseName	43335.31	16/08/2008
DatabaseName	43593.38	22/08/2008
DatabaseName	43661.12	25/08/2008
DatabaseName	43837.33	30/08/2008
DatabaseName	44032.26	06/09/2008
DatabaseName	44350.11	13/09/2008
DatabaseName	44684.76	20/09/2008

 #Transactions over period by day (10/08 - 20/09)
20/08/2008	3927
21/08/2008	191
22/08/2008	256
26/08/2008	3584
27/08/2008	5518
28/08/2008	318
29/08/2008	160
01/09/2008	1936
02/09/2008	3982
03/09/2008	4208
04/09/2008	220
05/09/2008	168
08/09/2008	1840
09/09/2008	4028
10/09/2008	3327
11/09/2008	590
12/09/2008	236
13/09/2008	1
15/09/2008	1902
16/09/2008	3180
17/09/2008	4353
18/09/2008	240
19/09/2008	123
20/09/2008	1

So Ideally what I want to do is say from (inc.) 06/09/2008 (UK date format that is!) to and including 12/09 (i.e. backup on 6th and up until the day before the next one) would sum 10021. The prob is the backups are not always weekly or of a given time span, as are transactions....may have none for some time then 1 day loads and next day none etc. Some sort of "between" but cant get my head round it.

TIA if you have any brainwaves.

M

 

[gmmastros]

To expound on SQLSister's advice...

What you really need in order to get your results is a date range. I mean... if you wanted the sum of transactions and you knew what the date range was, it would be very simple.

[tt][blue]
Select Sum(TransactionCount)
From TransactionTable
Where TransactionDate >= StartDate
And TransactionDate < EndDate
[/blue][/tt]

Getting the start date and end date is problematic, but not impossible. The simplest way to accomplish this is with a temp table (or table variable). First, let me show you...

Set DATEFORMAT DMY

Declare @Backups Table(DatabaseName VarChar(100), Size Decimal(20,10), BackupDate DateTime)
Insert Into @Backups Values('DatabaseName',    43127.02,    '10/08/2008')
Insert Into @Backups Values('DatabaseName',    43335.31,    '16/08/2008')
Insert Into @Backups Values('DatabaseName',    43593.38,    '22/08/2008')
Insert Into @Backups Values('DatabaseName',    43661.12,    '25/08/2008')
Insert Into @Backups Values('DatabaseName',    43837.33,    '30/08/2008')
Insert Into @Backups Values('DatabaseName',    44032.26,    '06/09/2008')
Insert Into @Backups Values('DatabaseName',    44350.11,    '13/09/2008')
Insert Into @Backups Values('DatabaseName',    44684.76,    '20/09/2008')

[green]-- Create a table variable to store intermediate values.[/green]
Declare @Temp Table(RowId Int Identity(1,1), BackupDate DateTime)

[green]--Insert data to table variable.  The ORDER BY is very important. [/green]
Insert Into @Temp(BackupDate)
Select BackupDate
From   @Backups 
Order By BackupDate

[green]-- Use the RowId column to join this table to itself
-- This allows us to use the identity column to link the table to itself[/green]
Select A.BackupDate As StartDate, 
       B.BackupDate As EndDate
From   @Temp As A 
       Inner Join @Temp As B 
         On [!]A.RowId = B.RowId - 1[/!]

The code above creates a table variable to store your sample data. You can copy/paste this to query analyzer to see how it works.

As you can see, the query above will show you the accurate date ranges, which is what you really need for this query. The rest is simple.

Set DATEFORMAT DMY

Declare @Backups Table(DatabaseName VarChar(100), Size Decimal(20,10), BackupDate DateTime)
Insert Into @Backups Values('DatabaseName',    43127.02,    '10/08/2008')
Insert Into @Backups Values('DatabaseName',    43335.31,    '16/08/2008')
Insert Into @Backups Values('DatabaseName',    43593.38,    '22/08/2008')
Insert Into @Backups Values('DatabaseName',    43661.12,    '25/08/2008')
Insert Into @Backups Values('DatabaseName',    43837.33,    '30/08/2008')
Insert Into @Backups Values('DatabaseName',    44032.26,    '06/09/2008')
Insert Into @Backups Values('DatabaseName',    44350.11,    '13/09/2008')
Insert Into @Backups Values('DatabaseName',    44684.76,    '20/09/2008')

Declare @Transactions Table(TransactionDate DateTime, TransactionCount int)

Insert Into @Transactions Values('20/08/2008',    3927)
Insert Into @Transactions Values('21/08/2008',    191)
Insert Into @Transactions Values('22/08/2008',    256)
Insert Into @Transactions Values('26/08/2008',    3584)
Insert Into @Transactions Values('27/08/2008',    5518)
Insert Into @Transactions Values('28/08/2008',    318)
Insert Into @Transactions Values('29/08/2008',    160)
Insert Into @Transactions Values('01/09/2008',    1936)
Insert Into @Transactions Values('02/09/2008',    3982)
Insert Into @Transactions Values('03/09/2008',    4208)
Insert Into @Transactions Values('04/09/2008',    220)
Insert Into @Transactions Values('05/09/2008',    168)
Insert Into @Transactions Values('08/09/2008',    1840)
Insert Into @Transactions Values('09/09/2008',    4028)
Insert Into @Transactions Values('10/09/2008',    3327)
Insert Into @Transactions Values('11/09/2008',    590)
Insert Into @Transactions Values('12/09/2008',    236)
Insert Into @Transactions Values('13/09/2008',    1)
Insert Into @Transactions Values('15/09/2008',    1902)
Insert Into @Transactions Values('16/09/2008',    3180)
Insert Into @Transactions Values('17/09/2008',    4353)
Insert Into @Transactions Values('18/09/2008',    240)
Insert Into @Transactions Values('19/09/2008',    123)
Insert Into @Transactions Values('20/09/2008',    1)

Declare @Temp Table(RowId Int Identity(1,1), BackupDate DateTime)
Insert Into @Temp(BackupDate)
Select BackupDate
From   @Backups 
Order By BackupDate

Select A.BackupDate As StartDate, 
       B.BackupDate As EndDate,
       Sum(TransactionCount) As Transactions
From   @Temp As A 
       Inner Join @Temp As B 
         On [!]A.RowId = B.RowId - 1[/!]
       Inner Join @Transactions T
         On [!]T.TransactionDate >= A.BackupDate
         And T.TransactionDate < B.BackupDate[/!]
Group By A.BackupDate, B.BackupDate

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mutley1]

Thanks George / SQLSister,

I will give it a go today - star in advance as I am 100% sure it is sage advice as usual.

Rgds,

M.

 

[OlafBogus]

Hi Mutley,

the following select statement will return the count of in T2 that occur between the entries in T1.

Tables are
T1
Size Recorded
10k 23/09/2008 02:00:00
20k 23/09/2008 10:00:00
30k 23/09/2008 12:00:00

T2
Item AddedAt
Item1 23/09/2008 01:00:00
Item2 23/09/2008 06:00:00
Item3 23/09/2008 07:00:00
Item4 23/09/2008 11:00:00
Item5 23/09/2008 11:30:00
Item6 23/09/2008 13:00:00

Output
Count Size Recorded
1 10K 23/09/2008 02:00:00
2 20K 23/09/2008 10:00:00
2 30K 23/09/2008 12:00:00

Query
SELECT count(*), This.Size,This.Recorded FROM T2
INNER JOIN T1 This ON This.Recorded > T2.AddedAt
AND (((SELECT MAX(T1.Recorded) FROM T1 WHERE T1.Recorded < This.Recorded) IS NOT NULL AND T2.AddedAt > (SELECT MAX(T1.Recorded) FROM T1 WHERE T1.Recorded < This.Recorded))
OR ((SELECT MAX(T1.Recorded) FROM T1 WHERE T1.Recorded < This.Recorded) IS NULL AND T2.AddedAt > '01/01/1900 00:00:00'))
Group By This.Size, This.Recorded

 

[mutley1]

Thanks all,

Worked an absolute treat. Much appreciated.

Cheers,

M.