Count Difference for Every 15 Min

Question

Count Difference for Every 15 Min

Sangeeth Raj

Hall of Fame

Points: 3541
More actions
February 8, 2017 at 8:37 am

#327087

Hi,
When I run the query using mulitple joins on Fact table and Dimension Table,
my Output would look like
Execution :1
When I run the Query at 15:24
Identifier Count Time
A 10 CurrentTimestamp (Getdate), i.e... 15:24
B 15 15:24
Execution:2
Later when I run the same query after 15 Min the output would be
When I run the query at 15:39
Identifier Count Time
A 16 15:39
B 17 15:39
Later when I run the query after 15 Min the output would be
When I run the query at 15:54
Identifier Count Time
A 21 15:54
B 30 15:54
I am trying to achieve as follows which is what is the increment count for each time stamp.
Indicator Count Time
A 10 15:24 ---- Initial Count
B 15 15:24
A 6 15:39 ---- After 15 Min Execution ( 16 - 10)
B 2 15:39 --- (17-15)
A 5 15:54 ---- After 15 Min Execution (21-16)
B 13 15:54 ---- i.e.(30-17 )
I will appreciate you time and skills.
Many Thanks in Advance.

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247180 More actions · Answer 1

Sangeeth878787 - Wednesday, February 8, 2017 8:37 AM
Hi,
When I run the query using mulitple joins on Fact table and Dimension Table,
my Output would look like
Execution :1
When I run the Query at 15:24
Identifier Count Time
A 10 CurrentTimestamp (Getdate), i.e... 15:24
B 15 15:24
Execution:2
Later when I run the same query after 15 Min the output would be
When I run the query at 15:39
Identifier Count Time
A 16 15:39
B 17 15:39
Later when I run the query after 15 Min the output would be
When I run the query at 15:54
Identifier Count Time
A 21 15:54
B 30 15:54
I am trying to achieve as follows which is what is the increment count for each time stamp.
Indicator Count Time
A 10 15:24 ---- Initial Count
B 15 15:24
A 6 15:39 ---- After 15 Min Execution ( 16 - 10)
B 2 15:39 --- (17-15)
A 5 15:54 ---- After 15 Min Execution (21-16)
B 13 15:54 ---- i.e.(30-17 )
I will appreciate you time and skills.
Many Thanks in Advance.

You know the drill: please give us DDL, sample data (as INSERT statements) and desired results.

Y.B. SSChampion Points: 11652 More actions · Answer 2

Well you need to specify a time frame if you only want the last 15 minutes.

i.e.

WHERE myDate >= DATEADD(mi,-15,GETDATE())

However, depending on your requirements that may not necessarily be your best solution. For example if you only wanted totals since the last time you checked I might log the most recent transaction by some unique identifier as a watermark for the next time the query runs. Please add some code and details and we'll be able to help you more.

SELECT quote FROM brain WHERE original = 1
0 rows returned

Sangeeth Raj Hall of Fame Points: 3541 More actions · Answer 3

Hi,

The sample script would be like and we do have primary keys and foreign keys on Fact Table.

select 'US' As CountryIndicator, sum(MoneyReceived) MoneyReceived, getdate() DateTime
from FctTranscations F
Inner join DimDate D on D.DateKey = F.DateKey
Inner Join DimLocaton l on l.locationkey = f.locationkey
inner join DimTime T on T.TimeKey = F.TimeKey
Inner join DimMethod M on M.MethodKey = F.MethodKey
where l.Locaton = 'US'
and M.Method = 'Direct'

Union All

select 'US' As CountryIndicator, sum(MoneyReceived) MoneyReceived, getdate() DateTime
from FctTranscations F
Inner join DimDate D on D.DateKey = F.DateKey
Inner Join DimLocaton l on l.locationkey = f.locationkey
inner join DimTime T on T.TimeKey = F.TimeKey
Inner join DimMethod M on M.MethodKey = F.MethodKey
where l.Locaton = 'UK'
and M.Method = 'InDirect'

Union All

select 'Canada' As CountryIndicator, sum(MoneyReceived) MoneyReceived, getdate() DateTime
from FctTranscations F
Inner join DimDate D on D.DateKey = F.DateKey
Inner Join DimLocaton l on l.locationkey = f.locationkey
inner join DimTime T on T.TimeKey = F.TimeKey
Inner join DimMethod M on M.MethodKey = F.MethodKey
where l.Locaton = 'Canada'
and M.Method like '%Online'

Output of above script:

CountryIndicator MoneyReceived DateTime
US 15000 15:00
UK 10000 15:00
Canada 8000 15:00

The query when I run at 15:15

CountryIndicator MoneyReceived DateTime
US 25000 15:15
UK 20000 15:15
Canada 9500 15:15

The query when I run at 15:30

CountryIndicator MoneyReceived DateTime
US 30000 15:30
UK 26000 15:30
Canada 10000 15:30

The Required output

CountryIndicator MoneyReceived DateTime
US 15000 15:00 Initial Money
UK 10000 15:00 Initial money
Canada 8000 15:00 Initial money
US 10000 15:15 First Run Time stamp , Difference between Initial Money and next run.
UK 10000 15:15
Canada 1500 15:15
US 5000 15:30
UK 6000 15:30
Canada 500 15:30

Thom A SSC Guru Points: 99143 More actions · Answer 4

Still no DDL, so I've had to guess your field names, and untested as no DLM, but maybe:
WITH CTE AS( SELECT l.Locaton AS CountryIndicator, MoneyReceived AS MoneyReceived, CAST(D.DateValue AS datetime) + CAST(T.TimeValue AS datetime) AS TransactionDateTime FROM FctTranscations F INNER JOIN DimDate D on D.DateKey = F.DateKey INNER JOIN DimLocaton l on l.locationkey = f.locationkey INNER JOIN DimTime T on T.TimeKey = F.TimeKey INNER JOIN DimMethod M on M.MethodKey = F.MethodKey WHERE (l.Locaton = 'US' AND M.Method = 'Direct') OR (l.Locaton = 'UK' AND M.Method = 'InDirect') OR (l.Locaton = 'Canada' AND M.Method LIKE '%Online')) SELECT CountryIndicator, SUM(MoneyReceived) AS MoneyReceived, DATEADD(MINUTE, DATEDIFF(MINUTE, '2000', TransactionDateTime) - (DATEDIFF(MINUTE, '2000', TransactionDateTime) % 5),0) AS TransactionDateTime FROM CTE GROUP BY CountryIndicator, DATEADD(MINUTE, DATEDIFF(MINUTE, '2000', TransactionDateTime) - (DATEDIFF(MINUTE, '2000', TransactionDateTime) % 5),0);

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Thom A SSC Guru Points: 99143 More actions · Answer 5

Duplicate of topic https://www.sqlservercentral.com/Forums/1857023/Every-day-count-of-Inventory?Update=1#bm1857267. Please direct further answers there.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Sangeeth Raj Hall of Fame Points: 3541 More actions · Answer 6

Y.B. - Wednesday, February 8, 2017 8:56 AM
Well you need to specify a time frame if you only want the last 15 minutes.
i.e.
WHERE myDate >= DATEADD(mi,-15,GETDATE())
However, depending on your requirements that may not necessarily be your best solution. For example if you only wanted totals since the last time you checked I might log the most recent transaction by some unique identifier as a watermark for the next time the query runs. Please add some code and details and we'll be able to help you more.

Hi Y.B,

Your answer provided am good hint to my problem but If I want to achieve in where condition like this Eg: WHERE max(myDate) >= DATEADD(mi,-15,max(myDate)
But to use above condition, I have got an error to use group by and Having clause But I don't want to use group by condition in my query as I am trying to achieve only count and current time stamp when the query is executed. Could you help me with this.

Eg:

Select count(Id),getdate()
from Table
where mydate between DATEADD(mi,-15,max(myDate) and max(mydate)

Many Thanks

Phil Parkin SSC Guru Points: 247180 More actions · Answer 7

Sangeeth878787 - Thursday, February 9, 2017 9:06 AM
Y.B. - Wednesday, February 8, 2017 8:56 AM
Well you need to specify a time frame if you only want the last 15 minutes.
i.e.
WHERE myDate >= DATEADD(mi,-15,GETDATE())
However, depending on your requirements that may not necessarily be your best solution. For example if you only wanted totals since the last time you checked I might log the most recent transaction by some unique identifier as a watermark for the next time the query runs. Please add some code and details and we'll be able to help you more.
Hi Y.B,
Your answer provided am good hint to my problem but If I want to achieve in where condition like this Eg: WHERE max(myDate) >= DATEADD(mi,-15,max(myDate)
But to use above condition, I have got an error to use group by and Having clause But I don't want to use group by condition in my query as I am trying to achieve only count and current time stamp when the query is executed. Could you help me with this.
Eg:
Select count(Id),getdate()
from Table
where mydate between DATEADD(mi,-15,max(myDate) and max(mydate)
Many Thanks

Here you go (link).

Thom A SSC Guru Points: 99143 More actions · Answer 8

Phil Parkin - Thursday, February 9, 2017 9:11 AM
Here you go (link).

I think this battle is lost, Phil. But I shall salute your resolution to "fight" on!

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Sangeeth Raj Hall of Fame Points: 3541 More actions · Answer 9

HI Thom, Phil,

Sorry I was on holiday from Friday, I just came back to today morning, I have just checked how to post the data. I am glad pointing me in correct direction to post the question.

Thank you