[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Help on SQL server query

Posted on 2009-07-01
3
Medium Priority
?
226 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this article I will describe the Backup & Restore 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.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

650 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