SELECT 	ID,
	Рейс,
	MAX(Груз) 				Груз,
	MAX(Груз) OVER (PARTITION BY ID, Рейс)	Груз за рейс,
	SUM(MAX(Груз))  OVER (PARTITION BY ID)	Груз за период,
	SUM(Case 
		When состояние = 'в грузу' Then Дистанция
		 Else 0
	End) Дист в грузу,
	SUM(SUM(Case 
		When состояние = 'в грузу' Then Дистанция
		 Else 0
	End)) OVER  (PARTITION BY ID, Рейс)		Дист в грузу за рейс,
	SUM(SUM(Case 
		When состояние = 'в грузу' Then Дистанция
		 Else 0
	End)) OVER  (PARTITION BY ID)		Дист в грузу за период
From table
Group by ID, Рейс

sELECT 	ID,
	Рейс,
	MAX(Груз) 				Груз,
        MAX(Груз) OVER (PARTITION BY ID, Рейс)	Груз за рейс
From table
Group by ID, Рейс

create table #t
	( 	id			int,
		voy			varchar(10),
		start_date	datetime,
		end_date	datetime,
		cargo		real,
		distance	real,
		condition	varchar(20)  	)
	 
INSERT INTO #t(id, voy,	start_date,	end_date, cargo, distance, condition)
Values(1, 01, '20120101', '20120102', 0,	200, 'balast')

INSERT INTO #t(id, voy,	start_date,	end_date, cargo, distance, condition)
Values(1, 01, '20120102', '20120103', 94397,	571, 'loaded')

INSERT INTO #t(id, voy,	start_date,	end_date, cargo, distance, condition)
Values(1, 02, '20120103', '20120105', 0, 300, 'balast')

INSERT INTO #t(id, voy,	start_date,	end_date, cargo, distance, condition)
Values(1, 02, '20120105', '20120106', 71601, 105, 'loaded')

INSERT INTO #t(id, voy,	start_date,	end_date, cargo, distance, condition)
Values(1, 02, '20120106', '20120107', 71601, 1402, 'loaded')

INSERT INTO #t(id, voy,	start_date,	end_date, cargo, distance, condition)
Values(1, 02, '20120107', '20120108', 71601, 81, 'loaded')

SELECT 	id,
		voy,
		start_date,
		end_date,
		MAX(cargo) 			'Груз',
		Max(MAX(cargo)) OVER (PARTITION BY id, voy)	'Груз за рейс',
		SUM(MAX(cargo))  OVER (PARTITION BY id)	'Груз за период',
		SUM(Case 
				When condition = 'loaded' Then distance
				Else 0
			End) 'Дист в грузу',
		SUM(SUM(Case 
					When condition = 'loaded' Then distance
					Else 0
				End)) OVER  (PARTITION BY id, voy)		'Дист в грузу за рейс',
		SUM(SUM(Case 
					When condition = 'loaded' Then distance
					Else 0
				End)) OVER  (PARTITION BY id)		'Дист в грузу за период'
From #t
Group by id,
		voy,
		start_date,
		end_date

Select AA.id,
	   AA.voy,
	   AA.start_date,
	   AA.end_date,
	   AA.CARGO_PASSAGE,
	   AA.CARGO_VOYAGE,
	   SUM(AA.CARGO_VOYAGE / AA.REC_VOYAGE) OVER (PARTITION BY id),
	   AA.DIST_WITH_CARGO,
	   AA.DIST_PER_VOYAGE,
	   AA.DIST_PER_PERIOD
From 
(
SELECT 	id,
		voy,
		start_date,
		end_date,
		MAX(cargo) 			'CARGO_PASSAGE',
		Max(MAX(cargo)) OVER (PARTITION BY id, voy)	'CARGO_VOYAGE',
		COUNT(id) OVER (PARTITION BY id, voy) 'REC_VOYAGE',
		/*--SUM(MAX(cargo)/COUNT(*))  OVER (PARTITION BY id)	'Груз за период',		
		--MAX(SUM(cargo))  OVER (PARTITION BY id)	'Груз за период',*/
		SUM(Case 
				When condition = 'loaded' Then distance
				Else 0
			End) 'DIST_WITH_CARGO',
		SUM(SUM(Case 
					When condition = 'loaded' Then distance
					Else 0
				End)) OVER  (PARTITION BY id, voy)		'DIST_PER_VOYAGE',
		SUM(SUM(Case 
					When condition = 'loaded' Then distance
					Else 0
				End)) OVER  (PARTITION BY id)		'DIST_PER_PERIOD'
From #t
Group by id,
		voy,
		start_date,
		end_date) AA
Group by AA.id,
	   AA.voy,
	   AA.start_date,
	   AA.end_date,
	   AA.CARGO_PASSAGE,
	   AA.CARGO_VOYAGE,
	   AA.REC_VOYAGE,
	   AA.DIST_WITH_CARGO,
	   AA.DIST_PER_VOYAGE,
	   AA.DIST_PER_PERIOD