Sql Partitioning

Question

I have Sql Table data and i need to filter only the Consecutive dates blocks as i highlighted on image below..

enter image description here .

and i need to add custom rates for each row on that selected blocks(this rate can display with separate column on out put).If there is more than 6 rows captured then $200 apply for each column of that block.if it is less than 6 ,it will be $125.The out put should be like this enter image description here

And it should group by EmpID. i need to get the out put using MSSQL. Can any one help me

this is what i have done through the sql view

ALTER   view [dbo].[vw_Test2] AS

SELECT   
         tbl2.ID as Tbl2ID,
         tbl1.[EmpID],
         tbl1.[ExpInDateTime] as Tbl1ExpDate,
         tbl2.[ExpInDateTime] as Tbl2ExpDate,
            case when(CONVERT(date,tbl1.[ActInDateTime]) = CONVERT(date, DATEADD(DAY,1,tbl2.[ExpInDateTime]))) then
                 1
            else 0 
            end as Token
from [dbo].[vw_Test] tbl1 join [dbo].[vw_Test]  tbl2 
on tbl1.ID=(tbl2.ID+1) 
GO

only thing is i have to do this using SQL views

Please give some more info, for example wanted result when input as shown! — jarlh, Feb 10 '15 at 08:20
Refer this link http://stackoverflow.com/questions/20402089/detect-consecutive-dates-ranges-using-sql — DevelopmentIsMyPassion, Feb 10 '15 at 08:38
I did the sql partitioning .but i cannot get the required out put — CodeMind, Feb 10 '15 at 11:48
can you post what you tried? Please post sql fiddle so its easy for us — DevelopmentIsMyPassion, Feb 10 '15 at 13:46
ALTER view [dbo].[vw_Test2] AS SELECT tbl2.ID as Tbl2ID, tbl1.[EmpID], tbl1.[ExpInDateTime] as Tbl1ExpDate, tbl2.[ExpInDateTime] as Tbl2ExpDate, case when(CONVERT(date,tbl1.[ActInDateTime]) = CONVERT(date, DATEADD(DAY,1,tbl2.[ExpInDateTime]))) then 1 else 0 end as Token from [dbo].[vw_Test] tbl1 join [dbo].[vw_Test] tbl2 on tbl1.ID=(tbl2.ID+1) GO — CodeMind, Feb 11 '15 at 05:22

score 1 · Answer 1 · answered Feb 19 '15 at 03:32

Please try this as a view:

ALTER VIEW [dbo].[vw_Test2] AS
    WITH PreResult AS (
        SELECT p.Id,p.EmpID,p.[DateTime],CASE WHEN LEAD(p.diff)OVER(ORDER BY p.Id) > 1 OR LEAD(p.EmpID)OVER(ORDER BY p.Id)<>p.EmpID THEN 1 ELSE 0 END StartNewGroup
        FROM (
            SELECT t.Id,t.EmpID,t.[DateTime], COALESCE(DATEDIFF(day,LAG(t.[DateTime])OVER(PARTITION BY t.EmpID ORDER BY t.Id),t.[DateTime]),1) [diff]   
            FROM [dbo].[vw_Test] t
        ) p
    )
    SELECT r.Id,r.EmpID,r.[DateTime]
        ,CASE WHEN COUNT(*)OVER(PARTITION BY r.NewGroup ORDER BY r.NewGroup) >= 6 THEN 250 ELSE 125 END [Rate]
    FROM (
        SELECT b.Id,b.EmpID,b.[DateTime],1+COALESCE((SELECT SUM(a.StartNewGroup) FROM PreResult a WHERE a.Id<b.Id),0) NewGroup
        FROM PreResult b
    ) r
GO

There is also query to play with:

CREATE TABLE #Test (Id BIGINT IDENTITY(1,1),EmpID BIGINT, [DateTime] DATETIME)

INSERT INTO #Test (EmpID,[DateTime]) VALUES (5,'20150106'),(5,'20150107'),(5,'20150109'),
    (5,'20150110'),(5,'20150126'),(5,'20150127'),
    (5,'20150128'),(5,'20150129'),(5,'20150130'),
    (5,'20150131'),(10,'20121203'),(10,'20121204'),
    (10,'20121205'),(10,'20121206'),
    (10,'20121207'),(10,'20121208'),(10,'20121209')

;WITH PreResult AS (
    SELECT p.Id,p.EmpID,p.[DateTime],CASE WHEN LEAD(p.diff)OVER(ORDER BY p.Id) > 1 OR LEAD(p.EmpID)OVER(ORDER BY p.Id)<>p.EmpID THEN 1 ELSE 0 END StartNewGroup
    FROM (
        SELECT t.Id,t.EmpID,t.[DateTime], COALESCE(DATEDIFF(day,LAG(t.[DateTime])OVER(PARTITION BY t.EmpID ORDER BY t.Id),t.[DateTime]),1) [diff]   
        FROM #Test t
    ) p
)
SELECT r.Id,r.EmpID,r.[DateTime]
    ,CASE WHEN COUNT(*)OVER(PARTITION BY r.NewGroup ORDER BY r.NewGroup) >= 6 THEN 250 ELSE 125 END [Rate]
FROM (
    SELECT b.Id,b.EmpID,b.[DateTime],1+COALESCE((SELECT SUM(a.StartNewGroup) FROM PreResult a WHERE a.Id<b.Id),0) NewGroup
    FROM PreResult b
) r

DROP TABLE #Test

Please let me know if you have any questions.

Sql Partitioning

1 Answers1