|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Dynamic SQL Column Total Helpgiven 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,opendate) + ' 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 ProjectsOpened 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 You 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 month 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 Show quote "LJohnso***@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,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,opendate) + ' 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 ProjectsOpened 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 > > (LJohnso***@gmail.com) writes:
> What I can't Figure Out is how to join the two queries so that where I think this query takes you closer to the final goal:> 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. 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, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/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. Show quote "Erland Sommarskog" wrote: > (LJohnso***@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, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > > 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? Alexander Kuznetsov (AK_TIREDOFSPAM@hotmail.COM) writes:
Show quote >> WHERE OpenDate >= @BeginDate and OpenDate <= @EndDate That criteria was from the original post, and that query was to display> > 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? 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, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx On 5 Jan 2006 19:33:49 -0800, Alexander Kuznetsov wrote:
>> WHERE OpenDate >= @BeginDate and OpenDate <= @EndDate Hi Alexander,> >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 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. > A better way to test for any overlap between two periods (OpenDate to>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? 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 seek if an index on either or both of these columns is defined). -- Hugo Kornelis, SQL Server MVP 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 determine the number of Active Projects in a given month 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.developersdex.com *** --CELKO-- (remove.jcelko***@earthlink.net) writes:
> Avoid proprietary and expensive temporal computations with auxiliary Yeah, that's good. Avoid anything that can be useful. Go with Celko's> tables that will be useful in other queries. useless solutions instead. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Why do you think that a Calendar table is useless? In particular, one
which has the first and last (business?) days of the year-month for each calendar date? I would think that the joins, which can be done in parallel with a small Calendar table in main storage, would be faster and far more portable than converting temporal types on a row-by-row basis to character strings. Furthermore, the Calendar table can be used in other queries, allows for odd holidays and unexpected non-business days (like a bombing), gets all the programmers using the same definitions, etc. >> 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; << do you think it portable? Do you think it's gonna work against Oracle? >> do you think it portable? Do you think it's gonna work against Oracle? << Portable is not the same as Standard. I can translate this SQL dialectinto SQL/PSM and to PL/SQL with a simple tool so that I maintain one code base. Pick the one you like. And it is portable! The trick with procedure language dialects is to keep the McCabe numbers as low as possible and avoid proprietary code -- same good practice as with 3GL programming. In the real world that just doesn't work.
You don't just use a tool to convert dialects, you need to test for different behaviours for a start between vendors. Portability is dead in the water, a fraction of a percentage point actually use or require portability; its far better to get value out of your investment so you get a faster, more efficient, more maintable solution for your businesss. You always without fail have to code more in order to 'portable', not that true portability exists. Show quote "--CELKO--" <jcelko***@earthlink.net> wrote in message news:1136688124.697045.24350@o13g2000cwo.googlegroups.com... >>> do you think it portable? Do you think it's gonna work against Oracle? >>> << > > Portable is not the same as Standard. I can translate this SQL dialect > into SQL/PSM and to PL/SQL with a simple tool so that I maintain one > code base. Pick the one you like. And it is portable! > > The trick with procedure language dialects is to keep the McCabe > numbers as low as possible and avoid proprietary code -- same good > practice as with 3GL programming. > 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. -- Show quote"LJohnso***@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,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,opendate) + ' 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 ProjectsOpened 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 > >  
Other interesting topics
|
|||||||||||||||||||||||
