September 7, 2011 at 10:44 pm
Hi There,
I have been playing around with a specific query that I just cannot get my head around.
I have 2 separate queries that work perfectly, but when I need to merge the data together, or join rather, either my code is wrong or I am just not using the correct method.
The first query is as follows:
declare @one int , @two int
SET @one = (Select sum(DocTotal) as AP1 From oinv where (DocDate >= '2010-01-01 00:00:00.000' and DocDate <= '2010-12-31 23:59:59.000' and CardCode = 'c1003'))
SET @two = (select sum(DocTotal) as AP2 From orin where (DocDate >= '2010-01-01 00:00:00.000' and DocDate <= '2010-12-31 23:59:59.000' and CardCode = 'c1003'))
select @one 'debit',
@two 'credit',
(@one-@two)/1.1
This essentially gets me the desired result for a mathematical equation.
The second query is as follows:
select CardCode, CardName, Phone1, Cellular from ocrd where groupcode='102' and SlpCode='66' order by CardName
As mentioned previously, these work when run by themselves.
The question I have is how to merge the data together.
The columns should be like this:
CardCode CardName Phone1 Cellular (@one-@two)/1.1
Hopefully this makes sense, if you need any more information please let me know.
I'm really hitting a brick wall with this one.
Thanks!
September 7, 2011 at 11:50 pm
Hi,
With the this script you will have the same debit, credit and 1.1 value on every row of the first query
if it's not what you whant you need something to join the information from the first to the second query other wise it wont make it
Hope it will work, can't test it.
declare @one int , @two int
SET @one = (Select sum(DocTotal) as AP1 From oinv where (DocDate >= '2010-01-01 00:00:00.000' and DocDate <= '2010-12-31 23:59:59.000' and CardCode = 'c1003'))
SET @two = (select sum(DocTotal) as AP2 From orin where (DocDate >= '2010-01-01 00:00:00.000' and DocDate <= '2010-12-31 23:59:59.000' and CardCode = 'c1003'))
select t1.CardCode, t1.CardName, t1.Phone1, t1.Cellular, t2.credit, t2.credit, t2.3rdvalue
from ocrd as t1
inner join ( select @one 'debit', @two 'credit', (@one-@two)/1.1 as 3rdvalue
) as t2
on t1.carcode <> '' --need to put a clause will be always true here
where t1.groupcode='102' and t1.SlpCode='66'
order by t1.CardName
September 8, 2011 at 12:31 am
Hi TheKnee,
That was very helpful, thank you!
I modified it a little as I didn't actually need a couple of statements (I should have cleaned it up first).
There is just one more thing, where I have and CardCode = 'c1003' I need this to be referenced between the tables as each Carcode should be presented here.
At the moment it returns all rows that match, but the '3rdvalue' is always going to be matching c1003. Is this what you meant in your reference to t1.CardCode?
I hope that makes sense.
Updated code so far:
declare @one int , @two int
SET @one = (Select sum(DocTotal) as AP1 From oinv where (DocDate >= '2010-01-01 00:00:00.000' and DocDate <= '2010-12-31 23:59:59.000' and CardCode = 'c1003'))
SET @two = (select sum(DocTotal) as AP2 From orin where (DocDate >= '2010-01-01 00:00:00.000' and DocDate <= '2010-12-31 23:59:59.000' and CardCode = 'c1003'))
select t1.CardCode, t1.CardName, t1.Phone1, t1.Cellular, t2.three
from ocrd as t1
inner join ( select (@one-@two)/1.1 as three
) as t2
on t1.cardcode <> ''
where t1.groupcode='102' and t1.SlpCode='66'
order by t1.CardName
September 8, 2011 at 6:20 am
So If I understand correctly the 1.1 field will should match with card code.
it's on the ON CLAUSE you have to create the Join.
This is like make a join on a table, except you do it on Sub Query.
Not sure if I answer the right thing let me know
declare @one int , @two int
SET @one = (Select sum(DocTotal) as AP1 From oinv where (DocDate >= '2010-01-01 00:00:00.000' and DocDate <= '2010-12-31 23:59:59.000' and CardCode = 'c1003'))
SET @two = (select sum(DocTotal) as AP2 From orin where (DocDate >= '2010-01-01 00:00:00.000' and DocDate <= '2010-12-31 23:59:59.000' and CardCode = 'c1003'))
select t1.CardCode, t1.CardName, t1.Phone1, t1.Cellular, t2.three
from ocrd as t1
inner join ( select (@one-@two)/1.1 as three
) as t2
on t1.cardcode = t2.three
where t1.groupcode='102' and t1.SlpCode='66'
order by t1.CardName
September 8, 2011 at 6:43 am
Why work on only one cardcode at a time? Have a look at what this returns, I'm sure it can be tailored to your requirements.
SELECT
ocrd.CardCode,
ocrd.CardName,
ocrd.Phone1,
ocrd.Cellular,
oinv.*,
orin.*
FROM ocrd ocrd
LEFT JOIN (
SELECT CardCode, AP1 = SUM(DocTotal)
FROM oinv
WHERE DocDate BETWEEN '2010-01-01 00:00:00.000' AND '2010-12-31 23:59:59.000'
GROUP BY CardCode
) oinv ON oinv.CardCode = ocrd.CardCode
LEFT JOIN (
SELECT CardCode, AP1 = SUM(DocTotal)
FROM orin
WHERE DocDate BETWEEN '2010-01-01 00:00:00.000' AND '2010-12-31 23:59:59.000'
GROUP BY CardCode
) orin ON orin.CardCode = ocrd.CardCode
WHERE 1 = 1 -- or whatever
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 8, 2011 at 6:45 am
Hi TheKnee,
Actually wouldn't this line be an issue as it references c1003 directly?
SET @one = (Select sum(DocTotal) as AP1 From oinv where (DocDate >= '2010-01-01 00:00:00.000' and DocDate <= '2010-12-31 23:59:59.000' and CardCode = 'c1003'))
Obbiously for @one and @two.
September 8, 2011 at 6:47 am
Hi Chris,
That's my point. I was able to test with a manually defined code, I'm after a dynamic field (if that's the correct term).
I will be back at work tomorrow morning and will try out your method.
Thanks!
September 8, 2011 at 9:05 pm
Hi Chris,
Your example worked well, however I don't know the syntax to only show the end result of the calculation.
To be more specific, Instead of displaying the result of the SUM(DocTotal) both times in each select table, I need to subtract (and divide by 10% as shown by my first example ) the result.
I'm a little new at this but once I have the correct method I can build on it from there.
Thanks!
September 9, 2011 at 2:14 am
Post up what you have so far, should be straightforward 😉
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 9, 2011 at 5:09 am
Actually, you are right.
I played around about and built up the query step by step from your example.
Now it works, but I need to add another complexity 😉
I can see that a select statement is working well within a join. I need to add another select statement from the same table, well one per select statement:
select cardcode, dMTD = sum(DocTotal)
from oinv
where DocDate between '2010-01-01 00:00:00.000' and '2010-01-31 23:59:59.000'
So essentially I will add a month do date column too. I can't seem to find any references on Google for multiple selects within a join (or my terminology is wrong).
Working code below.
Select ocrd.CardCode, ocrd.CardName, ocrd.CntctPrsn, ocrd.Phone1, ocrd.Cellular, (isnull(debit,0)-isnull(credit,0))/1.1
from OCRD
left join (
select cardcode, debit = sum(DocTotal)
from oinv
where DocDate between '2010-01-01 00:00:00.000' and '2010-12-31 23:59:59.000'
group by CardCode)
oinv on oinv.CardCode = ocrd.CardCode
left join (
select CardCode, credit = sum(DocTotal)
from orin
where DocDate between '2010-01-01 00:00:00.000' and '2010-12-31 23:59:59.000'
group by CardCode)
orin on orin.CardCode = ocrd.CardCode
where (ocrd.SlpCode='66' and ocrd.GroupCode='102')
order by CardName
Any ideas?
Thanks.
September 9, 2011 at 5:27 am
Something like this?
SELECT
ocrd.CardCode,
ocrd.CardName,
ocrd.CntctPrsn,
ocrd.Phone1,
ocrd.Cellular,
YearTotal = (ISNULL(oinv.debit,0)-ISNULL(orin.credit,0))/1.1,
Jan2010Total = (ISNULL(moinv.dMTD,0)-ISNULL(morin.cMTD,0))/1.1
FROM OCRD ocrd
LEFT JOIN (
SELECT cardcode, debit = SUM(DocTotal)
FROM oinv
WHERE DocDate BETWEEN '2010-01-01 00:00:00.000' AND '2010-12-31 23:59:59.000'
GROUP BY CardCode
) oinv ON oinv.CardCode = ocrd.CardCode
LEFT JOIN (
SELECT cardcode, dMTD = SUM(DocTotal)
FROM oinv
WHERE DocDate BETWEEN '2010-01-01 00:00:00.000' AND '2010-01-31 23:59:59.000'
GROUP BY CardCode
) moinv ON oinv.CardCode = ocrd.CardCode
LEFT JOIN (
SELECT CardCode, credit = sum(DocTotal)
FROM orin
WHERE DocDate BETWEEN '2010-01-01 00:00:00.000' AND '2010-12-31 23:59:59.000'
GROUP BY CardCode
) orin ON orin.CardCode = ocrd.CardCode
LEFT JOIN (
SELECT cardcode, cMTD = SUM(DocTotal)
FROM orin
WHERE DocDate BETWEEN '2010-01-01 00:00:00.000' AND '2010-01-31 23:59:59.000'
GROUP BY CardCode
) morin ON orin.CardCode = ocrd.CardCode
WHERE (ocrd.SlpCode='66' AND ocrd.GroupCode='102')
ORDER BY CardName
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 9, 2011 at 6:01 am
Hi Chris,
Very much so, although there is something not right.
I'm getting duplicates, in fact 198 of the same item with a different MTD for each.
I did pretty much what you have posted earlier today, but where I had it wrong was obviously a different join(?) name. I was repeating the 'oinv ON oinv.CardCode = ocrd.CardCode' etc.
Edit: I just changed the
moinv ON moinv.CardCode = ocrd.CardCode
to
morin ON morin.CardCode = ocrd.CardCode
That looks better, I'll have to check when I am back in against the accounting software.
Seriously though, thank you very much for your help. It's frustrating when you don't know the right questions to ask but you have sorted me out regardless!
September 9, 2011 at 6:12 am
Let's assume for a moment that the results aren't quite what you're expecting because of dupes in the output. Now, if you were to set up sample data scripts with some realistic values, then everyone can have a play to figure out what's going on. The query I've posted is quite poor; there are four LEFT JOINs to two tables and two of those duplicate work which has already, more or less, been done. I'd love to have the opportunity to not just get the numbers correct, but ensure that it's efficient, too - because efficiency matters perhaps more than you realise.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 9, 2011 at 6:39 am
Hi Chris,
Ok, I'll try attempt a rough table output, I'll omit DocDate unless you specify otherwise.
Table OCRD
CardCode CardName CntctPrsn Phone1 Cellular
c1001Acme CoBob Smith1234 4321
c1002Zepp CoJohn Smith5678 8765
Table oinv
CardCode DocTotal
c1001 5000
c1002 6000
Table orin
CardCode DocTotal
c1001 1000
c1002 500
Does that help or is it too rough?
Edit: The formatting is different in the editor 🙂
September 9, 2011 at 7:04 am
in_beta (9/9/2011)
Hi Chris,Ok, I'll try attempt a rough table output, I'll omit DocDate unless you specify otherwise.
...
You're new here - this is better:
USE tempdb
GO
SET DATEFORMAT MDY
CREATE Table OCRD (CardCode VARCHAR(20), CardName VARCHAR(20), CntctPrsn VARCHAR(20), Phone1 VARCHAR(20),
Cellular VARCHAR(20), SlpCode CHAR(2), GroupCode CHAR(3)
)
INSERT INTO OCRD (CardCode, CardName, CntctPrsn, Phone1, SlpCode, GroupCode)
SELECT 'c1001', 'Acme Co', 'Bob Smith', '1234 4321', '66', '102' UNION ALL
SELECT 'c1002', 'Zepp Co', 'John Smith', '5678 8765', '66', '102'
CREATE Table oinv (CardCode VARCHAR(20), DocTotal INT, DocDate DATETIME)
INSERT INTO oinv (CardCode, DocTotal, DocDate)
SELECT 'c1001', 5000, '2010-01-01 00:00:00.000' UNION ALL
SELECT 'c1002', 6000, '2010-02-01 00:00:00.000'
CREATE Table orin (CardCode VARCHAR(20), DocTotal INT, DocDate DATETIME)
INSERT INTO orin (CardCode, DocTotal, DocDate)
SELECT 'c1001', 1000, '2010-01-01 00:00:00.000' UNION ALL
SELECT 'c1002', 500, '2010-02-01 00:00:00.000'
-----------------------------------------------------------------------------------
SELECT
ocrd.CardCode,
ocrd.CardName,
ocrd.CntctPrsn,
ocrd.Phone1,
ocrd.Cellular,
YearTotal = (ISNULL(oinv.debit,0)-ISNULL(orin.credit,0))/1.1,
Jan2010Total = (ISNULL(moinv.dMTD,0)-ISNULL(morin.cMTD,0))/1.1
FROM OCRD ocrd
LEFT JOIN (
SELECT cardcode, debit = SUM(DocTotal)
FROM oinv
WHERE DocDate BETWEEN '2010-01-01 00:00:00.000' AND '2010-12-31 23:59:59.000'
GROUP BY CardCode
) oinv ON oinv.CardCode = ocrd.CardCode
LEFT JOIN (
SELECT cardcode, dMTD = SUM(DocTotal)
FROM oinv
WHERE DocDate BETWEEN '2010-01-01 00:00:00.000' AND '2010-01-31 23:59:59.000'
GROUP BY CardCode
) moinv ON oinv.CardCode = ocrd.CardCode
LEFT JOIN (
SELECT CardCode, credit = sum(DocTotal)
FROM orin
WHERE DocDate BETWEEN '2010-01-01 00:00:00.000' AND '2010-12-31 23:59:59.000'
GROUP BY CardCode
) orin ON orin.CardCode = ocrd.CardCode
LEFT JOIN (
SELECT cardcode, cMTD = SUM(DocTotal)
FROM orin
WHERE DocDate BETWEEN '2010-01-01 00:00:00.000' AND '2010-01-31 23:59:59.000'
GROUP BY CardCode
) morin ON orin.CardCode = ocrd.CardCode
WHERE (ocrd.SlpCode = '66' AND ocrd.GroupCode = '102')
ORDER BY CardName
-----------------------------------------------------------------------------------------------------
DROP Table OCRD
DROP Table oinv
DROP Table orin
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply