February 14, 2014 at 10:47 am
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:
Name2013-08-012013-08-022013-08-032013-08-042013-08-052013-08-062013-08-072013-08-082013-08-092013-08-102013-08-112013-08-122013-08-132013-08-142013-08-152013-08-162013-08-172013-08-182013-08-192013-08-202013-08-212013-08-222013-08-232013-08-242013-08-252013-08-262013-08-272013-08-282013-08-292013-08-302013-08-31
microsoft10001001100210031005100510061007100410331020 2013-08-27 2013-08-282013-08-29 2013-08-302013-08-31 NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
like that have to apply adding days as formula whichever the values having null.
how to achieve this . any help
February 14, 2014 at 11:53 am
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 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 14, 2014 at 12:07 pm
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?
February 14, 2014 at 12:17 pm
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 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 14, 2014 at 1:12 pm
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?
February 14, 2014 at 1:21 pm
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
February 14, 2014 at 1:57 pm
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 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 14, 2014 at 2:08 pm
yes, all my output data can be text. any help please
February 14, 2014 at 2:24 pm
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/[/url]
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 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 14, 2014 at 2:37 pm
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'.
February 14, 2014 at 2:42 pm
if am not wrong that i need to create tally table?
February 14, 2014 at 2:45 pm
You're right. You need to create a tally or numbers table.
February 14, 2014 at 2:47 pm
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] as
WITH
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 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 14, 2014 at 2:59 pm
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.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;
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 MyNewValue
from MyDates md
left join #data d on d.DataReceived = md.MyDate
the "MyNewValue" data has to come in the place of null on recordscount column
also i need the output as pivotized. any help please
also 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
February 14, 2014 at 3:09 pm
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 MyNewValue
from MyDates md
left join #data d on d.DataReceived = md.MyDate
from your query the exptected output has to be below,
microsoft2013-08-01 00:00:00.0001000
microsoft2013-08-02 00:00:00.0001001
microsoft2013-08-03 00:00:00.0001002
microsoft2013-08-04 00:00:00.0001003
microsoft2013-08-05 00:00:00.0001005
microsoft2013-08-06 00:00:00.0001005
microsoft2013-08-07 00:00:00.0001006
microsoft2013-08-08 00:00:00.0001007
microsoft2013-08-09 00:00:00.0001004
microsoft2013-08-10 00:00:00.0001033
microsoft2013-08-11 00:00:00.0001020
NULL2013-08-12 00:00:00.0002013-08-27 00:00:00.000
NULL2013-08-13 00:00:00.0002013-08-28 00:00:00.000
NULL2013-08-14 00:00:00.0002013-08-29 00:00:00.000
NULL2013-08-15 00:00:00.0002013-08-30 00:00:00.000
NULL2013-08-16 00:00:00.0002013-08-31 00:00:00.000
NULL2013-08-17 00:00:00.0002013-09-01 00:00:00.000
NULL2013-08-18 00:00:00.0002013-09-02 00:00:00.000
NULL2013-08-19 00:00:00.0002013-09-03 00:00:00.000
NULL2013-08-20 00:00:00.0002013-09-04 00:00:00.000
NULL2013-08-21 00:00:00.0002013-09-05 00:00:00.000
NULL2013-08-22 00:00:00.0002013-09-06 00:00:00.000
NULL2013-08-23 00:00:00.0002013-09-07 00:00:00.000
NULL2013-08-24 00:00:00.0002013-09-08 00:00:00.000
NULL2013-08-25 00:00:00.0002013-09-09 00:00:00.000
NULL2013-08-26 00:00:00.0002013-09-10 00:00:00.000
NULL2013-08-27 00:00:00.0002013-09-11 00:00:00.000
NULL2013-08-28 00:00:00.0002013-09-12 00:00:00.000
NULL2013-08-29 00:00:00.0002013-09-13 00:00:00.000
NULL2013-08-30 00:00:00.0002013-09-14 00:00:00.000
NULL2013-08-31 00:00:00.0002013-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
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply