CODEWITHSUNDEEP
sql-servert-sqlpivot
ironcurtain
ironcurtain

Reputation: 690

Exclude nulls from pivoted table

I have developed stored procedure as below. I would like to exclude the rows where in each inputdate colum value is null (if there is one input date which is not null, the row should stay). I have no idea how to do that. I serched the web but without any result. Appreciate Your help. Thanks.

create procedure [dbo].[sp_select_staff_time_inputs]
@startDate date,
@enddate date,
@teamid int,
@functionid int,
@servicefunctionid int

as

-- create variables, @columns = inputdates (columns of pivoted table)
DECLARE @columns NVARCHAR(2000)
declare @query nvarchar(4000)

select distinct inputdate into #temp_table_input_dates 
from timeinputs 
where inputdate >= @startDate and inputdate <= @enddate
order by inputdate

--select * from #temp_table_input_dates 

select @columns = isnull(@columns + ',', '') + '[' +  convert(varchar,convert(date,inputdate)) + ']' FROM #temp_table_input_dates

--select @columns

create table #temp_table_joins (
    soeid varchar(7),
    firstname varchar(50),
    lastname varchar(50),
    teamid int,
    team varchar(50),
    functionid int,
    function varchar(50),
    inputdate date,
    noofhours float,
    servicefunctionid int,
    servicefunction varchar(50),
    servicephaseid int,
    servicephase varchar(50)
    )

insert into #temp_table_joins
--select * into #temp_table_joins from 
SELECT 
u.SOEID, u.Firstname, u.Lastname, u.teamid,
t.team, t.functionid,
f.function,
ti.inputdate, ti.noofhours, ti.servicefunctionid,
sf.servicefunction, sf.servicephaseid,
sp. servicephase

from users u 
inner join teams t on u.teamid = t.teamid
inner join functions f on t.functionid = f.functionid
inner join timeinputs ti on u.userid = ti.userid
inner join servicefunctions sf on ti.servicefunctionid = sf.servicefunctionid
inner join servicephases sp on sf.servicephaseid = sp.servicephaseid

--select * from #temp_table_joins

set @query = 'select soeid, firstname, lastname, teamid, team, functionid,
    function, servicefunctionid, servicefunction, servicephaseid,
    servicephase' + @columns + ' from
(select * from #temp_table_joins
) p
pivot (sum (noofhours) for inputdate in (' + @columns + ')) as asd'

--select @query
--select *, noofhours, userid from timeinputs

execute(@query)

drop table #temp_table_joins
drop table #temp_table_input_dates 

GO

Upvotes: 0

Views: 1151

Answers (2)

ironcurtain
ironcurtain

Reputation: 690

Thank You valiik. Your solltion doesn't wok to me as expected but You inspired me to create my own.

Please see below code:

create procedure [dbo].[sp_select_staff_time_inputs]
@startDate date,
@enddate date,
@gvoteamid int,
@gvofunctionid int,
@servicefunctionid int

as

-- create variables, @columns = inputdates (columns of pivoted table)
DECLARE @columns NVARCHAR(2000)
declare @query nvarchar(4000)
DECLARE @columnscondition NVARCHAR(2000)

select distinct inputdate into #temp_table_input_dates 
from timeinputs 
where inputdate >= @startDate and inputdate <= @enddate
order by inputdate

--select * from #temp_table_input_dates 

select @columns = isnull(@columns + ',', '') + '[' +  convert(varchar,convert(date,inputdate)) + ']' FROM #temp_table_input_dates
select @columnscondition = isnull(@columnscondition + ' and ', '') + '[' +  convert(varchar,convert(date,inputdate)) + '] is not null' FROM #temp_table_input_dates

--SELECT @columnscondition


--select @columns

create table #temp_table_joins (
    soeid varchar(7),
    firstname varchar(50),
    lastname varchar(50),
    gvoteamid int,
    gvoteam varchar(50),
    gvofunctionid int,
    gvofunction varchar(50),
    inputdate date,
    noofhours float,
    servicefunctionid int,
    servicefunction varchar(50),
    servicephaseid int,
    servicephase varchar(50)
    )

insert into #temp_table_joins
--select * into #temp_table_joins from 
SELECT 
u.SOEID, u.Firstname, u.Lastname, u.gvoteamid,
t.gvoteam, t.gvofunctionid,
f.gvofunction,
ti.inputdate, ti.noofhours, ti.servicefunctionid,
sf.servicefunction, sf.servicephaseid,
sp. servicephase

from users u 
inner join gvoteams t on u.gvoteamid = t.gvoteamid
inner join gvofunctions f on t.gvofunctionid = f.gvofunctionid
inner join timeinputs ti on u.userid = ti.userid
inner join servicefunctions sf on ti.servicefunctionid = sf.servicefunctionid
inner join servicephases sp on sf.servicephaseid = sp.servicephaseid

--select * from #temp_table_joins

set @query = 'select soeid, firstname, lastname, gvoteamid, gvoteam, gvofunctionid,
    gvofunction, servicefunctionid, servicefunction, servicephaseid,
    servicephase, ' + @columns + ' from
(select * from #temp_table_joins where noofhours is not null
) p 

pivot (sum (noofhours) for inputdate in (' + @columns + ')) as asd
where (' + @columnscondition +')'

--select @query

--select @query
--select *, noofhours, userid from timeinputs

execute(@query)

drop table #temp_table_joins
drop table #temp_table_input_dates 



GO

Upvotes: 1

valiik
valiik

Reputation: 111

I'm not sure, but

DECLARE @columnscondition NVARCHAR(2000)
...
SELECT @columnscondition = isnull(@columns + ' AND ', ' WHERE ') + convert(varchar,convert(date,inputdate)) + ' IS NOT NULL ' FROM #temp_table_input_dates
...
set @query = 'select soeid, firstname, lastname, teamid, team, functionid,
    function, servicefunctionid, servicefunction, servicephaseid,
    servicephase' + @columns + ' from
(select * from #temp_table_joins
) p'
+ @columnscondition +
'pivot (sum (noofhours) for inputdate in (' + @columns + ')) as asd'

Perhaps, parentheses are missing in the dynamic part.

Upvotes: 1

Related Questions