3 tables 2 queries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

3 tables 2 queries

Author

Topic

hbadministrator
Posting Yak Master

120 Posts

Posted - 2014-09-05 : 10:30:49

I have 3 tables I need joined, The first has date fields I need to search first then to sum gallons then link the results into the other table.

First table is
FO_fuelrouting
This table only has 3 fields I care about.
Cust-no, DelDate, and DelGallons

Next 2 tables
Table
Equipment
Fields are
Model, Active, DeliveryStatus, [Prod-type], name, [Cust-no]

Table
Customer
Fields are
[cust-no], name, address, city, st, [zip-code], telephone

Now I already have the bottom half setup and it pulls the correct info for joining equipment and customer. But I need to add the FO_fuelrouting and need to select 2 dates run a query and see what fuel was delivered during that time and sum it then run the rest. Not sure if I need to add it in the select first or what. All I need to show is the sum of gallons between that time frame for those customers grouped.

SELECT e.Model, e.active, e.DeliveryStatus, e.[prod-type], customer.name, customer.Address, customer.City, customer.St, customer.[Zip-code], customer.Telephone,
e.[Cust-no]
FROM equipment AS e INNER JOIN
customer ON e.[Cust-no] = customer.[Cust-no]
WHERE (e.Model IN (@Model)) AND (e.active = 'A') AND (e.DeliveryStatus IN (@DeliveryStatus)) AND (e.[prod-type] IN (@ProdType))

Data

Model, active, DeliveryStatus, prod-type, name, address, city, st, zip-code, telephone, cust-no, gallons

LPTNK, A, Auto, LP, Amory M , 62 Knat Dr, Harrisburg, PA, 17111-4290, 7176579999, 014323, (SUM GALLONS)

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-05 : 11:31:13

Please follow these posting guidelines: http://www.sqlservercentral.com/articles/Best+Practices/61537/

hbadministrator
Posting Yak Master

120 Posts

Posted - 2014-09-05 : 11:56:43

gbritton,

So instead of posting that like a prick, and don't have an answer skip and don't post. I have been asking for help for years on here and not once have I had someone like yourself decide to post a comment like this.

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-09-05 : 12:38:22

seems like you are just looking for that final join.

SELECT e.Model, e.active, e.DeliveryStatus, e.[prod-type], customer.name, customer.Address, customer.City, customer.St, customer.[Zip-code], customer.Telephone,
e.[Cust-no], SUM(ISNULL(DelGallons,0))DelGallons
FROM equipment e
INNER JOIN c customer ON e.[Cust-no] = c.[Cust-no]
LEFT JOIN FO_fuelrouting r ON r.[Cust-no] = c.[Cust-no]
WHERE
e.Model = @Model AND e.active = 'A' AND e.DeliveryStatus = @DeliveryStatus AND e.prod-type = @ProdType
and DelDate Between @startDeliveryDate and @EndDeliveryDate
GROUP BY e.Model, e.active, e.DeliveryStatus, e.[prod-type], customer.name, customer.Address, customer.City, customer.St, customer.[Zip-code], customer.Telephone, e.[Cust-no]

also - I am pretty sure your parameters will not work like you have them, so I changed them to singular rather than inclusionary

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-05 : 14:40:32

quote:
Originally posted by hbadministrator

gbritton,

So instead of posting that like a prick, and don't have an answer skip and don't post. I have been asking for help for years on here and not once have I had someone like yourself decide to post a comment like this.

@hbadministrator -- no. I can probably answer the question but I do not want to guess at the table definition and write the inserts to set up an environment to test on. I volunteer here. My available time is limited. It is entirely fair (and always expected) that posters will provide consumable content so those who wish to work on the problem can get going at once.

hbadministrator
Posting Yak Master

120 Posts

Posted - 2014-09-08 : 08:21:52

MichaelJSQL,

Thank you for that I just had to make a few tweaks and it works like a charm.

SELECT e.Model, e.active, e.DeliveryStatus, e.[prod-type], c.name, c.Address, c.City, c.St, c.[Zip-code], c.Telephone, e.[Cust-no], SUM(r.DelGallons) AS DelGallons

FROM
equipment AS e INNER JOIN
customer AS c ON e.[Cust-no] = c.[Cust-no]
LEFT OUTER JOIN
FO_FuelRouting AS r ON r.CustNo = c.[Cust-no]

WHERE
(e.Model = @Model) AND (e.active = 'A') AND (e.DeliveryStatus = @DeliveryStatus)
AND (r.DelDate BETWEEN @startDeliveryDate AND @EndDeliveryDate) AND(e.[prod-type] = @ProdType)

GROUP BY
e.Model, e.active, e.DeliveryStatus, e.[prod-type], c.name, c.Address, c.City, c.St, c.[Zip-code], c.Telephone, e.[Cust-no]
ORDER BY e.[Cust-no]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources