getting data in 2 rows inspite of 4

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

getting data in 2 rows inspite of 4

Author

Topic

ricky_1605
Starting Member

30 Posts

Posted - 2011-04-04 : 07:22:13

Hey guys i am attaching the code for a stored procedure.
I have the data of 2 project sites in a table. The table contains data of 2 months (January & February 2010) in this table. I have to create a report which shows the data in order of project site and months as columns of the table. Now when i run the below procedure i get data in 4 rows but i want this data in 2 rows. 2 rows are duplicate rows.

DECLARE @auxReportTable TABLE
(Nameofsite varchar(100), Region varchar(20),Business_unit varchar(20), Risk_index int default(0),
MH_JAN int default(0), F_JAN int default(0), R_JAN int default(0),MDH_JAN int default(0),MP_Staff_JAN int default(0),
MH_Staff_JAN int default(0), MP_Worker_JAN int default(0),MH_Worker_JAN int default(0), NM_JAN int default(0),
FA_JAN int default(0),MT_JAN int default(0), STAFF_JAN int default(0),STAFF_MH_JAN int default(0), TRAINING_JAN int default(0),
TRAINING_MH_JAN int default(0)
)

INSERT INTO @auxReportTable
(Nameofsite, Region, Business_unit, Risk_index, MH_JAN, F_JAN, R_JAN, MDH_JAN, MP_Staff_JAN, MH_Staff_JAN, MP_Worker_JAN,
MH_Worker_JAN, NM_JAN, FA_JAN, MT_JAN, STAFF_JAN,STAFF_MH_JAN, TRAINING_JAN, TRAINING_MH_JAN)

SELECT

Nameofsite = Nameofsite,
Region = Region,
Business_unit = Business_unit,
Risk_index = Risk_index,
MH_JAN = SUM(ISNULL(Company_Staff_manhours,0) + ISNULL(Dept_Workmen_manhours,0) + ISNULL(Sub_Workmen_manhours,0)),
F_JAN = SUM(ISNULL(Fatality,0)),
R_JAN = SUM(ISNULL(Lost_time,0)),
MDH_JAN = SUM(ISNULL(Mandays_lost_TM,0) + ISNULL(Mandays_lost_TM1,0) + ISNULL(Mandays_lost_TM2,0) + ISNULL(Mandays_lost_TM3,0) + ISNULL(Mandays_lost_TM4,0)),
MP_Staff_JAN = SUM(ISNULL(Company_Staff_no,0)),
MH_Staff_JAN = SUM(ISNULL(Company_Staff_manhours,0)),
MP_Worker_JAN = SUM(ISNULL(Dept_Workmen_no,0) + ISNULL(Sub_Workmen_no,0)),
MH_Worker_JAN = SUM(ISNULL(Dept_Workmen_manhours,0) + ISNULL(Sub_Workmen_manhours,0)),
NM_JAN = SUM(ISNULL(Near_Miss,0)),
FA_JAN = SUM(ISNULL(First_aid,0)),
MT_JAN = SUM(ISNULL(Medical_treatment,0)),
STAFF_JAN = SUM(ISNULL(Safety_Training_staff_No,0)),
STAFF_MH_JAN = SUM(ISNULL(Safety_Training_staff_manhrs,0)),
TRAINING_JAN = SUM(ISNULL(Training_workers_No,0)),
TRAINING_MH_JAN = SUM(ISNULL(Training_workers_manhrs,0))

FROM MONTHLY_REPORT

WHERE Month = 'January' and Year = @Year

GROUP BY Nameofsite, Region, Business_unit, Risk_index

ORDER BY Nameofsite

DECLARE @auxReportTable2 TABLE
(MH_FEB int default(0), F_FEB int default(0), R_FEB int default(0),MDH_FEB int default(0),MP_Staff_FEB int default(0),
MH_Staff_FEB int default(0), MP_Worker_FEB int default(0),MH_Worker_FEB int default(0), NM_FEB int default(0),
FA_FEB int default(0),MT_FEB int default(0), STAFF_FEB int default(0),STAFF_MH_FEB int default(0), TRAINING_FEB int default(0),
TRAINING_MH_FEB int default(0)
)

INSERT INTO @auxReportTable2
(MH_FEB, F_FEB, R_FEB, MDH_FEB, MP_Staff_FEB, MH_Staff_FEB, MP_Worker_FEB, MH_Worker_FEB, NM_FEB, FA_FEB, MT_FEB, STAFF_FEB,
STAFF_MH_FEB, TRAINING_FEB, TRAINING_MH_FEB)

EXEC Cumu_Report_FEB
@Year

Thank you

Nipun Chawla

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources