Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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)
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:
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:
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 Smile | :)
 
Thanks in advance,
BrianS.
Posted 1-Oct-12 10:11am
BrianS1389
Comments
skydger at 1-Oct-12 16:30pm
   
Why don't you use join of A.DisplayAsID = B.DisplayAsID? It might help
BrianS1 at 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 at 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 at 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.
Rate this: bad
good
Please Sign up or sign in to vote.

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.
 
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.
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.
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...
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
  Permalink  
Comments
BrianS1 at 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 at 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.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

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.
 
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 meSmile | :) Thank you again for the help and ideas.
 
BrianS.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 Shai Vashdi 1,485
1 OriginalGriff 463
2 Manas Bhardwaj 383
3 Damith Weerasinghe 340
4 Tadit Dash 330
0 Sergey Alexandrovich Kryukov 9,575
1 OriginalGriff 6,006
2 Peter Leow 4,500
3 Maciej Los 3,540
4 Abhinav S 3,513


Advertise | Privacy | Mobile
Web02 | 2.8.140415.2 | Last Updated 25 Oct 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Use
Layout: fixed | fluid