Доброе время суток. Есть такая задача. Нужно написать Union который бы содержал количество записей из каждой таблицы. Нужно пощитать: Count(Task_Meeting), Count(Task_Call), Count(Pidpus_Agreem), Count(Vidmov_Agreem), Count(Test_Drive)
Вот сам запрос:
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.
--Task Meeting
SELECT dbo.FilteredNew_taskmeeteng.new_startdate AS startdate,
dbo.FilteredNew_taskmeeteng.new_name AS Task_Meeting,
NULL AS Task_Call,
NULL AS Pidpus_Agreem,
NULL AS Vidmov_Agreem,
NULL AS Test_Drive
FROM dbo.FilteredNew_taskmeeteng
WHERE (new_meeting = 1 ) AND (new_taskstate = 2 )
--Task Call
UNION ALL
SELECT FilteredNew_taskcall.new_name AS Task_Call,
FilteredNew_taskcall.new_startdate AS startdate,
NULL AS Task_Meeting,
NULL AS Pidpus_Agreem,
NULL AS Vidmov_Agreem,
NULL AS Test_Drive
FROM FilteredNew_taskcall
WHERE (FilteredNew_taskcall.new_taskstatename = 2 )
--Pidpus Agreement
UNION ALL
SELECT (dbo.FilteredNew_agreement.new_name) AS Pidpus_Agreem,
dbo.FilteredNew_agreement.new_startwork AS startdate,
NULL AS Task_Call,
NULL AS Task_Meeting,
NULL AS Vidmov_Agreem,
NULL AS Test_Drive
FROM dbo.FilteredNew_vehicle LEFT OUTER JOIN
dbo.FilteredNew_agreement ON dbo.FilteredNew_vehicle.new_vehicleid = dbo.FilteredNew_agreement.new_vehicleid
WHERE (dbo.FilteredNew_agreement.new_stateagreement = N'4')
-- Vidmov Agreemment
UNION ALL
SELECT (dbo.FilteredNew_agreement.new_name) AS Vidmov_Agreem,
dbo.FilteredNew_agreement.new_startwork AS StartDate,
NULL AS Task_Meeting,
NULL AS Task_Call,
NULL AS Pidpus_Agreem,
NULL AS Test_Drive
FROM dbo.FilteredNew_vehicle LEFT OUTER JOIN
dbo.FilteredNew_agreement ON dbo.FilteredNew_vehicle.new_vehicleid = dbo.FilteredNew_agreement.new_vehicleid
WHERE (dbo.FilteredNew_agreement.new_stateagreement = N'6')
--Test Drive
UNION ALL
SELECT dbo.New_tasktestdriveExtensionBase.New_StartDate AS startdate,
dbo.New_tasktestdriveExtensionBase.New_name AS Test_Drive,
NULL AS Task_Meeting,
NULL AS Task_Call,
NULL AS Pidpus_Agreem,
NULL AS Vidmov_Agreem
FROM dbo.New_tasktestdriveBase LEFT OUTER JOIN
dbo.New_tasktestdriveExtensionBase ON
dbo.New_tasktestdriveBase.New_tasktestdriveId = dbo.New_tasktestdriveExtensionBase.New_tasktestdriveId LEFT OUTER JOIN
dbo.new_vehicle_tasktestdriveBase ON
dbo.New_tasktestdriveBase.New_tasktestdriveId = dbo.new_vehicle_tasktestdriveBase.new_tasktestdriveid LEFT OUTER JOIN
dbo.New_vehicleExtensionBase ON dbo.new_vehicle_tasktestdriveBase.new_vehicleid = dbo.New_vehicleExtensionBase.New_vehicleId
WHERE (dbo.New_tasktestdriveExtensionBase.New_ResultTaskCode = N'5') AND
(dbo.New_tasktestdriveExtensionBase.New_TaskState = N'2')
Group By dbo.New_tasktestdriveExtensionBase.New_StartDate,
dbo.New_tasktestdriveExtensionBase.New_name;
При этом выпадает ошибка:
Msg 241, Level 16, State 1, Line 2
Conversion failed when converting datetime from character string.
В дальнейшем мне нужно в SSRS в матричной таблице вывести период дат.
Кстати. В запросе используются как вьюшки так и таблицы. И по отдельности все запросе внутри юниона работают нормально.
