Need help in SQL query

Question

1.00/5 (1 vote)

See more:

SQL

DECLARE @TB table (ItemId INT, PriceUpdateDate DATE, Price DECIMAL(18,3))
INSERT INTO @TB VALUES (101,'2018-07-20', 375)
INSERT INTO @TB VALUES (101,'2018-07-25', 390)
INSERT INTO @TB VALUES (101,'2018-08-10', 400)
INSERT INTO @TB VALUES (101,'2018-08-15', 450)
INSERT INTO @TB VALUES (101,'2018-08-17', 300)

INSERT INTO @TB VALUES (102,'2018-07-17', 400)
INSERT INTO @TB VALUES (102,'2018-07-18', 450)
INSERT INTO @TB VALUES (102,'2018-07-19', 300)
INSERT INTO @TB VALUES (102,'2018-08-05', 375)
INSERT INTO @TB VALUES (102,'2018-08-15', 390)


SELECT * FROM @TB


DECLARE @FromDate DATE='2018-08-01',@ToDate DATE='2018-08-31'

select * from @TB where PriceUpdateDate between @FromDate and @ToDate

There are records for the criteria given above which is as per my requirement.But let us suppose there is no record corresponding to @FromDate and @ToDate that we passed.
In that case i want the last updated record i.e. very first record before @FromDate which is '2018-07-25' for ItemId=101 and '2018-07-19' for ItemId=102

What I have tried:

I tried

SQL

DECLARE @FromDate DATE='2018-08-01',@ToDate DATE='2018-08-31'

select * from @TB where PriceUpdateDate between @FromDate and @ToDate

Posted 21-Aug-18 21:21pm

xpertzgurtej

Updated 22-Aug-18 6:03am

Nelek

v2

Add a Solution

2 solutions

Solution 1

SQL

DECLARE @FromDate DATE='2018/08/01',@ToDate DATE='2018/08/31'

select * from @TB where PriceUpdateDate between @FromDate and @ToDate

not - but /

Posted 22-Aug-18 5:16am

Herman<T>.Instance

Comments

CHill60 22-Aug-18 11:52am

This doesn't answer the OP's question and there is nothing wrong with using - for dates

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)

CHill60 · Accepted Answer · 2018-08-22T06:04:00

You need to know which dates are relevent for each item ...

SQL

DECLARE @m TABLE (ItemId INT, MaxDate DATE)
insert into @m SELECT ItemId, MAX(PriceUpdateDate) FROM @TB WHERE PriceUpdateDate < @FromDate GROUP BY ItemId

and then you can do something if no records are returned from your original query

SQL

if exists (select top 1 * from @TB where PriceUpdateDate between @FromDate and @ToDate)
	select * from @TB where PriceUpdateDate between @FromDate and @ToDate
else
	select T1.* from @TB T1 INNER JOIN @M T2 ON T1.ItemId = T2.ItemId AND T1.PriceUpdateDate = T2.MaxDate