Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 using join in PIVOT Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, February 14, 2014 2:42 PM
 SSC Veteran Group: General Forum Members Last Login: Thursday, September 3, 2015 11:06 PM Points: 204, Visits: 474
 if am not wrong that i need to create tally table?
Post #1541782
 Posted Friday, February 14, 2014 2:45 PM
 SSCrazy Eights Group: General Forum Members Last Login: Yesterday @ 1:48 PM Points: 8,233, Visits: 17,809
 You're right. You need to create a tally or numbers table. Luis C.General Disclaimer:Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?How to post data/code on a forum to get the best help: Option 1 / Option 2
Post #1541783
 Posted Friday, February 14, 2014 2:47 PM
 SSCoach Group: General Forum Members Last Login: Tuesday, December 6, 2016 8:08 PM Points: 16,145, Visits: 16,850
 born2achieve (2/14/2014)if am not wrong that i need to create tally table?Yes. The article I referenced will explain not only how to build one but also how to use one. It will completely change the way you look at data.I actually have mine as a view instead of a permanent table. It is super duper fast and generates zero reads.`create View [dbo].[Tally] asWITH E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)), E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max cteTally(N) AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4 )select N from cteTally` _______________________________________________________________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 Moden's splitter.Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs Understanding and Using APPLY (Part 1)Understanding and Using APPLY (Part 2)
Post #1541784
 Posted Friday, February 14, 2014 2:59 PM
 SSC Veteran Group: General Forum Members Last Login: Thursday, September 3, 2015 11:06 PM Points: 204, Visits: 474
 Thank you Luis and Sean.Here is my try`use tempdb;set nocount on;if OBJECT_ID('dbo.TallyTable') is not null drop table dbo.TallyTablegoselect top 100 IDENTITY(int,1,1) as IDinto dbo.TallyTable from master.dbo.syscolumnsalter 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 #companyselect 'microsoft' as Name, 15 as daysinlate union allselect 'nokia' as name, 10 as daysinlate union allselect '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 allselect 'microsoft' as Name, '2013-08-02' as datareceived, 1001 as recordscount union allselect 'microsoft' as Name, '2013-08-03' as datareceived, 1002 as recordscount union allselect 'microsoft' as Name, '2013-08-04' as datareceived, 1003 as recordscount union allselect 'microsoft' as Name, '2013-08-05' as datareceived, 1005 as recordscount union allselect 'microsoft' as Name, '2013-08-06' as datareceived, 1005 as recordscount union allselect 'microsoft' as Name, '2013-08-07' as datareceived, 1006 as recordscount union allselect 'microsoft' as Name, '2013-08-08' as datareceived, 1007 as recordscount union allselect 'microsoft' as Name, '2013-08-09' as datareceived, 1004 as recordscount union allselect 'microsoft' as Name, '2013-08-10' as datareceived, 1033 as recordscount union allselect '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 *, case when DataReceived IS not null then DataReceived 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 MyNewValuefrom MyDates mdleft join #data d on d.DataReceived = md.MyDate`the "MyNewValue" data has to come in the place of null on recordscount columnalso i need the output as pivotized. any help pleasealso am confused about the result as my expected results was column should be as dates and row as recordscount and if recordcount is null then whatever we discussed about applying the formula to bring the dates
Post #1541789
 Posted Friday, February 14, 2014 3:09 PM
 SSC Veteran Group: General Forum Members Last Login: Thursday, September 3, 2015 11:06 PM Points: 204, Visits: 474
 Here is my next level of try`select name,case when DataReceived IS not null then RecordsCount 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 MyNewValuefrom MyDates mdleft join #data d on d.DataReceived = md.MyDate`from your query the exptected output has to be below,`microsoft 2013-08-01 00:00:00.000 1000microsoft 2013-08-02 00:00:00.000 1001microsoft 2013-08-03 00:00:00.000 1002microsoft 2013-08-04 00:00:00.000 1003microsoft 2013-08-05 00:00:00.000 1005microsoft 2013-08-06 00:00:00.000 1005microsoft 2013-08-07 00:00:00.000 1006microsoft 2013-08-08 00:00:00.000 1007microsoft 2013-08-09 00:00:00.000 1004microsoft 2013-08-10 00:00:00.000 1033microsoft 2013-08-11 00:00:00.000 1020NULL 2013-08-12 00:00:00.000 2013-08-27 00:00:00.000NULL 2013-08-13 00:00:00.000 2013-08-28 00:00:00.000NULL 2013-08-14 00:00:00.000 2013-08-29 00:00:00.000NULL 2013-08-15 00:00:00.000 2013-08-30 00:00:00.000NULL 2013-08-16 00:00:00.000 2013-08-31 00:00:00.000NULL 2013-08-17 00:00:00.000 2013-09-01 00:00:00.000NULL 2013-08-18 00:00:00.000 2013-09-02 00:00:00.000NULL 2013-08-19 00:00:00.000 2013-09-03 00:00:00.000NULL 2013-08-20 00:00:00.000 2013-09-04 00:00:00.000NULL 2013-08-21 00:00:00.000 2013-09-05 00:00:00.000NULL 2013-08-22 00:00:00.000 2013-09-06 00:00:00.000NULL 2013-08-23 00:00:00.000 2013-09-07 00:00:00.000NULL 2013-08-24 00:00:00.000 2013-09-08 00:00:00.000NULL 2013-08-25 00:00:00.000 2013-09-09 00:00:00.000NULL 2013-08-26 00:00:00.000 2013-09-10 00:00:00.000NULL 2013-08-27 00:00:00.000 2013-09-11 00:00:00.000NULL 2013-08-28 00:00:00.000 2013-09-12 00:00:00.000NULL 2013-08-29 00:00:00.000 2013-09-13 00:00:00.000NULL 2013-08-30 00:00:00.000 2013-09-14 00:00:00.000NULL 2013-08-31 00:00:00.000 2013-09-15 00:00:00.000`but it's not happening. please help me on this. after getting the above result then have to make the pivot for final result
Post #1541791
 Posted Monday, February 17, 2014 7:36 AM
 SSC Veteran Group: General Forum Members Last Login: Thursday, September 3, 2015 11:06 PM Points: 204, Visits: 474
Post #1542128
 Posted Monday, February 17, 2014 8:22 AM
 SSCrazy Eights Group: General Forum Members Last Login: Yesterday @ 1:48 PM Points: 8,233, Visits: 17,809
 Will you always have your output in one line? Luis C.General Disclaimer:Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?How to post data/code on a forum to get the best help: Option 1 / Option 2
Post #1542152
 Posted Monday, February 17, 2014 8:22 AM
 SSCoach Group: General Forum Members Last Login: Tuesday, December 6, 2016 8:08 PM Points: 16,145, Visits: 16,850
 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 MyNewValuefrom MyDates mdleft 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 Moden's splitter.Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs Understanding and Using APPLY (Part 1)Understanding and Using APPLY (Part 2)
Post #1542153
 Posted Monday, February 17, 2014 9:32 AM
 SSC Veteran Group: General Forum Members Last Login: Thursday, September 3, 2015 11:06 PM Points: 204, Visits: 474
 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.
Post #1542198
 Posted Monday, February 17, 2014 9:36 AM
 SSCoach Group: General Forum Members Last Login: Tuesday, December 6, 2016 8:08 PM Points: 16,145, Visits: 16,850
 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 Moden's splitter.Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs Understanding and Using APPLY (Part 1)Understanding and Using APPLY (Part 2)
Post #1542200

 Permissions