I was treated very rudely on the SQL Programming message board by someone who
doesnot have all of the facts about my background. Hopefully, there will be
someone here who has some decency and kindness. (Otherwise, I'd continue
posting there.)

Below is my SQL view. I need to have the QUANTITY field from the "THEN
MRPW4060.QUANTITY ELSE 0 END AS [QTY ON ORDER]" clause of the CASE statement
below to be placed within just one record of my table (view).

You see, each record in my view represents a month. The SC020230.DATE1
field, for example, could be "2008-07-01 00:00:00.000'. And, the
MRPW4060.DUEDATE field could be "2008-07-20 00:00:00.000". Therefore, I want
the MRPW4060.QUANTITY field to appear just on that record for July (i.e. the
record for the month of July only). Since the quantity is due in the month
of July, I want the quantity to appear just on that record of rows returned
from my view.

How do I tell SQL to place the quantity due in just that one record for that
month that it is due? I imagine I would have to do some sort of "trim"
statement on the DUEDATE or something.....

Thanks! sqlprogrammer@hotmail.com

select ITEMNMBR as [ITEM],
DATE1 As [FORECAST DATE],
QTYTOFORECOST_I As [FORECAST QTY],
SUM([QTY ON ORDER]) As [TOTAL QTY ON ORDER],
DUEDATE As [DUE DATE],
GETDATE() As [CURRENT DATE]
FROM(
select DISTINCT SC020130.ITEMNMBR,
SC020230.SFLINEITEM_I,
SC020230.DATE1,
SC020230.QTYTOFORECOST_I,
MRPW4060.QUANTITY,
MRPW4060.DUEDATE,
GETDATE() As [CURRENT DATE],
CASE WHEN GETDATE()<=MRPW4060.DUEDATE AND GETDATE()>=SC020230.DATE1
THEN MRPW4060.QUANTITY ELSE 0 END AS [QTY ON ORDER]
from SC020230 As SC020230
JOIN SC020130 As SC020130
on SC020230.SFLINEITEM_I = SC020130.SFLINEITEM_I
JOIN MRPW4060 As MRPW4060
on SC020130.ITEMNMBR = MRPW4060.ITEMNMBR
where SC020230.PLANNAME_I = 'MRP FG1' and SC020130.ITEMNMBR = '89417' and
SOPTYPE = '2') As I
GROUP BY ITEMNMBR,
SFLINEITEM_I,
DATE1,
QTYTOFORECOST_I,
DUEDATE,
QUANTITY
ORDER BY ITEMNMBR ASC

SQL Programmer,

Yes, there are some rude folks out there, but don't let them chase you away.
(Kind of like you don't let someone honking from behind to cause you to go
through the STOP sign before the way is clear.) So....

Without going through your whole query, for which I would need the table
definitions and some INSERT statements to put in some sample data, perhaps a
simple discussion will .

The derived table (the nested query) named I does not obviously return 1 row
for month

The [QTY ON ORDER] from the derived table I is summed in the outer query as
[TOTAL QTY ON ORDER].

You are grouping on several columns from the derived table I, namely
ITEMNMBR, SFLINEITEM_I, DATE1, QTYTOFORECOST_I, DUEDATE, QUANTITY. So, you
are getting one summed value of [QTY ON ORDER] for each of these groups. If
I understand correctly, this is too small a grouping, but this should be
grouped in such a way that DATE1 is only (for example) the first day of each
month.

So, perhaps in your derived table your could replace:
SC020230.DATE1,
with:
DATEADD(MONTH,DATEDIFF(MONTH,0,SC020230.DATE1),0) AS DATE1,

This would allow your GROUP BY to work with a month level DATE1 instead of a
daily DATE1.

Hope that s,
RLF

PS - At the risk of getting shot at by someone, I will also answer in
..programming.


"SQL Programmer" <SQLProgrammer@discussions.microsoft.com> wrote in message
news:6C64B47F-200C-4ACE-947E-57C77C0EDC67@microsoft.com...
>I was treated very rudely on the SQL Programming message board by someone
>who
> doesnot have all of the facts about my background. Hopefully, there will
> be
> someone here who has some decency and kindness. (Otherwise, I'd continue
> posting there.)
>
> Below is my SQL view. I need to have the QUANTITY field from the "THEN
> MRPW4060.QUANTITY ELSE 0 END AS [QTY ON ORDER]" clause of the CASE
> statement
> below to be placed within just one record of my table (view).
>
> You see, each record in my view represents a month. The SC020230.DATE1
> field, for example, could be "2008-07-01 00:00:00.000'. And, the
> MRPW4060.DUEDATE field could be "2008-07-20 00:00:00.000". Therefore, I
> want
> the MRPW4060.QUANTITY field to appear just on that record for July (i.e.
> the
> record for the month of July only). Since the quantity is due in the
> month
> of July, I want the quantity to appear just on that record of rows
> returned
> from my view.
>
> How do I tell SQL to place the quantity due in just that one record for
> that
> month that it is due? I imagine I would have to do some sort of "trim"
> statement on the DUEDATE or something.....
>
> Thanks! sqlprogrammer@hotmail.com
>
> select ITEMNMBR as [ITEM],
> DATE1 As [FORECAST DATE],
> QTYTOFORECOST_I As [FORECAST QTY],
> SUM([QTY ON ORDER]) As [TOTAL QTY ON ORDER],
> DUEDATE As [DUE DATE],
> GETDATE() As [CURRENT DATE]
> FROM(
> select DISTINCT SC020130.ITEMNMBR,
> SC020230.SFLINEITEM_I,
> SC020230.DATE1,
> SC020230.QTYTOFORECOST_I,
> MRPW4060.QUANTITY,
> MRPW4060.DUEDATE,
> GETDATE() As [CURRENT DATE],
> CASE WHEN GETDATE()<=MRPW4060.DUEDATE AND GETDATE()>=SC020230.DATE1
> THEN MRPW4060.QUANTITY ELSE 0 END AS [QTY ON ORDER]
> from SC020230 As SC020230
> JOIN SC020130 As SC020130
> on SC020230.SFLINEITEM_I = SC020130.SFLINEITEM_I
> JOIN MRPW4060 As MRPW4060
> on SC020130.ITEMNMBR = MRPW4060.ITEMNMBR
> where SC020230.PLANNAME_I = 'MRP FG1' and SC020130.ITEMNMBR = '89417' and
> SOPTYPE = '2') As I
> GROUP BY ITEMNMBR,
> SFLINEITEM_I,
> DATE1,
> QTYTOFORECOST_I,
> DUEDATE,
> QUANTITY
> ORDER BY ITEMNMBR ASC

Thank you so much, Russell, for your kindness!

SQL Programmer

"Russell Fields" wrote:

> SQL Programmer,
>
> Yes, there are some rude folks out there, but don't let them chase you away.
> (Kind of like you don't let someone honking from behind to cause you to go
> through the STOP sign before the way is clear.) So....
>
> Without going through your whole query, for which I would need the table
> definitions and some INSERT statements to put in some sample data, perhaps a
> simple discussion will .
>
> The derived table (the nested query) named I does not obviously return 1 row
> for month
>
> The [QTY ON ORDER] from the derived table I is summed in the outer query as
> [TOTAL QTY ON ORDER].
>
> You are grouping on several columns from the derived table I, namely
> ITEMNMBR, SFLINEITEM_I, DATE1, QTYTOFORECOST_I, DUEDATE, QUANTITY. So, you
> are getting one summed value of [QTY ON ORDER] for each of these groups. If
> I understand correctly, this is too small a grouping, but this should be
> grouped in such a way that DATE1 is only (for example) the first day of each
> month.
>
> So, perhaps in your derived table your could replace:
> SC020230.DATE1,
> with:
> DATEADD(MONTH,DATEDIFF(MONTH,0,SC020230.DATE1),0) AS DATE1,
>
> This would allow your GROUP BY to work with a month level DATE1 instead of a
> daily DATE1.
>
> Hope that s,
> RLF
>
> PS - At the risk of getting shot at by someone, I will also answer in
> ..programming.
>
>
> "SQL Programmer" <SQLProgrammer@discussions.microsoft.com> wrote in message
> news:6C64B47F-200C-4ACE-947E-57C77C0EDC67@microsoft.com...
> >I was treated very rudely on the SQL Programming message board by someone
> >who
> > doesnot have all of the facts about my background. Hopefully, there will
> > be
> > someone here who has some decency and kindness. (Otherwise, I'd continue
> > posting there.)
> >
> > Below is my SQL view. I need to have the QUANTITY field from the "THEN
> > MRPW4060.QUANTITY ELSE 0 END AS [QTY ON ORDER]" clause of the CASE
> > statement
> > below to be placed within just one record of my table (view).
> >
> > You see, each record in my view represents a month. The SC020230.DATE1
> > field, for example, could be "2008-07-01 00:00:00.000'. And, the
> > MRPW4060.DUEDATE field could be "2008-07-20 00:00:00.000". Therefore, I
> > want
> > the MRPW4060.QUANTITY field to appear just on that record for July (i.e.
> > the
> > record for the month of July only). Since the quantity is due in the
> > month
> > of July, I want the quantity to appear just on that record of rows
> > returned
> > from my view.
> >
> > How do I tell SQL to place the quantity due in just that one record for
> > that
> > month that it is due? I imagine I would have to do some sort of "trim"
> > statement on the DUEDATE or something.....
> >
> > Thanks! sqlprogrammer@hotmail.com
> >
> > select ITEMNMBR as [ITEM],
> > DATE1 As [FORECAST DATE],
> > QTYTOFORECOST_I As [FORECAST QTY],
> > SUM([QTY ON ORDER]) As [TOTAL QTY ON ORDER],
> > DUEDATE As [DUE DATE],
> > GETDATE() As [CURRENT DATE]
> > FROM(
> > select DISTINCT SC020130.ITEMNMBR,
> > SC020230.SFLINEITEM_I,
> > SC020230.DATE1,
> > SC020230.QTYTOFORECOST_I,
> > MRPW4060.QUANTITY,
> > MRPW4060.DUEDATE,
> > GETDATE() As [CURRENT DATE],
> > CASE WHEN GETDATE()<=MRPW4060.DUEDATE AND GETDATE()>=SC020230.DATE1
> > THEN MRPW4060.QUANTITY ELSE 0 END AS [QTY ON ORDER]
> > from SC020230 As SC020230
> > JOIN SC020130 As SC020130
> > on SC020230.SFLINEITEM_I = SC020130.SFLINEITEM_I
> > JOIN MRPW4060 As MRPW4060
> > on SC020130.ITEMNMBR = MRPW4060.ITEMNMBR
> > where SC020230.PLANNAME_I = 'MRP FG1' and SC020130.ITEMNMBR = '89417' and
> > SOPTYPE = '2') As I
> > GROUP BY ITEMNMBR,
> > SFLINEITEM_I,
> > DATE1,
> > QTYTOFORECOST_I,
> > DUEDATE,
> > QUANTITY
> > ORDER BY ITEMNMBR ASC
>
>
>