February 17, 2014 at 7:36 am
Hi Sean/ Luis,
Any help please.
February 17, 2014 at 8:22 am
Will you always have your output in one line?
February 17, 2014 at 8:22 am
What part are you struggling with? It looks like you changed a couple of columns in the query that I posted last week. You should have all the data as far as I can tell. You might need to adjust it a little bit for formatting.
select N, MyDate, case when DataReceived IS not null then cast(RecordsCount as varchar(15)) else (select top 1 stuff(stuff(CONVERT(varchar, DATEADD(DAY, c.DaysInLate, md.MyDate), 112), 5, 0, '-'), 8, 0, '-') 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 d.DataReceived = md.MyDate
The only piece left is to PIVOT/Dynamic Cross tab the results. Your original post had a pivot so I assume you can handle that on your own?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 17, 2014 at 9:32 am
Hi Sean,
It's awesome and thank you so much for your great help. I am able to plot my Pivot. Cheers!!!
Hi Luis,
Thank you so much for your time on this.
February 17, 2014 at 9:36 am
born2achieve (2/17/2014)
Hi Sean,It's awesome and thank you so much for your great help. I am able to plot my Pivot. Cheers!!!
Hi Luis,
Thank you so much for your time on this.
Glad that worked you. If you have a few minutes can you post what your final solution was so that others who may stumble across this thread can see what your final solution was?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 17, 2014 at 10:16 am
Sure and i didn't tweak the pivot functionality as the data structure i gave is for testing purpose. but the logic will remains same. here is the full code.
use tempdb;
set nocount on;
if OBJECT_ID('dbo.TallyTable') is not null drop table dbo.TallyTable
go
select top 100 IDENTITY(int,1,1) as ID
into dbo.TallyTable from master.dbo.syscolumns
alter table dbo.TallyTable add constraint pk_tallytableId primary key clustered(ID)
GO
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 @cols nvarchar(4000);
DECLARE @Query nvarchar(4000);
with MyDates as
(
select ID, DATEADD(DAY, ID - 1, '2013-08-01') as MyDate
from dbo.TallyTable t
where t.ID <= 31
)
select @cols= Records.Output from
(
SELECT STUFF(
(
SELECT ',[' + CONVERT(VARCHAR(10), MyDate, 101) + ']' from MyDates
for xml path('')
), 1, 1, '') as Output) Records;
set @Query = 'SELECT * from
(
select Name, MyDate, case when DataReceived IS not null then cast(RecordsCount as varchar(15)) else (select top 1 stuff(stuff(CONVERT(varchar, DATEADD(DAY, c.DaysInLate, md.MyDate), 112), 5, 0, '-'), 8, 0, '-') 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 d.DataReceived = md.MyDate
)
as p PIVOT ( max([MyNewValue]) FOR [MyDate]
IN ('+ @cols+')) AS pvt';
EXEC sp_executesql @Query
.
If you have time could you please suggest me how can i make pivot using dynamic cross tabs. i ma aware of pivot but not dynamic cross tabs.
Thank you.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply