Several requirements/queries from two different DB

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

General SQL Server Forums

New to SQL Server Programming

Several requirements/queries from two different DB

Author

Topic

Lulle
Starting Member

3 Posts

Posted - 2015-04-09 : 10:31:47

Hello! Second entry =)
I have two databases (A, B) and two tables (Cust,Ship).

I have a task to pull out the purchase (database B, column Purchase) with the greatest number of units (database B, column Units) in november 2014 (database B, column Date) went to a customer (database A, column Customer) whose name contains the letter b. So far I have:

SELECT t1.DATE,t1.Purchase,t2.Customer,COUNT(t1.Units) AS Units
FROM B.SHIP t1 JOIN A.Cust t2 ON t2.CustomerCod = t1.CustmerCod
WHERE concat(YEAR(t1.DATE),MONTH(t1.DATE))='201411'
GROUP BY t1.Purchase HAVING MAX(t1.Unit)

I want to put an
AND LIKE Customer='%z%'
But it doesnt work. I just want a hint and not the whole solution thanks =)

PS I also tried making a subquery with sum value of Units and then selecting a MAX on that. Maybe I should have a HAVING?

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2015-04-09 : 10:52:35

and customer like '%z%'

Javeed Ahmed
https://www.linkedin.com/pub/javeed-ahmed/25/5b/95

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-09 : 10:54:44

Something like this maybe?


SELECT t11.DATE
    , t1.Purchase
    , t2.Customer
    , t1.Units
FROM (
    SELECT purchase
        , count(units) AS Units
    FROM b.ship
    WHERE replace(convert(VARCHAR(7), DATE, 102), '.', '') = '201411'
    GROUP BY purchase
    ) t1
JOIN B.SHIP t11 ON t1.Purchase = t11.Purchase
JOIN a.cust t2 ON t2.CustomerCod = t11.CustomerCod

Lulle
Starting Member

3 Posts

Posted - 2015-04-09 : 10:58:52

quote:
Originally posted by ahmeds08

and customer like '%z%'

Javeed Ahmed
https://www.linkedin.com/pub/javeed-ahmed/25/5b/95

Thanks. I just remember I did that way. I just wrote it wrong here. And that doesn't work for me

quote:
Originally posted by gbritton
Something like this maybe?

SELECT t11.DATE
, t1.Purchase
, t2.Customer
, t1.Units
FROM (
SELECT purchase
, count(units) AS Units
FROM b.ship
WHERE replace(convert(VARCHAR(7), DATE, 102), '.', '') = '201411'
GROUP BY purchase
) t1
JOIN B.SHIP t11 ON t1.Purchase = t11.Purchase
JOIN a.cust t2 ON t2.CustomerCod = t11.CustomerCod

Thanks! Will try it. But is this the whole solution :P? I rather just have hints, if you can give hints without giving away the answer of course

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-09 : 11:12:56

it's just a piece

customer like '%z%'

bit. The idea is to use the subquery to count units per purchase (you had count, I'm wondering if that should be sum?), then join back with the purchase table to get the other columns. You still have to get the max of the count(sum?) of units using a second subquery though

newwaysys
Starting Member

9 Posts

Posted - 2015-04-10 : 05:39:01

I have a task to pull out the purchase (database B, column Purchase) with the greatest number of units (database B, column Units) in november 2014 (database B, column Date) went to a customer (database A, column Customer) whose name contains the letter b. So far I have:

SELECT t1.DATE,t1.Purchase,t2.Customer,COUNT(t1.Units) AS Units
FROM B.SHIP t1 JOIN A.Cust t2 ON t2.CustomerCod = t1.CustmerCod
WHERE concat(YEAR(t1.DATE),MONTH(t1.DATE))='201411'
GROUP BY t1.Purchase HAVING MAX(t1.Unit)
unspammed
I want to put an
AND LIKE Customer='%z%'
But it doesnt work. I just want a hint and not the whole solution thanks =)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources