Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Shaun E on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Interval Report 1

Status
Not open for further replies.

PappaG

Technical User
Nov 21, 2003
288
GB
Hi All

Can anyone tell me how to go about making a half hour interval report. I have made an hourly version but cheated to do it can someone show me how to modify the attached to make a half hour report. Time format is hh:mm:ss let me know if you require any further info

SELECT dbo_RouteCallDetailAccess.Date, Left([dbo_RouteCallDetailAccess].[Time],2) & ":00" AS [Hour], ROUTING01_V_ROUTING.DNIS1, ROUTING01_V_ROUTING.PRODUCT, Count(ROUTING01_V_ROUTING.PRODUCT) AS Calls, dbo_RouteCallDetailAccess.Label
FROM dbo_RouteCallDetailAccess INNER JOIN ROUTING01_V_ROUTING ON dbo_RouteCallDetailAccess.DialedNumberString = ROUTING01_V_ROUTING.DNIS1
GROUP BY dbo_RouteCallDetailAccess.Date, Left([dbo_RouteCallDetailAccess].[Time],2) & ":00", ROUTING01_V_ROUTING.DNIS1, ROUTING01_V_ROUTING.PRODUCT, dbo_RouteCallDetailAccess.Label, ROUTING01_V_ROUTING.BUSINESS_GROUP, ROUTING01_V_ROUTING.CALL_CLASSIFICATION
HAVING (((dbo_RouteCallDetailAccess.Date) Like [Input]) AND ((ROUTING01_V_ROUTING.BUSINESS_GROUP)="SKYDEALS") AND ((ROUTING01_V_ROUTING.CALL_CLASSIFICATION)="BL"))
WITH OWNERACCESS OPTION;
 
Continue cheating I guess
Code:
SELECT D.[Date], Left( D.[Time],2) & ":00" AS [Hour], R.DNIS1, R.PRODUCT, Count(R.PRODUCT) AS Calls, D.Label

FROM dbo_RouteCallDetailAccess D INNER JOIN ROUTING01_V_ROUTING R ON D.DialedNumberString = R.DNIS1

WHERE D.[Date] Like [Input] AND R.BUSINESS_GROUP="SKYDEALS" AND R.CALL_CLASSIFICATION="BL"

GROUP BY D.[Date], Left(D.[Time],2) & ":00", R.DNIS1, R.PRODUCT, D.Label, R.BUSINESS_GROUP, R.CALL_CLASSIFICATION

UNION ALL

SELECT D.[Date], Left( D.[Time],2) & ":30" AS [Hour], R.DNIS1, R.PRODUCT, Count(R.PRODUCT) AS Calls, D.Label

FROM dbo_RouteCallDetailAccess D INNER JOIN ROUTING01_V_ROUTING R ON D.DialedNumberString = R.DNIS1

WHERE D.[Date] Like [Input] AND R.BUSINESS_GROUP="SKYDEALS" AND R.CALL_CLASSIFICATION="BL"

GROUP BY D.[Date], Left(D.[Time],2) & ":30", R.DNIS1, R.PRODUCT, D.Label, R.BUSINESS_GROUP, R.CALL_CLASSIFICATION

ORDER BY 1, 2
I'm assuming from the structure of your code that the fields Date and Time are not really DateTime fields.
 
Hi Golom

These are valid date time value's its just the query i wrote only looked at the hour field then added text to it to imitate an hourly report. This was the only way i knew how to achieve this but a half hourly report is a completly different kettle of fish
 
Hi Golom

Thanks that works a treat dont know how the heck you get the data from the above though to come out as a half hourly report you couldnt give me a little explanation on the above query could you.

Thanks
 
Your original query just concatenated ":00" to the hours part of the time field to produce "05:00" (for example.) I just built a second query that appends ":30" so that an [Hour] field of the form "05:30" was generated.

The UNION ALL just combines the results of those two queries and the ORDER BY puts them in the correct order after they are combined.
 
Yeah kinda get that
my query just removed the minutes of the hour and then counted then to give calls per hour i cant see how your query knows what calls were between 00-29 and then from 30-59 i can see what most of the query is doing this is the bit i dont get
how does it know there were say 10 calls say between 09:00 and 9:30 and then say 15 between 09:30 and 10:00 ??????
 
It doesn't. For that you need to go to correlated sub-queries
Code:
SELECT D.[Date], Left( D.[Time],2) & ":00" AS [Hour], R.DNIS1, R.PRODUCT,
 
(Select Count(*) 
 From dbo_RouteCallDetailAccess D1 INNER JOIN ROUTING01_V_ROUTING R1 
      ON D1.DialedNumberString = R1.DNIS1

 WHERE D1.[Date] Like [Input] 
   AND R1.BUSINESS_GROUP="SKYDEALS"  
   AND R1.CALL_CLASSIFICATION="BL"
   AND D1.Time BETWEEN Left( D.[Time],2) & ":00" 
                   AND Left( D.[Time],2) & ":29") AS Calls, 

D.Label

FROM dbo_RouteCallDetailAccess D INNER JOIN ROUTING01_V_ROUTING R ON D.DialedNumberString = R.DNIS1

WHERE D.[Date] Like [Input] AND R.BUSINESS_GROUP="SKYDEALS" AND R.CALL_CLASSIFICATION="BL"

GROUP BY D.[Date], Left(D.[Time],2) & ":00", R.DNIS1, R.PRODUCT, D.Label, R.BUSINESS_GROUP, R.CALL_CLASSIFICATION

UNION ALL

SELECT D.[Date], Left( D.[Time],2) & ":30" AS [Hour], R.DNIS1, R.PRODUCT, 

(Select Count(*) 
 From dbo_RouteCallDetailAccess D1 INNER JOIN ROUTING01_V_ROUTING R1 
      ON D1.DialedNumberString = R1.DNIS1

 WHERE D1.[Date] Like [Input] 
   AND R1.BUSINESS_GROUP="SKYDEALS" 
   AND R1.CALL_CLASSIFICATION="BL"
   AND D1.Time BETWEEN Left( D.[Time],2) & ":30" 
                   AND Left( D.[Time],2) & ":59") AS Calls, 

D.Label

FROM dbo_RouteCallDetailAccess D INNER JOIN ROUTING01_V_ROUTING R ON D.DialedNumberString = R.DNIS1

WHERE D.[Date] Like [Input] AND R.BUSINESS_GROUP="SKYDEALS" AND R.CALL_CLASSIFICATION="BL"

GROUP BY D.[Date], Left(D.[Time],2) & ":30", R.DNIS1, R.PRODUCT, D.Label, R.BUSINESS_GROUP, R.CALL_CLASSIFICATION

ORDER BY 1, 2
 
Thanks Golom

Makes much more sense i can see where the interval is made up from now thanks again for your help.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top