|
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 
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
|
|
|
|
|
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  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.
|
|
|
|
|
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!
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 ®'
|
|
|
|
|
Member 7723899 wrote: 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
The steps are
1. Determine what values are in there
2. Determine what values should be in there.
3. Create an algorithmic solution that will convert an incorrect value to a correct one. This is based on steps 1 and 2.
4. Implement 3 in SQL.
5. Run 4.
Your description sounds like you have not completed steps 1 and 2 in the above and are attempting to jump directly to step 4.
Additionally.
1. Determine if you want to prevent future incorrect values
2. If yes then add a constraint that prevents incorrect values.
Based on your description you would do this by creating an enumeration table which is nothing but a table of correct names. Then you use a foreign key from the existing table to the enumeration table.
|
|
|
|
|
No, no, no...
The steps are
0) Put your left foot in
1) Take your left foot out
2) Put your left foot in
3) Shake it all about
4) Do the hokey pokey
|
|
|
|
|
Greetings,
I've been having an issue on one of our servers here where we could not connect to the SQL database hosted on the same machine over named pipes from a web application. We could, however, connect from enterprise manager using the same credentials. Shutting the firewall off didn't help, nor did any tweak we made to the sql server configuration. While biting my lip in despair, I happened to notice that the system time was off by three years. I figured I'd fix it while I was trying to figure out what to do with SQL. Once I fixed it though, SQL authentication started working again.
Now, while I'm totally stoked that I was able to fix the problem, I don't understand WHY that fixed the problem, and I do try to avoid cargo-cult style system repairs. Can anybody explain this to me?
Thanks,
Will
|
|
|
|
|
I'm not familiar enough with SQL syntax, and I need to make a little complicated SELECT.
I have a table with 3 fields: contentID, date and readsCounter.
I have to get the top 2 read contents for the last 2 days.
For example:
contentID: 0, date: 1-1-2000, readsCounter: 10
contentID: 0, date: 2-1-2000, readsCounter: 80
contentID: 1, date: 1-1-2000, readsCounter: 40
contentID: 1, date: 2-1-2000, readsCounter: 5
contentID: 2, date: 1-1-2000, readsCounter: 20
contentID: 2, date: 2-1-2000, readsCounter: 30
the lines above are 6 records from my db, contents 0 and 2 are the contents that need to be selected(the contents and the reads sum for the last 2 days).
I'm working with MSACCESS DB. someone can help me?
|
|
|
|
|
The dates are from 10 years ago, so how would they be considered for the last 2 days. 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
|
Use a combination of Row_Number and Partition. You need to create a 2 query select, the first injects the row_number based on the partition of contentid and date, ordered by readscounter.
The outer query selects anything with a row_number < 3.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
