August 21, 2009 at 8:53 am
Dear Friends,
I want to display a blank row between each group. Is this possible in the below Query:
select pa.cellshiftname + '('+ CONVERT(CHAR(8), sa.shiftstartdate, 112) + ' ' + left(CONVERT(CHAR(12), sa.shiftstartdate, 114),5)
+'-'+ CONVERT(CHAR(8), sa.shiftenddate, 112) + ' ' + left(CONVERT(CHAR(12), sa.shiftenddate, 114),5)+')' ,
b.description,
a.description ,
cast(round(cast(sum((pa.qtyin)/puc.factor)*puc2.factor as float),2) as varchar)+ '(' + cu.countunitname + ')',
cast(round(cast(sum((pa.qtyout)/puc.factor)*puc2.factor as float),2) as varchar)+ '(' + cu.countunitname + ')',
cast(round(cast(sum((pa.qtyprocessed)/puc.factor)*puc2.factor as float),2) as varchar)+ '(' + cu.countunitname + ')'
from vprodaudit pa inner join shiftaudit sa on pa.cellshiftauditid=sa.auditid
inner join asset a on pa.auditassetname = a.assetname
inner join productunitconversion puc on pa.productid=puc.productid and a.countunitid=puc.countunitid
inner join countunit cu on a.countunitid=cu.countunitid
left outer join asset b with (nolock) on a.parentassetid=b.assetid
left outer join cellgroupcell cgc on b.assetid=cgc.assetid
left outer join cellgroup cg on cg.cellgroupid=cgc.cellgroupid
inner join productunitconversion puc2 on pa.productid=puc2.productid and b.countunitid=puc2.countunitid
where (pa.qtyin <>0 or pa.qtyout <>0 or pa.qtyprocessed <> 0)
AND pa.audittime between '20090813' AND '20090822'
AND isnull(pa.cellassetid,' ') =15
--AND isnull(pa.cellcrewName,' ') LIKE ?Crewname
--AND isnull(cg.cellgroupid,' ') LIKE ?cellGroupid
and pa.cellassetid<>pa.auditassetid
group by b.description,
a.description,
puc.factor,puc2.factor,cu.countunitname,
pa.cellshiftname,sa.shiftstartdate,sa.shiftenddate
order by sa.shiftstartdate desc
This gives me a result as:
DAY(20090821 09:28-99990101 00:00)Sarvesh Production LineFiller200(ROLLS)200(ROLLS)100(ROLLS)
DAY(20090821 09:28-99990101 00:00)Sarvesh Production LinePacker0(PACKS)0(PACKS)200(PACKS)
DAY(20090820 14:49-20090821 09:00)Sarvesh Production LineFiller400(ROLLS)300(ROLLS)200(ROLLS)
PM(20090820 11:45-20090820 14:49)Sarvesh Production LineFiller400(ROLLS)300(ROLLS)200(ROLLS)
MVI Morning(20090820 09:00-20090820 11:45)Sarvesh Production LineFiller200(ROLLS)150(ROLLS)300(ROLLS)
MVI Morning(20090819 09:00-20090819 20:00)Sarvesh Production LineFiller10(ROLLS)10(ROLLS)20(ROLLS)
i want a result as
DAY(20090821 09:28-99990101 00:00)Sarvesh Production LineFiller200(ROLLS)200(ROLLS)100(ROLLS)
DAY(20090821 09:28-99990101 00:00)Sarvesh Production LinePacker0(PACKS)0(PACKS)200(PACKS)
DAY(20090820 14:49-20090821 09:00)Sarvesh Production LineFiller400(ROLLS)300(ROLLS)200(ROLLS)
PM(20090820 11:45-20090820 14:49)Sarvesh Production LineFiller400(ROLLS)300(ROLLS)200(ROLLS)
MVI Morning(20090820 09:00-20090820 11:45)Sarvesh Production LineFiller200(ROLLS)150(ROLLS)300(ROLLS)
MVI Morning(20090819 09:00-20090819 20:00)Sarvesh Production LineFiller10(ROLLS)10(ROLLS)20(ROLLS)
Is this possible?
Many thanks.
August 21, 2009 at 9:24 am
What is your overall goal? is this for reporting purposes?
Chris Fitzgerald 😀
August 21, 2009 at 9:56 am
yes it is for reporting purpose, i am using this as an xml document which is being called by CGML.
CGML is limited as far as formatting data is concerned, so trying to resolve it in SQL.
August 21, 2009 at 10:17 am
SQL is limited in this respect too. Sorry.
The sample code below will do what you want, after a fashion. But I don't recommend this at all. Formatting is better done in the calling application and once you start down this path, you will waste a lot of time trying to get SQL to do things against its nature. Good luck.
declare @sample table (rowID int identity(1,1), data char(10))
insert into @sample
select 'apple' union all
select 'orange' union all
select 'pear' union all
select 'apple' union all
select 'orange' union all
select 'pear' union all
select 'apple' union all
select 'orange' union all
select 'pear' union all
select 'apple' union all
select 'orange' union all
select 'pear'
with cte1 as
(select data,rowid,max('') as filler from @sample
group by data,rowID
with rollup)
select case when rowID is null then '' else data end as data,
case when rowID is null then '' else cast(rowID as varchar(10)) end as rowID
from cte1
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply