SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Need Help in Pivot
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqllover
Constraint Violating Yak Guru

India
331 Posts

Posted - 02/14/2014 :  13:55:36  Show Profile  Reply with Quote
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;




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

India
331 Posts

Posted - 02/14/2014 :  23:16:17  Show Profile  Reply with Quote
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

Edited by - sqllover on 02/14/2014 23:17:56
Go to Top of Page

Vinnie881
Flowing Fount of Yak Knowledge

USA
1222 Posts

Posted - 02/17/2014 :  00:31:34  Show Profile  Reply with Quote
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

Edited by - Vinnie881 on 02/17/2014 00:33:39
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/17/2014 :  07:32:45  Show Profile  Reply with Quote
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
Go to Top of Page

sqllover
Constraint Violating Yak Guru

India
331 Posts

Posted - 02/17/2014 :  09:33:59  Show Profile  Reply with Quote
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
Go to Top of Page

Vinnie881
Flowing Fount of Yak Knowledge

USA
1222 Posts

Posted - 02/17/2014 :  10:39:41  Show Profile  Reply with Quote
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

Edited by - Vinnie881 on 02/17/2014 10:49:05
Go to Top of Page

sqllover
Constraint Violating Yak Guru

India
331 Posts

Posted - 02/17/2014 :  11:30:58  Show Profile  Reply with Quote
It's Awesome Vinne and thank you so much for your help
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000