How can I combine these two sets

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

SQL Server 2008 Forums

Transact-SQL (2008)

How can I combine these two sets

Author

Topic

sql-lover
Yak Posting Veteran

99 Posts

Posted - 2014-05-20 : 00:01:34

Hi,

Got these two sets:


SELECT RequestID, 
		RequestTypeID, 
		RequestStatusID
		,"DateApproved" = (SELECT DateApproved FROM  Ticket T Where T.TicketID = CR.TicketID)
	    ,"DateFirstReleased" =								(DateFirstReleased)

	    ,"ProductionDays" = (
									DateDiff(day,
											(SELECT DateApproved FROM Ticket T Where T.TicketID = CR.TicketID)
											,
											
  										(DateFirstReleased)
								 )
			
						     )
	 ,"TicketCompletedDate" = (SELECT CompletedDate FROM  Ticket T Where T.TicketID = CR.TicketID)

FROM
MyDatabase.dbo.CustomRequest CR
WHERE DateDeactivated is null AND CR.RequestTypeID <> 1


SELECT RequestID, 
		RequestTypeID, 
		RequestStatusID
		,"DateApproved" = (SELECT DateApproved FROM  Ticket T Where T.TicketID = CR.TicketID)
	    ,"DateFirstReleased" =								(SELECT NewAsOfDate FROM   MyDatabase.dbo.Report R Where R.ReportID = CR.ReportID AND IsDotNetActive =1)

	    ,"ProductionDays" = (
									DateDiff(day,
											(SELECT DateApproved FROM Ticket T Where T.TicketID = CR.TicketID)
											,
											
  										(SELECT NewAsOfDate FROM  MyDatabase.dbo.Report R Where R.ReportID = CR.ReportID AND IsDotNetActive =1)
								 )
			
						     )
	 ,"TicketCompletedDate" = (SELECT CompletedDate FROM  Ticket T Where T.TicketID = CR.TicketID)

FROM
MyDatabase.dbo.CustomRequest CR
WHERE DateDeactivated is null AND CR.RequestTypeID = 1

How can I combine in one single data set while keeping good performance? I was using CASE on one ProductionDays and DateFirstReleased, but SQL engine is making two Index Spools when I do that.

I tried UNION ALL but it's even worse as the engine reads the tables twice.

adsingh82
Starting Member

20 Posts

Posted - 2014-05-20 : 01:40:38

I do not think you need two queries.
Checking CR.RequestTypeID = 1 for one block of sql and CR.RequestTypeID <> 1 for another block is meaningless. You can fetch all the rows when you remove CR.RequestTypeID in the where condition

And one more thing is like you have many subqueries in your sql. change that to joins you will get the performance and removing the CR.RequestTypeID from where clause will work the sql as a single block

Regards,
Alwyn.M

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2014-05-20 : 05:19:02

SELECT RequestID,
RequestTypeID,
RequestStatusID
,"DateApproved" = (SELECT DateApproved FROM Ticket T Where T.TicketID = CR.TicketID)
,"DateFirstReleased" = CASE WHEN T.TicketID = CR.TicketID THEN (DateFirstReleased)
ELSE (SELECT NewAsOfDate FROM MyDatabase.dbo.Report R Where R.ReportID = CR.ReportID AND IsDotNetActive =1)
END

,"ProductionDays" = CASE WHEN T.TicketID = CR.TicketID THEN (DateDiff(day,(SELECT DateApproved FROM Ticket T Where T.TicketID = CR.TicketID),(DateFirstReleased)))
ELSE (DateDiff(day,(SELECT DateApproved FROM Ticket T Where T.TicketID = CR.TicketID),
(SELECT NewAsOfDate FROM MyDatabase.dbo.Report R Where R.ReportID = CR.ReportID AND IsDotNetActive =1)))
END

,"TicketCompletedDate" = (SELECT CompletedDate FROM Ticket T Where T.TicketID = CR.TicketID)

FROM
MyDatabase.dbo.CustomRequest CR
WHERE DateDeactivated is null AND CR.RequestTypeID <> 1

Veera

sql-lover
Yak Posting Veteran

99 Posts

Posted - 2014-05-20 : 20:10:15

Managed to fix it this way:


SELECT 
	cr.RequestID, 
	cr.RequestTypeID, 
	cr.RequestStatusID,
	t.DateApproved,
	x.DateFirstReleased,
	[ProductionDays] = DATEDIFF(day, t.DateApproved, x.DateFirstReleased),
	[TicketCompletedDate] = t.CompletedDate

FROM MyDatabase.dbo.CustomRequest CR
LEFT JOIN Ticket t 
	ON T.TicketID = CR.TicketID
LEFT JOIN MyDatabase.dbo.Report r 
	ON R.ReportID = CR.ReportID 
	AND r.IsDotNetActive =1
CROSS APPLY (
	SELECT [DateFirstReleased] = CASE 
		WHEN CR.RequestTypeID = 1 THEN r.NewAsOfDate 
		ELSE cr.DateFirstReleased END
) x
WHERE cr.DateDeactivated IS NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources