· 6 years ago · Jul 04, 2019, 06:54 AM
1string sb = "IF 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" +
3"GO" +
4" " +
5"SET ANSI_NULLS ON" +
6"GO" +
7" " +
8"SET QUOTED_IDENTIFIER ON" +
9"GO" +
10" " +
11"CREATE PROCEDURE dbo.usp_IndustryRegistrationReport" +
12" @StartDate DATETIME," +
13" @EndDate DATETIME" +
14"AS" +
15"BEGIN" +
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" " +
175"END" +
176" " +
177"GO";