Reports for 4 week dates - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Reports for 4 week dates

Author

Topic

naresh0407
Starting Member

30 Posts

Posted - 2010-11-08 : 09:25:22

Hi All,

How to get headers dynamically based on passed date to stored procedure.

If i pass date 8/22/2010 then my rsult header should be

8/1/2010 8/8/2010 8/15/2010 8/22/2010 i.e last 4 weeks header.

I will pass sunday date for example(8/22/2010) based on this i need to get reports for last weeks.Say if the user selects 8/22/2010 which is Sunday then my result should for last 4 weeks 1st,2,3 and 4th week.

Suppose if i dont have data for 3rd week i.e 8/15/2010 then i should get value as 0.

Just give me an idea on how to get header dynamically in rsult like shown below.User will pass sunday date(8/22/2010) i need to get reports for last 4 weeks even if i dont have data for particular week say 8/15/2010 i should get header date and corresponding value as 0.

Scehduled Hrs 8/1/2010 8/8/2010 8/15/2010 8/22/2010
<24 55 44 0 56
>24 45 56 100 44

naresh0407
Starting Member

30 Posts

Posted - 2010-11-08 : 09:29:55

This is my query which is pivot format which i am trying to convert it into normal stored procedure.

Reports is to find no of employees whose scehduled hours is less than 24,b/w 24 and 48 and greater than 48 hrs for 1st week,2nd week,3rd week and 4th week.User will pass Sunday date say 8/22/2010 then reports should be like this

Scehduled Hrs 8/1/2010 8/8/2010 8/15/2010 8/22/2010

Data

ALTER PROCEDURE [dbo].[SAR_Sp_GetSchedule] (@date1 DATETIME, @date2 DATETIME, @date3 DATETIME, @date4 DATETIME,@CampaignID INT,@TotalEmployee int)
AS
DECLARE @query VARCHAR(MAX)
BEGIN
SET @query = 'SELECT '+ CHAR(39) + 'Greater than 44 Hrs' + CHAR(39) + ' AS HeadCount, [' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']' +
'FROM
(SELECT WeekStartDate,
convert(numeric(4,0),dbo.FDiv(Count(Case When Totalhours>=44.00 Then EmpNo end),'+cast(@TotalEmployee as varchar)+')*100) as empno
FROM SAR_WeekData where CampaignID=' + CAST(@CampaignID AS VARCHAR) + ' group by WeekStartDate) AS SourceTable
PIVOT
(
max(empno)
FOR WeekStartDate IN (' + '[' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']' + ')
) AS PivotTable
UNION
SELECT ' + CHAR(39) + 'a Less Than 25.00' + CHAR(39) + ' AS TotalHours, [' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']' +
'FROM
(SELECT WeekStartDate,
convert(numeric(4,0),dbo.FDiv(Count(Case When Totalhours<=25.00 then EmpNo end),'+cast(@TotalEmployee as varchar)+')*100) as empno
FROM SAR_WeekData where CampaignID=' + CAST(@CampaignID AS VARCHAR) + ' group by WeekStartDate) AS SourceTable
PIVOT
(
max(empno)
FOR WeekStartDate IN (' + '[' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']' + ')
) AS PivotTable
union
SELECT ' + CHAR(39) + 'b/w 25.10 and 30.00' + CHAR(39) + ' AS TotalHours, [' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']' +
'FROM
(SELECT WeekStartDate,
convert(numeric(4,0),dbo.FDiv(Count(Case When Totalhours>25.00 and Totalhours<=30.00 then EmpNo end),'+cast(@TotalEmployee as varchar)+')*100)-
convert(numeric(4,0),dbo.FDiv(Count(Case When Totalhours<=25.00 then EmpNo end),'+cast(@TotalEmployee as varchar)+')*100)
as empno
FROM SAR_WeekData where CampaignID=' + CAST(@CampaignID AS VARCHAR) + ' group by WeekStartDate) AS SourceTable
PIVOT
(
max(empno)
FOR WeekStartDate IN (' + '[' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']' + ')
) AS PivotTable
union
SELECT ' + CHAR(39) + 'b/w 30.10 and 44.00' + CHAR(39) + ' AS TotalHours, [' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']' +
'FROM
(SELECT WeekStartDate,
convert(numeric(4,0),dbo.FDiv(Count(Case When Totalhours>=30.00 and Totalhours<44.00 then EmpNo end),'+cast(@TotalEmployee as varchar)+')*100)-
(convert(numeric(4,0),dbo.FDiv(Count(Case When Totalhours<=25.00 then EmpNo end),'+cast(@TotalEmployee as varchar)+')*100)+
convert(numeric(4,0),dbo.FDiv(Count(Case When Totalhours>25.00 and Totalhours<=30.00 then EmpNo end),'+cast(@TotalEmployee as varchar)+')*100))
as empno
FROM SAR_WeekData where CampaignID=' + CAST(@CampaignID AS VARCHAR) + ' group by WeekStartDate) AS SourceTable
PIVOT
(
max(empno)
FOR WeekStartDate IN (' + '[' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']' + ')
) AS PivotTable';
Exec(@query)
end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources