Need show the Result of this stored procedure Year wise in Pivot Format ?

Question

0.00/5 (No votes)

See more:

Dear All,

I need to show salevalue,Saleqty,ValueGrowth,QtyGrowth year wise using pivot.
Please go through the below procedure and let me know where I have done mistake.

SQL

ALTER PROCEDURE [dbo].[ssp_MasterList_Customer_New]

	@ZoneCode INT = NULL,
	@RCode INT = NULL,
	@SCode INT = NULL,
	@StoreType INT = NULL,
	@StoreCluster INT = NULL,
	@StoreBrand INT = NULL,
	@FDate DATETIME,
	@TDate DATETIME,
	@UserName VARCHAR(100) = NULL

AS

/*
   Created Date      :     04-DEC-2014
   Created By        :     VIJAYA KUMAR C
*/

BEGIN	
		
		SELECT @TDate = DATEADD(MM, 1, @TDate);
		
		SELECT  
				CList.CustomerCode,
				ISNULL(CList.CustomerName,'') AS CustomerName,
				ISNULL(CList.CustomerGroup,'') AS CustomerGroup,
				CList.AllowCredit,
				CList.TINNumber,
				CList.DiscountPercent,
				ISNULL(CList.AlternateCode,'') AS AlternateCode,
				ISNULL(CList.DoorDelivery,0) AS DoorDelivery,
				ISNULL(CList.CAAddress1,'') AS CAAddress1,
				ISNULL(CList.CAAddress2,'') AS CAAddress2,
				ISNULL(CList.CAAddress3,'') AS CAAddress3,
				ISNULL(CList.CAPincode,'') AS CAPincode,
				ISNULL(CList.CAAreaCode,'') AS CAAreaCode,					
				ISNULL(C.CityName,'') AS CACity,
				ISNULL(St.StateName,'') AS CAState,
				ISNULL(CN.CountryName,'') AS CACountry,
				ISNULL(CList.CALandLine,'') AS CALandLine,
				CList.CAMobile,
				CList.CAFax,
				CList.CAEmail,
				ISNULL(CList.BAPincode,'') AS BAPincode,				
				ISNULL(C1.CityName,'') AS BACity,
				ISNULL(St1.StateName,'') AS BAState,
				ISNULL(CN1.CountryName,'') AS BACountry,
				ISNULL(CList.BALandLine,'') AS BALandLine,
				CList.BAFax,				
				ISNULL(CList.BARemarks,'') AS BARemarks,				
				ISNULL(CList.DAAddressOption,'') AS DAAddressOption,
				ISNULL(CList.DAPincode,'') AS DAPincode,
				ISNULL(C2.CityName,'') AS DACity,
				ISNULL(St2.StateName,'') AS DAState,
				ISNULL(CN2.CountryName,'') AS DACountry,
				ISNULL(CList.DALandLine,'') AS DALandLine,
				CList.DAFax,				
				ISNULL(CList.DARemarks,'') AS DARemarks,					
				CList.IsActive,
				ISNULL(S1.StoreName,'') AS Store,
				ISNULL(R1.RegionName,'') AS Region,
				CASE ISNULL(CList.MaritalStatus,0) 
					WHEN 0 THEN 'Single' 
					WHEN 1 THEN 'Married' 
					WHEN 2 THEN 'Divorcee' 
					WHEN 3 THEN 'Widow/Widower' 
				END AS MaritalStatus,
				CASE
 					WHEN dobday IS NULL AND dobmonth IS NULL AND dobyear IS NULL THEN ISNULL(CONVERT(VARCHAR(4),dobyear),'')
 					WHEN dobday IS NULL AND dobmonth IS NULL AND dobyear IS NOT NULL THEN CONVERT(VARCHAR(4),dobyear)
 					WHEN dobday IS NULL AND dobmonth IS NOT NULL AND dobyear IS NULL THEN CONVERT(VARCHAR(2),dobmonth)
 					WHEN dobday IS NULL AND dobmonth IS NOT NULL AND dobyear IS NOT NULL THEN CONVERT(VARCHAR(2),dobmonth) + '/' + CONVERT(VARCHAR(4),dobyear)
 					WHEN dobday IS NOT NULL AND dobmonth IS NULL AND dobyear IS NULL THEN CONVERT(VARCHAR(2),dobday)
 					WHEN dobday IS NOT NULL AND dobmonth IS NULL AND dobyear IS NOT NULL THEN CONVERT(VARCHAR(2),dobday) + '/' + CONVERT(VARCHAR(4),dobyear)
 					WHEN dobday IS NOT NULL AND dobmonth IS NOT NULL AND dobyear IS NULL THEN CONVERT(VARCHAR(2),dobday) + '/' + CONVERT(VARCHAR(2),dobmonth)
 					WHEN dobday IS NOT NULL AND dobmonth IS NOT NULL AND dobyear IS NOT NULL THEN CONVERT(VARCHAR(2),dobday) + '/' + CONVERT(VARCHAR(2),dobmonth) + '/' + CONVERT(VARCHAR(4),dobyear)
 				END AS DateOfBirth,
				CASE WHEN ISNULL(CList.DateOfAnniversary,'') = '' THEN '' ELSE CONVERT(VARCHAR,CList.DateOfAnniversary,103) END DateOfAnniversary,
				ISNULL(CList.CreditLimit,0) AS CreditLimit,
				ISNULL(CList.CreditDays,0) AS CreditDays,
				ISNULL(CList.PaymentTerms,'') AS PaymentTerms,
				CList.ChequePostDateLimit,
				ISNULL(CList.TwitterID,'') AS [Twitter ID],
				ISNULL(CList.FacebookID,'') AS [Facebook ID],
				ISNULL(CList.AgreeToReceiveSMS,0) AS AgreeToReceiveSMS,
				ISNULL(CList.AgreeToReceiveEmail,0) AS AgreeToReceiveEmail,
				ISNULL(CList.TitleName,'') AS TitleName,
				ISNULL(CList.FirstName,'') AS FirstName,
				ISNULL(CList.MiddleName,'') AS MiddleName,
				ISNULL(CList.LastName,'') AS LastName,
				CASE WHEN ISNULL(CList.Gender,0) = 1 THEN 'Male' WHEN ISNULL(CList.Gender,0) = 2 THEN 'Female' ELSE '' END AS Gender,
				ISNULL(S.StoreName,'') AS BaseStore,
				CASE WHEN ISNULL(CList.CreatedDate,'') = '' THEN '' ELSE CONVERT(VARCHAR,CList.CreatedDate,103) END CreatedDate,
				ISNULL(CC.CLPDesc,'') AS [Default CLP Card],
				CASE WHEN ISNULL(CList.CAMobile,'') <> '' AND ISNULL(CList.CAEmail,'') <> '' THEN 'Penn Clique Member' ELSE '' END AS Member
		FROM
				dbo.Customers CList (NOLOCK)
				INNER JOIN dbo.Store S (NOLOCK) ON S.StoreCode = CList.CreatedAtStoreCode
				INNER JOIN dbo.Region R (NOLOCK) ON R.RegionCode = S.RegionCode				
				LEFT JOIN dbo.Store S1 (NOLOCK) ON S1.StoreCode = CList.StoreCode
				LEFT JOIN dbo.Region R1 (NOLOCK) ON R1.RegionCode = CList.RegionCode				
				LEFT JOIN dbo.City C (NOLOCK) ON C.CityCode = CList.CACity
				LEFT JOIN dbo.[State] St (NOLOCK) ON St.StateCode = CList.CAState
				LEFT JOIN dbo.Country CN (NOLOCK) ON CN.CountryCode = CList.CACountry
				LEFT JOIN dbo.City C1 (NOLOCK) ON C1.CityCode = CList.BACity
				LEFT JOIN dbo.[State] St1 (NOLOCK) ON St1.StateCode = CList.BAState
				LEFT JOIN dbo.Country CN1 (NOLOCK) ON CN1.CountryCode = CList.BACountry
				LEFT JOIN dbo.City C2 (NOLOCK) ON C2.CityCode = CList.DACity
				LEFT JOIN dbo.[State] St2 (NOLOCK) ON St2.StateCode = CList.DAState
				LEFT JOIN dbo.Country CN2 (NOLOCK) ON CN2.CountryCode = CList.DACountry
				LEFT JOIN CLPCardTrans CT (NOLOCK) ON CList.CustomerCode = CT.BillCustomerCode AND CONVERT(VARCHAR,CList.CustomerCode) = CT.BatchDescription
				LEFT JOIN CLPCards CC (NOLOCK) ON CC.CLPCode = CT.CLPCode
		WHERE
				(CList.CreatedDate BETWEEN @FDate AND @TDate) AND
				(R.ZoneCode = @ZoneCode OR @ZoneCode IS NULL) AND
				(R.RegionCode = @RCode OR @RCode IS NULL) AND
				(S.StoreCode = @SCode OR @SCode IS NULL) AND
				(S.StoreType = @StoreType OR @StoreType IS NULL) AND
				(S.StoreCluster = @StoreCluster OR @StoreCluster IS NULL) AND
				(S.StoreBrand = @StoreBrand OR @StoreBrand IS NULL)

		SELECT 
				1 AS CanExport
		FROM 
				dbo.Users U WITH (NOLOCK)
				INNER JOIN dbo.UserGroups UG WITH (NOLOCK) ON UG.GroupName = U.GroupName
		WHERE 
				(U.UserName = @UserName) AND (UG.Permission NOT LIKE '%,1454%')
	
		UNION
	
		SELECT 
				0 AS CanExport
		FROM	
				dbo.Users U WITH (NOLOCK)
				INNER JOIN dbo.UserGroups UG WITH (NOLOCK) ON UG.GroupName = U.GroupName
		WHERE	
				(U.UserName = @UserName) AND (UG.Permission LIKE '%,1454%')
				
END

Thanks in Advance

Posted 6-Jan-16 20:11pm

KishoreRoyal

Add a Solution

Comments

OriginalGriff 7-Jan-16 3:39am

This is not a good question - we cannot work out from that little what you are trying to do.
Remember that we can't see your screen, access your HDD, or read your mind.
So we can't run your SP as we don't have the data, we don't know what the input data looks like, what output you get, or what output you want.
And just dumping a long SP on us and saying "fix this" is rather rude.
Stop and think. Think about input that demos the problem, and a smaller, simpler chunk of SQL that gives an equivalent output without us having to wade through loads of SQL to work out what is happening.
Then show us the input, the SQL, the output and the output you want. Otherwise, you may be waiting a fair time for any useful response.
Use the "Improve question" widget to edit your question and provide better information.

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2016-01-06T21:57:00

Please, read OriginalGriff's comment to the question.

On the other side... It's quite simple. Have a look at example:

SQL

DECLARE @tmp TABLE(ClientId INT, SomeDate DATETIME, SomeValue INT)

INSERT INTO @tmp (ClientId, SomeDate, SomeValue)
VALUES(1, '2011-01-01', 100),(1, '2011-02-01', 110),
(1, '2012-02-01', 120), (1, '2012-03-01', 130),
(1, '2013-03-01', 140), (1, '2013-04-01', 140),
(1, '2014-04-21', 160), (1, '2014-05-01', 150),
(1, '2015-05-21', 180), (1, '2015-06-01', 170),
(2, '2012-10-01', 800), (2, '2012-11-11', 80),
(2, '2013-09-01', 1000), (2, '2013-10-01', 100),
(2, '2014-08-01', 1200), (2, '2014-09-01', 120),
(2, '2015-07-01', 1400), (2, '2015-08-01', 140)

SELECT ClientId, [2011], [2012], [2013], [2014], [2015]
FROM (
	SELECT ClientId, YEAR(SomeDate) AS YearOfSomeDate, SomeValue
	FROM @tmp
	) AS DT
PIVOT(SUM(SomeValue) FOR YearOfSomeDate IN([2011], [2012], [2013], [2014], [2015])) AS PT

Result:

ClientId	2011	2012	2013	2014	2015
1			210		250		280		310		350
2			NULL	880		1100	1320	1540

For dynamic version of pivoted columns, please use Google[^].

Need show the Result of this stored procedure Year wise in Pivot Format ?

1 solution

Solution 1

Add your solution here

Preview 0

Need show the Result of this stored procedure Year wise in Pivot Format ?

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us