January 14, 2008 at 2:34 am
Hi All,
I need help about writing the sql as below.
I find a StartDate and EndDate with Case function.
And I have to write a where clause with these dates.
I don't know the write syntax.
How can I write these t-sql?
kind regards...
SELECT dbo.Library_LibraryRecordDetails.Name as [Materyal],
dbo._Enum_TimingObjectStatus.caption as [Durumu],
(CASE dbo._Enum_TimingObjectStatus.value
WHEN 1 THEN dbo.Library_LoanTransfers.DueStartDate
WHEN 2 THEN dbo.Library_LoanTransfers.DueStartDate
WHEN 4 THEN dbo.Library_LoanTransfers.ActualStartDate
WHEN 5 THEN dbo.Library_LoanTransfers.ActualStartDate
END) as Baslangic_Tarihi,
(CASE dbo._Enum_TimingObjectStatus.value
WHEN 1 THEN dbo.Library_LoanTransfers.DueEndDate
WHEN 4 THEN null
WHEN 5 THEN dbo.Library_LoanTransfers.ActualEndDate
END) as Bitis_Tarihi,
dbo._Enum_LoanTransferReturnType.caption AS [İade_Şekli]
FROM dbo.Library_LoanTransfers INNER JOIN
dbo.Common_Accounts ON dbo.Library_LoanTransfers.MemberId = dbo.Common_Accounts.OId INNER JOIN
dbo.Library_LibraryRecordDetails ON dbo.Library_LoanTransfers.MateriyalId = dbo.Library_LibraryRecordDetails.OId INNER JOIN
dbo._Enum_TimingObjectStatus ON dbo.Library_LoanTransfers.TimingObjectStatus = dbo._Enum_TimingObjectStatus.value INNER JOIN
dbo._Enum_LoanTransferReturnType ON dbo.Library_LoanTransfers.ReturnType = dbo._Enum_LoanTransferReturnType.value
WHERE (dbo.Library_LoanTransfers.MemberId = 345)
AND
case (CASE dbo._Enum_TimingObjectStatus.value
WHEN 1 THEN dbo.Library_LoanTransfers.DueEndDate
WHEN 4 THEN null
WHEN 5 THEN dbo.Library_LoanTransfers.ActualEndDate
END)
When NULL
Then
(CASE dbo._Enum_TimingObjectStatus.value
WHEN 1 THEN dbo.Library_LoanTransfers.DueStartDate
WHEN 2 THEN dbo.Library_LoanTransfers.DueStartDate
WHEN 4 THEN dbo.Library_LoanTransfers.ActualStartDate
WHEN 5 THEN dbo.Library_LoanTransfers.ActualStartDate
END) between convert(datetime,@IlkTarih,103) and convert(datetime,@SonTarih,103)
END
OR
case (not(CASE dbo._Enum_TimingObjectStatus.value
WHEN 1 THEN dbo.Library_LoanTransfers.DueEndDate
WHEN 4 THEN null
WHEN 5 THEN dbo.Library_LoanTransfers.ActualEndDate
END)) is null
(CASE dbo._Enum_TimingObjectStatus.value WHEN 1 THEN dbo.Library_LoanTransfers.DueEndDate WHEN 4 THEN NULL
WHEN 5 THEN dbo.Library_LoanTransfers.ActualEndDate END)
between convert(datetime,@IlkTarih,103) and convert(datetime,@SonTarih,103)
Order by dbo._Enum_LoanTransferReturnType.caption,[Durumu],Baslangic_Tarihi
January 14, 2008 at 5:02 am
How about taking the initial query and placing it into a sub-select and the wrapping the outer where clause around that. Then you can refer to the columns derived from the original SELECT CASE Statements by name instead of having to rederive them. I can't precisely test this without your structure & some data, but something like:
SELECT x.Materyal
,x.Durumu
,x.Baslangic_Tarihi
,x.Bitis_Tarihi
,[Iade_Sekli]
FROM (
SELECT dbo.Library_LibraryRecordDetails.Name as [Materyal],
dbo._Enum_TimingObjectStatus.caption as [Durumu],
(CASE dbo._Enum_TimingObjectStatus.value
WHEN 1 THEN dbo.Library_LoanTransfers.DueStartDate
WHEN 2 THEN dbo.Library_LoanTransfers.DueStartDate
WHEN 4 THEN dbo.Library_LoanTransfers.ActualStartDate
WHEN 5 THEN dbo.Library_LoanTransfers.ActualStartDate
END) as Baslangic_Tarihi,
(CASE dbo._Enum_TimingObjectStatus.value
WHEN 1 THEN dbo.Library_LoanTransfers.DueEndDate
WHEN 4 THEN null
WHEN 5 THEN dbo.Library_LoanTransfers.ActualEndDate
END) as Bitis_Tarihi,
dbo._Enum_LoanTransferReturnType.caption AS [Iade_Sekli]
FROM dbo.Library_LoanTransfers INNER JOIN
dbo.Common_Accounts ON dbo.Library_LoanTransfers.MemberId = dbo.Common_Accounts.OId INNER JOIN
dbo.Library_LibraryRecordDetails ON dbo.Library_LoanTransfers.MateriyalId = dbo.Library_LibraryRecordDetails.OId INNER JOIN
dbo._Enum_TimingObjectStatus ON dbo.Library_LoanTransfers.TimingObjectStatus = dbo._Enum_TimingObjectStatus.value INNER JOIN
dbo._Enum_LoanTransferReturnType ON dbo.Library_LoanTransfers.ReturnType = dbo._Enum_LoanTransferReturnType.value
WHERE (dbo.Library_LoanTransfers.MemberId = 345)
) AS x
WHERE -- Input a clean version of the where clause here
Order by x._Enum_LoanTransferReturnType.caption,[Durumu],Baslangic_Tarihi
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 14, 2008 at 8:10 am
I suggest you try using a couple of user defined functions to replace the case statements.
create function dbo.fDetermineStartDate( @status int, @dueStartDate datetime, @actualStartDate datetime )
returns datetime
begin
return (CASE @status
WHEN 1 THEN @dueStartDate
WHEN 2 THEN @dueStartDate
WHEN 4 THEN @actualStartDate
WHEN 5 THEN @actualStartDate
else null
END)
end
create function dbo.fDetermineEndDate( @status int, @dueEndDate datetime, @actualEndDate datetime )
returns datetime
begin
return (CASE @status
WHEN 1 THEN @dueEndDate
WHEN 4 THEN null
WHEN 5 THEN @actualEndDate
else null
END)
end
They'll make the query simpler and easier to read. If _Enum_TimingObjectStatus simply defines all status types and has no other columns, it can be eliminated from the join.
January 15, 2008 at 1:45 am
Thank you for your help.
January 15, 2008 at 1:45 am
Thank you for your help.
January 15, 2008 at 5:45 am
Hi. With your help I wrote this sql as below.
But it doesn't work as I want.
I want to filter by with dbo.fDetermineStartDate return value when
fDetermineEndDate is null.
if fDetermineEndDate is not null I want to filter by with fDetermineEndDate
Kind regards.
SELECT Library_LibraryRecordDetails.Name AS Materyal, _Enum_TimingObjectStatus.caption AS Durumu,
dbo.fDetermineStartDate(_Enum_TimingObjectStatus.value, Library_LoanTransfers.DueStartDate, Library_LoanTransfers.ActualStartDate)
AS Baslangic_Tarihi, dbo.fDetermineEndDate(_Enum_TimingObjectStatus.value, Library_LoanTransfers.DueEndDate,
Library_LoanTransfers.ActualEndDate) AS Bitis_Tarihi,
(CASE dbo._Enum_TimingObjectStatus.value WHEN 5 THEN _Enum_LoanTransferReturnType.caption ELSE NULL END) AS Iade_Sekli
FROM Library_LoanTransfers INNER JOIN
Common_Accounts ON Library_LoanTransfers.MemberId = Common_Accounts.OId INNER JOIN
Library_LibraryRecordDetails ON Library_LoanTransfers.MateriyalId = Library_LibraryRecordDetails.OId INNER JOIN
_Enum_TimingObjectStatus ON Library_LoanTransfers.TimingObjectStatus = _Enum_TimingObjectStatus.value INNER JOIN
_Enum_LoanTransferReturnType ON Library_LoanTransfers.ReturnType = _Enum_LoanTransferReturnType.value
WHERE (Library_LoanTransfers.MemberId = @Uye) AND
((CASE dbo.fDetermineEndDate(_Enum_TimingObjectStatus.value,Library_LoanTransfers.DueEndDate, Library_LoanTransfers.ActualEndDate) WHEN NULL
THEN dbo.fDetermineStartDate(_Enum_TimingObjectStatus.value, Library_LoanTransfers.DueStartDate, Library_LoanTransfers.ActualStartDate)
ELSE dbo.fDetermineEndDate(_Enum_TimingObjectStatus.value, Library_LoanTransfers.DueEndDate, Library_LoanTransfers.ActualEndDate) END)
BETWEEN CONVERT(datetime, @IlkTarih, 103) AND CONVERT(datetime, @SonTarih, 103)) AND (_Enum_TimingObjectStatus.value = @Durumu)
ORDER BY Iade_Sekli, Durumu, Baslangic_Tarihi
January 15, 2008 at 7:42 am
aysegul (1/15/2008)
Hi. With your help I wrote this sql as below.But it doesn't work as I want.
I want to filter by with dbo.fDetermineStartDate return value when
fDetermineEndDate is null.
if fDetermineEndDate is not null I want to filter by with fDetermineEndDate
the SQL is difficult to read so simplifying it will help determine where the problem is. also consider using table aliases (e.g.: [font="Courier New"]FROM Library_LoanTransfers AS TRANSFERS ... JOIN _Enum_TimingObjectStatus AS STATUS [/font]) since they shorten the SQL and meaningful aliases can be explanatory.
the behavior you've described is best handled with IFNULL() rather than another CASE statement. applying these changes to this:
(CASE dbo.fDetermineEndDate(_Enum_TimingObjectStatus.value, Library_LoanTransfers.DueEndDate, Library_LoanTransfers.ActualEndDate)
WHEN NULL
THEN dbo.fDetermineStartDate(_Enum_TimingObjectStatus.value, Library_LoanTransfers.DueStartDate, Library_LoanTransfers.ActualStartDate)
ELSE dbo.fDetermineEndDate(_Enum_TimingObjectStatus.value, Library_LoanTransfers.DueEndDate, Library_LoanTransfers.ActualEndDate)
END)
results in this:
ifnull( dbo.fDetermineEndDate(STATUS.value, TRANSFERS.DueEndDate, TRANSFERS.ActualEndDate) ,
dbo.fDetermineStartDate(STATUS.value, TRANSFERS.DueStartDate, TRANSFERS.ActualStartDate) )
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply