Help With Report That Shows Late Sales Orders

Hello Everyone,
I'm running SQL Server Reporting Services on SQL2005 SE. The data for the
reports is coming from a SQL 2000 EE database. All running on Server 2003.
I've been asked to create a report that shows all sales orders that are past
thier due date. I'm having some difficulty in coming up with a way to do this.
The table I'm pulling the information from is named oe_hdr (Order Entry
Header)
In the table there is a field named req_date (required date). This field is
used by the sales staff to enter in the date that the order is due to ship
from our facility. There is also a field named complete. This field is
checked when the order is invoiced, I think.
The sales staff wants a report that shows all orders that have not shipped
by thier due date.
Based on the info I've provided does anyone have an idea how I can make this
report. Maybe an expression or something. If this is not enough info, please
let me know.
Thanks.On May 7, 11:50 am, Damon Johnson
<DamonJohn...@.discussions.microsoft.com> wrote:
> Hello Everyone,
> I'm running SQL Server Reporting Services on SQL2005 SE. The data for the
> reports is coming from a SQL 2000 EE database. All running on Server 2003.
> I've been asked to create a report that shows all sales orders that are past
> thier due date. I'm having some difficulty in coming up with a way to do this.
> The table I'm pulling the information from is named oe_hdr (Order Entry
> Header)
> In the table there is a field named req_date (required date). This field is
> used by the sales staff to enter in the date that the order is due to ship
> from our facility. There is also a field named complete. This field is
> checked when the order is invoiced, I think.
> The sales staff wants a report that shows all orders that have not shipped
> by thier due date.
> Based on the info I've provided does anyone have an idea how I can make this
> report. Maybe an expression or something. If this is not enough info, please
> let me know.
> Thanks.
So you want the report to ONLY show late orders and nothing else?
Put a where clause that includes this:
select *
from oe_hdr
where getdate() >= req_date and complete = ?
I'm not sure what data is put into that complete field, but whatever
indicated that is it NOT complete, then it should be put where I
placed the question marks. The script will look for any orders where
the getdate() [THIS IS TODAY'S DATE] is greater than or equal to the
req_date and the order has not completed - therefore it is overdue.
I hope this helps. A helpful tip, it might be better for your
company's privacy to make up names for tables. You never know how
people may use your information.|||Thanks Ayman,
Since writing my query, I found this in Visual Studio help;
=IIF(DateDiff("d",Fields!ImportantDate.Value, Now())>1,"Red","Blue")
What this does is compare the value of the importantdate with todays date
and if the importandate is greater than a day old, it will format the date
font as red, otherwise blue. This gets me closer to what I'm looking for.
I'll massage this and see what i come up with. I will also give your
suggestion a go as well.
Thanks for the privacy info.
"Ayman" wrote:
> On May 7, 11:50 am, Damon Johnson
> <DamonJohn...@.discussions.microsoft.com> wrote:
> > Hello Everyone,
> > I'm running SQL Server Reporting Services on SQL2005 SE. The data for the
> > reports is coming from a SQL 2000 EE database. All running on Server 2003.
> >
> > I've been asked to create a report that shows all sales orders that are past
> > thier due date. I'm having some difficulty in coming up with a way to do this.
> >
> > The table I'm pulling the information from is named oe_hdr (Order Entry
> > Header)
> > In the table there is a field named req_date (required date). This field is
> > used by the sales staff to enter in the date that the order is due to ship
> > from our facility. There is also a field named complete. This field is
> > checked when the order is invoiced, I think.
> >
> > The sales staff wants a report that shows all orders that have not shipped
> > by thier due date.
> >
> > Based on the info I've provided does anyone have an idea how I can make this
> > report. Maybe an expression or something. If this is not enough info, please
> > let me know.
> > Thanks.
> So you want the report to ONLY show late orders and nothing else?
> Put a where clause that includes this:
> select *
> from oe_hdr
> where getdate() >= req_date and complete = ?
> I'm not sure what data is put into that complete field, but whatever
> indicated that is it NOT complete, then it should be put where I
> placed the question marks. The script will look for any orders where
> the getdate() [THIS IS TODAY'S DATE] is greater than or equal to the
> req_date and the order has not completed - therefore it is overdue.
> I hope this helps. A helpful tip, it might be better for your
> company's privacy to make up names for tables. You never know how
> people may use your information.
>|||On May 7, 12:25 pm, Damon Johnson
<DamonJohn...@.discussions.microsoft.com> wrote:
> Thanks Ayman,
> Since writing my query, I found this in Visual Studio help;
> =IIF(DateDiff("d",Fields!ImportantDate.Value, Now())>1,"Red","Blue")
> What this does is compare the value of the importantdate with todays date
> and if the importandate is greater than a day old, it will format the date
> font as red, otherwise blue. This gets me closer to what I'm looking for.
> I'll massage this and see what i come up with. I will also give your
> suggestion a go as well.
> Thanks for the privacy info.
> "Ayman" wrote:
> > On May 7, 11:50 am, Damon Johnson
> > <DamonJohn...@.discussions.microsoft.com> wrote:
> > > Hello Everyone,
> > > I'm running SQL Server Reporting Services on SQL2005 SE. The data for the
> > > reports is coming from a SQL 2000 EE database. All running on Server 2003.
> > > I've been asked to create a report that shows all sales orders that are past
> > > thier due date. I'm having some difficulty in coming up with a way to do this.
> > > The table I'm pulling the information from is named oe_hdr (Order Entry
> > > Header)
> > > In the table there is a field named req_date (required date). This field is
> > > used by the sales staff to enter in the date that the order is due to ship
> > > from our facility. There is also a field named complete. This field is
> > > checked when the order is invoiced, I think.
> > > The sales staff wants a report that shows all orders that have not shipped
> > > by thier due date.
> > > Based on the info I've provided does anyone have an idea how I can make this
> > > report. Maybe an expression or something. If this is not enough info, please
> > > let me know.
> > > Thanks.
> > So you want the report to ONLY show late orders and nothing else?
> > Put a where clause that includes this:
> > select *
> > from oe_hdr
> > where getdate() >= req_date and complete = ?
> > I'm not sure what data is put into that complete field, but whatever
> > indicated that is it NOT complete, then it should be put where I
> > placed the question marks. The script will look for any orders where
> > the getdate() [THIS IS TODAY'S DATE] is greater than or equal to the
> > req_date and the order has not completed - therefore it is overdue.
> > I hope this helps. A helpful tip, it might be better for your
> > company's privacy to make up names for tables. You never know how
> > people may use your information.
Thats a good way to show all the data and filter it by color. Hint:
you can put nicer colors by using their numbers like "#dedab5" in
place of red or blue. If you select the entire data row, then put
that IIF command in the background color box under properties. It will
make the entire row change color as opposed to one cell - usually
easier to see.|||Ayman,
Do you have any suggestions on how to use the Datediff in my query to pull
the reports that are a day late?
Thanks.
"Ayman" wrote:
> On May 7, 12:25 pm, Damon Johnson
> <DamonJohn...@.discussions.microsoft.com> wrote:
> > Thanks Ayman,
> > Since writing my query, I found this in Visual Studio help;
> >
> > =IIF(DateDiff("d",Fields!ImportantDate.Value, Now())>1,"Red","Blue")
> >
> > What this does is compare the value of the importantdate with todays date
> > and if the importandate is greater than a day old, it will format the date
> > font as red, otherwise blue. This gets me closer to what I'm looking for.
> > I'll massage this and see what i come up with. I will also give your
> > suggestion a go as well.
> >
> > Thanks for the privacy info.
> >
> > "Ayman" wrote:
> > > On May 7, 11:50 am, Damon Johnson
> > > <DamonJohn...@.discussions.microsoft.com> wrote:
> > > > Hello Everyone,
> > > > I'm running SQL Server Reporting Services on SQL2005 SE. The data for the
> > > > reports is coming from a SQL 2000 EE database. All running on Server 2003.
> >
> > > > I've been asked to create a report that shows all sales orders that are past
> > > > thier due date. I'm having some difficulty in coming up with a way to do this.
> >
> > > > The table I'm pulling the information from is named oe_hdr (Order Entry
> > > > Header)
> > > > In the table there is a field named req_date (required date). This field is
> > > > used by the sales staff to enter in the date that the order is due to ship
> > > > from our facility. There is also a field named complete. This field is
> > > > checked when the order is invoiced, I think.
> >
> > > > The sales staff wants a report that shows all orders that have not shipped
> > > > by thier due date.
> >
> > > > Based on the info I've provided does anyone have an idea how I can make this
> > > > report. Maybe an expression or something. If this is not enough info, please
> > > > let me know.
> > > > Thanks.
> >
> > > So you want the report to ONLY show late orders and nothing else?
> > > Put a where clause that includes this:
> > > select *
> > > from oe_hdr
> > > where getdate() >= req_date and complete = ?
> >
> > > I'm not sure what data is put into that complete field, but whatever
> > > indicated that is it NOT complete, then it should be put where I
> > > placed the question marks. The script will look for any orders where
> > > the getdate() [THIS IS TODAY'S DATE] is greater than or equal to the
> > > req_date and the order has not completed - therefore it is overdue.
> >
> > > I hope this helps. A helpful tip, it might be better for your
> > > company's privacy to make up names for tables. You never know how
> > > people may use your information.
> Thats a good way to show all the data and filter it by color. Hint:
> you can put nicer colors by using their numbers like "#dedab5" in
> place of red or blue. If you select the entire data row, then put
> that IIF command in the background color box under properties. It will
> make the entire row change color as opposed to one cell - usually
> easier to see.
>|||On May 7, 1:26 pm, Damon Johnson
<DamonJohn...@.discussions.microsoft.com> wrote:
> Ayman,
> Do you have any suggestions on how to use the Datediff in my query to pull
> the reports that are a day late?
> Thanks.
> "Ayman" wrote:
> > On May 7, 12:25 pm, Damon Johnson
> > <DamonJohn...@.discussions.microsoft.com> wrote:
> > > Thanks Ayman,
> > > Since writing my query, I found this in Visual Studio help;
> > > =IIF(DateDiff("d",Fields!ImportantDate.Value, Now())>1,"Red","Blue")
> > > What this does is compare the value of the importantdate with todays date
> > > and if the importandate is greater than a day old, it will format the date
> > > font as red, otherwise blue. This gets me closer to what I'm looking for.
> > > I'll massage this and see what i come up with. I will also give your
> > > suggestion a go as well.
> > > Thanks for the privacy info.
> > > "Ayman" wrote:
> > > > On May 7, 11:50 am, Damon Johnson
> > > > <DamonJohn...@.discussions.microsoft.com> wrote:
> > > > > Hello Everyone,
> > > > > I'm running SQL Server Reporting Services on SQL2005 SE. The data for the
> > > > > reports is coming from a SQL 2000 EE database. All running on Server 2003.
> > > > > I've been asked to create a report that shows all sales orders that are past
> > > > > thier due date. I'm having some difficulty in coming up with a way to do this.
> > > > > The table I'm pulling the information from is named oe_hdr (Order Entry
> > > > > Header)
> > > > > In the table there is a field named req_date (required date). This field is
> > > > > used by the sales staff to enter in the date that the order is due to ship
> > > > > from our facility. There is also a field named complete. This field is
> > > > > checked when the order is invoiced, I think.
> > > > > The sales staff wants a report that shows all orders that have not shipped
> > > > > by thier due date.
> > > > > Based on the info I've provided does anyone have an idea how I can make this
> > > > > report. Maybe an expression or something. If this is not enough info, please
> > > > > let me know.
> > > > > Thanks.
> > > > So you want the report to ONLY show late orders and nothing else?
> > > > Put a where clause that includes this:
> > > > select *
> > > > from oe_hdr
> > > > where getdate() >= req_date and complete = ?
> > > > I'm not sure what data is put into that complete field, but whatever
> > > > indicated that is it NOT complete, then it should be put where I
> > > > placed the question marks. The script will look for any orders where
> > > > the getdate() [THIS IS TODAY'S DATE] is greater than or equal to the
> > > > req_date and the order has not completed - therefore it is overdue.
> > > > I hope this helps. A helpful tip, it might be better for your
> > > > company's privacy to make up names for tables. You never know how
> > > > people may use your information.
> > Thats a good way to show all the data and filter it by color. Hint:
> > you can put nicer colors by using their numbers like "#dedab5" in
> > place of red or blue. If you select the entire data row, then put
> > that IIF command in the background color box under properties. It will
> > make the entire row change color as opposed to one cell - usually
> > easier to see.
try using Today() instead of now since it is a date function. The
Syntax seems correct just make sure you use it in the actual report
properties section. So select the entire row (not the titles, but
where the data is on your table/matrix) and press properties. Under
background color, select EXPRESSION and put in the expression. You
can use TRANSPARENT as the color that is used for orders that are not
late as opposed to BLUE. There is also a section about visibility
under properties but it's pretty tricky and I've wasted hours on it.
Fancy colors should impress the boss : D
Let me know how it works out, I have a suggestion for a case statement
if needed.|||On May 7, 1:26 pm, Damon Johnson
<DamonJohn...@.discussions.microsoft.com> wrote:
> Ayman,
> Do you have any suggestions on how to use the Datediff in my query to pull
> the reports that are a day late?
> Thanks.
> "Ayman" wrote:
> > On May 7, 12:25 pm, Damon Johnson
> > <DamonJohn...@.discussions.microsoft.com> wrote:
> > > Thanks Ayman,
> > > Since writing my query, I found this in Visual Studio help;
> > > =IIF(DateDiff("d",Fields!ImportantDate.Value, Now())>1,"Red","Blue")
> > > What this does is compare the value of the importantdate with todays date
> > > and if the importandate is greater than a day old, it will format the date
> > > font as red, otherwise blue. This gets me closer to what I'm looking for.
> > > I'll massage this and see what i come up with. I will also give your
> > > suggestion a go as well.
> > > Thanks for the privacy info.
> > > "Ayman" wrote:
> > > > On May 7, 11:50 am, Damon Johnson
> > > > <DamonJohn...@.discussions.microsoft.com> wrote:
> > > > > Hello Everyone,
> > > > > I'm running SQL Server Reporting Services on SQL2005 SE. The data for the
> > > > > reports is coming from a SQL 2000 EE database. All running on Server 2003.
> > > > > I've been asked to create a report that shows all sales orders that are past
> > > > > thier due date. I'm having some difficulty in coming up with a way to do this.
> > > > > The table I'm pulling the information from is named oe_hdr (Order Entry
> > > > > Header)
> > > > > In the table there is a field named req_date (required date). This field is
> > > > > used by the sales staff to enter in the date that the order is due to ship
> > > > > from our facility. There is also a field named complete. This field is
> > > > > checked when the order is invoiced, I think.
> > > > > The sales staff wants a report that shows all orders that have not shipped
> > > > > by thier due date.
> > > > > Based on the info I've provided does anyone have an idea how I can make this
> > > > > report. Maybe an expression or something. If this is not enough info, please
> > > > > let me know.
> > > > > Thanks.
> > > > So you want the report to ONLY show late orders and nothing else?
> > > > Put a where clause that includes this:
> > > > select *
> > > > from oe_hdr
> > > > where getdate() >= req_date and complete = ?
> > > > I'm not sure what data is put into that complete field, but whatever
> > > > indicated that is it NOT complete, then it should be put where I
> > > > placed the question marks. The script will look for any orders where
> > > > the getdate() [THIS IS TODAY'S DATE] is greater than or equal to the
> > > > req_date and the order has not completed - therefore it is overdue.
> > > > I hope this helps. A helpful tip, it might be better for your
> > > > company's privacy to make up names for tables. You never know how
> > > > people may use your information.
> > Thats a good way to show all the data and filter it by color. Hint:
> > you can put nicer colors by using their numbers like "#dedab5" in
> > place of red or blue. If you select the entire data row, then put
> > that IIF command in the background color box under properties. It will
> > make the entire row change color as opposed to one cell - usually
> > easier to see.
try using Today() instead of now since it is a date function. The
Syntax seems correct just make sure you use it in the actual report
properties section. So select the entire row (not the titles, but
where the data is on your table/matrix) and press properties. Under
background color, select EXPRESSION and put in the expression. You
can use TRANSPARENT as the color that is used for orders that are not
late as opposed to BLUE. There is also a section about visibility
under properties but it's pretty tricky and I've wasted hours on it.
Fancy colors should impress the boss : D
Let me know how it works out, I have a suggestion for a case statement
if needed.sql

help with query

hi all
Need a bit of help/direction with an sql query
i have a table with customer details and in another with customer orders. I need to show all the orders in the same field based upon the id from the customer details table.In other words to piggy back all the orders in one field.Do i use some sort of sub query?
if i have :

customer orders:

custID Order
c1 Vauxhall
c1 ford
c1 VW
c1 BMW

so that i get:

customer car orders
c1 Vauxhall ford VW BMW


How can i achieve this? thanks daveThe best way to do a pivot is using the client software. It can be done reasonably cleanly on the server using database engine specific code. It can also be done with pure SQL, but that requires some assumptions and some rather ugly code.

The short answer is: you should really do this on the client.

-PatP|||Why not write a function that returns a list of orders and use it in the select list.
create function get_orders(i_custid VARCHAR2) RETURN VARCHAR2
IS
retval VARCHAR2(999);
BEGIN
for rec in (select order from customer_orders where custid = d_custid)
loop
retval := retval || ' '|| order;
end loop;
return retval;
end;

select distinct custid customer, get_orders(custid) "car orders"
from customer_orders;

Now, that's oracle specific but you get the gist.|||in sybase asa, use the list (http://sybooks.sybase.com/onlinebooks/group-sas/awg0802e/dbrfen8/@.Generic__BookTextView/22251;pt=14246/*;nh=1?DwebQuery=LIST+function&DwebSearchAll=1) function:
select customers.custID
, list(custorder)
from customers
left outer
join orders
on customers.custID
= orders.custID
group
by customers.custid

in mysql 4.1, use the group_concat (http://dev.mysql.com/doc/mysql/en/GROUP-BY-Functions.html) function:
select customers.custID
, group_concat(custorder)
from customers
left outer
join orders
on customers.custID
= orders.custID
group
by customers.custid

in other less advanced databases, write a program

;)|||Arrr, maybe we ought to start with why would you ever need this?
If I was to design my own DBMS I would have put this type of function on the LRU list and flush out of SGA ASAP.
:D|||thanks.. all your help is much appriciated ;)|||thanks.. all your help is much appriciated ;)
What DB are you on anyways?

Help With Query

Hi All,

I am trying to build a report where show all the numbers of orders completed and orders which are completed on time. However, I have a hard time to figure out how to build the where clause since I want to get all the completed orders and at the same time, I also want to count the completed orders on time. Can anyone help me with this query?

This is my query right now:

SELECT COUNT(o.OrderID)
FROM Order o
WHERE MONTH(o.InvoiceDT) = @.Month

TIA

hi,

first of all is there any field like order_to_be_finished_by (date) & finished_on(data) fields or not in your table.

Select Count(OrderID) As OrdersCompletedOnTime from table

where convert(varchar, to_be_Finished_Date, 103) = convert(varchar, finished_on, 103)

& second query

Select Count(OrderID) As OrdersCompletedOnTime from table

where finished_on is not null

hope it helps

regards,

satish.

|||

Thank you satish,

So, it is would be two different select statement in one single stored procedures.

|||

yep,

regards,

satish

Help with my query

Hi,

I would like to get the data which return employee code associate with the order that is already assigned to them. But on those orders, I also would like to create different column counting how many orders are finished, and how many orders are still in process.

Can anyone help me with the query?

=============================================================

SELECT
e.EmployeeCode,
COUNT(oa.OrderID) as OrderCount
FROM Employee e
INNER JOIN OrderAssignment oa ON oa.EmployeeID = e.EmployeeID
WHERE e.DivisionCode = 'COM'
GROUP BY e.EmployeeCode

==============================================================

Above query will return the number of orders assigned to employee, however, I also need to get how many orders finished and how many orders still in process.

Thanks in advance.

SELECT
e.EmployeeCode,
COUNT(oa.OrderID) as OrderCount,

SUM(CASE WHERE oa.OrderStatus='Finished' THEN 1 ELSE 0 END) AS Finished,

SUM(CASE WHERE oa.OrderStatus='InProcess' THEN 1 ELSE 0 END) AS InProcess
FROM Employee e
INNER JOIN OrderAssignment oa ON oa.EmployeeID = e.EmployeeID
WHERE e.DivisionCode = 'COM'
GROUP BY e.EmployeeCode