Want to insert Blank instead of Null in main table

[sunny297]

Hi,

I want to insert data into table such that I get output something like...

Location  Id   UniqueNum     TotalEvents  RecordedDateTime           DataDatetime      LongestTime    AverageTime

LocA	13768	00061DF110406	100	2009-10-21 08:51:22	2009-10-21 09:53:16	01:01:54	00:50:00
LocA	11380	000886F810406	156	2009-10-21 09:18:53	2009-10-21 10:19:27	01:00:34	00:58:00
LocA	823	000886F910406	765	2009-10-21 09:44:09	2009-10-21 10:44:50	01:00:41	00:48:20
LocA	1302	000886FA10406	345	2009-10-21 09:59:23	2009-10-21 10:59:33	01:00:10	00:51:34
LocA Total	                1366			                                01:01:54	00:51:59

GRAND TOTAL                     4567                                                    09:55:09        19:09:45

In actual scenario I have multiple locations, and will be displayed in the same way as LocA.
Each location has no of Id's so each line summarises every Id, in the same way the Location Total row summarises each Location.
In the end i will have a GRAND TOTAL row which would be the summary of the Location Total rows.

Below is the code snippet ive used to obtain the above (not completely obtained what i want).

Function (getAvgTime)


CREATE FUNCTION dbo.getAvgTime
(
@AddTime INT
)
RETURNS NVARCHAR(100)
AS

BEGIN

declare @temp_mod1 nvarchar(100),@temp_mod2 nvarchar(100),@temp_mod3 nvarchar(100),@temp_mod4 nvarchar(100)
declare @Hours nvarchar(100), @Min nvarchar(100), @Sec nvarchar(100),@Min_temp nvarchar(100), @FinalTime nvarchar(100)

select @temp_mod1 = @AddTime/3600
set @Hours = @temp_mod1

select @temp_mod2 = @AddTime%3600
set @Min_temp = @temp_mod2

select @temp_mod3 = @temp_mod2/60
set @Min = @temp_mod3

select @temp_mod4 = @temp_mod2%60
set @Sec = @temp_mod4

select @FinalTime = @Hours + ':' + @Min + ':' + @Sec

RETURN (@FinalTime)
END

Code which calls the function

BEGIN

declare @i INT, @j INT, @location nvarchar(100), @id INT, @Total NVARCHAR(50)
declare @a NVARCHAR(100), @EventCount INT, @c NVARCHAR(100), @d NVARCHAR(100), @e NVARCHAR(100), @f NVARCHAR(100), @sql nvarchar(4000)
declare @EventCount2 INT, @LongestTime2 NVARCHAR(100), @AverageTime2 NVARCHAR(100), @g nvarchar(100)
declare @EventCount3 INT, @LongestTime3 NVARCHAR(100), @AverageTime3 NVARCHAR(100),@LocationTotal nvarchar(100), @LocationTotal2 nvarchar(100)
declare @RecordedDateTime nvarchar(100), @DataDateTime nvarchar(100), @FindTime1 INT, @FindTime2 INT, @AddTime INT, @FindTime3 INT
declare @FindTime4 INT, @LocationCount1 int, @LocationCount2 int, @AddTime2 int, @AddTime3 INT , @TimeinSec INT

create table #TEMP( Location NVARCHAR(100),
                    Id INT,
                    UniqueNum NVARCHAR(100),
                    Events INT,
                    RecordedDateTime DATETIME,
                    DataDateTime DATETIME,
                    LongestTime NVARCHAR(100),
                    AverageTime NVARCHAR(100),
                    TimeinSec INT
                  )

select distinct location
into #temploc
from dbo.Table1

SET @i = (select count(*) from #temploc)

while @i >0
begin

     select top 1 @location = depot from #temploc
     delete from #temploc where @location = location

     select distinct id into #tempid
     from dbo.Table1
     where @location = location

     set @j = (select count(*) from #tempid)

     while @j >0
     begin

          select top 1 @id = id from #tempid
          delete from #tempid where @id = id

          SELECT @a = UniqueNum FROM dbo.Table1 WHERE @Id = Id and @Location = Location
          SELECT @EventCount = count(Code) from dbo.Table1 where @Id= Id and @Location = Location
          SELECT @c = MAX(CONVERT(NVARCHAR,RecordedDateTime,120)) FROM dbo.Table1 WHERE @Id = Id and @Location = Location
          SELECT @d = MAX(CONVERT(NVARCHAR,DataDateTime,120)) FROM dbo.Table1 WHERE @Id = Id and @Location = Location
          SELECT @e = max(convert(nvarchar,DataDateTime - RecordedDateTime,108)) FROM dbo.Table1 WHERE @Id = Id and @Location = Location
          select @FindTime1 = (((SUM(Datepart(hh,RecordedDateTime)*60*60))+(SUM(Datepart(mm,RecordedDateTime)*60)) + (SUM(Datepart(ss,RecordedDateTime))))) from dbo.Table1 WHERE @Id = Id and @Location = Location
          select @FindTime2 = (((SUM(Datepart(hh,DataDateTime)*60*60))+(SUM(Datepart(mm,DataDateTime)*60)) + (SUM(Datepart(ss,DataDateTime))))) from dbo.Table1 where @Id = Id and @Location = Location
          select @TimeinSec = (@FindTime1 + @FindTime2)/@EventCount

          SELECT @f = dbo.getAvgTime(@TimeinSec)

          INSERT INTO #TEMP VALUES
          (@Location, @Id, @a, @EventCount, @c, @d, @e, @f, @TimeinSec)

          set @j = @j -1

     end

    drop table #tempid

    SELECT @LocationTotal = (@Location + ' Total')
    SELECT @LocationCount1 = COUNT(*) FROM #TEMP WHERE @Location = Location
    SELECT @EventCount2 = SUM(Events) FROM #TEMP WHERE @Location = Location
    SELECT @LongestTime2 = MAX(CONVERT(NVARCHAR,LongestTime,120)) FROM #TEMP WHERE @Location = Location
    SELECT @FindTime3 = SUM(@TimeinSec) FROM #TEMP WHERE @Location = Location
    select @AddTime2 = @Findtime3/@LocationCount1
    select @AverageTime2 = dbo.getAvgTime(@AddTime2)

    INSERT INTO #TEMP VALUES
    (@DepotTotal, null, null, @EventCount2, null, null, @LongestTime2, @AverageTime2,@AddTime2)

    set @i = @i -1

end

--drop table #tempdepot

SELECT @LocationTotal2 = 'GRAND TOTAL'
SELECT @LocationCount2 = COUNT(*) FROM #TEMP WHERE Location like '%Total%'
SELECT @EventCount3 = SUM(Events) FROM #TEMP WHERE Location like '%Total%'
SELECT @LongestTime3 = MAX(CONVERT(NVARCHAR,LongestTime,120)) FROM #TEMP WHERE Location like '%Total%'
SELECT @FindTime4 = SUM(TimeinSec) FROM #TEMP WHERE Location like '%Total%'
select @AddTime3 = @Findtime4/@DepotCount2
select @AverageTime3 = dbo.getAvgTime(@AddTime3)

INSERT INTO #TEMP VALUES
(@DepotTotal2, null, null, @EventCount3, null, null, @LongestTime3, @AverageTime3,@AddTime3)

Insert into dbo.MainTable
select Location,Id,UniqueNum, Events,RecordedDateTime ,DataDateTime ,LongestTime ,AverageTime
from #TEMP order by Location

end

What i am still missing...

1) When I insert the data into #TEMP table, the rows are being displayed in order ( i mean location names , then their total;s and so on). But in the last segment, when I insert data from Temp table into main table, the rows tumble ( i mean location names are not in order). How can i resolve it ??

2) I have inserted Null into #TEMP table where i do not want to display a value in Total and GRAND TOTAL rows...but in the final table output i want a BLANK space and not NULL to be displayed. How can this be achieved.

[sunny297]

Could anyone assist me here please..

Thanks.