I had written a DB query to be run on SQL Server and the table structure was like:
pkey Home_Telephone ID EDU_ID City State Zip Menu_Selection Account_Number Billing_System Prin FTA Node Company Division Email_Addr Create_time system MAC_ID Service_Address
123456789 7778889999 00250087661438113227 5mm7mmm000000000mm10emm623300002 AAAAAAAA MS 31234 Tier1 1234567891234567 C 5500 0050 EC6664 0 0 email@example.com 2015-07-28 14:53:52.000 8381 NULL 123 MAIN DR
123455789 2225558888 00250068021438191419 55n90nn000000000nn10ennn23300002 BBBBBBBB MS 35678 NULL 8542687456248952 C 6600 0040 MA6610 0 0 firstname.lastname@example.org 2015-07-29 12:37:04.000 8381 NULL 456 CENTER RD
This type of data is available for past few months and Create_time column can be used as reference for timestamp. (Sorry, couldn't put the table in proper format)
step 1) What I am trying to do is if CURRENT_TIMESTAMP is say 2015-08-17 16:27:42.987
Then fetch count(ID) grouped by each state where Create_time is between 2015-08-17 16:00:00.000 to 2015-08-17 16:27:42.987.
step 2) Second step is to find count(ID) grouped by each state for past 7 days (Aug/10 to Aug/16) during the same 1 hour window 16:00:00.000 to 17:00:00.000 and take sum of count(ID) for past 7 days, and avegare count(ID) (divide sum by 7).
step 3) Third step is if the count(ID) for today (calculated in step1) is greater than average count(ID) then find the difference and display in a column (say Variance) and also calulate percentage deviation by using (Variance/average count(ID)) * 100 and display in a column say percentage.
I had formulated following query for doing this but it is for SQL server and now I have to modify it for Teradata as on long term, we'll be using Teradata.
Can someone suggest me if same query can be reused with minor modifications for Teradata as well and point those changes please?
--One week average call volume and %variance
declare @w0s datetime
declare @w0e datetime
declare @w1s datetime
declare @w1e datetime
,sum(case when create_time>=@w0s and create_time<@w0e then 1 else 0 end) as Call_Volume_This_hr
,sum(case when create_time>=@w1s and create_time<@w1e then 1 else 0 end)/7.0 as Avg_Call_Volume_Past_Week1
and datepart(hh,create_time)=datepart(hour, getdate())
and state is not null
group by state
, (InlineFunction.Variance/PreAggregate.Avg_Call_Volume_Past_Week1)*100 as 'Percentage_Variance'
cross apply (
when Call_Volume_This_hr < Avg_Call_Volume_Past_Week1 then 0
else Call_Volume_This_hr -Avg_Call_Volume_Past_Week1
You will need more than minor modifications...
Datetime calculations usually differ a lot across DBMSes.
CROSS APPLY is MS proprietary syntax which doesn't exist in Teradata. But in your case it's easy to replace because you can simply re-use an alias.
In Teradata defining variables is only possible within Stored Procedures (and you might not have the right to create them).
This should return the same result in Teradata:
WITH params AS
TO_TIMESTAMP(TO_CHAR(CURRENT_TIMESTAMP, 'yyyy-mm-dd hh24'),'yyyy-mm-dd hh24') AS w0s -- truncate to the current hour
,CURRENT_TIMESTAMP AS w0e
,CURRENT_DATE -8 AS w1s
,CURRENT_DATE -1 AS w1e
,SUM(CASE WHEN create_time>=w0s AND create_time<w0e THEN 1 ELSE 0 END) AS Call_Volume_This_hr
,SUM(CASE WHEN create_time>=w1s AND create_time<w1e THEN 1 ELSE 0 END)/7.0 AS Avg_Call_Volume_Past_Week1
,CASE WHEN Call_Volume_This_hr < Avg_Call_Volume_Past_Week1 THEN 0
ELSE Call_Volume_This_hr -Avg_Call_Volume_Past_Week1
END AS VARIANCE
,100 * VARIANCE / PreAggregate.Avg_Call_Volume_Past_Week1 AS Percentage_Variance
-- AND datepart(hh,create_time)=datepart(HOUR, getdate())
AND EXTRACT(HOUR FROM create_time)=EXTRACT( HOUR FROM CURRENT_TIMESTAMP)
AND state IS NOT NULL
GROUP BY state
Thank you for your response which will help me a lot.
I think the query is working fine (as I don't see syntax error in Teradata Studio) but on running the query I get "No more spool space" error.
On searching for the reason behind this error on net, it looks like spool space allocated to the user name I am using to connect to the DB is less than what the query requires.
I'll try to find from the DB admin and post the update.
I think I missed a syntax error earlier with the query and first qury had some error (as this was second query on screen of Teradata Studio, it didn't show error in this query).
The error is
I finally found what I was missing. For syntax error, I had to define column list between "params" and "as" so something like
WITH params (w0s,w0e,w1s,w1e) as
For no more spool space error I used advice to use inner join and the query started fetching result as expected. So the final query I am using now is:
WITH TEMP_TAB (w0s,w0e,w4s,w4e) AS
TO_TIMESTAMP(TO_CHAR(CURRENT_TIMESTAMP, 'yyyy-mm-dd hh24'),'yyyy-mm-dd hh24') AS w0s
,CURRENT_TIMESTAMP AS w0e
,CURRENT_DATE -29 AS w4s
,CURRENT_DATE -1 AS w4e
,SUM(CASE WHEN CRT_DTTM>=w0s AND CRT_DTTM<w0e THEN 1 ELSE 0 END) AS Call_Volume_This_hr
,SUM(CASE WHEN CRT_DTTM>=w4s AND CRT_DTTM<w4e THEN 1 ELSE 0 END)/28.0 AS Avg_Call_Volume_Past_4Weeks
,CASE WHEN Call_Volume_This_hr < Avg_Call_Volume_Past_4Weeks THEN 0
ELSE Call_Volume_This_hr -Avg_Call_Volume_Past_4Weeks
END AS VARIANCE
,100 * VARIANCE / Avg_Call_Volume_Past_4Weeks AS Percentage_Variance
FROM XDW_V.INB_CALL_NRT_RPT INNER JOIN TEMP_TAB
AND EXTRACT(HOUR FROM CRT_DTTM)=EXTRACT( HOUR FROM CURRENT_TIMESTAMP)
AND ST_NM IS NOT NULL
GROUP BY ST_NM;
Thank you dnoeth for your help without which I wouldn't have got a start.