June 1, 2010 at 12:31 pm
Hi,
I want to insert data into table such that I get output something like...
Location Id UniqueNum TotalEvents RecordedDateTime DataDatetime LongestTime AverageTime
LocA1376800061DF1104061002009-10-21 08:51:222009-10-21 09:53:1601:01:5400:50:00
LocA11380000886F8104061562009-10-21 09:18:532009-10-21 10:19:2701:00:3400:58:00
LocA823000886F9104067652009-10-21 09:44:092009-10-21 10:44:5001:00:4100:48:20
LocA1302000886FA104063452009-10-21 09:59:232009-10-21 10:59:3301:00:1000:51:34
LocA Total 1366 01:01:5400: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.
June 1, 2010 at 12:47 pm
1) You must sort your output or use an index on your MainTable.
2) Use ISNULL() or COALESCE to handle the null value.
SELECT ISNULL(Events,'') FROM #TEMP -- produces a blank if Events is null.
June 1, 2010 at 12:53 pm
If you would provide table definition, sample data and expected result in a ready to use format as decribed in the first link in my signature I'm sure there will be a number of people trying to replace your while loop with a set based solution...
At a first glance I couldn't spot anything that wouldn't be able to do set based.
June 1, 2010 at 1:09 pm
Thanks for the reply Steve. Will try it out.
@Lutz - Please find the table definations and expected output as given below.
-- Table from which data is imported for calculations
CREATE table dbo.Table1
(
Events INT,
Item CHAR(13),
Location NVARCHAR(100),
RecordedDateTime DATETIME,
DataDateTime DATETIME,
Id INT,
UniqueNum NVARCHAR(100)
)
-- Temporary table which would hold data from calculations
CREATE table dbo.Table1
(
Location NVARCHAR(100),
Id INT,
UniqueNum NVARCHAR(100),
RecordedDateTime DATETIME,
DataDateTime DATETIME,
LongestTime NVARCHAR(100),
AverageTime NVARCHAR(100),
TimeinSec INT
)
-- Main table into which final values are exported from Temporary table excpet for last column - TimeinSec which is excluded from MainTable.
**Same as Temp table
The expected output is something like given below....
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
Hope it helps.
June 1, 2010 at 1:20 pm
Now, all that's still missing are some sample data (in an INSERT INTO SELECT format) to play with that would match your expected result so we have something to test against.
So, we're almost ready to start 😉
June 1, 2010 at 1:26 pm
Lutz here you go...
-- Sample data for Inserted into the table from which data is imported i.e. Table1
insert into Table1 values (100, 'YU345678910TH' , 'LocA' ,'2009-10-21 08:51:22','2009-10-21 09:53:16' , 13768,'00061DF11')
insert into Table1 values(156, 'YU346678910TH' , 'LocA' ,'2009-10-21 09:18:53','2009-10-21 10:19:27' , 11380,'000886F81')
insert into Table1 values(765, 'YU347678910TH' , 'LocA' ,'2009-10-21 09:44:09','2009-10-21 10:44:50' , 13768,'000886F91')
-- Data for Insertion into #TEMP during Inner While Loop
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)
-- Data inserted into #TEMP during Outer While Loop
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)
I want show BLANK in the fields in the table where NULL values are being stored
-- Data for insertion after both the WHILE LOOPS
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)
I want show BLANK in the fields in the table where NULL values are being stored
-- Final insertion into Main Table from #TEMP Table
Insert into dbo.MainTable
select Location,Id,UniqueNum, Events,RecordedDateTime ,DataDateTime ,LongestTime ,AverageTime
from #TEMP order by Location
When data is inserted here, the rows are coming as jumbled in the table i.e. not in order even after using the ORDER BY clause ablove
Hope it helps.
June 1, 2010 at 1:54 pm
Unfortunately, your sample data and expected output doesn't really match and there was some fine tuning required to get your sample data inserted (wrong column order).
But here's what I came up with based on the data I have (excluding LongestTime,
AverageTime, and TimeinSec since those columns either don't make sense based on the sample data or the column is not part of the final output...)
SELECT
location,
Id,
UniqueNum,
TotalEvents,
RecordedDateTime ,
DataDateTime
FROM
(
SELECT
0 AS pos1,
1 AS pos2,
Location ,
CAST(Id AS CHAR(5)) AS Id ,
UniqueNum,
Events AS TotalEvents,
RecordedDateTime ,
DataDateTime
FROM table1
UNION ALL
SELECT
0 AS pos1,
2 AS pos2,
Location ,
'Total' ,
'',
SUM(Events),
'' ,
''
FROM table1
GROUP BY location
UNION ALL
SELECT
1 AS pos1,
1 AS pos2,
'Grand' ,
'Total' ,
'',
SUM(Events),
'' ,
''
FROM table1
) t
ORDER BY pos1,Location, pos2
June 1, 2010 at 3:07 pm
I believe you don't need that table at all.
What you need is a properly formatted query with GROUPING and ROLLUP.
If you look up BOL for these key words you'll find a complete explanation with good examples.
_____________
Code for TallyGenerator
June 1, 2010 at 3:21 pm
Sergiy (6/1/2010)
I believe you don't need that table at all.What you need is a properly formatted query with GROUPING and ROLLUP.
If you look up BOL for these key words you'll find a complete explanation with good examples.
That was my initial thought too. But then I stumbled across the requirement to add MAX(LongestTime) and AVG(AverageTime) to the ROLLUP row. I have no idea how to add that info so I decided to go for the UNION ALL version (even though I didn't include the aggregation due to suspect sample/result data).
June 2, 2010 at 1:07 am
Thanks for the reply Lutz. Ive got some more info for you to better understand the Final table columns as shown below...
-- Desc for input columns in Table1..
Events - no of events occuring on every Id for a Location
Item - Unique item no
Location - location name
RecordedDateTime- Timestamp at which the event was scanned ( format : '2010-05-06 09:12:22')
DataDateTime - Timestamp at which the event reached database ( format : '2010-05-06 09:12:22')
Id - Unique Id which scans items at every Location
UniqueNum - Unique num for every Id
-- Desc for columns in Resultant table..
Location (NVARCHAR)- Same as Above
Id (INT)- Same as above
UniqueNum (NVARCHAR)- Same as above
EventCount (INT)- Total count of Events for a particualar Id in particualr Location
RecordedDateTime (DATETIME)- Max RecordedDateTime for a particualar Id in particualr Location
DataDateATime (DATETIME)- Max DataDateATime for a particualar Id in particualr Location
LongestTime (NVARCHAR# HH:MM:SS format)- This is calculated by subtracting (DataDateATime - RecordedDateTime) for all Events and then get the MAX of the resultant for a particualar Id in particualr Location
AverageTime (NVARCHAR# HH:MM:SS format)- This is the most tricky calculation.. I need to add the time fields of RecordedDateTime and DataDateTime seperately and then together for all Events and then divide the sum by the EventCount for a particualar Id in particualr Location.
To achieve the AverageTime, I have added a seperated col "TimeinSec" in #TEMP table, as im converting the HH:MM:SS to seconds and then doing all the calculation.
And in the end calling Function getAvgTime() to convert the result in seconds to HH:MM:SS format to be displayed in AveragrTime field.
In summary what I need to get is...
1) For every Location, there are many Id's so my 1st requirement is to summarise for every Id which exists for that Location.
2) Once we have Location level Id summary rows, we need to summarise the Location as a whole then (represented by Location Total row)
3) Step 2 is repeated for all Locations.
4) Once all Location level sumamries are obtained, I need one GRAND TOTAL row which would summarise all the Location Total rows.
Cheers,
Sanchit
June 4, 2010 at 8:31 am
Hi,
Im really stuck with this one, could someone help me in fixing it.
Thanks,
Sanchit
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply