how to retrive all records between the range of dates ?

Question

0.00/5 (No votes)

See more:

hi,

for my application i have a table with following columns

ID   MONTH  NAME       ADDRESS        DATE

1     10    'A'        'HYD'          2012-10-24
2     11    'B'        'AMPT'         2012-11-12
3     01    'C'        'SEC'          2013-01-03

i want the result as when i am giving input dates as @From_date= 2012-10-24 and @Todate=2013-01-03

then all the three records between these dates must be retrived. ie, 1,2,3

Posted 23-Jan-13 22:57pm

sk. maqdoom ali

Updated 23-Jan-13 22:59pm

Aarti Meswania

v3

Add a Solution

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)

Aarti Meswania · Answer 1 · 2013-01-23T23:01:00

Solution 1

this way...

SQL

select * from TblName where date between @From_date and @Todate

Happy Coding!
:)

Posted 23-Jan-13 23:01pm

Aarti Meswania

Comments

ujju.1 24-Jan-13 5:12am

+5 :)

Aarti Meswania 24-Jan-13 8:44am

Thank you! :)

sk. maqdoom ali 24-Jan-13 8:35am

thank u

Aarti Meswania 24-Jan-13 8:43am

welcome! :)
Glad to help you! :)

sk. maqdoom ali 25-Jan-13 8:23am

happy weekend

sk. maqdoom ali 30-Jan-13 4:55am

hi,

for my application i will pass the input Date and

i want the result as the day before input Date and also check
TIME_FORMAT(ADate,'%H:%i:%s') >'6:00:00' from the table data.

Aarti Meswania 30-Jan-13 4:59am

1. for display result as the day before input Date, query like below...
select * from TblName where date < @Input_date
2. what is meaning of "check
TIME_FORMAT(ADate,'%H:%i:%s') >'6:00:00' from the table data" ?

sk. maqdoom ali 30-Jan-13 5:06am

1.we can write SELECT DATEADD(ADate,INTERVAL -1 DAY)
ex:2013-01-29 15:34:16
2.TIME_FORMAT(ADate,'%H:%i:%s') gives result as 15:34:16
and i want to check 15:34:16 > 6:00:00
the above two will be check in the single query

Aarti Meswania 30-Jan-13 5:13am

it's unclear,
please post your question as New question with full detail and example input-outputs
:)

sk. maqdoom ali 13-Feb-13 1:09am

hi, i am having a table PackageDetails
having data as below
Package_ID Package_Name [Level] Description Main_Package_Id
5 Project 1A 2 Project 1B NULL
6 PKG-1 3 Package 1 5
7 PKG-2 3 Package 2 5
8 PKG-3 3 Package 3 13
9 PKG-4 3 Package 4 13
13 Project 1B 2 Project 2B NULL
14 Project 2 2 Project 2 NULL
12 PKG-7 3 Package 7 14
---- and so on
from the above table data 5,13 are Main_Package_Id's so we are having NULL value in that Main_Package_Id column. Package_ID's 6,7 are sub_packages of 5, similarly Package_ID's 8,9 are are sub_packages of 13.

now i want the output in below format:
Package_ID Package_Name [Level] Description Main_Package_Id
5 Project 1A 2 Project 1B NULL
6 PKG-1 3 Package 1 5
7 PKG-2 3 Package 2 5
13 Project 1B 2 Project 2B NULL
8 PKG-3 3 Package 3 13
9 PKG-4 3 Package 4 13
?????
??

Aarti Meswania 13-Feb-13 1:14am

where is date in this picture???
you was asking about date and here no field which have date

sk. maqdoom ali 13-Feb-13 1:19am

sorry ,,i posted this question here for u ,
there is no date column here,
i mentioned the all requirement that i want

Aarti Meswania 13-Feb-13 1:20am

it's simple just write query

select * from tablename where Main_Package_Id is null or Main_Package_Id in (5,13)

sk. maqdoom ali 13-Feb-13 1:34am

select * from Package_Details where Main_Package_Id is null or Main_Package_Id in (5,13)
with this query i am getting the same result as
Package_ID Package_Name [Level] Description Main_Package_Id
5 Project 1A 2 Project 1B NULL
6 PKG-1 3 Package 1 5
7 PKG-2 3 Package 2 5
8 PKG-3 3 Package 3 13
9 PKG-4 3 Package 4 13
13 Project 1B 2 Project 2B NULL
but i want as
5 Project 1A 2 Project 1B NULL
6 PKG-1 3 Package 1 5
7 PKG-2 3 Package 2 5
similarly
13 Project 1B 2 Project 2B NULL
8 PKG-3 3 Package 3 13
9 PKG-4 3 Package 4 13

Aarti Meswania 13-Feb-13 1:38am

select p.* from Package_Details p
where p.Main_Package_Id =5 or ( p.Main_Package_Id is null and Package_ID=5)

sk. maqdoom ali 13-Feb-13 1:52am

using u r query , i am getting
Package_ID Package_Name [Level] Description Main_Package_Id
5 Project 1A 2 Project 1B NULL
5 Project 1A 2 Project 1B NULL

i modified that query as

select p1.* from Package_Details p left join Package_Details p1
on p.Package_ID=p1.Package_ID where p.Main_Package_Id in (5,13)
and getting result as
Package_ID Package_Name [Level] Description Main_Package_Id
6 PKG-1 3 Package 1 5
7 PKG-2 3 Package 2 5
8 PKG-3 3 Package 3 13
9 PKG-4 3 Package 4 13

but here
5 Project 1A 2 Project 1B NULL
13 Project 1B 2 Project 2B NULL
are missing

Aarti Meswania 13-Feb-13 1:53am

I have update that query
select p.* from Package_Details p
where p.Main_Package_Id =5 or ( p.Main_Package_Id is null and Package_ID=5)

sk. maqdoom ali 13-Feb-13 2:14am

so nice, thanks a lot

Aarti Meswania 13-Feb-13 2:16am

welcome! :)
Glad to help you!
:)

sk. maqdoom ali 13-Feb-13 1:54am

the result must be like this
5 Project 1A 2 Project 1B NULL
6 PKG-1 3 Package 1 5
7 PKG-2 3 Package 2 5
13 Project 1B 2 Project 2B NULL
8 PKG-3 3 Package 3 13
9 PKG-4 3 Package 4 13

Aarti Meswania 13-Feb-13 2:15am

select * from
(
select p.Package_ID,p.Package_Name ,p.[Level] ,p.Description , case when p.Main_Package_Id is null then p.Package_ID else p.Main_Package_Id end as Main_Package_Id
from Package_Details p
left join Package_Details p1 on p.Package_ID=p1.Package_ID
) as a
where a.Main_Package_Id in (5,13)

sk. maqdoom ali 17-Feb-13 23:37pm

hai,, gd mrng
how to calculate Cumulative value
Date Prod_Type Chainage Material_Id QTY_FTD Last_Updated
2013-02-13 1 192 2 120.00 2013-02-13 13:27:21.660
2013-02-13 1 247 2 560.00 2013-02-13 13:27:21.500
2013-02-13 1 192 2 400.00 2013-02-13 13:27:21.400
2013-02-12 1 192 2 150.00 2013-02-13 13:27:21.320
2013-02-14 1 192 2 200.00 2013-02-13 13:27:21.100
from the above table 192,247 are Chainage's , 2's are material_Id's, 1's are Production_Type's , 120.00 , 400.00 's are QTY_FTD 's

i want to calculate the QTY_Cumilative ie,i will pass Material_Id , Production_Type and i want to calculate the SUM(QTY_FTD) from the Last Last_Updated date for a particular Chainage

output required:
Prod_Type Chainage Material_Id QTY_Cumilative
1 192 2 1270
1 247 2 560

sk. maqdoom ali 17-Feb-13 23:43pm

i written the following query.
(select sum(Qty_FTD) from Production_Details pr where Material_Id=@MaterialType AND Production_Type=@ProductionType
and pr.Last_Updated <= pr.Last_Updated )

i am getting output as
Prod_Type Chainage Material_Id QTY_Cumilative
1 192 2 1830
1 247 2 1830

Aarti Meswania 17-Feb-13 23:57pm

please post it as new question because it's new topic

ali from hyd 23-Apr-13 8:00am

hi,

i have three tables

tableName: Mst_NDT

level_id Typejoint_Id Root_visual Final_Visual
1 1 100 100
2 1 100 10
3 1 100 100
4 1 100 100
5 1 100 100

tableName:Mst_TypeJoint

TypeJoint_Id Type_Joint Type
1 Butt Weld 1
2 Nozzle Weld 1
3 Socket Weld 1
4 Seal Weld 1

tableName:Mst_LevelInspection
Level_Id Level_Inspection
1 Level - 02
2 Level - 05
3 Level - 03
4 Level - 1B

i want to split my output as

Level - 02 100 10 Butt Weld 100 20 Nozzle Weld
Level - 05 100 10 Butt Weld 100 20 Nozzle Weld

i written the following query

select L.Level_Inspection,N.Root_Visual,N.Final_Visual,T.Type_Joint
from Mst_NDT N,Mst_TypeJoint T,Mst_LevelInspection L
where T.TypeJoint_Id=N.TypeJoint_Id and L.Level_Id=N.Level_Id ;

ali from hyd 14-May-13 6:11am

hi aarthi,
for my table i am having the data as below

RF_Code Date Time
104 2013-01-01 09:00:00
104 2013-01-01 18:12:00
104 2013-01-02 09:02:00
104 2013-01-02 19:05:00
105 2013-01-01 09:30:00
105 2013-01-01 18:30:00

for a particular month i need all the Intimes and Outtimes of my emplorees

i will give Date='2013-01-01' as input and i want to get output in the following way

01-01-2013 02-01-2013
RF_Code Min OutTime Min OutTime
104 09:00:00 18:12:00 09:02:00 19:05:00
105 09:30:00 18:30:00

Aarti Meswania 14-May-13 8:22am

post new question

and for just display you should do row_number and get intime and outtime by pivot then do date wise grouping in report

ali from hyd 15-May-13 1:02am

i written the following procedure and i am getting only one type of time only ie., By selecting min time or max time.

i want to concatenate both min time and max time with comma seperated

use ecampus;
DELIMITER $$
DROP PROCEDURE IF EXISTS `GetTimeByDate` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetTimeByDate`(in sdate date)
BEGIN
declare finish int default 0;
declare cdate date;
declare str varchar(10000) default "select RF_Code,";

declare curs cursor for select Date from ecampus.log where (month(Date)=month(sdate) AND YEAR(Date)=YEAR(sdate)) group by Date;
declare continue handler for not found set finish = 1;
open curs;
my_loop:loop
fetch curs into cdate;
if finish = 1 then
leave my_loop;
end if;
set str = concat(str, "min(case when Date = '",cdate,"' then Time else null end) as `",cdate,"`,");
end loop;
close curs;
set str = substr(str,1,char_length(str)-1);

set @str = concat(str," from ecampus.log
group by RF_Code");

prepare stmt from @str;
execute stmt;
deallocate prepare stmt;
END $$

DELIMITER ;

i am getting output as below:

RF_Code 2013-01-01 2013-01-02
104 09:00:00 09:02:00
105 09:30:00 null

required output is:

RF_Code 2013-01-01 2013-01-02
104 09:00:00,18:12:00 09:02:00,19:05:00
105 09:30:00,18:30:00 null,null

ujju.1 · Answer 2 · 2013-01-23T23:09:00

Solution 2

Refer this Linl[^]

or This Link[^]

Posted 23-Jan-13 23:09pm

ujju.1