Solved

Help on SQL server query

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
In this article I will describe the Copy Database Wizard 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.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

705 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

19 Experts available now in Live!

Get 1:1 Help Now