Solved

Help on SQL server query

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

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…
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.
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …

919 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now