I hope I have found the right place to post my question about SAP HANA coding issues?
I have a table that lists users and their location plus the time they were in that location. I am trying to build a table that counts the number of times two users meet based on location and a specified time range. Time range can be 120 sec (2 mins) for some locations or 600 sec for others. My code may or may not complete and build the table. If it fails it do so after a number of hours. The following is my code, I would appreciate help in trying to understand where I have gone wrong.
createCOLUMNTABLE"TABLES"."TBL_TEST_2014"as
(Select user1, user2, user1_mark, user2_mark, diff, meetings, meetings_A, meetings_S, meetings_m, meetings_t, meetings_c, meetings_l, meetings_li, meetings_r
From
(
Select
T1."USER"AS user1,
T2."USER"AS user2,
T3."MARK"AS user1_mark, --CHANGE
T4."MARK"as user2_mark, --CHANGE
ABS(T3." MARK" - T4." MARK") asdiff--Change
COUNT(*) AS meetings,
COUNT(CASEWHEN T1."Academic"='y'THEN 1 END) AS meetings_A,
COUNT(CASEWHEN T1."Academic"='n'THEN 1 END) AS meetings_S,
COUNT(CASEWHEN T1."type"='m'THEN 1 END) AS meetings_m,
COUNT(CASEWHEN T1."type"='t'THEN 1 END) AS meetings_t,
COUNT(CASEWHEN T1."type"='c'THEN 1 END) AS meetings_c,
COUNT(CASEWHEN T1."type"='l'THEN 1 END) AS meetings_l,
COUNT(CASEWHEN T1."type"='li'THEN 1 END) AS meetings_li,
COUNT(CASEWHEN T1."type"='r'THEN 1 END) AS meetings_r
FROM"WIFI_LOGS"."VIEW_WIFI_MEETING_LOGS" T1, -- change
"WIFI_LOGS"."VIEW_WIFI_MEETING_LOGS " T2, -- change
"WIFI_LOGS"."TBL_USER_MARKS" T3, -- RESULTS
"WIFI_LOGS"."TBL_USER_MARKS" T4 -- RESULTS
WHERE (T1."type" = 't'andABS(T1."TIME_STAMP" - T2."TIME_STAMP") < 120)
or (T1."type" = 'l'andABS(T1."TIME_STAMP" - T2."TIME_STAMP") < 600)
or (T1."type" = 'li'andABS(T1."TIME_STAMP" - T2."TIME_STAMP") < 600)
or (T1."type" = 'm'andABS(T1."TIME_STAMP" - T2."TIME_STAMP") < 600)
or (T1."type" = 'c'andABS(T1."TIME_STAMP" - T2."TIME_STAMP") < 600)
or (T1."type" = 'r'andABS(T1."TIME_STAMP" - T2."TIME_STAMP") < 600)
and T1."USER" < T2."NAME"
AND T1."LOCATIO" = T2."LOCATION"
and T3."USER" = T1."USER"
and T4."USER" = T2."USER"
GROUPBY T1."USER",
T2."USER",
T3."MARK",
T4."MARK"
))
Add comment