SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 TSQL Hell - brain teaser
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

2revup
Posting Yak Master

102 Posts

Posted - 03/25/2013 :  16:26:27  Show Profile  Reply with Quote
Ok so let me clear up a few of the questions up. The reason I am doing the distinct is because I am doing a lookup on the corrapodance table this means there could be multiple case IDs so therefore I need to distinct the select or as pointed out on the one field. The only problem with this is if an when I try and add the date onto this then the distinct goes out the window unfortunately.

The reason I need the dates is I am trying to show this data in A report form using reporting services and i have date filters I need to be able to use.

Does this answer your questions if not let me know I will try and provide data to show you more

Thanks for all your help guys.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/26/2013 :  14:35:00  Show Profile  Reply with Quote
Nope..Not mine anyways

My question is if you make CseID distinct which date you want to show? You will not able to return all values unless you make them into a list. Otherwise you need to retrive any one date ie first, latest or random by applying aggregates like MIN(),MAX() etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

2revup
Posting Yak Master

102 Posts

Posted - 03/27/2013 :  02:19:58  Show Profile  Reply with Quote
Really I don't care on the date so much I just need a date, the dates are close together if not the same day they will likely be the next day. the only reason I need a date is to filter on.


Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 03/27/2013 :  11:32:52  Show Profile  Reply with Quote
1) I belive if you come up with a desired output based on your sample records (provided on 03/25/2013 : 07:07:57), that would be handy for the team to help you.
2) from a statement in your response on "03/25/2013 : 16:26:27", "The reason I need the dates is I am trying to show this data in A report form using reporting services and i have date filters I need to be able to use."
It shows that infact you want to apply "date filter" values on the date column so that you can have desired data out just for those records that falls in "between, greater, less than" of specific date(s)?

Cheers
MIK

Edited by - MIK_2008 on 03/27/2013 11:33:41
Go to Top of Page

2revup
Posting Yak Master

102 Posts

Posted - 03/27/2013 :  22:55:36  Show Profile  Reply with Quote
Hi Mik,

to number 1.

getting:

User1 001255788711 Supervisor1 SYD 2013-01-18 16:47:18.000
User1 001273569661 Supervisor1 SYD 2013-01-23 17:18:39.000
User2 001273569661 Supervisor2 SYD 2013-01-25 18:13:59.000
User2 001273569661 Supervisor2 SYD 2013-01-25 18:45:38.000

based on the query: select Distinct UserLogin, CaseID, Supervisor, Location, creation_date from Correspondences_All

Desired output would be
User1 001255788711 Supervisor1 SYD 2013-01-18 16:47:18.000
User1 001273569661 Supervisor1 SYD 2013-01-23 17:18:39.000
User2 001273569661 Supervisor2 SYD 2013-01-25 18:13:59.000

Note the only difference is I have only a distinct user not a repeat, so essentially I am saying give me only a DISTINCT user irrespective of if there are multiple dates

2. So I guess the best way to answer this is reference the end result I am trying to achieve. I have 2 queries that the forum kindly helped me formulate to which gives me counts and then avg the 2 counts. As below:

SELECT
ISNULL(ca2.Agent_Login, cr2.userLogin) as userlogin,
ISNULL(ca2.cases, 0) as cases,
ISNULL(cr2.Correspondence, 0) as Correspondence,
CAST(ISNULL(cr2.Correspondence, 0.0) AS FLOAT)/ca2.cases AS Corr_Av

FROM
(
SELECT ca.Agent_Login, COUNT(Agent_Login)as cases
FROM CaseDetails_AllReports ca
WHERE ca.Creation_Date BETWEEN @startDate AND @endDate
GROUP BY Agent_Login
) ca2
FULL OUTER JOIN
(
SELECT cr.userLogin, COUNT(userLogin) as Correspondence

FROM Correspondences cr
WHERE cr.Creation_Date BETWEEN @startDate AND @endDate
GROUP BY cr.userLogin
) cr2 ON ca2.Agent_Login = cr2.userLogin


So what I am trying to do is get the query I can use and replace this part of the above query

SELECT ca.Agent_Login, COUNT(Agent_Login)as cases
FROM CaseDetails_AllReports ca
WHERE ca.Creation_Date BETWEEN @startDate AND @endDate
GROUP BY Agent_Login
) ca2

Now you might say this will render a different result to the first query this this post. I know it will but the reason I have provided the data is to give you an overview of what I need in order to get an accurate count.
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 03/28/2013 :  10:25:05  Show Profile  Reply with Quote
"based on the query: select Distinct UserLogin, CaseID, Supervisor, Location, creation_date from Correspondences_All

Desired output would be ....."
The desired output of the distinct query will not be the one you mentioned since distinct is applied on all the columns in select list... and CreationDate has different values in everyrow so the query will return all four rows. But if the desired output is what you're looking for then check this out ..hope this will give you a way to proceed

Declare @table table (UserLogin varchar(10),CaseID int, Supervisor varchar(20), Location varchar(10), creation_date datetime)
INSERT INTO @table
VALUES
('User1',001255788711,'Supervisor1','SYD','2013-01-18 16:47:18.000')
,('User1',001273569661,'Supervisor1','SYD','2013-01-23 17:18:39.000')
,('User2',001273569661,'Supervisor2','SYD','2013-01-25 18:13:59.000')
,('User2',001273569661,'Supervisor2','SYD','2013-01-25 18:45:38.000')
SELECT UserLogin, CaseID, Supervisor, Location, Min(creation_date) from @table
GROUP BY UserLogin, CaseID, Supervisor, Location

2) You may use the input (coming through the filtering criteria on the report - considering @startDate is the lowest in the sample records and @endDate is the highest in the sample records) then applying filter this way will give you even the desired result

Declare @table table (UserLogin varchar(10),CaseID int, Supervisor varchar(20), Location varchar(10), creation_date datetime)
Declare @StartDate datetime,@endDate datetime
SET @StartDate='2013-01-18'
SET @endDate='2013-01-25'

INSERT INTO @table
VALUES
('User1',001255788711,'Supervisor1','SYD','2013-01-18 16:47:18.000')
,('User1',001273569661,'Supervisor1','SYD','2013-01-23 17:18:39.000')
,('User2',001273569661,'Supervisor2','SYD','2013-01-25 18:13:59.000')
,('User2',001273569661,'Supervisor2','SYD','2013-01-25 18:45:38.000')
SELECT UserLogin, CaseID, Supervisor, Location, Min(creation_date) from @table
WHERE Convert(date,Creation_Date) BETWEEN @startDate AND @endDate
GROUP BY UserLogin, CaseID, Supervisor, Location

Cheers
MIK

Edited by - MIK_2008 on 03/29/2013 11:25:11
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/28/2013 :  11:02:47  Show Profile  Reply with Quote
WHERE Convert(date,Creation_Date) BETWEEN @startDate AND @endDate

can be better written as

WHERE Creation_Date >= @startDate AND Creation_Date < @endDate + 1

see

http://visakhm.blogspot.in/2012/12/different-ways-to-implement-date-range.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

2revup
Posting Yak Master

102 Posts

Posted - 04/01/2013 :  03:48:41  Show Profile  Reply with Quote
Thanks team, your solving my lives problems day by day!
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 04/03/2013 :  06:36:57  Show Profile  Reply with Quote
you're welcome :)

Cheers
MIK
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000