June 3, 2010 at 12:53 pm
I've got a report that selects various columns, sometimes using case when, from a left outer join on two tables, where one column is from a certain group, date closed is null, and meeting date is select mad meeting date;
ALTER PROCEDURE [dbo].[mktb_57]
@enterdate date
AS
IF OBJECT_ID('dbo.[tb_57]') IS NOT NULL
DROP TABLE dbo.[tb_57]
SELECT Right(dbo.AT.[TO],(LEN(dbo.AT.[TO])-4)) AS [TO],
dbo.AT.[DateReceived],
dbo.AT.[AName],
Case When dbo.[AT].[DateRejected] IS NOT NULL
Then ''
Else dbo.AT.[DateDue] END AS [DateDue],
dbo.[AT].DateSent,
dbo.[AT].DateReturned,
dbo.[AT].[DateSubmitted],
dbo.[AT].[Status]
dbo.[Comments].Miles AS Milestones,
dbo.[AT].[DateRejected]
into [tb_57]
FROM dbo.[AT] LEFT OUTER JOIN
dbo.[Comments] ON
dbo.[AT].[Id] = dbo.[Comments].[Id]
WHERE ((Right([TO],(Len(dbo.[to])-4))) Like '%5%') AND
dbo.AT.DateClosed Is Null AND
([MeetingDate] =
(Select MAX([MeetingDate]) from dbo.[Comments]
WHERE dbo.[AT].[Id] = dbo.[Comments].[Id]) OR
[MeetingDate] is null) AND
-
A bit about the tables & query:
Comments is a weekly copy of many AT columns with milestones + meeting date added.
They both have a Status Column - AT.Status and Comments.IStatus
The query is used to create a static table used in SSRS to provide a report which is contract deliverable.
-
A bit about what I need to do:
Canceled and Submitted Statuses need to show up for one week only, and then drop off the report.
so....?
AT.Status LIKE '%Submitted' and AT.Status = 'Cancelled'
These need to show up ONLY ONCE - aka, when the above Statuses appear WHILE [ meeting date = max meeting date ]
If the row that the status is Submitted or Canceled does not have a meeting date of max meeting date, then they should not show up on the report.
-
I've tried different approaches - using case, using more inner selects, splitting up the make table into different procs... but I'm taking a step back and asking the cloud for advice or code 😉
Thanks!
edit: ps: I removed a lot of code from the select statement for brevity
June 3, 2010 at 1:45 pm
Think I got it..
WHERE
((Right(TO],(Len([TO])-4))) Like '%5%') AND
dbo.AT.DateClosed Is Null AND
([meeting date] =
(Select MAX([meeting date]) from [Comments]
WHERE AT.[Id] = Comments.[ID]) OR
[meeting date] is null)AND NOT
((AT.Status LIKE '%Submitted%' AND
Comments.[meeting date] BETWEEN
CONVERT(Date, getdate()) AND CONVERT(Date, dateadd(dd, -8, getdate()))) OR
(AT.Status LIKE '%Canceled%' AND
Comments.[meeting date] BETWEEN
CONVERT(Date, getdate()) AND CONVERT(Date, dateadd(dd, -8, getdate()))))
.
No idea if it reads right, but it makes sense.... hrm...
June 3, 2010 at 2:42 pm
Tested it, and no go =(
Is it possible to if/then in side of a where?
if status like __ and date submitted between x and z then true else skip? >.>
June 3, 2010 at 2:46 pm
If you would provide table def for the tables involved, some sample data to work with and your expected result we might be able to provide you with a tested solution.
There are a few people out there (including myself) preferring sample data.
If you're unsure how to do that: please read and follow the advice given in the first link in my signature.
June 3, 2010 at 2:49 pm
Lutz,
Thanks so much for replying, but I can't provide table def. and sample data. Corporate rules ;(
June 3, 2010 at 2:56 pm
I don't think you would violate any rules if you would provide a table structure with fake table and column names and fake data and fake results based on those sample data.
By doing so you would actually provide even less information than you already did with the query you posted. 😉
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply