June 12, 2010 at 1:36 pm
Hi,
I am executing a SP on SQL Server 2000, to produce a format I require (attached below for reference - "Test_template.xls"). The issue i am having is that the Location names are not coming in an ordered format. The attached format shows the order I want my locations to be shown. But my SP is not returning in that particular format.
Table Structure...
CREATE TABLE DataLoad
(
Location NVARCHAR(100),
EventCode nvarchar(100),
Datetime1 DATETIME,
Datetime2 DATETIME,
Id INT,
UniqueNum NVARCHAR(100)
)
CREATE TABLE dbo.tblMainTable
(
Location nvarchar(100),
Id nvarchar(20),
UniqueNum nvarchar(100),
Events int,
MaxDataTime1 nvarchar(100),
MaxDateTime2 nvarchar(100),
LongestTime nvarchar(100),
)
The Stored Procedure is as given below...
CREATE PROCEDURE dbo.Test_run
AS
SET NOCOUNT ON
BEGIN
DECLARE @i INT, @j INT, @Depot nvarchar(100), @Id NVARCHAR(20), @GetUniqueno NVARCHAR(100), @EventCount INT, @MaxDateTime1 NVARCHAR(100), @MaxDateTime2 NVARCHAR(100), @GetLongestTime NVARCHAR(100)
DECLARE @EventCount2 INT, @LongestTime2 NVARCHAR(100),@UniqueNum nvarchar(100) @j int
DECLARE @EventCount3 INT, @LongestTime3 NVARCHAR(100),@LocTotal nvarchar(100), @LocTotal2 nvarchar(100)
DECLARE @Date1 nvarchar(100), @Date2 nvarchar(100),@LocCount1 int, @LocCount2 int
CREATE TABLE #TempStore(Location NVARCHAR(100),
Id NVARCHAR(20),
UniqueNum NVARCHAR(100),
Events INT,
DateTime1 NVARCHAR(100),
DateTime2 NVARCHAR(100),
LongestTime NVARCHAR(100),
)
SELECT DISTINCT Location INTO #tempid FROM dbo.DataLoad (NOLOCK) WHERE 1=1 ORDER BY Location
SET @i = (SELECT COUNT(*) FROM #templocation)
WHILE @i >0
BEGIN
SELECT top 1 @Location = Location FROM #templocation
DELETE FROM #templocation WHERE @Location = Location
SELECT DISTINCT Id INTO #tempdid FROM dbo.DataLoad (NOLOCK) 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 @GetUniqueno = UniqueNum FROM dbo.DataLoad (NOLOCK) WHERE @Id = Id AND @Location = Location
SELECT @EventCount = COUNT(EventCode) FROM dbo.DataLoad (NOLOCK) WHERE @Id= Id AND @Location = Location
SELECT @MaxDateTime1 = MAX(CONVERT(NVARCHAR,Datetime2,120)) FROM dbo.DataLoad (NOLOCK) WHERE @Id = Id AND @Location = Location
SELECT @MaxDateTime2 = MAX(CONVERT(NVARCHAR,Datetime1,120)) FROM dbo.DataLoad (NOLOCK) WHERE @Id = Id AND @Location = Location
SELECT @GetLongestTime = MAX(CONVERT(nvarchar,Datetime2 - Datetime1,108)) FROM dbo.DataLoad (NOLOCK) WHERE @Id = Id AND @Location = Location
INSERT INTO #TempStore VALUES(@Location, @Id, @GetUniqueno, @EventCount, @MaxDateTime1, @MaxDateTime2, @GetLongestTime)
SET @j = @j -1
END
DROP TABLE #tempid
SET @Id = NULL
SET @UniqueNum = NULL
SET @Date2 = NULL
SET @Date1 = NULL
SELECT @Id = ISNULL(@Id,'')
SELECT @UniqueNum = ISNULL(@UniqueNum,'')
SELECT @Date2 = ISNULL(@Date2,'')
SELECT @Date1 = ISNULL(@Date1,'')
SELECT @LocTotal = (@Location + ' Total')
SELECT @LocCount1 = COUNT(*) FROM #TempStore (NOLOCK) WHERE @Location = Location
SELECT @EventCount2 = SUM(Events) FROM #TempStore (NOLOCK) WHERE @Location = Location
SELECT @LongestTime2 = MAX(CONVERT(NVARCHAR,LongestTime,120)) FROM #TempStore (NOLOCK) WHERE @Location = Location
INSERT INTO #TempStore VALUES(@LocTotal, @Id, @UniqueNum, @EventCount2, @Date2, @Date1, @LongestTime2)
SET @i = @i -1
END
DROP TABLE #templocation
SET @Id = NULL
SET @UniqueNum = NULL
SET @Date2 = NULL
SET @Date1 = NULL
SELECT @Id = ISNULL(@Id,'')
SELECT @UniqueNum = ISNULL(@UniqueNum,'')
SELECT @Date2 = ISNULL(@Date2,'')
SELECT @Date1 = ISNULL(@Date1,'')
SELECT @LocTotal2 = 'GRAND TOTAL'
SELECT @LocCount2 = COUNT(*) FROM #TempStore (NOLOCK) WHERE Location LIKE '%Total%'
SELECT @EventCount3 = SUM(Events) FROM #TempStore (NOLOCK) WHERE Location LIKE '%Total%'
SELECT @LongestTime3 = MAX(CONVERT(NVARCHAR,LongestTime,120)) FROM #TempStore (NOLOCK) WHERE Location LIKE '%Total%'
INSERT INTO #TempStore VALUES(@LocTotal2, @Id, @UniqueNum, @EventCount3, @Date2, @Date1, @LongestTime3)
INSERT INTO dbo.tblMainTable
SELECT Location,Id,UniqueNum, Events,Datetime1 ,Datetime2 ,LongestTime
FROM #TempStore
ORDER BY Location
END
DROP TABLE #TempStore
What I am trying to achieve by this Procedure is produce Location wise details in a table, and then the table from the table would be exported to an Excel via an EXE.
Thanks in advance..
June 12, 2010 at 1:41 pm
If you want rows ordered, you must put an order by on the select that retrieves data from the table. There's no point in an order by on an insert statement, tables are unordered sets of rows and SQL is free to return data from tables in any order.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 12, 2010 at 1:48 pm
Hi Gail,
Thanks for the reply.
I have used an ORDER BY clause at the end where I am retrieving data from the Temp table (#TempStore) into the final table (tblMainTable) at the very end. But this is not working. What change should I make in the code in order to fix it.
Thanks.
June 12, 2010 at 2:08 pm
As I said, putting an order by on an insert statement (insert into tblMainTable select from #TempStore) is a waste of time. Tables are unordered sets and the order that rows are inserted into the table does not guarantee in any way the order that the rows will be returned.
The change you need to make is not in the listed code, it's in whatever selects from tblMainTable. The order by needs to go onto that select statement.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 13, 2010 at 4:12 am
Hi Gail,
The code provided above is the full version. What is happening after that last INSERT statement into tblMainTable is that an EXE will run and fetch the data from tblMainTable and insert it into an Excel sheet and export the excel sheet to a destination folder.
So my requirement is to get the data sorted before the EXE runs and picks data from the tblMainTable or in other words i need to put data into tblMainTable in proper/required order.
What change should I make to the code now to accomplish the above ?
Thanks..
June 13, 2010 at 5:53 am
As Gail already pointed out: you need to add the ORDER BY to your select statement that is used to export the data.
To use the related section of your original post
then the table from the table would be exported to an Excel via an EXE.
I expect that EXE has a line of code like
SELECT < col_list > FROM dbo.tblMainTable
--This line needs to be changed to
SELECT < col_list > FROM dbo.tblMainTable ORDER BY < criteria >
As a side note: you should replace your nested WHILE loops with a set based solution. I don't think the code needs to be that complicated...
Regarding export into an Excel format: I wouldn't use a separate EXE. Why not using SSIS or T-SQL/OPENROWSET?
June 13, 2010 at 6:54 am
Thanks for the suggestion Lutz, but it is not possible for me to edit the EXE as numerous other reports are produced from the SQL Server 2000 box from which this particular solution would be implemented. Editing the SELECT clause in the EXE would directly affect the other reports as well and I would want to avoid that.
Is there anything in the provided code, I could amend the existing version to produce the output i seek ?
Thanks.
June 13, 2010 at 7:23 am
Sanchit297 (6/13/2010)
Thanks for the suggestion Lutz, but it is not possible for me to edit the EXE as numerous other reports are produced from the SQL Server 2000 box from which this particular solution would be implemented. Editing the SELECT clause in the EXE would directly affect the other reports as well and I would want to avoid that.Is there anything in the provided code, I could amend the existing version to produce the output i seek ?
Thanks.
No. There is no way to guarantee the order of results than using ORDER BY in the final SELECT statement.
If you can't modify the EXE, you'refacing another issue: ALL result sets exported with that "tool" suffer the same problem: sooner or later you'll be faced with bad results due to the lack of this exe.
Instead of trying to avoid rewriting the other reports you should face the risk your report reliability has right now, fix the exe and all reports that make use of it.
As an alternative you could start implementing a separate solution (either another exec or an SSIS solution or some T-SQL code or whatever) and use it for your current scenario. By doing so you'll have something available when the current EXE starts to fail on other reports...
June 13, 2010 at 7:36 am
Sanchit297 (6/13/2010)
So my requirement is to get the data sorted before the EXE runs and picks data from the tblMainTable or in other words i need to put data into tblMainTable in proper/required order.
What you're asking is not possible. Tables do not have an intrinsic order to the rows within them. Regardless of insert order, SQL is free to return data in any order if there's no order by statement on the select.
What change should I make to the code now to accomplish the above ?
There aren't any changes you can make. What you are asking is impossible. The order by has to go onto the select that extracts data from the table. the one in the exe. That or you'll have to sort the data in excel once it's exported
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 16, 2010 at 3:12 am
Thanks for the replies. I will sort this out.
In the meantime, I have found a bug in my code during testing. It relates to LongestTime field in the MainTable which is not being displayed correctly.
Both Datetime1 and Datetime2 are of DATETIME type. Sample values in these fields as shown below..
Date1 Date2
----------------------- -----------------------
2010-06-11 10:22:41.000 2010-06-11 11:24:01.863
2010-06-12 06:23:35.000 2010-06-12 07:23:51.860
2010-06-12 09:53:46.000 2010-06-12 10:53:56.607
2010-06-11 06:21:01.000 2010-06-14 07:21:12.853
2010-06-12 06:08:41.000 2010-06-12 07:08:50.580
2010-06-11 08:13:03.000 2010-06-14 09:15:11.150
2010-06-11 08:24:24.000 2010-06-11 09:25:21.000
2010-06-11 09:39:32.000 2010-06-13 10:40:12.560
2010-06-11 10:17:50.000 2010-06-13 11:18:44.040
2010-06-12 07:03:58.000 2010-06-12 08:04:50.470
2010-06-12 07:04:04.000 2010-06-12 08:04:50.470
2010-06-12 07:46:00.000 2010-06-13 08:47:31.600
2010-06-12 08:21:08.000 2010-06-14 09:22:55.853
2010-06-12 09:33:19.000 2010-06-12 10:34:29.460
I have a column "Longest Time" (of NVARCHAR type) which calculates the difference of (Datetime2 - Datetime1) and shows the Maximum value from the subtracted values (calculated 3 times in the SP)
The queries in question are as given below...
SELECT @GetLongestTime = MAX(CONVERT(nvarchar,Datetime2 - Datetime1,108)) FROM dbo.tblMainTable (NOLOCK) WHERE @Id = Id AND @Location = Location
SELECT @LongestTime2 = MAX(CONVERT(NVARCHAR,LongestTime,120)) FROM #TempStore (NOLOCK) WHERE @Location = Location
SELECT @LongestTime3 = MAX(CONVERT(NVARCHAR,LongestTime,120)) FROM #TempStore (NOLOCK) WHERE Location LIKE '%Total%'
Now the above query works fine till the value from it is till 23:59:59, now if the value is more than 23:59:59, it shows wrong results.
Can someone suggest what can i do to remove this issue in my code as i need to finish my testing by today??
Thanks in advance..
June 16, 2010 at 9:57 am
Can someone provide suggestions on an urgent basis...
Thanks...
June 16, 2010 at 10:21 am
It's usally a better idea to start a new thread if you have a different question.
The chances are low to find somebody providing a date difference solution if the title of the thread talks about sorting...
Another reason why might not have an answer yet: requests marked as "urgent" tend to slow down the response process. Strange, but that's what I noticed.
Regarding the subject itself: please have a look at the DATEDIFF function. It might give you the result you're looking for. Since you didn't tell us what you consider "correct" or "wrong" it's hard to provide a more detailed answer.
So, please take the time to read and follow the first article referenced in my signature on how to provide sample data. I'm sure once you provided ready to use sample data together with your expected result in a new thread you'll get almost immediate response. 😉
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply