January 6, 2009 at 3:12 pm
I have a query that has some case statements and it brings the result in this format:
Checks Customer Complaints Securities Transactions.....
Member Firm A, 9/26/08 unreported no activity unreported unreported
Member Firm A, 9/26/08 no activity unreported unreported unreported
Member Firm A, 9/26/08 unreported unreported no activity unreported
Member Firm A, 10/3/08 activity unreported unreported unreported .......
.....
.....
.....
The query looks like this:
SELECT DISTINCT b.BranchName + ', ' + CONVERT(VARCHAR, na.WeekEndingDate, 101) AS [Member Firm],
(case
when (JournalTypeID = 1 AND IsActive = 1) then 'no activity'
when EXISTS (SELECT * FROM [Journals] WHERE [JournalTypeID] = 1
AND (BranchNumber = na.BranchNumber)
AND (DateReceived >= DATEADD(wk,DATEDIFF(wk,5,na.WeekEndingDate)-1,5)
AND DateReceived < DATEADD(wk,DATEDIFF(wk,4,na.WeekEndingDate),4)+1)) then 'activity'
else 'unreported' end) as Transactions,
(case
when (JournalTypeID = 2 AND IsActive = 1) then 'no activity'
when EXISTS (SELECT * FROM [Journals] WHERE [JournalTypeID] = 2
AND (BranchNumber = na.BranchNumber)
AND (DateReceived >= DATEADD(wk,DATEDIFF(wk,5,na.WeekEndingDate)-1,5)
AND DateReceived < DATEADD(wk,DATEDIFF(wk,4,na.WeekEndingDate),4)+1)) then 'activity'
else 'unreported' end) as Checks,
(case
when (JournalTypeID = 3 AND IsActive = 1) then 'no activity'
when EXISTS (SELECT * FROM [Journals] WHERE [JournalTypeID] = 3
AND (BranchNumber = na.BranchNumber)
AND (DateReceived >= DATEADD(wk,DATEDIFF(wk,5,na.WeekEndingDate)-1,5)
AND DateReceived < DATEADD(wk,DATEDIFF(wk,4,na.WeekEndingDate),4)+1)) then 'activity'
else 'unreported' end) as [Customer Complaints],
(case
when (JournalTypeID = 4 AND IsActive = 1) then 'no activity'
when EXISTS (SELECT * FROM [Journals] WHERE [JournalTypeID] = 4
AND (BranchNumber = na.BranchNumber)
AND (DateReceived >= DATEADD(wk,DATEDIFF(wk,5,na.WeekEndingDate)-1,5)
AND DateReceived < DATEADD(wk,DATEDIFF(wk,4,na.WeekEndingDate),4)+1)) then 'activity'
else 'unreported' end) as Securities
FROM NoActivity na
INNER JOIN Branches b on na.BranchNumber = b.BranchNumber
WHERE (na.WeekEndingDate >= '12/26/2008' AND na.WeekEndingDate <= '1/2/2009')
I wish to display all the Member Firms with the same date eg. Member Firm A, 9/26/08 in a single row instead of multiple rows.
Can somebody guide me?
January 6, 2009 at 3:19 pm
Well, it would help if you actually showed us how you want the data displayed based on the sample at the top of your post.
January 6, 2009 at 3:30 pm
I am sorry about that.
Currently my results are displayed as:
Checks Customer Complaints Securities Transactions.....
Member Firm A, 9/26/08 unreported no activity unreported unreported
Member Firm A, 9/26/08 no activity unreported unreported unreported
Member Firm A, 9/26/08 unreported unreported no activity unreported
Member Firm A, 10/3/08 activity unreported unreported unreported .......
I want the result to be displayed as:
Checks Customer Complaints Securities Transactions.....
Member Firm A, 9/26/08 no activity no activity no activity unreported
Member Firm A, 10/3/08 activity unreported unreported unreported .......
This is what the data in the table looks like:
ID JournalTypeID WeekEndingDate BranchNumber IsActive
1 1 9/26/2008 12:00:00 AM135 True
2 4 9/19/2008 12:00:00 AM135 False
3 5 9/19/2008 12:00:00 AM135 True
4 5 9/26/2008 12:00:00 AM135 True
5 2 9/26/2008 12:00:00 AM135 True
6 4 9/26/2008 12:00:00 AM135 True
7 5 9/26/2008 12:00:00 AM135 True
8 4 10/3/200 12:00:00 AM135 True
9 5 10/3/2008 12:00:00 AM135 True
If there's a True against IsActive flag display value is 'no activity', else it looks for records in another table.If an entry is found there the display value is 'activity' else it is 'unreported'
the problem that I am facing is it checks each row in the NoActivity table and creates a seperate resulting row for each of them. I want to group by branch and same date together and sisplay the corresponding values in a single row.
Hope I am making myself clear enough 🙁
January 7, 2009 at 2:13 am
try using sub-query + group by (BranchName,WeekEndingDate) should give you the results you wanted.
post back if you encounter any problems.
cheers 😉
jon
January 7, 2009 at 2:30 am
Anu, a good first step would be to tidy up the original query. Check this against your original, it's much simpler - and therefore easier to work with, and should be faster too...[font="Courier New"]SELECT DISTINCT b.BranchName + ', ' + CONVERT(VARCHAR, na.WeekEndingDate, 101) AS [Member Firm],
CASE WHEN JournalTypeID = 1 AND IsActive = 1 THEN 'no activity'
WHEN j.Entries IS NOT NULL THEN 'activity'
ELSE 'unreported' END AS Transactions,
CASE WHEN JournalTypeID = 2 AND IsActive = 1 THEN 'no activity'
WHEN j.Entries IS NOT NULL THEN 'activity'
ELSE 'unreported' END AS Checks,
CASE WHEN JournalTypeID = 3 AND IsActive = 1 THEN 'no activity'
WHEN j.Entries IS NOT NULL THEN 'activity'
ELSE 'unreported' END AS [Customer Complaints],
CASE WHEN JournalTypeID = 4 AND IsActive = 1 THEN 'no activity'
WHEN j.Entries IS NOT NULL THEN 'activity'
ELSE 'unreported' END AS Securities
FROM NoActivity na
INNER JOIN Branches b ON na.BranchNumber = b.BranchNumber
LEFT JOIN (SELECT COUNT(*) AS Entries, [JournalTypeID], BranchNumber, DateReceived
FROM [Journals]
GROUP BY [JournalTypeID], BranchNumber, DateReceived) j
ON j.BranchNumber = na.BranchNumber
AND j.JournalTypeID = na.JournalTypeID
AND DateReceived >= DATEADD(wk,DATEDIFF(wk,5,na.WeekEndingDate)-1,5)
AND DateReceived < DATEADD(wk,DATEDIFF(wk,4,na.WeekEndingDate),4)+1
WHERE na.WeekEndingDate >= '12/26/2008' AND na.WeekEndingDate <= '1/2/2009'
[/font]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 7, 2009 at 11:45 am
Sorry Jon I am not an expert in SQL 🙁
I didn't understand how to use sub-query for my query.
Can you help.
Also, the above query doesn't get me the desired results. It is getting me an activity for all the columns if there's a corresponding row for any of the columns.
January 7, 2009 at 2:30 pm
anu (1/7/2009)
Sorry Jon I am not an expert in SQL 🙁I didn't understand how to use sub-query for my query.
Can you help.
Also, the above query doesn't get me the desired results. It is getting me an activity for all the columns if there's a corresponding row for any of the columns.
Anu, please read the link below, it will describe how to post table structures and data which we can use to run against.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 8, 2009 at 8:57 pm
Chris Morris (1/7/2009)
Also, the above query doesn't get me the desired results. It is getting me an activity for all the columns if there's a corresponding row for any of the columns.
Hi,
Sub-query is nothing more than a select within a select.
I don't understand what you mean by "It is getting me an activity for all the columns if there's a corresponding row for any of the columns".
If you'r looking for a quick fix only a temp solution (idea is to query twice but if your resultset is huge avoid this approach), but i highly suggest you post your table structure + sample data for each table so we can help you.
IF OBJECT_ID('tempdb..#mi1') IS NOT NULL DROP TABLE #mi1
SELECT * INTO #mi1 FROM
(SELECT DISTINCT b.BranchName + ', ' + CONVERT(VARCHAR, na.WeekEndingDate, 101) AS [Member Firm],
CASE WHEN na.JournalTypeID = 1 AND IsActive = 1 THEN 'no activity'
WHEN j.Entries IS NOT NULL THEN 'activity'
ELSE 'unreported' END AS Transactions,
CASE WHEN na.JournalTypeID = 2 AND IsActive = 1 THEN 'no activity'
WHEN j.Entries IS NOT NULL THEN 'activity'
ELSE 'unreported' END AS Checks,
CASE WHEN na.JournalTypeID = 3 AND IsActive = 1 THEN 'no activity'
WHEN j.Entries IS NOT NULL THEN 'activity'
ELSE 'unreported' END AS [Customer Complaints],
CASE WHEN na.JournalTypeID = 4 AND IsActive = 1 THEN 'no activity'
WHEN j.Entries IS NOT NULL THEN 'activity'
ELSE 'unreported' END AS Securities
FROM @NoActivity na
INNER JOIN @branches b ON na.BranchNumber = b.BranchNumber
LEFT JOIN (SELECT COUNT(*) AS Entries, [JournalTypeID], BranchNumber, DateReceived
FROM @Journals
GROUP BY [JournalTypeID], BranchNumber, DateReceived) j
ON j.BranchNumber = na.BranchNumber
AND j.JournalTypeID = na.JournalTypeID
AND DateReceived >= DATEADD(wk,DATEDIFF(wk,5,na.WeekEndingDate)-1,5)
AND DateReceived < DATEADD(wk,DATEDIFF(wk,4,na.WeekEndingDate),4)+1
WHERE na.WeekEndingDate >= '12/26/2008' AND na.WeekEndingDate <= '1/2/2009') h
select h.[Member Firm],
(case when EXISTS (SELECT * FROM #mi1 WHERE [Member Firm] = h.[Member Firm] and Transactions = 'activity') then 'activity'
when EXISTS (SELECT * FROM #mi1 WHERE [Member Firm] = h.[Member Firm] and Transactions = 'no activity') then 'no activity'
else 'unreported' end) as Transactions,
(case when EXISTS (SELECT * FROM #mi1 WHERE [Member Firm] = h.[Member Firm] and Checks = 'activity') then 'activity'
when EXISTS (SELECT * FROM #mi1 WHERE [Member Firm] = h.[Member Firm] and Checks = 'no activity') then 'no activity'
else 'unreported' end) as Checks,
(case when EXISTS (SELECT * FROM #mi1 WHERE [Member Firm] = h.[Member Firm] and [Customer Complaints] = 'activity') then 'activity'
when EXISTS (SELECT * FROM #mi1 WHERE [Member Firm] = h.[Member Firm] and [Customer Complaints] = 'no activity') then 'no activity'
else 'unreported' end) as [Customer Complaints],
(case when EXISTS (SELECT * FROM #mi1 WHERE [Member Firm] = h.[Member Firm] and Securities = 'activity') then 'activity'
when EXISTS (SELECT * FROM #mi1 WHERE [Member Firm] = h.[Member Firm] and Securities = 'no activity') then 'no activity'
else 'unreported' end) as Securities
from #mi1 as h
group by h.[Member Firm]
January 9, 2009 at 2:29 am
This should fix the problem - but without any data to test against, it's hard to tell..
[font="Courier New"]SELECT DISTINCT b.BranchName + ', ' + CONVERT(VARCHAR, na.WeekEndingDate, 101) AS [Member Firm],
CASE WHEN JournalTypeID = 1 THEN
CASE WHEN IsActive = 1 THEN 'no activity' WHEN j.Entries IS NOT NULL THEN 'activity' ELSE 'unreported' END
END AS Transactions,
CASE WHEN JournalTypeID = 2 THEN
CASE WHEN IsActive = 1 THEN 'no activity' WHEN j.Entries IS NOT NULL THEN 'activity' ELSE 'unreported' END
END AS Checks,
CASE WHEN JournalTypeID = 3 THEN
CASE WHEN IsActive = 1 THEN 'no activity' WHEN j.Entries IS NOT NULL THEN 'activity' ELSE 'unreported' END
END AS [Customer Complaints],
CASE WHEN JournalTypeID = 4 THEN
CASE WHEN IsActive = 1 THEN 'no activity' WHEN j.Entries IS NOT NULL THEN 'activity' ELSE 'unreported' END
END AS Securities
FROM NoActivity na
INNER JOIN Branches b ON na.BranchNumber = b.BranchNumber
LEFT JOIN (SELECT COUNT(*) AS Entries, [JournalTypeID], BranchNumber, DateReceived
FROM [Journals]
GROUP BY [JournalTypeID], BranchNumber, DateReceived) j
ON j.BranchNumber = na.BranchNumber
AND j.JournalTypeID = na.JournalTypeID
AND DateReceived >= DATEADD(wk,DATEDIFF(wk,5,na.WeekEndingDate)-1,5)
AND DateReceived < DATEADD(wk,DATEDIFF(wk,4,na.WeekEndingDate),4)+1
WHERE na.WeekEndingDate >= '12/26/2008' AND na.WeekEndingDate <= '1/2/2009'
[/font]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 9, 2009 at 1:50 pm
Thanks for the replies.
I replaced my CASE statements with a PIVOT statement and now my query seems to be running fine.
This is what my query looks like now:
SELECT BranchName AS [Member Firm]
,ISNULL(CONVERT(VARCHAR, WeekEndingDate, 101),CONVERT(VARCHAR, @WeekEndingDateFrom, 101)) AS WeekEndingDate
,ISNULL([Transaction Journal],'unreported') AS [Transactions]
,ISNULL([Checks Journal],'unreported') AS [Checks]
,ISNULL([Customer Complaints Journal],'unreported') AS [Customer Complaints]
,ISNULL([Securities Journal],'unreported') AS [Securities]
,ISNULL([Business Expense Journal],'unreported') AS [Business Expense]
,ISNULL([Gifts & Gratuities Journal],'unreported') AS [Gifts & Gratuities]
,ISNULL([Non Cash Compensation Journal],'unreported') AS [Non Cash Compensation]
,ISNULL([2821 Transaction Journal],'unreported') AS [2821 Transactions]
FROM
(
SELECTb.BranchName, na.WeekEndingDate, jt.JournalType, (case when (na.IsActive = 1 AND j.Entries IS NULL) then 'no activity' when (j.Entries IS NOT NULL) then 'activity'else 'unreported' end) as NoActivityStatus
FROMBranches b
INNER JOIN UserBranches ub ON b.BranchNumber = ub.BranchNumber
LEFT JOIN NoActivity na ON b.BranchNumber = na.BranchNumber
LEFT JOIN JournalTypes jt on jt.JournalTypeID = na.JournalTypeID
LEFT JOIN (SELECT COUNT(*) AS Entries, [JournalTypeID], BranchNumber, DateReceived
FROM [Journals] GROUP BY [JournalTypeID], BranchNumber, DateReceived) j ON j.BranchNumber = na.BranchNumber AND j.JournalTypeID = na.JournalTypeID AND DateReceived >= (DATEADD(wk,DATEDIFF(wk,5,na.WeekEndingDate)-1,5)) AND DateReceived < (DATEADD(wk,DATEDIFF(wk,4,na.WeekEndingDate),4)+1)
WHERE (ub.UserName = @UserName)
) as tempNoActivity
PIVOT
(
Max(NoActivityStatus)
FOR JournalType
IN( [Transaction Journal]
,[Checks Journal]
,[Customer Complaints Journal]
,[Securities Journal]
,[Business Expense Journal]
,[Gifts & Gratuities Journal]
,[Non Cash Compensation Journal]
,[2821 Transaction Journal])
) as p
This query gets me the results in the desired format.
January 9, 2009 at 1:55 pm
In that case... see this... especially the last part where the performance tests were done...
http://www.sqlservercentral.com/articles/T-SQL/63681/
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply