Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Using one table with more than one search criteria

Author  Topic 

pwynne33
Starting Member

13 Posts
Posted - 2004-09-16 : 05:58:13
Username - Total Number Of Applications with user assigned to (Count) - Total Number Dealt With, with user assigned to (count) - Num Apps Assigned to user (Count) - Number Dealt With (Count)

This will be grouped by each user.

All this data comes from one table.

I've tried adding the same table to the query four times with different criteria for each (virtual?) table but I'll only get results for the original table. (hope thats not too confusing?)

Any ideas? Thanks.

mwjdavidson
Aged Yak Warrior

735 Posts
Posted - 2004-09-16 : 06:18:42
It sounds like you need a crosstab. Do a search on this site for relevant info. If this doesn't help, post back with the DDL for your table and some sample data.

Mark
Go to Top of Page

pwynne33
Starting Member

13 Posts
Posted - 2004-09-16 : 06:22:50
Cheers
Go to Top of Page

pwynne33
Starting Member

13 Posts
Posted - 2004-09-16 : 07:22:05
I'm doing this in SQL so there's no crosstab/query wizard in available.
Go to Top of Page

mwjdavidson
Aged Yak Warrior

735 Posts
Posted - 2004-09-16 : 07:30:06
Yes, but there's ways of achieving this in T-SQL if this is indeed what you want to do.

Mark
Go to Top of Page

pwynne33
Starting Member

13 Posts
Posted - 2004-09-16 : 07:31:20
This is what I've got so far which is right in theory (although it doesn't work).

I've added the same table four times to the query but this mustn't be the right method.

SELECT     @Date_From AS dtmFrom, @Date_To AS dtmTo, dbo.tblApplications.FRFTier AS Name, COUNT(tblApplications_1.FRFTier) AS TotalNumDealt, 
                      COUNT(dbo.tblApplications.FRFTier) AS TotalNumAssigned, COUNT(tblApplications_2.FRFTier) AS NumDealt, COUNT(tblApplications_3.FRFTier) 
                      AS NumAssigned
FROM         dbo.tblApplications INNER JOIN
                      dbo.tblApplications tblApplications_1 ON dbo.tblApplications.intApplicationID = tblApplications_1.intApplicationID INNER JOIN
                      dbo.tblApplications tblApplications_2 ON dbo.tblApplications.intApplicationID = tblApplications_2.intApplicationID INNER JOIN
                      dbo.tblApplications tblApplications_3 ON dbo.tblApplications.intApplicationID = tblApplications_3.intApplicationID
WHERE     ({ fn LENGTH(dbo.tblApplications.FRFTier) } > 2) AND (dbo.tblApplications.dtmApplicationDate >= @Date_From) AND 
                      (dbo.tblApplications.dtmApplicationDate <= @Date_To + 1) AND (tblApplications_1.dtmApplicationDate >= @Date_From) AND 
                      (tblApplications_1.dtmApplicationDate <= @Date_To + 1) AND (tblApplications_2.dtmApplicationDate >= @Date_From) AND 
                      (tblApplications_2.dtmApplicationDate <= @Date_To + 1) AND (tblApplications_3.dtmApplicationDate >= @Date_From) AND 
                      (tblApplications_3.dtmApplicationDate <= @Date_To + 1) AND ({ fn LENGTH(tblApplications_1.FRFTier) } > 2) AND (tblApplications_3.FRFTier <> 'None') 
                      AND (tblApplications_3.FRFTier <> 'Failed') AND (tblApplications_2.FRFTier <> 'None') AND (tblApplications_2.FRFTier <> 'Failed') AND 
                      (tblApplications_1.FRFTier <> 'None') AND (tblApplications_1.FRFTier <> 'Failed') AND (dbo.tblApplications.FRFTier <> 'None') AND 
                      (dbo.tblApplications.FRFTier <> 'Failed') AND (tblApplications_1.strStatus = N'Paid Out') AND ({ fn LENGTH(tblApplications_2.FRFTier) } > 2) AND 
                      ({ fn LENGTH(tblApplications_3.FRFTier) } > 2) AND ({ fn LENGTH(dbo.tblApplications.FRFTier) } > 2) AND (tblApplications_2.strStatus = N'Paid Out') OR
                      (tblApplications_1.strStatus LIKE N'Orig%') AND (tblApplications_2.strStatus LIKE N'Orig%')
GROUP BY dbo.tblApplications.FRFTier
Go to Top of Page

mwjdavidson
Aged Yak Warrior

735 Posts
Posted - 2004-09-16 : 07:40:21
Can you post your DDL for each of these tables along with some DML to generate a few rows of example data and a brief explanation.

Mark
Go to Top of Page

pwynne33
Starting Member

13 Posts
Posted - 2004-09-16 : 08:39:31
dtmFrom dtmTo Name TotalNumDealt TotalNumAssigned NumDealt NumAssigned
01/08/2004 17/08/2004 Carole B 12 12 12 12
01/08/2004 17/08/2004 Kate S 19 19 19 19
01/08/2004 17/08/2004 Mark S 4 4 4 4
01/08/2004 17/08/2004 Mel W 12 12 12 12
01/08/2004 17/08/2004 Paul H 10 10 10 10
01/08/2004 17/08/2004 Zoe S 28 28 28 28

For some reason the 4th to 7th columns are all displayin the same values when that value should be the value in the 7th column.

Is that the info that you wanted?
Go to Top of Page

mwjdavidson
Aged Yak Warrior

735 Posts
Posted - 2004-09-16 : 09:05:02
What we need is something like:

CREATE TABLE tblApplications (intApplicationID INT, etc.)
INSERT INTO tblApplications VALUES (1, etc.)
INSERT INTO tblApplications VALUES (2, etc.)

This will allow us to create the objects and populate them with some sample data.
You'll find you'll get assistance a lot quicker that way!
N.B. You can script your table out by right-clicking it in Query Analyser and Selecting 'Script Object To ClipBoard AS --> Create.

Mark
Go to Top of Page

pwynne33
Starting Member

13 Posts
Posted - 2004-09-16 : 09:27:55
Another method I've thought about is creating functions to do get the answer.

Username - Total Number Of Applications with user assigned to (Count) - #function#Total Number Dealt With, with user assigned to (count) - #function#Num Apps Assigned to user (Count) - #function#Number Dealt With (Count)

For the individual stats I could pass the username value to the function.

Thoughts?
Go to Top of Page

pwynne33
Starting Member

13 Posts
Posted - 2004-09-16 : 10:03:00
With refence to the functions post above...

I can get it working for the overall counting of records and the overall counting of records with xyz, but the user name is being carried across for the individual stats.

Date From - Date To - User - Total Number of Apps - Total Number of Dealt Apps - IndividualTotal - IndividualDealtTotal

produces...

Date - Date - Username - 378 (correct) - 52 (correct) - 0 (incorrect) - 0 (incorrect)

This is the function I'm using to get the individual total stats. I've taken the select from where from from an SP that produced the correct result.

ALTER FUNCTION dbo.fnReport_MonthlyBusinessReviewDealCount
	(
	@Date_From datetime,
	@Date_To datetime,
            @Name varchar
	)
RETURNS int
AS	
BEGIN			
DECLARE @COUNT int
SET @COUNT = (SELECT     COUNT(*) AS NumAssigned
FROM         dbo.tblApplications
WHERE     (dtmApplicationDate >= @Date_From) AND (dtmApplicationDate <= @Date_To + 1) AND (FRFTier = @Name))
RETURN @COUNT
END
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site