• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 228
  • Last Modified:

Help on SQL server query

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
gridline
Asked:
gridline
1 Solution
 
ralmadaCommented:
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
 
8080_DiverCommented:
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
 
gridlineAuthor Commented:
this is exactly what i needed...thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now