How do I filter date range depend on another attribute value

Question

0.00/5 (No votes)

See more:

I have a DATASET with sequence of order and target date. I need to retrieve data in between given target date range and there should include entire sequence order from beginning to end.

TNARef      TargetDate        Sequence
----------     -------------        -----------
TNA0001    02/01/2016              1
TNA0001    02/02/2016              2
TNA0001    02/07/2016              3

TNA0002    02/02/2016              1
TNA0002    02/05/2016              2
TNA0002    02/08/2016              3

TNA0003    01/26/2016              1
TNA0003    02/01/2016              2
TNA0003    02/03/2016              3

TNA0004    01/20/2016              1
TNA0004    01/26/2016              2
TNA0004    02/02/2016              3

In my query i can get following result
Target Date: 02/01/2016 To 02/10/2016

TNARef      TargetDate        Sequence
----------     -------------        -----------
TNA0001    02/01/2016              1
TNA0001    02/02/2016              2
TNA0001    02/07/2016              3

TNA0002    02/02/2016              1
TNA0002    02/05/2016              2
TNA0002    02/08/2016              3

TNA0003    02/01/2016              2
TNA0003    02/03/2016              3

TNA0004    02/02/2016              3

But only need to filter

TNARef      TargetDate        Sequence
----------     -------------        -----------
TNA0001    02/01/2016              1
TNA0001    02/02/2016              2
TNA0001    02/07/2016              3

TNA0002    02/02/2016              1
TNA0002    02/05/2016              2
TNA0002    02/08/2016              3

How can i do that only by SQL? Please help me guys

Posted 3-Feb-16 0:54am

PromodNishan

Add a Solution

Comments

[no name] 3-Feb-16 7:11am

What is the query you have written?

Secondly as per your requirement it will not record "TNA0003 02/01/2016 2" and why?

PromodNishan 4-Feb-16 22:17pm

I need to retrieve only records with complete sequence (actually its a order of sequence). There should be sequence number 1 in every set of record. how it can be possible?

ZurdoDev 3-Feb-16 7:57am

I don't understand what you want. It sounds like you want records between 2 dates, which is very simple to do so what exactly is your question?

PromodNishan 4-Feb-16 22:17pm

I need to retrieve only records with complete sequence (actually its a order of sequence). There should be sequence number 1 in every set of record. how it can be possible?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tomas Takac · Accepted Answer · 2016-02-03T21:04:00

Solution 1

If I understand you correctly you want to select only if the entire sequence falls into the range. First you need to know the max and min date for each reference and then filter based on that. Assuming you are using SQL Server and have two parameters named @FromTargetDate and @ToTargetDate:

SQL

with cte1 as 
(
    SELECT TNARef, Min(TargetDate) AS MinDate, Max(TargetDate) AS MaxDate
    FROM yourtable
    GROUP BY TNARef
)
, cte2 AS
(
    SELECT TNARef
    FROM cte1
    WHERE MinDate >= @FromTargetDate
      AND MaxDate <= @ToTargetDate 
)
SELECT *
FROM yourtable
WHERE TNARef IN (SELECT TNARef FROM cte2)

Of course the query could be structured differently and optimized, but I like to use CTEs because they are easier to follow.

Posted 3-Feb-16 21:04pm

Tomas Takac

Comments

PromodNishan 4-Feb-16 22:24pm

dear Tomas your solution quit right for me but when i apply it to my solution it popup an error. Please look at my code segment..

<pre lang="SQL">WITH CTRN1 AS(
SELECT TN.tnaRef AS TNAREF, CONVERT(VARCHAR(11), TND.TargetDate, 106) AS TargetDate, OAD.Sequance AS Sequence, MIN(OAD.Sequance) AS MINS, MAX(OAD.Sequance) AS MAXS
FROM dbo.tnaDetail AS TND INNER JOIN
dbo.TNA AS TN ON TND.tnaID = TN.tnaID INNER JOIN
dbo.JobGroupsDetail AS JD ON TND.JobOrderDetailID = JD.JobGroupsDetailID INNER JOIN
dbo.JobGroups AS JG ON JD.JobGroupsID = JG.JobGroupID INNER JOIN
dbo.JobGroupsJobs AS JJ ON JG.JobGroupID = JJ.JobGroupID INNER JOIN
dbo.JobOrder AS JO ON JO.JobOrderID = JJ.JobID INNER JOIN
dbo.OrderDetail AS OD ON OD.OrderDetailID = JO.OrderDetailID INNER JOIN
dbo.[Order] AS O ON O.OrderID = OD.OrderID INNER JOIN
dbo.Configuration AS S ON OD.StyleID = S.ConfigurationID INNER JOIN
dbo.Industry AS Z ON Z.IndustryID = OD.SizeID INNER JOIN
dbo.OrderActivityDetail AS OAD ON JD.OrderActivityDetailID = OAD.OrderActivityDetailID INNER JOIN
ProcessFlowdetail PFD ON PFD.ProcessFlowDetailID=OAD.ProcessFlowDetailID INNER JOIN
dbo.Process AS P ON PFD.ProcessID = P.ProcessID INNER JOIN
dbo.Venue AS D ON D.VenueID = P.DepartmentID INNER JOIN
dbo.ProcessActivity AS PA ON OAD.ProcessActivityID = PA.ProcessActivityID INNER JOIN
dbo.ProcessActivityDetail AS PAD ON PAD.ProcessActivityDetailID = JD.ProcessActivityDetailID INNER JOIN
dbo.Activity AS A ON A.ActivityID = PAD.ActivityID INNER JOIN
dbo.ComplexityDetail AS SC ON JD.StyleComplexityDetailID = SC.ComplexityDetailID INNER JOIN
dbo.[User] AS U ON TN.DateSuggestedBy = U.UserID INNER JOIN
dbo.Facility AS F ON O.ProductID = F.FacilityID INNER JOIN
dbo.FacilityCategory AS FC ON F.FacilityCategoryID = FC.FacilityCategoryID
WHERE (TN.isPublished = 1) --AND (U.VenueID = @VenueID OR P.DepartmentID = @VenueID ) AND TND.TargetDate BETWEEN @FromtDate AND @ToDate
GROUP BY TN.tnaRef,TND.TargetDate, OAD.Sequance
ORDER BY TN.tnaRef, OAD.Sequance
), CTRN2 AS
(
SELECT TNAREF
FROM CTRN1
WHERE MINS = 1
)
SELECT TOP (100) PERCENT S.Name AS Style, F.Name AS Product,FC.Name AS [ProductCategory],
Z.Name AS Size, O.OrderRef,O.DepartmentOrderRef, TN.tnaRef, JG.JobGroupRef,SUM(JJ.Quantity) AS QTY, P.Name AS Process,
CONVERT(VARCHAR(11), TND.TargetDate, 106) AS TargetDate, CONVERT(VARCHAR(11), TND.CompletedDate, 106) AS CompletedDate, OAD.Sequance, TND.isCompleted
FROM dbo.tnaDetail AS TND INNER JOIN
dbo.TNA AS TN ON TND.tnaID = TN.tnaID INNER JOIN
dbo.JobGroupsDetail AS JD ON TND.JobOrderDetailID = JD.JobGroupsDetailID INNER JOIN
dbo.JobGroups AS JG ON JD.JobGroupsID = JG.JobGroupID INNER JOIN
dbo.JobGroupsJobs AS JJ ON JG.JobGroupID = JJ.JobGroupID INNER JOIN
dbo.JobOrder AS JO ON JO.JobOrderID = JJ.JobID INNER JOIN
dbo.OrderDetail AS OD ON OD.OrderDetailID = JO.OrderDetailID INNER JOIN
dbo.[Order] AS O ON O.OrderID = OD.OrderID INNER JOIN
dbo.Configuration AS S ON OD.StyleID = S.ConfigurationID INNER JOIN
dbo.Industry AS Z ON Z.IndustryID = OD.SizeID INNER JOIN
dbo.OrderActivityDetail AS OAD ON JD.OrderActivityDetailID = OAD.OrderActivityDetailID INNER JOIN
ProcessFlowdetail PFD ON PFD.ProcessFlowDetailID=OAD.ProcessFlowDetailID INNER JOIN
dbo.Process AS P ON PFD.ProcessID = P.ProcessID INNER JOIN
dbo.Venue AS D ON D.VenueID = P.DepartmentID INNER JOIN
dbo.ProcessActivity AS PA ON OAD.ProcessActivityID = PA.ProcessActivityID INNER JOIN
dbo.ProcessActivityDetail AS PAD ON PAD.ProcessActiv

Tomas Takac 5-Feb-16 2:28am

What error?

PromodNishan 5-Feb-16 2:31am

Order by statement not allow in views. I fixed the error. your solution is great. thanks

PromodNishan 5-Feb-16 2:29am

#Tomas Takac:
Thanks for help. Your method helps me to resolve my problem. Thanks again

Jörgen Andersson 5-Feb-16 3:53am

The only optimization I can think of is to put both CTEs into one, like this:
with cte as
(
SELECT TNARef
FROM yourtable
HAVING Min(TargetDate) >= @FromTargetDate
AND Max(TargetDate) <= @ToTargetDate
GROUP BY TNARef
)
But it won't make any difference for performance.

Tomas Takac 5-Feb-16 4:18am

Yes. Or I could inline cte2 directly into the the In clause. or maybe use a JOIN or EXISTS instead of IN. I just felt this is the most readable form. In such a simple example all of these will be executed the same. But it's up to OP to fine tune it in the real world scenario.

Jörgen Andersson 5-Feb-16 4:26am

I'm willing to bet that the optimizer would realize that a join is the same ting in this case and create such a plan in any case. I'd keep the readability.