[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 448
  • Last Modified:

I need to fake months in a query

Hi all,

I trying to write a forecast report, but the problem is in the table the data comes from, not all of the records will have a full six months worth of data for the forecast. So I need to be able to create fake dates/0 amounts for up to six months in the future from the following sql

Declare @QuoteDate datetime
Set @QuoteDate = '09/01/2012'
SELECT distinct
[MSCo]
,[Quote]
,[Location2]
,[QuotedBy]
,[QuoteDate]
,[ReqDate]
,[MaterialCode]
,[MaterialDescription]
,sum([QuoteUnits]) QuoteUnits
,[UM]
FROM [Viewpoint].[dbo].[CST_MS_Quote]

where 
QuoteType = 'C'
and
QuoteDate>= @QuoteDate
group by
[MSCo]
,[Quote]
,[QuoteType]
,[Location2]
,[QuotedBy]
,[QuoteDate]
,[MaterialCode]
,[UM]
,[ReqDate]
,[MaterialDescription]

Open in new window


When the query runs, it  may not have 6 months worth of reqdate/Quoteunits for each material.

I need to be able to return a month/year for the reqdate and 0 for the Quoteunits for six months in the future.

I've created a dates table with all the dates from 1/1/1900  to 12/31/2099 so i can pull the dates from that to create the fake dates. but i'm not sure how best to do that. i tried I cross join to not avail.

what do i need to do for this?
0
Craigbob
Asked:
Craigbob
  • 24
  • 18
  • 4
  • +2
3 Solutions
 
Dale BurrellCommented:
Build your months of interest query - similar to below maybe, then join on your current data. This is rough, but hopefully gives you an idea - just ask more if you don't quite follow.

select *
from
(
  select date
  from DatesTable
  where date >= @QuoteDate and date <= dateadd(month, 6, @QuoteDate)
) Months
left join
(
  YourCurrentQuery
) Data on Data.date = Months.date
0
 
NikolasGCommented:
From what it seems from your query you have different [materialcode] and [description] at least in your results so you need to union your results with a join of the initial distinct result with the date table that you created.
Something similar to the following query..
Declare @QuoteDate datetime
Set @QuoteDate = '09/01/2012'
SELECT distinct
[MSCo]
,[Quote]
,[Location2]
,[QuotedBy]
,[QuoteDate]
,[ReqDate]
,[MaterialCode]
,[MaterialDescription]
,sum([QuoteUnits]) QuoteUnits
,[UM]
FROM [Viewpoint].[dbo].[CST_MS_Quote]

where 
QuoteType = 'C'
and
QuoteDate>= @QuoteDate
group by
[MSCo]
,[Quote]
,[QuoteType]
,[Location2]
,[QuotedBy]
,[QuoteDate]
,[MaterialCode]
,[UM]
,[ReqDate]
,[MaterialDescription]

union 
select [MSCo]
,[Quote]
,[Location2]
,[QuotedBy]
,[QuoteDate]
,[dates_table].[Dates] as [ReqDate]
,[MaterialCode]
,[MaterialDescription]
,0 QuoteUnits
,[UM]
from [Viewpoint].[dbo].[CST_MS_Quote] join [dates_table] where [dates_table].[Dates]>='01/01/2012'
 and [dates_table].[Dates]>='31/12/2012' -- the yeart that you want to complete.

where 
QuoteType = 'C'
and
QuoteDate>= @QuoteDate
group by
[MSCo]
,[Quote]
,[QuoteType]
,[Location2]
,[QuotedBy]
,[QuoteDate]
,[MaterialCode]
,[UM]
,[ReqDate]
,[MaterialDescription]

Open in new window

0
 
Scott PletcherSenior DBACommented:
SELECT
    cmq.[MSCo]
    ,cmq.Quote
    ,cmq.[Location2]
    ,cmq.QuotedBy
    ,dt.date AS QuoteDate
    ,cmq.[ReqDate]
    ,cmq.[MaterialCode]
    ,cmq.[MaterialDescription]
    ,ISNULL(sum([cmq.QuoteUnits]), 0) AS QuoteUnits
    ,cmq.[UM]
FROM [Viewpoint].[dbo].[dates_table] dt
LEFT OUTER JOIN [Viewpoint].[dbo].[CST_MS_Quote] cmq ON
    cmq.QuoteDate = dt.date
WHERE
    dt.date >= @QuoteDate AND
    dt.date < DATEADD(MONTH, 7, @QuoteDate) AND
    cmq.QuoteType = 'C' AND
    cmq.QuoteDate >= @QuoteDate
GROUP BY
    cmq.[MSCo]
    ,cmq.Quote
    ,cmq.[Location2]
    ,cmq.QuotedBy
    ,dt.date
    ,cmq.[MaterialCode]
    ,cmq.[UM]
    ,cmq.[ReqDate]
    ,cmq.[MaterialDescription]
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
mlmccCommented:
If you are trying to do this Crystal, as soon as you filter on the joined table, Crystal will treat it as an INNER JOIN since NULL cannot equal anything

LEFT OUTER JOIN [Viewpoint].[dbo].[CST_MS_Quote] cmq ON
    cmq.QuoteDate = dt.date AND (cmq.QuoteType Is Null or cmq.QuoteType = 'C' )
WHERE
    dt.date >= @QuoteDate AND
    dt.date < DATEADD(MONTH, 7, @QuoteDate) 

Open in new window


mlmcc
0
 
CraigbobAuthor Commented:
Thanks for the suggestions, i was out on Fri. so i did not get to try them until this morning.
@NikolasG and @ ScottPletcher, your solutions are close, but neither gives the extra fake req dates that I'm looking for.

I'm currently getting :
the Current State based on the queries in earlier answers
I need something along this line:
what i need (6 fake req dates/quote0
Notice that the quote has 6 fake dates in the reqdate field.

The quoteunits will be 0 if there is not already a reqdate with a quoteunit.
The quote date and reqdates will be grouped by month so having multiple reqdates is not an issue.
0
 
NikolasGCommented:
You can try my query with Union all instead of Union so it will give you all the dates and materials.
please feedback
Nikolas G
0
 
CraigbobAuthor Commented:
@NickolasG, the union all had no affect.
0
 
NikolasGCommented:
can you post the results that it gives you?
0
 
Dale BurrellCommented:
What happened when you tried my idea? The important thing when you need a complete range is to start your query with the range, then left join to that as I demonstrated.
0
 
CraigbobAuthor Commented:
@Dale,

I've actually tried a variation of yours a while  ago, and it was close, but I could not get the QuoteUnits to Sum up correctly. Here is that Code:

Declare @QuoteDate Datetime
Set @QuoteDate = '08/01/2012';

Select 
MSCo, 
Location, 
QuotedBy, 
--MonthName,
--MonthNumber,
--FullYear,
QuoteDate, 
--ISNULL(ReqDate, MonthNumber + '/1/' + FullYear) ReqDate,
convert(datetime,MonthNumber + '/1/' + FullYear)  as ReqDatenew, 
MaterialCode, 
MaterialDescription, 
QuoteUnits, 
UM
from 
(
Select row_number() over (partition by QuotedBy order by MonthNumber) row,*
from 
(
SELECT distinct
dbo.CST_MS_Quote.MSCo, 
dbo.CST_MS_Quote.QuotedBy, 
dbo.RJDateLookup.MonthName,
convert(varchar, dbo.RJDateLookup.MonthNumber,2) MonthNumber,
convert(varchar, dbo.RJDateLookup.FullYear, 4) FullYear,
dbo.CST_MS_Quote.QuoteDate, 
--Dateadd(m,6,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,QuoteDate))-1),DATEADD(mm,1,QuoteDate)),101)) as SixMonthFuture,
month(ISNULL(ReqDate, convert(varchar, dbo.RJDateLookup.MonthNumber,2) + '/1/' + convert(varchar, dbo.RJDateLookup.FullYear, 4))) ReqDate,
--dbo.CST_MS_Quote.ReqDate,
--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.QuoteUnits, 
dbo.CST_MS_Quote.UM 

FROM dbo.RJDateLookup 
cross JOIN dbo.CST_MS_Quote 
--ON dbo.RJDateLookup.DateFull = dbo.CST_MS_Quote.QuoteDate 
--and dbo.CST_MS_Quote.QuoteType = 'C' 
--and dbo.RJDateLookup.DateFull >=  @QuoteDate and dbo.RJDateLookup.DateFull <  Dateadd(m,7,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@QuoteDate))-1),DATEADD(mm,1,@QuoteDate)),101))

where 
dbo.RJDateLookup.DateFull >= @QuoteDate and dbo.RJDateLookup.DateFull <  Dateadd(m,7,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@QuoteDate))-1),DATEADD(mm,1,@QuoteDate)),101))
and 
dbo.CST_MS_Quote.QuoteDate >= @QuoteDate
and 
ReqDate < Dateadd(m,7,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@QuoteDate))-1),DATEADD(mm,1,@QuoteDate)),101))
) a 
) b

order by Location,QuotedBy,MaterialCode, ReqDatenew

Open in new window

 

What this is giving me is QuoteUnits  for the fake months where there should be none and they should = 0.
0
 
Dale BurrellCommented:
You can't use a cross join for this - as I specified you need a left join so that you include all the months, but only join on data records for the months where there is data. You can't do that using a cross join, you're ending up with records being joined on in months where there are none.
0
 
CraigbobAuthor Commented:
@Dale, That is exactly what I want. I want to create fake data records that will be used as the column ReqDate. Using the left join does not give me the fake ReqDates. The Cross Join does.

What I can't figure out is how to get the QuoteUnits to Display 0 for the fake reqdate columns.
The Quote Unit is repeating from the one record in there to all of the fake ones.
0
 
Dale BurrellCommented:
OK but think about it logically, using cross join matches all record on the left with all records on the right, so of course your quantity is going to be incorrect. You have to improve the left join query such that it works not change the join type.

The logic behind my solution is you create all the *required* information in the fake dates query, then only where actual data exists you join it on and add it up.

I would start by using the fake dates query to generate the rows you require, check that its OK, then left join on your data and check that the sums are OK.
0
 
CraigbobAuthor Commented:
@Dale,  I understand what you're saying and that did work a bit, but it did not give me the ReqDate past the current date. It did however give me the correct amounts for QuoteUnits.

My logic behind the cross join is that I can create the exact dates I need in the dates query, and then Join them to the exact data I need for the rest of the query.

 So it's like each gives me a bit of what I need. Now I just need to figure out how to combine them. :)
0
 
Dale BurrellCommented:
OK, well I won't bother you any more then, you asked for expert help I gave it, I've solved this problem many times in the past. You cannot use a cross join, you must use a left join. If you can't understand why that is I suggest you do some reading on join types. Good luck.
0
 
CraigbobAuthor Commented:
Dale, All I'm saying is that the left Join did not give me what I need. The Cross Join doe not give me all that I need either.

So the solution must be somewhere between the two.
0
 
Dale BurrellCommented:
Look I can't help you further when you don't believe me.
0
 
CraigbobAuthor Commented:
I didn't say I don't believe you, I just said the left join did not return what I needed. So here is the code, please explain why I am not getting the 6 months following the quote date as the require date.

Declare @QuoteDate Datetime
Set @QuoteDate = '08/01/2012';

select 
DateFull,
MonthNumber,
MonthName,
FullYear,
MSCo, 
QuotedBy, 
QuoteDate, 
isnull(ReqDate,DateFull) as ReqDate,
Location, 
MaterialCode, 
MaterialDescription, 
QuoteUnits, 
UM 
from
(
  select *
  from RJDateLookup
  where DateFull >= @QuoteDate and DateFull <  Dateadd(m,7,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@QuoteDate))-1),DATEADD(mm,1,@QuoteDate)),101))
) Months

Left Join
(SELECT 
dbo.CST_MS_Quote.MSCo, 
dbo.CST_MS_Quote.QuotedBy, 
dbo.CST_MS_Quote.QuoteDate, 
dbo.CST_MS_Quote.ReqDate,
dbo.CST_MS_Quote.Location2 AS Location, 
dbo.CST_MS_Quote.MaterialCode, 
dbo.CST_MS_Quote.MaterialDescription, 
dbo.CST_MS_Quote.QuoteUnits, 
dbo.CST_MS_Quote.UM 

FROM dbo.CST_MS_Quote
where 
dbo.CST_MS_Quote.QuoteDate >= @QuoteDate
and 
ReqDate between @QuoteDate and  Dateadd(m,7,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@QuoteDate))-1),DATEADD(mm,1,@QuoteDate)),101))
and 
dbo.CST_MS_Quote.QuoteType = 'C')Data On Data.QuoteDate = Months.DateFull

Open in new window

0
 
Dale BurrellCommented:
OK, well if you read back over what I said, I suggested you build the query from the date lookup table such that you get the desired rows and dates and initially ignore the quote data.

The whole purpose of the data lookup table is to allow you to ensure you get the exact rows and dates you require, you don't need the quote data to get that part of the query right. So get that working as a stand alone query before attempting any joins.

I can't easily debug your query for you as I don't have the database.
0
 
CraigbobAuthor Commented:
The date part gives me the date range and fields I expect. from the @QuoteDate forward to last day 6 months from the QuoteDate.

I really need it grouped at the Month/Year level but have the Full date so I can join the two tables.

Declare @QuoteDate Datetime
Set @QuoteDate = '08/01/2012';

  select 
  DateFull,
  MonthNumber,
  MonthName,
  FullYear
  from RJDateLookup
  where DateFull >= @QuoteDate and DateFull <  Dateadd(m,7,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@QuoteDate))-1),DATEADD(mm,1,@QuoteDate)),101))
 ) Months

Open in new window


The Quote part alone give me the correct data.

So I guess the question is, how do I convert the date data from a row to a column in the Quote section of the query?
0
 
Dale BurrellCommented:
OK, now you said that the "ReqDate" column was incorrect, yet I believe it should be the same as FullDate?

Don't try grouping or anything just yet, at this point you should be able to LEFT join your data table onto your dates table by day.

This should give you at least 1 row per date, some of which will have a lot of nulls because there is no quote for that day.

Finally you can group by year, month and sum any quantities to allow the grouping.

HTH
0
 
CraigbobAuthor Commented:
Ok, the ReqDate (from the quotes side is the column I'm looking for. All the Quote records may not have a ReqDate or may just have one. for the same month as the QuoteDate.

What I'm getting after the left join and without grouping is the quote records and a lot of records with just the date data. The screen shot below will help explain better I hope.

 (This are after putting the query into Crystal reports to create the cross tab).

The Bottom table should look like the top

As you can see the Top table (with the null QuotedBy records) has all the reqdates months in the column. (Since the query returns the dates with no other data as it should).

The Bottom Table only has the months that have a required date allready filled in. I need to get the missing reqdates months in as  the columns.

Obviously the order of the months is not correct in these examples since I'm just using Month(reqdate) without the year component.

I hope this helps clarify what I'm trying to accomplish.
0
 
Dale BurrellCommented:
OK, I think I sort of understand. Are you able to run the query in query analyser of sql server so you can see the raw data? Its harder to see what is happening with Crystal involved.

I think I might understand why you were thinking of a cross join now, do you want to add each product to each months even if the amount is zero?

If so you may need to do something like this:

select *
from RJDateLookup-- To ensure each date has an entry
cross join ProductTable -- To ensure each product has an entry
left join CST_MS_Quote-- Join on real entries

Now if  you can run this with all active products then easy. If however you just want the products that appear in this 6 month period then you can use your quote data to build this table e.g.

select *
from RJDateLookup -- To ensure each date has an entry
cross join
(
  select Product
  from CST_MS_Quote
  where {Add date cause here identical to main where date clause}
  group by Product
) ProductTable -- To ensure each product has an entry
left join CST_MS_Quote-- Join on real entries

Does that make sense? Does that help?
0
 
CraigbobAuthor Commented:
Yes, I've been running in Management Studio/analyzer all along. I just put into Crystal to make it easier to explain.

You are correct that I want to add each quote to each month even if the material/unit quote is missing.

However I don't have a products table, just the date lookup table  and the quote table.

The last query may be what I'm looking for. Let me give it a try and I'll let you know.
0
 
NikolasGCommented:
Hello again Graigbob
can you please run the following query and tell me if it gives you all the dates that you need with 0 quantity?
select [MSCo]
,[Quote]
,[Location2]
,[QuotedBy]
,[QuoteDate]
,[dates_table].[Dates] as [ReqDate]
,[MaterialCode]
,[MaterialDescription]
,0 QuoteUnits
,[UM]
from [Viewpoint].[dbo].[CST_MS_Quote] join [dates_table] where [dates_table].[Dates]>='01-Jan-2012'
 and [dates_table].[Dates]>='31-Dec-2012' -- the yeart that you want to complete.

where 
QuoteType = 'C'
and
QuoteDate>= @QuoteDate
group by
[MSCo]
,[Quote]
,[QuoteType]
,[Location2]
,[QuotedBy]
,[QuoteDate]
,[MaterialCode]
,[UM]
,[ReqDate]
,[MaterialDescription]

Open in new window


You ll need to make some changes to the code for the correct table and array name along with the date that you want cover, (if it works we ll make it with parameters)

if that works then try running the following
Declare @QuoteDate datetime
Set @QuoteDate = '09/01/2012'
select  
r.[MSCo]
,r.[Quote]
,r.[Location2]
,r.[QuotedBy]
,r.[QuoteDate]
,r.[ReqDate]
,r.[MaterialCode]
,r.[MaterialDescription]
,sum(r.[QuoteUnits]) QuoteUnits
,r.[UM]

from (
SELECT distinct
[MSCo]
,[Quote]
,[Location2]
,[QuotedBy]
,[QuoteDate]
,[ReqDate]
,[MaterialCode]
,[MaterialDescription]
,sum([QuoteUnits]) QuoteUnits
,[UM]
FROM [Viewpoint].[dbo].[CST_MS_Quote]

where 
QuoteType = 'C'
and
QuoteDate>= @QuoteDate
group by
[MSCo]
,[Quote]
,[QuoteType]
,[Location2]
,[QuotedBy]
,[QuoteDate]
,[MaterialCode]
,[UM]
,[ReqDate]
,[MaterialDescription]

union 
select [MSCo]
,[Quote]
,[Location2]
,[QuotedBy]
,[QuoteDate]
,[dates_table].[Dates] as [ReqDate]
,[MaterialCode]
,[MaterialDescription]
,0 QuoteUnits
,[UM]
from [Viewpoint].[dbo].[CST_MS_Quote] join [dates_table] where [dates_table].[Dates]>='01/01/2012'
 and [dates_table].[Dates]>='31/12/2012' -- the yeart that you want to complete.

where 
QuoteType = 'C'
and
QuoteDate>= @QuoteDate
group by
[MSCo]
,[Quote]
,[QuoteType]
,[Location2]
,[QuotedBy]
,[QuoteDate]
,[MaterialCode]
,[UM]
,[ReqDate]
,[MaterialDescription]
) r                                            
group by 
r.[MSCo]
,r.[Quote]
,r.[QuoteType]
,r.[Location2]
,r.[QuotedBy]
,r.[QuoteDate]
,r.[MaterialCode]
,r.[UM]
,r.[ReqDate]
,r.[MaterialDescription]

Open in new window


with the changes that you made in the first script..
0
 
CraigbobAuthor Commented:
Nickolas, The 1st query just returned 1 row. I'm trying the 2nd query and I'm getting the error message
"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

I need to trace down what is causing this.

@Dale, I'm trying the 2nd query you send last night but need to make a couple of changes to it to get it to work.
0
 
CraigbobAuthor Commented:
@ Nickolas, the 2nd query gave me some null records, and 0 in the Quote units,  but the null records had a Quote Unit. Where as those that had a req date had some Quote unts and some some as 0 quote units.

Also the ReqDate never went past the end of this month.
0
 
CraigbobAuthor Commented:
@Dale, I finally got the 2nd query to work, but I'm still getting the same results as easrlier where I get the missing dates, but everything else except for DateFull, MaterialCode and MaterialDescrip are null.

Here is the code for that.
Declare @QuoteDate Datetime
Set @QuoteDate = '08/01/2012';

select distinct
[MSCo]
--,[Quote]
--,[QuoteType]
,[Location2]
,[QuotedBy]
,[QuoteDate]
,[DateFull]
,[ReqDate]
,b.[MaterialCode]
,b.[MaterialDescription]
,QuoteUnits
,[UM]


from
( 
select  DateFull
  from RJDateLookup
  where 
       DateFull between @QuoteDate and  Dateadd(m,7,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@QuoteDate))-1),DATEADD(mm,1,@QuoteDate)),101))
)a
cross join 
(
  select 
    MaterialCode, 
    MaterialDescription 

  from dbo.CST_MS_Quote
  where 
        QuoteDate >= @QuoteDate
		and 
		ReqDate between @QuoteDate and  Dateadd(m,7,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@QuoteDate))-1),DATEADD(mm,1,@QuoteDate)),101))
		and 
		QuoteType = 'C'
  group by 
	MaterialCode, 
    MaterialDescription
) b -- To ensure each product has an entry
left join CST_MS_Quote c on c.QuoteDate = a.DateFull  -- Join on real entries

--Group by
--[MSCo]
----,[Quote]
----,[QuoteType]
--,[Location2]
--,[QuotedBy]
--,[QuoteDate]
--,[DateFull]
--,[ReqDate]
--,b.[MaterialCode]
--,b.[MaterialDescription]
--,[UM]

order by DateFull

Open in new window

0
 
Dale BurrellCommented:
Yeah, thats as I would have expected - how had you hoped to get data for the dates where there is no data?

You can isnull(field,0) the numerical columns to give 0 instead of null, but other than that you don't have the data - so where did you intend to pull it from?
0
 
CraigbobAuthor Commented:
@Dale, as you know, the Cross Join is what I was hoping to use to create the fake columns. I can get that part to work, but then I don't know how to get the correct values in the Quote Units fields.

The Cross join puts the QuoteUnits in the record. I wonder if it would work if I don't add the QuoteUnits to the main query but select them separately (based on Quote and QuoteDate) and then union them in to the main query (the one with the fake date coulmns).
0
 
Dale BurrellCommented:
CraigBob - just a little advice, you first need to clearly formulate the problem before you go rushing off joining stuff... you need some way of know if you've actually answered the correct question.

Now for the million dollar question, which I'm sure we are all waiting with baited breath for, is, wait for it...

What IS the correct value of Quote Units for a date when no quote exists?


This is why no one has been able to help you so far, none of us know how to create data when none exists. I've only been helping you ensure that a row exists for each date, but I have no idea what value of quote unit you want when there is none?

If you can define that then the solution will be trivial.

PS - looking back at what you've written, you've said that Quote Units can be 0 on more than one occassion?
0
 
CraigbobAuthor Commented:
I appreciate the help. and as you noticed I've mentioned that Quote Units should be 0 where the reqdate does not exist in the data and had to be created from the dates table.

So if a quote record has a reqdate it will have a quoteunits value. If the quote does not have a reqdate and we had to use the dates table to create it the quoteunit for that date should be 0.
0
 
Dale BurrellCommented:
So the answer as I mentioned above is to change the quote units column to

isnull(QuoteUnits,0) QuoteUnits

But is that really all you were looking for?
0
 
CraigbobAuthor Commented:
The isnull(QuoteUnits,0) QuoteUnits will work, if I can figure out where to put it so the rows with fake ReqDates include it.

As it stands now,

I have two parts that I don't know how to merge.
0
 
Dale BurrellCommented:
OK, in that case I'm totally lost, we've created a query that produces 1 row per date, we've left joined on your quote data, mean that sometimes it produces all null fields because no data exists. Therefore wouldn't you put isnull(QuoteUnits,0) QuoteUnits in your outer query where you current have QuoteUnites? Because it will only be null where the row is missing? What am I missing? Maybe you need to post some real data again, and as well post how you're expecting it to look?
0
 
CraigbobAuthor Commented:
You are correct in your assessment. But what is missing is joining the fake reqdates to the quote data so there is a fake reqdate for each for each quote up to 6 months in the future.

Here are the screen shots of the raw data  from earlier:

This 1st one shows the Quotes with the null fake reqdates (column)

 Current Query
This one shows the desired state. Notice how the Quote has 6 rows (1 for each reqdate) The quote Units mare not correct in this. It should be 34 for 10/1/2012 and 0 for the other months (which are fake reqdates)

Desired Raw Data
This shows the Crystal reports view. In this case you can see the table with the null QuoteedBy field has all the Reqdates (fake and real) where as the table with a quotedby field filled in has only Aug and Sept. as the Reqdate (column) I want to see the fake reqdates in the table where there is a Quoted By. The value for the QuoteUnits Should be 0 if the reqdate is a fake one.

Crystal reports view
I hope that helps clarify things.
0
 
Dale BurrellCommented:
Its the same question as before, where you are missing a ReqDate how should it be worked out? The data isn't there, you need to specify where it comes from, or how it is made up?

If its like the products and you need to show all existing ReqDates for all FakeDates, then add the ReqDate into the cross join part of the query which will then give you a full combination of all products, req-dates and fake-dates.

If thats not what you're looking for I need to know where the fake-req-date should come from?
0
 
CraigbobAuthor Commented:
The missing reqdate should come from the datelookup table. In the case the reqdate field is spoofed from the datelookup table the QuoteUnits should be 0.

However everytime I try the cross join I never get the 0 in the quoteunits, I always get a value from the quote table.
0
 
Dale BurrellCommented:
I'm still lost mate, if the missing req-date should come from the date lookup table then how would you do this?

isnull(ReqDate, DateFull) ReqDate? isnull(ReqDate, dateadd(month, 6, DateFull))? Some other computation? You need to know where you are expecting the data to come from - I can't read your mind.

Your example data shows multiple req-dates per fake-date? Hence my cross join suggestion, but when I said the cross join, I meant extend the existing cross join e.g.

cross join 
(
  select 
    MaterialCode 
    , MaterialDescription 
    , ReqDate
  from dbo.CST_MS_Quote
  where 
        QuoteDate >= @QuoteDate
		and 
		ReqDate between @QuoteDate and  Dateadd(m,7,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@QuoteDate))-1),DATEADD(mm,1,@QuoteDate)),101))
		and 
		QuoteType = 'C'
  group by MaterialCode, MaterialDescription, ReqDate
) b -- To ensure each product and req-date has an entry

Open in new window

0
 
CraigbobAuthor Commented:
Yes the Isnull(ReqDate, DateFull) is how I was planning on filling in the missing ReqDate columns.

Modifying the SQL you gave earlier to include the one you just gave is a bit closer, but all the reqdates are between 8/1 and 9/26. no future dates.


As an experiment I tried the code below to see what I'd get. All I did was clean it up a bit, put the dates in MM/YYYY format and removed the QuoteUnits field.

This gives me almost everything I need with the exception of the  QuoteUnits Field. Now if I can get the QuoteUnits to work I'd have it.

Declare @QuoteDate Datetime
Set @QuoteDate = '08/01/2012';

Select 
Location2
,QuoteDate
,QuotedBy
,isnull(b.ReqDate,a.ReqDate) Reqdate
,Material
--,isnull(b.QuoteUnits,a.QuoteUnits) QuoteUnits
,UM

From

(select  RIGHT(CONVERT(VARCHAR(10), DateFull, 103), 7) ReqDate, 0 QuoteUnits
  from RJDateLookup
  where 
       DateFull between @QuoteDate and  Dateadd(m,7,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@QuoteDate))-1),DATEADD(mm,1,@QuoteDate)),101))
)a
Cross Join
(select distinct
Location2
,QuotedBy
,RIGHT(CONVERT(VARCHAR(10), QuoteDate, 103), 7) QuoteDate
,RIGHT(CONVERT(VARCHAR(10), ReqDate, 103), 7)ReqDate
,MaterialCode + ' - ' + MaterialDescription Material
,isnull(QuoteUnits,0) QuoteUnits
,UM

from 
CST_MS_Quote
where QuoteDate >= '09/01/2012'
and QuoteType = 'C'
)b

Group By
Location2
,QuoteDate
,QuotedBy
,isnull(b.ReqDate,a.ReqDate)
,Material
,UM

Open in new window

0
 
Dale BurrellCommented:
Look, I don't think I can help you any further, nowhere in your question is it clear what actual data you are looking for. You seem to want data that doesn't exist, without knowing how to get it? Either that or you want something different from what you have explained in the question.

Forecasting in this way is very very simple, build a fake data set that gives you all the EXACT rows you want. All of them, exactly, precisely. Make sure this works correctly first.

Then LEFT Join on the actual data, this will obviously give you null values where the data doesn't exist.

Then if you know how, compute some fake data for the null values. All fake data, obviously, has to come from the fake data set, because thats the only data that definitely exists.

You say that using Isnull(ReqDate, DateFull) isn't giving you any future dates, its going to give you exactly precisely whatever date range you've set for your fake dates, so if you want fake dates in the future set your fake date query range to go into the future???
0
 
CraigbobAuthor Commented:
You do understand. I need data that does not necessarily exist in the Quote table. This would include ReqDates (for six months in the future)  and QuoteUnits ( = 0 where they have to be created.).

The problem with building it as a table variable, CTE etc... is that I will not know until run time what I need.

I won't know who the quote is by, or the material in the quote.  

In the query, I've set the fake dates to be from the quote date to 6 months in the future.

 select  RIGHT(CONVERT(VARCHAR(10), DateFull, 103), 7) ReqDate, 0 QuoteUnits
  from RJDateLookup
  where 
       DateFull between @QuoteDate and  Dateadd(m,6,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@QuoteDate))-1),DATEADD(mm,1,@QuoteDate)),101)) 

Open in new window


This works great and gives the fake dates perfectly for 6 months from the quote date.

The cross Join with
select distinct
Quote
,Location2
,QuotedBy
,RIGHT(CONVERT(VARCHAR(10), QuoteDate, 103), 7) QuoteDate
,RIGHT(CONVERT(VARCHAR(10), ReqDate, 103), 7)ReqDate
,MaterialCode + ' - ' + MaterialDescription Material
--,isnull(QuoteUnits,0) QuoteUnits
,UM
from 
CST_MS_Quote
--where QuoteDate >= '08/01/2012'
Where QuoteType = 'C'

Open in new window


Also works great.

It is only when I try and add Quote units that it fails.
0
 
Dale BurrellCommented:
"It is only when I try and add Quote units that it fails." I don't understand this though.

If you are LEFT joining it CORRECTLY you will add some data to some rows from the base query, and not to others. Then you can fake it for the others if you wish. Simple... it can't fail - unless you are specifying incorrect join criteria.

Or! Unless you need to group your REAL quote data before joining it on? If you don't want one row for each row of quote data you can group that before joining.

Otherwise I'm lost...
0
 
CraigbobAuthor Commented:
Maybe it is my left join that is jacking things up.

Here is the code that you provided:
Declare @QuoteDate Datetime
Set @QuoteDate = '08/01/2012';

select distinct
[MSCo]
--,[Quote]
--,[QuoteType]
,[Location2]
,[QuotedBy]
,RIGHT(CONVERT(VARCHAR(10), QuoteDate, 103), 7) QuoteDate
--,[DateFull]
,isnull(b.ReqDate,DateFull) ReqDate
,b.[MaterialCode] + ' - ' + b.[MaterialDescription] Material
--,isnull(c.QuoteUnits,a.QuoteUnits) QuoteUnits
,Isnull([UM],'TON') UM


from
( 
select  DateFull, 0 QuoteUnits
  from RJDateLookup
  where 
       DateFull between @QuoteDate and  Dateadd(m,7,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@QuoteDate))-1),DATEADD(mm,1,@QuoteDate)),101))
)a
cross join 
(
  select 
    MaterialCode 
    , MaterialDescription 
    , ReqDate
  from dbo.CST_MS_Quote
  where 
        QuoteDate >= @QuoteDate
		and 
		ReqDate between @QuoteDate and  Dateadd(m,7,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@QuoteDate))-1),DATEADD(mm,1,@QuoteDate)),101))
		and 
		QuoteType = 'C'
  group by MaterialCode, MaterialDescription, ReqDate
) b -- To ensure each product and req-date has an entry -- To ensure each product has an entry
left join CST_MS_Quote c on c.QuoteDate = a.DateFull  -- Join on real entries

Open in new window

I've joined the quote data to the datelookup table via the Quotedate and FullDate fields.

If this is wrong, what should I join on?
0
 
Dale BurrellCommented:
I don't know - only you know the correct join condition. What you have done will do the following:-

For each date where there is no quote data, the quota data columns will be null.
For each date where there is quote data you will get 1 row per quote data.

Is that what you want?

I still don't understand what is wrong with this? I thought you said it was correct except missing the ReqDate - in which case the question is, what date do you want to use for the ReqDate when there is no quote data for a given date?
0
 
CraigbobAuthor Commented:
For each date where there is no quote data, the quota data columns will be null.
For each date where there is quote data you will get 1 row per quote data.

I think I know what was happening.

I got hung up on the unnecessary need to have both the original ReqDate in the results as well as the  fake reqdate from the datelookup table. If I just use DateFull from the look up it seems to work. And I get both the 0 and the correct Quote Units.

Now to try it in Crystal and see what I get.
0
 
CraigbobAuthor Commented:
It is solved!!!

Here is the final code I wound up using.

Declare @QuoteDate Datetime
Set @QuoteDate = '08/01/2012';

Select
QuoteDate
,DateFull ReqDate
,Location
,Quote
,QuotedBy
,Material
,QuoteUnits

from 
(
Select distinct
QuoteDate
,DateFull
,Location
,Quote
,QuotedBy
,Material
,0 QuoteUnits 

from CST_RJ_DateForecast 
Cross Join  CST_RJ_QuoteForecast
Where 
QuoteDate >= @QuoteDate
and 
DateFull >=  @QuoteDate and DateFull < Dateadd(m,7,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@QuoteDate))-1),DATEADD(mm,1,@QuoteDate)),101))
) a

Union All
(
Select 
QuoteDate
,ReqDate
,Location
,Quote
,QuotedBy
,Material
,QuoteUnits

From CST_RJ_QuoteForecast
Where 
QuoteDate >= @QuoteDate
and
ReqDate >=  @QuoteDate 
and ReqDate < Dateadd(m,6,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@QuoteDate))-1),DATEADD(mm,1,@QuoteDate)),101))
) 

Order by QuotedBy desc

Open in new window


I figured it out this morning on my drive in to work I needed the Union All clause to get the final results.

@Dale and @ Nickolas Thanks for your help and patience.
0
 
CraigbobAuthor Commented:
I figured out the last piece (Union All) on my own and added it to the final answer.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 24
  • 18
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now