Any SQL gurus up for a challenge ?

[bigmouk]

Its not that i cant find a solution, its just im not happy with the one i have, want to make it more efficient.

If anyone is up for it, i will post up the task

 

[gazzrenn]

Depends how complicated the challenge is! Go for it!

 

[Pete]

Depends on what it is! lol

 

[bigmouk]

not complicated, think ive been at a while too long today, bet the answer is staring me in the face.

here is the sample data returned from my current query.

1109 83 1897.50 2006-06-05 01:00:00
1109 100 1897.50 2006-06-06 12:20:00
1110 60 5269.90 2006-06-05 00:01:00
1110 75 5269.90 2006-06-06 10:38:00
1110 100 5269.90 2006-06-09 14:37:00
1111 100 0.00 2006-06-12 00:01:00
1111 100 0.00 2006-06-26 09:00:00
1112 0 4627.50 2006-06-05 01:00:00
1112 0 4627.50 2006-06-26 09:00:00

col1 = projectid
col2 = % complete
col3 = cost
col4 = entry date

All i want is the MIN date and MAX date for each projectid within the date range (June 06 in the above example) ... so 2 records for every projectid

1109 is ok - 2 records
1110 not ok - 3 records
1111 is ok - 2 records
1112 is ok - 2 records

Cant use MIN and MAX because the GROUP BY clause needs the % complete which is different in all 3 records.

I can return all the records and manually loop to remove 'in between' records but on a 12 month result set i have in excess of 10,000 records.

Hope thats clear ??

 

[bigmouk]

Im thinking the best way is to loop the select statement using a cursor and dumping the first and last entry for each project in a temp table, then return all the records from the temp table? .... unless ive missed something ?

 

[gazzrenn]

Shouldn't you group by projectid?

Anyways, thats almost a little over my head, however.... we have about 15 SQL developers here, so i'll see if i can bend someones ear :rasp:

 

[gazzrenn]

That may be harder than i thought, they're all a tad busy it seems...

However, if you select MAX date then MIN date apparently you can use a UNION to join the results together.

I'll have a play and see where i get to

 

[bigmouk]

I thought and tried using UNION aswell mate, would still mean trying to get 1 record for each projectid (MIN and MAX) .... again unless ive missed somethin

Be very interested to see what a SQL Dev would recommend

Heres my query in sql which works, not quite finished, however its not very lightweight using cursor, looping and temp tables.....

DECLARE @ProjectID int
DECLARE @Percent int
DECLARE @Total decimal(18,2)
DECLARE @Date smalldatetime
DECLARE @PREVProjectID int
DECLARE @PREVPercent int
DECLARE @PREVTotal decimal(18,2)
DECLARE @PREVDate smalldatetime

SET @PREVProjectID = 0

DECLARE @FIRSTProjectID int
DECLARE @FIRSTPercent int
DECLARE @FIRSTTotal decimal(18,2)
DECLARE @FIRSTDate smalldatetime
DECLARE @LASTProjectID int
DECLARE @LASTPercent int
DECLARE @LASTTotal decimal(18,2)
DECLARE @LASTDate smalldatetime

DECLARE @FirstRecord int
SET @FirstRecord = 0

DECLARE MyCursor CURSOR FOR
SELECT tblAccrued_Work.ProjectID,
tblAccrued_Work.Percentage_Invoiceable_To_Date,
tblAccrued_Work.Total_Costed,
tblAccrued_Work.Date_Created
FROM tblAccrued_Work
INNER JOIN tblProjects
ON tblAccrued_Work.ProjectID = tblProjects.ProjectID
WHERE (tblAccrued_Work.Date_Created BETWEEN '1-6-2006' AND '30-6-2006')
AND (tblAccrued_Work.StageID = 0)
AND tblProjects.StatusID = 2
AND tblProjects.Archived = 0
ORDER BY tblAccrued_Work.ProjectID

OPEN MyCursor

FETCH NEXT FROM MyCursor
INTO @ProjectID, @Percent, @Total, @Date

WHILE @@FETCH_STATUS = 0
BEGIN
--first record in set
IF @PREVProjectID = 0
BEGIN
--store first record in set
SET @FIRSTProjectID = @ProjectID
SET @FIRSTPercent = @Percent
SET @FIRSTTotal = @Total
SET @FIRSTDate = @Date
END
--if this projectid is equal to previous projectid
ELSE IF @ProjectID <> @PREVProjectID
BEGIN --NEW PROJECTID FOUND / START OF NEW RECORD -- Keep info
--only 1 entry found in date range (new project / short date range / very old project)
IF @FIRSTProjectID <> @LASTProjectID
BEGIN
PRINT @FIRSTProjectID
END
ELSE
BEGIN
--**STORE IN TEMP TABLE**--
--store records in temp table
PRINT 'first = ' + CAST(@FIRSTProjectID AS varchar(10)) + ', ' + CAST(@FIRSTPercent AS varchar(3))
PRINT 'last = ' + CAST(@LASTProjectID AS varchar(10)) + ', ' + CAST(@LASTPercent AS varchar(3))
END

--store first record
SET @FIRSTProjectID = @ProjectID
SET @FIRSTPercent = @Percent
SET @FIRSTTotal = @Total
SET @FIRSTDate = @Date

END
ELSE IF @ProjectID = @PREVProjectID
BEGIN
--store last record
SET @LASTProjectID = @ProjectID
SET @LASTPercent = @Percent
SET @LASTTotal = @Total
SET @LASTDate = @Date
END

--store values
SET @PREVProjectID = @ProjectID
SET @PREVPercent = @Percent
SET @PREVTotal = @Total
SET @PREVDate = @Date
FETCH NEXT FROM MyCursor
INTO @ProjectID, @Percent, @Total, @Date
END

CLOSE MyCursor
DEALLOCATE MyCursor

 

[gazzrenn]

There must be an easier way than that!

I've made a sample database, gonna try and grab a dev for you :rasp:

 

[172.com]

Just read the initial post again. Forget anything I said.

 

[172.com]

OK, I'm confused about your end product... You want two records per projectid one with the first date one with the last date with the most up-to-date % complete?

Wouldn't the last date have the highest percentage anyway? or can the percentage go down?

 

[bigmouk]

^ exactly mate, earliest date in the range specified and latest date in the range specified. percentage + cost can go up or down, so record 1 could be 100%, £5000 and then change at a later date to say 80% and £6000 (jobs that get finished and then client adds more work on top last min).

also, you could run a report for 12 months and maybe have 100 records for each project. however, all i want is the first record in january and the last one in december.

basically its to get an approx amount of work in £ delivered in a period of time

 

[172.com]

SELECT MIN(projectid), MIN([entry date])
FROM xxx
WHERE [entry date] BETWEEN '20060601' AND '20060701'
GROUP BY projectid
UNION
SELECT MAX(projectid), MAX([entry date])
FROM xxx
WHERE [entry date] BETWEEN '20060601' AND '20060701'
GROUP BY projectid

Would work would it not?

Looks like your percentage and costs are coming from other tables so just do an inner join on those.

Or am I completely missing the point?

 

[bigmouk]

^ wont work mate. need the % complete and total cost data in each record aswell. then you need to GROUP BY so you are back at sq 1.

 

[172.com]

Are the % and total cost going to be the same in both rows? If so, just do the inner joins on each select.

 

[bigmouk]

There must be an easier way than that!

I've made a sample database, gonna try and grab a dev for you :rasp:

a bit more sample data

1065 5 24885.00 2006-06-01 10:52:00
1065 5 24885.00 2006-06-12 00:01:00
1065 5 24885.00 2006-06-19 09:00:00
1065 15 22658.10 2006-06-22 09:59:00
1065 30 22658.10 2006-06-29 17:22:00
1066 60 2185.00 2006-06-05 01:00:00
1066 60 2185.00 2006-06-12 01:00:00
1066 60 2185.00 2006-06-19 09:00:00
1066 70 2185.00 2006-06-21 18:19:00
1066 85 2185.00 2006-06-26 18:05:00
1067 40 10350.00 2006-06-05 01:00:00
1067 60 10350.00 2006-06-08 16:56:00
1067 65 10350.00 2006-06-12 17:30:00
1067 80 10350.00 2006-06-19 17:06:00
1067 90 10350.00 2006-06-26 09:53:00

 

[bigmouk]

Are the % and total cost going to be the same in both rows? If so, just do the inner joins on each select.

Its been a long day, i could be missing something but i think ive tried most things. You could knock up a table and drop in 10 records from above and try it. Each row can have a different value for % or cost and each row can have identical values (like a duplicate row - but this has been entered on purpose).

 

[bigmouk]

I thought using a view would be perfect but i dont think you can pass parameters to a view so that put a spanner in the works with that.

 

[KDF]

Looks like you need to read 'Relational Databases for Beginners'

:clown:

 

[bigmouk]

Helpful post^ :S

 

[KDF]

If you dont set up proper relational databases then you run into problems like above so ye it was helpfull.

 

[bigmouk]

How can you tell its not a 'proper relational database' if you know nothing about the database in question, were it came from, who developed it, what it is used for. You obviously know your stuff if you can tell that without seeing the other 40 tables and 200 stored procedures. If you would enlighten me on how this particular data should be stored that would be great !

 

[KDF]

If you had a proper relational database you would be in better control of the returned data.

All im saying is if you set it up keeping in mind what sort of queries your going to be running and use a proper data model then you make it easier for yourself in the long run.

Experience teaches you that.

 

[bigmouk]

Problem solved after a fresh crack at it this morning

Query below if anyone is interested ... (definitely more light weight than my looping solution)

SELECT t1.ProjectID, t1.StageID, MIN(t1.Date_Created) AS Date_Created
INTO #MinTable
FROM tblAccrued_Work AS t1
WHERE t1.Date_Created BETWEEN '1-6-2006' AND '30-6-2006'
AND t1.StageID = 0
GROUP BY t1.ProjectID, t1.StageID
ORDER BY t1.ProjectID

SELECT t1.ProjectID, t1.StageID, MAX(t1.Date_Created) AS Date_Created
INTO #MaxTable
FROM tblAccrued_Work AS t1
WHERE t1.Date_Created BETWEEN '1-6-2006' AND '30-6-2006'
AND t1.StageID = 0
GROUP BY t1.ProjectID, t1.StageID
ORDER BY t1.ProjectID

SELECT tblAccrued_Work.ProjectID, tblAccrued_Work.*
FROM #MinTable
LEFT JOIN tblAccrued_Work
ON #MinTable.ProjectID = tblAccrued_Work.ProjectID
AND #MinTable.Date_Created = tblAccrued_Work.Date_Created
AND #MinTable.StageID = tblAccrued_Work.StageID
UNION
SELECT tblAccrued_Work.ProjectID, tblAccrued_Work.*
FROM #MaxTable
LEFT JOIN tblAccrued_Work
ON #MaxTable.ProjectID = tblAccrued_Work.ProjectID
AND #MaxTable.Date_Created = tblAccrued_Work.Date_Created
AND #MaxTable.StageID = tblAccrued_Work.StageID
ORDER BY tblAccrued_Work.ProjectID

DROP TABLE #MinTable
DROP TABLE #MaxTable