SQL Help - sum items based on max but also smaller than...? - Database Discussion Boards

Just create one single table.
If you create a table per user the database will be unmaintainable.

Wout Louwers

Single table

This will do for now

Separating the tables per-user is not necessary. Make sure that you specify that the user ID is an index and the RDBMS will do all of the heavy work for you - making sure that internally the table is structures in such a way that queries such as:

select * from audit where UserID = 'someuserid'

are very rapid. It would be well worth examining the documentation on CREATE INDEX:

http://msdn.microsoft.com/en-us/library/ms188783.aspx[^]

Careful usage of features such as these will give a much more performant solution that trying to roll your own equivalent (by separating into tables per user).

I think perhaps you replied to the wrong post?

This will do for now

eight wrote:
1. Search single user activities
2. Search all user activities

I don't see any reason to put them in separate tables. If it's the same data, with only a single different property, then it should go in the same table and the extra property becomes part of the key.

I are Troll Suspicious | :suss:

Hi,

Just throwing few built-in mechanisms which could be usable (without knowing the backgrounds for your audit needs). Have you had a look at Change Tracking and Change Data Capture: http://msdn.microsoft.com/en-us/library/cc280519.aspx[^].

Then SQL Server also has several different mechanisms for pure auditing: http://msdn.microsoft.com/en-us/library/cc280526.aspx[^] but some of those are edition specific.

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

I'm upgrading a MSDE SP4 Database consists of Tables containing Blob objects to SQL 2008 Express R2, during upgrade it goes on fine,and works after upgrade, but and after a while i get this microsoft error report.

Some unexpeceted errors have happened on the software you recently used.You were not asked to send the error reports at the time they occured.
Error Signature
Event Type : sqlexception001

Any clue ?

Wow, not even sure how to word the question... Here goes:

Basically, I'm trying to build a custom burn-down with TFS, Excel and SQL. Don't ask me why I'm doing a custom one when I shouldn't be, that's a question for my boss Frown | :(

Anyhow, I have a query that's set up a few tables and now I want to join them. Here's an example of my two temp tables:

Tickets:

ID	Changed date		Points	State
61	2011-02-25 06:38:07.090	0	For Production
61	2011-02-26 03:46:02.577	0	In Production
61	2011-03-04 03:22:32.620	0	Done
499	2011-03-04 04:26:10.060	0	New
623	2011-02-28 00:25:45.250	0.5	In Production
708	2011-03-03 00:55:31.407	3	In Development
708	2011-03-03 00:57:27.497	3	In Development
708	2011-03-03 03:55:17.390	3	In QA/UAT
708	2011-03-03 23:05:56.020	3	In QA/UAT
708	2011-03-04 05:21:43.133	3	In QA/UAT
738	2011-02-28 05:04:04.250	5	In Development
738	2011-02-28 22:56:58.053	5	In Development
738	2011-03-01 00:50:28.037	5	In Development
738	2011-03-01 21:06:35.550	5	In Development
738	2011-03-03 22:54:17.137	5	In QA/UAT
894	2011-02-28 00:29:04.183	0	To Do

Historical data in TFS is stored as a copy of the WorkItem row as it was BEFORE the change, along with a Changed Date field. In other words, the work item with id 3 has multiple copies in the WorkItemsWere table, each with a set of different properties, as can be seen above.

and Sprint Days:

Day	Day of year
Mon 1	51
Tue 1	52
Wed 1	53
Thu 1	54
Fri 1	55
Mon 2	58
Tue 2	59
Wed 2	60
Thu 2	61
Fri 2	62

I am achieving my burn-down by joining these two tables. My results so far look like this:

Day	Points  Day of year
Mon 1	0	51
Tue 1	0	52
Wed 1	0	53
Thu 1	0	54
Fri 1	0	55
Mon 2	0	58
Tue 2	0	59
Tue 2	5	59
Wed 2	0	60
Wed 2	10	60
Thu 2	0	61
Thu 2	10	61
Fri 2	0	62
Fri 2	3	62
Fri 2	15	62

See, the problem is I am joining each row in my sprint days table against EVERY work item row with a changed date <= the sprint day, and then summing. Here's my query:

SELECT
	[Day] AS [Sprint Day],
	SUM(Points) AS Points,
	[Day of year] AS [Order]
	
FROM
	#sprintDays d
	
	LEFT JOIN
		(
			SELECT
				Id,
				Points,
				DATEPART(DAYOFYEAR, [Changed Date]) AS [Changed Date],
				ROW_NUMBER() OVER
				(
					PARTITION BY
						Id,
						DATEPART(DAYOFYEAR, [Changed Date])
					ORDER BY
						DATEPART(DAYOFYEAR, [Changed Date]) DESC
				) AS Row
				
			FROM
				#tickets

			WHERE
				([State] = 'Committed' OR
				 [State] = 'In Development' OR
				 [State] = 'In QA/UAT')
				 
			UNION
			
			SELECT                     -- this is to ensure that something 
				0,                 -- comes out for any given day
				0,
				0,
				1
				
		) AS t
	ON d.[Day of year] <= t.[Changed Date] -- this is the dodgy bit here, methinks
		
GROUP BY
	t.Row,
	t.Id,
	[Day],
	[Day of year]

HAVING
	Row = 1

ORDER BY
	[Order] ASC

What I REALLY need to do, is join against something like MAX(ChangedDate) WHERE ChangedDate <= SprintDay, i.e. the sum of the points of each row in #tickets that has both the highest (latest) ChangedDate and also has a ChangedDate that is smaller than the [Day of year] of the SprintDay row I am joining to.

Does that make any sense? Apologies if it doesn't, my brain is melting ever-so-slightly...

Hi,

First I must admit that I didn't follow the whole sequence, but if I understood correctly, you want to have each sprint date and the for every sprint date the sum of points for that and earlier dates. If that's correct, could you simply use scalar in your select list to calculate a running total. Something like:

SELECT d.Day,
       d.[Day Of Year],
       (SELECT SUM(Points)
        FROM   #tickets t
        WHERE  t.[Changed Date] <= d.[Day Of Year]
        ...other possible restrictions...) AS Points
FROM #sprintDays d

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

Mika Wendelius wrote:
you want to have each sprint date and the for every sprint date the sum of points for that and earlier dates

Aha! That's the rub, see - not just the sum of points for all earlier dates, but the one, latest date found per individual id in a list of earlier dates... I will give your suggestion a go and see if I can tweak it to my needs.

Thanks for your help! If you have any more suggestions, throw them my way Smile | :)

It's really hard to explain, I think

EDIT:
At the moment it feels like I need to partition again, but I need to partition on the [Changed Date] of the sprintDays table on "each loop" of the select in order to find the highest ranked item... Maybe I should use a FOR loop or something? I want to avoid it, though, surely there's a way to do this in "Plain Old SQL".

modified on Monday, March 7, 2011 6:22 PM

Hi,

I noticed that you solved the problem by using cursor, which is fine but fot performance reasons I think that if we could find a set-based solution it'd be best.

So if I understood correctly you need each individual ID in your result set and sums for them, so could the query be something like:

SELECT d.Day,
       d.[Day Of Year],
       (SELECT SUM(Points)
        FROM   #tickets t
        WHERE  t.[Changed Date] <= d.[Day Of Year]
        AND    t.Id = sub1.Id) AS Points
FROM #sprintDays d, 
     (SELECT DISTINCT t.Id
      FROM #tickets t) sub1

Just correct me if If I'm going to wrong direction.

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

Fixed - I bit the bullet and used a cursor. Although, funny story, I couldn't figure out why the cursor was taking forever and the query plan was saying it was <2% of my processing time...

I wasn't fetching inside the loop. It was update the first row over... and over... and over...

*bashes head into desk*

Dear all,

my question sounds very simple and yes it is very simple.
what i am looking for just to copy data from one table to another table. i could write

insert into table2 ( select * from table1)

but in my case there is one database name MyDataBase.mdf and table name Customer

so now when user performs year ending (31st march) option then system should create new folder for new year for ex. 2011 if user is working in 2010.

so now in folder 2011 i have copy database which is MyDataBase.mdf

now i want to copy all the customers from Customer table(in 2010 folder) to Customer table (in 2011 folder)

so how can i copy data from same named database and same named table to same named database & table

insert into Customer(select * from Customer)

So can I differentiate insert Customer and select Customer table? I dont want to change table name in next year.

waiting for your kind help.

What can I say, your design sucks.

Why not do it from the other end like every other organisation out there. Backup the database at the end of the year (2010) and remove the data for 2009 from the production database. This way you maintain continuity, you don't have to do any dancing around database names.

Never underestimate the power of human stupidity
RAH

What database? Sql Server? You can link the databases and then the INSERT/SELECT should work.

But I prefer to use a connection to each one, use a DataReader to read from the source, and a parameterized INSERT for the destination.

This allows me to report progress if the process is very long-running.

In other situations, using the DataReader allows me to log duplicates and referential integrity issues without stopping the whole process.

hi thanks for your rep.

so how can use datareder to get data from table any examples?
waiting for your kind rep.

Sorry for the cross/re post, but I really need a solution to this problem. So far I have had the question posted for two days with only 50 views and no hints or answers. I really do not want to have to go through the effort of sanitizing out any possible writes. I can not set the database to be readonly at the file level as there is another app that has to have read-write access. I would think there should be a way for SQLServer to simply refuse to do any write operations for a readonly connection, but I have not been able to figure it out so far.

http://www.codeproject.com/Questions/164065/Read-only-ADO-connection-still-allows-writing-to-d.aspx[^]

I wrote:
I have connected to my SQL Server 2005 database by using ADO (through A set of ADO classes - version 2.20 by Carlos Antollini[^]). Now I have set the connection mode to be ReadOnly
CADODatabase *pDatabase = NULL;
pDatabase = new CADODatabase();
pDatabase->SetConnectionString(MyConnectionString);
pDatabase->SetConnectionMode(CADODatabase::connectModeRead);
pDatabase->Open();
SetConnectMode() calls ADODB::Connection15::PutMode
and CADODatabase::connectModeRead is adModeRead.

Now my understanding is that setting the adModeRead mode should make the database connection read-only. But a simple test of the connection shows that I can use the connection to create and drop tables, as well as insert and update data.

How can I make the connection truely read-only?

You may be right
I may be crazy
-- Billy Joel --

Within you lies the power for good - Use it!

Ok so thats odd, it never occurred to me that you could have a read only connection, read only credentials used by a connection but then it is the creds that are controlled, not the connection. What is wrong with setting up a set of creds that have RO access and your app uses them.

Never underestimate the power of human stupidity
RAH

The problem is that I do not own the database (as in I did not write the app that created it), and if (or more likely when) I distribute my app to other users I do not want to have to add users and permissions etc to their copy of the DB. I want just a simple read only connection from my app into the database, I do not want to modify the database in any way.

You may be right
I may be crazy
-- Billy Joel --

Within you lies the power for good - Use it!

Some nasty concepts are creeping in here

PJ Arends wrote:
I do not want to have to add users and permissions etc to their copy of the DB

Implies an embedded style database. Just which database are you using.

PJ Arends wrote:
I do not want to modify the database in any way.

Then control your code, you own the client you control it! If your users are writing sql script then there is no hope for you.

Never underestimate the power of human stupidity
RAH

Mycroft Holmes wrote:
Implies an embedded style database. Just which database are you
using.

The DB is a local copy of SQL Server Express 2005

Mycroft Holmes wrote:
Then control your code, you own the client you control it! If your users are
writing sql script then there is no hope for you.

I have read about SQL injection attacks. While it is not likely that users of my app would want to screw up their own data, having a truely readonly connection would prevent it.

You may be right
I may be crazy
-- Billy Joel --

Within you lies the power for good - Use it!

Here is what I would do.

From your app I would query the users/logins of the database, if your functional id (this describes the identity your app is going to use to communicate with the database.

create the user with user name and password using sql authentication. Give the user RO rights on all the objects (views and tables)

change your connection to use the RO credentials.

I seriously don't know if a RO connection is valid, I have certainly never heard of anyone using one.

Never underestimate the power of human stupidity
RAH

Currently I have a column in a MySQL database full of car makes such as Audi, Porsche, and BMW. Although, the make column is filled with either Audi or Audi® or Audi ® and for the other car makes. There are over 2000 rows. I'm trying to figure out how to make every row just "MAKE ®" without typing it because I'm using it to make a menu in php/javascript and it repeats menu items like "Audi" and "Audi®" and "Audi ®."

http://i54.tinypic.com/w7zm6c.png

I've tried SELECT DISTINCT but that just takes away the repeated makes, I need to change all the makes to follow the same format.

For example I have..

$makes_select = "SELECT DISTINCT make FROM files_avail where shw=1 ORDER BY make"; // Fetching of makes

I was looking up the MySQL Reference Pages like on here:

http://dev.mysql.com/doc/refman/5.0/en/replace.html

And query commands like UPDATE and INSERT.

But I am unsure of how to do it in MySQL, I didn't want to do anything before I screw up the entire database :3

Thanks!

-Faul

Member 7723899 wrote:
I didn't want to do anything before I screw up the entire database

then make a backup first.
or at least copy the relevant table to a new one.
if you have PHPmyAdmin, both actions would be very easy.

Member 7723899 wrote:
make every row just "MAKE ®"

a single update could remove every trailing ® in all rows that have one
then a single update could remove every trailing space in all rows that have one
then a single update could append whatever you want in all rows

BTW: I see no use of a suffix " ®" that would be present in every row, it does not add information, it just makes things more difficult IMO. And if you want it to appear on some forms, then that is a presentation issue, something a database should not be involved in.

Note: we have a separate MySQL forum!

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.

I found it out.

Simple enough.. I was just only wondering WHERE I should make the SQL code.

I'm using SQL Manager Lite for MySQL and you just have to go to the database table, click the DLL tab, edit, delete all the code and write whatever you want and execute.

UPDATE tablename SET columnname = 'whatever' WHERE columname = 'asdf' OR columname = 'asdf ®'