Between Dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Between Dates

Author

Topic

dionismatos
Starting Member

6 Posts

Posted - 2010-10-26 : 11:17:07

Hello all

I'm Having issues with a Stored procedure that gets data between dates. i provide the dates and send them via my VB.net App.

When i execute i notice that if for example i try to get all the records between those dates, i find out that i'm not getting the whole thing. What is the best way to search between dates?

Thanks in Advance!

Here is my code:


ALTER PROCEDURE [dbo].[SP_CARGATSAP]
	-- Add the parameters for the stored procedure here
	(@desde  datetime, @hasta  datetime)
AS
BEGIN
	SELECT A.*  
	FROM 
		(SELECT M.CARDNUMBER, MIN(M.MESSDATETIME) MESSDATETIME, min(M.OBJECT) OBJECT, SUBSTRING(O.LABEL,1,1) IND
		 FROM DBO.MESSAGES M
		 INNER JOIN DBO.OBJECT O ON (O.RECNUM = M.OBJECT)
		 WHERE OBJECT IN ('729', '751', '740', '773', '762', '784', '2845', '2834', '817', '828', '839', '850')
			 AND MESSDATETIME BETWEEN @DESDE AND @HASTA
			 AND ISACTIVE = '1'

		AND SUBSTRING(O.LABEL,1,1) = 'E'
		GROUP BY M.CARDNUMBER, SUBSTRING(O.LABEL,1,1)
UNION ALL
SELECT M.CARDNUMBER, MAX(M.MESSDATETIME) MESSDATETIME, max(M.OBJECT) OBJECT,  SUBSTRING(O.LABEL,1,1) IND
  FROM DBO.MESSAGES M
INNER JOIN DBO.OBJECT O ON (O.RECNUM = M.OBJECT)
 WHERE OBJECT IN ('729', '751', '740', '773', '762', '784', '2845', '2834', '817', '828', '839', '850')



	 AND MESSDATETIME BETWEEN @DESDE AND @HASTA
	 AND ISACTIVE = '1'
	 --AND MESSDATETIME >= @desde AND MESSDATETIME <= @hasta



GROUP BY M.CARDNUMBER, SUBSTRING(O.LABEL,1,1)) A
ORDER BY A.CARDNUMBER, A.MESSDATETIME





END

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-10-26 : 12:39:10

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Please learn to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

1) Do not prefix a procedure name with "sp_"; it has special meaning.
2) Never name something "silly_object" -- too vague. In fact, all your data element names violate ISO-11179 rules
3) A row number is a physical concept and has no business in a schema
4) Good SQL programmer do not use flags -- they use predicates.
5) We have DATE data types now.
6) the first letter of the something_label seems to be a meaningful data element that you de-normalized into a string. It probably needs its own column.
7) Since teh two subqueries are almost identical, they can probably be combined. But we need DDL to figure it out.
8) Here is a first clean up

CREATE PROCEDURE Cargatsap --weird name
(@in_start_date DATE, @in_end_date DATE) -- you forget the role prefixes
AS
BEGIN
SELECT A.*
FROM
(SELECT M.card_nbr, MIN (M.mess_datetime), MIN (M.silly_object),
SUBSTRING(O.something_label, 1, 1)
FROM Messages AS M, Objects AS O
WHERE O.rec_num = M.silly_object)
AND silly_object
IN ('729', '751', '740', '773', '762', '784', '2845', '2834', '817', '828', '839', '850')
AND mess_datetime BETWEEN @in_start_date AND @in_end_date
AND is_active_flg = '1' -- bad programming
AND SUBSTRING(O.something_label, 1, 1) = 'E' -- bad programming
GROUP BY M.card_nbr, SUBSTRING(O.something_label, 1, 1)

UNION ALL

SELECT M.card_nbr, MAX(M.mess_datetime), MAX(M.silly_object),
SUBSTRING(O.something_label, 1, 1)
FROM Messages AS M, Objects AS O
WHERE O.rec_num = M.silly_object
AND silly_object
IN ('729', '751', '740', '773', '762', '784', '2845', '2834', '817', '828', '839', '850')
AND mess_datetime BETWEEN @in_start_date AND @in_end_date
AND is_active_flg = '1' -- bad programming
GROUP BY M.card_nbr, SUBSTRING(O.something_label, 1, 1)-- bad programming
AS A (card_nbr, mess_datetime, silly_object, ind);

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-10-27 : 04:04:45

Note that DATE datatype is supported from version 2008 onwards

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources