Using Date Ranges in SQL Server 2005

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

, +

I am having a problem with a query I am writing and would like to see if you guys could help out some? I have two integer fields with the same name in two different tables that I am trying to sum to get the total volume in a specified date range and my query is returning the worng results and I cannot figure out what is wrong. This is the query I am using:
(date is the primary key in both tables)

SQL

SELECT Sum(BOSS) AS TotalVolInRange
FROM Keyed_Data AS A INNER JOIN Stored_Vol_Counts AS B
ON A.Date = B.Date
WHERE A.Date >='2012-09-09' and <'2012-09-15' AND B.Date >= '2012-09-09' and <'2012-09-15'AND A.DisplayAsID = 35 AND B.DisplayAsID = 35;

I have also used this to no avail:

SQL

SELECT Sum(BOSS) AS TotalVolInRange
FROM Keyed_Data AS A INNER JOIN Stored_Vol_Counts AS B
ON A.Date = B.Date
WHERE A.Date between '2012-09-09' and '2012-09-15' AND B.Date between '2012-09-09' and '2012-09-15'AND A.DisplayAsID = 35 AND B.DisplayAsID = 35;

And this:

SQL

SELECT Sum(BOSS) AS TotalVolInRange
FROM Keyed_Data AS A INNER JOIN Stored_Vol_Counts AS B
ON A.Date = B.Date
WHERE A.Date between '09/09/12' and '09/15/12' AND B.Date between '09/09/12' and '09/15/12' AND A.DisplayAsID = 35 AND B.DisplayAsID = 35;

Any help would be greatly appreciated.This is the first time I have posted a question but use the site often as a reference :)

Thanks in advance,
BrianS.

Posted 1-Oct-12 10:11am

BrianS1

Add a Solution

Comments

skydger 1-Oct-12 16:30pm

Why don't you use join of A.DisplayAsID = B.DisplayAsID? It might help

BrianS1 2-Oct-12 8:50am

I will try that today and see if it will work. I think the problem is with the dates not working correctly. Date is the primary key and is a smalldatetime field and i am not sure that I am getting all of the records I need. I also tried a few other things using the DateAdd() and also Convert() and neither will work correctly for me or I am using them incorrectly? I will try what you have suggested and comment back later.

Thanks skydger!

BrianS1 2-Oct-12 11:38am

It did not work Skydger. The total it is pulling is way lower than the total that I have calculated manually. So the query is excluding results that should be included. I was just looking at the HTML code for the web page that pulls this data and there is a javascript function getweeks() that is being used to break the date up into 'year', 'month', and 'week' but I am not sure yet if this is effecting the dates that get passed from the web page itself or not? That may effect the way the query works also. But right now I am hard coding the dates into the query to make sure I get the correct results before moving on the coding the web page itself. I have another quick question if anybody is able to answer it: Can you wrap a calculated field in IsNULL() and: When you do it asks for two arguments and I only have one since Iam not putting the IsNULL around a field but around a collection of fields that are peforming a SUM and Division and then I need it to send a '0' if either side of the equation has NULLS or '0' so I do not get a divide by zero error when I run it.... I may need to post a new question for that

Thanks again,
BrianS.

skydger 3-Oct-12 2:54am

Does these dates in different tables really equal to each other? Is it possible that dates differs for miliseconds? If so, you could try to use DATEPART(Year, A.Date) = DATEPART(Year, B.Date) etc. If not, it would be great if you provide example of your tables.

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)

Maciej Los · Answer 1 · 2012-10-02T09:36:00

Solution 1

The smalldatetime[^] fields contain a date that is combined with a time of day. The time is based on a 24-hour day, with seconds always zero (:00) and without fractional seconds. See example and its output.

SQL

DECLARE @mydate smalldatetime

SET DATEFORMAT ymd;
SET @mydate = GETDATE()
SELECT @mydate as [CurrentDate]

Output:

[CurrentDate]
-------------------
2012-10-02 21:01:00

To get only date part, use CONVERT or CAST[^] function.

SQL

SELECT CONVERT(NVARCHAR(10), @mydate,121) as [CurrentDate]

Output:

[CurrentDate]
-------------------
2012-10-02

Finally... to correctly join tables on date field, you need to "reject" the time part... But after that operation i'm not sure that your data will be unique.

SQL

SELECT CONVERT(SMALLDATETIME, CONVERT(NVARCHAR(10), @mydate,121)) as [CurrentDate]

Output:

[CurrentDate]
-------------------
2012-10-02 00:00:00

I suggest you to create temporary table, load the corrected data into this table and select...

SQL

SET DATEFORMAT mdy;

CREATE TABLE #tt (TotalVol INT, MyDate SMALLDATETIME, ID INT)

INSERT INTO #tt (TotalVol, MyDate, ID)
SELECT [BOSS] AS [TotaVol], CONVERT(SMALLDATETIME, CONVERT(NVARCHAR(10),[Date],121) AS [MyDate], DisplayAsID As [ID]
FROM Keyed_Data AS KD LEFT JOIN Stored_Vol_Counts AS SVC ON CONVERT(SMALLDATETIME, CONVERT(NVARCHAR(10),KD.[Date],121)) = CONVERT(SMALLDATETIME, CONVERT(NVARCHAR(10),SVC.[Date],121))

DECLARE @dFrom SMALLDATETIME
DECLARE @dTo SMALLDATETIME

SET @dFrom = '09/09/12'
SET @dTo = '09/15/12'

--first view data
SELECT *
FROM #tt
WHERE (([MyDate] BETWEEN @dFrom AND @dTo) AND (ID = 35);

--get sum
SELECT SUM([TotalVol]) AS TotalVolInRange
FROM #tt
WHERE (([MyDate] BETWEEN @dFrom AND @dTo) AND (ID = 35);

DROP TABLE #tt

Posted 2-Oct-12 9:36am

Maciej Los

Comments

BrianS1 8-Oct-12 15:44pm

I have just tried the following query also just to see if the CONVERT function would work the way you have used it here and I get the same problem with it not wantig to add but five of the seven days and it is excluding the first day of 9-9 and the last day of 9-15. I have hand checked this on a calculator to make sure. The new query is as follows:

SELECT (SUM(isNULL(A.CMTotal,0) + isNULL(A.GMTotal,0) + isNULL(A.AccutermTotal,0)+ isNULL(A.AccutermRelease,0) + isNULL(A.AlamoTotal,0) + isNULL(A.AllianceTotal,0) + isNULL(A.ThetaTotal,0) + isNULL(A.BOSSTotal,0) + isNULL(B.GBSCM,0) +
isNULL(B.GMTradepoint,0) + isNULL(B.Alamo,0) + isNULL(B.Theta,0) + isNULL(B.Alliance,0) + isNULL(B.Accuterm,0) + isNULL(B.BOSS,0)+ isNULL(B.OtherSystem,0))) AS TotalVolInRange
FROM OMR_Stored_FileCounts AS A FULL OUTER JOIN OMR_Keyed_Data AS B
ON A.Date = B.Date
WHERE A.Date between CONVERT(NVARCHAR(10),'09/09/2012',121) AND CONVERT(NVARCHAR(10),'09/15/2012',121) AND B.Date between CONVERT(NVARCHAR(10),'09/09/2012',121) AND CONVERT(NVARCHAR(10),'09/15/2012',121)
AND A.DisplayAsID = 35 AND B.DisplayAsID = 35
AND A.ProductID = 2 AND B.ProductID = 2;

BrianS1 9-Oct-12 11:28am

I am still working on a solution for this and I am now very close. The problem I am having now is that the query will only add the dates of 9-10 thru 9-14. For some reason the query will not count the first and last days selected? I even changed the dates to 9-8 thru 9-16 to see of I could get the correct total at all. I am putting a current example of the query I have working right now that does work but excludes the firat and last day in the total. The table names and fields have changed just a little also as our development database is a little different than production. the query is as follows:

SELECT (SUM(isNULL(A.CMTotal,0) + isNULL(A.GMTotal,0) + isNULL(A.AccutermTotal,0)+ isNULL(A.AccutermRelease,0) + isNULL(A.AlamoTotal,0) + isNULL(A.AllianceTotal,0) + isNULL(A.ThetaTotal,0) + isNULL(A.BOSSTotal,0) + isNULL(B.GBSCM,0) +

isNULL(B.GMTradepoint,0) + isNULL(B.Alamo,0) + isNULL(B.Theta,0) + isNULL(B.Alliance,0) + isNULL(B.Accuterm,0) + isNULL(B.BOSS,0)+ isNULL(B.OtherSystem,0))) AS TotalVolInRange

FROM OMR_Stored_FileCounts AS A FULL OUTER JOIN OMR_Keyed_Data AS B

ON A.Date = B.Date

WHERE A.Date between ''+ @StartDate +'' AND '' + @EndDate +'' AND B.Date between ''+ @StartDate +'' AND ''+ @EndDate +''

AND A.DisplayAsID = 35 AND B.DisplayAsID = 35

AND A.ProductID = 2 AND B.ProductID = 2;

The dates are now variables of a stored procedure. I have also tried CONVERT and DATEPART and a few other things to try to break the date down to make a better match in the tables but the problem now is jsut the two days that are not adding up in the range selected. I treid the basic operators greater than and less than and that did not work either. I cannot give examples of the tables or data due to comapny confidentiality but suffice to say that both tables are identical except one is populated with data manually by hand keyers and the other is populated from many other systems through a application we have built to pull the info from a mainframe. Still working on this so any other advice would be great, There are a few things above I am going to try to implement and will post back in the am. I have been off for a few days which is why it has taken me so long to get back. Thanks in advance everyone for helping.

Brian S.

BrianS1 · Answer 2 · 2012-10-25T11:16:00

You guys have came up with some good alternatives and ideas for me to try out and I really appreciate the time and effort that is put into answering these questions. Thanks everyone for their time and contributions. However, after much trial and error I have finished the query with one simple change that made it work perfect and that was by adding the following code to the JOIN ON part of the query to make the query sum all rows in the date range selected.

SQL

SELECT Sum(BOSS) AS TotalVolInRange
FROM Keyed_Data AS A FULL OUTER JOIN Stored_Vol_Counts AS B
ON A.Date = B.Date AND B.Date between ''+ @StartDate +'' AND ''+ @EndDate +'' AND B.DisplayAsID = @DisplayAsID AND B.ProductID = @Product
WHERE A.Date between ''+ @StartDate +'' AND '' + @EndDate +'' 
AND A.DisplayAsID = @DisplayAsID 
AND A.ProductID = @Product ;

This works perfect and was less code to write however the other solutions are definately good valid soulutions. Our SQL guru helped me out with this one so it was not all me:) Thank you again for the help and ideas.

BrianS.

Using Date Ranges in SQL Server 2005

2 solutions

Solution 1

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us