Last month New Employees

[soucyinter]

Hello,
I've been trying to get a report showing below columns (from 3 different custom fields).
[size=3]When I was using 2 custom fields, it worked. When I tried to input a third one, I failed... I now have double information per custom fields, so instead of having 2 results, I get 3 times the 2 results.[/size]
[EDIT]
This part now works, but there's place to code improvement (shorten the code), maybe using different joins? I'm not very familiar with the type of joins..


Columns should look like this :
| Ticket user | Ticket # | Ticket Creator | Creation date | Employee name | Entry Date | Is a replacement? |

These are Custom Fields, their values must be shown in the columns.
Employee Name, Entry Date, Is a replacement?

Also, I have no idea on how to filter to show only the Last Month (not the last 30 days).
I can just get the last year and filter from Excel after but I'd like to make it a report by mail or onscreen in the Dashboard.

Anyone can help me figure this out please? Thanks.

Here's my code:

Select Top 1000000 htblusers1.name As [Ticket User],
  htblticket.ticketid As [Ticket #],
  htblusers.name As [Ticket Creator],
  htblticket.date As [Creation Date],
  htblticketcustomfield.data As [Employee Name],
  htblticketcustomfield1.data As [Entry date],
  htblticketcustomfield2.data As [Est-ce un remplacement]
From htblhistorytypes
  Inner Join htblhistory On htblhistorytypes.typeid = htblhistory.typeid
  Inner Join htblticket On htblticket.ticketid = htblhistory.ticketid
  Inner Join htblusers On htblusers.userid = htblhistory.userid
  Inner Join htblusers htblusers1 On htblticket.fromuserid = htblusers1.userid
    And htblhistory.userid != htblusers1.userid
  Inner Join htblticketcustomfield On htblticket.ticketid =
    htblticketcustomfield.ticketid
  Inner Join htblcustomfields On htblcustomfields.fieldid =
    htblticketcustomfield.fieldid
  Inner Join htblticketcustomfield htblticketcustomfield1
    On htblticket.ticketid = htblticketcustomfield1.ticketid
  Inner Join htblcustomfields htblcustomfields1 On htblcustomfields1.fieldid =
    htblticketcustomfield1.fieldid
  Inner Join htblticketcustomfield htblticketcustomfield2
    On htblticket.ticketid = htblticketcustomfield2.ticketid
  Inner Join htblcustomfields htblcustomfields2 On htblcustomfields2.fieldid =
    htblticketcustomfield2.fieldid
Where htblcustomfields.name = 'First name, Last name' And
  htblcustomfields1.name = 'Date d''entrée en vigueur' And
  htblcustomfields2.name = 'Est-ce un remplacement?' And
  htblhistorytypes.typeid = 31
Order By [Entry date] Desc,
  [Est-ce un remplacement]

[soucyinter · Accepted solution]

Finally made it with some help!
If it can help anyone...

Here's the code!

Select Top 1000000 htblusers.name As [Ticket User],
  htblticket.ticketid As [Ticket #],
  htblusers1.name As [Ticket agent],
  htblticket.date As [Creation Date],
  htblticketcustomfield.data As [Employee Name],
  htblticketcustomfield1.data As [Entry date],
  htblticketcustomfield2.data As [Est-ce un remplacement],
  DateDiff(DAY, htblticket.date, Cast(htblticketcustomfield1.data As datetime))
  As [Délai d'avis]
From htblticket
  Left Join htblusers On htblusers.userid = htblticket.fromuserid
  Inner Join htblagents On htblagents.agentid = htblticket.agentid
  Left Join htblusers htblusers1 On htblagents.userid = htblusers1.userid
  Left Join htblticketcustomfield On htblticket.ticketid =
    htblticketcustomfield.ticketid
  Inner Join htblcustomfields On htblcustomfields.fieldid =
    htblticketcustomfield.fieldid And htblcustomfields.name =
    'First name, Last name'
  Left Join htblticketcustomfield htblticketcustomfield1
    On htblticket.ticketid = htblticketcustomfield1.ticketid
  Inner Join htblcustomfields htblcustomfields1 On htblcustomfields1.fieldid =
    htblticketcustomfield1.fieldid And htblcustomfields1.name =
    'Date d''entrée en vigueur'
  Left Join htblticketcustomfield htblticketcustomfield2
    On htblticket.ticketid = htblticketcustomfield2.ticketid
  Inner Join htblcustomfields htblcustomfields2 On htblcustomfields2.fieldid =
    htblticketcustomfield2.fieldid And htblcustomfields2.name =
    'Est-ce un remplacement?'
  Left Join htblticketcustomfield htblticketcustomfield3
    On htblticket.ticketid = htblticketcustomfield3.ticketid
  Inner Join htblcustomfields htblcustomfields3 On htblcustomfields3.fieldid =
    htblticketcustomfield3.fieldid And htblcustomfields3.name =
    'Est-ce une demande de changement?'
  Left Join htbltickettypes On htblticket.tickettypeid =
    htbltickettypes.tickettypeid
Where htblticketcustomfield3.data = 'Non' And DatePart(month, DateAdd(month, -1,
  DatePart(month, GetDate()))) >= DatePart(month,
  Cast(htblticketcustomfield1.data As datetime)) And DatePart(month,
  DateAdd(month, -1, DatePart(month, GetDate()))) <= DatePart(month,
  Cast(htblticketcustomfield1.data As datetime)) And
  htbltickettypes.tickettypeid = 7
Order By [Entry date],
  [Est-ce un remplacement]

[soucyinter]

That did not work for the "last month".
It shows no results, I even tried with only "and" instead of the "or" since all criterias needs to be answered by a yes. Alas it didn't work.

[ProfileNL]

Can you try the following query? I havent tried it yet so it might not work

Select Top 1000000 htblusers1.name As [Ticket User],
  htblticket.ticketid As [Ticket #],
  htblusers.name As [Ticket Creator],
  htblticket.date As [Creation Date],
  htblticketcustomfield.data As [Employee Name],
  htblticketcustomfield1.data As [Entry date],
  htblticketcustomfield2.data As [Est-ce un remplacement]
From htblhistorytypes
  Inner Join htblhistory On htblhistorytypes.typeid = htblhistory.typeid
  Inner Join htblticket On htblticket.ticketid = htblhistory.ticketid
  Inner Join htblusers On htblusers.userid = htblhistory.userid
  Inner Join htblusers htblusers1 On htblticket.fromuserid = htblusers1.userid
    And htblhistory.userid != htblusers1.userid
  Inner Join htblticketcustomfield On htblticket.ticketid =
    htblticketcustomfield.ticketid
  Inner Join htblcustomfields On htblcustomfields.fieldid =
    htblticketcustomfield.fieldid
  Inner Join htblticketcustomfield htblticketcustomfield1
    On htblticket.ticketid = htblticketcustomfield1.ticketid
  Inner Join htblcustomfields htblcustomfields1 On htblcustomfields1.fieldid =
    htblticketcustomfield1.fieldid
  Inner Join htblticketcustomfield htblticketcustomfield2
    On htblticket.ticketid = htblticketcustomfield2.ticketid
  Inner Join htblcustomfields htblcustomfields2 On htblcustomfields2.fieldid =
    htblticketcustomfield2.fieldid
Where htblcustomfields.name = 'First name, Last name' And
  htblcustomfields1.name = 'Date d''entrée en vigueur' Or
  htblcustomfields2.name = 'Est-ce un remplacement?' And
  htblhistorytypes.typeid = 31 Or
  htblticket.date BETWEEN getdate() AND Dateadd(Day, -30, GETDATE())
Order By [Entry date] Desc,
  [Est-ce un remplacement]