help in pivot - 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

SQL Server 2005 Forums

Transact-SQL (2005)

help in pivot

Author

Topic

abcd
Yak Posting Veteran

92 Posts

Posted - 2009-03-04 : 02:47:19

SELECT

LabourId
,LabourName
,LabourCode
,isnull([1],0) as [1]
,isnull([2],0) as [2]
,isnull([3],0) as [3]
,isnull([4],0) as [4]
,isnull([5],0) as [5]
,isnull([6],0) as [6]
,isnull([7],0) as [7]
,isnull(

,0) as

,isnull([9],0) as [9]
,isnull([10],0) as [10]
,isnull([11],0) as [11]
,isnull([12],0) as [12]
,isnull([13],0) as [13]
,isnull([14],0) as [14]
,isnull([15],0) as [15]
,isnull([16],0) as [16]
,isnull([17],0) as [17]
,isnull([18],0) as [18]
,isnull([19],0) as [19]
,isnull([20],0) as [20]
,isnull([21],0) as [21]
,isnull([22],0) as [22]
,isnull([23],0) as [23]
,isnull([24],0) as [24]
,isnull([25],0) as [25]
,isnull([26],0) as [26]
,isnull([27],0) as [27]
,isnull([28],0) as [28]
,isnull([29],0) as [29]
,isnull([30],0) as [30]
,isnull([31],0) as [31]
from
(
SELECT

LAttenDetails.LabourId
,LMaster.LabourName
,LMaster.LabourCode
,LAttenDetails.IsABsent
,Day(Date) 'LDAy'
FROM
Labour_AttendenceDetails as LAttenDetails
Left Join Labour_Master as LMaster on LAttenDetails.LabourId=LMaster.LabourId
WHERE month(date)=02 AND Year(DAte)=2009 AND LAttenDetails.LabourId=10
) as Data
PIVOT(
Max(IsAbsent)
FOR LDAY IN([1],[2],[3],[4],[5],[6],[7],

,[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31])
) AS PVT

Order BY LabourId

select * from Labour_AttendenceDetails
where labourid=10 and month(date)=2

output generated by the query---
LabourId LabourName LabourCode
10 RANJEET SBPL/E27/008 10 RANJEET SBPL/E27/008 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0.5 0 0 1 1 1 1 1 0 0 0 0 0 0 0
Sno LabourId IsAbsent date CreatedOn CreatedBy IsActive
----------- ----------- ---------------------- ----------------------- ----------------------- ----------- --------
5541 10 0 2009-02-02 00:00:00.000 2009-02-03 10:39:47.500 41 1
5604 10 0 2009-02-03 00:00:00.000 2009-02-04 10:52:20.313 41 1
5667 10 0 2009-02-04 00:00:00.000 2009-02-05 11:07:59.780 41 1
5731 10 0 2009-02-05 00:00:00.000 2009-02-06 10:37:51.220 41 1
5795 10 0 2009-02-06 00:00:00.000 2009-02-07 10:46:14.327 41 1
5859 10 0 2009-02-07 00:00:00.000 2009-02-09 10:48:52.093 41 1
5923 10 0 2009-02-09 00:00:00.000 2009-02-10 10:54:57.233 41 1
6051 10 0 2009-02-10 00:00:00.000 2009-02-11 10:38:01.687 41 1
6115 10 0 2009-02-11 00:00:00.000 2009-02-12 10:50:41.547 41 1
6179 10 0 2009-02-12 00:00:00.000 2009-02-13 10:39:21.517 41 1
6243 10 0 2009-02-13 00:00:00.000 2009-02-24 10:34:24.250 41 1
6307 10 0 2009-02-14 00:00:00.000 2009-02-16 10:34:14.483 41 1
6371 10 0 2009-02-16 00:00:00.000 2009-02-17 10:40:58.720 41 1
6565 10 0 2009-02-18 00:00:00.000 2009-02-19 11:10:47.860 41 1
6630 10 1 2009-02-21 00:00:00.000 2009-02-24 11:00:29.280 41 1
6696 10 1 2009-02-24 00:00:00.000 2009-02-25 10:23:12.827 41 1
7221 10 0 2009-02-25 00:00:00.000 2009-03-03 10:56:18.827 30 1
7419 10 0.5 2009-02-17 00:00:00.000 2009-03-03 13:25:31.593 41 1
7484 10 0 2009-02-08 00:00:00.000 2009-03-03 15:52:43.657 41 1
7810 10 0 2009-02-15 00:00:00.000 2009-03-03 16:16:31.437 41 1
8466 10 0 2009-02-28 00:00:00.000 2009-03-03 16:40:52.937 41 1
8532 10 0 2009-02-27 00:00:00.000 2009-03-03 16:46:22.720 41 1
8664 10 0 2009-02-26 00:00:00.000 2009-03-03 16:48:08.983 41 1
8730 10 1 2009-02-23 00:00:00.000 2009-03-03 16:49:13.327 41 1
8796 10 1 2009-02-22 00:00:00.000 2009-03-03 16:49:30.657 41 1
8928 10 1 2009-02-20 00:00:00.000 2009-03-03 16:50:59.797 41 1
8994 10 0 2009-02-19 00:00:00.000 2009-03-03 16:51:16.640 41 1

(27 row(s) affected)

Now the problem is ,how to display records in month date which are not in database,,,
Suppose my query is having [1]...[31 ] checking fields,,,Now if month is february i want to display output as null under[29],[30],[31] there will be no data in february month on these days...
hope i am able to clarify my problem...
do help..its urgent

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2009-03-04 : 03:04:41

In Below query, I assumed that your date format is mm/dd/yyyy and month and year taken as static values. and i concatenated in that format. change it as per your requirements.

SELECT

LabourId
,LabourName
,LabourCode
,isnull([1],0) as [1]
,isnull([2],0) as [2]
,isnull([3],0) as [3]
,isnull([4],0) as [4]
,isnull([5],0) as [5]
,isnull([6],0) as [6]
,isnull([7],0) as [7]
,isnull(

,0) as

,isnull([9],0) as [9]
,isnull([10],0) as [10]
,isnull([11],0) as [11]
,isnull([12],0) as [12]
,isnull([13],0) as [13]
,isnull([14],0) as [14]
,isnull([15],0) as [15]
,isnull([16],0) as [16]
,isnull([17],0) as [17]
,isnull([18],0) as [18]
,isnull([19],0) as [19]
,isnull([20],0) as [20]
,isnull([21],0) as [21]
,isnull([22],0) as [22]
,isnull([23],0) as [23]
,isnull([24],0) as [24]
,isnull([25],0) as [25]
,isnull([26],0) as [26]
,isnull([27],0) as [27]
,isnull([28],0) as [28]
,case when isdate( '02/' + cast( 29 as varchar) + '/2009') = 0 then null
else isnull([29],0) end as [29]
,case when isdate( '02/' + cast( 30 as varchar) + '/2009') = 0 then null
else isnull([30],0) end as [30]
,case when isdate( '02/' + cast( 31 as varchar) + '/2009') = 0 then null
else isnull([31],0) end as [31]

from
(
SELECT

LAttenDetails.LabourId
,LMaster.LabourName
,LMaster.LabourCode
,LAttenDetails.IsABsent
,Day(Date) 'LDAy'
FROM
Labour_AttendenceDetails as LAttenDetails
Left Join Labour_Master as LMaster on LAttenDetails.LabourId=LMaster.LabourId
WHERE month(date)=02 AND Year(DAte)=2009 AND LAttenDetails.LabourId=10
) as Data
PIVOT(
Max(IsAbsent)
FOR LDAY IN([1],[2],[3],[4],[5],[6],[7],

abcd
Yak Posting Veteran

92 Posts

Posted - 2009-03-04 : 03:12:25

thnks for ur wonderful suggestion....
But now i am getting a new problem...if i put month(date) as 1 then also the query is producing same result in 29,30,31 as null....
do help...

abcd
Yak Posting Veteran

92 Posts

Posted - 2009-03-04 : 04:09:16

please do help...
i did what u suggested ...
but how to change my query if dont want to hard code my month ...
Something as i create a procedure by use of this query and pass two parameters as monthid,yearid??

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-03-04 : 04:43:23

There is a easy way out -


SELECT

LabourId
,LabourName
,LabourCode
,[1],[2],[3],[4],[5],[6],[7],,[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31]
from
(
SELECT 

LAttenDetails.LabourId
,LMaster.LabourName
,LMaster.LabourCode
,ISNULL(LAttenDetails.IsABsent,0) AS IsABsent,Day(Date) 'LDAy'
FROM
Labour_AttendenceDetails as LAttenDetails
Left Join Labour_Master as LMaster on LAttenDetails.LabourId=LMaster.LabourId
WHERE month(date)=02 AND Year(DAte)=2009 AND LAttenDetails.LabourId=10 
) as Data
PIVOT(
Max(IsAbsent)
FOR LDAY IN([1],[2],[3],[4],[5],[6],[7],,[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31])
) AS PVT

Order BY LabourId

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

abcd
Yak Posting Veteran

92 Posts

Posted - 2009-03-04 : 05:04:33

thank you all of you...
i got my problem solved....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources