am trying to dynamically determine the number of Active Projects in a
given month dynamically for a give ProjectType.
(e.g. For June 2005, I would like to see all projects created on June
30, 2005 or before that have not yet closed grouped by Project Type.)
**Simplified DDL **
Projects
(
ProjectID
OpenDate,
ClosedDate,
ProjectType
)
SQL I already have that works that gives the number of projects opened
in a given month
======================
SELECT ProjectType,
cast(datename(month,opendate) as varchar(15)) [DateMonth],
--Guarantees Months of Diferent Years are not grouped together
Convert(datetime,datename(month,opendate
) + ' 1 ' +
datename(year,opendate),110) [OrderByDate],
count(*) ProjectsOpened
INTO ##temp
FROM Projects P
WHERE OpenDate >= @.BeginDate and OpenDate <= @.EndDate --Year
to retrieve Projects for
GROUP BY ProjectName,
cast(datename(month,opendate) as varchar(15)),
Convert(datetime,datename(month,opendate
) + ' 1 ' +
datename(year,opendate),110)
ORDER BY Convert(datetime,datename(month,opendat
e) + ' 1 ' +
datename(year,opendate),110),
ProjectType
I have a second query to retrieve the number of active projects at the
end of a particular month
==========================
SELECT ProjectType,Count(*) OpenProjectCount
FROM Projects P2
WHERE OpenDate <= dbo.EndOfMonth('01-May-2005')
AND (ClosedDate Is Null Or ClosedDate >
dbo.EndOfMonth('01-May-2005'))
GROUP BY ProjectType
What I can't Figure Out is how to join the two queries so that where
you see '01-May-2005', I can pass the
" Convert(datetime,datename(month,opendate
) + ' 1 ' +
datename(year,opendate),110)" value and return the Active Projects for
each particular month in the year span.
RESULTSET SHOULD BE
ProjectType DateMonth OrderByDate Projec
tsOpened ActiveProjects
1 Jan 01-Jan-2005 5 9
1 Feb 01-Feb-2005 3 7
NOTE
-====
I have simplified the DDL for the example, but the gist of what I need
remains the same
Sorry about the google post and any formatting issues, but the company
I work for doesn't allow outlook express to be used.
L Anthony JohnsonYou need to use derived to combine them within one query. Since the join
happens prior to the group, I am going to join 2 derived tables. I made it
a
bit more robust by doing a full outer join between the two tables, this will
handle cases for months that have active but no open and vice versa. This
results in all the ISNULLs in the SELECT.
I am going to modify the second query slightly to use @.EndDate instead of
the function, since it seems like you have this. Also, this query needs to
return its month to join by. Hopefully this will give you a rough idea of
what you can do.
Something like this would work (although I didn't test it out)(Sorry about
the formatting)
DECLARE @.BeginDate as DateTime
DECLARE @.EndDate as DateTime
SET @.BeginDate = '6/1/2005'
SET @.EndDate = '6/30/2005'
SELECT ISNULL(derived_table1.ProjectType, derived_table2.ProjectType) as
ProjectType
ISNULL(derived_table1.GroupByDate, derived_table2.GroupByDate) as
OrderByDate,
ISNULL(derived_table1.OpenProjects, 0) as ProjectsOpened,
ISNULL(derived_table.ActiveProjects,0) as ActiveProjects
FROM (
SELECT ProjectType,
Convert(datetime,datename(month,opendate
) + ' 1 '
+datename
(year,opendate),110) as GroupByDate,
count(*) as OpenProjects,
FROM Projects P
WHERE OpenDate >= @.BeginDate and OpenDate <= @.EndDate
GROUP BY ProjectType,
Convert(datetime,datename(month,opendate
) + ' 1 ' +
datename
(year,opendate),110)
) derived_table1 -- table that gives us project type and open count per mont
h
FULL OUTER JOIN
( SELECT ProjectType,
@.BeginDate as GroupByDate,
count(*) as ActiveProjects,
FROM Projects P
WHERE ClosedDate IS NULL OR ClosedDate > @.EndDate
GROUP BY ProjectType
) derived_table2 -- table that gives us project type and active as of end of
month
-- join derived tables together on both type and date (which is always first
of month)
ON derived_table1.ProjectType = derived_table2.ProjectType
AND derived_table1.GroupByDate = derived_table2.GroupByDate
ORDER BY
derived_table1.GroupByDate,
derived_table1.ProjectType
HTH
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"LJohnsonNG@.gmail.com" wrote:
> am trying to dynamically determine the number of Active Projects in a
> given month dynamically for a give ProjectType.
> (e.g. For June 2005, I would like to see all projects created on June
> 30, 2005 or before that have not yet closed grouped by Project Type.)
> **Simplified DDL **
> Projects
> (
> ProjectID
> OpenDate,
> ClosedDate,
> ProjectType
> )
> SQL I already have that works that gives the number of projects opened
> in a given month
> ======================
> SELECT ProjectType,
> cast(datename(month,opendate) as varchar(15)) [DateMonth],
> --Guarantees Months of Diferent Years are not grouped together
> Convert(datetime,datename(month,open
date) + ' 1 ' +
> datename(year,opendate),110) [OrderByDate],
> count(*) ProjectsOpened
> INTO ##temp
> FROM Projects P
> WHERE OpenDate >= @.BeginDate and OpenDate <= @.EndDate --Year
> to retrieve Projects for
> GROUP BY ProjectName,
> cast(datename(month,opendate) as varchar(15)),
> Convert(datetime,datename(month,open
date) + ' 1 ' +
> datename(year,opendate),110)
> ORDER BY Convert(datetime,datename(month,opendat
e) + ' 1 ' +
> datename(year,opendate),110),
> ProjectType
>
> I have a second query to retrieve the number of active projects at the
> end of a particular month
> ==========================
> SELECT ProjectType,Count(*) OpenProjectCount
> FROM Projects P2
> WHERE OpenDate <= dbo.EndOfMonth('01-May-2005')
> AND (ClosedDate Is Null Or ClosedDate >
> dbo.EndOfMonth('01-May-2005'))
> GROUP BY ProjectType
> What I can't Figure Out is how to join the two queries so that where
> you see '01-May-2005', I can pass the
> " Convert(datetime,datename(month,opendate
) + ' 1 ' +
> datename(year,opendate),110)" value and return the Active Projects for
> each particular month in the year span.
>
> RESULTSET SHOULD BE
>
> ProjectType DateMonth OrderByDate Projec
tsOpened ActiveProjects
> 1 Jan 01-Jan-2005 5 9
> 1 Feb 01-Feb-2005 3 7
> NOTE
> -====
> I have simplified the DDL for the example, but the gist of what I need
> remains the same
> Sorry about the google post and any formatting issues, but the company
> I work for doesn't allow outlook express to be used.
> L Anthony Johnson
>|||(LJohnsonNG@.gmail.com) writes:
> What I can't Figure Out is how to join the two queries so that where
> you see '01-May-2005', I can pass the
> " Convert(datetime,datename(month,opendate
) + ' 1 ' +
> datename(year,opendate),110)" value and return the Active Projects for
> each particular month in the year span.
I think this query takes you closer to the final goal:
SELECT A.ProjectType, A.YearMonth, A.ProjectsOpened,
(SELECT COUNT(*)
FROM Projects B
WHERE B.OpenDate < dateadd(MONTH, 1, A.YearMonth + '01')
AND (B.CloseDate IS NULL OR
B.CloseDate >= dateadd(MONTH, 1, A.YearMonth + '01')))
AS ActiveProjects
FROM (SELECT ProjectType, YearMonth = convert(char(6), OpenDate, 112),
count(*) ProjectsOpened
FROM Projects
WHERE OpenDate >= @.BeginDate and OpenDate <= @.EndDate
GROUP BY ProjectType, convert(char(6), OpenDate, 112)) AS A
ORDER BY A.ProjectType, A.YearMonth
I'm using a derived table, to encapsulate the inner query. A derived
table is sort of a temptable within the query, but is never materialised.
This is a very powerful tool in SQL.
I took the liberty to change the date formatting a bit, to not get
drowned in those issues. By nesting more derived tables, you can
get the format you want.
Since you did not supply sample data, this is untested.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland's solution is better than mine.
I realized that I failed to provide something that would work for more than
1 month. The challenge was that your original query2 really needs to be
reexecuted for each month returned by query1. So, my solution does not
satisify that.
Erland's solution utilized a sub-select for query 2 in order to accomplish
this. That would do the trick. You could also use the new CROSS APPLY in
SQL 2005 to accomplish this type of thing.
The one problem with Erland's solution is that I think his solution would
not correctly group all projects for the month into one row for that month.
I think if you change his use of convert(char(6), OpenDate, 112) back into
something like you were originally doing to change all entries from the same
month into the first of that month prior to grouping.
Sorry if I misled you. I did not quite grasp the whole problem originally.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Erland Sommarskog" wrote:
> (LJohnsonNG@.gmail.com) writes:
> I think this query takes you closer to the final goal:
> SELECT A.ProjectType, A.YearMonth, A.ProjectsOpened,
> (SELECT COUNT(*)
> FROM Projects B
> WHERE B.OpenDate < dateadd(MONTH, 1, A.YearMonth + '01')
> AND (B.CloseDate IS NULL OR
> B.CloseDate >= dateadd(MONTH, 1, A.YearMonth + '01'))
)
> AS ActiveProjects
> FROM (SELECT ProjectType, YearMonth = convert(char(6), OpenDate, 112
),
> count(*) ProjectsOpened
> FROM Projects
> WHERE OpenDate >= @.BeginDate and OpenDate <= @.EndDate
> GROUP BY ProjectType, convert(char(6), OpenDate, 112)) AS A
> ORDER BY A.ProjectType, A.YearMonth
> I'm using a derived table, to encapsulate the inner query. A derived
> table is sort of a temptable within the query, but is never materialised.
> This is a very powerful tool in SQL.
> I took the liberty to change the date formatting a bit, to not get
> drowned in those issues. By nesting more derived tables, you can
> get the format you want.
> Since you did not supply sample data, this is untested.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Why did you not post REAL DDL instead of making other people do it and
have to create their own sample data to help you? Is this what you
would have done, if you cared about us?
CREATE TABLE Projects
(project_id INTEGER NOT NULL PRIMARY KEY,
open_date DATETIME NOT NULL,
close_date DATETIME, -- null is still open
CHECK (open_date < close_date),
project_type CHAR(1) NOT NULL
CHECK (project_type IN ('a', 'b', ..))
);
dynamically for a give project_type.<<
Create a Calendar table; this is a standard SQL programming that you can
Google. The best way to do this is with a procedure. You need a
month-year which you want to keep in a temproal datatype -- I picked the
first day of a month -- so you can do math on it.
CREATE TABLE Calendar
(cal_date DATETIME NOT NULL PRIMARY KEY,
cal_year_month DATETIME NOT NULL,-- first day of month
.);
CREATE PROCEDURE MonthlyProjectTypeTally
(@.my_cal_year_month_open DATETIME,
@.my_cal_year_month_close DATETIME,
@.my_project_type CHAR(1))
AS
SELECT C1.cal_year_month,
@.my_project_type,
COUNT(DISTINCT project_id) AS project_tally
FROM Calendar AS C1, Projects AS P1
WHERE P1.project_type = @.my_project_type
AND C1.cal_date BETWEEN P1.open_date
AND COALESCE (P1.close_date, CURRENT_TIMESTAMP)
GROUP BY P1.project_type, C1.cal_year_month;
Avoid proprietary and expensive temporal computations with auxiliary
tables that will be useful in other queries.
--CELKO--
Please post DDL in a human-readable format and not a machine-generated
one. This way people do not have to guess what the keys, constraints,
DRI, datatypes, etc. in your schema are. Sample data is also a good
idea, along with clear specifications.
*** Sent via Developersdex http://www.examnotes.net ***|||> WHERE OpenDate >= @.BeginDate and OpenDate <= @.EndDate
Erland, I like your solution, but I have some reservations.
What if a projected opened on 15-jun-2005 and closed on 15-nov-2005,
and @.BeginDate=20050801 and @.EndDate=20050831? The project will surely
be open in Agust 2005, but will it mett the criteria OpenDate >=
@.BeginDate and OpenDate <= @.EndDate ? I don't think so.
I would suggest this criteria (assuming CLoseDate is not null):
( OpenDate >= @.BeginDate and OpenDate <= @.EndDate)
or
(@.BeginDate between OpenDate and CloseDate)
or
(@.EndDate between OpenDate and CloseDate)
What do you think?|||--CELKO-- (remove.jcelko212@.earthlink.net) writes:
> Avoid proprietary and expensive temporal computations with auxiliary
> tables that will be useful in other queries.
Yeah, that's good. Avoid anything that can be useful. Go with Celko's
useless solutions instead.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Alexander Kuznetsov (AK_TIREDOFSPAM@.hotmail.COM) writes:
> Erland, I like your solution, but I have some reservations.
> What if a projected opened on 15-jun-2005 and closed on 15-nov-2005,
> and @.BeginDate=20050801 and @.EndDate=20050831? The project will surely
> be open in Agust 2005, but will it mett the criteria OpenDate >=
> @.BeginDate and OpenDate <= @.EndDate ? I don't think so.
> I would suggest this criteria (assuming CLoseDate is not null):
> ( OpenDate >= @.BeginDate and OpenDate <= @.EndDate)
> or
> (@.BeginDate between OpenDate and CloseDate)
> or
> (@.EndDate between OpenDate and CloseDate)
> What do you think?
That criteria was from the original post, and that query was to display
projects *opened* during the period. Thus your example project were not to
to be listed as opened.
But I now realise that there is a flaw in my query: if there are no
projects at opened at all during a month, then that month would not
be displayed. Note that since project still may have been closed
during that month, the number of active projects could change from
the month before.
The best way to handle this, would be to use a month table (or some
sort of calendar table). We'll see if LJohnson comes back add says
what he thinks of the suggested solution. Who knows, maybe we even
get some test data to work with. :-)
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I'm coming into this thread a little late, but give this a try. This is a
stored procedure that takes a project type as an input and produces a
calendar list of the number of projects opened in that month, along with the
active projects.
I made the assumption that the datatype for ProjectType is int; the
following procedure would need to be modified if it wasn't.
CREATE PROCEDURE showProjects @.ProjectType int
AS
DECLARE @.minmonth int
DECLARE @.maxopen int
DECLARE @.maxclosed int
DECLARE @.maxmonth int
DECLARE @.numActiveProjects int
DECLARE @.ActiveProjects table (ProjectMonth datetime, NumberActiveProjects
int)
/* Get the month that the earliest project was opened in. */
SELECT @.minmonth = MIN(DATEDIFF(mm, 0, OpenDate)) FROM Projects
/* Since ClosedDate can be null, it's possible that
the latest date in the table will be an OpenDate. Check to see whether
OpenDate or Closed date has the latest date */
SELECT @.maxopen = MAX(DATEDIFF(mm, 0, OpenDate)) FROM Projects
SELECT @.maxclosed = MAX(DATEDIFF(mm, 0, ClosedDate)) FROM Projects
SELECT @.maxmonth = CASE WHEN @.maxopen >= @.maxclosed THEN @.maxopen ELSE
@.maxclosed END
WHILE (@.minmonth <= @.maxmonth)
BEGIN
SELECT @.numActiveProjects = Count(*)
FROM Projects P2
WHERE OpenDate <= dbo.EndOfMonth(DATEADD(mm, @.minmonth, 0))
AND (ClosedDate Is Null Or ClosedDate > dbo.EndOfMonth(DATEADD(mm,
@.minmonth, 0)))
AND ProjectType = @.ProjectType
INSERT INTO @.ActiveProjects VALUES(DATEADD(mm, @.minmonth, 0),
@.numActiveProjects)
SET @.minmonth = @.minmonth + 1
END
/* Join the results together to get both number of projects started in month
and number of active projects during that month */
SELECT A.ProjectMonth, ISNULL(t1.NumProjectsOpened, 0) AS "ProjectsOpened",
A.NumberActiveProjects
FROM @.ActiveProjects A
LEFT JOIN (SELECT DATEADD(mm, DATEDIFF(mm, 0, OpenDate), 0) AS ProjectMonth,
COUNT(*) AS NumProjectsOpened
FROM [Projects]
WHERE ProjectType = @.ProjectType
GROUP BY DATEADD(mm, DATEDIFF(mm, 0, OpenDate), 0)
) t1
ON A.ProjectMonth = t1.ProjectMonth
The results will be in the following format
ProjectMonth ProjectsOpened NumberActiveProjects
2005-01-01 3 3
2005-02-01 2 5
You can play around with the formatting of the date, but this way is pretty
readable as well.
"LJohnsonNG@.gmail.com" wrote:
> am trying to dynamically determine the number of Active Projects in a
> given month dynamically for a give ProjectType.
> (e.g. For June 2005, I would like to see all projects created on June
> 30, 2005 or before that have not yet closed grouped by Project Type.)
> **Simplified DDL **
> Projects
> (
> ProjectID
> OpenDate,
> ClosedDate,
> ProjectType
> )
> SQL I already have that works that gives the number of projects opened
> in a given month
> ======================
> SELECT ProjectType,
> cast(datename(month,opendate) as varchar(15)) [DateMonth],
> --Guarantees Months of Diferent Years are not grouped together
> Convert(datetime,datename(month,open
date) + ' 1 ' +
> datename(year,opendate),110) [OrderByDate],
> count(*) ProjectsOpened
> INTO ##temp
> FROM Projects P
> WHERE OpenDate >= @.BeginDate and OpenDate <= @.EndDate --Year
> to retrieve Projects for
> GROUP BY ProjectName,
> cast(datename(month,opendate) as varchar(15)),
> Convert(datetime,datename(month,open
date) + ' 1 ' +
> datename(year,opendate),110)
> ORDER BY Convert(datetime,datename(month,opendat
e) + ' 1 ' +
> datename(year,opendate),110),
> ProjectType
>
> I have a second query to retrieve the number of active projects at the
> end of a particular month
> ==========================
> SELECT ProjectType,Count(*) OpenProjectCount
> FROM Projects P2
> WHERE OpenDate <= dbo.EndOfMonth('01-May-2005')
> AND (ClosedDate Is Null Or ClosedDate >
> dbo.EndOfMonth('01-May-2005'))
> GROUP BY ProjectType
> What I can't Figure Out is how to join the two queries so that where
> you see '01-May-2005', I can pass the
> " Convert(datetime,datename(month,opendate
) + ' 1 ' +
> datename(year,opendate),110)" value and return the Active Projects for
> each particular month in the year span.
>
> RESULTSET SHOULD BE
>
> ProjectType DateMonth OrderByDate Projec
tsOpened ActiveProjects
> 1 Jan 01-Jan-2005 5 9
> 1 Feb 01-Feb-2005 3 7
> NOTE
> -====
> I have simplified the DDL for the example, but the gist of what I need
> remains the same
> Sorry about the google post and any formatting issues, but the company
> I work for doesn't allow outlook express to be used.
> L Anthony Johnson
>|||On 5 Jan 2006 19:33:49 -0800, Alexander Kuznetsov wrote:
>Erland, I like your solution, but I have some reservations.
>What if a projected opened on 15-jun-2005 and closed on 15-nov-2005,
>and @.BeginDate=20050801 and @.EndDate=20050831? The project will surely
>be open in Agust 2005, but will it mett the criteria OpenDate >=
>@.BeginDate and OpenDate <= @.EndDate ? I don't think so.
Hi Alexander,
I understand that it's not really relevant to the question originally
posed in this thread, but since situations like the one you describe
come up quite common, I'll chime in anyway.
>I would suggest this criteria (assuming CLoseDate is not null):
>( OpenDate >= @.BeginDate and OpenDate <= @.EndDate)
>or
>(@.BeginDate between OpenDate and CloseDate)
>or
>(@.EndDate between OpenDate and CloseDate)
>What do you think?
A better way to test for any overlap between two periods (OpenDate to
CloseDate vs @.BeginDate to @.EndDate) is
WHERE OpenDate < @.EndDate
AND CloseDate > @.BeginDate
This is a lot shorter, and it's completely sargable (which means that
SQL Server can use index s
if an index on either or both of these
columns is defined).
Hugo Kornelis, SQL Server MVP
Lisa Nicholls wrote:
.. I am sorry to reply late.. I was trying out lisa's solution.