Добрый день, форумчане.
У меня беда с которой я воюю уже четвертый день с утра и до самого вечера и результата нет.
Ниже изображена таблица из которой я потом "конкатом" собираю текст в отчеты.
Таблица собирается из разных таблиц, но самое главное- это чтобы интервалы были отсортированы и каждый "на своем месте", то есть если у первой таблице отметка 20, то отметка второй таблицы должна быть тоже 20, а если там 22, то эти данные должны быть ниже, но у меня никак не получается этого достичь. По какой причине я не пойму, использовала всевозможные сортировки и связи - результата нет.
Вот на всякий случай скрипт, хотя от него толку ноль... Мне бы идею, как это сделать. Подскажите, слезно прошу, поЖЖЖалуйста
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
209.
210.
211.
212.
213.
214.
215.
216.
217.
218.
219.
220.
221.
222.
223.
224.
225.
226.
227.
228.
229.
230.
231.
232.
233.
234.
235.
236.
237.
238.
239.
240.
241.
242.
243.
244.
245.
246.
247.
248.
249.
250.
251.
252.
253.
254.
255.
256.
257.
258.
259.
260.
261.
262.
263.
264.
265.
266.
267.
268.
269.
270.
271.
272.
273.
274.
275.
276.
277.
278.
279.
280.
281.
282.
283.
284.
285.
286.
287.
288.
289.
290.
291.
292.
293.
294.
295.
296.
297.
298.
299.
300.
301.
302.
303.
304.
305.
306.
307.
308.
309.
310.
311.
312.
313.
314.
315.
316.
317.
318.
319.
320.
321.
322.
323.
324.
325.
326.
327.
328.
329.
330.
331.
332.
333.
334.
335.
336.
337.
338.
339.
340.
341.
342.
343.
344.
345.
346.
347.
348.
349.
350.
351.
352.
353.
354.
355.
356.
357.
358.
359.
360.
361.
362.
363.
364.
365.
366.
367.
368.
369.
370.
371.
372.
373.
374.
375.
376.
377.
378.
379.
380.
381.
382.
383.
384.
385.
386.
387.
388.
389.
390.
391.
392.
393.
394.
395.
396.
397.
398.
399.
400.
401.
402.
403.
404.
405.
406.
407.
408.
409.
410.
411.
412.
413.
414.
415.
416.
417.
418.
419.
420.
421.
422.
423.
424.
425.
426.
427.
428.
429.
430.
431.
432.
433.
434.
435.
436.
437.
438.
439.
440.
441.
442.
443.
444.
445.
446.
447.
448.
449.
450.
451.
452.
453.
454.
455.
456.
457.
458.
459.
460.
461.
462.
463.
464.
465.
466.
467.
468.
469.
470.
471.
472.
473.
474.
475.
476.
477.
478.
479.
480.
481.
482.
483.
484.
485.
486.
487.
488.
489.
490.
491.
492.
493.
494.
495.
496.
497.
498.
499.
500.
501.
502.
503.
504.
505.
506.
507.
508.
509.
510.
511.
512.
513.
514.
515.
516.
517.
518.
519.
520.
521.
522.
523.
524.
525.
526.
527.
528.
529.
530.
531.
532.
533.
534.
535.
536.
537.
538.
539.
540.
541.
542.
543.
544.
545.
546.
547.
548.
549.
550.
551.
552.
553.
554.
555.
556.
557.
558.
559.
560.
561.
562.
563.
564.
565.
566.
567.
568.
569.
570.
571.
572.
573.
574.
575.
576.
577.
578.
579.
580.
581.
582.
583.
584.
585.
586.
587.
588.
589.
590.
591.
592.
593.
594.
595.
596.
597.
598.
599.
600.
601.
602.
603.
604.
605.
606.
607.
608.
609.
610.
611.
612.
613.
614.
615.
616.
617.
618.
619.
620.
621.
622.
623.
624.
625.
626.
627.
628.
629.
630.
631.
632.
633.
634.
635.
636.
637.
638.
639.
640.
641.
642.
643.
644.
645.
646.
647.
648.
649.
650.
651.
652.
653.
654.
655.
656.
657.
658.
659.
660.
661.
662.
663.
664.
665.
666.
667.
668.
669.
670.
671.
672.
673.
674.
675.
676.
677.
678.
679.
680.
681.
682.
683.
684.
685.
686.
687.
688.
689.
690.
691.
692.
693.
694.
695.
696.
697.
698.
699.
700.
701.
702.
703.
704.
705.
706.
707.
708.
709.
710.
711.
712.
713.
714.
715.
716.
717.
718.
719.
720.
721.
722.
723.
724.
725.
726.
727.
728.
729.
730.
731.
732.
733.
734.
735.
736.
737.
738.
739.
740.
741.
742.
743.
744.
745.
746.
747.
748.
749.
750.
751.
752.
753.
754.
755.
756.
757.
758.
759.
760.
761.
762.
763.
764.
765.
766.
767.
768.
769.
770.
771.
772.
773.
774.
775.
776.
777.
778.
779.
780.
781.
782.
783.
784.
785.
786.
787.
788.
789.
790.
791.
792.
793.
794.
795.
796.
797.
798.
799.
800.
801.
802.
803.
804.
-- Type your SQL script here.
select case row_number() over(partition by a.holeid order by a.geolfrom) when 1 then a.holeid end as x_holeid,
case row_number() over(partition by a.holeid,a.geolfrom order by a.geolfrom) when 1 then a.GEOLFROM end as geol_from,
case row_number() over(partition by a.holeid,a.geolfrom,a.geolto order by a.geolfrom) when 1 then a.GEOLTO end as geol_to,
case row_number() over(partition by a.holeid,a.geolfrom,a.geolto,[a].Primary_Lithology_rus_Desc_D order by a.geolfrom) when 1 then [a].Primary_Lithology_rus_Desc_D end as 'Primary Lithology',
geolfrom_sec,geolto_sec,Lith3_RockName,rn_sec,geolfrom_str, geolto_str, Strucrure,rn_struc, geolfrom_alt,geolto_alt,Alteration,rn_alt,geolfrom_min,geolto_min,Mineralisation, rn_min from
( SELECT TOP 100 PERCENT * FROM (SELECT * FROM (SELECT TOP 100 PERCENT * FROM (SELECT TOP 100 PERCENT [HOLEID], [PROJECTCODE], [GEOLFROM], [GEOLTO], [PRIORITY], ( CASE WHEN [ACQDERIVEDVIEW].[Lith_RockName_Desc_D] is null THEN '' ELSE [ACQDERIVEDVIEW].[Lith_RockName_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Lith1_pct]) is null THEN '' ELSE '(' + ltrim(str([ACQDERIVEDVIEW].[Lith1_pct])) + '%' + ')'
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith_Colour_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Lith_Colour_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith1_Texture_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Lith1_Texture_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith1_Structure_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Lith1_Structure_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith2_RockName_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Lith2_RockName_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Lith2_pct]) is null THEN '' ELSE '(' + ltrim(str([ACQDERIVEDVIEW].[Lith2_pct])) + '%' + ')'
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith2_Colour_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Lith2_Colour_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith2_Texture_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Lith2_Texture_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith2_Structure_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Lith2_Structure_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith_Comments] is null THEN '' ELSE ', ( ' + [ACQDERIVEDVIEW].[Lith_Comments] + ')'
END
) AS [Primary_Lithology_Desc_D], ( CASE WHEN [ACQDERIVEDVIEW].[Lith_CompRock_rus_Desc_D] is null THEN '' ELSE [ACQDERIVEDVIEW].[Lith_CompRock_rus_Desc_D]+': '
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith_RockName_rus_Desc_D] is null THEN '' ELSE [ACQDERIVEDVIEW].[Lith_RockName] + ' - ' + [ACQDERIVEDVIEW].[Lith_RockName_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith1_pct] = 100 THEN ',' ELSE '(' + ltrim(str([ACQDERIVEDVIEW].[Lith1_pct])) + '%' + '),'
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith_Colour_Int_rus_Desc_D] is null THEN '' ELSE ' ' + [ACQDERIVEDVIEW].[Lith_Colour_Int_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith_Colour_rus_Desc_D] is null THEN '' ELSE ' ' + [ACQDERIVEDVIEW].[Lith_Colour_rus_Desc_D] + ' цвет, '
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith_Colour2_Int_rus_Desc_D] is null THEN '' ELSE [ACQDERIVEDVIEW].[Lith_Colour2_Int_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith_Colour2_rus_Desc_D] is null THEN '' ELSE [ACQDERIVEDVIEW].[Lith_Colour2_rus_Desc_D] + ' - второстепенный цвет '
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith1_Texture_rus_Desc_D] is null THEN '' ELSE ', структура ' + [ACQDERIVEDVIEW].[Lith1_Texture_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith1_Texture2_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Lith1_Texture2_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith1_Structure_rus_Desc_D] is null THEN '' ELSE ', текстура ' + [ACQDERIVEDVIEW].[Lith1_Structure_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith1_Structure2_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Lith1_Structure2_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith2_RockName_rus_Desc_D] is null THEN '' ELSE '; ' + [ACQDERIVEDVIEW].[Lith2_RockName] + ' - ' + [ACQDERIVEDVIEW].[Lith2_RockName_rus_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Lith2_pct]) is null THEN '' ELSE '(' + ltrim(str([ACQDERIVEDVIEW].[Lith2_pct])) + '%' + ')'
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith2_Colour_rus_Desc_D] is null THEN '' ELSE ', цвет ' + [ACQDERIVEDVIEW].[Lith2_Colour_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith2_Texture_rus_Desc_D] is null THEN '' ELSE ', структура ' + [ACQDERIVEDVIEW].[Lith2_Texture_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith2_Texture2_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Lith2_Texture2_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith2_Structure_rus_Desc_D] is null THEN '' ELSE ', текстура ' + [ACQDERIVEDVIEW].[Lith2_Structure_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith2_Structure2_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Lith2_Structure2_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith_Comments] is null THEN '' ELSE ', (Прим. ' + [ACQDERIVEDVIEW].[Lith_Comments] + ')'
END ) AS [Primary_Lithology_rus_Desc_D] FROM ( SELECT TOP 100 PERCENT ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith_Colour'
and LOOKUP = [DFINDF].[Lith_Colour]
and ACTIVE = 1
) AS [Lith_Colour_Desc_D], ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith_RockName'
and LOOKUP = [DFINDF].[Lith_RockName]
and ACTIVE = 1 ) AS [Lith_RockName_Desc_D], ( select LOWER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith1_Structure'
and LOOKUP = [DFINDF].[Lith1_Structure]
and ACTIVE = 1 ) AS [Lith1_Structure_Desc_D], ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith1_Texture'
and LOOKUP = [DFINDF].[Lith1_Texture]
and ACTIVE = 1 ) AS [Lith1_Texture_Desc_D], ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith2_Colour'
and LOOKUP = [DFINDF].[Lith2_Colour]
and ACTIVE = 1 ) AS [Lith2_Colour_Desc_D], ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith2_RockName'
and LOOKUP = [DFINDF].[Lith2_RockName]
and ACTIVE = 1 ) AS [Lith2_RockName_Desc_D], ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith2_Structure'
and LOOKUP = [DFINDF].[Lith2_Structure]
and ACTIVE = 1 ) AS [Lith2_Structure_Desc_D], ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith2_Texture'
and LOOKUP = [DFINDF].[Lith2_Texture]
and ACTIVE = 1 ) AS [Lith2_Texture_Desc_D], ( select UPPER(LEFT(Reportdesc,1))+UPPER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith_CompRock'
and LOOKUP = [DFINDF].[Lith_CompRock]
and ACTIVE = 1 ) AS [Lith_CompRock_rus_Desc_D], ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith_RockName'
and LOOKUP = [DFINDF].[Lith_RockName]
and ACTIVE = 1
) AS [Lith_RockName_rus_Desc_D], ( select LOWER(LEFT(Reportdesc,1)) +LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith_Colour_Int'
and LOOKUP = [DFINDF].[Lith_Colour_Int]
and ACTIVE = 1
) AS [Lith_Colour_Int_rus_Desc_D], ( select LOWER(LEFT(Reportdesc,1)) +LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith_Colour'
and LOOKUP = [DFINDF].[Lith_Colour]
and ACTIVE = 1
) AS [Lith_Colour_rus_Desc_D], ( select LOWER(LEFT(Reportdesc,1)) +LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith_Colour2_Int'
and LOOKUP = [DFINDF].[Lith_Colour2_Int]
and ACTIVE = 1
) AS [Lith_Colour2_Int_rus_Desc_D], ( select LOWER(LEFT(Reportdesc,1)) +LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith_Colour2'
and LOOKUP = [DFINDF].[Lith_Colour2]
and ACTIVE = 1
) AS [Lith_Colour2_rus_Desc_D], ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith1_Texture'
and LOOKUP = [DFINDF].[Lith1_Texture]
and ACTIVE = 1 ) AS [Lith1_Texture_rus_Desc_D], ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith1_Texture2'
and LOOKUP = [DFINDF].[Lith1_Texture2]
and ACTIVE = 1 ) AS [Lith1_Texture2_rus_Desc_D], ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith1_Structure'
and LOOKUP = [DFINDF].[Lith1_Structure]
and ACTIVE = 1 ) AS [Lith1_Structure_rus_Desc_D], ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith1_Structure2'
and LOOKUP = [DFINDF].[Lith1_Structure2]
and ACTIVE = 1 ) AS [Lith1_Structure2_rus_Desc_D], ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith2_RockName'
and LOOKUP = [DFINDF].[Lith2_RockName]
and ACTIVE = 1 ) AS [Lith2_RockName_rus_Desc_D], ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith2_Colour'
and LOOKUP = [DFINDF].[Lith2_Colour]
and ACTIVE = 1 ) AS [Lith2_Colour_rus_Desc_D], ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith2_Texture'
and LOOKUP = [DFINDF].[Lith2_Texture]
and ACTIVE = 1 ) AS [Lith2_Texture_rus_Desc_D], ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith2_Texture2'
and LOOKUP = [DFINDF].[Lith2_Texture2]
and ACTIVE = 1 ) AS [Lith2_Texture2_rus_Desc_D], ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith2_Structure'
and LOOKUP = [DFINDF].[Lith2_Structure]
and ACTIVE = 1 ) AS [Lith2_Structure_rus_Desc_D], ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith2_Structure2'
and LOOKUP = [DFINDF].[Lith2_Structure2]
and ACTIVE = 1 ) AS [Lith2_Structure2_rus_Desc_D], [HOLEID], [PROJECTCODE], [GEOLFROM], [GEOLTO], [PRIORITY], [Lith_Comments], [Lith1_pct], [Lith2_pct], [Lith_RockName], [Lith2_RockName]
FROM ( SELECT [GEOINTERVAL].[HOLEID], [GEOINTERVAL].[PROJECTCODE], [GEOINTERVAL].[GEOLFROM], [GEOINTERVAL].[GEOLTO], [GEOINTERVAL].[PRIORITY], [GEODETAILS].[Lith_Colour],
[GEOBIGCOMMENT].[Lith_Comments], [GEODETAILS].[Lith_RockName], CAST([GEODETAILS].[Lith1_pct] AS FLOAT) AS [Lith1_pct], [GEODETAILS].[Lith1_Structure], [GEODETAILS].[Lith1_Texture],
[GEODETAILS].[Lith2_Colour], CAST([GEODETAILS].[Lith2_pct] AS FLOAT) AS [Lith2_pct], [GEODETAILS].[Lith2_RockName], [GEODETAILS].[Lith2_Structure], [GEODETAILS].[Lith2_Texture],
[GEODETAILS].[Lith_CompRock], [GEODETAILS].[Lith_Colour_Int], [GEODETAILS].[Lith_Colour2_Int], [GEODETAILS].[Lith_Colour2], [GEODETAILS].[Lith1_Texture2], [GEODETAILS].[Lith1_Structure2],
[GEODETAILS].[Lith2_Texture2], [GEODETAILS].[Lith2_Structure2] FROM [GEOINTERVAL] INNER JOIN (SELECT * FROM [HOLELOCATION]) AS [CollarWSF] ON [CollarWSF].[HOLEID] = [GEOINTERVAL].[HOLEID]
AND [CollarWSF].[PROJECTCODE] = [GEOINTERVAL].[PROJECTCODE] AND [CollarWSF].[HOLETYPE] = 'DRILLHOLE' LEFT JOIN (SELECT [GEODETAILS].[HOLEID], [GEODETAILS].[PROJECTCODE], [GEODETAILS].[GEOLFROM],
[GEODETAILS].[GEOLTO], [GEODETAILS].[PRIORITY], min(CASE when [GEODETAILS].[NAME] = 'Lith_Colour' then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith_Colour], min(CASE when [GEODETAILS].[NAME] = 'Lith_RockName'
then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith_RockName], min(CASE when [GEODETAILS].[NAME] = 'Lith1_pct' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Lith1_pct], min(CASE when [GEODETAILS].[NAME] = 'Lith1_Structure'
then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith1_Structure], min(CASE when [GEODETAILS].[NAME] = 'Lith1_Texture' then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith1_Texture], min(CASE when [GEODETAILS].[NAME] = 'Lith2_Colour' then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith2_Colour],
min(CASE when [GEODETAILS].[NAME] = 'Lith2_pct' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Lith2_pct], min(CASE when [GEODETAILS].[NAME] = 'Lith2_RockName'
then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith2_RockName], min(CASE when [GEODETAILS].[NAME] = 'Lith2_Structure' then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith2_Structure], min(CASE when [GEODETAILS].[NAME] = 'Lith2_Texture' then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith2_Texture],
min(CASE when [GEODETAILS].[NAME] = 'Lith_CompRock' then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith_CompRock], min(CASE when [GEODETAILS].[NAME] = 'Lith_Colour_Int' then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith_Colour_Int], min(CASE when [GEODETAILS].[NAME] = 'Lith_Colour2_Int'
then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith_Colour2_Int], min(CASE when [GEODETAILS].[NAME] = 'Lith_Colour2' then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith_Colour2], min(CASE when [GEODETAILS].[NAME] = 'Lith1_Texture2' then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith1_Texture2],
min(CASE when [GEODETAILS].[NAME] = 'Lith1_Structure2' then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith1_Structure2], min(CASE when [GEODETAILS].[NAME] = 'Lith2_Texture2' then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith2_Texture2], min(CASE when [GEODETAILS].[NAME] = 'Lith2_Structure2'
then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith2_Structure2] FROM [GEODETAILS] GROUP BY [GEODETAILS].[PROJECTCODE], [GEODETAILS].[PRIORITY], [GEODETAILS].[GEOLTO], [GEODETAILS].[GEOLFROM], [GEODETAILS].[HOLEID])[GEODETAILS] ON [GEOINTERVAL].[PROJECTCODE] = [GEODETAILS].[PROJECTCODE]
AND [GEOINTERVAL].[PRIORITY] = [GEODETAILS].[PRIORITY] AND [GEOINTERVAL].[GEOLTO] = [GEODETAILS].[GEOLTO] AND [GEOINTERVAL].[GEOLFROM] = [GEODETAILS].[GEOLFROM] AND [GEOINTERVAL].[HOLEID] = [GEODETAILS].[HOLEID] LEFT JOIN (SELECT [GEOBIGCOMMENT].[HOLEID], [GEOBIGCOMMENT].[PROJECTCODE],
[GEOBIGCOMMENT].[GEOLFROM], [GEOBIGCOMMENT].[GEOLTO], [GEOBIGCOMMENT].[PRIORITY],
min(CASE when [GEOBIGCOMMENT].[NAME] = 'Lith_Comments'
then [GEOBIGCOMMENT].[VALUE] ELSE NULL END) as [Lith_Comments] FROM [GEOBIGCOMMENT] WHERE [GEOBIGCOMMENT].[NAME] IN ('Lith_Comments')
GROUP BY [GEOBIGCOMMENT].[PROJECTCODE], [GEOBIGCOMMENT].[PRIORITY], [GEOBIGCOMMENT].[GEOLTO], [GEOBIGCOMMENT].[GEOLFROM], [GEOBIGCOMMENT].[HOLEID])[GEOBIGCOMMENT]
ON [GEOINTERVAL].[PROJECTCODE] = [GEOBIGCOMMENT].[PROJECTCODE] AND [GEOINTERVAL].[PRIORITY] = [GEOBIGCOMMENT].[PRIORITY] AND [GEOINTERVAL].[GEOLTO] = [GEOBIGCOMMENT].[GEOLTO]
AND [GEOINTERVAL].[GEOLFROM] = [GEOBIGCOMMENT].[GEOLFROM] AND [GEOINTERVAL].[HOLEID] = [GEOBIGCOMMENT].[HOLEID] WHERE ([Lith_Colour] IS NOT NULL OR [Lith_Comments] IS NOT NULL
OR [Lith_RockName] IS NOT NULL OR [Lith1_pct] IS NOT NULL OR [Lith1_Structure] IS NOT NULL OR [Lith1_Texture] IS NOT NULL OR [Lith2_Colour] IS NOT NULL OR [Lith2_pct] IS NOT NULL
OR [Lith2_RockName] IS NOT NULL OR [Lith2_Structure] IS NOT NULL OR [Lith2_Texture] IS NOT NULL OR [Lith_CompRock] IS NOT NULL OR [Lith_Colour_Int] IS NOT NULL OR [Lith_Colour2_Int] IS NOT NULL
OR [Lith_Colour2] IS NOT NULL OR [Lith1_Texture2] IS NOT NULL OR [Lith1_Structure2] IS NOT NULL OR [Lith2_Texture2] IS NOT NULL OR [Lith2_Structure2] IS NOT NULL) ) [DFINDF]) [ACQDERIVEDVIEW]) [ACQTMP] ) AS [TMPVIEW915]) [TMPSQLSHEETVIEW]) a
outer apply (Select * From (Select geolfrom as geolfrom_sec, geolto as geolto_sec, b.Secondary_Lithology_rus_Desc_D as 'Lith3_RockName',
row_number() over (order by geolfrom) as rn_sec
From
(SELECT TOP 100 PERCENT * FROM (SELECT * FROM (SELECT TOP 100 PERCENT * FROM (SELECT TOP 100 PERCENT [HOLEID], [PROJECTCODE], [GEOLFROM], [GEOLTO], [PRIORITY], ( CASE WHEN [ACQDERIVEDVIEW].[Lith_BeddingForm_Desc_D] is null THEN '' ELSE [ACQDERIVEDVIEW].[Lith_BeddingForm_Desc_D] + ', '
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith3_RockName_Desc_D] is null THEN '' ELSE [ACQDERIVEDVIEW].[Lith3_RockName_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith3_Colour_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Lith3_Colour_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith3_Texture_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Lith3_Texture_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith3_Structure_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Lith3_Structure_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Str2_Top_Alpha]) is null THEN '' ELSE ', ' + str([ACQDERIVEDVIEW].[Str2_Top_Alpha])
END +
CASE WHEN str([ACQDERIVEDVIEW].[Str2_Bot_Alpha]) is null THEN '' ELSE ', ' + str([ACQDERIVEDVIEW].[Str2_Bot_Alpha])
END ) AS [Secondary_Lithology_Desc_D], ( CASE WHEN [ACQDERIVEDVIEW].[Lith_BeddingForm_rus_Desc_D] is null THEN '' ELSE [ACQDERIVEDVIEW].[Lith_BeddingForm_rus_Desc_D] + ', '
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith3_RockName_rus_Desc_D] is null THEN '' ELSE [ACQDERIVEDVIEW].[Lith3_RockName] + ' - ' + [ACQDERIVEDVIEW].[Lith3_RockName_rus_Desc_D] + ', '
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith3_Colour_Int_rus_Desc_D] is null THEN '' ELSE [ACQDERIVEDVIEW].[Lith3_Colour_Int_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith3_Colour_rus_Desc_D] is null THEN '' ELSE [ACQDERIVEDVIEW].[Lith3_Colour_rus_Desc_D] + ' цвет'
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith3_Texture_Desc_D] is null THEN '' ELSE ', структура ' + [ACQDERIVEDVIEW].[Lith3_Texture_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith3_Structure_rus_Desc_D] is null THEN '' ELSE ', текстура ' + [ACQDERIVEDVIEW].[Lith3_Structure_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Str2_Top_Type] is null THEN '' ELSE '. Верхний контакт: ' + [ACQDERIVEDVIEW].[Str2_Top_Type_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Str2_Top_Appear] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Str2_Top_Appear_rus_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Str2_Top_Alpha]) is null THEN '' ELSE ', ' + 'уг.ок ' + ltrim(str([ACQDERIVEDVIEW].[Str2_Top_Alpha]))
END +
CASE WHEN str([ACQDERIVEDVIEW].[Str2_Top_Beta]) is null THEN '' ELSE ', ' + 'уг.beta' + ltrim(str([ACQDERIVEDVIEW].[Str2_Top_Beta]))
END +
CASE WHEN [ACQDERIVEDVIEW].[Str2_Bot_Type] is null THEN '' ELSE '. Нижний контакт: ' + [ACQDERIVEDVIEW].[Str2_Bot_Type_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Str2_Bot_Appear] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Str2_Bot_Appear_rus_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Str2_Bot_Alpha]) is null THEN '' ELSE ', ' + 'уг.ок ' + ltrim(str([ACQDERIVEDVIEW].[Str2_Bot_Alpha]))
END+
CASE WHEN str([ACQDERIVEDVIEW].[Str2_Bot_Beta]) is null THEN '' ELSE ', ' + 'уг beta ' + ltrim(str([ACQDERIVEDVIEW].[Str2_Bot_Beta]))
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith3_Comments] is null THEN '' ELSE ', (' + [ACQDERIVEDVIEW].[Lith3_Comments] + ')'
END
) AS [Secondary_Lithology_rus_Desc_D] FROM ( SELECT TOP 100 PERCENT ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith_BeddingForm'
and LOOKUP = [DFINDF].[Lith_BeddingForm]
and ACTIVE = 1 ) AS [Lith_BeddingForm_Desc_D], ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith3_Colour'
and LOOKUP = [DFINDF].[Lith3_Colour]
and ACTIVE = 1 ) AS [Lith3_Colour_Desc_D], ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith3_RockName'
and LOOKUP = [DFINDF].[Lith3_RockName]
and ACTIVE = 1 ) AS [Lith3_RockName_Desc_D], ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith3_Structure'
and LOOKUP = [DFINDF].[Lith3_Structure]
and ACTIVE = 1 ) AS [Lith3_Structure_Desc_D], ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith3_Texture'
and LOOKUP = [DFINDF].[Lith3_Texture]
and ACTIVE = 1 ) AS [Lith3_Texture_Desc_D], ( select UPPER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith_BeddingForm'
and LOOKUP = [DFINDF].[Lith_BeddingForm]
and ACTIVE = 1 ) AS [Lith_BeddingForm_rus_Desc_D], ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith3_RockName'
and LOOKUP = [DFINDF].[Lith3_RockName]
and ACTIVE = 1 ) AS [Lith3_RockName_rus_Desc_D], ( select LOWER(LEFT(Reportdesc,1)) +LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith3_Colour_Int'
and LOOKUP = [DFINDF].[Lith3_Colour_Int]
and ACTIVE = 1
) AS [Lith3_Colour_Int_rus_Desc_D], ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith3_Colour'
and LOOKUP = [DFINDF].[Lith3_Colour]
and ACTIVE = 1 ) AS [Lith3_Colour_rus_Desc_D], ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith3_Texture'
and LOOKUP = [DFINDF].[Lith3_Texture]
and ACTIVE = 1 ) AS [Lith3_Texture_rus_Desc_D], ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith3_Structure'
and LOOKUP = [DFINDF].[Lith3_Structure]
and ACTIVE = 1 ) AS [Lith3_Structure_rus_Desc_D], ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Str2_Top_Type'
and LOOKUP = [DFINDF].[Str2_Top_Type]
and ACTIVE = 1 ) AS [Str2_Top_Type_rus_Desc_D], ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Str2_Top_Appear'
and LOOKUP = [DFINDF].[Str2_Top_Appear]
and ACTIVE = 1 ) AS [Str2_Top_Appear_rus_Desc_D], ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Str2_Bot_Type'
and LOOKUP = [DFINDF].[Str2_Bot_Type]
and ACTIVE = 1 ) AS [Str2_Bot_Type_rus_Desc_D], ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Str2_Bot_Appear'
and LOOKUP = [DFINDF].[Str2_Bot_Appear]
and ACTIVE = 1 ) AS [Str2_Bot_Appear_rus_Desc_D], [HOLEID], [PROJECTCODE], [GEOLFROM], [GEOLTO], [PRIORITY], [Str2_Bot_Alpha], [Str2_Top_Alpha], [Str2_Top_Type], [Str2_Bot_Type], [Str2_Top_Beta], [Lith3_Comments], [Str2_Top_Appear], [Lith3_RockName], [Str2_Bot_Beta], [Str2_Bot_Appear]
FROM ( SELECT [GEOINTERVAL].[HOLEID], [GEOINTERVAL].[PROJECTCODE], [GEOINTERVAL].[GEOLFROM], [GEOINTERVAL].[GEOLTO], [GEOINTERVAL].[PRIORITY], [GEODETAILS].[Lith_BeddingForm], [GEODETAILS].[Lith3_Colour], [GEODETAILS].[Lith3_RockName], [GEODETAILS].[Lith3_Structure], [GEODETAILS].[Lith3_Texture],
CAST([GEODETAILS].[Str2_Bot_Alpha] AS FLOAT) AS [Str2_Bot_Alpha], CAST([GEODETAILS].[Str2_Top_Alpha] AS FLOAT) AS [Str2_Top_Alpha], [GEODETAILS].[Lith3_Colour_Int], [GEODETAILS].[Str2_Top_Type], [GEODETAILS].[Str2_Top_Appear], CAST([GEODETAILS].[Str2_Top_Beta] AS FLOAT) AS [Str2_Top_Beta],
[GEODETAILS].[Str2_Bot_Type], [GEODETAILS].[Str2_Bot_Appear], CAST([GEODETAILS].[Str2_Bot_Beta] AS FLOAT) AS [Str2_Bot_Beta], [GEOBIGCOMMENT].[Lith3_Comments] FROM [GEOINTERVAL] INNER JOIN (SELECT * FROM [HOLELOCATION]) AS [CollarWSF] ON [CollarWSF].[HOLEID] = [GEOINTERVAL].[HOLEID]
AND [CollarWSF].[PROJECTCODE] = [GEOINTERVAL].[PROJECTCODE] AND [CollarWSF].[HOLETYPE] = 'DRILLHOLE' LEFT JOIN (SELECT [GEODETAILS].[HOLEID], [GEODETAILS].[PROJECTCODE], [GEODETAILS].[GEOLFROM], [GEODETAILS].[GEOLTO], [GEODETAILS].[PRIORITY], min(CASE when [GEODETAILS].[NAME] = 'Lith_BeddingForm'
then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith_BeddingForm], min(CASE when [GEODETAILS].[NAME] = 'Lith3_Colour' then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith3_Colour], min(CASE when [GEODETAILS].[NAME] = 'Lith3_RockName' then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith3_RockName],
min(CASE when [GEODETAILS].[NAME] = 'Lith3_Structure' then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith3_Structure], min(CASE when [GEODETAILS].[NAME] = 'Lith3_Texture' then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith3_Texture], min(CASE when [GEODETAILS].[NAME] = 'Str2_Bot_Alpha' then
CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Str2_Bot_Alpha], min(CASE when [GEODETAILS].[NAME] = 'Str2_Top_Alpha' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Str2_Top_Alpha], min(CASE when [GEODETAILS].[NAME] = 'Lith3_Colour_Int' then [GEODETAILS].[VALUE] ELSE NULL END)
as [Lith3_Colour_Int], min(CASE when [GEODETAILS].[NAME] = 'Str2_Top_Type' then [GEODETAILS].[VALUE] ELSE NULL END) as [Str2_Top_Type], min(CASE when [GEODETAILS].[NAME] = 'Str2_Top_Appear' then [GEODETAILS].[VALUE] ELSE NULL END) as [Str2_Top_Appear], min(CASE when [GEODETAILS].[NAME] = 'Str2_Top_Beta'
then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Str2_Top_Beta], min(CASE when [GEODETAILS].[NAME] = 'Str2_Bot_Type' then [GEODETAILS].[VALUE] ELSE NULL END) as [Str2_Bot_Type], min(CASE when [GEODETAILS].[NAME] = 'Str2_Bot_Appear' then [GEODETAILS].[VALUE] ELSE NULL END) as [Str2_Bot_Appear],
min(CASE when [GEODETAILS].[NAME] = 'Str2_Bot_Beta' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Str2_Bot_Beta] FROM [GEODETAILS] GROUP BY [GEODETAILS].[PROJECTCODE], [GEODETAILS].[PRIORITY], [GEODETAILS].[GEOLTO], [GEODETAILS].[GEOLFROM], [GEODETAILS].[HOLEID])[GEODETAILS]
ON [GEOINTERVAL].[PROJECTCODE] = [GEODETAILS].[PROJECTCODE] AND [GEOINTERVAL].[PRIORITY] = [GEODETAILS].[PRIORITY] AND [GEOINTERVAL].[GEOLTO] = [GEODETAILS].[GEOLTO] AND [GEOINTERVAL].[GEOLFROM] = [GEODETAILS].[GEOLFROM] AND [GEOINTERVAL].[HOLEID] = [GEODETAILS].[HOLEID] LEFT JOIN
(SELECT [GEOBIGCOMMENT].[HOLEID], [GEOBIGCOMMENT].[PROJECTCODE], [GEOBIGCOMMENT].[GEOLFROM], [GEOBIGCOMMENT].[GEOLTO], [GEOBIGCOMMENT].[PRIORITY], min(CASE when [GEOBIGCOMMENT].[NAME] = 'Lith3_Comments' then [GEOBIGCOMMENT].[VALUE] ELSE NULL END) as [Lith3_Comments] FROM [GEOBIGCOMMENT]
WHERE [GEOBIGCOMMENT].[NAME] IN ('Lith3_Comments') GROUP BY [GEOBIGCOMMENT].[PROJECTCODE], [GEOBIGCOMMENT].[PRIORITY], [GEOBIGCOMMENT].[GEOLTO], [GEOBIGCOMMENT].[GEOLFROM], [GEOBIGCOMMENT].[HOLEID])[GEOBIGCOMMENT] ON [GEOINTERVAL].[PROJECTCODE] = [GEOBIGCOMMENT].[PROJECTCODE] AND
[GEOINTERVAL].[PRIORITY] = [GEOBIGCOMMENT].[PRIORITY] AND [GEOINTERVAL].[GEOLTO] = [GEOBIGCOMMENT].[GEOLTO] AND [GEOINTERVAL].[GEOLFROM] = [GEOBIGCOMMENT].[GEOLFROM] AND [GEOINTERVAL].[HOLEID] = [GEOBIGCOMMENT].[HOLEID] WHERE ([Lith_BeddingForm] IS NOT NULL OR [Lith3_Colour] IS NOT NULL
OR [Lith3_RockName] IS NOT NULL OR [Lith3_Structure] IS NOT NULL OR [Lith3_Texture] IS NOT NULL OR [Str2_Bot_Alpha] IS NOT NULL OR [Str2_Top_Alpha] IS NOT NULL OR [Lith3_Colour_Int] IS NOT NULL OR [Str2_Top_Type] IS NOT NULL OR [Str2_Top_Appear] IS NOT NULL OR [Str2_Top_Beta] IS NOT NULL
OR [Str2_Bot_Type] IS NOT NULL OR [Str2_Bot_Appear] IS NOT NULL OR [Str2_Bot_Beta] IS NOT NULL OR [Lith3_Comments] IS NOT NULL) ) [DFINDF]) [ACQDERIVEDVIEW]) [ACQTMP] ) AS [TMPVIEW263]) [TMPSQLSHEETVIEW]
) b
Where a.holeid = b.holeid
and a.geolto>b.geolfrom and a.geolfrom<b.geolto) Sec
full join
(Select geolfrom as geolfrom_alt, geolto as geolto_alt, c.Alteration_Zone_rus_Desc_D as 'Alteration',
row_number() over (order by geolfrom) as rn_alt
From
(SELECT TOP 100 PERCENT * FROM (SELECT * FROM (SELECT TOP 100 PERCENT * FROM (SELECT TOP 100 PERCENT [HOLEID], [PROJECTCODE], [GEOLFROM], [GEOLTO], [PRIORITY], ( CASE WHEN [ACQDERIVEDVIEW].[Alteration_Desc_D] is null THEN '' ELSE [ACQDERIVEDVIEW].[Alteration_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt_Intensity_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Alt_Intensity_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt_Occurence_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Alt_Occurence_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt1_Min_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Alt1_Min_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt1_Morphology_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Alt1_Morphology_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt2_Min_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Alt2_Min_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt2_Morphology_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Alt2_Morphology_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt3_Min_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Alt3_Min_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt3_Morphology_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Alt3_Morphology_Desc_D]
END ) AS [Alteration_Zone_Desc_D], ( CASE WHEN [ACQDERIVEDVIEW].[Alteration_rus_Desc_D] is null THEN '' ELSE [ACQDERIVEDVIEW].[Alteration_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt_Occurence_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Alt_Occurence_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt_Intensity_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Alt_Intensity_rus_Desc_D] + ' интенсивность'
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt1_Min_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Alt1_Min_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt1_Morphology_rus_Desc_D] is null THEN '' ELSE ' - ' + [ACQDERIVEDVIEW].[Alt1_Morphology_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt2_Min_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Alt2_Min_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt2_Morphology_rus_Desc_D] is null THEN '' ELSE ' - ' + [ACQDERIVEDVIEW].[Alt2_Morphology_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt3_Min_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Alt3_Min_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt3_Morphology_rus_Desc_D] is null THEN '' ELSE ' - ' + [ACQDERIVEDVIEW].[Alt3_Morphology_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt_Weathering_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Alt_Weathering_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt_Comments] is null THEN '' ELSE ', (' + [ACQDERIVEDVIEW].[Alt_Comments] + ')'
END ) AS [Alteration_Zone_rus_Desc_D] FROM ( SELECT TOP 100 PERCENT ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt_Intensity'
and LOOKUP = [DFINDF].[Alt_Intensity]
and ACTIVE = 1 ) AS [Alt_Intensity_Desc_D], ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt_Occurence'
and LOOKUP = [DFINDF].[Alt_Occurence]
and ACTIVE = 1 ) AS [Alt_Occurence_Desc_D], ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt1_Min'
and LOOKUP = [DFINDF].[Alt1_Min]
and ACTIVE = 1 ) AS [Alt1_Min_Desc_D], ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt1_Morphology'
and LOOKUP = [DFINDF].[Alt1_Morphology]
and ACTIVE = 1 ) AS [Alt1_Morphology_Desc_D], ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt2_Min'
and LOOKUP = [DFINDF].[Alt2_Min]
and ACTIVE = 1 ) AS [Alt2_Min_Desc_D], ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt2_Morphology'
and LOOKUP = [DFINDF].[Alt2_Morphology]
and ACTIVE = 1 ) AS [Alt2_Morphology_Desc_D], ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt3_Min'
and LOOKUP = [DFINDF].[Alt3_Min]
and ACTIVE = 1 ) AS [Alt3_Min_Desc_D], ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt3_Morphology'
and LOOKUP = [DFINDF].[Alt3_Morphology]
and ACTIVE = 1 ) AS [Alt3_Morphology_Desc_D], ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alteration'
and LOOKUP = [DFINDF].[Alteration]
and ACTIVE = 1 ) AS [Alteration_Desc_D], ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt_Intensity'
and LOOKUP = [DFINDF].[Alt_Intensity]
and ACTIVE = 1 ) AS [Alt_Intensity_rus_Desc_D], ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt_Occurence'
and LOOKUP = [DFINDF].[Alt_Occurence]
and ACTIVE = 1 ) AS [Alt_Occurence_rus_Desc_D], ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt_Weathering'
and LOOKUP = [DFINDF].[Alt_Weathering]
and ACTIVE = 1 ) AS [Alt_Weathering_rus_Desc_D], ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt1_Min'
and LOOKUP = [DFINDF].[Alt1_Min]
and ACTIVE = 1 ) AS [Alt1_Min_rus_Desc_D], ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt1_Morphology'
and LOOKUP = [DFINDF].[Alt1_Morphology]
and ACTIVE = 1 ) AS [Alt1_Morphology_rus_Desc_D], ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt2_Min'
and LOOKUP = [DFINDF].[Alt2_Min]
and ACTIVE = 1 ) AS [Alt2_Min_rus_Desc_D], ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt2_Morphology'
and LOOKUP = [DFINDF].[Alt2_Morphology]
and ACTIVE = 1 ) AS [Alt2_Morphology_rus_Desc_D], ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt3_Min'
and LOOKUP = [DFINDF].[Alt3_Min]
and ACTIVE = 1 ) AS [Alt3_Min_rus_Desc_D], ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt3_Morphology'
and LOOKUP = [DFINDF].[Alt3_Morphology]
and ACTIVE = 1 ) AS [Alt3_Morphology_rus_Desc_D], ( select UPPER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alteration'
and LOOKUP = [DFINDF].[Alteration]
and ACTIVE = 1 ) AS [Alteration_rus_Desc_D], [HOLEID], [PROJECTCODE], [GEOLFROM], [GEOLTO], [PRIORITY], [Alt_Comments] FROM ( SELECT [GEOINTERVAL].[HOLEID], [GEOINTERVAL].[PROJECTCODE], [GEOINTERVAL].[GEOLFROM], [GEOINTERVAL].[GEOLTO], [GEOINTERVAL].[PRIORITY], [GEODETAILS].[Alt_Intensity],
[GEODETAILS].[Alt_Occurence], [GEODETAILS].[Alt1_Min], [GEODETAILS].[Alt1_Morphology], [GEODETAILS].[Alt2_Min], [GEODETAILS].[Alt2_Morphology], [GEODETAILS].[Alt3_Min], [GEODETAILS].[Alt3_Morphology], [GEODETAILS].[Alteration], [GEOBIGCOMMENT].[Alt_Comments], [GEODETAILS].[Alt_Weathering]
FROM [GEOINTERVAL] INNER JOIN (SELECT * FROM [HOLELOCATION]) AS [CollarWSF] ON [CollarWSF].[HOLEID] = [GEOINTERVAL].[HOLEID] AND [CollarWSF].[PROJECTCODE] = [GEOINTERVAL].[PROJECTCODE] AND [CollarWSF].[HOLETYPE] = 'DRILLHOLE' LEFT JOIN (SELECT [GEODETAILS].[HOLEID], [GEODETAILS].[PROJECTCODE],
[GEODETAILS].[GEOLFROM], [GEODETAILS].[GEOLTO], [GEODETAILS].[PRIORITY], min(CASE when [GEODETAILS].[NAME] = 'Alt_Intensity' then [GEODETAILS].[VALUE] ELSE NULL END) as [Alt_Intensity], min(CASE when [GEODETAILS].[NAME] = 'Alt_Occurence' then [GEODETAILS].[VALUE] ELSE NULL END) as [Alt_Occurence],
min(CASE when [GEODETAILS].[NAME] = 'Alt1_Min' then [GEODETAILS].[VALUE] ELSE NULL END) as [Alt1_Min], min(CASE when [GEODETAILS].[NAME] = 'Alt1_Morphology' then [GEODETAILS].[VALUE] ELSE NULL END) as [Alt1_Morphology], min(CASE when [GEODETAILS].[NAME] = 'Alt2_Min' then [GEODETAILS].[VALUE] ELSE NULL END)
as [Alt2_Min], min(CASE when [GEODETAILS].[NAME] = 'Alt2_Morphology' then [GEODETAILS].[VALUE] ELSE NULL END) as [Alt2_Morphology], min(CASE when [GEODETAILS].[NAME] = 'Alt3_Min' then [GEODETAILS].[VALUE] ELSE NULL END) as [Alt3_Min], min(CASE when [GEODETAILS].[NAME] = 'Alt3_Morphology'
then [GEODETAILS].[VALUE] ELSE NULL END) as [Alt3_Morphology], min(CASE when [GEODETAILS].[NAME] = 'Alteration' then [GEODETAILS].[VALUE] ELSE NULL END) as [Alteration], min(CASE when [GEODETAILS].[NAME] = 'Alt_Weathering' then [GEODETAILS].[VALUE] ELSE NULL END) as [Alt_Weathering] FROM [GEODETAILS]
WHERE [GEODETAILS].[NAME] IN ('Alt_Intensity', 'Alt_Occurence', 'Alt1_Min', 'Alt1_Morphology', 'Alt2_Min', 'Alt2_Morphology', 'Alt3_Min', 'Alt3_Morphology', 'Alteration', 'Alt_Weathering') GROUP BY [GEODETAILS].[PROJECTCODE], [GEODETAILS].[PRIORITY], [GEODETAILS].[GEOLTO], [GEODETAILS].[GEOLFROM],
[GEODETAILS].[HOLEID])[GEODETAILS] ON [GEOINTERVAL].[PROJECTCODE] = [GEODETAILS].[PROJECTCODE] AND [GEOINTERVAL].[PRIORITY] = [GEODETAILS].[PRIORITY] AND [GEOINTERVAL].[GEOLTO] = [GEODETAILS].[GEOLTO] AND [GEOINTERVAL].[GEOLFROM] = [GEODETAILS].[GEOLFROM] AND [GEOINTERVAL].[HOLEID] = [GEODETAILS].[HOLEID]
LEFT JOIN (SELECT [GEOBIGCOMMENT].[HOLEID], [GEOBIGCOMMENT].[PROJECTCODE], [GEOBIGCOMMENT].[GEOLFROM], [GEOBIGCOMMENT].[GEOLTO], [GEOBIGCOMMENT].[PRIORITY], min(CASE when [GEOBIGCOMMENT].[NAME] = 'Alt_Comments' then [GEOBIGCOMMENT].[VALUE] ELSE NULL END) as [Alt_Comments] FROM [GEOBIGCOMMENT]
WHERE [GEOBIGCOMMENT].[NAME] IN ('Alt_Comments') GROUP BY [GEOBIGCOMMENT].[PROJECTCODE], [GEOBIGCOMMENT].[PRIORITY], [GEOBIGCOMMENT].[GEOLTO], [GEOBIGCOMMENT].[GEOLFROM], [GEOBIGCOMMENT].[HOLEID])[GEOBIGCOMMENT] ON [GEOINTERVAL].[PROJECTCODE] = [GEOBIGCOMMENT].[PROJECTCODE]
AND [GEOINTERVAL].[PRIORITY] = [GEOBIGCOMMENT].[PRIORITY] AND [GEOINTERVAL].[GEOLTO] = [GEOBIGCOMMENT].[GEOLTO] AND [GEOINTERVAL].[GEOLFROM] = [GEOBIGCOMMENT].[GEOLFROM] AND [GEOINTERVAL].[HOLEID] = [GEOBIGCOMMENT].[HOLEID] WHERE ([Alt_Intensity] IS NOT NULL OR [Alt_Occurence] IS NOT NULL
OR [Alt1_Min] IS NOT NULL OR [Alt1_Morphology] IS NOT NULL OR [Alt2_Min] IS NOT NULL OR [Alt2_Morphology] IS NOT NULL OR [Alt3_Min] IS NOT NULL OR [Alt3_Morphology] IS NOT NULL OR [Alteration] IS NOT NULL OR [Alt_Comments] IS NOT NULL OR [Alt_Weathering] IS NOT NULL) ) [DFINDF]) [ACQDERIVEDVIEW]) [ACQTMP] )
AS [TMPVIEW271]) [TMPSQLSHEETVIEW] ) c
Where a.holeid = c.holeid
and a.geolto>c.geolfrom and a.geolfrom<c.geolto and a.GEOLFROM<=c.GEOLFROM) alt
on rn_sec = rn_alt
full join
(Select geolfrom as geolfrom_str, geolto as geolto_str, s.Structural_Data_rus_Desc_D as 'Strucrure',
row_number() over (order by geolfrom) as rn_struc
From
(SELECT TOP 100 PERCENT * FROM (SELECT * FROM (SELECT TOP 100 PERCENT * FROM (SELECT TOP 100 PERCENT [HOLEID], [PROJECTCODE], [GEOLFROM], [GEOLTO], [PRIORITY], ( CASE WHEN [ACQDERIVEDVIEW].[Str_Elements_Desc_D] is null THEN '' ELSE [ACQDERIVEDVIEW].[Str_Elements_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Str_Intensity_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Str_Intensity_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Str_AlphaAngleCoreFr]) is null THEN '' ELSE ', from ' + str([ACQDERIVEDVIEW].[Str_AlphaAngleCoreFr])
END +
CASE WHEN str([ACQDERIVEDVIEW].[Str_AlphaAngleCoreTo]) is null THEN '' ELSE ' to ' + str([ACQDERIVEDVIEW].[Str_AlphaAngleCoreTo])
END +
CASE WHEN str([ACQDERIVEDVIEW].[Str_Beta]) is null THEN '' ELSE ', ' + str([ACQDERIVEDVIEW].[Str_Beta])
END ) AS [Structural_Data_Desc_D], ( CASE WHEN [ACQDERIVEDVIEW].[Str_Elements_rus_Desc_D] is null THEN '' ELSE [ACQDERIVEDVIEW].[Str_Elements_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Str_Intensity_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Str_Intensity_rus_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Str_AlphaAngleCoreFr]) is null THEN '' ELSE
(CASE WHEN [ACQDERIVEDVIEW].[Str_Elements] = 'CONT' THEN ' на отм.' + str([ACQDERIVEDVIEW].[GEOLTO],7,2) + 'м под уг.ок ' + ltrim(str([ACQDERIVEDVIEW].[Str_AlphaAngleCoreFr])) ELSE
' уг.ок ' + ltrim(str([ACQDERIVEDVIEW].[Str_AlphaAngleCoreFr])) END )
END +
CASE WHEN [ACQDERIVEDVIEW].[Str_Type] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Str_Type_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Str_Appearance] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Str_Appearance_rus_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Str_AlphaAngleCoreTo]) is null THEN '' ELSE '-' + ltrim(str([ACQDERIVEDVIEW].[Str_AlphaAngleCoreTo]))
END +
CASE WHEN str([ACQDERIVEDVIEW].[Str_Beta]) is null THEN '' ELSE ', ' + 'уг beta ' + ltrim(str([ACQDERIVEDVIEW].[Str_Beta]))
END +
CASE WHEN [ACQDERIVEDVIEW].[Struc_Comments] is null THEN '' ELSE ', ( ' + [ACQDERIVEDVIEW].[Struc_Comments] + ')'
END ) AS [Structural_Data_rus_Desc_D] FROM ( SELECT TOP 100 PERCENT ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Str_Elements'
and LOOKUP = [DFINDF].[Str_Elements]
and ACTIVE = 1 ) AS [Str_Elements_Desc_D], ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Str_Intensity'
and LOOKUP = [DFINDF].[Str_Intensity]
and ACTIVE = 1 ) AS [Str_Intensity_Desc_D], ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Str_Appearance'
and LOOKUP = [DFINDF].[Str_Appearance]
and ACTIVE = 1 ) AS [Str_Appearance_rus_Desc_D], ( select UPPER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Str_Elements'
and LOOKUP = [DFINDF].[Str_Elements]
and ACTIVE = 1 ) AS [Str_Elements_rus_Desc_D], ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Str_Intensity'
and LOOKUP = [DFINDF].[Str_Intensity]
and ACTIVE = 1 ) AS [Str_Intensity_rus_Desc_D], ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Str_Type'
and LOOKUP = [DFINDF].[Str_Type]
and ACTIVE = 1 ) AS [Str_Type_rus_Desc_D], [HOLEID], [PROJECTCODE], [GEOLFROM], [GEOLTO], [PRIORITY], [Str_AlphaAngleCoreFr], [Str_AlphaAngleCoreTo], [Str_Beta], [Str_Appearance], [Struc_Comments], [Str_Type], [Str_Elements]
FROM ( SELECT [GEOINTERVAL].[HOLEID], [GEOINTERVAL].[PROJECTCODE], [GEOINTERVAL].[GEOLFROM], [GEOINTERVAL].[GEOLTO], [GEOINTERVAL].[PRIORITY], CAST([GEODETAILS].[Str_AlphaAngleCoreFr] AS FLOAT) AS [Str_AlphaAngleCoreFr], CAST([GEODETAILS].[Str_AlphaAngleCoreTo] AS FLOAT)
AS [Str_AlphaAngleCoreTo], CAST([GEODETAILS].[Str_Beta] AS FLOAT) AS [Str_Beta], [GEODETAILS].[Str_Elements], [GEODETAILS].[Str_Intensity], [GEODETAILS].[Str_Appearance], [GEODETAILS].[Str_Type], [GEOBIGCOMMENT].[Struc_Comments] FROM [GEOINTERVAL] INNER JOIN (SELECT * FROM [HOLELOCATION])
AS [CollarWSF] ON [CollarWSF].[HOLEID] = [GEOINTERVAL].[HOLEID] AND [CollarWSF].[PROJECTCODE] = [GEOINTERVAL].[PROJECTCODE] AND [CollarWSF].[HOLETYPE] = 'DRILLHOLE' LEFT JOIN (SELECT [GEODETAILS].[HOLEID], [GEODETAILS].[PROJECTCODE], [GEODETAILS].[GEOLFROM], [GEODETAILS].[GEOLTO], [GEODETAILS].[PRIORITY],
min(CASE when [GEODETAILS].[NAME] = 'Str_AlphaAngleCoreFr' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Str_AlphaAngleCoreFr], min(CASE when [GEODETAILS].[NAME] = 'Str_AlphaAngleCoreTo' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Str_AlphaAngleCoreTo],
min(CASE when [GEODETAILS].[NAME] = 'Str_Beta' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Str_Beta], min(CASE when [GEODETAILS].[NAME] = 'Str_Elements' then [GEODETAILS].[VALUE] ELSE NULL END) as [Str_Elements], min(CASE when [GEODETAILS].[NAME] = 'Str_Intensity'
then [GEODETAILS].[VALUE] ELSE NULL END) as [Str_Intensity], min(CASE when [GEODETAILS].[NAME] = 'Str_Appearance' then [GEODETAILS].[VALUE] ELSE NULL END) as [Str_Appearance], min(CASE when [GEODETAILS].[NAME] = 'Str_Type' then [GEODETAILS].[VALUE] ELSE NULL END) as [Str_Type] FROM [GEODETAILS]
WHERE [GEODETAILS].[NAME] IN ('Str_AlphaAngleCoreFr', 'Str_AlphaAngleCoreTo', 'Str_Beta', 'Str_Elements', 'Str_Intensity', 'Str_Appearance', 'Str_Type') GROUP BY [GEODETAILS].[PROJECTCODE], [GEODETAILS].[PRIORITY], [GEODETAILS].[GEOLTO], [GEODETAILS].[GEOLFROM], [GEODETAILS].[HOLEID])[GEODETAILS]
ON [GEOINTERVAL].[PROJECTCODE] = [GEODETAILS].[PROJECTCODE] AND [GEOINTERVAL].[PRIORITY] = [GEODETAILS].[PRIORITY] AND [GEOINTERVAL].[GEOLTO] = [GEODETAILS].[GEOLTO] AND [GEOINTERVAL].[GEOLFROM] = [GEODETAILS].[GEOLFROM] AND [GEOINTERVAL].[HOLEID] = [GEODETAILS].[HOLEID] LEFT JOIN
(SELECT [GEOBIGCOMMENT].[HOLEID], [GEOBIGCOMMENT].[PROJECTCODE], [GEOBIGCOMMENT].[GEOLFROM], [GEOBIGCOMMENT].[GEOLTO], [GEOBIGCOMMENT].[PRIORITY], min(CASE when [GEOBIGCOMMENT].[NAME] = 'Struc_Comments' then [GEOBIGCOMMENT].[VALUE] ELSE NULL END) as [Struc_Comments] FROM [GEOBIGCOMMENT]
WHERE [GEOBIGCOMMENT].[NAME] IN ('Struc_Comments') GROUP BY [GEOBIGCOMMENT].[PROJECTCODE], [GEOBIGCOMMENT].[PRIORITY], [GEOBIGCOMMENT].[GEOLTO], [GEOBIGCOMMENT].[GEOLFROM], [GEOBIGCOMMENT].[HOLEID])[GEOBIGCOMMENT] ON [GEOINTERVAL].[PROJECTCODE] = [GEOBIGCOMMENT].[PROJECTCODE]
AND [GEOINTERVAL].[PRIORITY] = [GEOBIGCOMMENT].[PRIORITY] AND [GEOINTERVAL].[GEOLTO] = [GEOBIGCOMMENT].[GEOLTO] AND [GEOINTERVAL].[GEOLFROM] = [GEOBIGCOMMENT].[GEOLFROM] AND [GEOINTERVAL].[HOLEID] = [GEOBIGCOMMENT].[HOLEID] WHERE ([Str_AlphaAngleCoreFr] IS NOT NULL OR [Str_AlphaAngleCoreTo] IS NOT NULL
OR [Str_Beta] IS NOT NULL OR [Str_Elements] IS NOT NULL OR [Str_Intensity] IS NOT NULL OR [Str_Appearance] IS NOT NULL OR [Str_Type] IS NOT NULL OR [Struc_Comments] IS NOT NULL) ) [DFINDF]) [ACQDERIVEDVIEW]) [ACQTMP] ) AS [TMPVIEW267]) [TMPSQLSHEETVIEW] ) s
Where a.holeid = s.holeid
and a.geolto>s.geolfrom and a.geolfrom<s.geolto and a.GEOLFROM<=s.GEOLFROM ) struc
on rn_sec = rn_struc
full join
(Select geolfrom as geolfrom_min, geolto as geolto_min,m.Mineralization_Zone_rus_Desc_D as 'Mineralisation',
row_number() over (order by geolfrom) as rn_min
From (
SELECT TOP 100 PERCENT * FROM (SELECT * FROM (SELECT TOP 100 PERCENT * FROM (SELECT TOP 100 PERCENT [HOLEID], [PROJECTCODE], [GEOLFROM], [GEOLTO], [PRIORITY], ( CASE WHEN [ACQDERIVEDVIEW].[Mineralization_Desc_D] is null THEN '' ELSE [ACQDERIVEDVIEW].[Mineralization_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Min_Morphology_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min_Morphology_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Min_Occurence_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min_Occurence_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min_IntensityPct]) is null THEN '' ELSE ', ' + str([ACQDERIVEDVIEW].[Min_IntensityPct]) + '%'
END +
CASE WHEN [ACQDERIVEDVIEW].[Mineralization2_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Mineralization2_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Min2_Morphology_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min2_Morphology_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Min2_Occurence_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min2_Occurence_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min2_IntensityPct]) is null THEN '' ELSE ', ' + str([ACQDERIVEDVIEW].[Min2_IntensityPct]) + '%'
END +
CASE WHEN [ACQDERIVEDVIEW].[Mineralization3_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Mineralization3_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Min3_Morphology_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min3_Morphology_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Min3_Occurence_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min3_Occurence_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min3_IntensityPct]) is null THEN '' ELSE ', ' + str([ACQDERIVEDVIEW].[Min3_IntensityPct]) + '%'
END +
CASE WHEN [ACQDERIVEDVIEW].[Mineralization4_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Mineralization4_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Mineralization5_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Mineralization5_Desc_D]
END ) AS [Mineralization_Zone_Desc_D], ( CASE WHEN [ACQDERIVEDVIEW].[Mineralization_rus_Desc_D] is null THEN '' ELSE [ACQDERIVEDVIEW].[Mineralization_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Min_Morphology_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min_Morphology_rus_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min_SizeFrom]) is null THEN '' ELSE ', ' + ltrim(str([ACQDERIVEDVIEW].[Min_SizeFrom]))
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min_SizeTo]) is null THEN '' ELSE ' - ' + ltrim(str([ACQDERIVEDVIEW].[Min_SizeTo])) + 'мм'
END +
CASE WHEN [ACQDERIVEDVIEW].[Min_Occurence_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min_Occurence_rus_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min_IntensityPct]) is null THEN '' ELSE ', ' + ltrim(str([ACQDERIVEDVIEW].[Min_IntensityPct],4,1)) + '%'
END +
CASE WHEN [ACQDERIVEDVIEW].[Mineralization2_rus_Desc_D] is null THEN '' ELSE '. ' + [ACQDERIVEDVIEW].[Mineralization2_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Min2_Morphology_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min2_Morphology_rus_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min2_SizeFrom]) is null THEN '' ELSE ', ' + ltrim(str([ACQDERIVEDVIEW].[Min2_SizeFrom]))
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min2_SizeTo]) is null THEN '' ELSE ' - ' + ltrim(str([ACQDERIVEDVIEW].[Min2_SizeTo])) + 'мм'
END +
CASE WHEN [ACQDERIVEDVIEW].[Min2_Occurence_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min2_Occurence_rus_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min2_IntensityPct]) is null THEN '' ELSE ', ' + ltrim(str([ACQDERIVEDVIEW].[Min2_IntensityPct],4,1)) + '%'
END +
CASE WHEN [ACQDERIVEDVIEW].[Mineralization3_rus_Desc_D] is null THEN '' ELSE '. ' + [ACQDERIVEDVIEW].[Mineralization3_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Min3_Morphology_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min3_Morphology_rus_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min3_SizeFrom]) is null THEN '' ELSE ', ' + ltrim(str([ACQDERIVEDVIEW].[Min3_SizeFrom]))
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min3_SizeTo]) is null THEN '' ELSE ' - ' + ltrim(str([ACQDERIVEDVIEW].[Min3_SizeTo])) + 'мм'
END +
CASE WHEN [ACQDERIVEDVIEW].[Min3_Occurence_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min3_Occurence_rus_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min3_IntensityPct]) is null THEN '' ELSE ', ' + ltrim(str([ACQDERIVEDVIEW].[Min3_IntensityPct],4,1)) + '%'
END +
CASE WHEN [ACQDERIVEDVIEW].[Mineralization4_rus_Desc_D] is null THEN '' ELSE '. ' + [ACQDERIVEDVIEW].[Mineralization4_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Min4_Morphology_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min4_Morphology_rus_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min4_SizeFrom]) is null THEN '' ELSE ', ' + ltrim(str([ACQDERIVEDVIEW].[Min4_SizeFrom]))
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min4_SizeTo]) is null THEN '' ELSE ' - ' + ltrim(str([ACQDERIVEDVIEW].[Min4_SizeTo])) + 'мм'
END +
CASE WHEN [ACQDERIVEDVIEW].[Min4_Occurence_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min4_Occurence_rus_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min4_IntensityPct]) is null THEN '' ELSE ', ' + ltrim(str([ACQDERIVEDVIEW].[Min4_IntensityPct],4,1)) + '%'
END +
CASE WHEN [ACQDERIVEDVIEW].[Mineralization5_rus_Desc_D] is null THEN '' ELSE '. ' + [ACQDERIVEDVIEW].[Mineralization5_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Min5_Morphology_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min5_Morphology_rus_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min5_SizeFrom]) is null THEN '' ELSE ', ' + ltrim(str([ACQDERIVEDVIEW].[Min5_SizeFrom]))
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min5_SizeTo]) is null THEN '' ELSE ' - ' + ltrim(str([ACQDERIVEDVIEW].[Min5_SizeTo])) + 'мм'
END +
CASE WHEN [ACQDERIVEDVIEW].[Min5_Occurence_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min5_Occurence_rus_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min5_IntensityPct]) is null THEN '' ELSE ', ' + ltrim(str([ACQDERIVEDVIEW].[Min5_IntensityPct],4,1)) + '%'
END +
CASE WHEN [ACQDERIVEDVIEW].[Min_Comments] is null THEN '' ELSE ', (' + [ACQDERIVEDVIEW].[Min_Comments] + ')'
END ) AS [Mineralization_Zone_rus_Desc_D] FROM ( SELECT TOP 100 PERCENT ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min_Morphology'
and LOOKUP = [DFINDF].[Min_Morphology]
and ACTIVE = 1 ) AS [Min_Morphology_Desc_D], ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min_Occurence'
and LOOKUP = [DFINDF].[Min_Occurence]
and ACTIVE = 1 ) AS [Min_Occurence_Desc_D], ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min2_Morphology'
and LOOKUP = [DFINDF].[Min2_Morphology]
and ACTIVE = 1 ) AS [Min2_Morphology_Desc_D], ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min2_Occurence'
and LOOKUP = [DFINDF].[Min2_Occurence]
and ACTIVE = 1 ) AS [Min2_Occurence_Desc_D], ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min3_Morphology'
and LOOKUP = [DFINDF].[Min3_Morphology]
and ACTIVE = 1 ) AS [Min3_Morphology_Desc_D], ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min3_Occurence'
and LOOKUP = [DFINDF].[Min3_Occurence]
and ACTIVE = 1 ) AS [Min3_Occurence_Desc_D], ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Mineralisation'
and LOOKUP = [DFINDF].[Mineralisation]
and ACTIVE = 1 ) AS [Mineralization_Desc_D], ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Mineralisation2'
and LOOKUP = [DFINDF].[Mineralisation2]
and ACTIVE = 1 ) AS [Mineralization2_Desc_D], ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Mineralisation3'
and LOOKUP = [DFINDF].[Mineralisation3]
and ACTIVE = 1 ) AS [Mineralization3_Desc_D], ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Mineralisation4'
and LOOKUP = [DFINDF].[Mineralisation4]
and ACTIVE = 1 ) AS [Mineralization4_Desc_D], ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Mineralisation5'
and LOOKUP = [DFINDF].[Mineralisation5]
and ACTIVE = 1 ) AS [Mineralization5_Desc_D], ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min_Morphology'
and LOOKUP = [DFINDF].[Min_Morphology]
and ACTIVE = 1 ) AS [Min_Morphology_rus_Desc_D], ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min_Occurence'
and LOOKUP = [DFINDF].[Min_Occurence]
and ACTIVE = 1 ) AS [Min_Occurence_rus_Desc_D], ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min2_Morphology'
and LOOKUP = [DFINDF].[Min2_Morphology]
and ACTIVE = 1 ) AS [Min2_Morphology_rus_Desc_D], ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min2_Occurence'
and LOOKUP = [DFINDF].[Min2_Occurence]
and ACTIVE = 1 ) AS [Min2_Occurence_rus_Desc_D], ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min3_Morphology'
and LOOKUP = [DFINDF].[Min3_Morphology]
and ACTIVE = 1 ) AS [Min3_Morphology_rus_Desc_D], ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min3_Occurence'
and LOOKUP = [DFINDF].[Min3_Occurence]
and ACTIVE = 1 ) AS [Min3_Occurence_rus_Desc_D], ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min4_Morphology'
and LOOKUP = [DFINDF].[Min4_Morphology]
and ACTIVE = 1 ) AS [Min4_Morphology_rus_Desc_D], ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min4_Occurence'
and LOOKUP = [DFINDF].[Min4_Occurence]
and ACTIVE = 1 ) AS [Min4_Occurence_rus_Desc_D], ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min5_Morphology'
and LOOKUP = [DFINDF].[Min5_Morphology]
and ACTIVE = 1 ) AS [Min5_Morphology_rus_Desc_D], ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min5_Occurence'
and LOOKUP = [DFINDF].[Min5_Occurence]
and ACTIVE = 1 ) AS [Min5_Occurence_rus_Desc_D], ( select UPPER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Mineralisation'
and LOOKUP = [DFINDF].[Mineralisation]
and ACTIVE = 1 ) AS [Mineralization_rus_Desc_D], ( select UPPER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Mineralisation2'
and LOOKUP = [DFINDF].[Mineralisation2]
and ACTIVE = 1 ) AS [Mineralization2_rus_Desc_D], ( select UPPER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Mineralisation3'
and LOOKUP = [DFINDF].[Mineralisation3]
and ACTIVE = 1 ) AS [Mineralization3_rus_Desc_D], ( select UPPER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Mineralisation4'
and LOOKUP = [DFINDF].[Mineralisation4]
and ACTIVE = 1 ) AS [Mineralization4_rus_Desc_D], ( select UPPER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Mineralisation5'
and LOOKUP = [DFINDF].[Mineralisation5]
and ACTIVE = 1 ) AS [Mineralization5_rus_Desc_D], [HOLEID], [PROJECTCODE], [GEOLFROM], [GEOLTO], [PRIORITY], [Min_IntensityPct], [Min2_IntensityPct], [Min3_IntensityPct], [Min4_SizeFrom], [Min2_SizeTo], [Min5_SizeTo], [Min_Comments], [Min5_SizeFrom], [Min_SizeTo], [Min2_SizeFrom], [Min3_SizeTo],
[Min4_IntensityPct], [Min5_IntensityPct], [Min_SizeFrom], [Min3_SizeFrom], [Min4_SizeTo] FROM ( SELECT [GEOINTERVAL].[HOLEID], [GEOINTERVAL].[PROJECTCODE], [GEOINTERVAL].[GEOLFROM], [GEOINTERVAL].[GEOLTO], [GEOINTERVAL].[PRIORITY], CAST([GEODETAILS].[Min_IntensityPct] AS FLOAT) AS [Min_IntensityPct],
[GEODETAILS].[Min_Morphology], [GEODETAILS].[Min_Occurence], CAST([GEODETAILS].[Min2_IntensityPct] AS FLOAT) AS [Min2_IntensityPct], [GEODETAILS].[Min2_Morphology], [GEODETAILS].[Min2_Occurence], CAST([GEODETAILS].[Min3_IntensityPct] AS FLOAT) AS [Min3_IntensityPct], [GEODETAILS].[Min3_Morphology],
[GEODETAILS].[Min3_Occurence], [GEODETAILS].[Mineralisation], [GEODETAILS].[Mineralisation2], [GEODETAILS].[Mineralisation3], [GEODETAILS].[Mineralisation4], [GEODETAILS].[Mineralisation5], [GEOBIGCOMMENT].[Min_Comments], CAST([GEODETAILS].[Min_SizeFrom] AS FLOAT) AS [Min_SizeFrom], CAST([GEODETAILS].[Min_SizeTo]
AS FLOAT) AS [Min_SizeTo], CAST([GEODETAILS].[Min2_SizeFrom] AS FLOAT) AS [Min2_SizeFrom], CAST([GEODETAILS].[Min2_SizeTo] AS FLOAT) AS [Min2_SizeTo], CAST([GEODETAILS].[Min3_SizeFrom] AS FLOAT) AS [Min3_SizeFrom], CAST([GEODETAILS].[Min3_SizeTo] AS FLOAT) AS [Min3_SizeTo], CAST([GEODETAILS].[Min4_IntensityPct]
AS FLOAT) AS [Min4_IntensityPct], [GEODETAILS].[Min4_Morphology], [GEODETAILS].[Min4_Occurence], CAST([GEODETAILS].[Min4_SizeFrom] AS FLOAT) AS [Min4_SizeFrom], CAST([GEODETAILS].[Min4_SizeTo] AS FLOAT) AS [Min4_SizeTo], CAST([GEODETAILS].[Min5_IntensityPct] AS FLOAT) AS [Min5_IntensityPct],
[GEODETAILS].[Min5_Morphology], [GEODETAILS].[Min5_Occurence], CAST([GEODETAILS].[Min5_SizeFrom] AS FLOAT) AS [Min5_SizeFrom], CAST([GEODETAILS].[Min5_SizeTo] AS FLOAT) AS [Min5_SizeTo] FROM [GEOINTERVAL] INNER JOIN (SELECT * FROM [HOLELOCATION]) AS [CollarWSF] ON [CollarWSF].[HOLEID] = [GEOINTERVAL].[HOLEID]
AND [CollarWSF].[PROJECTCODE] = [GEOINTERVAL].[PROJECTCODE] AND [CollarWSF].[HOLETYPE] = 'DRILLHOLE' LEFT JOIN (SELECT [GEODETAILS].[HOLEID], [GEODETAILS].[PROJECTCODE], [GEODETAILS].[GEOLFROM], [GEODETAILS].[GEOLTO], [GEODETAILS].[PRIORITY], min(CASE when [GEODETAILS].[NAME] = 'Min_IntensityPct' then
CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Min_IntensityPct], min(CASE when [GEODETAILS].[NAME] = 'Min_Morphology' then [GEODETAILS].[VALUE] ELSE NULL END) as [Min_Morphology], min(CASE when [GEODETAILS].[NAME] = 'Min_Occurence' then [GEODETAILS].[VALUE] ELSE NULL END) as [Min_Occurence],
min(CASE when [GEODETAILS].[NAME] = 'Min2_IntensityPct' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Min2_IntensityPct], min(CASE when [GEODETAILS].[NAME] = 'Min2_Morphology' then [GEODETAILS].[VALUE] ELSE NULL END) as [Min2_Morphology], min(CASE when [GEODETAILS].[NAME] = 'Min2_Occurence'
then [GEODETAILS].[VALUE] ELSE NULL END) as [Min2_Occurence], min(CASE when [GEODETAILS].[NAME] = 'Min3_IntensityPct' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Min3_IntensityPct], min(CASE when [GEODETAILS].[NAME] = 'Min3_Morphology' then [GEODETAILS].[VALUE] ELSE NULL END) as [Min3_Morphology],
min(CASE when [GEODETAILS].[NAME] = 'Min3_Occurence' then [GEODETAILS].[VALUE] ELSE NULL END) as [Min3_Occurence], min(CASE when [GEODETAILS].[NAME] = 'Mineralisation' then [GEODETAILS].[VALUE] ELSE NULL END) as [Mineralisation], min(CASE when [GEODETAILS].[NAME] = 'Mineralisation2' then [GEODETAILS].[VALUE] ELSE
NULL END) as [Mineralisation2], min(CASE when [GEODETAILS].[NAME] = 'Mineralisation3' then [GEODETAILS].[VALUE] ELSE NULL END) as [Mineralisation3], min(CASE when [GEODETAILS].[NAME] = 'Mineralisation4' then [GEODETAILS].[VALUE] ELSE NULL END) as [Mineralisation4], min(CASE when
[GEODETAILS].[NAME] = 'Mineralisation5' then [GEODETAILS].[VALUE] ELSE NULL END) as [Mineralisation5], min(CASE when [GEODETAILS].[NAME] = 'Min_SizeFrom' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Min_SizeFrom], min(CASE when [GEODETAILS].[NAME] = 'Min_SizeTo' then CAST([GEODETAILS].[VALUE]
AS FLOAT) ELSE NULL END) as [Min_SizeTo], min(CASE when [GEODETAILS].[NAME] = 'Min2_SizeFrom' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Min2_SizeFrom], min(CASE when [GEODETAILS].[NAME] = 'Min2_SizeTo' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Min2_SizeTo],
min(CASE when [GEODETAILS].[NAME] = 'Min3_SizeFrom' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Min3_SizeFrom], min(CASE when [GEODETAILS].[NAME] = 'Min3_SizeTo' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Min3_SizeTo], min(CASE when [GEODETAILS].[NAME] = 'Min4_IntensityPct'
then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Min4_IntensityPct], min(CASE when [GEODETAILS].[NAME] = 'Min4_Morphology' then [GEODETAILS].[VALUE] ELSE NULL END) as [Min4_Morphology], min(CASE when [GEODETAILS].[NAME] = 'Min4_Occurence' then [GEODETAILS].[VALUE] ELSE NULL END) as [Min4_Occurence],
min(CASE when [GEODETAILS].[NAME] = 'Min4_SizeFrom' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Min4_SizeFrom], min(CASE when [GEODETAILS].[NAME] = 'Min4_SizeTo' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Min4_SizeTo], min(CASE when [GEODETAILS].[NAME] = 'Min5_IntensityPct'
then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Min5_IntensityPct], min(CASE when [GEODETAILS].[NAME] = 'Min5_Morphology' then [GEODETAILS].[VALUE] ELSE NULL END) as [Min5_Morphology], min(CASE when [GEODETAILS].[NAME] = 'Min5_Occurence' then [GEODETAILS].[VALUE] ELSE NULL END) as [Min5_Occurence],
min(CASE when [GEODETAILS].[NAME] = 'Min5_SizeFrom' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Min5_SizeFrom], min(CASE when [GEODETAILS].[NAME] = 'Min5_SizeTo' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Min5_SizeTo] FROM [GEODETAILS] GROUP BY [GEODETAILS].[PROJECTCODE],
[GEODETAILS].[PRIORITY], [GEODETAILS].[GEOLTO], [GEODETAILS].[GEOLFROM], [GEODETAILS].[HOLEID])[GEODETAILS] ON [GEOINTERVAL].[PROJECTCODE] = [GEODETAILS].[PROJECTCODE] AND [GEOINTERVAL].[PRIORITY] = [GEODETAILS].[PRIORITY] AND [GEOINTERVAL].[GEOLTO] = [GEODETAILS].[GEOLTO]
AND [GEOINTERVAL].[GEOLFROM] = [GEODETAILS].[GEOLFROM] AND [GEOINTERVAL].[HOLEID] = [GEODETAILS].[HOLEID] LEFT JOIN (SELECT [GEOBIGCOMMENT].[HOLEID], [GEOBIGCOMMENT].[PROJECTCODE], [GEOBIGCOMMENT].[GEOLFROM], [GEOBIGCOMMENT].[GEOLTO], [GEOBIGCOMMENT].[PRIORITY],
min(CASE when [GEOBIGCOMMENT].[NAME] = 'Min_Comments' then [GEOBIGCOMMENT].[VALUE] ELSE NULL END) as [Min_Comments] FROM [GEOBIGCOMMENT] WHERE [GEOBIGCOMMENT].[NAME] IN ('Min_Comments') GROUP BY [GEOBIGCOMMENT].[PROJECTCODE], [GEOBIGCOMMENT].[PRIORITY], [GEOBIGCOMMENT].[GEOLTO],
[GEOBIGCOMMENT].[GEOLFROM], [GEOBIGCOMMENT].[HOLEID])[GEOBIGCOMMENT] ON [GEOINTERVAL].[PROJECTCODE] = [GEOBIGCOMMENT].[PROJECTCODE] AND [GEOINTERVAL].[PRIORITY] = [GEOBIGCOMMENT].[PRIORITY] AND [GEOINTERVAL].[GEOLTO] = [GEOBIGCOMMENT].[GEOLTO] AND [GEOINTERVAL].[GEOLFROM] = [GEOBIGCOMMENT].[GEOLFROM]
AND [GEOINTERVAL].[HOLEID] = [GEOBIGCOMMENT].[HOLEID] WHERE ([Min_IntensityPct] IS NOT NULL OR [Min_Morphology] IS NOT NULL OR [Min_Occurence] IS NOT NULL OR [Min2_IntensityPct] IS NOT NULL OR [Min2_Morphology] IS NOT NULL OR [Min2_Occurence] IS NOT NULL OR [Min3_IntensityPct] IS NOT NULL
OR [Min3_Morphology] IS NOT NULL OR [Min3_Occurence] IS NOT NULL OR [Mineralisation] IS NOT NULL OR [Mineralisation2] IS NOT NULL OR [Mineralisation3] IS NOT NULL OR [Mineralisation4] IS NOT NULL OR [Mineralisation5] IS NOT NULL OR [Min_Comments] IS NOT NULL OR [Min_SizeFrom] IS NOT NULL
OR [Min_SizeTo] IS NOT NULL OR [Min2_SizeFrom] IS NOT NULL OR [Min2_SizeTo] IS NOT NULL OR [Min3_SizeFrom] IS NOT NULL OR [Min3_SizeTo] IS NOT NULL OR [Min4_IntensityPct] IS NOT NULL OR [Min4_Morphology] IS NOT NULL OR [Min4_Occurence] IS NOT NULL OR [Min4_SizeFrom] IS NOT NULL OR [Min4_SizeTo] IS NOT NULL
OR [Min5_IntensityPct] IS NOT NULL OR [Min5_Morphology] IS NOT NULL OR [Min5_Occurence] IS NOT NULL OR [Min5_SizeFrom] IS NOT NULL OR [Min5_SizeTo] IS NOT NULL) ) [DFINDF]) [ACQDERIVEDVIEW]) [ACQTMP] ) AS [TMPVIEW275]) [TMPSQLSHEETVIEW]) m
Where a.holeid = m.holeid
and a.geolto>m.geolfrom and a.geolfrom<m.geolto and a.GEOLFROM<=m.GEOLFROM) min on isnull(rn_sec,rn_alt) = rn_min
) others
--left join (select case row_number() over(partition by holeid order by geolfrom) when 1 then holeid end as x_holeid, * from GEODETAILS where name='Lith_RockName' or name='Alteration' or name='Mineralization' or name='Structure'
--)ord on ord.holeid=a.HOLEID and ord.GEOLFROM=a.GEOLFROM
WHere a.HOLEID='DZDZ-18-001'
--order by ord.HOLEID,ord.GEOLFROM
Order by a.holeid desc,a.geolfrom,a.Primary_Lithology_rus_Desc_D, COALESCE(others.rn_struc,others.rn_sec,others.rn_alt,others.rn_min) asc
|
