September 15, 2018 at 2:53 am
Create table #TEMP
(
ID INT
)
Create table #TEMP1
(
ID INT,
Letter_Type VARCHAR(100),
Letter_Sent_Date DATE
)
INSERT INTO #TEMP VALUES (1),(2),(3),(4)
GO
INSERT INTO #TEMP1 VALUES
(1,'A','01/01/2017'),
(1,'B','01/02/2017'),
(1,'C','01/03/2018'),
(1,'D','01/04/2018'),
(2,'A','01/01/2017'),
(2,'B','01/02/2017'),
(2,'C','01/10/2018'),
(2,'D','01/12/2018')
I'm trying to achieve below results - data should be based on date.
Suppose I want to know any letter sent after '01/05/2018' for letter type C. For ID 1 there is no letter C - in that case, we need to print null value.
I'm trying to do it in single statement as query I currently have is super big due to couple of joins used.
Any help is much appreciated. Thanks!
OUTPUT
1,NULL,NULL
2,C,'01/10/2018'
September 15, 2018 at 2:52 pm
Try this:
select a.ID
,b.*
from
#TEMP a
left join #TEMP1 b on
b.ID = a.ID
and b.Letter_Type = 'C'
and b.Letter_Sent_Date >= '01/05/2018'
where exists (select null from #TEMP1 c where c.ID = a.ID)
September 16, 2018 at 1:32 am
You can do this in a single select statement using simple aggregation. This is a much faster method if you are working with large data sets.
😎
USE TEEST;
GO
SET NOCOUNT ON;
--/*
Create table #TEMP1
(
ID INT,
Letter_Type VARCHAR(100),
Letter_Sent_Date DATE
)
INSERT INTO #TEMP1 VALUES
(1,'A','01/01/2017'),
(1,'B','01/02/2017'),
(1,'C','01/03/2018'),
(1,'D','01/04/2018'),
(2,'A','01/01/2017'),
(2,'B','01/02/2017'),
(2,'C','01/10/2018'),
(2,'D','01/12/2018');
-- Index that avoids having to sort the set
CREATE NONCLUSTERED INDEX TEMP1_ID_DATE_INCL_LETTER ON #TEMP1(ID ASC,Letter_Sent_Date ASC) INCLUDE (Letter_Type);
--*/
DECLARE @SDATE DATE = '01/05/2018';
SELECT
TT.ID
,MAX(CASE
WHEN TT.Letter_Type = 'C' AND TT.Letter_Sent_Date >= @SDATE THEN 'C'
ELSE NULL
END)
,MAX(CASE
WHEN TT.Letter_Sent_Date >= @SDATE THEN TT.Letter_Sent_Date
ELSE NULL
END)
FROM #TEMP1 TT
GROUP BY TT.ID;
DROP TABLE #TEMP1;
September 16, 2018 at 10:51 pm
Eirikur Eiriksson - Sunday, September 16, 2018 1:32 AMYou can do this in a single select statement using simple aggregation. This is a much faster method if you are working with large data sets.
😎
USE TEEST;
GO
SET NOCOUNT ON;
--/*
Create table #TEMP1
(
ID INT,
Letter_Type VARCHAR(100),
Letter_Sent_Date DATE
)INSERT INTO #TEMP1 VALUES
(1,'A','01/01/2017'),
(1,'B','01/02/2017'),
(1,'C','01/03/2018'),
(1,'D','01/04/2018'),
(2,'A','01/01/2017'),
(2,'B','01/02/2017'),
(2,'C','01/10/2018'),
(2,'D','01/12/2018');-- Index that avoids having to sort the set
CREATE NONCLUSTERED INDEX TEMP1_ID_DATE_INCL_LETTER ON #TEMP1(ID ASC,Letter_Sent_Date ASC) INCLUDE (Letter_Type);
--*/DECLARE @SDATE DATE = '01/05/2018';
SELECT
TT.ID
,MAX(CASE
WHEN TT.Letter_Type = 'C' AND TT.Letter_Sent_Date >= @SDATE THEN 'C'
ELSE NULL
END)
,MAX(CASE
WHEN TT.Letter_Sent_Date >= @SDATE THEN TT.Letter_Sent_Date
ELSE NULL
END)
FROM #TEMP1 TT
GROUP BY TT.ID;DROP TABLE #TEMP1;
I see that #Temp table is missed in this solution, #Temp is main table and there are chances all ID's in #temp will not exist in #Temp1. I could of posted in original question
September 16, 2018 at 10:53 pm
femi.olaniyan - Saturday, September 15, 2018 2:52 PMTry this:
select a.ID
,b.*
from
#TEMP aleft join #TEMP1 b on
b.ID = a.ID
and b.Letter_Type = 'C'
and b.Letter_Sent_Date >= '01/05/2018'
where exists (select null from #TEMP1 c where c.ID = a.ID)
As posted in original question, i' m trying to avoid exists/ not in. I will try you;re solution and see how it is working. Thanks for response and time.
September 16, 2018 at 11:50 pm
Why would you want to avoid EXISTS? That's a really odd requirement. Is this a homework assignment?
The great thing about EXISTS is it reads until it finds a disqualifying record and then stops.
September 17, 2018 at 12:13 am
koti.raavi - Sunday, September 16, 2018 10:51 PMEirikur Eiriksson - Sunday, September 16, 2018 1:32 AMYou can do this in a single select statement using simple aggregation. This is a much faster method if you are working with large data sets.
😎
USE TEEST;
GO
SET NOCOUNT ON;
--/*
Create table #TEMP1
(
ID INT,
Letter_Type VARCHAR(100),
Letter_Sent_Date DATE
)INSERT INTO #TEMP1 VALUES
(1,'A','01/01/2017'),
(1,'B','01/02/2017'),
(1,'C','01/03/2018'),
(1,'D','01/04/2018'),
(2,'A','01/01/2017'),
(2,'B','01/02/2017'),
(2,'C','01/10/2018'),
(2,'D','01/12/2018');-- Index that avoids having to sort the set
CREATE NONCLUSTERED INDEX TEMP1_ID_DATE_INCL_LETTER ON #TEMP1(ID ASC,Letter_Sent_Date ASC) INCLUDE (Letter_Type);
--*/DECLARE @SDATE DATE = '01/05/2018';
SELECT
TT.ID
,MAX(CASE
WHEN TT.Letter_Type = 'C' AND TT.Letter_Sent_Date >= @SDATE THEN 'C'
ELSE NULL
END)
,MAX(CASE
WHEN TT.Letter_Sent_Date >= @SDATE THEN TT.Letter_Sent_Date
ELSE NULL
END)
FROM #TEMP1 TT
GROUP BY TT.ID;DROP TABLE #TEMP1;
I see that #Temp table is missed in this solution, #Temp is main table and there are chances all ID's in #temp will not exist in #Temp1. I could of posted in original question
This query produces exactly the output you posted, can you clarify this further please?
😎
If you need to list all the IDs in #TEMP and the matching entries from #TEMP1 then simply place this query in a CTE and then join the CTE to #TEMP. The query will then only have to scan each table/index once.
USE TEEST;
GO
SET NOCOUNT ON;
--/*
Create table #TEMP
(
ID INT PRIMARY KEY CLUSTERED
);
INSERT INTO #TEMP VALUES (1),(2),(3),(4);
Create table #TEMP1
(
ID INT,
Letter_Type VARCHAR(100),
Letter_Sent_Date DATE
)
INSERT INTO #TEMP1 VALUES
(1,'A','01/01/2017'),
(1,'B','01/02/2017'),
(1,'C','01/03/2018'),
(1,'D','01/04/2018'),
(2,'A','01/01/2017'),
(2,'B','01/02/2017'),
(2,'C','01/10/2018'),
(2,'D','01/12/2018');
-- Index that avoids having to sort the set
CREATE NONCLUSTERED INDEX TEMP1_ID_DATE_INCL_LETTER ON #TEMP1(ID ASC,Letter_Sent_Date ASC) INCLUDE (Letter_Type);
--*/
DECLARE @SDATE DATE = '01/05/2018';
;WITH BASE_DATA AS
(
SELECT
TT.ID
,MAX(CASE
WHEN TT.Letter_Type = 'C' AND TT.Letter_Sent_Date >= @SDATE THEN 'C'
ELSE NULL
END) AS Letter_Type
,MAX(CASE
WHEN TT.Letter_Sent_Date >= @SDATE THEN TT.Letter_Sent_Date
ELSE NULL
END) AS Letter_Sent_Date
FROM #TEMP1 TT
GROUP BY TT.ID
)
SELECT
T.ID
,BD.Letter_Type
,BD.Letter_Sent_Date
FROM BASE_DATA BD
RIGHT OUTER JOIN #TEMP T
ON BD.ID = T.ID
DROP TABLE #TEMP;
DROP TABLE #TEMP1;
Output
ID Letter_Type Letter_Sent_Date
----------- ----------- ----------------
1 NULL NULL
2 C 2018-01-12
3 NULL NULL
4 NULL NULL
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply