Want to insert Blank instead of Null in main table
Hi,
I want to insert data into table such that I get output something like...
Code:
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).
Code:
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
Code:
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.
