Eric,
You can find total payments made against orders in a specific date range that
contain a specific part number by doing something like this:
select
sum(p.pmt) as order_pmts,
i.part_number,
o.order_number,
o.order_date,
o.order_total
from orders o
inner join order_items i on o.order_id = i.order_id
inner join payments on o.order_id = p.order_id
where o.order_date
between <cfqueryparam cfsqltype="cf_sql_timestamp" value="#start_date#">
and <cfqueryparam cfsqltype="cf_sql_timestamp" value="#end_date#">
and i.part_number =
<cfqueryparam cfsqltype="cf_sql_varchar" value="#part_number#">
group by
o.order_date, i.part_number, o.order_total, o.order_number
order by
o.order_date
If you need to find payments for orders with multiple part numbers, you can
rewrite the part number filter to:
and i.part_number in (<cfqueryparam cfsqltype="cf_sql_varchar" list="yes"
value="#part_number#">)
This is not tested and is not database-specific since you didn't mention what
DBMS you are using, but it should get you started in the right direction.
Dina
----- Original Message -----
From: "Eric Hoffman" <[EMAIL PROTECTED]>
To: "CF-Talk" <[email protected]>
Sent: Monday, January 24, 2005 10:43 AM
Subject: RE: Query Help
> I know its goofy....but lets say the 500 lb gorilla wants to eat...and
> he likes crappy reports.
>
> Is there a logic pattern I should follow...or do I run the query to find
> total $ in order items that contain the inventory item....and then check
> the payments table for any payments, and then just say, well, if there
> was a payment...count it? I would love to do in a single SQL query...
>
> Know what I mean?
>
> -----Original Message-----
> From: Dina Hess [mailto:[EMAIL PROTECTED]
> Sent: Monday, January 24, 2005 10:12 AM
> To: CF-Talk
> Subject: Re: Query Help
>
> Eric,
>
> I don't think the problem has anything to do with your knowledge of SQL.
> It's not typical for a company to allocate order payments to specific
> order line items; rather, payments are typically applied to the entire
> order. Therefore, how could you possibly determine which line item a
> payment should be applied to...unless I'm missing something.
>
> Dina
>
>
> ----- Original Message -----
> From: "Eric Hoffman" <[EMAIL PROTECTED]>
> To: "CF-Talk" <[email protected]>
> Sent: Monday, January 24, 2005 7:27 AM
> Subject: Query Help
>
>
> > I have to make a report based upon this scenario...seems simple, yet I
> > can't get the numbers right....any pointers? (I have ordered a newer
> > Advanced SQL book in the meantime!!)
> >
> > Need to get orders within date range containing a part number and get
> > total dollars received in payments for this.
> >
> > So I query the orders table....which is related to the order_items
> > table, where each line item of the order is stored, but does include a
> > line_total field which did qty * rate charged. On the other hand, the
> > payments table is related to the orders table by order id, and shows
> > amount paid...but obviously not breaking out what of the payment
> applies
> > to what.
> >
> > So how do I query to get the applicable payment amount correct by that
> > item id? Right now my numbers are inflated because it gets the whole
> > order payment amount. Recievables higher than sales is great in
> > fantasyland. Mucho frustrating no doubt.
> >
> > Thanks guys.
> >
> > Eric
> >
> >
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble
Ticket application
http://www.houseoffusion.com/banners/view.cfm?bannerid=48
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191691
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
