Database

26-Mar-11 20:44

Hi I have a column where are values regarding to time. And In my query I want to add additional column where I will have delta between each record. So e.g. between one record and another remain 34 seconds. For this one I was trying to use DateDiff function, and compare my column with times to the same column but offset by one record. And there is the problem. It looks like date DateDIFF(seconds, column, [subquery]) and I have error that subquery return more than 1 value and it its not appropriate where are following; =, != or something like that.
Do you have any ideas? how to manage this problem?

Dnorus

Wendelius26-Mar-11 22:07

26-Mar-11 22:07

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:

SQL

SELECT ...
       DATEDIFF(seconds,
                datecolumn1, 
                (SELECT TOP 1 datecolumn2 
                 FROM YourTable a1
                 WHERE ...
                 ORDER BY datecolumn2 DESC))
FROM YourTable
WHERE ...

The need to optimize rises from a bad design.My articles[^]

Member 377323026-Mar-11 22:24

26-Mar-11 22:24

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.

Wendelius26-Mar-11 22:42

26-Mar-11 22:42

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.

The need to optimize rises from a bad design.My articles[^]

Member 377323026-Mar-11 23:16

26-Mar-11 23:16

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.

Wendelius26-Mar-11 23:38

26-Mar-11 23:38

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.

The need to optimize rises from a bad design.My articles[^]

List of common misconceptions

Jörgen Andersson27-Mar-11 8:26

Jörgen Andersson

27-Mar-11 8:26

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

Re: Delta between records in one column [modified]

Member 377323027-Mar-11 22:34

What are solutions for a way-too-big database?

27-Mar-11 22:34

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

Pivot?

Andy_L_J26-Mar-11 14:56

Andy_L_J

26-Mar-11 14:56

I always struggle when it comes to PIVOTs (as I am pretty sure that is what is required).

I have the following query:

SQL

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

Re: Pivot?

Mycroft Holmes26-Mar-11 19:56

Mycroft Holmes

26-Mar-11 19:56

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

Re: Pivot?

Andy_L_J27-Mar-11 18:18

Andy_L_J

27-Mar-11 18:18

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

Re: Pivot?

Andy_L_J27-Mar-11 23:47

Andy_L_J

27-Mar-11 23:47

Tada...

SQL

-- Copy Article stuff
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)
	
-- test output
--print @sql
--SELECT * FROM #tblX
	
-- exucute dynamic sql
Exec (@Sql)
	
-- Drop Table
DROP TABLE #tblX
GO

Thanks for prompting me to persist. Now to add a couple more columns to the pivot Big Grin | :-D

I don't speak Idiot - please talk slowly and clearly

'This space for rent'

Driven to the arms of Heineken by the wife

bobbi200426-Mar-11 7:23

bobbi2004

26-Mar-11 7:23

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 ...............
...................................................

Wendelius26-Mar-11 7:41

26-Mar-11 7:41

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.

The need to optimize rises from a bad design.My articles[^]

bobbi200426-Mar-11 16:20

bobbi2004

26-Mar-11 16:20

Thank you very much, that helped me a lot Smile | :)

. As least I got the path to start with

PIEBALDconsult26-Mar-11 17:21

PIEBALDconsult

26-Mar-11 17:21

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).

bobbi200427-Mar-11 6:53

bobbi2004

27-Mar-11 6:53

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.

Multiple selection from the same table

Klaus-Werner Konrad31-Mar-11 8:42

Klaus-Werner Konrad

31-Mar-11 8:42

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 ..

CodingLover25-Mar-11 19:43

CodingLover

25-Mar-11 19:43

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 Smile | :)

Аslam Iqbal26-Mar-11 0:14

Аslam Iqbal

26-Mar-11 0:14

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'.

SQL

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.

Wendelius26-Mar-11 0:53

26-Mar-11 0:53

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.

The need to optimize rises from a bad design.My articles[^]

Luc Pattyn26-Mar-11 7:27

Luc Pattyn

26-Mar-11 7:27

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.

Smile | :)

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.

Wendelius26-Mar-11 7:45