November 7, 2013 at 2:35 am
Hi Everyone
I have wrote a query as
SELECT PD.Name,PD.ContactNumber,Sum(cast(dbo.GetNumericValue(GI.AAY_Wheat)as numeric(18,0)))[Allocation],
'Week '+cast(DATEPART(wk, GI.EnteredOn)as varchar(50)) AS WeekNumber,
sum(cast(dbo.GetNumericValue(GI.AAY_Wheat)as numeric(18,0))) AS [Consumtion]
FROM PDS_GodownCallInfo GI
full join PDS_GodownAllocation GA on GA.ContactNumber=GI.ContactNumber
full join PDS_ShopOwnnerContactDetails PD on PD.ContactNumber=GI.ContactNumber
WHERE GI.EnteredOn >= '2013-01-01' AND GI.EnteredOn < '2013-11-30'
GROUP BY DATEPART(wk, GI.EnteredOn),PD.Name,PD.ContactNumber
and its output is as
Name ContactNumber Allocation WeekNumber Consumtion
NULL NULL 1485 Week 44 1485
Amit Singh 8091366307 220 Week 44 220
Anil Semwal 9418497722 1500 Week 44 1500
Rakesh Thakur 8091022334 220 Week 44 220
Rakesh Verma 8627811198 220 Week 44 220
NULL NULL 1445 Week 45 1445
Amit Singh 8091366307 1446 Week 45 1446
Rakesh Thakur 8091022334 0 Week 45 0
Samridh Dhawan 9805396622 45 Week 45 45
then I write a pivot query as below:
SELECT *
FROM (
SELECT PD.Name,PD.ContactNumber,Sum(cast(dbo.GetNumericValue(GI.AAY_Wheat)as numeric(18,0)))[Allocation],
'Week '+cast(DATEPART(wk, GI.EnteredOn)as varchar(50)) AS WeekNumber,
sum(cast(dbo.GetNumericValue(GI.AAY_Wheat)as numeric(18,0))) AS [Consumtion]
FROM PDS_GodownCallInfo GI
full join PDS_GodownAllocation GA on GA.ContactNumber=GI.ContactNumber
full join PDS_ShopOwnnerContactDetails PD on PD.ContactNumber=GI.ContactNumber
WHERE GI.EnteredOn >= '2013-01-01' AND GI.EnteredOn < '2013-11-30'
GROUP BY DATEPART(wk, GI.EnteredOn),PD.Name,PD.ContactNumber
) as data
PIVOT
(
sum(Consumtion)
FOR [WeekNumber] IN (week44,week45)
)AS p
and Its output is as bellow
Name ContactNumber Allocation week44 week45
NULL NULL 1445 NULL NULL
NULL NULL 1485 NULL NULL
Amit Singh 8091366307 220 NULL NULL
Amit Singh 8091366307 1446 NULL NULL
Anil Semwal 9418497722 1500 NULL NULL
Rakesh Thakur 8091022334 0 NULL NULL
Rakesh Thakur 8091022334 220 NULL NULL
Rakesh Verma 8627811198 220 NULL NULL
Samridh Dhawan 9805396622 45 NULL NULL
there is values for Consumtion is showing NULL.
can any one help me how can I fix the issue?
November 7, 2013 at 2:56 am
First thing to do is be consistent with what you are calling your weeks. They start odd as "Week 44" and "Week 45" but you're pivoting on "Week44" and "Week45".
_____________________________________________________________________
MCSA SQL Server 2012
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply