August 17, 2012 at 8:34 am
trying to group this data by week instead of day.....SQL and I don't really communicate well, so any help would be great.
DECLARE @RecordType varchar(010), @LogonRecordType varchar(010), @StartDate datetime, @EndDate datetime
SET @RecordType = 'DAILY'
SET @LogonRecordType = 'INTERVAL'
SET @StartDate = '05/01/2012'
SET @EndDate = '08/31/2012'
------------------------------------
SELECT
DAS.Date
,DAS.Split
,LI.Client1 as Client
,DAS.PunchHours
,DAS.LogTime
,DAS.Ready
,DAS.Connect
,DAS.Typing
,DAS.Idle
,DAS.Preview
,DAS.PreviewDial
,SI.Accounts
,SI.Worked
,SI.Attempts
,(DAT.C0+DAT.C1+DAT.C2+DAT.C3+DAT.C4+DAT.C5+DAT.C6+DAT.C7
+DAT.C8+DAT.C9+DAT.CQ+DAT.CR+DAT.CS+DAT.CT+DAT.CU+DAT.CV
+DAT.CW+DAT.CX+DAT.CY+DAT.CZ+DAT.MA+DAT.MM+DAT.TN+DAT.TB
+DAT.T3+DAT.TR+DAT.BL+DAT.AB) AS Calls
,SI.HeldCalls
,SI.HeldDur
,SI.NC
,SI.BN
,SI.AH
,SI.IO
,SI.DD
,SI.HU
,SI.NB
,SI.VC
,SI.IC
,SI.TT
,SI.AA
,SI.RO
,SI.NA
,SI.BZ
,SI.BL
,DAT.C0
,DAT.C1
,DAT.C2
,DAT.C3
,DAT.C4
,DAT.C5
,DAT.C6
,DAT.C7
,DAT.C8
,DAT.C9
,DAT.CQ
,DAT.CR
,DAT.CS
,DAT.CT
,DAT.CU
,DAT.CV
,DAT.CW
,DAT.CX
,DAT.CY
,DAT.CZ
,DAT.MA
,DAT.MM
,DAT.TN
,DAT.TB
,DAT.T3
,DAT.TR
,DAT.BL
,DAT.AB
,(DAT.C0+DAT.C1+DAT.C2+DAT.C3+DAT.C4+DAT.C5+DAT.C6+DAT.C7
+DAT.C8+DAT.C9+DAT.CQ+DAT.CR+DAT.CS+DAT.CT+DAT.CU+DAT.CV+DAT.CX) AS RPCs
,(DAT.C0+DAT.C1+DAT.C2+DAT.C3+DAT.C4) AS Promises
,(DAT.C7+DAT.C8+DAT.C9+DAT.CQ+DAT.CR+DAT.CS+DAT.CT+DAT.CU+DAT.CV+DAT.CX) AS Exceptions
,convert(varchar,DAS.Date,101) + LI.Client1 AS DayClient
,convert(varchar(02),DATEPART(mm,DAS.Date)) + convert(varchar(04),DATEPART(yyyy,DAS.Date)) + LI.Client1 AS MonthClient
,convert(varchar(04),DATEPART(yyyy,DAS.Date)) + LI.Client1 AS YearClient
FROM
(
SELECT
Date
,Split
,RIGHT(CONVERT(NVARCHAR(20),
convert(varchar(05),sum(totHrs) / 3600) +':' +
convert(varchar(05),sum(totHrs) % 3600/60) + ':' +
convert(varchar(05),sum(totHrs) % 60),20),10) AS PunchHours
,RIGHT(CONVERT(NVARCHAR(20),
convert(varchar(05),sum(LogTime) / 3600) +':' +
convert(varchar(05),sum(LogTime) % 3600/60) + ':' +
convert(varchar(05),sum(LogTime) % 60),20),10) AS LogTime
,RIGHT(CONVERT(NVARCHAR(20),
convert(varchar(02),sum(Ready) / 3600) + ':' +
convert(varchar(02),sum(Ready) % 3600/60) + ':' +
convert(varchar(02),sum(Ready) % 60),20),10) AS Ready
,RIGHT(CONVERT(NVARCHAR(20),
convert(varchar(02),sum(Connect) / 3600) + ':' +
convert(varchar(02),sum(Connect) % 3600/60) + ':' +
convert(varchar(02),sum(Connect) % 60),20),10) AS Connect
,RIGHT(CONVERT(NVARCHAR(20),
convert(varchar(02),sum(Typing) / 3600) + ':' +
convert(varchar(02),sum(Typing) % 3600/60) + ':' +
convert(varchar(02),sum(Typing) % 60),20),10) AS Typing
,RIGHT(CONVERT(NVARCHAR(20),
convert(varchar(02),sum(Idle) / 3600) + ':' +
convert(varchar(02),sum(Idle) % 3600/60) + ':' +
convert(varchar(02),sum(Idle) % 60),20),10) AS Idle
,RIGHT(CONVERT(NVARCHAR(20),
convert(varchar(02),sum(Preview) / 3600) + ':' +
convert(varchar(02),sum(Preview) % 3600/60) + ':' +
convert(varchar(02),sum(Preview) % 60),20),10) AS Preview
,RIGHT(CONVERT(NVARCHAR(20),
convert(varchar(02),sum(PreviewDial) / 3600) + ':' +
convert(varchar(02),sum(PreviewDial) % 3600/60) + ':' +
convert(varchar(02),sum(PreviewDial) % 60),20),10) AS PreviewDial
FROM (
SELECT CASE WHEN DATENAME(weekday,x.Date) in ('Saturday') THEN DATEADD(day,DATEDIFF(day,1,x.Date),0)
WHEN DATENAME(weekday,x.Date) in ('Sunday') THEN DATEADD(day,DATEDIFF(day,2,x.Date),0)
ELSE x.Date
END AS Date
,x.Split
,CASE WHEN ISNULL(x.TotHrs,0) < LogTime
THEN (CASE WHEN DATENAME(weekday,x.Date) IN ('Saturday') THEN 4*3600
WHEN DATENAME(weekday,x.Date) IN ('Sunday') THEN 5*3600
ELSE (CASE WHEN LogTime > 28800 THEN 10*3600 ELSE 8*3600 END) END)
ELSE x.TotHrs END AS TotHrs
,x.LogTime
,x.Ready
,x.Connect
,x.Typing
,x.Idle
,x.Preview
,x.PreviewDial
FROM (
SELECT DATEADD(day,DATEDIFF(day,1,date_time),0) as Date
,Split
,sum(ph.totHrs) AS TotHrs
,sum(logon_duration) AS LogTime
,(sum(pready_dur)+sum(ready_dur)) AS Ready
,sum(cnx_dur) AS Connect
,sum(typing_dur) AS Typing
,sum(idle_dur) AS Idle
,sum(preview_dur) AS Preview
,sum(pdial_dur) AS PreviewDial
FROM dialer.Agent_States a
LEFT JOIN
(
SELECT ts.Date, PersonnelID, convert(int,sum(totHrs)*3600) as TotHrs
FROM tsTimeSaver_vw ts
LEFT JOIN SandBox.[SHERMAN\juhrinek].DialerAgentInfo DAI on DAI.TimeSaverName = ts.EmpNM
WHERE ts.Date between @StartDate and @EndDate
AND DAI.PersonnelID is not null
GROUP BY ts.Date, PersonnelID
) PH on PH.Date = DATEADD(day,DATEDIFF(day,1,date_time),0)
and PH.PersonnelID = a.Agent_ID
WHERE DATEADD(day,DATEDIFF(day,1,date_time),0) between @StartDate and @EndDate
AND record_type = @RecordType
AND Split NOT IN (1,2,80)
GROUP BY DATEADD(day,DATEDIFF(day,1,date_time),0)
,Split, ph.totHrs, logon_duration
) x
) y
GROUP BY Date
,Split) DAS
INNER JOIN
(
SELECT
Date
,Split
,sum(C0) as C0
,sum(C1) as C1
,sum(C2) as C2
,sum(C3) as C3
,sum(C4) as C4
,SUM(C5) as C5
,SUM(C6) as C6
,SUM(C7) as C7
,SUM(C8) as C8
,SUM(C9) as C9
,SUM(CQ) as CQ
,SUM(CR) as CR
,SUM(CS) as CS
,SUM(CT) as CT
,SUM(CU) as CU
,SUM(CV) as CV
,SUM(CW) as CW
,SUM(CX) as CX
,SUM(CY) as CY
,SUM(CZ) as CZ
,SUM(MA) as MA
,SUM(TN) as TN
,SUM(TB) as TB
,SUM(T3) as T3
,SUM(TR) as TR
,SUM(BL) as BL
,SUM(MM) as MM
,SUM(AB) as AB
FROM
(
SELECT
CASE WHEN DATENAME(weekday,Date) in ('Saturday') THEN DATEADD(day,DATEDIFF(day,1,Date),0)
WHEN DATENAME(weekday,Date) in ('Sunday') THEN DATEADD(day,DATEDIFF(day,2,Date),0)
ELSE Date
END AS Date
,Split
,C0
,C1
,C2
,C3
,C4
,C5
,C6
,C7
,C8
,C9
,CQ
,CR
,CS
,CT
,CU
,CV
,CW
,CX
,CY
,CZ
,MA
,TN
,TB
,T3
,TR
,BL
,MM
,AB
FROM
(
SELECT
DATEADD(day,DATEDIFF(day,1,date_time),0) as Date
,split_number as Split
,SUM([c0_count]) as C0
,SUM([c1_count]) as C1
,SUM([c2_count]) as C2
,SUM([c3_count]) as C3
,SUM([c4_count]) as C4
,SUM([c5_count]) as C5
,SUM([c6_count]) as C6
,SUM([c7_count]) as C7
,SUM([c8_count]) as C8
,SUM([c9_count]) as C9
,SUM([cq_count]) as CQ
,SUM([cr_count]) as CR
,SUM([cs_count]) as CS
,SUM([ct_count]) as CT
,SUM([cu_count]) as CU
,SUM([cv_count]) as CV
,SUM([cw_count]) as CW
,SUM([cx_count]) as CX
,SUM([cy_count]) as CY
,SUM([cz_count]) as CZ
,SUM([manual_count]) as MA
,SUM([no_answer_count]) as TN
,SUM([busy_count]) as TB
,SUM([three_tone_count]) as T3
,SUM([write_count]) as TR
,SUM([bad_line_count]) as BL
,SUM([message_machine_count]) as MM
,SUM([abandoned_count]) as AB
FROM dialer.rp_Agent_Transactions
WHERE DATEADD(day,DATEDIFF(day,1,date_time),0) between @StartDate and @EndDate
AND record_type = @RecordType
AND split_number NOT IN (1,2,80)
GROUP BY DATEADD(day,DATEDIFF(day,1,date_time),0)
,split_number
) x
) y
GROUP BY Date, Split) DAT ON DAT.Split = DAS.Split
AND DAT.Date = DAS.Date
INNER JOIN
(
SELECT
Date
,Split
,sum(Accounts) as Accounts
,sum(Worked) as Worked
,sum(Attempts) as Attempts
,sum(HeldCalls) as HeldCalls
,sum(HeldDur) as HeldDur
,SUM(NC) as NC
,SUM(BN) as BN
,SUM(AB) as AH
,SUM(IO) as 'IO'
,SUM(DD) as DD
,SUM(HU) as HU
,SUM(NB) as NB
,SUM(VC) as VC
,SUM(IC) as IC
,SUM(TT) as TT
,SUM(AA) as AA
,SUM(RO) as RO
,SUM(NA) as NA
,SUM(BZ) as BZ
,SUM(BL) as BL
FROM
(
SELECT
CASE WHEN DATENAME(weekday,Date) in ('Saturday') THEN DATEADD(day,DATEDIFF(day,1,Date),0)
WHEN DATENAME(weekday,Date) in ('Sunday') THEN DATEADD(day,DATEDIFF(day,2,Date),0)
ELSE Date
END AS Date
,Split
,ListName
,Accounts
,Worked
,Attempts
,HeldCalls
,HeldDur
,NC
,BN
,AB
,IO
,DD
,HU
,NB
,VC
,IC
,TT
,AA
,RO
,NA
,BZ
,BL
FROM
(
SELECT
DATEADD(day,DATEDIFF(day,7,date_time),0) as Date
,split_number as Split
,LEFT(REPLACE(contact_list_name,' ',''),8) as ListName
,max(total_records) as Accounts
,CASE WHEN max(pen.Worked) > max(total_records) THEN max(total_records)
ELSE max(pen.Worked)
END AS Worked
,sum(dials) as Attempts
,sum(outbound_held_calls) as HeldCalls
,sum(outbound_held_duration) as HeldDur
,SUM(not_called_count) as NC
,SUM(bad_number_count) as BN
,SUM(abandoned_outbound_count) as AB
,SUM(ineffective_other_count) as IO
,SUM(data_device_count) as DD
,SUM(hang_up_count) as HU
,SUM(network_busy_count) as NB
,SUM(vacant_code_count) as VC
,SUM(intercept_count) as IC
,SUM(three_tone_count) as TT
,SUM(answer_machine_count) as AA
,SUM(reorder_count) as RO
,SUM(no_answer_count) as NA
,SUM(busy_count) as BZ
,SUM(bad_line_count) as BL
FROM dialer.rp_system_info a
INNER JOIN
(
SELECT ListName, COUNT(Worked) as Worked, Date
FROM
(
SELECT LEFT(REPLACE(contact_list_name,' ',''),8) as ListName,account_number as Worked, DATEADD(day,DATEDIFF(day,0,DATEADD(hh,-4,time_of_contact)),0) as Date
FROM dialer.cl_contact_event_vw
WHERE DATEADD(day,DATEDIFF(day,0,DATEADD(hh,-4,time_of_contact)),0) between @StartDate and @EndDate
GROUP BY LEFT(REPLACE(contact_list_name,' ',''),8), account_number, DATEADD(day,DATEDIFF(day,0,DATEADD(hh,-4,time_of_contact)),0)
) x
GROUP BY ListName, Date
) pen on pen.ListName = LEFT(REPLACE(contact_list_name,' ',''),8) and pen.Date = DATEADD(day,DATEDIFF(day,1,date_time),0)
WHERE DATEADD(day,DATEDIFF(day,1,date_time),0) between @StartDate and @EndDate
AND record_type = @RecordType
AND split_number NOT IN (1,2,80)
GROUP BY DATEADD(day,DATEDIFF(day,7,date_time),0)
,split_number
,LEFT(REPLACE(contact_list_name,' ',''),8)
) x
) y
GROUP BY Date, Split) SI ON SI.Date = DAS.Date
AND SI.Split = DAS.Split
INNER JOIN SandBox.[SHERMAN\juhrinek].ListInfo LI ON LI.Split = DAS.Split
ORDER BY 1,2
August 17, 2012 at 8:49 am
jfowler 10337 (8/17/2012)
trying to group this data by week instead of day.....SQL and I don't really communicate well, so any help would be great.code removed from quote...
Couple of things.
One, when posting code in the forum please use the IFCode Shortcuts to mark the code block. In this case you would use the [ code="sql" ] and [ /code ] (no spaces inside the square brackets) so that you code will look like this:
DECLARE @RecordType varchar(010), @LogonRecordType varchar(010), @StartDate datetime, @EndDate datetime
SET @RecordType = 'DAILY'
SET @LogonRecordType = 'INTERVAL'
SET @StartDate = '05/01/2012'
SET @EndDate = '08/31/2012'
------------------------------------
SELECT
DAS.Date
,DAS.Split
,LI.Client1 as Client
,DAS.PunchHours
,DAS.LogTime
,DAS.Ready
,DAS.Connect
,DAS.Typing
,DAS.Idle
,DAS.Preview
,DAS.PreviewDial
,SI.Accounts
,SI.Worked
,SI.Attempts
,(DAT.C0+DAT.C1+DAT.C2+DAT.C3+DAT.C4+DAT.C5+DAT.C6+DAT.C7
+DAT.C8+DAT.C9+DAT.CQ+DAT.CR+DAT.CS+DAT.CT+DAT.CU+DAT.CV
+DAT.CW+DAT.CX+DAT.CY+DAT.CZ+DAT.MA+DAT.MM+DAT.TN+DAT.TB
+DAT.T3+DAT.TR+DAT.BL+DAT.AB) AS Calls
,SI.HeldCalls
,SI.HeldDur
,SI.NC
,SI.BN
,SI.AH
,SI.IO
,SI.DD
,SI.HU
,SI.NB
,SI.VC
,SI.IC
,SI.TT
,SI.AA
,SI.RO
,SI.NA
,SI.BZ
,SI.BL
,DAT.C0
,DAT.C1
,DAT.C2
,DAT.C3
,DAT.C4
,DAT.C5
,DAT.C6
,DAT.C7
,DAT.C8
,DAT.C9
,DAT.CQ
,DAT.CR
,DAT.CS
,DAT.CT
,DAT.CU
,DAT.CV
,DAT.CW
,DAT.CX
,DAT.CY
,DAT.CZ
,DAT.MA
,DAT.MM
,DAT.TN
,DAT.TB
,DAT.T3
,DAT.TR
,DAT.BL
,DAT.AB
,(DAT.C0+DAT.C1+DAT.C2+DAT.C3+DAT.C4+DAT.C5+DAT.C6+DAT.C7
+DAT.C8+DAT.C9+DAT.CQ+DAT.CR+DAT.CS+DAT.CT+DAT.CU+DAT.CV+DAT.CX) AS RPCs
,(DAT.C0+DAT.C1+DAT.C2+DAT.C3+DAT.C4) AS Promises
,(DAT.C7+DAT.C8+DAT.C9+DAT.CQ+DAT.CR+DAT.CS+DAT.CT+DAT.CU+DAT.CV+DAT.CX) AS Exceptions
,convert(varchar,DAS.Date,101) + LI.Client1 AS DayClient
,convert(varchar(02),DATEPART(mm,DAS.Date)) + convert(varchar(04),DATEPART(yyyy,DAS.Date)) + LI.Client1 AS MonthClient
,convert(varchar(04),DATEPART(yyyy,DAS.Date)) + LI.Client1 AS YearClient
FROM
(
SELECT
Date
,Split
,RIGHT(CONVERT(NVARCHAR(20),
convert(varchar(05),sum(totHrs) / 3600) +':' +
convert(varchar(05),sum(totHrs) % 3600/60) + ':' +
convert(varchar(05),sum(totHrs) % 60),20),10) AS PunchHours
,RIGHT(CONVERT(NVARCHAR(20),
convert(varchar(05),sum(LogTime) / 3600) +':' +
convert(varchar(05),sum(LogTime) % 3600/60) + ':' +
convert(varchar(05),sum(LogTime) % 60),20),10) AS LogTime
,RIGHT(CONVERT(NVARCHAR(20),
convert(varchar(02),sum(Ready) / 3600) + ':' +
convert(varchar(02),sum(Ready) % 3600/60) + ':' +
convert(varchar(02),sum(Ready) % 60),20),10) AS Ready
,RIGHT(CONVERT(NVARCHAR(20),
convert(varchar(02),sum(Connect) / 3600) + ':' +
convert(varchar(02),sum(Connect) % 3600/60) + ':' +
convert(varchar(02),sum(Connect) % 60),20),10) AS Connect
,RIGHT(CONVERT(NVARCHAR(20),
convert(varchar(02),sum(Typing) / 3600) + ':' +
convert(varchar(02),sum(Typing) % 3600/60) + ':' +
convert(varchar(02),sum(Typing) % 60),20),10) AS Typing
,RIGHT(CONVERT(NVARCHAR(20),
convert(varchar(02),sum(Idle) / 3600) + ':' +
convert(varchar(02),sum(Idle) % 3600/60) + ':' +
convert(varchar(02),sum(Idle) % 60),20),10) AS Idle
,RIGHT(CONVERT(NVARCHAR(20),
convert(varchar(02),sum(Preview) / 3600) + ':' +
convert(varchar(02),sum(Preview) % 3600/60) + ':' +
convert(varchar(02),sum(Preview) % 60),20),10) AS Preview
,RIGHT(CONVERT(NVARCHAR(20),
convert(varchar(02),sum(PreviewDial) / 3600) + ':' +
convert(varchar(02),sum(PreviewDial) % 3600/60) + ':' +
convert(varchar(02),sum(PreviewDial) % 60),20),10) AS PreviewDial
FROM (
SELECT CASE WHEN DATENAME(weekday,x.Date) in ('Saturday') THEN DATEADD(day,DATEDIFF(day,1,x.Date),0)
WHEN DATENAME(weekday,x.Date) in ('Sunday') THEN DATEADD(day,DATEDIFF(day,2,x.Date),0)
ELSE x.Date
END AS Date
,x.Split
,CASE WHEN ISNULL(x.TotHrs,0) < LogTime
THEN (CASE WHEN DATENAME(weekday,x.Date) IN ('Saturday') THEN 4*3600
WHEN DATENAME(weekday,x.Date) IN ('Sunday') THEN 5*3600
ELSE (CASE WHEN LogTime > 28800 THEN 10*3600 ELSE 8*3600 END) END)
ELSE x.TotHrs END AS TotHrs
,x.LogTime
,x.Ready
,x.Connect
,x.Typing
,x.Idle
,x.Preview
,x.PreviewDial
FROM (
SELECT DATEADD(day,DATEDIFF(day,1,date_time),0) as Date
,Split
,sum(ph.totHrs) AS TotHrs
,sum(logon_duration) AS LogTime
,(sum(pready_dur)+sum(ready_dur)) AS Ready
,sum(cnx_dur) AS Connect
,sum(typing_dur) AS Typing
,sum(idle_dur) AS Idle
,sum(preview_dur) AS Preview
,sum(pdial_dur) AS PreviewDial
FROM dialer.Agent_States a
LEFT JOIN
(
SELECT ts.Date, PersonnelID, convert(int,sum(totHrs)*3600) as TotHrs
FROM tsTimeSaver_vw ts
LEFT JOIN SandBox.[SHERMAN\juhrinek].DialerAgentInfo DAI on DAI.TimeSaverName = ts.EmpNM
WHERE ts.Date between @StartDate and @EndDate
AND DAI.PersonnelID is not null
GROUP BY ts.Date, PersonnelID
) PH on PH.Date = DATEADD(day,DATEDIFF(day,1,date_time),0)
and PH.PersonnelID = a.Agent_ID
WHERE DATEADD(day,DATEDIFF(day,1,date_time),0) between @StartDate and @EndDate
AND record_type = @RecordType
AND Split NOT IN (1,2,80)
GROUP BY DATEADD(day,DATEDIFF(day,1,date_time),0)
,Split, ph.totHrs, logon_duration
) x
) y
GROUP BY Date
,Split) DAS
INNER JOIN
(
SELECT
Date
,Split
,sum(C0) as C0
,sum(C1) as C1
,sum(C2) as C2
,sum(C3) as C3
,sum(C4) as C4
,SUM(C5) as C5
,SUM(C6) as C6
,SUM(C7) as C7
,SUM(C8) as C8
,SUM(C9) as C9
,SUM(CQ) as CQ
,SUM(CR) as CR
,SUM(CS) as CS
,SUM(CT) as CT
,SUM(CU) as CU
,SUM(CV) as CV
,SUM(CW) as CW
,SUM(CX) as CX
,SUM(CY) as CY
,SUM(CZ) as CZ
,SUM(MA) as MA
,SUM(TN) as TN
,SUM(TB) as TB
,SUM(T3) as T3
,SUM(TR) as TR
,SUM(BL) as BL
,SUM(MM) as MM
,SUM(AB) as AB
FROM
(
SELECT
CASE WHEN DATENAME(weekday,Date) in ('Saturday') THEN DATEADD(day,DATEDIFF(day,1,Date),0)
WHEN DATENAME(weekday,Date) in ('Sunday') THEN DATEADD(day,DATEDIFF(day,2,Date),0)
ELSE Date
END AS Date
,Split
,C0
,C1
,C2
,C3
,C4
,C5
,C6
,C7
,C8
,C9
,CQ
,CR
,CS
,CT
,CU
,CV
,CW
,CX
,CY
,CZ
,MA
,TN
,TB
,T3
,TR
,BL
,MM
,AB
FROM
(
SELECT
DATEADD(day,DATEDIFF(day,1,date_time),0) as Date
,split_number as Split
,SUM([c0_count]) as C0
,SUM([c1_count]) as C1
,SUM([c2_count]) as C2
,SUM([c3_count]) as C3
,SUM([c4_count]) as C4
,SUM([c5_count]) as C5
,SUM([c6_count]) as C6
,SUM([c7_count]) as C7
,SUM([c8_count]) as C8
,SUM([c9_count]) as C9
,SUM([cq_count]) as CQ
,SUM([cr_count]) as CR
,SUM([cs_count]) as CS
,SUM([ct_count]) as CT
,SUM([cu_count]) as CU
,SUM([cv_count]) as CV
,SUM([cw_count]) as CW
,SUM([cx_count]) as CX
,SUM([cy_count]) as CY
,SUM([cz_count]) as CZ
,SUM([manual_count]) as MA
,SUM([no_answer_count]) as TN
,SUM([busy_count]) as TB
,SUM([three_tone_count]) as T3
,SUM([write_count]) as TR
,SUM([bad_line_count]) as BL
,SUM([message_machine_count]) as MM
,SUM([abandoned_count]) as AB
FROM dialer.rp_Agent_Transactions
WHERE DATEADD(day,DATEDIFF(day,1,date_time),0) between @StartDate and @EndDate
AND record_type = @RecordType
AND split_number NOT IN (1,2,80)
GROUP BY DATEADD(day,DATEDIFF(day,1,date_time),0)
,split_number
) x
) y
GROUP BY Date, Split) DAT ON DAT.Split = DAS.Split
AND DAT.Date = DAS.Date
INNER JOIN
(
SELECT
Date
,Split
,sum(Accounts) as Accounts
,sum(Worked) as Worked
,sum(Attempts) as Attempts
,sum(HeldCalls) as HeldCalls
,sum(HeldDur) as HeldDur
,SUM(NC) as NC
,SUM(BN) as BN
,SUM(AB) as AH
,SUM(IO) as 'IO'
,SUM(DD) as DD
,SUM(HU) as HU
,SUM(NB) as NB
,SUM(VC) as VC
,SUM(IC) as IC
,SUM(TT) as TT
,SUM(AA) as AA
,SUM(RO) as RO
,SUM(NA) as NA
,SUM(BZ) as BZ
,SUM(BL) as BL
FROM
(
SELECT
CASE WHEN DATENAME(weekday,Date) in ('Saturday') THEN DATEADD(day,DATEDIFF(day,1,Date),0)
WHEN DATENAME(weekday,Date) in ('Sunday') THEN DATEADD(day,DATEDIFF(day,2,Date),0)
ELSE Date
END AS Date
,Split
,ListName
,Accounts
,Worked
,Attempts
,HeldCalls
,HeldDur
,NC
,BN
,AB
,IO
,DD
,HU
,NB
,VC
,IC
,TT
,AA
,RO
,NA
,BZ
,BL
FROM
(
SELECT
DATEADD(day,DATEDIFF(day,7,date_time),0) as Date
,split_number as Split
,LEFT(REPLACE(contact_list_name,' ',''),8) as ListName
,max(total_records) as Accounts
,CASE WHEN max(pen.Worked) > max(total_records) THEN max(total_records)
ELSE max(pen.Worked)
END AS Worked
,sum(dials) as Attempts
,sum(outbound_held_calls) as HeldCalls
,sum(outbound_held_duration) as HeldDur
,SUM(not_called_count) as NC
,SUM(bad_number_count) as BN
,SUM(abandoned_outbound_count) as AB
,SUM(ineffective_other_count) as IO
,SUM(data_device_count) as DD
,SUM(hang_up_count) as HU
,SUM(network_busy_count) as NB
,SUM(vacant_code_count) as VC
,SUM(intercept_count) as IC
,SUM(three_tone_count) as TT
,SUM(answer_machine_count) as AA
,SUM(reorder_count) as RO
,SUM(no_answer_count) as NA
,SUM(busy_count) as BZ
,SUM(bad_line_count) as BL
FROM dialer.rp_system_info a
INNER JOIN
(
SELECT ListName, COUNT(Worked) as Worked, Date
FROM
(
SELECT LEFT(REPLACE(contact_list_name,' ',''),8) as ListName,account_number as Worked, DATEADD(day,DATEDIFF(day,0,DATEADD(hh,-4,time_of_contact)),0) as Date
FROM dialer.cl_contact_event_vw
WHERE DATEADD(day,DATEDIFF(day,0,DATEADD(hh,-4,time_of_contact)),0) between @StartDate and @EndDate
GROUP BY LEFT(REPLACE(contact_list_name,' ',''),8), account_number, DATEADD(day,DATEDIFF(day,0,DATEADD(hh,-4,time_of_contact)),0)
) x
GROUP BY ListName, Date
) pen on pen.ListName = LEFT(REPLACE(contact_list_name,' ',''),8) and pen.Date = DATEADD(day,DATEDIFF(day,1,date_time),0)
WHERE DATEADD(day,DATEDIFF(day,1,date_time),0) between @StartDate and @EndDate
AND record_type = @RecordType
AND split_number NOT IN (1,2,80)
GROUP BY DATEADD(day,DATEDIFF(day,7,date_time),0)
,split_number
,LEFT(REPLACE(contact_list_name,' ',''),8)
) x
) y
GROUP BY Date, Split) SI ON SI.Date = DAS.Date
AND SI.Split = DAS.Split
INNER JOIN SandBox.[SHERMAN\juhrinek].ListInfo LI ON LI.Split = DAS.Split
ORDER BY 1,2
Second, not really sure how you want the results grouped based just on your code. It would help if you would provide the DDL (CREATE TABLE) statement(s) for the table(s), sample data for the table(s) as a series of INSERT INTO statements, and the expected results based on the sample data.
August 17, 2012 at 9:29 am
i just need things grouped by week starting with Monday and ending on Thursday.
August 17, 2012 at 9:44 am
jfowler 10337 (8/17/2012)
i just need things grouped by week starting with Monday and ending on Thursday.
No problem.
Run the results into a temp table, index it, and select with an aggregate.
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
August 17, 2012 at 9:50 am
jfowler 10337 (8/17/2012)
i just need things grouped by week starting with Monday and ending on Thursday.
That's nice. I charge $200.00 an hour with a minimum 8 hour charge to do your work for you with no assistance from you.
I have no table definitions, sample data, nothing to help me make sure that what I do is even correct.
If you want help from the volunteers on this site, then help us help you. Give us enough information in a readily consummable format (i.e. all we have to do is cut, paste, execute) to setup a development environment in an empty sandbox database (which means all the code we ask you to post should work). This should include the expected results based on the sample data you would provide so we have something to test our results against.
August 17, 2012 at 9:55 am
jfowler 10337 (8/17/2012)
i just need things grouped by week starting with Monday and ending on Thursday.
There are 13 selects in your query - each one has to be aggregated at the correct level. This isn't a trivial task (except perhaps by spooling the result into a temp table). Lynn isn't kidding when he's quoting for this work.
There are major improvements which could be made to the code too, here's one for free
DECLARE @totHrs INT
SET @totHrs = 5300
SELECT RIGHT(CONVERT(NVARCHAR(20),
convert(varchar(05),sum(@totHrs) / 3600) +':' +
convert(varchar(05),sum(@totHrs) % 3600/60) + ':' +
convert(varchar(05),sum(@totHrs) % 60),20),10) AS PunchHours
SELECT CONVERT(VARCHAR(8),DATEADD(second,SUM(@totHrs),0),108) AS PunchHours
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
August 17, 2012 at 9:56 am
ok, thanks. I was hoping it would be something as simple as looking at the code and changing something in the grouping. I've inherited the code, basically was told to google for solutions. I don't know how to post all that other stuff you're looking for. Thanks for your time thought. 🙂
August 17, 2012 at 9:58 am
jfowler 10337 (8/17/2012)
ok, thanks. I was hoping it would be something as simple as looking at the code and changing something in the grouping. I've inherited the code, basically was told to google for solutions. I don't know how to post all that other stuff you're looking for. Thanks for your time thought. 🙂
Your output includes date - why not try my suggestion?
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
August 17, 2012 at 9:58 am
jfowler 10337 (8/17/2012)
ok, thanks. I was hoping it would be something as simple as looking at the code and changing something in the grouping. I've inherited the code, basically was told to google for solutions. I don't know how to post all that other stuff you're looking for. Thanks for your time thought. 🙂
That's simple, if you look in my signature block, the first article I reference tells you what you need to do.
Here is the link so you don't have to look:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 17, 2012 at 10:08 am
I was taking another look at your code, and I'm sorry but it isn't a matter of just looking at it and recommending some changes. You have several derived tables, several source tables, quite a few data conversions going on. To help you modify this code we need to understand what it is doing now and what you want it do when we are done.
This means we need the tables, some sample data, and the final expected resutls based on the sample data.
Big note, sample data does not mean actual data. You need to come up with data that is representative of your problem domain so that we can understand the problem. Also, it doesn't need to be a lot of data. Ten to twenty rows depending on how the data is manipulated. trust me, we have had people post 1000's of rows of data, we don't really need that much.
August 17, 2012 at 10:10 am
ChrisM@Work (8/17/2012)
jfowler 10337 (8/17/2012)
ok, thanks. I was hoping it would be something as simple as looking at the code and changing something in the grouping. I've inherited the code, basically was told to google for solutions. I don't know how to post all that other stuff you're looking for. Thanks for your time thought. 🙂Your output includes date - why not try my suggestion?
Maybe he thinks you were being funny like I was with my initial snarky comment.
August 17, 2012 at 10:16 am
results it is spitting out now:
DateSplitClientPunchHoursLogTimeReadyConnectTypingIdlePreview
2012-05-01 00:00:00.0005Dell94:8:248:40:482:58:243:27:150:58:00:59:280:16:37
2012-05-01 00:00:00.0008Spec. Recovery31:53:241:40:280:20:410:38:530:31:280:9:60:0:18
2012-05-01 00:00:00.0009Cap1 DSA85:7:124:30:111:40:371:45:220:33:110:30:250:0:35
2012-05-01 00:00:00.00010Performing One Due72:11:2414:26:356:23:106:29:281:15:450:14:470:3:18
2012-05-01 00:00:00.00050OOS Primes NEW Score194:8:2414:13:255:2:395:15:91:46:542:0:560:7:19
2012-05-02 00:00:00.0004Consumer16:10:127:52:194:6:303:18:210:20:400:1:90:5:16
2012-05-02 00:00:00.0005Dell68:40:484:15:591:8:291:30:420:47:140:44:540:4:29
2012-05-02 00:00:00.0006CCA45:14:243:5:201:24:51:3:560:27:410:7:70:2:21
2012-05-02 00:00:00.0007Debt Settlement68:40:486:27:251:55:02:16:201:22:130:49:250:4:7
2012-05-02 00:00:00.0008Spec. Recovery77:40:484:9:181:26:311:43:310:34:20:21:70:3:59
2012-05-02 00:00:00.0009Cap1 DSA50:31:483:4:221:12:270:50:310:14:90:44:320:2:32
2012-05-02 00:00:00.00010Performing One Due72:25:4821:28:319:25:529:21:571:53:240:43:130:3:59
I want the results to return the weekof April 30, Monday throught thursday for each Client in one single row. Adding up the results for each day. so it would look like this
DateSplitClient
2012-05-01 00:00:00.0005Dell sum of weekly data for 4-30-5-03 2012-05-01 00:00:00.0008Spec. Recovery
2012-05-01 00:00:00.0009Cap1 DSA
2012-05-01 00:00:00.00010Performing One Due
2012-05-01 00:00:00.00050OOS Primes NEW Score1
2012-05-08 00:00:00.0004Consumer
2012-05-08 00:00:00.0005Dell sum of weekly data for 50-07 -5-10
2012-05-08 00:00:00.0006CCA
2012-05-08 00:00:00.0007Debt Settlement
2012-05-08 00:00:00.0008Spec. Recovery
2012-05-08 00:00:00.0009Cap1 DSA
2012-05-08 00:00:00.00010Performing One Due
August 17, 2012 at 10:21 am
Still don't have what is needed to really help. Have you read that article yet?
August 17, 2012 at 10:23 am
i read it....my mind just doesn't get computer code lingo all that well. If I posted the data of the tables I'm pulling from it would be an absolute mess on here.....no need to waste any more of your time. I will just keep doing what I've been doing, trial and error.
August 20, 2012 at 2:00 am
jfowler 10337 (8/17/2012)
i read it....my mind just doesn't get computer code lingo all that well. If I posted the data of the tables I'm pulling from it would be an absolute mess on here.....no need to waste any more of your time. I will just keep doing what I've been doing, trial and error.
Trial and error is very often how inexperienced programmers get their work done - I know, that's how I started. There are two serious problems with this approach though. The first is obvious - time. It's going to take a heck of a lot longer to complete this task by trial and error compared with a deliberated attack. The second is less obvious. Experienced developers will attempt to code for cases which don't yet exist in the data they're coding against, and perhaps account for change too. The trial and error approach can only deliver code which is by definition "only just there".
Don't be discouraged by the replies to your posts, folks are here to help and willingly go the extra mile - help with the immediate problem, and also give you some pointers to help you develop that "deliberated attack". It's very much a case of how much effort you are prepared to put in.
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
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply