Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

using join in PIVOT Expand / Collapse
Author
Message
Posted Friday, February 14, 2014 10:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 11:31 AM
Points: 175, Visits: 351
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
Post #1541680
Posted Friday, February 14, 2014 11:53 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:21 PM
Points: 13,083, Visits: 11,918


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.



Nice job posting ddl and sample data. Of course your columns are NULL after 8/11. You don't have any data after that "date".

Can you explain what you want the output to be when there is no data to use?

You might also consider using a cross tab instead of a pivot. I find it a lot easier to work with and often faster than a PIVOT. You could also leverage a tally table so you don't have to hard code a whole months worth of data.


_______________________________________________________________

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 #1541695
Posted Friday, February 14, 2014 12:07 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 11:31 AM
Points: 175, Visits: 351
Hi Sean,

Thanks for your reply.

If the value is null then get the column form the pivot which is date and get the "daysinlate" data from table compaany based on the date and add the "daysinlate" data to the date colun of the pivot and display instead of null value.

ex :

on 2013-08-12 the value should be as "2013-08-27" = (2013-08-12 + 15 days)

select daysinlate from company which is 15 so add 15 days of column(2013-08-12 + 15) which is 2013-08-27 on the row.

am i explaining clear?
Post #1541698
Posted Friday, February 14, 2014 12:17 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:21 PM
Points: 13,083, Visits: 11,918
born2achieve (2/14/2014)
Hi Sean,

Thanks for your reply.

If the value is null then get the column form the pivot which is date and get the "daysinlate" data from table compaany based on the date and add the "daysinlate" data to the date colun of the pivot and display instead of null value.

ex :

on 2013-08-12 the value should be as "2013-08-27" = (2013-08-12 + 15 days)

select daysinlate from company which is 15 so add 15 days of column(2013-08-12 + 15) which is 2013-08-27 on the row.

am i explaining clear?


So if there is no value you want to instead count the number of days since there was a value? I don't understand what you want for output. It seems you want a value in the 2013-08-27 column? What value would it be? And it shows up in that column because that is the value of daysinlate? What happens if the calculation takes you past the end of reported data?


_______________________________________________________________

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 #1541701
Posted Friday, February 14, 2014 1:12 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 11:31 AM
Points: 175, Visits: 351
Hi Sean,

Thanks for your time on this. please execute the below query and please take a look at column(2013-08-12) the value is null.

I need to join the table data with company on(data.name = company.name) and get the value of daysinlate

as per my sample data the value is : 15 (consider 15 as 15days)

on the column(2013-08-12) intead of null i need to dispaly (2013-08-12 + 15 days = 2013-08-27). So the output would be

2013-08-11 2013-08-12 2013-08-13 2013-08-14 2013-08-15

1020 2013-08-27 2013-08-28 2013-08-29 2013-08-30

as like the calculation will go on.


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 ;



am i clear you now?
Post #1541738
Posted Friday, February 14, 2014 1:21 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 11:31 AM
Points: 175, Visits: 351
Please run this script to know the my desired output,

select 1000 as '2013-08-01', 1001 as '2013-08-02', 1002 as '2013-08-03', 1003 as '2013-08-04',1005 as '2013-08-05',1005 as '2013-08-06',
1006 as '2013-08-07',1007 as '2013-08-08',1004 as '2013-08-08',1033 as '2013-08-08',

1020 as '2013-08-11','2013-08-27' as '2013-08-12', '2013-08-28' as '2013-08-13', '2013-08-29' as '2013-08-14','2013-08-30' as '2013-08-15'

,'2013-08-31' as '2013-08-16', '2013-09-01' as '2013-08-17', '2013-09-02' as '2013-08-17','2013-09-03' as '2013-08-17'

formula : column value as date + 15 days
Post #1541743
Posted Friday, February 14, 2014 1:57 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:21 PM
Points: 13,083, Visits: 11,918
Is all of your data actually text? This is a very strange requirement to say the least.

_______________________________________________________________

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 #1541762
Posted Friday, February 14, 2014 2:08 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 11:31 AM
Points: 175, Visits: 351
yes, all my output data can be text. any help please
Post #1541768
Posted Friday, February 14, 2014 2:24 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:21 PM
Points: 13,083, Visits: 11,918
born2achieve (2/14/2014)
yes, all my output data can be text. any help please


I was hoping you would say no. It is much easier to do date math with actual dates.

I know this isn't a complete solution but here is a way to get most of it by using a tally table. You can read about tally tables here. http://www.sqlservercentral.com/articles/62867/

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 N, DATEADD(DAY, N - 1, '2013-08-01') as MyDate
from tally t
where t.N <= 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 MyNewValue
from MyDates md
left join #data d on d.DataReceived = md.MyDate



_______________________________________________________________

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 #1541775
Posted Friday, February 14, 2014 2:37 PM This worked for the OP Answer marked as solution
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 11:31 AM
Points: 175, Visits: 351
Hi Sean,

Thank you and if i execute the script i ma getting error as below. would like to anything wrong from my side

(3 row(s) affected)

(11 row(s) affected)
Msg 208, Level 16, State 1, Line 40
Invalid object name 'tally'.

Post #1541781
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse