February 8, 2017 at 4:01 am
Hi,
we have an Inventory Transactional Fact Table, which provides count of inventory according to particular product.
like
| Date | Items |
| 8th Feb | 100 |
| 9th Feb | 200 |
| 10th Feb | 300 |
| 11th Feb | 400 |
| 12th Feb | 600 |
| 13th Feb | 100 |
| 14th Feb | 150 |
| 15th Feb | 700 |
| 16th Feb | 100 |
| 17th Feb | 200 |
| 18th Feb | 400 |
| 19th Feb | 500 |
| 20th Feb | 250 |
| 21st Feb | 350 |
| 22nd Feb | 140 |
| Total | 4490 |
Now we got requirement to see what's the how many items are added between of every 7 days only instead of total count until that day. could some one give some suggestions on it. I believe this would be something like Periodic Snapshot Table .
like
8th Feb 100
15th Feb 2450
22nd Feb 1940
Many Thanks in Advance.
February 8, 2017 at 4:19 am
Sangeeth878787 - Wednesday, February 8, 2017 4:01 AMHi,we have an Inventory Transactional Fact Table, which provides count of inventory according to particular product.
like
Date Items 8th Feb 100 9th Feb 200 10th Feb 300 11th Feb 400 12th Feb 600 13th Feb 100 14th Feb 150 15th Feb 700 16th Feb 100 17th Feb 200 18th Feb 400 19th Feb 500 20th Feb 250 21st Feb 350 22nd Feb 140 Total 4490 Now we got requirement to see what's the how many items are added between of every 7 days only instead of total count until that day. could some one give some suggestions on it. I believe this would be something like Periodic Snapshot Table .
like
8th Feb 100
15th Feb 2450
22nd Feb 1940Many Thanks in Advance.
;WITH SomeSampleData AS (
SELECT
MyDate = CAST(DATEADD(DAY,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),GETDATE()-3) AS date)
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n)
)
SELECT
MyDate,
Grouper = DATEDIFF(DAY,2,MyDate)/7*7
FROM SomeSampleData
-- Second parameter of DATEDIFF configures the first day of the week:
-- 0 for monday, 1 for tuesday, 2 for wednesday etc
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 8, 2017 at 4:45 am
The end of Chris's solution appears to have got gobbled up by the hungry forum software. It looks as if he was going to use GROUP BY. Here's an alternative, using window functions. It relies on there being exactly one row per date. If that's not what you have, you'll need to join to a table of numbers or dates.
WITH ITFT AS (
SELECT
InvDate
, Items
FROM (VALUES
(CAST('8 Feb 2017' AS date),100),
(CAST('9 Feb 2017' AS date),200),
(CAST('10 Feb 2017' AS date),300),
(CAST('11 Feb 2017' AS date),400),
(CAST('12 Feb 2017' AS date),600),
(CAST('13 Feb 2017' AS date),100),
(CAST('14 Feb 2017' AS date),150),
(CAST('15 Feb 2017' AS date),700),
(CAST('16 Feb 2017' AS date),100),
(CAST('17 Feb 2017' AS date),200),
(CAST('18 Feb 2017' AS date),400),
(CAST('19 Feb 2017' AS date),500),
(CAST('20 Feb 2017' AS date),250),
(CAST('21 Feb 2017' AS date),350),
(CAST('22 Feb 2017' AS date),140)
) v (InvDate, Items)
)
SELECT
InvDate
, Items
, SUM(Items) OVER (ORDER BY InvDate ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS Last7Days
FROM ITFT
ORDER BY InvDate
John
February 8, 2017 at 4:53 am
John Mitchell-245523 - Wednesday, February 8, 2017 4:45 AMThe end of Chris's solution appears to have got gobbled up by the hungry forum software. It looks as if he was going to use GROUP BY. Here's an alternative, using window functions. It relies on there being exactly one row per date. If that's not what you have, you'll need to join to a table of numbers or dates.
WITH ITFT AS (
SELECT
InvDate
, Items
FROM (VALUES
(CAST('8 Feb 2017' AS date),100),
(CAST('9 Feb 2017' AS date),200),
(CAST('10 Feb 2017' AS date),300),
(CAST('11 Feb 2017' AS date),400),
(CAST('12 Feb 2017' AS date),600),
(CAST('13 Feb 2017' AS date),100),
(CAST('14 Feb 2017' AS date),150),
(CAST('15 Feb 2017' AS date),700),
(CAST('16 Feb 2017' AS date),100),
(CAST('17 Feb 2017' AS date),200),
(CAST('18 Feb 2017' AS date),400),
(CAST('19 Feb 2017' AS date),500),
(CAST('20 Feb 2017' AS date),250),
(CAST('21 Feb 2017' AS date),350),
(CAST('22 Feb 2017' AS date),140)
) v (InvDate, Items)
)
SELECT
InvDate
, Items
, SUM(Items) OVER (ORDER BY InvDate ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS Last7Days
FROM ITFT
ORDER BY InvDateJohn
Aha - your solution is quite different to mine, John. You're looking at the last seven days up to the current date, I'm converting a date into a week and grouping by week. I'm not sure which solution the OP wants.
The GROUP BY wasn't chopped off the end of my post - it was omitted. I figured out that the OP would probably get this part, given how to convert a series of dates into weeks, with a configurable start day - which for most would be the tricky part.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 8, 2017 at 7:36 am
Hi, I am glad for replies, this is an example of requirement but I need to achieve the same thing for every 15 minutes, what is the updated count between last 15 min and now, this will continue for every 15 min and I need to send this data to others in CSV format.
February 8, 2017 at 7:51 am
Without any table DDL and sample data from you, we're just guessing. But, assuming you have exactly one row for each 15 minute period, all you need to do is change the 6 in my query to a 671 (7 days x 24 hours x 4 quarters - 1).
If you want to send in csv fomrat, you can write an SSIS package to do the export, or (which is probably a lot simpler) use bcp.
John
February 8, 2017 at 7:59 am
I am sorry I would have inform early, For simplicity I have written the dates as 8th Feb, 9th Feb, but In my Fact Table and Date table we have proper Date Column, all I am trying to approach snap shot fact table mechanism.
February 8, 2017 at 8:02 am
I'm not sure what you mean by that, but if you need any more help, please provide table DDL (CREATE TABLE statement), sample data (INSERT statement(s)) and expected results based on that sample data.
John
February 9, 2017 at 4:18 am
Hi,
I am glad for your replies, to be prescise I am looking to achieve the following example
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 FctTranscations2 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 FctTranscations3 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'
---- Note: In the above query, I can't combine each select query as one due to requirement to apply different multiple factors but at the end I have to Union All as they have same number of columns and headings.
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
February 9, 2017 at 4:35 am
Just noticed you duplicated this topic here: https://qa.sqlservercentral.com/Forums/1857105/RE-Count-Difference-for-Every-15-Min#bm1857132 I've answered your questions there, I believe. Please direct your answer to this topic though, don't create two topics for one question.
Edit:
My answer from other post:
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) % 15),0) AS TransactionDateTime
FROM CTE
GROUP BY CountryIndicator,
DATEADD(MINUTE, DATEDIFF(MINUTE, '2000', TransactionDateTime) - (DATEDIFF(MINUTE, '2000', TransactionDateTime) % 15),0);
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 9, 2017 at 4:38 am
Still no CREATE TABLE or INSERT statements, either in this topic or the new one you started to ask what appears to be exactly the same question. But this should work. You'll notice that it is indeed possible to combine the three queries.
SELECT
CountryIndicator
, MoneyReceived
, [DateTime]
, MoneyReceived - LAG(MoneyReceived,1,0) OVER (
PARTITION BY CountryIndicator
ORDER BY [DateTime]
) AS MoneyDifference
FROM (
select 'Canada' As CountryIndicator, sum(MoneyReceived) MoneyReceived, getdate() DateTime
from FctTranscations3 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')
OR (l.Locaton = 'UK' and M.Method = 'InDirect')
OR (l.Locaton = 'US' and M.Method = 'Direct')
) Transactions
John
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply