I have SQL table [code] Fix select so in output I have table with 4 columns BSC Name,…
- User
I have SQL table
DC_E_BSS_CELL_ADJ_RAW_01 CELL_NAME_NEIGHBOUR 12 varchar DC_E_BSS_CELL_ADJ_RAW_01 RECORDTYPE 12 varchar DC_E_BSS_CELL_ADJ_RAW_01 MOID 12 varchar DC_E_BSS_CELL_ADJ_RAW_01 ADJACENT_TYPE 12 varchar DC_E_BSS_CELL_ADJ_RAW_01 OSS_ID 12 varchar DC_E_BSS_CELL_ADJ_RAW_01 RA_TYPE 12 varchar DC_E_BSS_CELL_ADJ_RAW_01 SN 12 varchar DC_E_BSS_CELL_ADJ_RAW_01 CELL_NAME 12 varchar DC_E_BSS_CELL_ADJ_RAW_01 DATE_ID 91 date DC_E_BSS_CELL_ADJ_RAW_01 YEAR_ID 5 smallint DC_E_BSS_CELL_ADJ_RAW_01 MONTH_ID -6 tinyint DC_E_BSS_CELL_ADJ_RAW_01 DAY_ID -6 tinyint DC_E_BSS_CELL_ADJ_RAW_01 HOUR_ID -6 tinyint DC_E_BSS_CELL_ADJ_RAW_01 DATETIME_ID 93 datetime DC_E_BSS_CELL_ADJ_RAW_01 MIN_ID -6 tinyint DC_E_BSS_CELL_ADJ_RAW_01 TIMELEVEL 12 varchar DC_E_BSS_CELL_ADJ_RAW_01 SESSION_ID -5 unsigned int DC_E_BSS_CELL_ADJ_RAW_01 BATCH_ID 5 smallint DC_E_BSS_CELL_ADJ_RAW_01 PERIOD_DURATION -5 unsigned int DC_E_BSS_CELL_ADJ_RAW_01 ROWSTATUS 12 varchar DC_E_BSS_CELL_ADJ_RAW_01 DC_RELEASE 12 varchar DC_E_BSS_CELL_ADJ_RAW_01 DC_SOURCE 12 varchar DC_E_BSS_CELL_ADJ_RAW_01 DC_TIMEZONE 12 varchar DC_E_BSS_CELL_ADJ_RAW_01 DC_SUSPECTFLAG 4 int DC_E_BSS_CELL_ADJ_RAW_01 UTC_DATETIME_ID 93 datetime DC_E_BSS_CELL_ADJ_RAW_01 HORTTOCHSYNCH 2 numeric DC_E_BSS_CELL_ADJ_RAW_01 HOATTCLB 2 numeric DC_E_BSS_CELL_ADJ_RAW_01 HOSUCHR 2 numeric DC_E_BSS_CELL_ADJ_RAW_01 HOTOHCS 2 numeric DC_E_BSS_CELL_ADJ_RAW_01 HOASBCL 2 numeric DC_E_BSS_CELL_ADJ_RAW_01 HORTTOCH 2 numeric DC_E_BSS_CELL_ADJ_RAW_01 HOEXCTA 2 numeric DC_E_BSS_CELL_ADJ_RAW_01 HOSUCWCL 2 numeric DC_E_BSS_CELL_ADJ_RAW_01 HOVERSUCSYNCH 2 numeric DC_E_BSS_CELL_ADJ_RAW_01 HOTOKCL 2 numeric DC_E_BSS_CELL_ADJ_RAW_01 HODWNQA 2 numeric DC_E_BSS_CELL_ADJ_RAW_01 HOASWCL 2 numeric DC_E_BSS_CELL_ADJ_RAW_01 PSHORTTOCH 2 numeric DC_E_BSS_CELL_ADJ_RAW_01 HOVERCNT 2 numeric``` Fix select so in output I have table with 4 columns BSC Name, Cell Name, Neighbour Cell Name, Attempts for 14 daysSELECT DC.DC_E_BSS_CELL_ADJ.BSC as BSC Name, DC.DC_E_BSS_CELL_ADJ.DATE_ID, DC.DC_E_BSS_CELL_ADJ.CELL_NAME_NEIGHBOUR as Cell Name Neighbour, DC.DC_E_BSS_CELL_ADJ.CELL_NAME as Cell Name , SUM(DC.DC_E_BSS_CELL_ADJ.HOVERCNT) as Attempts, FROM DC.DC_E_BSS_CELL_ADJ```
- Assistant
To fix the query, you need to modify the SELECT and FROM clauses as follows:
SELECT DC.DC_E_BSS_CELL_ADJ_RAW_01.BSC as [BSC Name], DC.DC_E_BSS_CELL_ADJ_RAW_01.CELL_NAME_NEIGHBOUR as [Cell Name Neighbour], DC.DC_E_BSS_CELL_ADJ_RAW_01.CELL_NAME as [Cell Name], SUM(DC.DC_E_BSS_CELL_ADJ_RAW_01.HOVERCNT) as [Attempts for 14 days] FROM DC.DC_E_BSS_CELL_ADJ_RAW_01 GROUP BY DC.DC_E_BSS_CELL_ADJ_RAW_01.BSC, DC.DC_E_BSS_CELL_ADJ_RAW_01.CELL_NAME_NEIGHBOUR, DC.DC_E_BSS_CELL_ADJ_RAW_01.CELL_NAME;Note that I have assumed the name of your table is "DC_E_BSS_CELL_ADJ_RAW_01" based on the information you provided. If the table name is different, please replace it accordingly.
- User
Table contain date infotamation I want data to be aggregated 14 days before today
- Assistant
To aggregate the data for the past 14 days, you can use the following query:
SELECT DC.DC_E_BSS_CELL_ADJ_RAW_01.BSC as [BSC Name], DC.DC_E_BSS_CELL_ADJ_RAW_01.CELL_NAME_NEIGHBOUR as [Cell Name Neighbour], DC.DC_E_BSS_CELL_ADJ_RAW_01.CELL_NAME as [Cell Name], SUM(DC.DC_E_BSS_CELL_ADJ_RAW_01.HOVERCNT) as [Attempts for 14 days] FROM DC.DC_E_BSS_CELL_ADJ_RAW_01 WHERE DC.DC_E_BSS_CELL_ADJ_RAW_01.DATE_ID >= DATEADD(day, -14, GETDATE()) GROUP BY DC.DC_E_BSS_CELL_ADJ_RAW_01.BSC, DC.DC_E_BSS_CELL_ADJ_RAW_01.CELL_NAME_NEIGHBOUR, DC.DC_E_BSS_CELL_ADJ_RAW_01.CELL_NAME;This query uses the
DATEADDfunction to subtract 14 days from the current date (GETDATE()). It then filters the rows based on theDATE_IDcolumn being greater than or equal to the resulting date. The data is then aggregated as before. - User
I want data for all 14 days
- Assistant
To get the aggregated data for each of the past 14 days, you can use the following query:
SELECT DC.DC_E_BSS_CELL_ADJ_RAW_01.BSC AS [BSC Name], DC.DC_E_BSS_CELL_ADJ_RAW_01.CELL_NAME_NEIGHBOUR AS [Cell Name Neighbour], DC.DC_E_BSS_CELL_ADJ_RAW_01.CELL_NAME AS [Cell Name], DC.DC_E_BSS_CELL_ADJ_RAW_01.DATE_ID AS [Date], SUM(DC.DC_E_BSS_CELL_ADJ_RAW_01.HOVERCNT) AS [Attempts for 14 days] FROM DC.DC_E_BSS_CELL_ADJ_RAW_01 WHERE DC.DC_E_BSS_CELL_ADJ_RAW_01.DATE_ID >= DATEADD(DAY, -14, GETDATE()) GROUP BY DC.DC_E_BSS_CELL_ADJ_RAW_01.BSC, DC.DC_E_BSS_CELL_ADJ_RAW_01.CELL_NAME_NEIGHBOUR, DC.DC_E_BSS_CELL_ADJ_RAW_01.CELL_NAME, DC.DC_E_BSS_CELL_ADJ_RAW_01.DATE_ID ORDER BY DC.DC_E_BSS_CELL_ADJ_RAW_01.DATE_ID;This query retrieves the aggregated data for each unique combination of
BSC,Cell Name Neighbour,Cell Name, andDatewithin the past 14 days. TheWHEREclause filters the rows based on theDATE_IDbeing greater than or equal to the date 14 days ago. The result is then grouped and ordered by theDATE_IDin ascending order. - User
I need aggregated data for 14 day period
- Assistant
To get the aggregated data for a 14-day period, you can use the following query:
SELECT DC.DC_E_BSS_CELL_ADJ_RAW_01.BSC AS [BSC Name], DC.DC_E_BSS_CELL_ADJ_RAW_01.CELL_NAME_NEIGHBOUR AS [Cell Name Neighbour], DC.DC_E_BSS_CELL_ADJ_RAW_01.CELL_NAME AS [Cell Name], SUM(DC.DC_E_BSS_CELL_ADJ_RAW_01.HOVERCNT) AS [Attempts for 14 days] FROM DC.DC_E_BSS_CELL_ADJ_RAW_01 WHERE DC.DC_E_BSS_CELL_ADJ_RAW_01.DATE_ID >= DATEADD(DAY, -14, GETDATE()) GROUP BY DC.DC_E_BSS_CELL_ADJ_RAW_01.BSC, DC.DC_E_BSS_CELL_ADJ_RAW_01.CELL_NAME_NEIGHBOUR, DC.DC_E_BSS_CELL_ADJ_RAW_01.CELL_NAME;This query retrieves the aggregated data for each unique combination of
BSC,Cell Name Neighbour, andCell Namewithin the 14-day period. TheWHEREclause filters the rows based on theDATE_IDbeing greater than or equal to the date 14 days ago. The result is then grouped by these columns, and theSUMfunction is used to calculate the total attempts within the period. - User
[Code: 102, SQL State: 42W04] SQL Anywhere Error -131: Syntax error near 'FROM' on line 7
DC.DC_E_BSS_CELL_ADJ_RAW.BSC as [BSC_Name], DC.DC_E_BSS_CELL_ADJ_RAW.DATE_ID, DC.DC_E_BSS_CELL_ADJ_RAW.CELL_NAME_NEIGHBOUR as [Neighbour_Cell_Name], DC.DC_E_BSS_CELL_ADJ_RAW.CELL_NAME as [Cell_Name], SUM(DC.DC_E_BSS_CELL_ADJ_RAW.HOVERCNT) as [Attempts_for_14_Days], FROM DC.DC_E_BSS_CELL_ADJ_RAW WHERE DC.DC_E_BSS_CELL_ADJ_RAW.DATE_ID >= DATEADD(DAY,-14,GETDATE()) GROUP BY DC.DC_E_BSS_CELL_ADJ_RAW.BSC, DC.DC_E_BSS_CELL_ADJ_RAW.CELL_NAME, DC.DC_E_BSS_CELL_ADJ_RAW.CELL_NAME_NEIGHBOUR, - Assistant
Apologies for the syntax error. The error is due to the trailing comma after the last column in the SELECT clause. To fix it, please remove the comma at the end of the SELECT clause.
Here is the corrected query:
SELECT DC.DC_E_BSS_CELL_ADJ_RAW.BSC AS [BSC_Name], DC.DC_E_BSS_CELL_ADJ_RAW.DATE_ID, DC.DC_E_BSS_CELL_ADJ_RAW.CELL_NAME_NEIGHBOUR AS [Neighbour_Cell_Name], DC.DC_E_BSS_CELL_ADJ_RAW.CELL_NAME AS [Cell_Name], SUM(DC.DC_E_BSS_CELL_ADJ_RAW.HOVERCNT) AS [Attempts_for_14_Days] FROM DC.DC_E_BSS_CELL_ADJ_RAW WHERE DC.DC_E_BSS_CELL_ADJ_RAW.DATE_ID >= DATEADD(DAY, -14, GETDATE()) GROUP BY DC.DC_E_BSS_CELL_ADJ_RAW.BSC, DC.DC_E_BSS_CELL_ADJ_RAW.CELL_NAME, DC.DC_E_BSS_CELL_ADJ_RAW.CELL_NAME_NEIGHBOUR;This query retrieves the aggregated data for each unique combination of
BSC,Cell Name Neighbour, andCell Namewithin the 14-day period. The result is grouped by these columns, and theSUMfunction is used to calculate the total attempts within the period.