Solved

Dynamic Group By in SQL Server

Posted on 2014-10-13
11
210 Views
Last Modified: 2014-10-14
I currently have a stored procedure that returns 12 fields from a combination of tables in my SQL Server database.  This table contains data for every day of production for over 1000 wells/facilities, over about 8 years.  I currently pass the stored procedure values for DSID, FieldID, Well_ID, StartDate and EndDate, and it returns individual records for each day for each well in list of wells that meet the criteria.  

I want to modify this query, so that it will return the same fields, but so that the Oil, Gas, and Water fields are summed over a particular period (either by month, quarter, or year), still within the data range determined by StartDate and EndDate.  The stored procedure currently looks like:

ALTER PROCEDURE [dbo].[df_Analysis_Production_by_Well_Facility]

	@DSID integer,
	@FieldID integer, 
	@EntityID integer,
	@StartDate DateTime, 
	@EndDate DateTime

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

SET @DSID = ISNULL(@DSID, 0)
SET @FieldID = IsNull(@FieldID, 0)
SET @EntityID = ISNULL(@EntityID, 0)

SELECT WF.DS_ID
, DS.DS_Description AS Data_Source
, WF.FieldID
, WF.FieldName
, P.Entity_ID
, WF.Entity_Name AS Well_Fac_Name
, WF.Bolo_ID
, WF.Entity_Type_Desc AS Well_Fac_Type
, P.docDate
, P.Oil
, P.Gas
, P.Water
FROM vw_Wells_and_Facilities_All as WF
RIGHT JOIN tbl_sysProduction as P ON WF.Entity_ID = P.Entity_ID
LEFT JOIN lst_Data_Sources as DS ON WF.DS_ID = DS.DS_ID
WHERE ((@DSID = 0) OR (WF.DS_ID = @DSID))
AND ((@FieldID = 0) OR (WF.FieldID = @FieldID))
AND ((@EntityID = 0) OR (P.Entity_ID = @EntityID))
AND ((@StartDate IS NULL) OR (P.docDate >= @StartDate))
AND ((@EndDate IS NULL) OR (P.docDate < DATEADD(day, 1, @EndDate)))
ORDER BY WF.FieldName, WF.Entity_Name, P.docDate

END

Open in new window

How can I modify this so that I can pass in an additional parameter and have it group accordingly.  I assume I'm going to have to modify the query so that it builds a dynamic SQL string and then execute that from within the SP.  I know how I would do this in Access, using the Format( ) function with the docDate and a format string that either looks like "yyyy", "yy-q", or "yy-mm", but I'm not sure how to go about that in SQL Server.
0
Comment
Question by:Dale Fye (Access MVP)
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40378996
SQL Server CASE Solutions, scroll down to 'CASE can also be used in the ORDER BY clause'.

>pass in an additional parameter and have it group accordingly.
Define 'group accordingly', not getting that.

An example..
ALTER PROC get_names (@SortColumnName varchar(50)) AS

SELECT name, group_id, is_active
FROM some_table
ORDER BY 
   -- First sort position
   CASE 
      WHEN @SortColumnName='name' THEN name
      WHEN @SortColumnName='group_id' THEN CAST(group_id as varchar(max)) END,
	-- Second sort position
   CASE 
      WHEN @SortColumnName='name' THEN CAST(group_id as varchar(max))
      WHEN @SortColumnName='group_id' THEN name END
GO

Open in new window

0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 40379034
Jim,

group accordingly:  depending on how which value I pass in, I either want to group the data year, quarter, month, or day and sum the Oil, Gas, and Water values over those periods.

In Access, instead of displaying docDate in the SELECT clause, I would use Format(docDate, "yy-mm-dd"), or Format(docDate, "yy-mm") or Format(docDate, "yy-q") or Format(docDate, "yy"), and then SUM() on each of the Oil, Gas, and Water fields.  Then I would need to create a GroupBy clause with all of the other fields, and this same Format() statement.  

So, I'm just not sure how to do the Format() conversion in SQL Server based on a passed in parameter.

From your example above, I'm guessing that I won't need to create a string dynamically, I can simply use a Case statement within the SELECT and the Group By clause.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40379062
when using a case expression the result data type will need to be consistent. This might result in issues (e.g. having to sort by date, via a varchar) so it some circumstances just using NULL can be helpful. e.g.

  case when @SortColumnName='name' THEN name ELSE NULL END
, case when @SortColumnName='group_id' THEN group_id ELSE NULL END
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 65

Expert Comment

by:Jim Horn
ID: 40379085
Still not getting it, but sleep deprivation is likely in play here, and since I'm going to bed and it's Paul's morning on Kiwi Time he can pick it up from here.

I'll throw this out as a last grasp...

>I would use Format(docDate, "yy-mm-dd"), or Format(docDate, "yy-mm") or Format(docDate, "yy-q") or Format(docDate, "yy")
>Then I would need to create a GroupBy clause with all of the other fields, and this same Format() statement.  
Let's make it easy on ourselves, avoid the dynamic part entirely, and pass the query off with the above date expression as a subquery, and then just group on it in the main query, so we only have to do the above expression once.  Something like <total air code>

SELECT a.goo, a.foo, a.boo, a.date_part, SUM(some_total), COUNT(some_count) 
FROM (
SELECT goo, foo, boo, some_total, some_count
Case @par 
   WHEN 'ymd' THEN Format(docDate, "yy-mm-dd")
   WHEN 'ym' THEN Format(docDate, "yy-mm") 
   WHEN 'yq' THEN Format(docDate, "yy-q") 
   WHEN 'y' THEN Format(docDate, "yy") END as date_part
FROM some_table) a
GROUP BY a.goo, a.foo, a.boo, a.date_part

Open in new window

0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 40379578
JIm,

The point is, there is no FORMAT( ) function in SQL Server 2008 R2.  I don't know what SQL SERVER function to call to make those transformations from a date into a string in the format of 'yy-mm-dd', 'yy-mm', 'yy-q', or 'yy'.

It doesn't look like they incorportated FORMAT() in SQL Server until 2012.
0
 
LVL 9

Accepted Solution

by:
Valliappan AN earned 300 total points
ID: 40379616
You may try use these:

select DatePart(QUARTER, mydate)

select DatePart(year, mydate)

SELECT CONVERT(VARCHAR(7), GETDATE(), 111) AS [YYYY/MM]
//have not tried above though.. from some other pages..
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 40379618
FYI, the links and comment that i posted before, was deleted by some admin, saying such links not allowed.
0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 200 total points
ID: 40379712
Yeah I just did a copy-paste on that one.
You can use a combination of DATEPART and Paul's article on SQL Server Date Styles (formats) using CONVERT()

SELECT convert(varchar, your_date_here ,20) gives you yy-mm-dd
SELECT RIGHT(CAST(DATEPART(year, your_date_here) as char(4)),2)  + '-' + RIGHT('0' + CAST(DATEPART(month, your_date_here) as varchar(2)), 2) gives you yy-mm

I'd give you more, but I'm at a gig where they haven't set me up with SQL yet, so I'll back out to encourage other experts to respond.
0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 40379738
Jim,

I've been playing around with this a bit since I found Paul's article.  Between that, and Valli_an's comments about date part, I've managed to get what I need, but it just amazes me how much work it takes.  You have to wonder why it took MS so long to incorporate this simple function (Format) into SQL Server.
0
 
LVL 47

Author Closing Comment

by:Dale Fye (Access MVP)
ID: 40379747
Thanks, all.

Paul,  why didn't you mention your article on Convert and SQL dates.  Took me a while to find it but that helped as well.

Dale
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40381049
"why didn't you mention your article on Convert and SQL dates."
I wasn't online at the right time :)

Anyway, pleased you found it, and that you found it useful.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question