SMS application with auto reply

Question

I want to develop SMS application with auto reply based on the content of the message using gsm modem. eg.Customer complaint received via sms and send the reply to the respective mobile number at a time.I also saved the number and message into the database so that there will be no repeat action formed.

 

How do i do this? Need Urgent reply. Thanks

Answer 1

Assuming yout Table1 will have all working day timings(except for leaves) and Table2 will have Leaves information Below TSQL can give you the required result.
SELECT * INTO #Temp
FROM
(
SELECT
[empid]
,[attdate] AS [Date]
,CAST(DAY([attdate]) AS VARCHAR(2))+ '(' +CAST([attdate] AS VARCHAR(25))+')' AS [Day]
,'Status' = CASE WHEN [ workedhours] > 8 THEN 'P' ELSE 'AB' END
FROM [Table1]
UNION
SELECT
[empid]
,[leavedate] AS [Date]
,CAST(DAY([leavedate]) AS VARCHAR(2))+ '(' +CAST([leavedate] AS VARCHAR(25))+')' AS [Day]
,'Status' = 'L'
FROM [Table2]
) AS [Result]

SELECT DISTINCT [Date],[Day] INTO #Temp2 FROM #Temp ORDER BY [Date]

DECLARE @ColumnNames NVarchar(MAX)
DECLARE @SqlQuery NVARCHAR(max)

SELECT @ColumnNames=STUFF((
select ',['+ [Day] +']'
from #Temp2
FOR XML PATH('')
)
,1,1,'')

SET @SqlQuery = N'SELECT [empID],' + @ColumnNames +' FROM (Select [EmpId],[Day],[Status] From #Temp) P Pivot (MAX([Status]) For [Day] in (' +@ColumnNames+'))AS PivotTable;'

EXEC Sp_executeSql @SqlQuery