Solved

Help on SQL server query

Posted on 2009-07-01
3
224 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 500 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

729 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