I have the following data
CREATE TABLE [dbo].[Test](
[CustId] [int] NULL,
[Spend] [money] NULL,
[TimeOdSpent] [datetime] NULL,
[ID] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Test] ON
GO
INSERT [dbo].[Test] ([CustId], [Spend], [TimeOdSpent], [ID])
VALUES (11, 400.0000, CAST(N'2016-10-27 10:00:00.000' AS DateTime), 1)
INSERT [dbo].[Test] ([CustId], [Spend], [TimeOdSpent], [ID])
VALUES (11, 200.0000, CAST(N'2016-10-27 11:00:00.000' AS DateTime), 2)
INSERT [dbo].[Test] ([CustId], [Spend], [TimeOdSpent], [ID])
VALUES (11, 400.0000, CAST(N'2016-10-28 09:00:00.000' AS DateTime), 3)
INSERT [dbo].[Test] ([CustId], [Spend], [TimeOdSpent], [ID])
VALUES (11, 500.0000, CAST(N'2016-10-28 16:00:00.000' AS DateTime), 4)
GO
SET IDENTITY_INSERT [dbo].[Test] OFF
Expected Result should be like this
1 2016-10-27 11:00:00.000 600
2 2016-10-28 09:00:00.000 1000
3 2016-10-28 16:00:00.000 900
I want to find out the instances where the spend Totals > 500 within a 24 hour period. Being trying to write a windowing query without luck
This is the sort of thing I was looking for. I used the Sales.SalesOrderHeader table from AdventureWorks Instead of my simple table above
;WITH cte1 as
(
select
LAG(ShipDate) OVER(PARTITION By SAlesPersonId ORDER BY ShipDate) ShipDateBefore,ShipDate, SalesPersonID,SubTotal,CAST(ShipDate as Date) Date
from Sales.SalesOrderHeader
where CAST(ShipDate as DATE)<'20080710' and SalesPersonID IS NOT NULL
),cte2 as
(Select * ,DATEDIFF(ss,ShipDateBefore,ShipDate) as DiffinDays
from cte1
), cte3 as (
select * ,SUM(DiffinDays) OVER(Partition BY SalesPersonId ORDER BY ShipDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as RunningTime
from cte2
),cte4 as
(select
*,ISNULL(CAST((RunningTime / 86400.00) AS INT),0) Cycle
FROM cte3
)
SELECT
SalesPersonID ,SUM(SubTotal)Total,MIN(ShipDate)DurationStart,MAX(ShipDate)DurationStart
from cte4
GROUP by SalesPersonID,Cycle
Having SUM(SubTotal) > 100000.00