How i select the last 5000 kilo that car is moved from table in c# winapp or used cursor

Question

0.00/5 (No votes)

See more:

for example:- i have table :-
Number , items , current_dist , prev_distance , value
------- ------- ---------- --- ------- ------
600 , 1 , 6000 , 3000 , 400
600 , 2 , 7000 , 6000 , 500
600 , 3 , 7500 , 7000 , 230

What I have tried:

I want to write a SQL Query on above table which should return the travel history of the car and for the respective kilometer. User will provide Car Number and KM as a parameter to this query

e.g. Suppose the parameters will be 600 and 4000 KM then result should be
first two rows from the above table

e.g. Suppose the parameters are 600 and 5000 KM then result should be
all rows from the above table

then I want to display this result into crystal report

Posted 6-Feb-16 22:26pm

Member 12244977

Updated 7-Feb-16 2:27am

RDBurmon

v3

Add a Solution

Comments

OriginalGriff 7-Feb-16 4:50am

This is not a good question - we cannot work out from that little what you are trying to do.
Remember that we can't see your screen, access your HDD, or read your mind.
Use the "Improve question" widget to edit your question and provide better information.

Member 12244977 7-Feb-16 4:54am

i want print the last 5000 kilo for this car number 600 is driven or moved , i have in this table current_distance and prev_distance for this car how i do that with cursor or with loop in C#

RDBurmon 7-Feb-16 8:31am

CP , I have updated your question and added the perfect answer for you. Having said that, read my updated question carefully and that will help you to understand how to ask question in CP portal

3 solutions

Solution 3

Below procedure will work for you -

SQL

--CREATE TABLE tbltravel (Number INT, items INT, current_dist INT, prev_distance INT, value INT)
--
--INSERT INTO tbltravel 
--SELECT 600 , 1 , 6000 , 3000 ,400 UNION ALL
--SELECT 600 , 2 , 7000 , 6000 , 500 UNION ALL
--SELECT 600 , 3 , 7500 , 7000 , 230

CREATE PROCEDURE spGetCarTravelHistory (@varNumber INT,@varDistance INT)
AS 
BEGIN
DECLARE @varttlrows INT
DECLARE @idx INT
DECLARE @ttlDistance INT


DECLARE @tmptbl AS TABLE(idx INT IDENTITY(1,1),Number INT, items INT, current_dist INT, prev_distance INT, value INT)

INSERT INTO @tmptbl (Number , items , current_dist , prev_distance , value )
SELECT * FROM tbltravel WHERE Number=@varNumber ORDER BY  current_dist DESC

SET @varttlrows =@@ROWCOUNT

SET @idx=1
SET @ttlDistance=0
WHILE @idx <= @varttlrows 
BEGIN
 
    SELECT @ttlDistance =@ttlDistance + (current_dist - prev_distance) FROM @tmptbl WHERE idx=@idx 
    IF (@ttlDistance > @varDistance) 
    BEGIN
       DELETE FROM @tmptbl WHERE idx=@idx 
    END 
     
    SET @idx = @idx + 1
END

SELECT Number , items , current_dist , prev_distance , value  
FROM @tmptbl ORDER BY prev_distance 
END

Posted 7-Feb-16 2:28am

RDBurmon

Solution 1

You may use a table function as data source for your report, that adds records to the output table until 5000 kilo is reached, than breaks out the loop and returns the resulting table

Posted 7-Feb-16 0:01am

NightWizzard

v2

Comments

Member 12244977 7-Feb-16 6:04am

yes :) How i do that please and thank you for help

NightWizzard 7-Feb-16 6:17am

See microsoft documentation for T-SQL, for example:
https://technet.microsoft.com/en-us/library/aa214485%28v=sql.80%29.aspx
or the codeprojecr article:
http://www.codeproject.com/Articles/167399/Using-Table-Valued-Functions-in-SQL-Server

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)

OriginalGriff · Accepted Answer · 2016-02-07T00:04:00

You can do it in SQL:

SQL

SELECT a.* FROM MyTable a
JOIN (SELECT Number, MAX(current_dist) as m FROM MyTable GROUP BY Number) b ON a.Number = b.Number
WHERE a.current_dist > m - 5000 AND a.Number = 600
ORDER BY current_dist DESC

"its work but can you explain it for me please and thanks"

It's pretty simple:
You have an "inner query" which returns the car number, and the maximum current_dist value for each car:

SQL

SELECT Number, MAX(current_dist) as m FROM MyTable GROUP BY Number

You then join that to each of the main table, so that each row has its maximum distance associated with it.

SQL

SELECT a.* FROM MyTable a
JOIN (...) b ON a.Number = b.Number

You then just filter it with a WHERE clause to get your "rows above distance":

SQL

WHERE a.current_dist > m - 5000 AND ...

and specific car number:

SQL

WHERE ... AND a.Number = 600

The final line just tells it to return them in descending order of current distance.

SQL

ORDER BY current_dist DESC

Have a look at W3Schools:
SQL GROUP BY Statement[^]
SQL Joins[^]
They will explain them better than I could! :laugh: