· 6 years ago · Jul 04, 2019, 06:52 AM
1IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[usp_IndustryRegistrationReport]') AND TYPE IN (N'P',N'PC'))
2 DROP PROCEDURE [dbo].usp_IndustryRegistrationReport
3GO
4
5SET ANSI_NULLS ON
6GO
7
8SET QUOTED_IDENTIFIER ON
9GO
10
11CREATE PROCEDURE dbo.usp_IndustryRegistrationReport
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 INTO #temp
44 FROM IndustryDetails id
45 INNER JOIN IndustryLegalStructureInfo ilsi ON id.LegalStructureTypeId = ilsi.Id
46 INNER JOIN IndustryTypes ity ON id.IndustryTypeId = ity.Id
47 WHERE RegistrationDate >= @StartDate AND
48 RegistrationDate <= @EndDate
49 GROUP BY IndustryCategoryId,
50 IndustrySubCategoryId,
51 IndustryName
52
53 SELECT
54 ROW_NUMBER() OVER(ORDER BY IndustryCategoryId, IndustrySubCategoryId, OrderId ASC) AS Id,
55 IndustryName,
56 Industrialist,
57 [Address],
58 FixedCapital,
59 CurrentCapital,
60 TotalCapital,
61 MaleEmployeeCount,
62 FemaleEmployeeCount,
63 MaleEntrepreneurCount,
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
77 FROM (
78 SELECT
79 IndustryCategoryId,
80 99999 AS IndustrySubCategoryId,
81 3 OrderId,
82 N'जम्मा ' + ic.Name IndustryName,
83 '' Industrialist,
84 '' [Address],
85 SUM(id.FixedCapital) AS FixedCapital,
86 SUM(id.CurrentCapital) AS CurrentCapital,
87 SUM(id.TotalCapital) AS TotalCapital,
88 SUM(MaleEmployeeCount) AS MaleEmployeeCount,
89 SUM(FemaleEmployeeCount) AS FemaleEmployeeCount,
90 SUM(MaleEntrepreneurCount) AS MaleEntrepreneurCount,
91 SUM(FemaleEntrepreneurCount) AS FemaleEntrepreneurCount,
92 SUM(YearlyProductionCapacity) AS YearlyProductionCapacity,
93 SUM(ElectricalConsumption) AS ElectricalConsumption,
94 '' AS RegistrationNumber,
95 null AS RegistrationDate,
96 '' AS IndustryPurpose,
97 '' AS IndustryState,
98 CAST(COUNT(id.Prafa) AS VARCHAR(10)) AS Prafa,
99 CAST(COUNT(id.Prali) AS VARCHAR(10)) AS Prali,
100 CAST(COUNT(id.Safa) AS VARCHAR(10)) AS Safa,
101 CAST(COUNT(id.Sana) AS VARCHAR(10)) AS Sana,
102 CAST(COUNT(id.Gharelu) AS VARCHAR(10)) AS Gharelu
103 FROM #temp id
104 INNER JOIN IndustryCategories ic ON id.IndustryCategoryId = ic.Id
105 GROUP BY IndustryCategoryId,
106 ic.Name
107
108 UNION
109
110 SELECT
111 id.IndustryCategoryId,
112 IndustrySubCategoryId,
113 2 OrderId,
114 N'जम्मा ' + isc.Name IndustryName,
115 '' Industrialist,
116 '' [Address],
117 SUM(id.FixedCapital) AS FixedCapital,
118 SUM(id.CurrentCapital) AS CurrentCapital,
119 SUM(id.TotalCapital) AS TotalCapital,
120 SUM(MaleEmployeeCount) AS MaleEmployeeCount,
121 SUM(FemaleEmployeeCount) AS FemaleEmployeeCount,
122 SUM(MaleEntrepreneurCount) AS MaleEntrepreneurCount,
123 SUM(FemaleEntrepreneurCount) AS FemaleEntrepreneurCount,
124 SUM(YearlyProductionCapacity) AS YearlyProductionCapacity,
125 SUM(ElectricalConsumption) AS ElectricalConsumption,
126 '' RegistrationNumber,
127 null AS RegistrationDate,
128 '' IndustryPurpose,
129 '' IndustryState,
130 CAST(COUNT(id.Prafa) AS VARCHAR(10)),
131 CAST(COUNT(id.Prali) AS VARCHAR(10)),
132 CAST(COUNT(id.Safa) AS VARCHAR(10)),
133 CAST(COUNT(id.Sana) AS VARCHAR(10)),
134 CAST(COUNT(id.Gharelu) AS VARCHAR(10))
135 FROM #temp id
136 INNER JOIN IndustrySubCategories isc ON id.IndustrySubCategoryId = isc.Id
137 GROUP BY id.IndustryCategoryId,
138 IndustrySubCategoryId,
139 isc.Name
140
141 UNION
142
143 SELECT * FROM #temp
144
145 UNION
146
147 SELECT
148 99999 AS IndustryCategoryId,
149 99999 AS IndustrySubCategoryId,
150 4 OrderId,
151 N'कुल जम्मा ' IndustryName,
152 '' Industrialist,
153 '' [Address],
154 SUM(id.FixedCapital),
155 SUM(id.CurrentCapital),
156 SUM(id.TotalCapital),
157 SUM(MaleEmployeeCount),
158 SUM(FemaleEmployeeCount),
159 SUM(MaleEntrepreneurCount),
160 SUM(FemaleEntrepreneurCount),
161 SUM(YearlyProductionCapacity),
162 SUM(ElectricalConsumption),
163 SUM(RegistrationNumber),
164 null AS RegistrationDate,
165 '' AS IndustryPurpose,
166 '' AS IndustryState,
167 CAST(COUNT(id.Prafa) AS VARCHAR(10)),
168 CAST(COUNT(id.Prali) AS VARCHAR(10)),
169 CAST(COUNT(id.Safa) AS VARCHAR(10)),
170 CAST(COUNT(id.Sana) AS VARCHAR(10)),
171 CAST(COUNT(id.Gharelu) AS VARCHAR(10))
172 FROM #temp id) x
173 ORDER BY IndustryCategoryId, IndustrySubCategoryId, OrderId
174
175END
176
177GO