Отвечай на вопросы, используя следующий контекст:
declare @basketNumber varchar(max) = 'TST-082224-EA17CA527EAD';

--Auto
select
d.DateStart,
d.DateSign,
d.DateEnd,
rg.Name as 'Регион использования',
p.Name as 'Тип полиса',
a.AutoBrandName,
a.AutoModelName,
a.AutoYearOfIssueValue,
a.AutoEnginePowerValue,
a.Vin,
case a.IsCreditAuto when 1 then sb.Name else null end as 'Банк',
a.IsCreditAuto,
a.AutoCost as 'Стоимость ТС',
ad.IsMultidrive,
ts.Series,
ts.Number,
ts.TSIdentityCardTypeCode,
ts.DateOfIssued,
a.purposeOfUsingCode
from
Basket b with(nolock)
left join Deal d with(nolock) on b.Id = d.Basket_Id
left join Product p with(nolock) on d.Product_Id = p.Id
left join InsuranceObject insObj with(nolock) on d.Id = insObj.Deal_Id
left join Auto a with(nolock) on insObj.Auto_Id = a.Id
left join AutoDeal ad with(nolock) on d.AutoDeal_Id = ad.Id
left join TsIdentityCard ts with(nolock) on ad.TsIdentityCard_Id = ts.Id
left join RegistrationRegion rg with(nolock) on a.RegionOfUsingCode = rg.Code
left join SuggestedBank sb with(nolock) on d.Id = sb.Deal_Id
where
b.Number= @basketNumber
and b.State = (select Max(State) from Basket with(nolock) where Number = @basketNumber)
and d.IsInBasket = 1


--Insurant
select
p.LastName,
p.MiddleName,
p.Name,
p.Age,
p.DateOfBirth,
p.Email1,
p.Phone1,
id.IssuedBy,
id.Series,
id.Number,
ad.KladrId,
p.InnNumber
from
Basket b with(nolock)
left join Deal d with(nolock) on b.Id = d.Basket_Id
left join Insurant ins with(nolock) on d.Insurant_Id = ins.Id
left join IdentityDocument id with(nolock) on ins.IdentityDocument_Id = id.Id
left join Subject sub with(nolock) on ins.Subject_Id = sub.Id
left join IndividualEntity ie with(nolock) on ie.Id = sub.IndividualEntity_Id
left join Person p with(nolock) on ie.Person_Id = p.Id
left join Address ad with(nolock) on p.Id = ad.Person_Id
where
b.Number = @basketNumber
and b.State = (select Max(State) from Basket with(nolock) where Number = @basketNumber)
and d.IsInBasket = 1

--Owner
select
p.LastName,
p.MiddleName,
p.Name,
p.Age,
p.DateOfBirth,
p.Email1,
p.Phone1,
id.IssuedBy,
id.Series,
id.Number,
ad.KladrId,
p.InnNumber
from
Basket b with(nolock)
left join Deal d with(nolock) on b.Id = d.Basket_Id
left join InsuranceObject insObj on d.Id = insObj.Deal_Id
left join [Owner] own on insObj.Id = own.InsuranceObject_Id
left join IdentityDocument id with(nolock) on own.IdentityDocument_Id = id.Id
left join Subject sub with(nolock) on own.Subject_Id = sub.Id
left join IndividualEntity ie with(nolock) on ie.Id = sub.IndividualEntity_Id
left join Person p with(nolock) on ie.Person_Id = p.Id
left join Address ad with(nolock) on p.Id = ad.Person_Id
where
b.Number = @basketNumber
and b.State = (select Max(State) from Basket with(nolock) where Number = @basketNumber)
and d.IsInBasket = 1


--Driver
select
p.LastName,
p.MiddleName,
p.Name,
p.Age,
p.DriveExperience,
p.DateOfBirth,
p.Email1,
p.Phone1,
dr.DrivingExperienceDateStart as 'Дата выдачи прав',
dr.FullDrivingExperience as 'Стаж',
driv.DriverLicenseSeries,
driv.DriverLicenseNumber
from
Basket b with(nolock)
left join Deal d with(nolock) on b.Id = d.Basket_Id
left join AutoDeal autodl on d.AutoDeal_Id = autodl.Id
left join AutoDealDriver autodealDrv on d.AutoDeal_Id = autodealDrv.AutoDeal_Id
left join Driver driv on autodealDrv.Driver_Id = driv.Id
left join DrivingExperience dr on driv.Id = dr.Driver_Id
left join Subject sub with(nolock) on driv.Subject_Id = sub.Id
left join IndividualEntity ie with(nolock) on ie.Id = sub.IndividualEntity_Id
left join Person p with(nolock) on ie.Person_Id = p.Id
where
b.Number = @basketNumber
and b.State = (select Max(State) from Basket with(nolock) where Number = @basketNumber)
and d.IsInBasket = 1
"Deal.InsuranceObjects.Owners.Subject.LegalEntity"

-- Получить номер корзины по номеру полиса
SELECT b.Number AS BasketNumber
FROM Deal d
JOIN Basket b ON d.Basket_Id = b.Id
WHERE d.PolicyNumber = '035AT-24/0000962-S';
INSERT INTO PriorityProlongationPolicies (PolicyNumber, IsDeleted)
VALUES ('XXX0299290199',  0);


-- Обновление настройки в таблице EntitySetting
UPDATE [EntitySetting]
SET [SettingValue] = 'true'
WHERE [EntitySettingConfigurationCode] = 'UsePriorityProlongationPolicies'
AND [EntitySettingDictionariesVersionId] = (SELECT MAX(Id) FROM [EntitySettingDictionariesVersion] WHERE [DateTo] is null)
AND [IsDeleted] =  0;



-- Посмотреть текущее значение настройки
SELECT TOP   1 [SettingValue]
FROM [EntitySetting]
WHERE [EntitySettingConfigurationCode] = 'UsePriorityProlongationPolicies'
AND [EntitySettingDictionariesVersionId] = (SELECT MAX(Id) FROM [EntitySettingDictionariesVersion] WHERE [DateTo] is null)
AND [IsDeleted] =   0;


select * from PriorityProlongationPolicies where PolicyNumber = 'XXX0299290199'

SELECT *   FROM AutoProlongationPolicy

select * from AutoProlongationPackage  where Description = 'SRL08006 - Subscription'  
order by CreateDate desc
-- VerifyUpdatedConditions.sql
-- Проверяем правильность обновления данных в таблице Condition

SELECT id, Name, Expression
FROM Condition
WHERE id IN (2010, 2011, 2012);

------------------------------
-- VerifyUpdatedCalculationFormulas.sql
-- Проверяем правильность обновления данных в таблице CalculationComponentSetting

SELECT StructId, CalculationFormula
FROM CalculationComponentSetting
WHERE StructId IN (5495, 5496, 5497);

------------------------------
-- UpdateExpressionConditions.sql
-- Обновляем выражения в таблице Condition для заданных ID
UPDATE Condition
SET Expression = 
  CASE
    WHEN id = 2010 THEN 
      'if ({attr_оффер} == 2177) {2990} else if ({attr_оффер} == 2179) {4490} else if ({attr_оффер} == 2180) {5990} else if ({attr_оффер} == 2158) {2519} else {1090}'
    WHEN id = 2011 THEN 
      'if ({attr_оффер} == 2184) {2490} else if ({attr_оффер} == 2185) {3990} else if ({attr_оффер} == 2186) {5490} else if ({attr_оффер} == 2159) {2219} else {790}'
    WHEN id = 2012 THEN 
      'if ({attr_оффер} == 2181) {1990} else if ({attr_оффер} == 2182) {3490} else if ({attr_оффер} == 2183) {4490} else if ({attr_оффер} == 2160) {1919} else {490}'
  END
WHERE id IN (2010, 2011, 2012);

------------------------------
--UpdateCalculationFormulas.sql
-- Обновляем формулы расчета в таблице CalculationComponentSetting для заданных StructId
UPDATE CalculationComponentSetting
SET CalculationFormula = 'var result = {coeff_C_DisastrerAndFallingObject}; result;'
WHERE StructId = 5497;

UPDATE CalculationComponentSetting
SET CalculationFormula = 'var result = {coeff_C_FallingObject}; result;'
WHERE StructId = 5496;

UPDATE CalculationComponentSetting
SET CalculationFormula = 'var result = {coeff_C_Disastrer}; result;'
WHERE StructId = 5495;
-- Проверка статуса urm по номеру корзины
declare @basket_number varchar(50) = 'DEV-081424-683D02057E94'  
select u.URMApprovalStatusCode,u.* from URMApproval u
join AutoDeal ad on ad.id = u.AutoDeal_Id
join Deal d on d.AutoDeal_Id = ad.Id
join basket b on b.id = d.Basket_Id
where b.Number = @basket_number AND u.State = 2
-- Включить/отключить страницу "Спасибо"
SELECT [Percent]
FROM [dbo].[ABTestVariant]
WHERE [Code] = 'KaskoFeedbackOn';

UPDATE [dbo].[ABTestVariant]
SET [Percent] = CASE 
                    WHEN [Code] = 'KaskoFeedbackOn' THEN 100
                    WHEN [Code] = 'KaskoFeedbackOff' THEN 0
                END
WHERE [Code] IN ('KaskoFeedbackOn', 'KaskoFeedbackOff');
-- Все данные по посреднику с внутренним именем 'Yandex',
SELECT *
FROM [dbo].[Intermediary]
WHERE [IntermediaryInternalName] = 'Yandex'
AND [State] = 2
AND [IsDeleted] = 0
AND [InteractionTypeCode] = 'Broker';

-- Проверить какие offerGroup привязаны к Яндекс Intermediary.
SELECT iog.Intermediary_Id, og.Code
FROM IntermediaryOfferGroup iog
JOIN OfferGroup og ON iog.OfferGroup_Id = og.Id
WHERE iog.Intermediary_Id = (SELECT TOP 1 Id FROM [dbo].Intermediary WHERE [IntermediaryInternalName] = 'Yandex' AND [State] = 2 AND [IsDeleted] = 0 AND [InteractionTypeCode] = 'Broker');

-- Смотреть Product_id = 14
select * from IntermediaryToCostcenter where Intermediary_Id = (SELECT TOP 1 Id FROM [dbo].Intermediary WHERE [IntermediaryInternalName] = 'Yandex' AND [State] = 2 AND [IsDeleted] = 0 AND [InteractionTypeCode] = 'Broker') AND [State] = 2 AND [IsDeleted] = 0;
-- Получить проверки андеррайтинга по номеру корзины
DECLARE @basket_number varchar(50) = 'DEV-092424-8D14944541A4';
SELECT dia.StatusCode, dia.*
FROM Basket b WITH (NOLOCK)
JOIN Deal d WITH (NOLOCK) ON b.Id = d.Basket_Id
JOIN AutoDeal ad WITH (NOLOCK) ON d.AutoDeal_Id = ad.Id
JOIN DIUnderwriterApproval dia WITH (NOLOCK) ON ad.Id = dia.AutoDeal_Id
WHERE b.Number = @basket_number
AND b.State = (SELECT MAX(State) FROM Basket WHERE Number = @basket_number)
AND d.IsInBasket = 1;
select * from PrintingFormOfferStruct
where OfferStruct_Id = 10562 and PrintingForm_Id = 250

select * from Offer


SELECT os.Id as OfferStructId
		FROM OfferStruct os
			LEFT JOIN ProductConfiguration pc ON os.ProductConfigurationEntityId = pc.EntityId
		WHERE Offer_Id = 2116 AND pc.State = 2 AND pc.ParentStructId IS NULL AND os.State = 2 AND os.IsDeleted = 0
		ORDER BY OfferStructId

select * from PrintingForm where Code = 'KID_KASKO'
DECLARE @IntermediaryInternalName varchar(255) = 'Yandex';

-- Получение Id посредника
DECLARE @IntermediaryId int = (SELECT TOP 1 Id FROM [dbo].Intermediary WHERE [IntermediaryInternalName] = @IntermediaryInternalName AND [State] = 2 AND [IsDeleted] = 0 AND [InteractionTypeCode] = 'Broker');

-- Обновление значения CostcenterCode на 'SRL08010' для существующего посредника
UPDATE [Intermediary] SET
    CostcenterCode = 'SRL08010',
    isThemingNeeded = 0,
    AllowRefByURL = 1
WHERE Id = @IntermediaryId;

-- Обновляем IntermediaryToCostcenter
UPDATE IntermediaryToCostcenter
SET CostcenterCode = 'SRL08010'
WHERE Intermediary_Id = (SELECT TOP 1 Id FROM Intermediary WHERE Code = 'YAPartners' AND State = 2 AND IsDeleted = 0 ORDER BY IntermediaryStatusCode)
AND Product_Id = (SELECT id FROM Product WHERE ProductDictCode = 'KASKO' AND State = 2 AND IsDeleted = 0)
AND IsDeleted = 0;
-- Посмотреть все коэффициенты по номеру корзины
SELECT ccr.CalculationComponentSetting_Id, b.Number, d.BranchCode, ccr.Value, coefcr.CalculationComponentCoefficient_Id, c.Id, c.Name, coefcr.Value FROM ComponentCalculationResult ccr
	JOIN CoefficientCalculationResult coefcr ON ccr.Id = coefcr.ComponentCalculationResult_Id
	JOIN CalculationComponentCoefficient ccc ON coefcr.CalculationComponentCoefficient_Id = ccc.Id
	--JOIN CalculationComponentCoefficientComponentTarifficationAttribute ccccta ON ccc.Id = ccccta.CalculationComponentCoefficient_Id
	--JOIN ComponentTarifficationAttribute cta ON ccccta.ComponentTarifficationAttribute_Id = cta.Id
	--JOIN TarifficationAttribute ta ON cta.TarifficationAttribute_Id = ta.Id
	JOIN Coefficient c ON ccc.Coefficient_Id = c.Id
	JOIN Proposal p ON ccr.Proposal_Id = p.Id
	JOIN Deal d ON p.Deal_Id = d.Id
	JOIN Basket b ON d.Basket_Id = b.Id
	JOIN InsuranceObject io on io.Deal_Id = d.Id
	JOIN Auto a on a.Id = io.Auto_Id
WHERE
	b.Number IN ('DEV-070324-CA64BAFB70F1')
	AND p.IsSelected = 1
	AND p.State = 2
	AND ccr.State = 2
ORDER BY ccr.CalculationComponentSetting_Id, coefcr.CalculationComponentCoefficient_Id, b.Number
--- Подставить номер своей котировки
---Проверка опций риск и франшиз по котировки

declare @basket_number varchar(50) = 'TST-112123-77210D8AED4E'

declare @dealId table (dealId int)
insert @dealId
select d.Id from Basket b with(nolock)
join Deal d with(nolock) on d.Basket_Id = b.Id
where
b.Number = @basket_number
--in (Select BasketNumber from AutoProlongationPolicy where AutoProlongationPackage_Id = 389 and Status=50) --= @basket_number

select distinct 
b.Price, b.Number, p.Offer_Id, ccr.Value, p.TotalSum as PTotalSum, dc.StructId, pc.CoverageCode, dc.Sum as CovSum, dr.StructId, pr.RiskCode, dr.Sum as RiskSum,
dfr.StructId, pfr.Code as RCovFran, dfr.Sum as RCovFranSum,
do.StructId, po.OptionCode as CovOption, do.Sum as CovOptionSum,
dor.StructId, por.OptionCode as RiskOption, dor.Sum as RiskOptionSum,
df.StructId, pf.Code as CovFran, df.Sum as CovFranSum
, b.Price, b.State
from Basket b with(nolock)
join Deal d with(nolock) on d.Basket_Id = b.Id
left outer join DealConditionGroup dcg with(nolock) on dcg.Deal_Id = d.Id
left outer join InsuranceObjectCondition ioc with(nolock) on ioc.DealConditionGroup_Id = dcg.Id
left outer join DealCoverage dc with(nolock) on dc.InsuranceObjectCondition_Id = ioc.Id
left outer join ComponentCalculationResult ccr with (nolock) on ccr.Id = dc.ComponentCalculationResult_Id
left outer join ProductCoverage pc with(nolock) on pc.Id = dc.ProductCoverage_Id
left outer join Proposal p with(nolock) on p.Deal_Id = d.Id
left outer join DealRisk dr with(nolock) on dr.DealCoverage_Id = dc.Id
left outer join ProductRisk pr with(nolock) on pr.Id = dr.ProductRisk_Id
left outer join ConditionBlock cb with(nolock) on cb.Id = dc.ConditionBlock_Id
left outer join DealOption do with(nolock) on do.ConditionBlock_Id = cb.Id
left outer join ProductOption po with(nolock) on po.Id = do.ProductOption_Id
left outer join DealFranchise df with(nolock) on df.ConditionBlock_Id = cb.Id
left outer join ProductFranchise pf with(nolock) on pf.Id = df.ProductFranchise_Id
left outer join ConditionBlock cbr with(nolock) on cbr.Id = dr.ConditionBlock_Id
left outer join DealOption dor with(nolock) on dor.ConditionBlock_Id = cbr.Id
left outer join ProductOption por with(nolock) on por.Id = dor.ProductOption_Id
left outer join DealFranchise dfr with(nolock) on dfr.ConditionBlock_Id = cbr.Id
left outer join ProductFranchise pfr with(nolock) on pfr.Id = dfr.ProductFranchise_Id

where d.Id in (select * from @dealId)
--and d.Product_Id in (14)
and d.State in (2)
And p.ISSELECTED = 1
--and  pr.RiskCode = 'CIVILLIABILITY'
order by dc.StructId asc;
-- Для поиска полисов для автопролонгации
SELECT TOP (10000) *   
FROM [AutoProlongationPolicy] 
 --- where Description = 'SRL08006 - Subscription'
 where CreateDate >= '20230210'  
---and AutoProlongationPackage_ID in (5760)  
---and status in (20, 50, 40)  
order by CreateDate desc 
  ---  Пакеты по полисам 
select TOP (1000) * 
from AutoProlongationPackage 
--- where Description = 'SRL08006 - Subscription' 
 order by CreateDate desc 
-- Получаем номер корзины апсейла по номеру корзины осаго
select UpsaleSourceBasketNumber from Basket nolock where Number = 'DEV-100224-F764E7EEF5F5'
Что бы сделать запрос к определенной базе данных на ветке, нужно нажать правой кнопкой на сервер: ren-webtestdb01.itlab.renins.com и выбрать "New Query". В самом запросе нужно указать определенную базу с нужным МР, например:
USE [IrisInsuranceDB-Kasko-merge-requests-4393];
SELECT * FROM Basket;
как получить сделку по корзине