· 6 years ago · Jul 04, 2019, 01:34 PM
1IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[usp_IndsutryRegistrationReport]') AND TYPE IN (N'P',N'PC'))
2 DROP PROCEDURE [dbo].usp_IndsutryRegistrationReport
3GO
4
5SET ANSI_NULLS ON
6GO
7
8SET QUOTED_IDENTIFIER ON
9GO
10
11CREATE PROCEDURE dbo.usp_IndsutryRegistrationReport
12 @StartDate DATETIME,
13 @EndDate DATETIME
14AS
15BEGIN
16
17 IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp
18 SELECT
19 IndustryCategoryId,
20 IndustrySubCategoryId,
21 1 OrderId,
22 IndustryName,
23 MAX(id.Industrialist) AS Industrialist,
24 MAX([Address]) AS [Address],
25 SUM(id.FixedCapital) AS FixedCapital,
26 SUM(id.CurrentCapital) AS CurrentCapital,
27 SUM(id.TotalCapital) AS TotalCapital,
28 SUM(MaleEmployeeCount) AS MaleEmployeeCount,
29 SUM(FemaleEmployeeCount) AS FemaleEmployeeCount,
30 SUM(MaleEntrepreneurCount) AS MaleEntrepreneurCount,
31 SUM(FemaleEntrepreneurCount) AS FemaleEntrepreneurCount,
32 SUM(YearlyProductionCapacity) AS YearlyProductionCapacity,
33 SUM(ElectricalConsumption) AS ElectricalConsumption,
34 SUM(RegistrationNumber) AS RegistrationNumber,
35 MAX(RegistrationDate) AS RegistrationDate,
36 MAX(IndustryPurpose) AS IndustryPurpose,
37 MAX(id.IndustryState) AS IndustryState,
38 CASE WHEN MAX(ilsi.Name) = N'प्रा.फ' Then MAX(ilsi.Name) END AS Prafa,
39 CASE WHEN MAX(ilsi.Name) = N'प्रा.लि' Then MAX(ilsi.Name) END AS Prali,
40 CASE WHEN MAX(ilsi.Name) = N'सा.फ' Then MAX(ilsi.Name) END AS Safa,
41 CASE WHEN MAX(ity.Name) = N'साना' Then MAX(ity.Name) END AS Sana,
42 CASE WHEN MAX(ity.Name) = N'घरेलु' Then MAX(ity.Name) END AS Gharelu,
43 'record' AS Class
44 INTO #temp
45 FROM IndustryDetails id
46 INNER JOIN IndustryLegalStructureInfo ilsi ON id.LegalStructureTypeId = ilsi.Id
47 INNER JOIN IndustryTypes ity ON id.IndustryTypeId = ity.Id
48 WHERE RegistrationDate >= @StartDate AND
49 RegistrationDate <= @EndDate
50 GROUP BY IndustryCategoryId,
51 IndustrySubCategoryId,
52 IndustryName
53
54 SELECT
55 IndustryName,
56 Industrialist,
57 [Address],
58 FixedCapital,
59 CurrentCapital,
60 TotalCapital,
61 MaleEmployeeCount,
62 FemaleEmployeeCount,
63 FemaleEntrepreneurCount,
64 FemaleEntrepreneurCount,
65 YearlyProductionCapacity,
66 Prafa,
67 Prali,
68 Safa,
69 Sana,
70 Gharelu,
71 ElectricalConsumption,
72 RegistrationNumber,
73 RegistrationDate,
74 IndustryPurpose,
75 IndustryState,
76 Class
77
78 FROM (
79 SELECT
80 IndustryCategoryId,
81 99999 AS IndustrySubCategoryId,
82 3 OrderId,
83 N'जम्मा ' + ic.Name IndustryName,
84 '' Industrialist,
85 '' [Address],
86 SUM(id.FixedCapital) AS FixedCapital,
87 SUM(id.CurrentCapital) AS CurrentCapital,
88 SUM(id.TotalCapital) AS TotalCapital,
89 SUM(MaleEmployeeCount) AS MaleEmployeeCount,
90 SUM(FemaleEmployeeCount) AS FemaleEmployeeCount,
91 SUM(MaleEntrepreneurCount) AS MaleEntrepreneurCount,
92 SUM(FemaleEntrepreneurCount) AS FemaleEntrepreneurCount,
93 SUM(YearlyProductionCapacity) AS YearlyProductionCapacity,
94 SUM(ElectricalConsumption) AS ElectricalConsumption,
95 '' AS RegistrationNumber,
96 null AS RegistrationDate,
97 '' AS IndustryPurpose,
98 '' AS IndustryState,
99 CAST(COUNT(id.Prafa) AS VARCHAR(10)) AS Prafa,
100 CAST(COUNT(id.Prali) AS VARCHAR(10)) AS Prali,
101 CAST(COUNT(id.Safa) AS VARCHAR(10)) AS Safa,
102 CAST(COUNT(id.Sana) AS VARCHAR(10)) AS Sana,
103 CAST(COUNT(id.Gharelu) AS VARCHAR(10)) AS Gharelu,
104 'categoryTotal' AS Class
105 FROM #temp id
106 INNER JOIN IndustryCategories ic ON id.IndustryCategoryId = ic.Id
107 GROUP BY IndustryCategoryId,
108 ic.Name
109
110 UNION
111
112 SELECT
113 id.IndustryCategoryId,
114 IndustrySubCategoryId,
115 2 OrderId,
116 N'जम्मा ' + isc.Name IndustryName,
117 '' Industrialist,
118 '' [Address],
119 SUM(id.FixedCapital) AS FixedCapital,
120 SUM(id.CurrentCapital) AS CurrentCapital,
121 SUM(id.TotalCapital) AS TotalCapital,
122 SUM(MaleEmployeeCount) AS MaleEmployeeCount,
123 SUM(FemaleEmployeeCount) AS FemaleEmployeeCount,
124 SUM(MaleEntrepreneurCount) AS MaleEntrepreneurCount,
125 SUM(FemaleEntrepreneurCount) AS FemaleEntrepreneurCount,
126 SUM(YearlyProductionCapacity) AS YearlyProductionCapacity,
127 SUM(ElectricalConsumption) AS ElectricalConsumption,
128 '' RegistrationNumber,
129 null AS RegistrationDate,
130 '' IndustryPurpose,
131 '' IndustryState,
132 CAST(COUNT(id.Prafa) AS VARCHAR(10)),
133 CAST(COUNT(id.Prali) AS VARCHAR(10)),
134 CAST(COUNT(id.Safa) AS VARCHAR(10)),
135 CAST(COUNT(id.Sana) AS VARCHAR(10)),
136 CAST(COUNT(id.Gharelu) AS VARCHAR(10)),
137 'subCategoryTotal' AS Class
138 FROM #temp id
139 INNER JOIN IndustrySubCategories isc ON id.IndustrySubCategoryId = isc.Id
140 GROUP BY id.IndustryCategoryId,
141 IndustrySubCategoryId,
142 isc.Name
143
144 UNION
145
146 SELECT * FROM #temp
147
148 UNION
149
150 SELECT
151 99999 AS IndustryCategoryId,
152 99999 AS IndustrySubCategoryId,
153 4 OrderId,
154 N'कुल जम्मा ' IndustryName,
155 '' Industrialist,
156 '' [Address],
157 SUM(id.FixedCapital),
158 SUM(id.CurrentCapital),
159 SUM(id.TotalCapital),
160 SUM(MaleEmployeeCount),
161 SUM(FemaleEmployeeCount),
162 SUM(MaleEntrepreneurCount),
163 SUM(FemaleEntrepreneurCount),
164 SUM(YearlyProductionCapacity),
165 SUM(ElectricalConsumption),
166 SUM(RegistrationNumber),
167 null AS RegistrationDate,
168 '' AS IndustryPurpose,
169 '' AS IndustryState,
170 CAST(COUNT(id.Prafa) AS VARCHAR(10)),
171 CAST(COUNT(id.Prali) AS VARCHAR(10)),
172 CAST(COUNT(id.Safa) AS VARCHAR(10)),
173 CAST(COUNT(id.Sana) AS VARCHAR(10)),
174 CAST(COUNT(id.Gharelu) AS VARCHAR(10)),
175 'grandTotal' AS Class
176 FROM #temp id) x
177 ORDER BY IndustryCategoryId, IndustrySubCategoryId, OrderId
178
179END
180
181GO