| Author |
Message |
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
   Posted: 22 Aug 2012 at 12:09pm |
so your RJDateLookup table is just a "full calendar table" (one row per day) that has a lot of meta data for each day. You actual data is in the Quote table but you need to limit that by only items in the next 6 months (including missing days/months) and only where the Type='C'...correct?
try this for the join and where clause
FROM dbo.RJDateLookup LEFT JOIN dbo.CST_MS_Quote
ON dbo.RJDateLookup.DateFull = dbo.CST_MS_Quote.QuoteDate and (dbo.CST_MS_Quote.QuoteType = 'C')
WHERE (dbo.RJDateLookup.DateFull BETWEEN CONVERT(DATETIME, '2012-07-01 00:00:00', 102) AND CONVERT(DATETIME, '2013-01-01 00:00:00', 102))
|
 IP Logged |
|
Craigbob
Newbie
Joined: 21 Aug 2012
Location: United States
Online Status: Offline
Posts: 27
|
Posted: 22 Aug 2012 at 12:25pm |
|
You have the concept correct. The code you gave only brought back data for July and August. nothing past 8/31/2012. I think the problem lies in the join being DateFull = QuoteDate.
If I'm understanding it correct it will only pull data for the CST_MS_Quote table for those days where the two tables each have a date in common. But it should return all the dates in the RJdatelookup table.
Since I want to group this by month/yr would it work if we changed the join to that?
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 22 Aug 2012 at 2:26pm |
|
The grouping can be handled in your crystal design.
If you have one day for every day in the RJlookup table then you should have gotten all of the records from Quotes table with a type of c between the listed dates plus all of the blank dates in that range as well (from the RJlookup table).
If your lookup table is missing any days then it they will be missing those days from the quote table also.
Does that help?
|
|
IP Logged |
|
Craigbob
Newbie
Joined: 21 Aug 2012
Location: United States
Online Status: Offline
Posts: 27
|
Posted: 22 Aug 2012 at 2:37pm |
Originally posted by DBlank
The grouping can be handled in your crystal design.
If you have one day for every day in the RJlookup table then you should have gotten all of the records from Quotes table with a type of c between the listed dates plus all of the blank dates in that range as well (from the RJlookup table).
If your lookup table is missing any days then it they will be missing those days from the quote table also.
Does that help?
The RJDatelookup table has every day from 1/1/1900 thru 12/31/2099 so they are all there.
I did get the QuoteDates, but not any date after 8/31/12.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 22 Aug 2012 at 2:49pm |
|
Run a query on the quote table to verify that you have items past that date that also have the c type.
Basically just use the same query, just remove the date tableland make sure to read the c type to the where clause becuase it will disappear when you get rid of the join.
Do you get all of the dates you were expecting?
|
|
IP Logged |
|
Craigbob
Newbie
Joined: 21 Aug 2012
Location: United States
Online Status: Offline
Posts: 27
|
Posted: 22 Aug 2012 at 3:01pm |
Originally posted by DBlank
Run a query on the quote table to verify that you have items past that date that also have the c type.
Basically just use the same query, just remove the date tableland make sure to read the c type to the where clause becuase it will disappear when you get rid of the join.
Do you get all of the dates you were expecting?
Ahah! I see the miscommunication, let me clarify. At this point, there are no records that have a ReqDate past 8/31 in the Quote Table. Hence why I created the date table to fill in the rest of the dates. At this point the table is in a test/transition phase and while we have live data its not being used in full production.
That being said how can I get the data I need?
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 22 Aug 2012 at 3:10pm |
|
Oops.
Try and change it to a right join
|
|
IP Logged |
|
Craigbob
Newbie
Joined: 21 Aug 2012
Location: United States
Online Status: Offline
Posts: 27
|
Posted: 22 Aug 2012 at 3:14pm |
Originally posted by DBlank
Oops.
Try and change it to a right join
Nope, No change from the left Join.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 22 Aug 2012 at 3:23pm |
|
Also this will not fill in the quote date field. That will be null in the data set for items past 8/31.
Instead use the date.fulldate field as it matches the records plus fills in the 'blanks' that way.
Does this help?
Edited by DBlank - 22 Aug 2012 at 3:24pm
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 22 Aug 2012 at 3:28pm |
|
Also is y our WHERE clause using the rjdatelookup table only, not the quote table, correct?
Edited by DBlank - 22 Aug 2012 at 3:29pm
|
|
IP Logged |
|
|
|
