// Inner Join, Left Join, order by and Group by
select * from customers c inner join orders o
ON c.customer_id = o.customer_id
inner join shippings s
on c.customer_id= [Link]
order by c.customer_id
select c.customer_id, c.first_name, c.last_name, [Link],o.order_id, [Link], [Link],s.shipping_id,
[Link] from customers c inner join orders o
ON c.customer_id = o.customer_id
inner join shippings s
on c.customer_id= [Link]
update orders set customer_id=5 where order_id=2
select * from customers where first_name like '%o%';
select distinct(first_name) from customers;
select * from orders where amount in (400, 12000)
left Join
select c.customer_id, c.first_name, c.last_name, [Link],o.order_id, [Link], [Link] from customers
c left join orders o
ON c.customer_id = o.customer_id
select * from ActeEngagement where clientgroupid in (select id from
clientgroup where anneecampaign=2023)
select * from clientgroup where anneecampaign=2023
select * from plateforme where fournisseurid= 18
select * from plateforme where nom like'%krill%' order by fournisseurid
update plateforme set fournisseurid=10 where id=2
select * from plateforme where id=2
select * from plateforme where nom= 'DS Armorique'
select distinct ('DS Armorique') from plateforme
select fournisseurid1 from selectiondefinitive
union
select fournisseurid2 from selectiondefinitive
select max (fournisseurid1) from selectiondefinitive
select * from selectiondefinitive where fournisseurid1=(select
max(fournisseurid1) from selectiondefinitive)
select * from selectiondefinitive where clientgroupid in (select id from
clientgroup where anneecampaign=2023) order by id
select * from selectiondefinitive where fournisseurid3 is not null
update plateforme set fournisseurid=18 where id=5101
select * from plateforme where id=5101
Left Join for 3 tables
select c.customer_id, c.first_name, c.last_name, [Link],o.order_id, [Link], [Link],s.shipping_id,
[Link] from customers c left join orders o
ON c.customer_id = o.customer_id
left join shippings s
ON c.customer_id = [Link]
Union and Union all
select customer_id from customers
union
select customer_id from orders
select customer_id from customers
union all
select customer from shippings
Max function in sub queries
select first_name, age from customers where age=(select max(age) from customers)
select * from orders where customer_id in (select customer_id from customers where age<31)
select sum(amount) from orders
select count(item) from orders
select id from clientgroup where id not in ( select clientgroupid from
selectiondefinitive where Campagneid=6) //182
select count(id) from clientgroup where grouptypeID=1
1. select nom from fournisseur where id in (select fournisseurid from
plateforme where nom='PASSIONFROID Paris Sud')
2. select adresse1 from adresse where id= (select adresseid from fournisseur
where nom='Hedis')
3. select count(id) from utilisateur_Frontoffice where clientid=36 //3
4. select count(lotid) from utilisateurlot_frontoffice where
utilisateur_frontofficeid=( select id from utilisateur_frontoffice where
nom='Lebot')
5. select lotid from fournisseurLot where fournisseurid=13 //84 lots
1. SELECT COUNT(id) from clientgroup where grouptypeid=3 //7
2. select distinct(plateformeid) from Acteengagement where clientgroupid=3 and
campagneid=6
3. No, there is no such record which exists in SD but doesn’t exists in AE
bcoz SD data itself comes from AE. So, its possible that SD doesn’t contain
some of records from AE but vice-versa cannot.
5. update fournisseur set email='abc@[Link]'
4. insert into utilisateur_frontoffice values ('abc','pqr', 'abc@[Link]',
0101101011,234560,0,'AAGRyIjTczSFLBdH9Gc1T',1,36,0,1,'monsieur',0,1)
// by default takes null values
select * from selectiondefinitive where Lotid=675
select * from acteengagement where Lotid=675 and fournisseurid=14
select distinct(plateformeid) from Acteengagement where clientgroupid=3 and
campagneid=6
select * from selectiondefinitive where Lotid not in (select Lotid from
acteengagement where fournisseurid=14 and clientgroupid=3)
select * from utilisateur_frontoffice
insert into utilisateur values (36,'john',
'chris','abc@[Link]','adjfggvuutruegfg', 1)
delete from utilisateur where id=42
insert into utilisateur_frontoffice values ('abc','dfg',
'abc@[Link]',0101101011,234560,0,'AAGRyIjTczSFLBdH9Gc1T',1,36,
0,1,'monsieur',0,1)
select * from selectiondefinitive where lotid=675
select * from utilisateur_frontoffice where fournisseurid=45
select * from Document order by id desc
select * from statut where id=5
select * from clientgrouplot where clientgroupid=350 and lotid=676
select * from lot
select [Link], [Link],[Link], [Link],[Link],[Link] from
clientgrouplot cg join lot l
on [Link]=[Link] where annee=2023 and clientgroupid=350 order by [Link]
select * from YouSign WHERE ClientGroupId=272 and SupplierId in (13,3496)
select * FROM YouSignAttachment WHERE SignatureId IN
(SELECT SignatureId from YouSign WHERE ClientGroupId=272 and SupplierId in (13,3496))
select * from utilisateur_frontoffice where clientgroupid in (279)
select * from lot where id in (628,598,626)
select * from fournisseur where id=77
select * from plateforme where fournisseurid=77
select * from Contact where fournisseurid=77
select * from utilisateur_frontoffice where clientid=36
select * from client where id=36
select * from Bpudocument order by id desc
delete from bpudocument where id=6195
delete from BPUVerificationError where bpudocumentid=6195
select * from statut where id=5
select * from document order by id desc
select [Link], [Link], [Link], [Link], [Link], [Link],
[Link] from clientgrouplot cg join lot l
on [Link]=[Link] where annee=2023 and [Link]>1 order by [Link]
-- clientgroup with duplicate lots
Select * from clientgroup where id in (select count(clientgroupid) as
total_clientgroup ,lotid from clientgrouplot group by lotid
having count(lotid)>1
Query for validate Nombre de client
//script for Nombre de client
select distinct [Link], [Link] from ClientGroupLot cgl
inner join ClientGroup cg on [Link] = [Link]
inner join lot l on [Link] = [Link]
where
[Link] = 1
and [Link] = 0
and [Link] = 2023
// To Reset DEMAT page on FO
Declare @supplierId Int = 1,@campaignId int =6
delete from FournisseurLotSurvey where FournisseurId=@supplierId and
CampaigneId=@campaignId
delete from FournisseurSurveyDepartment where FournisseurId=@supplierId and
CampaigneId=@campaignId
delete from ActeEngagement where FournisseurId=@supplierId and
CampagneId=@campaignId
update FournisseurSurveySetting set
StepToDisplay=0,
Surveystatus='',
DceDownload=0,
IsBpuViewValidated=0,
Step1Completed=0,
Step2Completed=0,
Step3Completed=0,
Step4Completed=0,
Step5Completed=0,
Step6Completed=0,
Step7Completed=0,
Step8Completed=0,
Step9Completed=0,
Step10Completed=0,
Step11Completed=0,
Step12Completed=0,
Step13Completed=0,
Step14Completed=0,
Step15Completed=0,
Step16Completed=0,
Step17Completed =0,
LastUpdatedBy='',
IsStep2Validated=0,
IsDceFileReady=1
where SupplierId=@supplierId and CampaigneId=@campaignId
select * from Document where FournisseurId=1 and Annee=2023 and TypeFichierId=1 order by id
desc
Campaigne screen:
select * from ClientGroup where IsActif=1
select top 1 [Link], [Link] from
CampaigneClientGroupMapping ccm
left join Campaigne c on [Link] =[Link]
where ClientGroupId = 20
order by [Link] desc
select * from campaigne
select * from SelectionDefinitive
where ClientGroupId =20 and CampagneId =6 // check box for cg not in 2023
select * from ClientGroup
where CampaigneId is null and IsActif = 1 // Sans Campaigne clientgroup
select distinct [Link] from ClientGroup cg
join CampaigneClientGroupMapping ccm
on [Link] = [Link]
where [Link]=1 and [Link] =6 and [Link] is not null
