I have my sP output as given below:
Audit_Id Audit_Name Audit_CreatedDate 6.2 6.3 6.2.1 6.2.2
1 abc 1/1/2007 Pass PassYes No
2 abc 1/1/2007 Pass Fail Yes No
3 abc 1/1/2007 Pass PassYes No
4 abc 1/1/2007 Pass Fail Yes No
5 abc 1/1/2007 Pass Fail Yes No
What i need is this way
Audit_Id Audit_Name Audit_CreatedDate 6.2 6.3 6.2.1 6.2.2 Passcount
1 abc 1/1/2007 Pass Pass Yes No 2
2 abc 1/1/2007 Pass Fail Yes No 1
3 abc 1/1/2007 Pass Pass Yes No 2
4 abc 1/1/2007 Pass Fail Yes No 1
5 abc 1/1/2007 Pass Fail Pass No 2
Similarly i need FailCount, Yes Count, Nocount as few more columns.
The query for the first table output is this way..
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[VerificationSummaryReport_TEST] '1/1/2007','3/28/2007','ONBOARD'
-- Add the parameters for the stored procedure here
@.FromDate datetime,@.ToDate datetime,@.VerificationType varchar(15)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
select AD.audit_id, AD.Audit_Name, SM.Shortcode 'shortcode_name', CM.Campaign_Name , CM.Shortcode_Owner 'brand_name', SM.Operator_Name,
E.first_name+' '+E.last_name 'employee_name',AD.Type_of_Service, AD.SignUp,
AD.Delivery, AD.Price, AD.Unitofpurchase,AD.Audit_createdDate,
--case when DE.Status_criteria = 'PASS' then count(*) else '0' end as 'PassCount',
max(case when DE.criteria_id = 1 then DE.Status_criteria else '-' end) as '6.2',
max(case when DE.criteria_id = 2 then DE.Status_criteria else '-' end) as '6.2.1',
max(case when DE.criteria_id = 3 then DE.Status_criteria else '-' end) as '6.2.2',
max(case when DE.criteria_id = 4 then DE.Status_criteria else '-' end) as '6.3',
max(case when DE.criteria_id = 5 then DE.Status_criteria else '-' end) as '6.3.1',
max(case when DE.criteria_id = 6 then DE.Status_criteria else '-' end) as '6.4',
max(case when DE.criteria_id = 7 then DE.Status_criteria else '-' end) as '6.4.1',
max(case when DE.criteria_id = 8 then DE.Status_criteria else '-' end) as '6.4.3',
max(case when DE.criteria_id = 9 then DE.Status_criteria else '-' end) as '6.4.2',
max(case when DE.criteria_id = 10 then DE.Status_criteria else '-' end) as '3.1',
max(case when DE.criteria_id = 11 then DE.Status_criteria else '-' end) as '3.1.1',
max(case when DE.criteria_id = 12 then DE.Status_criteria else '-' end) as '3.1.2',
max(case when DE.criteria_id = 14 then DE.Status_criteria else '-' end) as '3.2',
max(case when DE.criteria_id = 15 then DE.Status_criteria else '-' end) as '3.2.1',
max(case when DE.criteria_id = 16 then DE.Status_criteria else '-' end) as '3.2.2',
max(case when DE.criteria_id = 19 then DE.Status_criteria else '-' end) as '4.6',
max(case when DE.criteria_id = 20 then DE.Status_criteria else '-' end) as '4.6.2',
max(case when DE.criteria_id = 21 then DE.Status_criteria else '-' end) as '4.6.3',
max(case when DE.criteria_id = 22 then DE.Status_criteria else '-' end) as '4.6.4',
max(case when DE.criteria_id = 23 then DE.Status_criteria else '-' end) as '4.7',
max(case when DE.criteria_id = 24 then DE.Status_criteria else '-' end) as '4.7.2',
max(case when DE.criteria_id = 25 then DE.Status_criteria else '-' end) as '4.8',
max(case when DE.criteria_id = 26 then DE.Status_criteria else '-' end) as '4.8.1',
max(case when DE.criteria_id = 27 then DE.Status_criteria else '-' end) as '4.9',
max(case when DE.criteria_id = 28 then DE.Status_criteria else '-' end) as '4.9.2',
max(case when DE.criteria_id = 29 then DE.Status_criteria else '-' end) as '7.1',
max(case when DE.criteria_id = 30 then DE.Status_criteria else '-' end) as '7.1.1',
max(case when DE.criteria_id = 31 then DE.Status_criteria else '-' end) as '7.2',
max(case when DE.criteria_id = 32 then DE.Status_criteria else '-' end) as '7.2.1',
max(case when DE.criteria_id = 36 then DE.Status_criteria else '-' end) as '5.10',
max(case when DE.criteria_id = 37 then DE.Status_criteria else '-' end) as '5.10.1',
max(case when DE.criteria_id = 38 then DE.Status_criteria else '-' end) as '5.5',
max(case when DE.criteria_id = 39 then DE.Status_criteria else '-' end) as '5.5.1',
max(case when DE.criteria_id = 41 then DE.Status_criteria else '-' end) as '5.5.2',
max(case when DE.criteria_id = 42 then DE.Status_criteria else '-' end) as '5.6',
max(case when DE.criteria_id = 43 then DE.Status_criteria else '-' end) as '5.6.1',
max(case when DE.criteria_id = 44 then DE.Status_criteria else '-' end) as '5.6.2',
max(case when DE.criteria_id = 45 then DE.Status_criteria else '-' end) as '5.7',
max(case when DE.criteria_id = 46 then DE.Status_criteria else '-' end) as '5.7.1',
max(case when DE.criteria_id = 47 then DE.Status_criteria else '-' end) as '5.9',
max(case when DE.criteria_id = 48 then DE.Status_criteria else '-' end) as '5.9.1',
max(case when DE.criteria_id = 49 then DE.Status_criteria else '-' end) as '5.9.2',
max(case when DE.criteria_id = 51 then DE.Status_criteria else '-' end) as '1.3',
max(case when DE.criteria_id = 60 then DE.Status_criteria else '-' end) as '8.2',
max(case when DE.criteria_id = 66 then DE.Status_criteria else '-' end) as '9.3',
max(case when DE.criteria_id = 67 then DE.Status_criteria else '-' end) as '9.3.1',
max(case when DE.criteria_id = 68 then DE.Status_criteria else '-' end) as '9.3.2',
max(case when DE.criteria_id = 69 then DE.Status_criteria else '-' end) as '10.1',
max(case when DE.criteria_id = 70 then DE.Status_criteria else '-' end) as '10.1.1',
max(case when DE.criteria_id = 71 then DE.Status_criteria else '-' end) as '10.1.2',
max(case when DE.criteria_id = 72 then DE.Status_criteria else '-' end) as '10.5',
max(case when DE.criteria_id = 73 then DE.Status_criteria else '-' end) as '10.5.1',
max(case when DE.criteria_id = 74 then DE.Status_criteria else '-' end) as '10.5.2',
max(case when DE.criteria_id = 75 then DE.Status_criteria else '-' end) as '10.6',
max(case when DE.criteria_id = 76 then DE.Status_criteria else '-' end) as '10.6.1',
max(case when DE.criteria_id = 77 then DE.Status_criteria else '-' end) as '10.7',
max(case when DE.criteria_id = 78 then DE.Status_criteria else '-' end) as '10.7.1',
max(case when DE.criteria_id = 79 then DE.Status_criteria else '-' end) as '6.4.4'
--, case when DE.Status_criteria = 'PASS' then Count(*) else 0 end as 'PASSCOUNT'
from dbo.Audit_Details AD INNER JOIN
dbo.Data_Evaluation DE ON DE.Audit_Id = AD.Audit_ID INNER JOIN
dbo.ShortCode_Master SM ON SM.ShortCode_Id = AD.Shortcode_Id INNER JOIN
dbo.Campaign_Master CM ON CM.Campaign_Id = AD.Campaign_Id INNER JOIN
dbo.Employee E ON E.Emp_Id = AD.DE_empid
where AD.present_auditstate = 'AS' and AD.verificationtype = @.VerificationType AND AD.STATUS = 'ACTIVE'
AND AD.audit_createdDate between @.FromDate and --'3/29/2007'
REPLACE(CONVERT(CHAR(10),DATEADD(day, 1,@.ToDate),110),'-','/')
group by AD.audit_id, AD.shortcode_id, AD.campaign_id, AD.Audit_Name, SM.Shortcode, CM.Campaign_Name , CM.Shortcode_Owner, SM.Operator_Name,
E.first_name,E.last_name ,AD.Type_of_Service, AD.SignUp,
AD.Delivery, AD.Price, AD.Unitofpurchase,AD.Audit_createdDate--, DE.Status_criteria
--order by AD.audit_id
END
Please Help me out in solving this problem. Thanks in advance
Moving to T-SQL forum.
Mike
|||sum(case when DE.Status_criteria = 'PASS' then 1 else 0 end) as 'PASSCOUNT'
No comments:
Post a Comment