MeghanaThakekar

CREATE TABLE #TempIDs (
  ID INT identity(1,1) ,UserID INT
);
-- Insert the IDs from the source table into the temporary table
INSERT INTO #TempIDs (UserID) select id from Users where status=1 and ID not in (select userid from Partners) and id in (6770,
6767,6766,6765,6760,6532,6291,6175,6159,6145,5788,5786,5745,5710,5684,5315,5222,5021,4953,4813,4795,4793,4792,4790,4789,4675,4674,4670,
4668,4667,4666,4658,4576,4215,4119,4009,3976,3830,3663,3557,3552,3527,3421,980);

-- Declare a variable for looping

MeghanaThakekar

select 
Contacts.id as contactid, 
Contacts.CompanyID as contact_companyid, 
Partners.id as partnerid, 
Partners.CompanyID as partner_companyid 
from Contacts inner join Partners on Contacts.ID = Partners.ContactID
where Contacts.Status = 3 
and Partners.Status = 1 
and Contacts.CompanyID != Partners.CompanyID 
and Partners.CompanyID is not null

MeghanaThakekar

update Contacts set Contacts.CompanyID = Partners.CompanyID
from Contacts  inner join Partners on Contacts.ID = Partners.ContactID
where Contacts.Status = 3 
and Partners.Status = 1 
and Contacts.CompanyID != Partners.CompanyID 
and Partners.CompanyID is not null

MeghanaThakekar

update FileHandles set IsPublic=0 
where
status = 1 
and IsPublic=1 
and Project_ID is not null 
and ProjectType_ID is not null
and ID in (select LinkID from FilterLinks where FilterID=(select ID from Filters where Name='requestforquotes'))

MeghanaThakekar

select 
(select top 1 F_38 from FieldSetType_2 where linkid=Organizations.ID) as PartnerId_AAN,
Organizations.Name,
Organizations.Country,
(select count(id) from users where users.OrganizationID = organizations.id and status = 1) as number_of_users,
Organizations.CreatedOn,
(select top 1 F_19 from FieldSetType_2 where linkid=Organizations.ID) as Service_Provider_Level,
(select top 1 F_20 from FieldSetType_2 where linkid=Organizations.ID) as Service_Provider_Status,
(select top 1 F_21 from FieldSetType_2 where linkid=Organizations.ID) as Service_Provider_Sub_Status,

MeghanaThakekar

----JAN 
select Name,FirstName,LastName,Email,Country,CreatedOn,( select top 1 F_38 from FieldSetType_2 where Linkid=users.OrganizationID) as AAN,
(select top 1 LoginDate from UserLoginHistory where LoginDate<='2022/01/31' and UserID= Users.ID order by LoginDate desc) Last_login_date_of_month,
(select top 1 LoginDate from UserLoginHistory where  UserID= Users.ID  order by LoginDate desc) Last_login_date,
(select top 1 F_14 from UserField_1 where LinkID=Users.ID) as User_Type
,(select COUNT(ID) from UserLoginHistory where LoginDate>'2022/01/01' and LoginDate<='2022/01/31' and UserID= Users.ID ) as Total_Logins_of_the_Month
from users where status=1 and id in (select userid from UserLoginHistory where  LoginDate<='2022/1/31')
order by name desc

----FEB

MeghanaThakekar

select Organizations.ID,Organizations.Name
,(SELECT COUNT(users.id)
        FROM users
        WHERE
		  Users.Status=1
		  And Users.OrganizationID = Organizations.ID
          AND id in (select distinct(UserID) from UserLoginHistory where LoginDate >= '2023-01-01' and LoginDate <= '2023-03-31')) As Q1_Users
,(SELECT COUNT(users.id)
		FROM users
		WHERE

MeghanaThakekar

select LinkID,count(linkid) from FilterLinks where FilterID in ('-7868189828709343000','-487524855443201340')
group by LinkID having count(linkid)>1

MeghanaThakekar

update Tasks set processing=0 where id=119956

MeghanaThakekar

 select top 100 projecttype_3.ID, 
 projecttype_3.Opportunity, 
 projecttype_3.createdBy, 
 projecttype_3.OwnedBy
 ,[FileHandles].DisplayName,
 --projecttype_3.f_101,

 ( select fieldvalue from Contactfields_00000003 where contactid=( SELECT top 1 ID  FROM [Contacts] WHERE  [Contacts].[CompanyID] =  187 AND Contacts.ID IN ( SELECT [ProjectContacts].[ContactID] FROM [ProjectContacts] WHERE [ProjectContacts].[ProjectTypeID] =  3 AND [ProjectContacts].[ProjectID] =  ProjectType_3.ID ) AND [Contacts].[Status] =  1 /* Active */ )) as Customer_Name ,

 (select f_1 from AccountField_1 where companyid=187 and LinkID = (select ID from ContactCompanies where ID =( SELECT top 1 ContactCompanyID  FROM [Contacts] WHERE  [Contacts].[CompanyID] =  187 AND Contacts.ID IN ( SELECT [ProjectContacts].[ContactID] FROM [ProjectContacts] WHERE [ProjectContacts].[ProjectTypeID] =  3 AND [ProjectContacts].[ProjectID] =  ProjectType_3.ID ) AND [Contacts].[Status] =  1 /* Active */ ))) as clientid