?
Solved

Help on SQL server query

Posted on 2009-07-01
3
Medium Priority
?
225 Views
Last Modified: 2012-05-07
I am trying to create a report on a db using SQL server and the code below is where i'm at. What i would like to add is another Alias field which would contain a number range within my group of documents. This # range exist in the "Beg Bates" and "End Bates" fields within the tblUserData.


SELECT dbo.tblDocuments.Volume,  COUNT(*) AS num_vols
, dbo.tblUserData.Custodian, COUNT(*) AS num_cust
, dbo.tblDocuments.[Date Added]
FROM dbo.tblDocuments INNER JOIN dbo.tblUserData ON dbo.tblDocuments.DocGUID = dbo.tblUserData.MasterGUID
GROUP BY dbo.tblDocuments.Volume
, dbo.tblUserData.Custodian
, dbo.tblDocuments.[Date Added]

Open in new window

0
Comment
Question by:gridline
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 41

Accepted Solution

by:
ralmada earned 2000 total points
ID: 24759467
Something like this?
SELECT 	dbo.tblDocuments.Volume,  
	COUNT(*) AS num_vols
	, dbo.tblUserData.Custodian, 
	COUNT(*) AS num_cust
	, dbo.tblDocuments.[Date Added]
	,MAX(tblUserData.[Beg Bates])
	,MAX(tblUserData.[End Bates])
 
FROM dbo.tblDocuments 
INNER JOIN dbo.tblUserData ON dbo.tblDocuments.DocGUID = dbo.tblUserData.MasterGUID
 
GROUP BY dbo.tblDocuments.Volume
 
, dbo.tblUserData.Custodian
, dbo.tblDocuments.[Date Added]

Open in new window

0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24759985
Your request is a little ambiguous.  What do you mean by "which would contain a number range within my group of documents"?
SQL_1 shows how to add the columns to the initial query as simply additional columns.
SQL_2 shows how to combine the 2 columns into a single (Nmbr_Range) column .
 
I am still a little vague as to what you expect to actually get as your results.
 
Could you provide some sample expected output?

SQL_1:
 
SELECT D.Volume,  
       COUNT(*) AS num_vols, 
       U.Custodian, 
       COUNT(*) AS num_cust, 
       U.[Date Added]
       U.[End Bates]
FROM  dbo.tblDocuments D
INNER JOIN dbo.tblUserData U
   ON D.DocGUID = U.MasterGUID
GROUP BY D.Volume, 
         U.Custodian, 
         D.[Date Added],
         U.[Beg Bates],
         U.[End Bates];
 
 
 
SQL_2:
 
SELECT D.Volume,  
       COUNT(*) AS num_vols, 
       U.Custodian, 
       COUNT(*) AS num_cust, 
       D.[Date Added],
       'Range: [' + CONVERT(VARCHAR(6), [Beg Bates]) + '] - [' +
        CONVERT(VARCHAR(6), [End Bates]) + ']' AS Nmbr_Range
FROM  dbo.tblDocuments D
INNER JOIN dbo.tblUserData U
   ON D.DocGUID = U.MasterGUID
GROUP BY D.Volume, 
         U.Custodian, 
         D.[Date Added],
         'Range: [' + CONVERT(VARCHAR(6), [Beg Bates]) + '] - [' +
        CONVERT(VARCHAR(6), [End Bates]) + ']' ;

Open in new window

0
 

Author Closing Comment

by:gridline
ID: 31599015
this is exactly what i needed...thank you
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

771 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