this is my table look like

i have to show data Part Name wise

Part Name    Time          Incoming    Outgoing    Transfer
---------    ------------  --------    --------    ---------
Sales        9:00 - 9:30      1           4           6
Sales        9:30 - 10:00     4           5           7
Sales        10:30 - 11:00    8           7           2
Michel       9:00 - 9:30      2           40          16
Michel       9:30 - 10:00     5           15          17

i have to show data party wise and time wise

i am not very good in complex sql development but got a help from one guy. his sql look like

DECLARE     @StartTime datetime = '2015-06-09 09:00:00',
            @EndTime datetime = '2015-06-09 18:00:00',
            @Interval int = 30 -- this can be changed.

DECLARE     @PartyIntervals TABLE (
            Party1Name      VARCHAR(500),
            StartRange      DATETIME,
            EndRange        DATETIME) 

DECLARE     @CSVIntervals   TABLE (
            Party1Name      VARCHAR(500),
            Incoming        INT,
            StartRange      DATETIME) 

;WITH cSequence AS
(
    SELECT
       @StartTime AS StartRange, 
       DATEADD(MINUTE, @Interval, @StartTime) AS EndRange
    UNION ALL
    SELECT
      EndRange, 
      DATEADD(MINUTE, @Interval, EndRange)
    FROM cSequence 
    WHERE DATEADD(MINUTE, @Interval, EndRange) < @EndTime
)


INSERT INTO @PartyIntervals
SELECT      Party1Name, 
            StartRange,
            EndRange 
FROM (SELECT DISTINCT Party1Name FROM tridip_Interval) A, cSequence ORDER BY Party1Name;
--SELECT * FROM @PartyIntervals ORDER BY Party1Name

INSERT INTO @CSVIntervals
SELECT  Party1Name,
        count(*) as incoming,
        CONVERT(smalldatetime, ROUND(CAST(Convert(datetime, [Call Start]) AS float) * 48.0,0,1)/48.0) AS StartRange
FROM    tridip_Interval
WHERE   direction='I'
GROUP   BY CONVERT(smalldatetime, ROUND(CAST(Convert(datetime, [Call Start]) AS float) * 48.0,0,1)/48.0), Party1Name

SELECT  P.Party1Name,
        P.StartRange,
        P.EndRange,
        IsNull(Incoming , 0) AS Incoming
FROM    @PartyIntervals P LEFT OUTER JOIN 
        @CSVIntervals A ON  A.Party1Name = P.Party1Name AND P.StartRange = A.StartRange
ORDER BY  P.Party1Name,P.StartRange

this calculation is not clear

CONVERT(smalldatetime, ROUND(CAST(Convert(datetime, [Call Start]) AS float) * 48.0,0,1)/48.0) AS StartRange
why 48 is used ?

see this part too

INSERT INTO @CSVIntervals
SELECT  Party1Name,
        count(*) as incoming,
        CONVERT(smalldatetime, ROUND(CAST(Convert(datetime, [Call Start]) AS float) * 48.0,0,1)/48.0) AS StartRange
FROM    tridip_Interval
WHERE   direction='I'
GROUP   BY CONVERT(smalldatetime, ROUND(CAST(Convert(datetime, [Call Start]) AS float) * 48.0,0,1)/48.0), Party1Name

here only incoming related data is getting inserted in CSVIntervals table but i need to also insert outgoing and transfer data for all part in same sql. this is not getting clear how to add more sql to insert outgoing and transfer related data?

if anyone could understand my requirement then please help me to form the sql as a result i will get my desire result.

thanks

Hi,

that number 48 is number of half hours in day 24 * 2.

I would write something like this instead.

DECLARE @t TABLE(
	CallStar datetime,
	Direction char(1),
	PartyName varchar(100))

INSERT INTO @t VALUES
('20150609 08:02:51','I','VM Channel 1'),
('20150609 08:04:14','I','VM Channel 1'),
('20150609 08:02:51','O','VM Channel 1'),
('20150609 08:04:14','O','VM Channel 1')

SELECT
	PartyName,
	DATEADD(minute, DATEDIFF(minute,0,CallStar) / 30 * 30, 0) AS "DateStart",
	DATEADD(minute, 30, DATEADD(minute, DATEDIFF(minute, 0, CallStar) / 30 * 30, 0)) AS "DateEnd",
	SUM(CASE WHEN Direction = 'I' THEN 1 ELSE 0 END) AS "Incoming",
	SUM(CASE WHEN Direction = 'O' THEN 1 ELSE 0 END) AS "Outgoing",
	SUM(CASE WHEN Direction = 'T' THEN 1 ELSE 0 END) AS "Transfer"
FROM @t
GROUP BY
	PartyName,
	DATEADD(minute, DATEDIFF(minute,0,CallStar) / 30 * 30, 0)

• Edited by Steelleg4 16 hours 21 minutes ago

Just to take Steelleg's solution one step further:

DECLARE @t TABLE(CallStar datetime,	Direction char(1), PartyName varchar(100))

INSERT INTO @t VALUES
('2015-06-09 08:02:51','I','VM Channel 1'),
('2015-06-09 08:04:14','I','VM Channel 1'),
('2015-06-09 08:02:51','O','VM Channel 1'),
('2015-06-09 08:02:51','I','VM Channel 1'),
('2015-06-09 08:34:14','I','VM Channel 1'),
('2015-06-09 18:02:51','I','VM Channel 1'),
('2015-06-09 18:04:14','I','VM Channel 1'),
('2015-06-09 18:02:51','O','VM Channel 1'),
('2015-06-09 18:02:51','T','VM Channel 1'),

('2015-06-09 01:02:51','I','VM Channel 2'),
('2015-06-09 02:04:14','I','VM Channel 2'),
('2015-06-09 03:02:51','O','VM Channel 2'),
('2015-06-09 04:02:51','I','VM Channel 2'),
('2015-06-09 05:34:14','I','VM Channel 2'),
('2015-06-09 16:02:51','I','VM Channel 2'),
('2015-06-09 17:04:14','I','VM Channel 2'),
('2015-06-09 18:02:51','O','VM Channel 2'),
('2015-06-09 19:02:51','T','VM Channel 2')

SELECT PartyName, LEFT(CONVERT(VARCHAR,DATEADD(minute, DATEDIFF(minute,0,CallStar) / 30 * 30, 0),108),5) + ' - ' + LEFT(CONVERT(VARCHAR,DATEADD(minute, 30, DATEADD(minute, DATEDIFF(minute, 0, CallStar) / 30 * 30, 0)),108),5) AS groupName,	DATEADD(minute, DATEDIFF(minute,0,CallStar) / 30 * 30, 0) AS DateStart,	DATEADD(minute, 30, DATEADD(minute, DATEDIFF(minute, 0, CallStar) / 30 * 30, 0)) AS DateEnd, SUM(CASE WHEN Direction = 'I' THEN 1 ELSE 0 END) AS Incoming, SUM(CASE WHEN Direction = 'O' THEN 1 ELSE 0 END) AS Outgoing, SUM(CASE WHEN Direction = 'T' THEN 1 ELSE 0 END) AS Transfer
  FROM @t
GROUP BY PartyName,	DATEADD(minute, DATEDIFF(minute,0,CallStar) / 30 * 30, 0)
ORDER BY PartyName, DateStart

thanks i will check your sql and let u know tomorrow.

please explain meaning of below code

DATEADD(minute, DATEDIFF(minute,0,CallStar) / 30 * 30, 0) AS "DateStart",
DATEADD(minute, 30, DATEADD(minute, DATEDIFF(minute, 0, CallStar) / 30 * 30, 0)) AS "DateEnd",

why u r dividing by 30*30 = 600 ?

• Edited by Mou_kolkata 12 hours 57 minutes ago

@Steelleg4 thanks for reply

I asked : this calculation is not clear

CONVERT(smalldatetime, ROUND(CAST(Convert(datetime, [Call Start]) AS float) * 48.0,0,1)/48.0) AS StartRange
why 48 is used ?

you explain : that number 48 is number of half hours in day 24 * 2.
but still not clear what u try to say?

would explain it in more detail. thanks

The purpose of the expression is to break each time down into it's half hour segment. It's a different form of rounding:

DECLARE @times TABLE (dateTimeCol DATETIME)
INSERT INTO @times (dateTimeCol) VALUES 
(CURRENT_TIMESTAMP),(CURRENT_TIMESTAMP+.2),(CURRENT_TIMESTAMP+.1),(CURRENT_TIMESTAMP+.4)

SELECT *, CONVERT(smalldatetime, ROUND(CAST(Convert(datetime, dateTimeCol) AS float) * 48.0,0,1)/48.0)
  FROM @times

@Steelleg4 thanks for reply

I asked : this calculation is not clear

CONVERT(smalldatetime, ROUND(CAST(Convert(datetime, [Call Start]) AS float) * 48.0,0,1)/48.0) AS StartRange
why 48 is used ?

you explain : that number 48 is number of half hours in day 24 * 2.
but still not clear what u try to say?

would explain it in more detail. thanks

Hi,

First. Datetime value is converted to number. Its same as in Excel. For example date value 2015-09-09 06:00 is converted to number 42254.25

42254 is number of days from 1900-01-01 and 0.25 is part of day.

If you would round 42254.25 to whole number and then to date you would get 2015-09-09 00:00

Because you want to round it to nearest half hour you need to multiplay that number by 48. Round it to whole number. And then divide by 48.