========================================================================================= 
                          Формы запросов ПК "Метролог v2.x"                                
 ========================================================================================= 
 Текст запроса вставлять в верхнее окно интерфейса появляющегося при выполнении команды    
 меню  Файл-Создать                                                                        
 Все запросы отдельные. Т.е. нельзя выполнять сразу весь нижележащий текст.                
 ========================================================================================= 
execution_analyze1.sql 
 ========================================================================================= 
--анализ по приборам
use metrology2
declare @@bill_year int, @@month_begin int, @@month_end int

set @@bill_year = 2008 --год анализа
set @@month_begin = 1 --начало периода, месяц
set @@month_end = 12 --конец пкриода, месяц
-----------------------------------------------------------------------------------------------------------------
--здесь указывается год и месяцы анализа, будет сравнивать этот год с прошлым
-----------------------------------------------------------------------------------------------------------------

select branch_name as [Филиал], subdivision_name as [Отдел], measure_type_name as [Вид измерений], instrument_category_name as [Прибор], isnull(e1.instrument_count, 0) as [поверено в прошлом году], isnull(e2.instrument_count, 0) as [поверено в этом году], isnull(e1.defective_count, 0) as [брак в прошлом году], isnull(e2.defective_count, 0) as [брак в этом году], @@bill_year
from 
(
select instrument_category_id, branch_id, subdivision_id from bill_instruments bi
inner join bills b
on bi.bill_id = b.bill_id
inner join instruments i
on bi.instrument_id = i.instrument_id
inner join services s
on s.service_id = bi.service_id and verification = 1
--where service_id not in ('20CB75D1-4C29-427C-848D-079B299DE078', '0D328B08-AEBA-4EC5-A3D4-1E707F3C1C22', '521AA033-AA5F-44AA-9C63-D92518167FDE', 'ECECFE69-6E6C-4A76-974F-907DB451B6DF', '9776748B-696B-4A06-AAFD-DC1B71555CA7')
group by instrument_category_id, branch_id, subdivision_id
) bi

left join
(
  select branch_id, subdivision_id, instrument_category_id, sum(real_count) as instrument_count, sum(defective_count) as defective_count from bill_service_executions bse
  inner join bill_instruments bi
  on bse.bill_instrument_id = bi.bill_instrument_id
  inner join instruments i
  on bi.instrument_id = i.instrument_id
  inner join bills b
  on bi.bill_id = b.bill_id
  where year(execution_end_date) = @@bill_year-1 and month(execution_end_date) between @@month_begin and @@month_end
  group by branch_id, instrument_category_id, subdivision_id
) e1
on bi.instrument_category_id = e1.instrument_category_id and bi.branch_id = e1.branch_id and bi.subdivision_id = e1.subdivision_id

left join
(
  select branch_id, subdivision_id, instrument_category_id, sum(real_count) as instrument_count, sum(defective_count) as defective_count from bill_service_executions bse
  inner join bill_instruments bi
  on bse.bill_instrument_id = bi.bill_instrument_id
  inner join instruments i
  on bi.instrument_id = i.instrument_id
  inner join bills b
  on bi.bill_id = b.bill_id
  where year(execution_end_date) = @@bill_year and month(execution_end_date) between @@month_begin and @@month_end
  group by branch_id, instrument_category_id, subdivision_id
) e2
on bi.instrument_category_id = e2.instrument_category_id and bi.branch_id = e2.branch_id and bi.subdivision_id = e2.subdivision_id

inner join instrument_categories ic
on bi.instrument_category_id = ic.instrument_category_id
inner join measure_types m
on ic.measure_type_id = m.measure_type_id
inner join branchs b
on bi.branch_id = b.branch_id
inner join subdivisions s
on bi.subdivision_id = s.subdivision_id

where e2.instrument_category_id is not null or e1.instrument_category_id is not null
order by branch_name, subdivision_name, measure_type_name, instrument_category_name
==================================block=end==============================================



==================================block=start============================================
execution_analyze2.sql 
 ========================================================================================= 
--анализ по заказчикам
use metrology2
declare @@bill_year int, @@month_begin int, @@month_end int

set @@bill_year = 2008 --год анализа
set @@month_begin = 1 --начало периода, месяц
set @@month_end = 12 --конец пкриода, месяц

-----------------------------------------------------------------------------------------------------------------
--здесь указывается год и месяцыанализа, будет сравнивать этот год с прошлым
-----------------------------------------------------------------------------------------------------------------

select branch_name as [Филиал], subdivision_name as [Отдел], client_name as [Заказчик], isnull(e1.instrument_count, 0) as [поверено в прошлом году], isnull(e2.instrument_count, 0) as [поверено в этом году], isnull(e1.defective_count, 0) as [брак в прошлом году], isnull(e2.defective_count, 0) as [брак в этом году], @@bill_year
from 
(
select client_id, branch_id, subdivision_id from bill_instruments bi
inner join bills b
on bi.bill_id = b.bill_id
inner join contracts c
on b.contract_id = c.contract_id
inner join services s
on s.service_id = bi.service_id and verification = 1
--where service_id not in ('20CB75D1-4C29-427C-848D-079B299DE078', '0D328B08-AEBA-4EC5-A3D4-1E707F3C1C22', '521AA033-AA5F-44AA-9C63-D92518167FDE', 'ECECFE69-6E6C-4A76-974F-907DB451B6DF', '9776748B-696B-4A06-AAFD-DC1B71555CA7')
group by client_id, branch_id, subdivision_id
) bi

left join
(
	select branch_id, subdivision_id, client_id, sum(real_count) as instrument_count, sum(defective_count) as defective_count from bill_service_executions bse
	inner join bill_instruments bi
	on bse.bill_instrument_id = bi.bill_instrument_id
	inner join bills b
	on bi.bill_id = b.bill_id
	inner join contracts c
	on b.contract_id = c.contract_id
--	where year(execution_end_date) = @@bill_year-1 and month(execution_end_date) < 7
        where year(execution_end_date) = @@bill_year-1 and month(execution_end_date) between @@month_begin and @@month_end

	group by branch_id, client_id, subdivision_id
) e1
on bi.client_id = e1.client_id and bi.branch_id = e1.branch_id and bi.subdivision_id = e1.subdivision_id

left join
(
	select branch_id, subdivision_id, client_id, sum(real_count) as instrument_count, sum(defective_count) as defective_count from bill_service_executions bse
	inner join bill_instruments bi
	on bse.bill_instrument_id = bi.bill_instrument_id
	inner join bills b
	on bi.bill_id = b.bill_id
	inner join contracts c
	on b.contract_id = c.contract_id

--	where year(execution_end_date) = @@bill_year and month(execution_end_date) < 7
        where year(execution_end_date) = @@bill_year and month(execution_end_date) between @@month_begin and @@month_end

	group by branch_id, client_id, subdivision_id
) e2
on bi.client_id = e2.client_id and bi.branch_id = e2.branch_id and bi.subdivision_id = e2.subdivision_id

inner join clients c
on bi.client_id = c.client_id
inner join branchs b
on bi.branch_id = b.branch_id
inner join subdivisions s
on bi.subdivision_id = s.subdivision_id

where e2.client_id is not null or e1.client_id is not null
order by branch_name, subdivision_name, client_name
==================================block=end==============================================



==================================block=start============================================
mark_dup.sql 
 ========================================================================================= 
select * from metrcontrol_marks
where mark in (
select mark from metrcontrol_marks
group by mark
having (count(*) > 1)
)
order by mark

==================================block=end==============================================



==================================block=start============================================
tariffs.sql 
 ========================================================================================= 
select measure_type_name, instrument_category_name, instrument_name, instrument_model, register_number, service_name, tariff from measure_types m
inner join instrument_categories ic
on m.measure_type_id = ic.measure_type_id
left join instruments i
on ic.instrument_category_id = i.instrument_category_id
left join official_register r
on i.instrument_id = r.instrument_id
left join tariffs t
on ic.instrument_category_id = t.instrument_category_id and service_place_id = '610045D3-8827-4495-9D36-5B7775BF6540'
left join services s
on t.service_id = s.service_id
order by measure_type_name, instrument_category_name, instrument_name, instrument_model, service_name

==================================block=end==============================================



==================================block=start============================================
TRASPORT.SQL 
 ========================================================================================= 

insert into metrology2..banks
	select * from banks where bank_id not in (select bank_id from metrology2..banks)
	and bank_name not in (select bank_name from metrology2..banks)
update metrology2..banks
	set metrology2..banks.bank_id = banks.bank_id
from banks
	where metrology2..banks.bank_name = banks.bank_name
	and metrology2..banks.bank_id <> banks.bank_id

insert into metrology2..localities
	select * from localities where locality_id not in (select locality_id from metrology2..localities)
	and locality_name not in (select locality_name from metrology2..localities)
update metrology2..localities
	set metrology2..localities.locality_id = localities.locality_id
from localities
	where metrology2..localities.locality_name = localities.locality_name
	and metrology2..localities.locality_id <> localities.locality_id

insert into metrology2..clients
	select * from clients where client_id not in (select client_id from metrology2..clients)
	and client_name not in (select client_name from metrology2..clients)
	and locality_id in (select locality_id  from metrology2..localities)

insert into metrology2..clients_enterprises
	select * from clients_enterprises where client_id not in (select client_id from metrology2..clients_enterprises)
	and client_id in (select client_id from metrology2..clients)

insert into metrology2..clients_privates
	select * from clients_privates where client_id not in (select client_id from metrology2..clients_privates)
	and client_id in (select client_id from metrology2..clients)

insert into metrology2..clients_persons
	select * from clients_persons where client_id not in (select client_id from metrology2..clients_persons)
	and client_id in (select client_id from metrology2..clients)

insert into metrology2..clients_branchs
	select *, null from clients_branchs where client_branch_id not in (select client_branch_id from metrology2..clients_branchs)
	and client_id in (select client_id from metrology2..clients)

insert into metrology2..contracts (contract_id, client_id, contract_number, contract_date, begin_date, end_date, notes, contract_setup_id, add_date, add_user)
	select contract_id, client_id, contract_number, contract_date, begin_date, end_date, notes, contract_setup_id, add_date, add_user from contracts where contract_id not in (select contract_id from metrology2..contracts)
	and client_id in (select client_id from metrology2..clients)
	and contract_number + cast(contract_year as char(4)) not in (select contract_number + cast(contract_year as char(4)) from metrology2..contracts)

insert into metrology2..schedulies
	select * from schedulies where contract_id in (select contract_id from metrology2..contracts)
	and contract_id not in  (select contract_id from metrology2..schedulies)

alter table metrology2..schedule_years disable trigger all
insert into metrology2..schedule_years
	select * from schedule_years where contract_id in (select contract_id from metrology2..schedulies)
	and schedule_id not in (select schedule_id from metrology2..schedule_years)
and cast(contract_id as char(40))+cast(isnull(client_branch_id,'{A1A7179D-232C-4D7E-A8CB-303F42180C8A}') as char(40))+cast(schedule_year as char(4)) not in (select cast(contract_id as char(40))+cast(isnull(client_branch_id,'{A1A7179D-232C-4D7E-A8CB-303F42180C8A}') as char(40))+cast(schedule_year as char(4)) from metrology2..schedule_years)
alter table metrology2..schedule_years enable trigger all

alter table metrology2..schedule_instruments disable trigger all
insert into metrology2..schedule_instruments
	select * from schedule_instruments where schedule_id in (select schedule_id from metrology2..schedule_years)
and schedule_instrument_id in
(
select s1.schedule_instrument_id from schedule_instruments s1
left join metrology2..schedule_instruments s2
on s1.schedule_id = s2.schedule_id
and s1.maintenance_type_id = s2.maintenance_type_id
and s1.instrument_id = s2.instrument_id
and s1.schedule_month = s2.schedule_month
where s2.schedule_instrument_id is null
)
and instrument_id in (select instrument_id from metrology2..instruments)
and schedule_instrument_id not in (select schedule_instrument_id from metrology2..schedule_instruments)
and executor_id in (select client_id from metrology2..clients)
alter table metrology2..schedule_instruments enable trigger all

insert into metrology2..schedule_instruments_vendor_numbers	
	select * from schedule_instruments_vendor_numbers where schedule_instrument_id in (select schedule_instrument_id from metrology2..schedule_instruments)

==================================block=end==============================================



==================================block=start============================================
Вычисление расходов по дополнительным тарифам (справочник счета) по подразделениям.sql 
 ========================================================================================= 
-- Вычисление расходов по дополнительным тарифам (справочник счета) по подразделениям
set dateformat dmy
select branch_name, subdivision_name, count(*) as [кол.], sum(tariff) as [сум.] from bill_addition_tariffs bat
inner join bills_calcs bc
on bat.bill_id = bc.bill_id and close_date >= '01.08.2003' and close_date < '1.09.2003' and closed = 1
-- даты закрытия                               ^^^^^^^^^^                    ^^^^^^^^^
inner join bills bs
on bc.bill_id = bs.bill_id
inner join branchs b
on bs.branch_id = b.branch_id
inner join subdivisions s
on bat.subdivision_id = s.subdivision_id
-- указать addition_tariff_id нужного тарифа
-- например транспортные расходы id={BD42F83F-1E00-478C-BAE3-12B5F73C6972}
-- соответственно внизу пишем '{BD42F83F-1E00-478C-BAE3-12B5F73C6972}'
-- чтобы узнать id выполните запрос: select * from addition_tariffs
where addition_tariff_id = '{BD42F83F-1E00-478C-BAE3-12B5F73C6972}'
group by branch_name, subdivision_name
order by branch_name, subdivision_name

==================================block=end==============================================



==================================block=start============================================
График предприятия с суммами.sql 
 ========================================================================================= 
declare @@client_name varchar(255), @@schedule_year smallint, @@begin_month tinyint, @@end_month tinyint
set @@client_name = 'ГУЗ "ОБЛАСТНОЙ ПЕРИНАТАЛЬНЫЙ ЦЕНТР"' -- точное наименование заказчика
set @@schedule_year = 2006  -- год
set @@begin_month = 1   -- начало периода (включительно)
set @@end_month = 12    -- конец периода (включительно)

select instrument_name as [СИ], schedule_month as [Срок], count_perfect as [Кол-во], count_defective as [Неиспр.], tariff as [Тариф],
count_perfect * tariff as [Сумма без НДС], count_perfect * tariff * 1.18 as [Сумма с НДС]
from schedule_years sy
inner join contracts c
on sy.contract_id = c.contract_id
inner join clients cl
on c.client_id = cl.client_id
inner join schedule_instruments si
on sy.schedule_id = si.schedule_id
inner join instruments i
on si.instrument_id = i.instrument_id
left join tariffs t				--тариф
on t.service_id = '{0667A251-886F-4ACF-9159-C78F94B935B1}' and t.service_place_id = si.service_place_id and t.instrument_category_id = i.instrument_category_id
	and t.commit_date =
		 (select top 1 t1.commit_date from tariffs t1
		where t1.commit_date <= getdate() and t1.service_id = '{0667A251-886F-4ACF-9159-C78F94B935B1}' and t1.service_place_id = si.service_place_id AND t1.instrument_category_id = i.instrument_category_id
		order by t1.commit_date desc)

where client_name = @@client_name and schedule_year = @@schedule_year and schedule_month between @@begin_month and @@end_month
and (executor_id is null or executor_id in (select client_id from attribute))
==================================block=end==============================================



==================================block=start============================================
Графики (не сдавшие ни одного прибора).sql 
 ========================================================================================= 
set dateformat dmy
declare @@start_date datetime, @@end_date datetime

set @@start_date = '01.01.2009'
set @@end_date = '01.03.2009'

select distinct client_name from schedule_years sy
inner join schedule_instruments si
on sy.schedule_id = si.schedule_id
inner join contracts c
on sy.contract_id = c.contract_id
inner join clients cl
on c.client_id = cl.client_id
where (schedule_year between year(@@start_date) and year(end_date)) and (schedule_month between month(@@start_date) and month(end_date))
and c.client_id not in
(
select client_id from bills b
inner join bill_instruments bi
on b.bill_id = bi.bill_id
inner join bill_service_executions bse
on bi.bill_instrument_id = bse.bill_instrument_id
--inner join bills_calcs bc
--on b.bill_id = bc.bill_id and closed = 1
inner join contracts c
on b.contract_id = c.contract_id
where (bill_date between @@start_date and end_date) and service_id in (select service_id from services where verification = 1)
)


==================================block=end==============================================



==================================block=start============================================
Графики с пустыми номераи.sql 
 ========================================================================================= 
declare @schedule_year smallint


set  @schedule_year = 2010


select client_name from contracts c
inner join clients cl
on c.client_id = cl.client_id
where contract_id in (
select contract_id from schedule_years
where schedule_number is null and  schedule_year = @schedule_year)
==================================block=end==============================================



==================================block=start============================================
Для сотрудников.sql 
 ========================================================================================= 
set dateformat dmy
declare @start_date datetime, @stop_date datetime
set @start_date = '01.01.2008'
set @stop_date = '01.07.2008'
select client_name as [Сотрудник], bill_sum as [Сумма всего], bill_cost as [Сумма без НДС], bill_number as [З/С №], bill_date as [З/С дата] from bills b
inner join bill_instruments bi
on b.bill_id = bi.bill_id
inner join bill_service_executions bse
on bi.bill_instrument_id = bse.bill_instrument_id
inner join instruments i
on bi.instrument_id = i.instrument_id
inner join instrument_categories ic
on i.instrument_category_id = ic.instrument_category_id
inner join bills_calcs bc
on b.bill_id = bc.bill_id
inner join contracts c
on b.contract_id = c.contract_id
inner join clients cl
on c.client_id = cl.client_id
where instrument_category_name like '%ДЛЯ СОТРУДНИКОВ ЦСМ%'
and execution_end_date between @start_date and @stop_date
order by client_name, bill_number
==================================block=end==============================================



==================================block=start============================================
для ТВ.sql 
 ========================================================================================= 
declare @@report_year smallint

set @@report_year = 2007 --указать год, он будет сравнивать с предыдущим

select instrument_category_name as [СИ], s1.count as [прошлый], isnull(s2.count, 0) as [текущий] from (
select instrument_category_id, sum(count_perfect) as count from schedule_instruments si
inner join instruments i
on si.instrument_id = i.instrument_id
where schedule_id in (select schedule_id from schedule_years where schedule_year = @@report_year-1)
group by instrument_category_id
) s1
full join (
select instrument_category_id, sum(count_perfect) as count from schedule_instruments si
inner join instruments i
on si.instrument_id = i.instrument_id
where schedule_id in (select schedule_id from schedule_years where schedule_year = @@report_year)
group by instrument_category_id
) s2
on s1.instrument_category_id = s2.instrument_category_id
inner join instrument_categories i
on isnull(s1.instrument_category_id, s2.instrument_category_id) = i.instrument_category_id


where isnull(s1.count, 0)     >      isnull(s2.count, 0)


order by measure_type_id,  instrument_category_name
==================================block=end==============================================



==================================block=start============================================
Должники.sql 
 ========================================================================================= 
select client_name as [Заказчик], bill_date as [С/З дата], bill_number as [С/З номер], bill_sum as [С/З сумма], bill_payment_sum as [Оплачено]
from vw_get_debtors cl
inner join clients cc
on cl.client_id = cc.client_id
inner join contracts c
on cl.client_id = c.client_id
inner join bills b
on c.contract_id = b.contract_id
inner join bills_calcs bc
on b.bill_id = bc.bill_id
where bill_sum > bill_payment_sum
order by client_name, bill_date, bill_number
==================================block=end==============================================



==================================block=start============================================
дублирование приборов в справочнике.sql 
 ========================================================================================= 
select instrument_name as [СИ наименование], instrument_model as [СИ тип], register_number as [№ вгос.реестре], instrument_category_name as [Тарифная группа] from instruments  i
inner join instrument_categories ic
on i.instrument_category_id = ic.instrument_category_id
left join official_register r
on i.instrument_id = r.instrument_id
where instrument_name + instrument_model in (
select instrument_name + instrument_model from instruments
group by instrument_name, instrument_model
having count(*) > 1) 
order by instrument_name, instrument_model

==================================block=end==============================================



==================================block=start============================================
Инженеро-день.sql 
 ========================================================================================= 
set dateformat dmy
declare @@start_date datetime, @@end_date datetime
set @@start_date = '01.01.2007'
set @@end_date = '31.12.2007'
select branch_name, subdivision_name, client_name, bill_number, bill_date, tariff from bills b
inner join bills_calcs bc
on b.bill_id = bc.bill_id
inner join contracts c
on b.contract_id = c.contract_id
inner join clients cl
on c.client_id = cl.client_id
inner join branchs br
on b.branch_id = br.branch_id
inner join bill_addition_tariffs bat
on b.bill_id = bat.bill_id
inner join subdivisions s
on bat.subdivision_id = s.subdivision_id
where addition_tariff_id = '63FA3298-CD72-4893-A83F-4D3B64B4E425'
and close_date between @@start_date and @@end_date
order by branch_name, subdivision_name, client_name, bill_date, bill_number
==================================block=end==============================================



==================================block=start============================================
Клиенты не сдавшие графики за указанный год.sql 
 ========================================================================================= 
select substring(client_name,1,255) from vw_valid_contracts co
inner join clients c
on co.client_id = c.client_id and client_type_id = 1
left join schedule_years s
on co.contract_id = s.contract_id and client_branch_id is null
where s.contract_id is null --and schedule_year = 2005

==================================block=end==============================================



==================================block=start============================================
Кол_во поверенных пиборов по предприятиям.sql 
 ========================================================================================= 
select sum(instrument_count) from clients cl
inner join contracts c
on cl.client_id = c.client_id
inner join bills b
on c.contract_id = b.contract_id
inner join bills_calcs bc
on b.bill_id = bc.bill_id and closed = 1
inner join bill_instruments bi
on b.bill_id = bi.bill_id
where 
year(close_date) = 2008
and
client_name like '%больница%'
==================================block=end==============================================



==================================block=start============================================
Количество поверенных приборов (по тар_группам)(индивидуально).sql 
 ========================================================================================= 
--Количество поверенных приборов
set dateformat dmy
declare @@begin_date datetime, @@end_date datetime, @@instrument varchar(255)
set dateformat dmy
set @@instrument = 'нивелир'        --прибор точное название
set @@begin_date = '01.01.2005'    --начало периода
set @@end_date = '31.12.2005'        --конец периода

select branch_name as [Филиал], subdivision_name as [Отдел], client_name as [Клиент], sum(real_count) as [Кол-во поверенных], sum(real_count*tariff) as [Сумма], sum(defective_count) as [Кол-во неисправных],
ltrim(isnull(locality_name, '') + ' ' + isnull(cast(zip_code as varchar(6)), '') + ' ' +isnull(address, '')) as [Адрес]
from bill_service_executions bse
inner join workers w
on w.worker_id = bse.worker_id 
inner join branchs br
on w.branch_id = br.branch_id
inner join subdivisions su
on w.subdivision_id = su.subdivision_id
inner join bill_instruments bi
on bse.bill_instrument_id = bi.bill_instrument_id
inner join services s
on bi.service_id = s.service_id and verification = 1
inner join bills b
on bi.bill_id = b.bill_id
inner join contracts c
on b.contract_id = c.contract_id
inner join clients cl
on c.client_id = cl.client_id
inner join instruments i
on bi.instrument_id = i.instrument_id --and instrument_name like '%'+@@instrument+'%'
inner join instrument_categories ic
on i.instrument_category_id = ic.instrument_category_id and instrument_category_name like '%'+@@instrument+'%'
inner join vw_instruments_services_cost isc
on isc.bill_instrument_id = bi.bill_instrument_id
left join localities l
on cl.locality_id = l.locality_id

where execution_end_date between @@begin_date and @@end_date
group by branch_name, subdivision_name, client_name, locality_name, zip_code, address
==================================block=end==============================================



==================================block=start============================================
Количество поверенных приборов по моделям (всех).sql 
 ========================================================================================= 
set dateformat dmy
declare @@begin_date datetime, @@end_date datetime
set @@begin_date = '01.01.2009'     --начало периода отчета
set @@end_date = '31.12.2009'         --конец период отчета
select  branch_name as [Филиал], subdivision_name as [Отдел], measure_type_name as [Вид измерений], instrument_name as [Прибор], instrument_model as [Модель], service_place_name as [Место поверки], sum(real_count) as [Кол-во], sum(defective_count) as [Кол-во неиспр.], tariff as [Тариф], sum(tariff*real_count) as [Сумма]
from bill_service_executions bse
inner join bill_instruments bi
on bse.bill_instrument_id = bi.bill_instrument_id
inner join services se
on bi.service_id = se.service_id and verification = 1
inner join subdivisions s
on bi.subdivision_id = s.subdivision_id
inner join bills b
on bi.bill_id = b.bill_id
inner join service_places sp
on b.service_place_id = sp.service_place_id
inner join branchs br
on b.branch_id = br.branch_id
inner join instruments i
on bi.instrument_id = i.instrument_id
inner join instrument_categories ic
on i.instrument_category_id = ic.instrument_category_id
inner join measure_types mt
on ic.measure_type_id = mt.measure_type_id

LEFT JOIN tariffs t				--тариф
ON t.service_id = bi.service_id AND t.service_place_id = b.service_place_id AND t.instrument_category_id = i.instrument_category_id
	AND t.commit_date =
		 (SELECT TOP 1 commit_date FROM tariffs
		WHERE commit_date <= b.bill_date AND service_id = bi.service_id AND service_place_id = b.service_place_id AND instrument_category_id = i.instrument_category_id
		ORDER BY commit_date DESC)

where execution_end_date between @@begin_date and @@end_date
group by branch_name, subdivision_name, measure_type_name, instrument_name, instrument_model, service_place_name, tariff
order by branch_name, subdivision_name, measure_type_name, instrument_name, instrument_model, service_place_name
==================================block=end==============================================



==================================block=start============================================
Количество поверенных приборов(всех).sql 
 ========================================================================================= 
set dateformat dmy
declare @@begin_date datetime, @@end_date datetime
set @@begin_date = '01.01.2012'     --начало периода отчета
set @@end_date = '31.12.2012'         --конец период отчета
select  branch_name as [Филиал], subdivision_name as [Отдел], measure_type_name as [Вид измерений], instrument_category_name as [Прибор], service_place_name as [Место поверки], sum(real_count) as [Кол-во], tariff as [Тариф], sum(tariff*real_count) as [Сумма]
from bill_service_executions bse
inner join bill_instruments bi
on bse.bill_instrument_id = bi.bill_instrument_id
inner join services se
on bi.service_id = se.service_id and verification = 1
inner join subdivisions s
on bi.subdivision_id = s.subdivision_id
inner join bills b
on bi.bill_id = b.bill_id
inner join service_places sp
on b.service_place_id = sp.service_place_id
inner join branchs br
on b.branch_id = br.branch_id
inner join instruments i
on bi.instrument_id = i.instrument_id
inner join instrument_categories ic
on i.instrument_category_id = ic.instrument_category_id
inner join measure_types mt
on ic.measure_type_id = mt.measure_type_id

LEFT JOIN tariffs t				--тариф
ON t.service_id = bi.service_id AND t.service_place_id = b.service_place_id AND t.instrument_category_id = i.instrument_category_id
	AND t.commit_date =
		 (SELECT TOP 1 commit_date FROM tariffs
		WHERE commit_date <= b.bill_date AND service_id = bi.service_id AND service_place_id = b.service_place_id AND instrument_category_id = i.instrument_category_id
		ORDER BY commit_date DESC)

where execution_end_date between @@begin_date and @@end_date
group by branch_name, subdivision_name, measure_type_name, instrument_category_name, service_place_name, tariff
order by branch_name, subdivision_name, measure_type_name, instrument_category_name, service_place_name
==================================block=end==============================================



==================================block=start============================================
Количество поверенных приборов(ГУ)(всех).sql 
 ========================================================================================= 
set dateformat dmy
declare @@begin_date datetime, @@end_date datetime
set @@begin_date = '01.01.2012'     --начало периода отчета
set @@end_date = '31.12.2012'         --конец период отчета
select  branch_name as [Филиал], subdivision_name as [Отдел], measure_type_name as [Вид измерений], instrument_category_name as [Прибор], service_place_name as [Место поверки], sum(real_count) as [Кол-во], tariff as [Тариф], sum(tariff*real_count) as [Сумма]
from bill_service_executions bse
inner join bill_instruments bi
on bse.bill_instrument_id = bi.bill_instrument_id
inner join services se
on bi.service_id = se.service_id and verification = 1
inner join subdivisions s
on bi.subdivision_id = s.subdivision_id
inner join bills b
on bi.bill_id = b.bill_id
inner join service_places sp
on b.service_place_id = sp.service_place_id
inner join branchs br
on b.branch_id = br.branch_id
inner join instruments i
on bi.instrument_id = i.instrument_id
inner join instrument_categories ic
on i.instrument_category_id = ic.instrument_category_id
inner join measure_types mt
on ic.measure_type_id = mt.measure_type_id

LEFT JOIN tariffs t				--тариф
ON t.service_id = bi.service_id AND t.service_place_id = b.service_place_id AND t.instrument_category_id = i.instrument_category_id
	AND t.commit_date =
		 (SELECT TOP 1 commit_date FROM tariffs
		WHERE commit_date <= b.bill_date AND service_id = bi.service_id AND service_place_id = b.service_place_id AND instrument_category_id = i.instrument_category_id
		ORDER BY commit_date DESC)

where execution_end_date between @@begin_date and @@end_date and bi.service_id = '{2A656004-1B5B-42BC-997F-4F6143553AAD}'
group by branch_name, subdivision_name, measure_type_name, instrument_category_name, service_place_name, tariff
order by branch_name, subdivision_name, measure_type_name, instrument_category_name, service_place_name
==================================block=end==============================================



==================================block=start============================================
Количество поверенных приборов(по моделям)(индивидуально).sql 
 ========================================================================================= 
--Количество поверенных приборов
set dateformat dmy
declare @@begin_date datetime, @@end_date datetime, @@instrument varchar(255), @@model varchar(255)
set dateformat dmy
set @@instrument = 'нивелир'        --прибор точное название
set @@model = 'qqq'                          --модель точное название
set @@begin_date = '01.01.2005'    --начало периода
set @@end_date = '31.12.2005'        --конец периода

select branch_name as [Филиал], subdivision_name as [Отдел], client_name as [Клиент], sum(real_count) as [Кол-во поверенных], sum(real_count*tariff) as [Сумма], sum(defective_count) as [Кол-во неисправных],
ltrim(isnull(locality_name, '') + ' ' + isnull(cast(zip_code as varchar(6)), '') + ' ' +isnull(address, '')) as [Адрес]
from bill_service_executions bse
inner join workers w
on w.worker_id = bse.worker_id 
inner join branchs br
on w.branch_id = br.branch_id
inner join subdivisions su
on w.subdivision_id = su.subdivision_id
inner join bill_instruments bi
on bse.bill_instrument_id = bi.bill_instrument_id
inner join services s
on bi.service_id = s.service_id and verification = 1
inner join bills b
on bi.bill_id = b.bill_id
inner join contracts c
on b.contract_id = c.contract_id
inner join clients cl
on c.client_id = cl.client_id
inner join instruments i
on bi.instrument_id = i.instrument_id and instrument_name like '%'+@@instrument+'%' and instrument_model like '%'+@@model+'%'
inner join instrument_categories ic
on i.instrument_category_id = ic.instrument_category_id --and instrument_category_name like '%'+@@instrument+'%'
inner join vw_instruments_services_cost isc
on isc.bill_instrument_id = bi.bill_instrument_id
left join localities l
on cl.locality_id = l.locality_id

where execution_end_date between @@begin_date and @@end_date
group by branch_name, subdivision_name, client_name, locality_name, zip_code, address
==================================block=end==============================================



==================================block=start============================================
Количество приборов в графиках за указанный год.sql 
 ========================================================================================= 
select measure_type_name, sum(count_perfect) from schedule_instruments si
inner join schedule_years sy
on si.schedule_id=sy.schedule_id and schedule_year=2007
inner join instruments i
on si.instrument_id = i.instrument_id
inner join instrument_categories ic
on i.instrument_category_id = ic.instrument_category_id
inner join measure_types mt
on ic.measure_type_id = mt.measure_type_id
group by measure_type_name
==================================block=end==============================================



==================================block=start============================================
Количество принятых приборов в этом году.sql 
 ========================================================================================= 
select instrument_name + ' ' + isnull(instrument_model, '') as [Наименование СИ], sum(instrument_count) as [К-во]
from bills b
inner join bill_instruments bi
on b.bill_id = bi.bill_id
inner join instruments i
on bi.instrument_id = i.instrument_id
where year(receive_date ) = year(getdate())
group by instrument_name, instrument_model
==================================block=end==============================================



==================================block=start============================================
льгов.sql 
 ========================================================================================= 
set dateformat dmy
DECLARE @@start_date DATETIME, @@end_date DATETIME, @@branch_id UNIQUEIDENTIFIER, @@subdivision_id UNIQUEIDENTIFIER
SET @@start_date = '01.01.2011'
SET @@end_date = '31.12.2011'
SET @@branch_id = '38E75827-510B-464F-A152-EEFC25D24CF3'
--SET @@subdivision_id = :subdivision_id


select * from
(
SELECT

worker_name, service_name, ISNULL(SUM(real_count), 0) as instrument_count, ISNULL(SUM(defective_count), 0) as defective_count,
ISNULL(SUM(real_count * ( bi.tariff )), 0) AS instrument_sum

FROM bill_service_executions bse

INNER JOIN vw_instruments_services_cost bi
ON bse.bill_instrument_id = bi.bill_instrument_id 

INNER JOIN bills_calcs bc
ON bse.bill_id = bc.bill_id AND (execution_end_date BETWEEN @@start_date AND @@end_date)

INNER JOIN workers w
ON bse.worker_id = w.worker_id

inner join services s
on bi.service_id = s.service_id

inner join bills b
on bse.bill_id = b.bill_id
inner join contracts c
on b.contract_id = c.contract_id
inner join attribute a
on c.client_id = a.client_id 

WHERE w.branch_id = @@branch_id 

group by worker_name, service_name

) e
union

SELECT worker_name, 'ДОП.ТАРИФ', COUNT(*) AS tariff_count, 0, ISNULL(SUM(execution_sum), 0) AS tariff

FROM bill_addition_tariff_executions bate

INNER JOIN workers w
ON bate.worker_id = w.worker_id 

INNER JOIN bills_calcs bc
ON bate.bill_id = bc.bill_id AND (execution_end_date BETWEEN @@start_date AND @@end_date)

inner join bills b
on bate.bill_id = b.bill_id
inner join contracts c
on b.contract_id = c.contract_id
inner join attribute a
on c.client_id = a.client_id 

WHERE w.branch_id = @@branch_id

GROUP BY worker_name

order by worker_name, service_name



==================================block=end==============================================



==================================block=start============================================
Метрконтроль (кол-во экспорт).sql 
 ========================================================================================= 
set dateformat dmy

declare @@number int, @@start_date datetime, @@end_date datetime

set @@number = 118
set @@start_date = '01.01.2008'
set @@end_date = '31.01.2008'

select table_number, worker_name, sum(real_count), sum(real_count*tariff) from vw_instruments_services_cost bisc
inner join bill_service_executions bse
on bisc.bill_instrument_id = bse.bill_instrument_id
inner join bills_calcs bc
on bisc.bill_id = bc.bill_id
inner join workers w
on bse.worker_id = w.worker_id
where table_number = @@number and execution_end_date between @@start_date and @@end_date and export = 1
group by table_number, worker_name
==================================block=end==============================================



==================================block=start============================================
Метрконтроль (по закрытию).sql 
 ========================================================================================= 
set dateformat dmy
declare @@start_date datetime, @@end_date datetime

set @@start_date = '01.01.2007'    -- начало периода
set @@end_date = '31.01.2007'    -- конец периода

select bill_number, b.bill_date, branch_name, subdivision_name from bills b 
inner join bills_calcs c
on b.bill_id = c.bill_id and closed = 1
inner join csm_metrcontrol..order_head_actual o
on bill_number = bill_num and b.bill_date = o.bill_date
inner join branchs br
on b.branch_id = br.branch_id
inner join (
select bill_id, subdivision_name from bill_instruments bi
inner join subdivisions s
on bi.subdivision_id = s.subdivision_id
group by bill_id, subdivision_name
) si
on b.bill_id = si.bill_id
where isnull(order_closed, 0) = 0
and close_date between @@start_date and @@end_date
==================================block=end==============================================



==================================block=start============================================
Метрконтроль (по маркам).sql 
 ========================================================================================= 
set dateformat dmy
declare @@start_date datetime, @@end_date datetime

set @@start_date = '01.01.2007'    -- начало периода
set @@end_date = '31.01.2007'    -- конец периода

select distinct bill_num, bill_date from csm_metrcontrol..order_pos_actual p
inner join (
select order_pos_id, count(*) as device_count from csm_metrcontrol..device_calibration_rec_actual
where isnull(mark_num, 0) > 0
group by order_pos_id
) d
on p.order_pos_id = d.order_pos_id and mark_type_id = 1
inner join csm_metrcontrol..order_head_actual h
on p.order_id = h.order_id
where qty - broken_qty > device_count and integrate = 0
and bill_date between @@start_date and @@end_date
==================================block=end==============================================



==================================block=start============================================
Метрконтроль (разница в количстве).sql 
 ========================================================================================= 
select bill_number, m1.bill_date, refunded, real_count, qty from
(
select bill_number, bill_date, close_date, sum(real_count) as real_count from bills b
inner join bills_calcs bc
on b.bill_id = bc.bill_id and month(close_date) = 1 and year(close_date) = 2007
inner join bill_instruments bi
on b.bill_id = bi.bill_id
inner join bill_service_executions bse
on bi.bill_instrument_id = bse.bill_instrument_id
inner join services s
on bi.service_id = s.service_id and verification = 1
group by bill_number, bill_date, close_date
) m1
left join
(
select bill_num, bill_date, refunded, sum(device_count) as qty from csm_metrcontrol..order_head_actual h
inner join csm_metrcontrol..order_pos_actual p
on h.order_id = p.order_id
inner join csm_metrcontrol..device_calibration_rec_actual c
on p.order_pos_id = c.order_pos_id
--where year(close_date) = 2007 and moth()
group by bill_num, bill_date, refunded
) m2
on bill_number = bill_num and m1.bill_date = m2.bill_date and close_date = refunded
where real_count <> qty
==================================block=end==============================================



==================================block=start============================================
Надбавки.sql 
 ========================================================================================= 
set dateformat dmy
declare @percent decimal(6,2), @start_date datetime, @end_date datetime
set @percent = 50
set @start_date = '01.01.2009'
set @end_date = '31.12.2009'

select branch_name, subdivision_name, sum(aa.instrument_count*add_account_tariff)
from vw_services_add_accounts_cost aa
inner join bills b
on aa.bill_id = b.bill_id
inner join bill_instruments bi
on aa.bill_instrument_id = bi.bill_instrument_id 
inner join bills_calcs bc
on aa.bill_id = bc.bill_id
inner join branchs br
on b.branch_id = br.branch_id
inner join subdivisions s
on bi.subdivision_id = s.subdivision_id
where aa_percent = @percent
and close_date between @start_date and @end_date
group by branch_name, subdivision_name

==================================block=end==============================================



==================================block=start============================================
НДС только для приборов.sql 
 ========================================================================================= 
set dateformat dmy

SELECT branch_name, subdivision_name, sum(b.instrument_count * tariff * service_tax_percent / 100) as [сум. НДС]

FROM vw_bills_instruments_sum b

inner join bills_calcs bc
on b.bill_id = bc.bill_id and close_date >= '01.01.2003' and close_date < '01.02.2003'
-- даты закрытия                             ^^^^^^^^^^                    ^^^^^^^^^^

inner join bills l
on b.bill_id = l.bill_id

inner join branchs br
on l.branch_id = br.branch_id

INNER JOIN bill_instruments bi
ON b.bill_id = bi.bill_id

inner join subdivisions s
on bi.subdivision_id = s.subdivision_id

group by branch_name, subdivision_name
order by branch_name, subdivision_name

==================================block=end==============================================



==================================block=start============================================
Не выданные СФ по закрытым заявлениям.sql 
 ========================================================================================= 
select bill_number, bill_date, client_name, bill_cost, bill_sum, invoice_number, invoice_date from bills b
inner join contracts c
on b.contract_id = c.contract_id
inner join clients cl
on c.client_id = cl.client_id
inner join bills_calcs bc
on b.bill_id = bc.bill_id
inner join invoices i
on b.bill_id = i.bill_id
where month(invoice_date) = 6 and year(invoice_date) = 2010


==================================block=end==============================================



==================================block=start============================================
Номенклатура приборов в графике.sql 
 ========================================================================================= 
set dateformat dmy
declare @@contract_number varchar(10), @@contract_date datetime, @@schedule_year int
set @@contract_number = '2927' --номер договора
set @@contract_date = '14.10.2003'  --дата договора
set @@schedule_year = 2006  -- год графика

select instrument_name as [Наименование СИ], instrument_model as [Модель СИ], sum(count_perfect) as [Кол-во], sum(count_defective) as [Из них неиспр.] from schedule_instruments si
inner join schedule_years sy
on si.schedule_id = sy.schedule_id
inner join contracts c
on sy.contract_id = c.contract_id
inner join instruments i
on si.instrument_id = i.instrument_id
where contract_number = @@contract_number and contract_date = @@contract_date and schedule_year = @@schedule_year
group by instrument_name, instrument_model
==================================block=end==============================================



==================================block=start============================================
Отчет об исполнении графиковпо видам измерений.sql 
 ========================================================================================= 
declare @@_year smallint
set @@_year = 2008

select isnull(e.measure_type_name, s.measure_type_name) as [Вид измерений], s.instrument_count as [В графике], e.instrument_count as [Поверено] from
(
select measure_type_name, sum(count_perfect) as instrument_count from schedule_instruments si
inner join schedule_years sy
on si.schedule_id=sy.schedule_id and schedule_year=@@_year
inner join instruments i
on si.instrument_id = i.instrument_id
inner join instrument_categories ic
on i.instrument_category_id = ic.instrument_category_id
inner join measure_types mt
on mt.measure_type_id = ic.measure_type_id
where schedule_month > 0 and isnull(executor_id, (select top 1 client_id from attribute)) in (select client_id from attribute)
group by measure_type_name
) s
full join
(
select measure_type_name, sum(real_count) as instrument_count from bill_instruments bi
inner join (
select bill_instrument_id, sum(real_count) as real_count
from bill_service_executions
where year(execution_end_date) = @@_year and work_type_id <> '{892A96DB-678F-4E58-A2E5-73A27C3AB969}'
group by bill_instrument_id) bse
on bse.bill_instrument_id = bi.bill_instrument_id
inner join instruments i
on bi.instrument_id = i.instrument_id
inner join instrument_categories ic
on i.instrument_category_id = ic.instrument_category_id
inner join measure_types mt
on mt.measure_type_id = ic.measure_type_id
where service_id not in (select service_id from services where isnull(verification, 0) = 0)
group by measure_type_name
) e
on  s.measure_type_name = e.measure_type_name

order by isnull(e.measure_type_name, s.measure_type_name)
==================================block=end==============================================



==================================block=start============================================
Отчет по видам измерений.sql 
 ========================================================================================= 
set dateformat dmy
declare @@begin_date datetime, @@end_date datetime
set @@begin_date = '01.11.2010'     --начало периода отчета
set @@end_date = '30.11.2010'         --конец период отчета
select 
measure_type_name as [Вид измерений],
branch_name as [Филиал],
subdivision_name as [Подразделение],
sum(real_count) as [Кол-во приборов],
sum(defective_count) as [Из них неисправно],
sum(isnull(complete_count, instrument_count)) as [К-во комплектов],
sum(tariff*instrument_count) as [Сумма]
from bill_instruments bi
inner join (
select bill_instrument_id, sum(real_count) as real_count, sum(defective_count) as defective_count
from bill_service_executions
where execution_end_date between @@begin_date and @@end_date 
group by bill_instrument_id) bse
on bse.bill_instrument_id = bi.bill_instrument_id
inner join bills_calcs bc
on bi.bill_id = bc.bill_id
inner join bills b
on bi.bill_id = b.bill_id
inner join instruments i
on bi.instrument_id = i.instrument_id
inner join instrument_categories ic
on i.instrument_category_id = ic.instrument_category_id
inner join measure_types mt
on mt.measure_type_id = ic.measure_type_id
inner join subdivisions s
on s.subdivision_id = bi.subdivision_id
inner join branchs br
on b.branch_id = br.branch_id
left join tariffs t				--тариф
on t.service_id = bi.service_id and t.service_place_id = b.service_place_id and t.instrument_category_id = i.instrument_category_id
	and t.commit_date =
		 (select top 1 commit_date from tariffs
		where commit_date <= b.bill_date and service_id = bi.service_id and service_place_id = b.service_place_id and instrument_category_id = i.instrument_category_id
		order by commit_date desc)
--where close_date between @@begin_date and @@end_date
group by measure_type_name, branch_name, subdivision_name
order by branch_name, subdivision_name, measure_type_name
==================================block=end==============================================



==================================block=start============================================
Отчет по графику.sql 
 ========================================================================================= 
set dateformat dmy
declare @@contract_number varchar(9), @@contract_date datetime, @@schedule_year int

set @@contract_number = '2940'
set @@contract_date = '15.10.2003'
set @@schedule_year = 2007

select instrument_name, sum(count_perfect) as [К-во], dbo.fn_get_schedule_instrument_price(i.instrument_id, service_place_id, getdate(), si.service_id) as [Тариф], sum(count_perfect*dbo.fn_get_schedule_instrument_price(i.instrument_id, service_place_id, getdate(), si.service_id)) as [Всего],
sum(1.18*count_perfect*dbo.fn_get_schedule_instrument_price(i.instrument_id, service_place_id, getdate(), si.service_id)) as [Всего с НДС]
from schedule_years sy
inner join schedule_instruments si
on sy.schedule_id = si.schedule_id
inner join instruments i
on si.instrument_id = i.instrument_id
inner join contracts c
on sy.contract_id = c.contract_id and contract_number = @@contract_number and contract_date = @@contract_date
where schedule_year = @@schedule_year
group by instrument_name, i.instrument_id, service_place_id, si.service_id



==================================block=end==============================================



==================================block=start============================================
Отчет по сферам ГМК.sql 
 ========================================================================================= 
set dateformat dmy
declare @start_date datetime, @end_date datetime

set @start_date = '01.01.2008'
set @end_date = '31.12.2008'

select maintenance_type_name as [Сфера ГМК], sum(real_count) as [Кол-во поверенных СИ], sum(defective_count) as [из них непригодно], sum(real_count*tariff)  as [Сумма без НДС]  from bills b
inner join bills_calcs bc
on b.bill_id = bc.bill_id
inner join bill_instruments bi
on b.bill_id = bi.bill_id
inner join vw_instruments_services_cost  sc
on bi.bill_instrument_id  = sc.bill_instrument_id
inner join bill_service_executions bse
on bi.bill_instrument_id  = bse.bill_instrument_id
inner join maintenance_types mt
on bi.maintenance_type_id = mt.maintenance_type_id
where close_date between @start_date and @end_date
group by maintenance_type_name
order by maintenance_type_name
==================================block=end==============================================



==================================block=start============================================
План-график размещения заказов.sql 
 ========================================================================================= 
declare @quarter tinyint, @year smallint
set @quarter  = 1
set @year = 2012

select
status_id as [Статус размещения заказа],
method_id as [Код способа зазмещения], 
p.okdp_code as [Код структурированной номенклатуры],
order_name as [Наименование предмета государственного контракта],
unit_name as [Ед. изм.],
order_count as [Кол-во],
order_cost as [Ориентировочная стоимость, цена контракта, руб.],
publication_date as [Плановая дата публикации и размещения извещения о торгах],
performance_date as [Плановый срок исполнения котракта]
from tenders..planning_items p
left join tenders..vw_planning_item_to_rule_items r
on p.plan_item_id = r.plan_item_id
left join tenders..auctions_conditions a
on p.plan_item_id = a.plan_item_id 
where isnull(rule_group_number, 999) in
(
select isnull(rule_group_number, 999) from tenders..vw_planning_report g
where plan_quarter = @quarter and plan_year = @year and rule_group_cost > 100000
)
and plan_id in
(
select plan_id from tenders..planning_periods
where plan_quarter = @quarter and plan_year = @year
)
==================================block=end==============================================



==================================block=start============================================
По Валиной просьбе.sql 
 ========================================================================================= 
--Количество поверенных приборов
set dateformat dmy
declare @@begin_date datetime, @@end_date datetime, @@instrument varchar(255)
set dateformat dmy
set @@instrument = 'нивелир'        --прибор точное название
set @@begin_date = '01.01.2005'    --начало периода
set @@end_date = '31.12.2005'        --конец периода

select client_name as [Клиент], client_branch_name as [Подразделение клиента], instrument_name as [Прибор], sum(real_count) as [Кол-во поверенных], sum(real_count*tariff) as [Сумма], sum(defective_count) as [Кол-во неисправных],
subdivision_name as [Подразделение], ltrim(isnull(locality_name, '') + ' ' + isnull(cast(cl.zip_code as varchar(6)), '') + ' ' +isnull(cl.address, '')) as [Адрес]
from bill_service_executions bse
inner join bill_instruments bi
on bse.bill_instrument_id = bi.bill_instrument_id
inner join bills b
on bi.bill_id = b.bill_id and branch_id = '{38E75827-510B-464F-A152-EEFC25D24CF3}'
inner join subdivisions s
on bi.subdivision_id = s.subdivision_id
inner join contracts c
on b.contract_id = c.contract_id
inner join clients cl
on c.client_id = cl.client_id
inner join instruments i
on bi.instrument_id = i.instrument_id --and instrument_name like '%'+@@instrument+'%'
inner join instrument_categories ic
on i.instrument_category_id = ic.instrument_category_id --and instrument_category_name like '%'+@@instrument+'%'
inner join vw_instruments_services_cost isc
on isc.bill_instrument_id = bi.bill_instrument_id
left join localities l
on cl.locality_id = l.locality_id
left join clients_branchs cb
on b.client_branch_id = cb.client_branch_id

where execution_end_date between @@begin_date and @@end_date
group by client_name, locality_name, cl.zip_code, cl.address, client_branch_name, instrument_name, subdivision_name
==================================block=end==============================================



==================================block=start============================================
Поверка ГУ отчет.sql 
 ========================================================================================= 
declare @year smallint, @month tinyint
set @year = 2012
set @month = 1

select maintenance_type_name, sum(bi.instrument_count) as instrument_count, sum(s.instrument_count*tariff) as instrument_sum from vw_instruments_services_cost s
inner join bills_calcs bc
on s.bill_id = bc.bill_id and month(close_date) = @month and year(close_date) = @year
inner join bill_instruments bi
on bi.bill_instrument_id = s.bill_instrument_id 
left join maintenance_types m
on m.maintenance_type_id = bi.maintenance_type_id
where bi.service_id = '{2A656004-1B5B-42BC-997F-4F6143553AAD}'
group by maintenance_type_name
order by maintenance_type_name
==================================block=end==============================================



==================================block=start============================================
Поверка ГУ.sql 
 ========================================================================================= 
declare @year smallint, @month tinyint
set @year = 2012
set @month = 1

select bill_number, b.bill_date, instrument_name, instrument_model, instrument_count, instrument_count*tariff as cost, close_date from vw_instruments_services_cost s
inner join bills b
on s.bill_id = b.bill_id
inner join bills_calcs bc
on s.bill_id = bc.bill_id and month(close_date) = @month and year(close_date) = @year
inner join instruments i
on s.instrument_id = i.instrument_id
where service_id = '{2A656004-1B5B-42BC-997F-4F6143553AAD}'
order by b.bill_date, bill_number
==================================block=end==============================================



==================================block=start============================================
Проверка дублирования номеров счетов.sql 
 ========================================================================================= 
set dateformat dmy
declare @begin_date datetime, @end_date datetime
set @begin_date = '01.03.2009'   --начало периода проверки
set @end_date = '31.03.2009'     --конец периода проверки



select branch_name as [Подразделение], bill_number as [№], bill_date as [Дата] from bills b
inner join branchs br
on b.branch_id = br.branch_id
where str(bill_order_number)+cast(bill_date as char(40))+cast(b.branch_id as char(40)) in (
select str(bill_order_number)+cast(bill_date as char(40))+cast(branch_id as char(40)) from bills
where bill_date between @begin_date and @end_date
group by bill_order_number, bill_date, branch_id
having count(*) > 1
)
order by branch_name, bill_date, bill_order_number
==================================block=end==============================================



==================================block=start============================================
Реестр договоров.sql 
 ========================================================================================= 
set dateformat dmy
declare @@start_date datetime, @@end_date datetime
set @@start_date = '01.12.2006'  --начало периода
set @@end_date = '31.12.2006'   --конец периода
select contract_number as [№ договора], contract_date as [Дата договора], client_name as [Заказчик], end_date as [Дата окончания действия договора], c.notes as [Примечание]
from contracts c
inner join clients cl
on c.client_id = cl.client_id
where contract_date between @@start_date and @@end_date
order by contract_date, contract_number
==================================block=end==============================================



==================================block=start============================================
СИ не найденные в реестре.sql 
 ========================================================================================= 
select
measure_type_name as [Вид измерения], instrument_category_name as [Тарифная группа], register_number as [№ по гос.реестру], instrument_name as [СИ], instrument_model as [Тип]
from instruments i
inner join instrument_categories ic
on i.instrument_category_id = ic.instrument_category_id
inner join measure_types t
on ic.measure_type_id = t.measure_type_id
left join official_register r
on i.instrument_id = r.instrument_id
left join csm_metrcontrol..center_device_type_actual
on instrument_name = device_name and isnull(instrument_model, '') = isnull(type_str, '') and gos_no = register_number
where center_id is null
order by measure_number, instrument_category_name, instrument_name, instrument_model

==================================block=end==============================================



==================================block=start============================================
Спецификация (без нулей).sql 
 ========================================================================================= 
set dateformat dmy
declare @@contract_number varchar(15), @@contract_date datetime, @@schedule_year int, @@client_name varchar(255), @@start_month datetime, @@end_month datetime

set @@contract_number = '213'
set @@contract_date = '12.01.2009'
set @@schedule_year = 2009
set @@start_month = 1
set @@end_month = 12


select @@client_name = client_name from clients
where client_id in (select client_id from contracts where contract_number =  @@contract_number and contract_date = @@contract_date)

select instrument_name + ' ' +isnull(instrument_model, '') as [СИ], sum(count_perfect) as [К-во], dbo.fn_get_schedule_instrument_price(i.instrument_id, service_place_id, getdate(), service_id) as [Тариф], sum(count_perfect*dbo.fn_get_schedule_instrument_price(i.instrument_id, service_place_id, getdate(), service_id)) as [Всего],
sum(1.18*count_perfect*dbo.fn_get_schedule_instrument_price(i.instrument_id, service_place_id, getdate(), service_id)) as [Всего с НДС], @@contract_number as [Договор №], @@contract_date as [Договор дата], @@client_name as [Заказчик]
from schedule_years sy
inner join schedule_instruments si
on sy.schedule_id = si.schedule_id
inner join instruments i
on si.instrument_id = i.instrument_id
inner join contracts c
on sy.contract_id = c.contract_id and contract_number = @@contract_number and contract_date = @@contract_date
where schedule_year = @@schedule_year and (schedule_month between @@start_month and @@end_month) and dbo.fn_get_schedule_instrument_price(i.instrument_id, service_place_id, getdate(), service_id) > 0 and schedule_month > 0
group by instrument_name, instrument_model, i.instrument_id, service_place_id, service_id


union all

select 'Итого', sum(count_perfect) as [К-во], 0, sum(count_perfect*dbo.fn_get_schedule_instrument_price(i.instrument_id, service_place_id, getdate(), service_id)) as [Всего],
sum(1.18*count_perfect*dbo.fn_get_schedule_instrument_price(i.instrument_id, service_place_id, getdate(), service_id)) as [Всего с НДС], @@contract_number, @@contract_date, @@client_name
from schedule_years sy
inner join schedule_instruments si
on sy.schedule_id = si.schedule_id
inner join instruments i
on si.instrument_id = i.instrument_id
inner join contracts c
on sy.contract_id = c.contract_id and contract_number = @@contract_number and contract_date = @@contract_date
where schedule_year = @@schedule_year and (schedule_month between @@start_month and @@end_month) and dbo.fn_get_schedule_instrument_price(i.instrument_id, service_place_id, getdate(), service_id) > 0 and schedule_month > 0




==================================block=end==============================================



==================================block=start============================================
Спецификация.sql 
 ========================================================================================= 
set dateformat dmy
declare @@contract_number varchar(15), @@contract_date datetime, @@schedule_year int, @@client_name varchar(255), @@start_month datetime, @@end_month datetime

set @@contract_number = '121'
set @@contract_date = '16.01.2007'
set @@schedule_year = 2008
set @@start_month = 1
set @@end_month = 12

select @@client_name = client_name from clients
where client_id in (select client_id from contracts where contract_number =  @@contract_number and contract_date = @@contract_date)

select instrument_name + ' ' +isnull(instrument_model, '') as [СИ], sum(count_perfect) as [К-во], dbo.fn_get_schedule_instrument_price(i.instrument_id, service_place_id, getdate(), service_id) as [Тариф], sum(count_perfect*dbo.fn_get_schedule_instrument_price(i.instrument_id, service_place_id, getdate(), service_id)) as [Всего],
sum(1.18*count_perfect*dbo.fn_get_schedule_instrument_price(i.instrument_id, service_place_id, getdate(), service_id)) as [Всего с НДС], @@contract_number as [Договор №], @@contract_date as [Договор дата], @@client_name as [Заказчик]
from schedule_years sy
inner join schedule_instruments si
on sy.schedule_id = si.schedule_id
inner join instruments i
on si.instrument_id = i.instrument_id
inner join contracts c
on sy.contract_id = c.contract_id and contract_number = @@contract_number and contract_date = @@contract_date
where schedule_year = @@schedule_year and (schedule_month between @@start_month and @@end_month)
group by instrument_name, instrument_model, i.instrument_id, service_place_id, service_id


union all

select 'Итого', sum(count_perfect) as [К-во], 0, sum(count_perfect*dbo.fn_get_schedule_instrument_price(i.instrument_id, service_place_id, getdate(), service_id)) as [Всего],
sum(1.18*count_perfect*dbo.fn_get_schedule_instrument_price(i.instrument_id, service_place_id, getdate(), service_id)) as [Всего с НДС], @@contract_number, @@contract_date, @@client_name
from schedule_years sy
inner join schedule_instruments si
on sy.schedule_id = si.schedule_id
inner join instruments i
on si.instrument_id = i.instrument_id
inner join contracts c
on sy.contract_id = c.contract_id and contract_number = @@contract_number and contract_date = @@contract_date
where schedule_year = @@schedule_year and (schedule_month between @@start_month and @@end_month)



==================================block=end==============================================



==================================block=start============================================
Сума по доптарифам.sql 
 ========================================================================================= 
set dateformat dmy
declare @@start_date datetime, @@stop_date datetime

set @@start_date = '01.01.2006'   -- начало отчетного периода
set @@stop_date = '31.12.2006'  -- конец отчетного периода

select branch_name, subdivision_name, addition_tariff_name, sum(execution_sum) as sum
from bill_addition_tariff_executions bat
inner join addition_tariffs at
on bat.addition_tariff_id = at.addition_tariff_id
inner join workers w
on bat.worker_id = w.worker_id
inner join branchs b 
on w.branch_id = b.branch_id
inner join subdivisions s
on w.subdivision_id = s.subdivision_id
where execution_end_date between @@start_date and @@stop_date
group by branch_name, subdivision_name, addition_tariff_name
==================================block=end==============================================



==================================block=start============================================
Сумма по графикам за указанный год.sql 
 ========================================================================================= 
set dateformat dmy
declare @@year smallint, @@client_name varchar(255)
set @@year = 2005

set @@client_name = 'МУЗ ГОРОДСКАЯ БОЛЬНИЦА №1'

--select measure_type_name as [Вид измерений],  sum(real_count) as [Кол-во приборов], sum(defective_count) as [Из них неисправно]
select sum((count_perfect-count_defective)*dbo.fn_get_schedule_instrument_price(instrument_id, service_place_id, getdate(), service_id))
from schedule_years sy
inner join contracts c
on sy.contract_id = c.contract_id
inner join clients cl
on c.client_id = cl.client_id and client_name like '%'+@@client_name+'%'
inner join schedule_instruments  si
on sy.schedule_id = si.schedule_id
/*inner join instruments i
on si.instrument_id = i.instrument_id
LEFT JOIN tariffs t				--тариф
ON t.service_id = '{0667A251-886F-4ACF-9159-C78F94B935B1}' AND t.service_place_id = si.service_place_id AND t.instrument_category_id = i.instrument_category_id
	AND t.commit_date =
		 (SELECT TOP 1 t1.commit_date FROM tariffs t1
		WHERE t1.commit_date <= getdate() AND t1.service_id = '{0667A251-886F-4ACF-9159-C78F94B935B1}' AND t1.service_place_id = si.service_place_id AND t1.instrument_category_id = i.instrument_category_id
		ORDER BY t1.commit_date DESC)*/
where schedule_year = @@year
==================================block=end==============================================



==================================block=start============================================
Сумма по графикам за указанный период(по видам).sql 
 ========================================================================================= 
set dateformat dmy
declare @@client_name varchar(255), @@begin_date datetime, @@end_date datetime
set @@begin_date = '01.01.2006'
set @@end_date = '31.12.2006'

set @@client_name = 'КУРСКАЯ ПСИХИАТРИЧЕСКАЯ БОЛЬНИЦА'

--select measure_type_name as [Вид измерений],  sum(real_count) as [Кол-во приборов], sum(defective_count) as [Из них неисправно]
select measure_type_name as [Вид измерений],
sum((count_perfect-count_defective)*dbo.fn_get_schedule_instrument_price(i.instrument_id, service_place_id, getdate(), service_id)) as [Сумма]
from schedule_years sy
inner join contracts c
on sy.contract_id = c.contract_id
inner join clients cl
on c.client_id = cl.client_id and client_name like '%'+@@client_name+'%'
inner join schedule_instruments  si
on sy.schedule_id = si.schedule_id
inner join instruments i
on si.instrument_id = i.instrument_id
inner join instrument_categories ic
on ic.instrument_category_id = i.instrument_category_id
inner join measure_types m
on ic.measure_type_id = m.measure_type_id
where schedule_month > 0 and cast('01.' + str(schedule_month, 2) + '.' +str(schedule_year, 4) as datetime) between @@begin_date and @@end_date
group by measure_type_name
==================================block=end==============================================



==================================block=start============================================
Тарифы.sql 
 ========================================================================================= 
set dateformat dmy
declare @@center_id uniqueidentifier, @@client_id uniqueidentifier, @@actual_date datetime
set @@center_id = '610045D3-8827-4495-9D36-5B7775BF6540' -- id центра из service_place_id
set @@client_id = 'E01E3F2F-6E7A-4FB3-A199-D4FB57031890' --    заказчика
set @@actual_date = getdate() --дата актуальности

select measure_type_name as [Вид измерений], instrument_category_name as [Тарифная группа], service_number, instrument_name +' ' + isnull(instrument_model, '') as [Прибор], service_name as [Вид работ], t1.tariff as [Цена (центр)], t2.tariff as [Цена (заказчик)]

from
(
select t.instrument_category_id, t.service_id, t.service_place_id, tariff from tariffs t
inner join 
(
select instrument_category_id, service_id, service_place_id, max(commit_date) as  commit_date from tariffs
where service_place_id = @@center_id and commit_date < @@actual_date
group by instrument_category_id, service_id, service_place_id
) d
on t.instrument_category_id = d.instrument_category_id
and t.service_id = d.service_id
and t.service_place_id = d.service_place_id
and t.commit_date = d.commit_date
) t1

full join

(
select t.instrument_category_id, t.service_id, t.service_place_id, tariff from tariffs t
inner join 
(
select instrument_category_id, service_id, service_place_id, max(commit_date) as  commit_date from tariffs
where service_place_id = @@client_id and commit_date < @@actual_date
group by instrument_category_id, service_id, service_place_id
) d
on t.instrument_category_id = d.instrument_category_id
and t.service_id = d.service_id
and t.service_place_id = d.service_place_id
and t.commit_date = d.commit_date
) t2
on t1.instrument_category_id = t2.instrument_category_id
and t1.service_id = t2.service_id

inner join services c
on c.service_id = t1.service_id /*убрать то что правее если не только поверка*/ and verification = 1
inner join instrument_categories ic
on t1.instrument_category_id = ic.instrument_category_id
inner join measure_types m
on m.measure_type_id = ic.measure_type_id
inner join instruments i
on i.instrument_category_id = ic.instrument_category_id
inner join services_for_instruments sfi
on t1.instrument_category_id = sfi.instrument_category_id
and t1.service_id = sfi.service_id

where t1.tariff > 0 and t1.tariff > 0

order by measure_number, instrument_category_name, instrument_name, service_name

==================================block=end==============================================



==================================block=start============================================
ЧЛ.sql 
 ========================================================================================= 
set dateformat dmy
declare @begin_date datetime, @end_date datetime

set @begin_date = '01.01.2012'
set @end_date = '31.01.2012'

select sum(bill_sum) from bills b
inner join bills_calcs bc
on b.bill_id = bc.bill_id
inner join contracts c
on b.contract_id = c.contract_id
inner join clients cl
on c.client_id = cl.client_id
where closed = 1 and (close_date between @begin_date and @end_date) and client_type_id = 3
==================================block=end==============================================



==================================block=start============================================