| Author |
Message |
Craigbob
Newbie
Joined: 21 Aug 2012
Location: United States
Online Status: Offline
Posts: 27
|
   Posted: 22 Aug 2012 at 3:55pm |
Originally posted by DBlank
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?
I'm not looking to fill in the quotedate field. That will be pulled from the record.
I want to fill in the missing ReqDates (a child field from a sub table) with the Datefull Date from the Datelookup table.
|
 IP Logged |
|
Craigbob
Newbie
Joined: 21 Aug 2012
Location: United States
Online Status: Offline
Posts: 27
|
Posted: 22 Aug 2012 at 3:57pm |
Originally posted by DBlank
Also is y our WHERE clause using the rjdatelookup table only, not the quote table, correct?
Correct. Though I will be replacing the 1st date with the quotedate and the last with a date of the last day of the month 6 months in the future (from the Quotedate).
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
|
Posted: 23 Aug 2012 at 3:28am |
Try this for your query: SELECT
dbo.CST_MS_Quote.MSCo,
dbo.CST_MS_Quote.JCCo,
dbo.CST_MS_Quote.Quote,
dbo.CST_MS_Quote.MSHQStatus,
dbo.CST_MS_Quote.QuoteType,
dbo.CST_MS_Quote.Job,
dbo.CST_MS_Quote.Status,
dbo.CST_MS_Quote.Description,
dbo.CST_MS_Quote.QuotedBy,
dbo.RJDateLookup.MonthNumber,
dbo.RJDateLookup.MonthName,
dbo.RJDateLookup.DateFull,
dbo.CST_MS_Quote.QuoteDate,
dbo.CST_MS_Quote.ReqDate,
dbo.CST_MS_Quote.ExpDate,
dbo.CST_MS_Quote.FromLoc,
dbo.CST_MS_Quote.Location2 AS Location,
dbo.CST_MS_Quote.MaterialCode,
dbo.CST_MS_Quote.MaterialDescription,
dbo.CST_MS_Quote.UM,
dbo.CST_MS_Quote.QuoteUnits
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'
and dbo.RJDateLookup.MonthNumber <= month(QuoteDate) + 6
WHERE dbo.RJDateLookup.DateFull >= '07/01/2012'
This way you will be filtering the dates based on the date table, not on the quote table. -Dell
|
|
|
|
IP Logged |
|
Craigbob
Newbie
Joined: 21 Aug 2012
Location: United States
Online Status: Offline
Posts: 27
|
Posted: 23 Aug 2012 at 7:28am |
|
Thank You. That is better. But it is still returning dates past 6 months in the future, so it is not stopping when expected.
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
|
Posted: 23 Aug 2012 at 7:51am |
That's because of your filter - you're filtering for end date on 6 months past the Quote date where the quote date can be any date after 7/1/12. So, if the quote date is 8/1/12, you'll get an end month for that data of 2/1/13. If you want just 6 months, take the date part out of the join an add something like the following to your where clause: and dbo.RJDateLookup.DateFull < "2/1/2013" -Dell
|
|
|
|
IP Logged |
|
Craigbob
Newbie
Joined: 21 Aug 2012
Location: United States
Online Status: Offline
Posts: 27
|
Posted: 23 Aug 2012 at 2:25pm |
Originally posted by hilfyThat's because of your filter - you're filtering for end date on 6 months past the Quote date where the quote date can be any date after 7/1/12. So, if the quote date is 8/1/12, you'll get an end month for that data of 2/1/13. If you want just 6 months, take the date part out of the join an add something like the following to your where clause: and dbo.RJDateLookup.DateFull < "2/1/2013" -Dell
I still think I'm muddling what I'm trying for.
The month and year of the QuoteDate will be used a parameter. I don't need the quote date through the 6 months in the future.
The date field I need for 6 months in the future is the ReqDate. This is the field that has missing data that needs to be filled in by the Datelookup table.
so in essence I want the report to look like this: (Showing Fields rather than data)
Location2
QuotedBy
Material ReqDate (Current Month) ReqDate (Next Month).... (6 months out)
Sum(BidAmt) (0 if ReqDate is missing)
Hopefully that makes more sense and clarifies things a bit.
Again thanks for the assistance.
|
|
IP Logged |
|
Craigbob
Newbie
Joined: 21 Aug 2012
Location: United States
Online Status: Offline
Posts: 27
|
Posted: 29 Aug 2012 at 8:53am |
Bump, Is there any hope for me here? 
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
|
Posted: 29 Aug 2012 at 9:22am |
If your report is pulling multiple quotes, each with a different Quote Date, then you're going to see more than 6 months in your cross-tab, because you have more than 6 months worth of dates in your data. -Dell
|
|
|
|
IP Logged |
|
Craigbob
Newbie
Joined: 21 Aug 2012
Location: United States
Online Status: Offline
Posts: 27
|
Posted: 29 Aug 2012 at 9:44am |
Originally posted by hilfy
If your report is pulling multiple quotes, each with a different Quote Date, then you're going to see more than 6 months in your cross-tab, because you have more than 6 months worth of dates in your data. -Dell
Doh'  I realized that after I sent the message.
But I'm still not getting what I expect.
I ran a query for a single quote dated 8/1/2012 and I was hoping to get dates for Aug 2012 thru Feb 2013 and did not. I just got 1 record for that quote. Which had no required dates.
What am I missing and how can I get force/fake the 6 months for the forecast to display?
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
|
Posted: 30 Aug 2012 at 3:19am |
Please post the exact join and where clauses from your query so that we can see what's going on. -Dell
|
|
|
|
IP Logged |
|
|
|
