How do I filter many records with same ID

Question

1.00/5 (2 votes)

See more:

I have a ticketing system and i want to send email to the employee who assigned tickets for him which not responded or not resolved on (Tickets) time, so i need just the query to filter the record to send individual Emails, Email for every employee with only his tickets which assigned to him, and these some columns of the table(TicketId, TicketDate, TicketStatusId, AssignToId, AssignToIdEmail, RespondDate, ResolvedDate), so any suggestion is appriciated, and if it needs more explanation, tell me where is the ambiguity.
Thank you,

What I have tried:

I tried this code but it gives all the records(for if i have two employees it gives all records two times) not one by one

SQL

DECLARE @AssignToId INT
SET @AssignToId = 0
WHILE (@AssignToId < (select count(distinct AssignToId) from TicketDetails
where ((RespondDate is not null) or (LEN(RespondDate) !=0)) and ((TicketStatusId = 1) or (TicketStatusId = 5)) and RespondDate < DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()+1)) and AssignToId=AssignToId))

BEGIN

select TicketId, TicketDate, ProjectNameEn, RegionNameEn, CityNameEn, AssignToNameEn, PriorityNameEn, CreatedByEmployeeEn, RespondDate, AssignToEmail, AssignToSupervisorEmail, AssignToId
from TicketDetails
where ((RespondDate is not null) or (LEN(RespondDate) !=0)) and ((TicketStatusId = 1) or (TicketStatusId = 5)) and RespondDate < DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()+1)) and AssignToId=AssignToId
order by TicketId

Set @AssignToId = @AssignToId + 1
END

Posted 26-Feb-20 1:13am

ABAKh

Updated 26-Feb-20 2:53am

F-ES Sitecore

v2

Add a Solution

Comments

ABAKh 26-Feb-20 7:12am

This is the code:
DECLARE @AssignToId INT
SET @AssignToId = 0
WHILE (@AssignToId < (select count(distinct AssignToId) from TicketDetails
where ((RespondDate is not null) or (LEN(RespondDate) !=0)) and ((TicketStatusId = 1) or (TicketStatusId = 5)) and RespondDate < DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()+1)) and AssignToId=AssignToId))

BEGIN

select TicketId, TicketDate, ProjectNameEn, RegionNameEn, CityNameEn, AssignToNameEn, PriorityNameEn, CreatedByEmployeeEn, RespondDate, AssignToEmail, AssignToSupervisorEmail, AssignToId
from TicketDetails
where ((RespondDate is not null) or (LEN(RespondDate) !=0)) and ((TicketStatusId = 1) or (TicketStatusId = 5)) and RespondDate < DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()+1)) and AssignToId=AssignToId
order by TicketId

Set @AssignToId = @AssignToId + 1
END

ZurdoDev 26-Feb-20 8:48am

SQL coding does not work this week. It works in sets, not on individual values like you have with the @ASsingTold parameter.

SELECT *
FROM ticketsDetails
WHERE COALESCE(RespondDate, '2050-01-01') < GETDATE() + 1

You don't need all the weird checks on RespondDate, assuming it is a date field.

Also, I have no idea what you're trying to do with @AssignTold but whatever it is, you can't do it that way.

Also, your where clause has AssignTold = AssignTold which is the same as true. You probably meant @AssignTold but again, you can't do it that way.

ABAKh 26-Feb-20 8:53am

Then if i can't do it this way, please explain to me how to do it

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2020-02-26T02:55:00

The use of LEN:

SQL

where ((RespondDate is not null) or (LEN(RespondDate) !=0))

implied that the RespondDate column is a VARCHAR or NVARCHAR field - and that's probably a big part of your problem: string comparisons are resolved by comparing the first pair of different characters in the two strings, all other characters are ignored. So if you start looking today and use a European data format by default on your fields, you sill find every date where the day of the month is less than 27 (because today is the 26th.) Month and year will be ignored.

If they are, then change your DB and store values in appropriate datatypes: numbers in INT FLOAT, or DECIMAL, dates in DATE, DATETIME or DATETIME2, and so forth. If you don;t problems like this will occur every time you try to use the data (as well as others, when your data gets entered by people using another date system)