Need 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 2008 Forums

Transact-SQL (2008)

Need Help in Pivot

Author

Topic

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-02-14 : 13:55:36

Hi below is sample data for my issue.


with data as (
   
select 'microsoft' as Name, '2013-08-01' as datareceived, 1000 as recordscount union all
select 'microsoft' as Name, '2013-08-02' as datareceived, 1001 as recordscount union all
select 'microsoft' as Name, '2013-08-03' as datareceived, 1002 as recordscount union all
select 'microsoft' as Name, '2013-08-04' as datareceived, 1003 as recordscount union all
select 'microsoft' as Name, '2013-08-05' as datareceived, 1005 as recordscount union all
select 'microsoft' as Name, '2013-08-06' as datareceived, 1005 as recordscount union all
select 'microsoft' as Name, '2013-08-07' as datareceived, 1006 as recordscount union all
select 'microsoft' as Name, '2013-08-08' as datareceived, 1007 as recordscount union all
select 'microsoft' as Name, '2013-08-09' as datareceived, 1004 as recordscount union all
select 'microsoft' as Name, '2013-08-10' as datareceived, 1033 as recordscount union all
select 'microsoft' as Name, '2013-08-11' as datareceived, 1020 as recordscount )

SELECT * from
				 ( SELECT name,				 
				 [datareceived] ,recordscount
				   FROM  data				   
				   where DATEPART(MM, datareceived) = (8)  and DATEPART(yy, datareceived) = STR(2013)
				  ) 
				  as p PIVOT ( max([recordscount]) FOR [datareceived] 
							  IN ([2013-08-01],[2013-08-02],[2013-08-03],[2013-08-04],[2013-08-05],[2013-08-06],[2013-08-07],[2013-08-08],[2013-08-09],[2013-08-10],[2013-08-11],[2013-08-12],[2013-08-13],[2013-08-14],[2013-08-15],[2013-08-16],[2013-08-17],[2013-08-18],[2013-08-19],[2013-08-20],[2013-08-21],[2013-08-22],[2013-08-23],[2013-08-24],[2013-08-25],[2013-08-26],[2013-08-27],[2013-08-28],[2013-08-29],[2013-08-30],[2013-08-31])) AS pvt ;

the above query i tried to print from my original script.

Here is my original script:


declare @month int = 8
declare @year int = 2013
declare @cols nvarchar(4000); 							  
DECLARE @Query nvarchar(4000);						 

   with data as (
   
select 'microsoft' as Name, '2013-08-01' as datareceived, 1000 as recordscount union all
select 'microsoft' as Name, '2013-08-02' as datareceived, 1001 as recordscount union all
select 'microsoft' as Name, '2013-08-03' as datareceived, 1002 as recordscount union all
select 'microsoft' as Name, '2013-08-04' as datareceived, 1003 as recordscount union all
select 'microsoft' as Name, '2013-08-05' as datareceived, 1005 as recordscount union all
select 'microsoft' as Name, '2013-08-06' as datareceived, 1005 as recordscount union all
select 'microsoft' as Name, '2013-08-07' as datareceived, 1006 as recordscount union all
select 'microsoft' as Name, '2013-08-08' as datareceived, 1007 as recordscount union all
select 'microsoft' as Name, '2013-08-09' as datareceived, 1004 as recordscount union all
select 'microsoft' as Name, '2013-08-10' as datareceived, 1033 as recordscount union all
select 'microsoft' as Name, '2013-08-11' as datareceived, 1020 as recordscount )


select @cols =  Records.Output from (
			
			SELECT
	STUFF(DateString, 1, 1, '') as Output 
FROM
(   
	SELECT 
		',[' +  + CONVERT(VARCHAR(10), (DATEADD(DAY, Number, DATEADD(MONTH, @Month - 1, DATEADD(YEAR, @Year - 1900, '19000101')))), 121) + ']'
	FROM 
		master..spt_values 
	WHERE 
		type = 'P' 
		AND 
			DATEADD(DAY, Number, DATEADD(MONTH, 8 - 1, DATEADD(YEAR, @Year - 1900, '19000101')))
			<
			DATEADD(MONTH, 1, (DATEADD(MONTH, @Month - 1, DATEADD(YEAR, @Year - 1900, '19000101'))))
	ORDER BY
		DATEADD(DAY, Number, DATEADD(MONTH, @Month - 1, DATEADD(YEAR, @Year - 1900, '19000101')))
	FOR XML PATH('')
) AS T(DateString) ) Records

		set @Query = 'SELECT * from
				 (  SELECT name as [Company Name],				 
				 [datareceived] ,recordscount
				   FROM  data				   
				   where DATEPART(MM, datareceived) = ('+STR(@Month)+')  and DATEPART(yy, datareceived) = STR('+str(@Year)+')
				  ) 
				  as p PIVOT ( max([recordscount]) FOR [datareceived] 
							  IN ('+ @cols+')) AS pvt'; 		


exec @Query;

with company as (
select 'microsoft' as Name, 15 as daysinlate union all
select 'nokia' as name, 10 as daysinlate union all
select 'Google' as name, 13 as daysinlate)

select * from company;



if you execute the ready made query which is my first code block it will execute fine and after 2013-08-11 the recordscount values will null on the output.

my requirement is i need to get the daysinlate from table company based on the name and check for the null columns and get the column value and add as days and display the value.

I know i am little confused. as i said earlier after 2013-08-11 the recordscount values are null on the output of my query.

so my required out put is for 2013-08-12 the value is "2013-08-27" = (2013-08-12 + 15 days)
for 2013-08-13 the value is "2013-08-28" = (2013-08-12 + 15 days)
for 2013-08-14 the value is "2013-08-29" = (2013-08-12 + 15 days)
for 2013-08-15 the value is "2013-08-30" = (2013-08-12 + 15 days)
for 2013-08-16 the value is "2013-08-31" = (2013-08-12 + 15 days)



sample required output:

Name	2013-08-01	2013-08-02	2013-08-03	2013-08-04	2013-08-05	2013-08-06	2013-08-07	2013-08-08	2013-08-09	2013-08-10	2013-08-11	2013-08-12	2013-08-13	2013-08-14	2013-08-15	2013-08-16	2013-08-17	2013-08-18	2013-08-19	2013-08-20	2013-08-21	2013-08-22	2013-08-23	2013-08-24	2013-08-25	2013-08-26	2013-08-27	2013-08-28	2013-08-29	2013-08-30	2013-08-31
microsoft	1000	1001	1002	1003	1005	1005	1006	1007	1004	1033	1020	 2013-08-27  2013-08-28	2013-08-29 	2013-08-30	2013-08-31 	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL


like that have to apply adding days as formula whichever the values having null.

how to achieve this . any help

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-02-14 : 23:16:17

Here is my next step to make the pivot. but before making the pivot i am getting issue.

Query:


if OBJECT_ID('tempdb..#company') is not null
	drop table #company
	
create table #company
(
	Name varchar(20),
	DaysInLate int
)

insert #company
select 'microsoft' as Name, 15 as daysinlate union all
select 'nokia' as name, 10 as daysinlate union all
select 'Google' as name, 13 as daysinlate
		
if OBJECT_ID('tempdb..#data') is not null
	drop table #data		
		
create table #data
(
	Name varchar(20),
	DataReceived datetime,
	RecordsCount int
)

insert #data  
select 'microsoft' as Name, '2013-08-01' as datareceived, 1000 as recordscount union all
select 'microsoft' as Name, '2013-08-02' as datareceived, 1001 as recordscount union all
select 'microsoft' as Name, '2013-08-03' as datareceived, 1002 as recordscount union all
select 'microsoft' as Name, '2013-08-04' as datareceived, 1003 as recordscount union all
select 'microsoft' as Name, '2013-08-05' as datareceived, 1005 as recordscount union all
select 'microsoft' as Name, '2013-08-06' as datareceived, 1005 as recordscount union all
select 'microsoft' as Name, '2013-08-07' as datareceived, 1006 as recordscount union all
select 'microsoft' as Name, '2013-08-08' as datareceived, 1007 as recordscount union all
select 'microsoft' as Name, '2013-08-09' as datareceived, 1004 as recordscount union all
select 'microsoft' as Name, '2013-08-10' as datareceived, 1033 as recordscount union all
select 'microsoft' as Name, '2013-08-11' as datareceived, 1020 as recordscount;

with MyDates as
(
	select ID, DATEADD(DAY, ID - 1, '2013-08-01') as MyDate
	from dbo.TallyTable t
	where t.ID <= 31
)

select name,md.MyDate,case when DataReceived IS not null then null
else (select top 1 DATEADD(DAY, c.DaysInLate, md.MyDate) from #data d2 join #company c on d2.Name = c.Name where c.Name = 'microsoft') end as MyNewValue
from MyDates md
left join #data d on cast(d.DataReceived as date) = cast(md.MyDate as date)

getting output:


microsoft	2013-08-01 00:00:00.000	NULL
microsoft	2013-08-02 00:00:00.000	NULL
microsoft	2013-08-03 00:00:00.000	NULL
microsoft	2013-08-04 00:00:00.000	NULL
microsoft	2013-08-05 00:00:00.000	NULL
microsoft	2013-08-06 00:00:00.000	NULL
microsoft	2013-08-07 00:00:00.000	NULL
microsoft	2013-08-08 00:00:00.000	NULL
microsoft	2013-08-09 00:00:00.000	NULL
microsoft	2013-08-10 00:00:00.000	NULL
microsoft	2013-08-11 00:00:00.000	NULL
NULL	2013-08-12 00:00:00.000	2013-08-27 00:00:00.000
NULL	2013-08-13 00:00:00.000	2013-08-28 00:00:00.000
NULL	2013-08-14 00:00:00.000	2013-08-29 00:00:00.000
NULL	2013-08-15 00:00:00.000	2013-08-30 00:00:00.000
NULL	2013-08-16 00:00:00.000	2013-08-31 00:00:00.000
NULL	2013-08-17 00:00:00.000	2013-09-01 00:00:00.000
NULL	2013-08-18 00:00:00.000	2013-09-02 00:00:00.000
NULL	2013-08-19 00:00:00.000	2013-09-03 00:00:00.000
NULL	2013-08-20 00:00:00.000	2013-09-04 00:00:00.000
NULL	2013-08-21 00:00:00.000	2013-09-05 00:00:00.000
NULL	2013-08-22 00:00:00.000	2013-09-06 00:00:00.000
NULL	2013-08-23 00:00:00.000	2013-09-07 00:00:00.000
NULL	2013-08-24 00:00:00.000	2013-09-08 00:00:00.000
NULL	2013-08-25 00:00:00.000	2013-09-09 00:00:00.000
NULL	2013-08-26 00:00:00.000	2013-09-10 00:00:00.000
NULL	2013-08-27 00:00:00.000	2013-09-11 00:00:00.000
NULL	2013-08-28 00:00:00.000	2013-09-12 00:00:00.000
NULL	2013-08-29 00:00:00.000	2013-09-13 00:00:00.000
NULL	2013-08-30 00:00:00.000	2013-09-14 00:00:00.000
NULL	2013-08-31 00:00:00.000	2013-09-15 00:00:00.000

But my desired output should be


microsoft	2013-08-01 00:00:00.000	1000
microsoft	2013-08-02 00:00:00.000	1001
microsoft	2013-08-03 00:00:00.000	1002
microsoft	2013-08-04 00:00:00.000	1003
microsoft	2013-08-05 00:00:00.000	1005
microsoft	2013-08-06 00:00:00.000	1005
microsoft	2013-08-07 00:00:00.000	1006
microsoft	2013-08-08 00:00:00.000	1007
microsoft	2013-08-09 00:00:00.000	1004
microsoft	2013-08-10 00:00:00.000	1033
microsoft	2013-08-11 00:00:00.000	1020
NULL	2013-08-12 00:00:00.000	2013-08-27 00:00:00.000
NULL	2013-08-13 00:00:00.000	2013-08-28 00:00:00.000
NULL	2013-08-14 00:00:00.000	2013-08-29 00:00:00.000
NULL	2013-08-15 00:00:00.000	2013-08-30 00:00:00.000
NULL	2013-08-16 00:00:00.000	2013-08-31 00:00:00.000
NULL	2013-08-17 00:00:00.000	2013-09-01 00:00:00.000
NULL	2013-08-18 00:00:00.000	2013-09-02 00:00:00.000
NULL	2013-08-19 00:00:00.000	2013-09-03 00:00:00.000
NULL	2013-08-20 00:00:00.000	2013-09-04 00:00:00.000
NULL	2013-08-21 00:00:00.000	2013-09-05 00:00:00.000
NULL	2013-08-22 00:00:00.000	2013-09-06 00:00:00.000
NULL	2013-08-23 00:00:00.000	2013-09-07 00:00:00.000
NULL	2013-08-24 00:00:00.000	2013-09-08 00:00:00.000
NULL	2013-08-25 00:00:00.000	2013-09-09 00:00:00.000
NULL	2013-08-26 00:00:00.000	2013-09-10 00:00:00.000
NULL	2013-08-27 00:00:00.000	2013-09-11 00:00:00.000
NULL	2013-08-28 00:00:00.000	2013-09-12 00:00:00.000
NULL	2013-08-29 00:00:00.000	2013-09-13 00:00:00.000
NULL	2013-08-30 00:00:00.000	2013-09-14 00:00:00.000
NULL	2013-08-31 00:00:00.000	2013-09-15 00:00:00.000

any suggestion please

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2014-02-17 : 00:31:34

Not at a computer, and I wrote this to run the dates for each company, but just set it to Microsoft if you want company specific.


    Declare @Startdate datetime, @EndDate datetime

Set @startdate = '8/1/2013'
Set @enddate = '8/31/2013'

;WITH DateRange(Date) AS
    (
        SELECT
            @StartDate Date
        UNION ALL
        SELECT
            DATEADD(day, 1, Date) Date
        FROM
            DateRange
        WHERE
            Date < @EndDate
    )
    SELECT c.name,Date as mydate  ,case when b.name is null then DATEADD(DAY, c.DaysInLate, a.Date) else convert(datetime,0,101) end as newdate,b.recordscount
    FROM DateRange a
Cross join
Company c
Left join
#data b
On a.name = b.name
And a.date = b.datereceived
    OPTION (0);

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-17 : 07:32:45

Can i ask reason for this condition?
case when DataReceived IS not null then null

i think this is what is causing the values to be NULL for first set of records.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-02-17 : 09:33:59

hi vinnie and Visah thanks fr your reply. Here is my chaged code based on vinnie's sample.


if OBJECT_ID('tempdb..#company') is not null
	drop table #company
	
create table #company
(
	Name varchar(20),
	DaysInLate int
)

insert #company
select 'microsoft' as Name, 15 as daysinlate union all
select 'nokia' as name, 10 as daysinlate union all
select 'Google' as name, 13 as daysinlate
		
if OBJECT_ID('tempdb..#data') is not null
	drop table #data		
		
create table #data
(
	Name varchar(20),
	DataReceived datetime,
	RecordsCount int
)

insert #data  
select 'microsoft' as Name, '2013-08-01' as datareceived, 1000 as recordscount union all
select 'microsoft' as Name, '2013-08-02' as datareceived, 1001 as recordscount union all
select 'microsoft' as Name, '2013-08-03' as datareceived, 1002 as recordscount union all
select 'microsoft' as Name, '2013-08-04' as datareceived, 1003 as recordscount union all
select 'microsoft' as Name, '2013-08-05' as datareceived, 1005 as recordscount union all
select 'microsoft' as Name, '2013-08-06' as datareceived, 1005 as recordscount union all
select 'microsoft' as Name, '2013-08-07' as datareceived, 1006 as recordscount union all
select 'microsoft' as Name, '2013-08-08' as datareceived, 1007 as recordscount union all
select 'microsoft' as Name, '2013-08-09' as datareceived, 1004 as recordscount union all
select 'microsoft' as Name, '2013-08-10' as datareceived, 1033 as recordscount union all
select 'microsoft' as Name, '2013-08-11' as datareceived, 1020 as recordscount;

Declare @Startdate datetime, @EndDate datetime

Set @startdate = '8/1/2013'
Set @enddate = '8/31/2013'

;WITH DateRange(Date) AS
    (
        SELECT
            @StartDate Date
        UNION ALL
        SELECT
            DATEADD(day, 1, Date) Date
        FROM
            DateRange
        WHERE
            Date < @EndDate
    )
    
    
    
    SELECT c.name,Date as mydate  ,case when b.name is null then DATEADD(DAY, c.DaysInLate, a.Date) else convert(datetime,0,101) end as newdate,b.recordscount
    FROM DateRange a
Cross join
#company c
Left join
#data b
On a.Date = b.name
And a.date = b.DataReceived

but it says


Msg 241, Level 16, State 1, Line 6
Conversion failed when converting date and/or time from character string.

tried to find what causing the issue and i coudn't. any suggestions please

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2014-02-17 : 10:39:41

THat's what I get for not using a computer:)


if OBJECT_ID('tempdb..#company') is not null
	drop table #company
	
create table #company
(
	Name varchar(20),
	DaysInLate int
)

insert #company
select 'microsoft' as Name, 15 as daysinlate union all
select 'nokia' as name, 10 as daysinlate union all
select 'Google' as name, 13 as daysinlate
		
if OBJECT_ID('tempdb..#data') is not null
	drop table #data		
		
create table #data
(
	Name varchar(20),
	DataReceived datetime,
	RecordsCount int
)

insert #data  
select 'microsoft' as Name, '2013-08-01' as datareceived, 1000 as recordscount union all
select 'microsoft' as Name, '2013-08-02' as datareceived, 1001 as recordscount union all
select 'microsoft' as Name, '2013-08-03' as datareceived, 1002 as recordscount union all
select 'microsoft' as Name, '2013-08-04' as datareceived, 1003 as recordscount union all
select 'microsoft' as Name, '2013-08-05' as datareceived, 1005 as recordscount union all
select 'microsoft' as Name, '2013-08-06' as datareceived, 1005 as recordscount union all
select 'microsoft' as Name, '2013-08-07' as datareceived, 1006 as recordscount union all
select 'microsoft' as Name, '2013-08-08' as datareceived, 1007 as recordscount union all
select 'microsoft' as Name, '2013-08-09' as datareceived, 1004 as recordscount union all
select 'microsoft' as Name, '2013-08-10' as datareceived, 1033 as recordscount union all
select 'microsoft' as Name, '2013-08-11' as datareceived, 1020 as recordscount;

Declare @Startdate datetime, @EndDate datetime

Set @startdate = '8/1/2013'
Set @enddate = '8/31/2013'

;WITH DateRange(Date) AS
    (
        SELECT
            @StartDate as Date
        UNION ALL
        SELECT
            DATEADD(day, 1, Date) as Date
        FROM
            DateRange
        WHERE
            Date < @EndDate
    ) 
    
    SELECT c.name,Date as mydate  
	,case when b.name is null then DATEADD(DAY, c.DaysInLate, a.Date) else convert(datetime,0,101) end as newdate,b.recordscount
    FROM DateRange a
Cross join
#company c
Left join
#data b
On c.name = b.name
And a.date = b.DataReceived
order by name,date
option (maxrecursion 0)

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-02-17 : 11:30:58

It's Awesome Vinne and thank you so much for your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources