Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Whit ONE select

Status
Not open for further replies.
bborissov

bborissov

Programmer
May 3, 2005
5,167
BG
've been asked it is possible to do this with ONE SELECT. First I answered NO, but here is about 2 PM and my mind doesn't works properly, so ask you :)
Here the conditions:

The database is a bug tracking database.
Table has several columns: bugID, open_date, close_date
A bug is considered open on a given day if it's open date is on or before that day, and it's close date is on or after that day or it is NULL.
So the goal is to generate a record set to show the number of open bugs for a range of dates.
The ideal result set would have two columns: date and open bug count on that date.
Some data:

(all dates here are in German Format - dd.mm.yyyy)
Code: 
BugId   Open_Date       Close_Date
 1      01.01.2006      02.01.2006	
 3      21.01.2006      NULL
 4      05.01.2006      10.02.2006	
 5      01.12.2005      NULL
 7      13.12.2005      13.02.2006
 8      12.02.2006      NULL
12      10.02.2006      10.02.2006
14      07.04.2006      NULL
20      18.11.2005      NULL
21      10.05.2006      NULL
22      15.02.2006      NULL
23      01.10.2004      03.02.2006	
24      31.12.2005      31.12.2005	
27      09.01.2005      09.01.2006	
32      01.01.2006      NULL
45      13.02.2006      NULL
55      12.12.2005      12.12.2005
61      31.12.2005      NULL
88      09.01.2006      09.01.2006
99      13.03.2006      NULL
So if the period is 01.04.2006 - 15.04.2006 desired result is:
Code: 
Date                          BugsCount 
2006-04-01 00:00:00.000	        9
2006-04-02 00:00:00.000	        9
2006-04-03 00:00:00.000	        9
2006-04-04 00:00:00.000	        9
2006-04-05 00:00:00.000	        9
2006-04-06 00:00:00.000	        9
2006-04-07 00:00:00.000	       10
2006-04-08 00:00:00.000	       10
2006-04-09 00:00:00.000	       10
2006-04-10 00:00:00.000	       10
2006-04-11 00:00:00.000	       10
2006-04-12 00:00:00.000	       10
2006-04-13 00:00:00.000	       10
2006-04-14 00:00:00.000	       10
2006-04-15 00:00:00.000	       10
Thanks in advance

Borislav Borissov
 
Sort by date Sort by votes
Suppose you have calendar table. If not, create one. Example:
Code: 
create table Calendar
(	calDate smalldatetime primary key
)
declare @d smalldatetime; set @d = '2006'
while year(@d) < 2007
begin
	insert into Calendar values (@d)
	set @d = @d + 1
end
And then:
Code: 
-- set ansi_warnings off
select C.calDate, count(BugID)
from Calendar C
left outer join blah B on B.Open_date <= C.calDate and (B.Close_date > C.calDate or B.Close_date is null)
where calDate between '20060401' and '20060415'
group by C.calDate
order by C.calDate
Note: that will kick some annoying/meaningless ANSI warnings if there are no open bugs for any of dates.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)
[banghead]
 
Upvote 0 Downvote
Thank you vongurt. I made this by creating a temporary table and include all dates for choosen period in it and then I do the SELECT, but the answer was "Not permited" :-( I have no words to describe how I fill (I have, but not for public area :))

Borislav Borissov
 
Upvote 0 Downvote
Maybe this will work for you...

Code: 
Select Open_Date,
       Sum(Case When CloseDate >= OpenDate 
                Then 1 
                Else 0 
                End) As BugCount
From   BugTrackingTable
Group By OpenDate
Order By OpenDate

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
Wouldn't that list sum of closed bugs - on a present day? Plus there is always that "missing rows" problem that makes use of temp/number/calendar table almost mandatory...

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)
[banghead]
 
Upvote 0 Downvote
Agree :) (I love the picture in your signature :))

Borislav Borissov
 
Upvote 0 Downvote
Sorry. I mis-understood the question. [blush]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
No need to sorry :)
I hardly understood it also :)

Borislav Borissov
 
Upvote 0 Downvote
I think this modification might work...

Code: 
DECLARE @BeginDate datetime
DECLARE @EndDate datetime

SET @BeginDate = '04/01/2006'
SET @EndDate = '04/15/2006'

Select Open_Date,
       Sum(Case 
              When OpenDate <= @EndDate 
                   and CloseDate >= @BeginDate Then 1 Else 0 
           End) As BugCount
From   BugTrackingTable
Group By OpenDate
Order By OpenDate
 
Upvote 0 Downvote
No, it didn't works. It counts bugs correctly but did not insert records for these dates that are missing in the file :-(

Borislav Borissov
 
Upvote 0 Downvote
When you say "did not insert records for these dates that are missing in the file", what do you mean? What missing records? Are you talking about the NULLS?
 
Upvote 0 Downvote
Code: 
DECLARE @BeginDate datetime
DECLARE @EndDate datetime

SET @BeginDate = '04/01/2006'
SET @EndDate = '04/15/2006'

Select Open_Date,
       Sum(Case 
              When OpenDate <= @EndDate 
                   and isnull(CloseDate,getdate()) >= @BeginDate Then 1 Else 0 
           End) As BugCount
From   BugTrackingTable
Group By OpenDate
Order By OpenDate
 
Upvote 0 Downvote
Nope, but did you see the example data and desired result?
The table has only one record for Apr. 2006:
14 07.04.2006 NULL

but I want all dates between that period to show :-(
and only them, not with open dates before 04/01 and after 04/15

Borislav Borissov
 
Upvote 0 Downvote
Btw. you said "... but the answer was "Not permited""... plz explain?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)
[banghead]
 
Upvote 0 Downvote
The man who asks me that question when I send my answer with temporary table said to me that creation of table is not allowed :-(. I don't know why!

Borislav Borissov
 
Upvote 0 Downvote
OK, I'll try :eek:)))
Thank you all for your effords.

Borislav Borissov
 
Upvote 0 Downvote
In the meantime... How about table @variable instead?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)
[banghead]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Cpreston
  • Locked
  • Question
Group results into one row 3
2
Replies
37
Views
2K
Chris Miller
Chris Miller
SashaBuilder3
  • Locked
  • Question
select a single row based on conditions in multiple rows 1
Replies
3
Views
860
SashaBuilder3
SashaBuilder3
peac3
  • Locked
  • Question
split one row into multiple rows... help pls 2
Replies
12
Views
1K
Chris Miller
Chris Miller
leifoet
  • Locked
  • Question
Error in Select query
Replies
14
Views
830
Andrzejek
Andrzejek
grnzbra
  • Locked
  • Question
SELECT TOP 1 of Each of a Group
Replies
5
Views
561
grnzbra
grnzbra

Part and Inventory Search

Sponsor

Back
Top