|
If you use a subquery to fetch the previous (or next) record you must ensure that only 1 record is fetched. So this means that you have to be able to order the rows based on some column and take only the first, for example using TOP 1. So your query could look something like:
SELECT ...
DATEDIFF(seconds,
datecolumn1,
(SELECT TOP 1 datecolumn2
FROM YourTable a1
WHERE ...
ORDER BY datecolumn2 DESC))
FROM YourTable
WHERE ...
|
|
|
|
|
It isnt good solution. subquery returns one record for example first one. and Each record in datecolumn1 is compared to only one record from datecolumn2. I want to create some query that I will have data from datecolumn from ID 1 to 10, then create subquery to have data from the same datecolumn from ID 2 to 11, and then use datediff to obtain time delta between following records.
|
|
|
|
|
This is why I wrote WHERE ... inside the subquery. What you must do is to add necessary conditions into the subquery using correlation to the outer query. Since you didn't provide the query and the columns I'm just quessing the columns but try adding something like the following to the subquery
...
WHERE a1.datecolumn1 < a.datecolumn1
...
A1 is the alias for you table in the subquery and A is alias for your table in the main query.
|
|
|
|
|
Oh it is working;) thx.. but it is working for to long.... maybe another way?
something like:
....
Datediff(seconds, datecolumn, (Delete first row from datecolumn and add at the end null))
Is there any possibility to do that? Because now I have almost the same searching in main query and subquery, and I think this main issue for this long working.
|
|
|
|
|
If it's taking a long time, it's probably because for each row in the main query a scan is done to the table to fetch the previous/next row. If possible, can you add an index to the table for the column you're using in the subquery. If several columns are used in the subquery condition consider having more than one column in the index.
|
|
|
|
|
You can build it on this principle.
WITH Ordered AS (
Select IDColumn,DateColumn,ROW_NUMBER() OVER(ORDER BY DateColumn) as rn
FROM YourTable
)
SELECT o2.IDColumn,DateDiff(seconds,o2.DateColumn - o1.DateColumn)
FROM ordered o1
join ordered o2
on o1.rn = o2.rn - 1
|
|
|
|
|
Okey it seems working and also in a very fast way. But I have a another question.
In section With ordered as( ) I have some values declared... and now I dont know where can I put Declare and Set variable..
Ah Ok I didnt have ";".. before With or after declarations
So now it is perfect;)
modified on Monday, March 28, 2011 4:59 AM
|
|
|
|
|
I always struggle when it comes to PIVOTs (as I am pretty sure that is what is required).
I have the following query:
WITH plant As
(SELECT mp.ID, mp.MixPlant_Name
FROM MixPlant mp
WHERE mp.IsActive = 1)
SELECT DISTINCT(mp.MixDate), plant.MixPlant_Name, SUM(mp.NoMixes) As No
FROM MixProduction mp
JOIN plant
ON mp.MixPlant_ID = plant.ID
WHERE mp.MixDate BETWEEN '2/22/2011' AND '2/23/2011'
Group By plant.MixPlant_Name, mp.MixDate
Go
which produces the following result:
MixDate MixPlant_Name No
2011-02-22 00:00:00.000 MTI 88
2011-02-22 00:00:00.000 Plasmec 116
2011-02-22 00:00:00.000 Prodex 27
2011-02-23 00:00:00.000 MTI 69
2011-02-23 00:00:00.000 Plasmec 121
2011-02-23 00:00:00.000 Prodex 15
I am looking to get the following output:
MixDate MTI Plasmec Prodex
2011-02-22 00:00:00.000 88 116 27
2011-02-23 00:00:00.000 36 121 15
Any suggestions, links or gentle sarcasm appreciated.
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
Some have found this article[^] useful, I know you need just the standard 1 column pivot but anyone who can use a CTE should have no problems with this.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks, I had actually looked at that is my wanderings.
I will post my solution when complete.
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
DECLARE @List VARCHAR(1000), @Sql VarChar(5000)
SET @List = ''
SET @Sql = ''
IF EXISTS (SELECT * FROM tempdb.sys.objects
WHERE name LIKE '#tblX%' AND type IN (N'U'))
DROP TABLE [dbo].#tblX
CREATE TABLE #tblX(MixDate DateTime, NoMixes Int, MixPlant_Name VarChar(50) )
INSERT [#tblX]
SELECT DISTINCT(mp.MixDate), SUM(mp.NoMixes) As NoMixes, Mixplant.MixPlant_Name
FROM MixProduction mp
INNER JOIN MixPlant
ON mp.MixPlant_ID = Mixplant.ID And MixPlant.IsActive =1
WHERE mp.MixDate BETWEEN '2/1/2011' AND '2/28/2011'
Group By Mixplant.MixPlant_Name, mp.MixDate
DECLARE @tblY TABLE(MixPlant_Name VarChar(50))
INSERT @tblY
SELECT DISTINCT MixPlant_Name
FROM #tblX
ORDER BY MixPlant_Name
SELECT @List = ISNULL(@List, '') +
CASE WHEN ISNULL(@List,'') = ''
THEN '[' + MixPlant_Name + ']'
ELSE ',[' + MixPlant_Name + ']' END
FROM @tblY
ORDER BY MixPlant_Name
SET @Sql = 'SELECT MixDate, ' + @List + CHAR(13)
SET @Sql = @Sql + 'FROM (SELECT MixDate, NoMixes, MixPlant_Name ' + CHAR(13)
SET @Sql = @Sql + 'FROM #tblX ) As P ' + CHAR(13)
SET @Sql = @Sql + 'PIVOT (SUM(NoMixes) FOR MixPlant_Name IN (' + @List + ')) As Pvt' + CHAR(13)
Exec (@Sql)
DROP TABLE #tblX
GO
Thanks for prompting me to persist. Now to add a couple more columns to the pivot 
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
Hi guys!
I'm a software developer and not very good in database designing. One day, I was asked something like this :
"For example, there is a company with a web application. One day, the database for that application is way too big, caused performance issues and others, what is the solution for that application database?"
At first, I thought that was about using multiple database with single app. But I don't know if I was right.
I want to ask that what are the solutions? If it's "multiple database" then what should I do? Using two connection to 2 database simutaneously?
I appreciate any replies. Thanks!
...................................................
............ I have nothing to lose ...............
...................................................
|
|
|
|
|
This is a large topic but some keywords you could lookup (taken this is SQL Server):
- (better) indexing
- partitioning
- using filegroups to distribute data across disks
- using well optimized views
- transferring workload to another database using different techniques to copy the data from original db (may not be possible)
- etc etc.
- and last but not least, redesign of the database
I'd say that the key thing is to try to make such modifications which are not visible to the application (expect in response time of course) so using directly multiple db's will cause large redesigns and also typically leads to different kinds of conflict situations.
|
|
|
|
|
Thank you very much, that helped me a lot  . As least I got the path to start with
|
|
|
|
|
Most importantly, have an expert review the SQL code.
At one job I had I was assigned to add some functionality to a (batch) program and noticed some rather odd SQL. I asked about it and got the reponse, "If it ain't broke, don't fix it". In my opinion it was broke so I darn well fixed it. The result was that after I changed the SQL and added the functionality the program ran in a quarter of the time (ten minutes instead of forty).
|
|
|
|
|
Indeed!
I was also adviced that, doing the database job with an expert if you are not sure about it. It's a rather safe way.
|
|
|
|
|
One common technique is vertical splitting, so you hold only the most recent records in the (main) table and create a history table for the old ones.
To achieve this you have to conditionally (based on the time frame) do a union on the history table, and also create a mechanism to shift records from your main table to the history table.
Also, you can try to (as another poster wrote) optimize / add indexes, and/or invest in a speedier DB-server ..
|
|
|
|
|
Hi all,
I'm stuck with selecting multiple conditions in same table. I'll explain in this way. I've a table called tblPackages as follows.
package id value code
------- ---- ------ ------
pak 1 | 1 | 0 | 58
pak 2 | 3 | 0 | 58
pak 3 | 4 | 0 | 58
pak 1 | 3 | 10 | 31
pak 2 | 2 | 100 | 31
pak 3 | 3 | 8 | 31
so I want to find all the package details that id = 1 / code = 58 and id = 3 / code = 31. So the package should be 'pak 1'
I can explain the same like this. Say I execute the following query
select * from tblPackages where id = 1 and code = 58, the result is
package id value code
------- ---- ------ ------
pak 1 | 1 | 0 | 58
and then I execute the following query,
select * from tblPackages where id = 3 and code = 31, the result is
package id value code
------- ---- ------ ------
pak 1 | 3 | 10 | 31
pak 3 | 3 | 8 | 31
so the common result is,
package id value code
------- ---- ------ ------
pak 1 | 1 | 0 | 58
pak 1 | 3 | 10 | 31
That's what I want to get. From that later I want to get either code of 58 data or code of 31 data.
Can anyone of you comment on me. I've try to get data separately and merge manually, but it's not easy as i though.
thanks
I appreciate your help all the time...
CodingLover
|
|
|
|
|
It is not clear that what is your problem. As i understood your problem is to view both results of those query in a single output. To that you have to use 'UNION'.
select * from tblPackages where id = 1 and code = 58
UNION
select * from tblPackages where id = 3 and code = 31
It'll produce this output:
package id value code
------- ---- ------ ------
pak 1 | 1 | 0 | 58
pak 1 | 3 | 10 | 31
You have to use 'DISTINCT' to eliminate duplicate entries.
You can also try using JOIN but I can't understand what is you problem.
|
|
|
|
|
Hi,
You didn't quite specify what relates those to rows so I assume it's the package. In that case could have something like:
SELECT *
FROM tblPackages main
WHERE Id IN (1,3)
AND Code IN (58, 31)
AND Package = (SELECT DISTINCT Package
FROM (SELECT Package
FROM tblPackages
WHERE id = 1
AND code = 58) a,
(SELECT Package
FROM tblPackages
WHERE id = 3
AND code = 31) b
WHERE a.Package = b.Package)Don't mind about typos etc, it's not tested at all.
In the above if you can have several matching package use IN instead of equality for the package comparison. Also the statement can be simplified so this is just one version.
|
|
|
|
|
Huh? I think this[^] holds the answer, and
... WHERE (id = 1 AND code = 58) OR (id = 3 AND code = 31)
should do it.
CodingLover wrote: all the package details that id = 1 / code = 58 and id = 3 / code = 31
I guess your problem is linguistic: the "and" in the above sentence isn't really an "and" (a row couldn't have ID=1 AND ID=3 at the same time) it is more of an "and also", which actually indicates an "or" situation.
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
|
|
|
|
|
Wouldn't that lead to a situation where the row
pak 3 | 3 | 8 | 31 is included? I got the impression that it should be eliminated from the result.
|
|
|
|
|
yes it would.
however I found "so I want to find all the package details that id = 1 / code = 58 and id = 3 / code = 31. So the package should be 'pak 1'" a bit confusing, it would result in two rows IMO.
The enquirer will have to make up his mind and tell us what he really wants.
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
|
|
|
|
|
Luc Pattyn wrote: The enquirer will have to make up his mind and tell us what he really wants
That's true
|
|
|
|
|
Hi,
Is it possible to dynamically change column name in sql select statement
eg:
DECLARE @Currency NVARCHAR(50)
SET @Currency ='USD'
Select Col1 as Currency+@Currency ,col2 from tbl_Emp
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
