Answered by:
SQL query question
Question

Hello,
I'm able to received expected output from the below query,
IF OBJECT_ID('tempdb..#TEMP1') IS NOT NULL DROP TABLE #TEMP1 IF OBJECT_ID('tempdb..#TEMP2') IS NOT NULL DROP TABLE #TEMP2 CREATE TABLE #TEMP1 ( [ID] INT, [MCK] INT, [CLS] CHAR(1), [EFF] DATE ) CREATE TABLE #TEMP2 ( [TID] INT, [Date] DATE, [MonDate] DATE ) INSERT INTO #TEMP1 VALUES ( 1, 123, 'M', '20120725' ) INSERT INTO #TEMP1 VALUES ( 2, 456, 'D', '20150401' ) INSERT INTO #TEMP1 VALUES ( 3, 456, 'V', '20150401' ) INSERT INTO #TEMP1 VALUES ( 4, 789, 'M', '20161015' ) INSERT INTO #TEMP1 VALUES ( 5, 789, 'V', '20161015' ) INSERT INTO #TEMP2 VALUES ( 12, '20120107', '20120131' ) INSERT INTO #TEMP2 VALUES ( 23, '20120831', '20120831' ) INSERT INTO #TEMP2 VALUES ( 32, '20150331', '20150331' ) INSERT INTO #TEMP2 VALUES ( 39, '20160407', '20160430' ) INSERT INTO #TEMP2 VALUES ( 42, '20160430', '20160430' ) INSERT INTO #TEMP2 VALUES ( 45, '20171130', '20171130' ) SELECT t.[MCK], t.[TID], CASE WHEN t.DT = 1 AND t.MD = 0 AND t.VI = 0 THEN 1 WHEN t.DT = 0 AND t.MD = 1 AND t.VI = 0 THEN 2 WHEN t.DT = 0 AND t.MD = 0 AND t.VI = 1 THEN 3 WHEN t.DT = 1 AND t.MD = 1 AND t.VI = 0 THEN 4 WHEN t.DT = 1 AND t.MD = 1 AND t.VI = 1 THEN 5 WHEN t.DT = 1 AND t.MD = 0 AND t.VI = 1 THEN 6 WHEN t.DT = 0 AND t.MD = 1 AND t.VI = 1 THEN 7 END AS [COMBO] FROM ( SELECT a.[MCK], b.[TID], a.[CLS], MAX(CASE WHEN a.[CLS] = 'D' THEN 1 ELSE 0 END) AS DT, MAX(CASE WHEN a.[CLS] = 'M' THEN 1 ELSE 0 END) AS MD, MAX(CASE WHEN a.[CLS] = 'V' THEN 1 ELSE 0 END) AS VI FROM #TEMP1 a INNER JOIN #TEMP2 b ON a.[EFF] <= b.[Date] WHERE b.[Date] = b.[MonDate] GROUP BY a.[MCK], b.[TID] ORDER BY a.[MCK], a.[CLS] ) t ORDER BY t.[MCK] IF OBJECT_ID('tempdb..#TEMP1') IS NOT NULL DROP TABLE #TEMP1 IF OBJECT_ID('tempdb..#TEMP2') IS NOT NULL DROP TABLE #TEMP2
But above query is taking a lot time to execute.
Is there any other way to write narrow down this query into single query instead of subquery using new windows SQL functions or so.
Help needed.
Thank You
Regards,
gk03
Please do let us know your feedback. Thank You  KG, MCTS
Thursday, September 7, 2017 4:34 PM
Answers

Hi gk1393,
How about the following query？
;WITH CTE AS ( SELECT a.[MCK], b.[TID], a.[CLS], MAX(CASE WHEN a.[CLS] = 'D' THEN 1 ELSE 0 END) AS DT, MAX(CASE WHEN a.[CLS] = 'M' THEN 1 ELSE 0 END) AS MD, MAX(CASE WHEN a.[CLS] = 'V' THEN 1 ELSE 0 END) AS VI FROM #TEMP1 a INNER JOIN #TEMP2 b ON a.[EFF] <= b.[Date] WHERE b.[Date] = b.[MonDate] CROSS APPLY (SELECT [TID] FROM #TEMP2 WHERE [Date]>= a.[EFF] and [Date]=[MonDate]) b GROUP BY a.[MCK], b.[TID] ORDER BY a.[MCK], a.[CLS] ) SELECT t.[MCK], t.[TID], CASE WHEN t.DT = 1 AND t.MD = 0 AND t.VI = 0 THEN 1 WHEN t.DT = 0 AND t.MD = 1 AND t.VI = 0 THEN 2 WHEN t.DT = 0 AND t.MD = 0 AND t.VI = 1 THEN 3 WHEN t.DT = 1 AND t.MD = 1 AND t.VI = 0 THEN 4 WHEN t.DT = 1 AND t.MD = 1 AND t.VI = 1 THEN 5 WHEN t.DT = 1 AND t.MD = 0 AND t.VI = 1 THEN 6 WHEN t.DT = 0 AND t.MD = 1 AND t.VI = 1 THEN 7 END AS [COMBO] FROM CTE t (  SELECT a.[MCK],  b.[TID], a.[CLS],  MAX(CASE WHEN a.[CLS] = 'D' THEN 1 ELSE 0 END) AS DT,  MAX(CASE WHEN a.[CLS] = 'M' THEN 1 ELSE 0 END) AS MD,  MAX(CASE WHEN a.[CLS] = 'V' THEN 1 ELSE 0 END) AS VI  FROM #TEMP1 a  INNER JOIN #TEMP2 b ON a.[EFF] <= b.[Date]  WHERE b.[Date] = b.[MonDate]  GROUP BY a.[MCK], b.[TID]  ORDER BY a.[MCK], a.[CLS] ) t ORDER BY t.[MCK]
Besides, you'd better provide the exact execution plan, so that we could know the exact effect of the query.
Best Regards,
Will
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com. Marked as answer by gk1393 Friday, September 8, 2017 7:59 PM
Friday, September 8, 2017 7:43 AM
All replies

Can you tell us with words, what is it that you are trying to solve?
AMB
Some guidelines for posting questions...
AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntasThursday, September 7, 2017 5:02 PM 
Can you please tell us more about it...
It will be helpful for us if you can send execution plan too.
Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh
Thursday, September 7, 2017 5:06 PM 
Just below query brings 158K million counts with 1 min of execution,
SELECT COUNT(*)158,000,000 FROM #TEMP1 a INNER JOIN #TEMP2 b ON a.[EFF] <= b.[Date] WHERE b.[Date] = b.[MonDate]
Thanks
Regards,
gk03
Please do let us know your feedback. Thank You  KG, MCTS
Thursday, September 7, 2017 5:18 PM 
I was trying to see if we can replace CASE/MAX statement and can drive my initial query with more robust execution timings since CASE/MAX statement are giving me results set with 2 hours of total time.
Please do let us know your feedback. Thank You  KG, MCTS
Thursday, September 7, 2017 5:21 PM 
Please, tell us the business problem you are trying to solve?
AMB
Some guidelines for posting questions...
AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntasThursday, September 7, 2017 5:38 PM 
Business problem above query is taken 12 hours with the total execution
Please do let us know your feedback. Thank You  KG, MCTS
Thursday, September 7, 2017 5:48 PM 
Use regular tables instead of temp tables and add primary key and appropriate index to each table.Thursday, September 7, 2017 5:57 PM

They are regular physical tables and NOT temp table with column store indexes on those table.
I was just throwing an example in this forum with the temp table example.
Please do let us know your feedback. Thank You  KG, MCTS
Thursday, September 7, 2017 6:18 PM 
Check your execution plan to find out the query cost to see where it can be improved.Thursday, September 7, 2017 6:57 PM

Up to my knowledge execution plan looks good but still query is running too slow to pull data from physical tables.
Please do let us know your feedback. Thank You  KG, MCTS
Thursday, September 7, 2017 8:12 PM 
Did you try to move the statement SELECT... FROM... WHERE... GROUP BY... out of the FROM statement and put the result set into a temporary table?
A Fan of SSIS, SSRS and SSAS
Thursday, September 7, 2017 9:18 PM 
Yes, but still it is not helping out.
Please do let us know your feedback. Thank You  KG, MCTS
Thursday, September 7, 2017 9:44 PM 
Hi gk1393,
How about the following query？
;WITH CTE AS ( SELECT a.[MCK], b.[TID], a.[CLS], MAX(CASE WHEN a.[CLS] = 'D' THEN 1 ELSE 0 END) AS DT, MAX(CASE WHEN a.[CLS] = 'M' THEN 1 ELSE 0 END) AS MD, MAX(CASE WHEN a.[CLS] = 'V' THEN 1 ELSE 0 END) AS VI FROM #TEMP1 a INNER JOIN #TEMP2 b ON a.[EFF] <= b.[Date] WHERE b.[Date] = b.[MonDate] CROSS APPLY (SELECT [TID] FROM #TEMP2 WHERE [Date]>= a.[EFF] and [Date]=[MonDate]) b GROUP BY a.[MCK], b.[TID] ORDER BY a.[MCK], a.[CLS] ) SELECT t.[MCK], t.[TID], CASE WHEN t.DT = 1 AND t.MD = 0 AND t.VI = 0 THEN 1 WHEN t.DT = 0 AND t.MD = 1 AND t.VI = 0 THEN 2 WHEN t.DT = 0 AND t.MD = 0 AND t.VI = 1 THEN 3 WHEN t.DT = 1 AND t.MD = 1 AND t.VI = 0 THEN 4 WHEN t.DT = 1 AND t.MD = 1 AND t.VI = 1 THEN 5 WHEN t.DT = 1 AND t.MD = 0 AND t.VI = 1 THEN 6 WHEN t.DT = 0 AND t.MD = 1 AND t.VI = 1 THEN 7 END AS [COMBO] FROM CTE t (  SELECT a.[MCK],  b.[TID], a.[CLS],  MAX(CASE WHEN a.[CLS] = 'D' THEN 1 ELSE 0 END) AS DT,  MAX(CASE WHEN a.[CLS] = 'M' THEN 1 ELSE 0 END) AS MD,  MAX(CASE WHEN a.[CLS] = 'V' THEN 1 ELSE 0 END) AS VI  FROM #TEMP1 a  INNER JOIN #TEMP2 b ON a.[EFF] <= b.[Date]  WHERE b.[Date] = b.[MonDate]  GROUP BY a.[MCK], b.[TID]  ORDER BY a.[MCK], a.[CLS] ) t ORDER BY t.[MCK]
Besides, you'd better provide the exact execution plan, so that we could know the exact effect of the query.
Best Regards,
Will
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com. Marked as answer by gk1393 Friday, September 8, 2017 7:59 PM
Friday, September 8, 2017 7:43 AM