Solved

Help on SQL server query

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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

733 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