0

My table has one record per employee per year. What should be the query to change visibility if employee data is not recorded this year.

Column names are employee_name, employee_Id, rating, CreatedOn where CreatedOn is basically timestamp.

Suppose there are two forms, form1 and form2 and its visibility depends on the user input. If user has submitted form1, he can only see form2. Exactly after a year, respective user can see the form1again.

I tried the following command, but is not working

SELECT COUNT(*) FROM
TABLE
WHERE employee_Id = '$CurrentUserId$'
AND CreatedOn > DATEADD(year,-1,GETDATE())

if query = 0, show form1, else show form2

I know basic SQL, but my skills are not upto this level.

Parth
  • 9
  • 1

2 Answers2

1
SELECT CASE COUNT(*) WHEN 0 THEN 'Form1' ELSE 'Form2' END AS ShowForm
FROM
TABLE
WHERE employee_Id = '$CurrentUserId$'
AND CreatedOn > DATEADD(year,-1,GETDATE())
Dheerendra
  • 284
  • 1
  • 7
  • the problem is `DATEADD(year,-1,GETDATE())` is not working, probably due to `createdon`is saved as timestamp like ''2018-12-16 12:29:56.577" – Parth Jan 16 '19 at 13:47
  • 1
    `and CreatedOn > DATEADD(year,-1,GETDATE())` works fine in my test, having CreatedOn declared as a `timestamp`. So your problem must be elsewhere – GuidoG Jan 16 '19 at 13:56
  • Use the condition as CAST(CreatedOn AS DATE) > CAST(DATEADD(YEAR,-1,GETDATE()) AS DATE) – Dheerendra Jan 16 '19 at 13:56
  • `CreatedOn` cannot be declared as `TimeStamp` in your table. Than `CAST(CreatedOn AS DATE)` will return an error `Explicit conversion from data type timestamp to date is not allowed.` – GuidoG Jan 16 '19 at 14:29
  • TimeStamp in SQL Server has nothing to do with datetime ! See also this https://stackoverflow.com/questions/8119386/how-to-convert-sql-servers-timestamp-column-to-datetime-format – GuidoG Jan 16 '19 at 14:42
0

When checking if a record exists or not, use EXISTS / NOT EXISTS. COUNT() will actually count all rows while EXISTS will short-circuit at the first row, thus being quicker.

IF EXISTS (SELECT 'record this year' FROM [TABLE] T WHERE T.employee_Id = '$CurrentUserId$' AND T.CreatedOn > DATEADD(YEAR,-1,GETDATE()))
BEGIN
    -- Do stuff if record this last year
END
ELSE
BEGIN
    -- Do stuff if no record this last year
END

If you need this on 1 SELECT statement you can use Dheerendra's solution.

EzLo
  • 13,780
  • 10
  • 33
  • 38