June 17, 2015 at 6:21 pm
I am having the hardest time combining these two select statements into one. Can anyone give me some advice on these?
Thank you!!
Basically i want my results to be like this:
Segment, Revenue (Current Yr), Revenue (Prior Yr)
Group 1, $$$, $$$
Group 2, $$$, $$$
Group 3, $$$, $$$
Other, $$$, $$$
SELECT
case
when Substring(gl_account.id, 5, 3) = 001 then 'Group 1'
when Substring(gl_account.id, 5, 3) = 002 then 'Group 2'
when Substring(gl_account.id, 5, 3) = 003 then 'Group 3'
when Substring(gl_account.id, 5, 3) = 000 then 'Other'
end Segment
,Sum(-amount) 'Revenue (Current Yr)'
FROM gl_account
LEFT OUTER JOIN gl_ledger
ON gl_account.id = gl_ledger.glid
AND ( gl_ledger.transaction_no != 'BBF'
OR gl_ledger.transaction_no IS NULL )
AND (gl_ledger.transaction_date >= CONVERT(DATETIME, @startdate, 102) )
AND (gl_ledger.transaction_date <= CONVERT(DATETIME, @enddate, 102) )
AND gl_ledger.company_id = 'Main',
gl_type
WHERE gl_account.company_id = 'Main'
AND gl_ledger.transaction_date IS NOT NULL
AND Substring(gl_account.id, 8, 4) >= '4000'
AND Substring(gl_account.id, 8, 4) <= '4999'
AND gl_account.type_id = gl_type.type_id
AND gl_type.company_id = 'Main'
GROUP BY Substring(gl_account.id, 5, 3)
order by Substring(gl_account.id, 5, 3)
SELECT
case
when Substring(gl_account.id, 5, 3) = 001 then 'Group 1'
when Substring(gl_account.id, 5, 3) = 002 then 'Group 2'
when Substring(gl_account.id, 5, 3) = 003 then 'Group 3'
when Substring(gl_account.id, 5, 3) = 000 then 'Other'
end Segment
,Sum(-amount) 'Revenue (Prior Yr)'
FROM gl_account
LEFT OUTER JOIN gl_ledger_hist
ON gl_account.id = gl_ledger_hist.glid
AND ( gl_ledger_hist.transaction_no != 'BBF'
OR gl_ledger_hist.transaction_no IS NULL )
AND (gl_ledger_hist.transaction_date >= CONVERT(DATETIME, @startdateprior, 102) )
AND (gl_ledger_hist.transaction_date <= CONVERT(DATETIME, @enddateprior, 102) )
AND gl_ledger_hist.company_id = 'Main',
gl_type
WHERE gl_account.company_id = 'Main'
AND gl_ledger_hist.transaction_date IS NOT NULL
AND Substring(gl_account.id, 8, 4) >= '4000'
AND Substring(gl_account.id, 8, 4) <= '4999'
AND gl_account.type_id = gl_type.type_id
AND gl_type.company_id = 'Main'
GROUP BY Substring(gl_account.id, 5, 3)
order by Substring(gl_account.id, 5, 3)
June 17, 2015 at 7:22 pm
If the data comes from different tables, you have to union the two queries together.
SELECT...
FROM tableA
UNION [ALL]
SELECT ...
FROM tableB;
Why are the history records in another table? Did this come from something miserable like Access?
June 17, 2015 at 7:26 pm
Union will not work.
This will:
SELECT
case
when Substring(gl_account.id, 5, 3) = 001 then 'Group 1'
when Substring(gl_account.id, 5, 3) = 002 then 'Group 2'
when Substring(gl_account.id, 5, 3) = 003 then 'Group 3'
when Substring(gl_account.id, 5, 3) = 000 then 'Other'
end Segment
,Sum(CASE WHEN (gl_ledger.transaction_date >= CONVERT(DATETIME, @startdate, 102) )
AND (gl_ledger.transaction_date <= CONVERT(DATETIME, @enddate, 102) )
THEN Amount
ELSE 0
End) [Revenue (Current Yr)]
,Sum(CASE WHEN (gl_ledger.transaction_date >= CONVERT(DATETIME, @startdateprior, 102) )
AND (gl_ledger.transaction_date <= CONVERT(DATETIME, @enddateprior, 102) )
THEN Amount
ELSE 0
End) [Revenue (Prior Yr)]
FROM gl_account
LEFT OUTER JOIN gl_ledger
ON gl_account.id = gl_ledger.glid
AND ( gl_ledger.transaction_no != 'BBF'
OR gl_ledger.transaction_no IS NULL )
AND (gl_ledger.transaction_date >= CONVERT(DATETIME, @startdateprior, 102) )
AND (gl_ledger.transaction_date <= CONVERT(DATETIME, @enddate, 102) )
AND gl_ledger.company_id = 'Main',
gl_type
WHERE gl_account.company_id = 'Main'
AND gl_ledger.transaction_date IS NOT NULL
AND Substring(gl_account.id, 8, 4) >= '4000'
AND Substring(gl_account.id, 8, 4) <= '4999'
AND gl_account.type_id = gl_type.type_id
AND gl_type.company_id = 'Main'
GROUP BY Substring(gl_account.id, 5, 3)
order by Substring(gl_account.id, 5, 3)
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
June 18, 2015 at 7:40 am
sqlnoob2015 (6/17/2015)
I am having the hardest time combining these two select statements into one. Can anyone give me some advice on these?Thank you!!
Basically i want my results to be like this:
Segment, Revenue (Current Yr), Revenue (Prior Yr)
Group 1, $$$, $$$
Group 2, $$$, $$$
Group 3, $$$, $$$
Other, $$$, $$$
SELECT
case
when Substring(gl_account.id, 5, 3) = 001 then 'Group 1'
when Substring(gl_account.id, 5, 3) = 002 then 'Group 2'
when Substring(gl_account.id, 5, 3) = 003 then 'Group 3'
when Substring(gl_account.id, 5, 3) = 000 then 'Other'
end Segment
,Sum(-amount) 'Revenue (Current Yr)'
FROM gl_account
LEFT OUTER JOIN gl_ledger
ON gl_account.id = gl_ledger.glid
AND ( gl_ledger.transaction_no != 'BBF'
OR gl_ledger.transaction_no IS NULL )
AND (gl_ledger.transaction_date >= CONVERT(DATETIME, @startdate, 102) )
AND (gl_ledger.transaction_date <= CONVERT(DATETIME, @enddate, 102) )
AND gl_ledger.company_id = 'Main',
gl_type
WHERE gl_account.company_id = 'Main'
AND gl_ledger.transaction_date IS NOT NULL
AND Substring(gl_account.id, 8, 4) >= '4000'
AND Substring(gl_account.id, 8, 4) <= '4999'
AND gl_account.type_id = gl_type.type_id
AND gl_type.company_id = 'Main'
GROUP BY Substring(gl_account.id, 5, 3)
order by Substring(gl_account.id, 5, 3)
SELECT
case
when Substring(gl_account.id, 5, 3) = 001 then 'Group 1'
when Substring(gl_account.id, 5, 3) = 002 then 'Group 2'
when Substring(gl_account.id, 5, 3) = 003 then 'Group 3'
when Substring(gl_account.id, 5, 3) = 000 then 'Other'
end Segment
,Sum(-amount) 'Revenue (Prior Yr)'
FROM gl_account
LEFT OUTER JOIN gl_ledger_hist
ON gl_account.id = gl_ledger_hist.glid
AND ( gl_ledger_hist.transaction_no != 'BBF'
OR gl_ledger_hist.transaction_no IS NULL )
AND (gl_ledger_hist.transaction_date >= CONVERT(DATETIME, @startdateprior, 102) )
AND (gl_ledger_hist.transaction_date <= CONVERT(DATETIME, @enddateprior, 102) )
AND gl_ledger_hist.company_id = 'Main',
gl_type
WHERE gl_account.company_id = 'Main'
AND gl_ledger_hist.transaction_date IS NOT NULL
AND Substring(gl_account.id, 8, 4) >= '4000'
AND Substring(gl_account.id, 8, 4) <= '4999'
AND gl_account.type_id = gl_type.type_id
AND gl_type.company_id = 'Main'
GROUP BY Substring(gl_account.id, 5, 3)
order by Substring(gl_account.id, 5, 3)
I'm quite sure what Michael L John posted isn't going to combine data as it fails to take the history table into account. The previous poster to Michael was also incorrect, and a UNION or UNION ALL isn't going to help. Here's a CTE (aka Common Table Expression) query that should do it:
WITH REVENUE_CURRENT_YR AS (
SELECT
CASE SUBSTRING(GLA.id, 5, 3)
WHEN '000' THEN 'Other'
WHEN '001' THEN 'Group 1'
WHEN '002' THEN 'Group 2'
WHEN '003' THEN 'Group 3'
END AS Segment,
SUM(-amount) AS 'Revenue (Current Yr)'
FROM gl_account AS GLA
INNER JOIN gl_type AS GLT
ON GLA.company_id = GLT.company_id
AND GLA.[type_id] = GLT.[type_id]
INNER JOIN gl_ledger AS GLL
ON GLA.id = GLL.glid
AND ISNULL(GLL.transaction_no, 'XXX') <> 'BBF'
AND GLL.transaction_date BETWEEN CONVERT(datetime, @startdate, 102) AND CONVERT(datetime, @enddate, 102)
AND GLL.company_id = 'Main',
WHERE GLA.company_id = 'Main'
AND SUBSTRING(GLA.id, 8, 4) BETWEEN '4000' AND '4999'
GROUP BY
CASE SUBSTRING(GLA.id, 5, 3)
WHEN '000' THEN 'Other'
WHEN '001' THEN 'Group 1'
WHEN '002' THEN 'Group 2'
WHEN '003' THEN 'Group 3'
END
),
REVENUE_PRIOR_YR AS (
SELECT
CASE SUBSTRING(GLA.id, 5, 3)
WHEN '000' THEN 'Other'
WHEN '001' THEN 'Group 1'
WHEN '002' THEN 'Group 2'
WHEN '003' THEN 'Group 3'
END AS Segment,
SUM(-amount) AS 'Revenue (Prior Yr)'
FROM gl_account AS GLA
INNER JOIN gl_type AS GLT
ON GLA.company_id = GLT.company_id
AND GLA.[type_id] = GLT.[type_id]
INNER JOIN gl_ledger_hist AS GLH
ON GLA.id = GLH.glid
AND ISNULL(GLH.transaction_no, 'XXX') <> 'BBF'
AND GLH.transaction_date BETWEEN CONVERT(datetime, @startdate, 102) AND CONVERT(datetime, @enddate, 102)
AND GLH.company_id = 'Main',
WHERE gl_account.company_id = 'Main'
AND SUBSTRING(GLA.id, 8, 4) BETWEEN '4000' AND '4999'
GROUP BY
CASE SUBSTRING(GLA.id, 5, 3)
WHEN '000' THEN 'Other'
WHEN '001' THEN 'Group 1'
WHEN '002' THEN 'Group 2'
WHEN '003' THEN 'Group 3'
END
)
SELECT ISNULL(RCY.Segment, RPY.Segment) AS Segment, RCY.[Revenue (Current Yr)], RPY.[Revenue (Prior Yr)]
FROM REVENUE_CURRENT_YR AS RCY
FULL OUTER JOIN REVENUE_PRIOR_YR AS RPY
ON RCY.Segment = RPY.Segment
ORDER BY ISNULL(RCY.Segment, RPY.Segment);
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 18, 2015 at 7:44 am
A couple of additional notes:
1.) Your LEFT OUTER JOINs are getting converted to INNER JOINs because of your WHERE clause seeking a NOT NULL value for the date from the table being joined, so you might as well have the INNER JOIN to begin with, and you can then remove that from the WHERE clause because you already guarantee a NOT NULL value from the JOIN.
2.) You use a comma to join a table that you end up with an INNER JOIN to, so I just put the WHERE CLAUSE stuff into the join for it.
3.) I simplified some of the coding to use BETWEEN.
4.) I formatted the query to help make it more readable.
Enjoy...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 18, 2015 at 8:40 am
sgmunson (6/18/2015)
sqlnoob2015 (6/17/2015)
I am having the hardest time combining these two select statements into one. Can anyone give me some advice on these?Thank you!!
Basically i want my results to be like this:
Segment, Revenue (Current Yr), Revenue (Prior Yr)
Group 1, $$$, $$$
Group 2, $$$, $$$
Group 3, $$$, $$$
Other, $$$, $$$
SELECT
case
when Substring(gl_account.id, 5, 3) = 001 then 'Group 1'
when Substring(gl_account.id, 5, 3) = 002 then 'Group 2'
when Substring(gl_account.id, 5, 3) = 003 then 'Group 3'
when Substring(gl_account.id, 5, 3) = 000 then 'Other'
end Segment
,Sum(-amount) 'Revenue (Current Yr)'
FROM gl_account
LEFT OUTER JOIN gl_ledger
ON gl_account.id = gl_ledger.glid
AND ( gl_ledger.transaction_no != 'BBF'
OR gl_ledger.transaction_no IS NULL )
AND (gl_ledger.transaction_date >= CONVERT(DATETIME, @startdate, 102) )
AND (gl_ledger.transaction_date <= CONVERT(DATETIME, @enddate, 102) )
AND gl_ledger.company_id = 'Main',
gl_type
WHERE gl_account.company_id = 'Main'
AND gl_ledger.transaction_date IS NOT NULL
AND Substring(gl_account.id, 8, 4) >= '4000'
AND Substring(gl_account.id, 8, 4) <= '4999'
AND gl_account.type_id = gl_type.type_id
AND gl_type.company_id = 'Main'
GROUP BY Substring(gl_account.id, 5, 3)
order by Substring(gl_account.id, 5, 3)
SELECT
case
when Substring(gl_account.id, 5, 3) = 001 then 'Group 1'
when Substring(gl_account.id, 5, 3) = 002 then 'Group 2'
when Substring(gl_account.id, 5, 3) = 003 then 'Group 3'
when Substring(gl_account.id, 5, 3) = 000 then 'Other'
end Segment
,Sum(-amount) 'Revenue (Prior Yr)'
FROM gl_account
LEFT OUTER JOIN gl_ledger_hist
ON gl_account.id = gl_ledger_hist.glid
AND ( gl_ledger_hist.transaction_no != 'BBF'
OR gl_ledger_hist.transaction_no IS NULL )
AND (gl_ledger_hist.transaction_date >= CONVERT(DATETIME, @startdateprior, 102) )
AND (gl_ledger_hist.transaction_date <= CONVERT(DATETIME, @enddateprior, 102) )
AND gl_ledger_hist.company_id = 'Main',
gl_type
WHERE gl_account.company_id = 'Main'
AND gl_ledger_hist.transaction_date IS NOT NULL
AND Substring(gl_account.id, 8, 4) >= '4000'
AND Substring(gl_account.id, 8, 4) <= '4999'
AND gl_account.type_id = gl_type.type_id
AND gl_type.company_id = 'Main'
GROUP BY Substring(gl_account.id, 5, 3)
order by Substring(gl_account.id, 5, 3)
I'm quite sure what Michael L John posted isn't going to combine data as it fails to take the history table into account. The previous poster to Michael was also incorrect, and a UNION or UNION ALL isn't going to help. Here's a CTE (aka Common Table Expression) query that should do it:
WITH REVENUE_CURRENT_YR AS (
SELECT
CASE SUBSTRING(GLA.id, 5, 3)
WHEN '000' THEN 'Other'
WHEN '001' THEN 'Group 1'
WHEN '002' THEN 'Group 2'
WHEN '003' THEN 'Group 3'
END AS Segment,
SUM(-amount) AS 'Revenue (Current Yr)'
FROM gl_account AS GLA
INNER JOIN gl_type AS GLT
ON GLA.company_id = GLT.company_id
AND GLA.[type_id] = GLT.[type_id]
INNER JOIN gl_ledger AS GLL
ON GLA.id = GLL.glid
AND ISNULL(GLL.transaction_no, 'XXX') <> 'BBF'
AND GLL.transaction_date BETWEEN CONVERT(datetime, @startdate, 102) AND CONVERT(datetime, @enddate, 102)
AND GLL.company_id = 'Main',
WHERE GLA.company_id = 'Main'
AND SUBSTRING(GLA.id, 8, 4) BETWEEN '4000' AND '4999'
GROUP BY
CASE SUBSTRING(GLA.id, 5, 3)
WHEN '000' THEN 'Other'
WHEN '001' THEN 'Group 1'
WHEN '002' THEN 'Group 2'
WHEN '003' THEN 'Group 3'
END
),
REVENUE_PRIOR_YR AS (
SELECT
CASE SUBSTRING(GLA.id, 5, 3)
WHEN '000' THEN 'Other'
WHEN '001' THEN 'Group 1'
WHEN '002' THEN 'Group 2'
WHEN '003' THEN 'Group 3'
END AS Segment,
SUM(-amount) AS 'Revenue (Prior Yr)'
FROM gl_account AS GLA
INNER JOIN gl_type AS GLT
ON GLA.company_id = GLT.company_id
AND GLA.[type_id] = GLT.[type_id]
INNER JOIN gl_ledger_hist AS GLH
ON GLA.id = GLH.glid
AND ISNULL(GLH.transaction_no, 'XXX') <> 'BBF'
AND GLH.transaction_date BETWEEN CONVERT(datetime, @startdate, 102) AND CONVERT(datetime, @enddate, 102)
AND GLH.company_id = 'Main',
WHERE gl_account.company_id = 'Main'
AND SUBSTRING(GLA.id, 8, 4) BETWEEN '4000' AND '4999'
GROUP BY
CASE SUBSTRING(GLA.id, 5, 3)
WHEN '000' THEN 'Other'
WHEN '001' THEN 'Group 1'
WHEN '002' THEN 'Group 2'
WHEN '003' THEN 'Group 3'
END
)
SELECT ISNULL(RCY.Segment, RPY.Segment) AS Segment, RCY.[Revenue (Current Yr)], RPY.[Revenue (Prior Yr)]
FROM REVENUE_CURRENT_YR AS RCY
FULL OUTER JOIN REVENUE_PRIOR_YR AS RPY
ON RCY.Segment = RPY.Segment
ORDER BY ISNULL(RCY.Segment, RPY.Segment);
I missed the differences in the tables. I stand corrected. My code will not work as is.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
June 18, 2015 at 9:25 am
This worked perfect! I didnt realize you can abbreviate the table names for ease of use later. That really helps!
This got me exactly what i needed though!
Thanks again!!
June 18, 2015 at 11:18 am
sgmunson (6/18/2015)
A couple of additional notes:3.) I simplified some of the coding to use BETWEEN.
Enjoy...
You probably do not want to use BETWEEN for a datetime field.
If you are passing in a date without the time portion, you will not get all of the records for the ending date.
If the date field contains, as an example:
12/31/2015 01:00:000,
then an ending date of '12/31/2015' will not include this record.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
June 18, 2015 at 12:03 pm
^^ Good point. I thought about that for other operational reports but for GL reporting, it will work fine.
Thanks
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply