how to get before date Rates using sqlserver?

Question

0.00/5 (No votes)

See more:

C#

select rateperkg from K_FS_FeedMrpDetails where date = getdate()-1

I want to display rateperkg column before date values

Posted 5-Jan-14 20:29pm

Siva Hyderabad

Add a Solution

Comments

Sibasisjena 6-Jan-14 2:33am

What actually you want to do ? I think you want the all records having inserted before today.

2 solutions

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)

Karthik_Mahalingam · Answer 1 · 2014-01-05T21:02:00

Solution 2

try like this.

SQL

select rateperkg  from  (select rateperkg   , ROW_NUMBER() over ( order by [date] desc ) as slno from K_FS_FeedMrpDetails  ) as temp
where slno = 2

Posted 5-Jan-14 21:02pm

Karthik_Mahalingam

Comments

Siva Hyderabad 6-Jan-14 4:17am

It's fine but it's based on slno

Karthik_Mahalingam 6-Jan-14 4:19am

you need the last date ( not today ) right ???
ignore slno .. that is used to get the last date.
one question: does the date column contains duplicate dates ?

Siva Hyderabad 6-Jan-14 4:28am

yes, but i tried another way
declare @DaVal as datetime

--111 will return yyyy/MM/dd
--101 will return MM//dd/yyyy
set @DaVal = convert(datetime, convert(varchar(25), DATEADD(d,-1,GETDATE()), 111))

select rateperkg from K_FS_FeedMrpDetails where date = @DaVal

Karthik_Mahalingam 6-Jan-14 4:30am

is this working ?

Siva Hyderabad 6-Jan-14 4:32am

yes ,it's working..it shows all rates of products before date

Karthik_Mahalingam 6-Jan-14 4:36am

u need only one rate??

then use select top 1 rateperkg from K_FS_FeedMrpDetails where date = @DaVal

Siva Hyderabad 9-Jan-14 4:20am

Hai katrhik,busy na? i hava a doubt?

Karthik_Mahalingam 9-Jan-14 4:22am

tell me ...

Siva Hyderabad 9-Jan-14 4:24am

select sum(AR.recamount) as Recamount,round(sum(OD1.noofbags*MR.rateperkg*BG.bagtype),2)as totalamount from K_FS_AmountReceived AR
inner join K_FS_OrderDetails1 OD1 on AR.orderno = OD1.ID
inner join K_FS_bagtype BG on BG.sno = OD1.bagtype
inner join k_FPS_FeedType FT on FT.sno = OD1.feedtype
inner join K_FS_FeedMrpDetails MR on FT.sno=MR.feedtype
where AR.Name = 'Mohan'and AR.orderno='198'
group by AR.recamount,OD1.noofbags,MR.rateperkg,BG.bagtype

if i execute this three rows come..but i want one row sum of total

Siva Hyderabad 9-Jan-14 4:27am

Here Mohan has only one column..but it's repeated..

Karthik_Mahalingam 9-Jan-14 4:29am

Hi sambasiva
can u post this in a new question, so that it wil get notifed by many users and they may reply soon..

Siva Hyderabad 9-Jan-14 4:30am

k,k...

Rajesh_DotNet · Answer 2 · 2014-01-05T20:42:00

Solution 1

Hi..,
Do this query

below query is for only Yesterday's date:
select rateperkg from K_FS_FeedMrpDetails where date = DATEADD(day,-1,getdate())

Query for all the date lesser then today's date:
select rateperkg from K_FS_FeedMrpDetails where date < DATEADD(day,-1,getdate())

this solves your problem..:)

Posted 5-Jan-14 20:42pm

Rajesh_DotNet

Updated 5-Jan-14 23:47pm

v3

Comments

Siva Hyderabad 6-Jan-14 4:11am

It gives all dates less than today date

Rajesh_DotNet 6-Jan-14 5:11am

then instead of specifying getdate(), use the target date or any date in where clause

Rajesh_DotNet 6-Jan-14 5:22am

Hey I edited my solution....I think that's what you need...right?

Siva Hyderabad 6-Jan-14 5:26am

kk.i check now

Rajesh_DotNet 6-Jan-14 5:48am

have you checked it?

Rajesh_DotNet 6-Jan-14 5:25am

your question is not clear...that's why everyone struggling to understand it...have a great day..:)

Rajesh_DotNet 6-Jan-14 5:44am

is that solution working?

Siva Hyderabad 6-Jan-14 7:01am

YA,i check it..it not working ..thanx for reply

Rajesh_DotNet 6-Jan-14 7:30am

If you are struggling to convey your question in English you may proceed in tamil, telugu or hindi. Pls dont mistake me, I am trying to help you.
Until now the question was not clear or give some other sample or pictorial representaion.

Rajesh_DotNet 6-Jan-14 9:39am

Yes.. I know telugu....you can explain it in telugu..

Rajesh_DotNet 7-Jan-14 0:22am

Yes...see this link http://www.codeproject.com/Questions/664301/Time-Picker-Control-in-Asp-net

Siva Hyderabad 7-Jan-14 0:24am

kk ,nice post

Rajesh_DotNet 7-Jan-14 7:49am

Yes tell me...

Rajesh_DotNet 7-Jan-14 8:19am

Ok.. :)

Rajesh_DotNet 7-Jan-14 8:48am

Rajesh

Rajesh_DotNet 7-Jan-14 8:53am

OK be in touch frnd.. :)

Siva Hyderabad 9-Jan-14 4:19am

rajesh..busy na?

Rajesh_DotNet 9-Jan-14 4:35am

Hi tell me..

Siva Hyderabad 3-Feb-14 6:14am

hi Rajesh..busy na?