Mixing
How can I Group By a query result where a table is joined on itself?
I have a mess of a report, that is build up in spaghetti coding, doing 100's of SQL request, and bogging down the clients. I'm in the process of re-writing it, but my SQL feels like a mess.
I feel like I'm missing a key operation ...
The report shows the number of invoice, the sum of them, grouped by invoice type and filter based on the current month, the previous month, and the cumulative sum of the current year, and the previous year.
for simplicity, let's say all invoice are worth 100$ and I have 10 invoice of each type per area (outside of December) in 2017 and 2018.
So if I get the report for December, I want something like this :
type || description || location || 2017-DEC || 2018-DEC || 2017-CUMUL || 2018-CUMUL
-------------------------------------------------------------------------------------------
type A || descr. 'a' || town 1 || 1 || 3 || 1100$ || 1300$
type B || descr. 'b || town 1 || 2 || 4 || 1200$ || 1400$
Anyone can shed a light on what I'm doing wrong here ? the coalesce feels like cleaning up the mess made by wrong use of full outer join ...
here is my query so far :
select a.type
, a.desc
, a.area
, sum(a.prec_InvoiceNumber)
, sum(a.InvoiceNumber)
, sum(a.yprec_amount)
, sum(a.ycurr_amount)
from (
SELECT coalesce(CURR.type,prec.type, yprec.type,YCurr.type) as type
, coalesce(CURR.desc,prec.desc, yprec.desc,YCurr.desc) as desc
, coalesce(CURR.area,prec.area, yprec.area,YCurr.area) as area
, isnull(PREC.InvoiceNumber,0) as prec_InvoiceNumber
, isnull(CURR.InvoiceNumber,0) as InvoiceNumber
, isnull(YPREC.amount,0) as YPREC_amount
, isnull(YCurr.amount,0) as YCurr_amount
from
(
SELECT invoice.type
, invoiceType.desc
, locationarea.desc as area
, Count(INVOICE.IDNumber) as InvoiceNumber,
, SUM(Invoice.amount) as amount
FROM Invoice
left join invoicteype on invoice.type = invoicetype.type
left join locationarea on locationarea.Id = invoice.locationID
Where Invoice.date between '2017-12-01' and '2017-12-31'
and invoicetype.type in ('a','b')
and locationarea.ID = 1
group by Invoice.type, locationarea.desc, invoiceType.desc
) PREC
FULL OUTER JOIN (
SELECT invoice.type
, invoiceType.desc
, locationarea.desc as area
, Count(INVOICE.IDNumber) as InvoiceNumber,
, SUM(Invoice.amount) as amount
FROM Invoice
left join invoicteype on invoice.type = invoicetype.type
left join locationarea on locationarea.Id = invoice.locationID
Where Invoice.date between '2018-12-01' and '2018-12-31'
and invoicetype.type in ('a','b')
and locationarea.ID = 1
group by Invoice.type, locationarea.desc, invoiceType.desc
) CURR ON PREC.type= CURR.type AND PREC.area= CURR.area AND PREC.desc= CURR.desc
FULL OUTER JOIN (
SELECT invoice.type
, invoiceType.desc
, locationarea.desc as area
, Count(INVOICE.IDNumber) as InvoiceNumber,
, SUM(Invoice.amount) as amount
FROM Invoice
left join invoicteype on invoice.type = invoicetype.type
left join locationarea on locationarea.Id = invoice.locationID
Where Invoice.date between '2017-01-01' and '2017-12-31'
and invoicetype.type in ('a','b')
and locationarea.ID = 1
group by Invoice.type, locationarea.desc, invoiceType.desc
) YPREC ON CURR.type= YPREC.type AND CURR.area= YPREC.area AND CURR.desc= YPREC.desc
FULL OUTER JOIN (
SELECT invoice.type
, invoiceType.desc
, locationarea.desc as area
, Count(INVOICE.IDNumber) as InvoiceNumber,
, SUM(Invoice.amount) as amount
FROM Invoice
left join invoicteype on invoice.type = invoicetype.type
left join locationarea on locationarea.Id = invoice.locationID
Where Invoice.date between '2018-01-01' and '2018-12-31'
and invoicetype.type in ('a','b')
and locationarea.ID = 1
group by Invoice.type, locationarea.desc, invoiceType.desc
) YCURR ON CURR.type= YCURR .type AND CURR.area= YCURR.area AND CURR.desc= YCURR.desc
) a
group by a.type, a.desc, a.area
EDIT :
I would be happy to get rid of the last 'select' and all the coalesce, I'm sure there's a cleaner way to do it.
Since the way I created the full outer join, they all join up on the 1st sub query ('PREC'), so if an invoice type exists in 2018, but not in 2017, I have some messed up result ('hence' the clunky use of coalesce and the 'select a.' around the main 'select' and the full outer join subqueries )
in my head there should be a simpler way to write it along the line of :
select type, descr, location, count(a.id), count(b.Id), sum(c.value), sum(d.value)
from TypeTable
join locationtable
join descriptiontable
join invoice a
join invoice b
join invoice c
join invoice d
where a.date between U and V
and b.date between W and X
and c.date between U and Y
and d.date between W and z
and type = 'a'
and description = 'descr a'
and location = 'town 1'
without needing to add all those coalesce and sub-queries, but I can't find the way to do it.
tsql
asked Jan 2 at 17:26
Thierry Savard SaucierThierry Savard Saucier
2971214
add a comment |
I have a mess of a report, that is build up in spaghetti coding, doing 100's of SQL request, and bogging down the clients. I'm in the process of re-writing it, but my SQL feels like a mess.
I feel like I'm missing a key operation ...
The report shows the number of invoice, the sum of them, grouped by invoice type and filter based on the current month, the previous month, and the cumulative sum of the current year, and the previous year.
for simplicity, let's say all invoice are worth 100$ and I have 10 invoice of each type per area (outside of December) in 2017 and 2018.
So if I get the report for December, I want something like this :
type || description || location || 2017-DEC || 2018-DEC || 2017-CUMUL || 2018-CUMUL
-------------------------------------------------------------------------------------------
type A || descr. 'a' || town 1 || 1 || 3 || 1100$ || 1300$
type B || descr. 'b || town 1 || 2 || 4 || 1200$ || 1400$
Anyone can shed a light on what I'm doing wrong here ? the coalesce feels like cleaning up the mess made by wrong use of full outer join ...
here is my query so far :
select a.type
, a.desc
, a.area
, sum(a.prec_InvoiceNumber)
, sum(a.InvoiceNumber)
, sum(a.yprec_amount)
, sum(a.ycurr_amount)
from (
SELECT coalesce(CURR.type,prec.type, yprec.type,YCurr.type) as type
, coalesce(CURR.desc,prec.desc, yprec.desc,YCurr.desc) as desc
, coalesce(CURR.area,prec.area, yprec.area,YCurr.area) as area
, isnull(PREC.InvoiceNumber,0) as prec_InvoiceNumber
, isnull(CURR.InvoiceNumber,0) as InvoiceNumber
, isnull(YPREC.amount,0) as YPREC_amount
, isnull(YCurr.amount,0) as YCurr_amount
from
(
SELECT invoice.type
, invoiceType.desc
, locationarea.desc as area
, Count(INVOICE.IDNumber) as InvoiceNumber,
, SUM(Invoice.amount) as amount
FROM Invoice
left join invoicteype on invoice.type = invoicetype.type
left join locationarea on locationarea.Id = invoice.locationID
Where Invoice.date between '2017-12-01' and '2017-12-31'
and invoicetype.type in ('a','b')
and locationarea.ID = 1
group by Invoice.type, locationarea.desc, invoiceType.desc
) PREC
FULL OUTER JOIN (
SELECT invoice.type
, invoiceType.desc
, locationarea.desc as area
, Count(INVOICE.IDNumber) as InvoiceNumber,
, SUM(Invoice.amount) as amount
FROM Invoice
left join invoicteype on invoice.type = invoicetype.type
left join locationarea on locationarea.Id = invoice.locationID
Where Invoice.date between '2018-12-01' and '2018-12-31'
and invoicetype.type in ('a','b')
and locationarea.ID = 1
group by Invoice.type, locationarea.desc, invoiceType.desc
) CURR ON PREC.type= CURR.type AND PREC.area= CURR.area AND PREC.desc= CURR.desc
FULL OUTER JOIN (
SELECT invoice.type
, invoiceType.desc
, locationarea.desc as area
, Count(INVOICE.IDNumber) as InvoiceNumber,
, SUM(Invoice.amount) as amount
FROM Invoice
left join invoicteype on invoice.type = invoicetype.type
left join locationarea on locationarea.Id = invoice.locationID
Where Invoice.date between '2017-01-01' and '2017-12-31'
and invoicetype.type in ('a','b')
and locationarea.ID = 1
group by Invoice.type, locationarea.desc, invoiceType.desc
) YPREC ON CURR.type= YPREC.type AND CURR.area= YPREC.area AND CURR.desc= YPREC.desc
FULL OUTER JOIN (
SELECT invoice.type
, invoiceType.desc
, locationarea.desc as area
, Count(INVOICE.IDNumber) as InvoiceNumber,
, SUM(Invoice.amount) as amount
FROM Invoice
left join invoicteype on invoice.type = invoicetype.type
left join locationarea on locationarea.Id = invoice.locationID
Where Invoice.date between '2018-01-01' and '2018-12-31'
and invoicetype.type in ('a','b')
and locationarea.ID = 1
group by Invoice.type, locationarea.desc, invoiceType.desc
) YCURR ON CURR.type= YCURR .type AND CURR.area= YCURR.area AND CURR.desc= YCURR.desc
) a
group by a.type, a.desc, a.area
EDIT :
I would be happy to get rid of the last 'select' and all the coalesce, I'm sure there's a cleaner way to do it.
Since the way I created the full outer join, they all join up on the 1st sub query ('PREC'), so if an invoice type exists in 2018, but not in 2017, I have some messed up result ('hence' the clunky use of coalesce and the 'select a.' around the main 'select' and the full outer join subqueries )
in my head there should be a simpler way to write it along the line of :
select type, descr, location, count(a.id), count(b.Id), sum(c.value), sum(d.value)
from TypeTable
join locationtable
join descriptiontable
join invoice a
join invoice b
join invoice c
join invoice d
where a.date between U and V
and b.date between W and X
and c.date between U and Y
and d.date between W and z
and type = 'a'
and description = 'descr a'
and location = 'town 1'
without needing to add all those coalesce and sub-queries, but I can't find the way to do it.
tsql
asked Jan 2 at 17:26
Thierry Savard SaucierThierry Savard Saucier
2971214
It might help to show an example of the output you are getting that's wrong too.
– BlueMonkMN
Jan 2 at 17:33
the output isnt bad ... but that query is over simplified ... I have 7 or 8 table joined to get all of the data imput for a single invoice ( imagine you want that report by item on the invoice, and regroup by item part of a specific group(1 row for pencils, 1 for paper, etc )) ... so the whole query takes 100's of more lines ... i just wish I had a clean example to start working with. each added joined table is include at least 4 more times, each with specific conditions, which makes the number of resulting parameters kinda hard to follow up.
– Thierry Savard Saucier
Jan 2 at 17:35
add a comment |
I have a mess of a report, that is build up in spaghetti coding, doing 100's of SQL request, and bogging down the clients. I'm in the process of re-writing it, but my SQL feels like a mess.
I feel like I'm missing a key operation ...
The report shows the number of invoice, the sum of them, grouped by invoice type and filter based on the current month, the previous month, and the cumulative sum of the current year, and the previous year.
for simplicity, let's say all invoice are worth 100$ and I have 10 invoice of each type per area (outside of December) in 2017 and 2018.
So if I get the report for December, I want something like this :
type || description || location || 2017-DEC || 2018-DEC || 2017-CUMUL || 2018-CUMUL
-------------------------------------------------------------------------------------------
type A || descr. 'a' || town 1 || 1 || 3 || 1100$ || 1300$
type B || descr. 'b || town 1 || 2 || 4 || 1200$ || 1400$
Anyone can shed a light on what I'm doing wrong here ? the coalesce feels like cleaning up the mess made by wrong use of full outer join ...
here is my query so far :
select a.type
, a.desc
, a.area
, sum(a.prec_InvoiceNumber)
, sum(a.InvoiceNumber)
, sum(a.yprec_amount)
, sum(a.ycurr_amount)
from (
SELECT coalesce(CURR.type,prec.type, yprec.type,YCurr.type) as type
, coalesce(CURR.desc,prec.desc, yprec.desc,YCurr.desc) as desc
, coalesce(CURR.area,prec.area, yprec.area,YCurr.area) as area
, isnull(PREC.InvoiceNumber,0) as prec_InvoiceNumber
, isnull(CURR.InvoiceNumber,0) as InvoiceNumber
, isnull(YPREC.amount,0) as YPREC_amount
, isnull(YCurr.amount,0) as YCurr_amount
from
(
SELECT invoice.type
, invoiceType.desc
, locationarea.desc as area
, Count(INVOICE.IDNumber) as InvoiceNumber,
, SUM(Invoice.amount) as amount
FROM Invoice
left join invoicteype on invoice.type = invoicetype.type
left join locationarea on locationarea.Id = invoice.locationID
Where Invoice.date between '2017-12-01' and '2017-12-31'
and invoicetype.type in ('a','b')
and locationarea.ID = 1
group by Invoice.type, locationarea.desc, invoiceType.desc
) PREC
FULL OUTER JOIN (
SELECT invoice.type
, invoiceType.desc
, locationarea.desc as area
, Count(INVOICE.IDNumber) as InvoiceNumber,
, SUM(Invoice.amount) as amount
FROM Invoice
left join invoicteype on invoice.type = invoicetype.type
left join locationarea on locationarea.Id = invoice.locationID
Where Invoice.date between '2018-12-01' and '2018-12-31'
and invoicetype.type in ('a','b')
and locationarea.ID = 1
group by Invoice.type, locationarea.desc, invoiceType.desc
) CURR ON PREC.type= CURR.type AND PREC.area= CURR.area AND PREC.desc= CURR.desc
FULL OUTER JOIN (
SELECT invoice.type
, invoiceType.desc
, locationarea.desc as area
, Count(INVOICE.IDNumber) as InvoiceNumber,
, SUM(Invoice.amount) as amount
FROM Invoice
left join invoicteype on invoice.type = invoicetype.type
left join locationarea on locationarea.Id = invoice.locationID
Where Invoice.date between '2017-01-01' and '2017-12-31'
and invoicetype.type in ('a','b')
and locationarea.ID = 1
group by Invoice.type, locationarea.desc, invoiceType.desc
) YPREC ON CURR.type= YPREC.type AND CURR.area= YPREC.area AND CURR.desc= YPREC.desc
FULL OUTER JOIN (
SELECT invoice.type
, invoiceType.desc
, locationarea.desc as area
, Count(INVOICE.IDNumber) as InvoiceNumber,
, SUM(Invoice.amount) as amount
FROM Invoice
left join invoicteype on invoice.type = invoicetype.type
left join locationarea on locationarea.Id = invoice.locationID
Where Invoice.date between '2018-01-01' and '2018-12-31'
and invoicetype.type in ('a','b')
and locationarea.ID = 1
group by Invoice.type, locationarea.desc, invoiceType.desc
) YCURR ON CURR.type= YCURR .type AND CURR.area= YCURR.area AND CURR.desc= YCURR.desc
) a
group by a.type, a.desc, a.area
EDIT :
I would be happy to get rid of the last 'select' and all the coalesce, I'm sure there's a cleaner way to do it.
Since the way I created the full outer join, they all join up on the 1st sub query ('PREC'), so if an invoice type exists in 2018, but not in 2017, I have some messed up result ('hence' the clunky use of coalesce and the 'select a.' around the main 'select' and the full outer join subqueries )
in my head there should be a simpler way to write it along the line of :
select type, descr, location, count(a.id), count(b.Id), sum(c.value), sum(d.value)
from TypeTable
join locationtable
join descriptiontable
join invoice a
join invoice b
join invoice c
join invoice d
where a.date between U and V
and b.date between W and X
and c.date between U and Y
and d.date between W and z
and type = 'a'
and description = 'descr a'
and location = 'town 1'
without needing to add all those coalesce and sub-queries, but I can't find the way to do it.
tsql
asked Jan 2 at 17:26
Thierry Savard SaucierThierry Savard Saucier
2971214
I have a mess of a report, that is build up in spaghetti coding, doing 100's of SQL request, and bogging down the clients. I'm in the process of re-writing it, but my SQL feels like a mess.
I feel like I'm missing a key operation ...
The report shows the number of invoice, the sum of them, grouped by invoice type and filter based on the current month, the previous month, and the cumulative sum of the current year, and the previous year.
for simplicity, let's say all invoice are worth 100$ and I have 10 invoice of each type per area (outside of December) in 2017 and 2018.
So if I get the report for December, I want something like this :
type || description || location || 2017-DEC || 2018-DEC || 2017-CUMUL || 2018-CUMUL
-------------------------------------------------------------------------------------------
type A || descr. 'a' || town 1 || 1 || 3 || 1100$ || 1300$
type B || descr. 'b || town 1 || 2 || 4 || 1200$ || 1400$
Anyone can shed a light on what I'm doing wrong here ? the coalesce feels like cleaning up the mess made by wrong use of full outer join ...
here is my query so far :
select a.type
, a.desc
, a.area
, sum(a.prec_InvoiceNumber)
, sum(a.InvoiceNumber)
, sum(a.yprec_amount)
, sum(a.ycurr_amount)
from (
SELECT coalesce(CURR.type,prec.type, yprec.type,YCurr.type) as type
, coalesce(CURR.desc,prec.desc, yprec.desc,YCurr.desc) as desc
, coalesce(CURR.area,prec.area, yprec.area,YCurr.area) as area
, isnull(PREC.InvoiceNumber,0) as prec_InvoiceNumber
, isnull(CURR.InvoiceNumber,0) as InvoiceNumber
, isnull(YPREC.amount,0) as YPREC_amount
, isnull(YCurr.amount,0) as YCurr_amount
from
(
SELECT invoice.type
, invoiceType.desc
, locationarea.desc as area
, Count(INVOICE.IDNumber) as InvoiceNumber,
, SUM(Invoice.amount) as amount
FROM Invoice
left join invoicteype on invoice.type = invoicetype.type
left join locationarea on locationarea.Id = invoice.locationID
Where Invoice.date between '2017-12-01' and '2017-12-31'
and invoicetype.type in ('a','b')
and locationarea.ID = 1
group by Invoice.type, locationarea.desc, invoiceType.desc
) PREC
FULL OUTER JOIN (
SELECT invoice.type
, invoiceType.desc
, locationarea.desc as area
, Count(INVOICE.IDNumber) as InvoiceNumber,
, SUM(Invoice.amount) as amount
FROM Invoice
left join invoicteype on invoice.type = invoicetype.type
left join locationarea on locationarea.Id = invoice.locationID
Where Invoice.date between '2018-12-01' and '2018-12-31'
and invoicetype.type in ('a','b')
and locationarea.ID = 1
group by Invoice.type, locationarea.desc, invoiceType.desc
) CURR ON PREC.type= CURR.type AND PREC.area= CURR.area AND PREC.desc= CURR.desc
FULL OUTER JOIN (
SELECT invoice.type
, invoiceType.desc
, locationarea.desc as area
, Count(INVOICE.IDNumber) as InvoiceNumber,
, SUM(Invoice.amount) as amount
FROM Invoice
left join invoicteype on invoice.type = invoicetype.type
left join locationarea on locationarea.Id = invoice.locationID
Where Invoice.date between '2017-01-01' and '2017-12-31'
and invoicetype.type in ('a','b')
and locationarea.ID = 1
group by Invoice.type, locationarea.desc, invoiceType.desc
) YPREC ON CURR.type= YPREC.type AND CURR.area= YPREC.area AND CURR.desc= YPREC.desc
FULL OUTER JOIN (
SELECT invoice.type
, invoiceType.desc
, locationarea.desc as area
, Count(INVOICE.IDNumber) as InvoiceNumber,
, SUM(Invoice.amount) as amount
FROM Invoice
left join invoicteype on invoice.type = invoicetype.type
left join locationarea on locationarea.Id = invoice.locationID
Where Invoice.date between '2018-01-01' and '2018-12-31'
and invoicetype.type in ('a','b')
and locationarea.ID = 1
group by Invoice.type, locationarea.desc, invoiceType.desc
) YCURR ON CURR.type= YCURR .type AND CURR.area= YCURR.area AND CURR.desc= YCURR.desc
) a
group by a.type, a.desc, a.area
EDIT :
I would be happy to get rid of the last 'select' and all the coalesce, I'm sure there's a cleaner way to do it.
Since the way I created the full outer join, they all join up on the 1st sub query ('PREC'), so if an invoice type exists in 2018, but not in 2017, I have some messed up result ('hence' the clunky use of coalesce and the 'select a.' around the main 'select' and the full outer join subqueries )
in my head there should be a simpler way to write it along the line of :
select type, descr, location, count(a.id), count(b.Id), sum(c.value), sum(d.value)
from TypeTable
join locationtable
join descriptiontable
join invoice a
join invoice b
join invoice c
join invoice d
where a.date between U and V
and b.date between W and X
and c.date between U and Y
and d.date between W and z
and type = 'a'
and description = 'descr a'
and location = 'town 1'
without needing to add all those coalesce and sub-queries, but I can't find the way to do it.
tsql
tsql
asked Jan 2 at 17:26
Thierry Savard SaucierThierry Savard Saucier
2971214
asked Jan 2 at 17:26
Thierry Savard SaucierThierry Savard Saucier
2971214
edited Jan 2 at 18:12
Thierry Savard Saucier
asked Jan 2 at 17:26
Thierry Savard SaucierThierry Savard Saucier
2971214
asked Jan 2 at 17:26
Thierry Savard SaucierThierry Savard Saucier
2971214
asked Jan 2 at 17:26
Thierry Savard SaucierThierry Savard Saucier
2971214
2971214
It might help to show an example of the output you are getting that's wrong too.
– BlueMonkMN
Jan 2 at 17:33
the output isnt bad ... but that query is over simplified ... I have 7 or 8 table joined to get all of the data imput for a single invoice ( imagine you want that report by item on the invoice, and regroup by item part of a specific group(1 row for pencils, 1 for paper, etc )) ... so the whole query takes 100's of more lines ... i just wish I had a clean example to start working with. each added joined table is include at least 4 more times, each with specific conditions, which makes the number of resulting parameters kinda hard to follow up.
– Thierry Savard Saucier
Jan 2 at 17:35
add a comment |
It might help to show an example of the output you are getting that's wrong too.
– BlueMonkMN
Jan 2 at 17:33
the output isnt bad ... but that query is over simplified ... I have 7 or 8 table joined to get all of the data imput for a single invoice ( imagine you want that report by item on the invoice, and regroup by item part of a specific group(1 row for pencils, 1 for paper, etc )) ... so the whole query takes 100's of more lines ... i just wish I had a clean example to start working with. each added joined table is include at least 4 more times, each with specific conditions, which makes the number of resulting parameters kinda hard to follow up.
– Thierry Savard Saucier
Jan 2 at 17:35
It might help to show an example of the output you are getting that's wrong too.
– BlueMonkMN
Jan 2 at 17:33
It might help to show an example of the output you are getting that's wrong too.
– BlueMonkMN
Jan 2 at 17:33
the output isnt bad ... but that query is over simplified ... I have 7 or 8 table joined to get all of the data imput for a single invoice ( imagine you want that report by item on the invoice, and regroup by item part of a specific group(1 row for pencils, 1 for paper, etc )) ... so the whole query takes 100's of more lines ... i just wish I had a clean example to start working with. each added joined table is include at least 4 more times, each with specific conditions, which makes the number of resulting parameters kinda hard to follow up.
– Thierry Savard Saucier
Jan 2 at 17:35
the output isnt bad ... but that query is over simplified ... I have 7 or 8 table joined to get all of the data imput for a single invoice ( imagine you want that report by item on the invoice, and regroup by item part of a specific group(1 row for pencils, 1 for paper, etc )) ... so the whole query takes 100's of more lines ... i just wish I had a clean example to start working with. each added joined table is include at least 4 more times, each with specific conditions, which makes the number of resulting parameters kinda hard to follow up.
– Thierry Savard Saucier
Jan 2 at 17:35
add a comment |
2 Answers
2
active
oldest
votes
It looks like the main difference you are dealing with in the different join sub-queries is the date range if I understand correctly. In order to be able to merge results from a single date range even if the specific dates are different, you could try using a CASE expression to return the same values for all rows within the same date range. For example, you could use a CASE expression to return the beginning of the date range for any date within the range. Or if you don't care about the actual date value, but just want to sort it into separate groups, assign any number.
SELECT a.description, invoice_sum
FROM (
SELECT description, sum(a.InvoiceNumber) invoice_sum,
CASE WHEN date between U and V then 1
WHEN date between W and X then 2
WHEN date between U and Y then 3
WHEN date between W and Z then 4
ELSE 5 END dategroup
FROM Invoice
) a
GROUP BY a.dategroup, a.description
answered Jan 2 at 17:42
BlueMonkMNBlueMonkMN
18.8k656120
Yeah, the date range is the only different parameters. I'm not sure how using a case to get a specific value will help me, can you show a simple exemple (english isnt my main language ) ?
– Thierry Savard Saucier
Jan 2 at 17:49
@ThierrySavardSaucier Does that help?
– BlueMonkMN
Jan 3 at 13:53
wait, scratch that ... I guess I could make a between U and V and not between W and Z, and just add the 2 sum for the cumul ... I'll go try it out.
– Thierry Savard Saucier
Jan 5 at 5:17
Alright, it worked out nicely. seems alot cleaner now too, thanks.
– Thierry Savard Saucier
Jan 5 at 6:57
add a comment |
try this...
DECLARE @monthtoview int
SET @monthtoview=12
;WITH acummyear AS (
SELECT invoice.type
, locationID as area
-- , INVOICE.IDNumber as InvoiceNumber
,year( dbo.Invoice.[date]) AS [year]
,month( dbo.Invoice.[date]) AS [month]
, SUM(Invoice.amount) OVER (PARTITION BY dbo.Invoice.type, year( dbo.Invoice.[date]) ) as amountyear
,Invoice.amount AS amountmonth
FROM Invoice
Where Invoice.date> '2016-12-31' and Invoice.date<= '2018-12-31' -- years 2017 and 2018
AND locationID = 1
AND type IN (1,2)
)
SELECT type,a.area,a.[year],a.[month]
,count(a.type) as invoicecount
,max(a.amountyear) AS amountperyear
,sum(a.amountmonth) AS amountpermonth
from acummyear a --join here your type and location tables
WHERE a.[month]=@monthtoview
GROUP BY type,a.area,a.[year],a.[month]
but in your report(ssrs) use a matrix to display the values, your rows will be the type and location and your columns... one for year(20XX-CUMUL)with amountperyear in the value and another one for year/month(20XX-DEC)with invoicecount in the value, remove the "sum" added to the values in the cell.
answered Jan 4 at 1:52
user2391537user2391537
132
If not using this framework of mine and being purely in sql, this would most likely have worked. I need to work more with CTE and learnt them for good. I've given my answer to the guy who answered me 1st though, thanks for the nice example.
– Thierry Savard Saucier
Jan 5 at 5:31
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54010627%2fhow-can-i-group-by-a-query-result-where-a-table-is-joined-on-itself%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
It looks like the main difference you are dealing with in the different join sub-queries is the date range if I understand correctly. In order to be able to merge results from a single date range even if the specific dates are different, you could try using a CASE expression to return the same values for all rows within the same date range. For example, you could use a CASE expression to return the beginning of the date range for any date within the range. Or if you don't care about the actual date value, but just want to sort it into separate groups, assign any number.
SELECT a.description, invoice_sum
FROM (
SELECT description, sum(a.InvoiceNumber) invoice_sum,
CASE WHEN date between U and V then 1
WHEN date between W and X then 2
WHEN date between U and Y then 3
WHEN date between W and Z then 4
ELSE 5 END dategroup
FROM Invoice
) a
GROUP BY a.dategroup, a.description
answered Jan 2 at 17:42
BlueMonkMNBlueMonkMN
18.8k656120
Yeah, the date range is the only different parameters. I'm not sure how using a case to get a specific value will help me, can you show a simple exemple (english isnt my main language ) ?
– Thierry Savard Saucier
Jan 2 at 17:49
@ThierrySavardSaucier Does that help?
– BlueMonkMN
Jan 3 at 13:53
wait, scratch that ... I guess I could make a between U and V and not between W and Z, and just add the 2 sum for the cumul ... I'll go try it out.
– Thierry Savard Saucier
Jan 5 at 5:17
Alright, it worked out nicely. seems alot cleaner now too, thanks.
– Thierry Savard Saucier
Jan 5 at 6:57
add a comment |
It looks like the main difference you are dealing with in the different join sub-queries is the date range if I understand correctly. In order to be able to merge results from a single date range even if the specific dates are different, you could try using a CASE expression to return the same values for all rows within the same date range. For example, you could use a CASE expression to return the beginning of the date range for any date within the range. Or if you don't care about the actual date value, but just want to sort it into separate groups, assign any number.
SELECT a.description, invoice_sum
FROM (
SELECT description, sum(a.InvoiceNumber) invoice_sum,
CASE WHEN date between U and V then 1
WHEN date between W and X then 2
WHEN date between U and Y then 3
WHEN date between W and Z then 4
ELSE 5 END dategroup
FROM Invoice
) a
GROUP BY a.dategroup, a.description
answered Jan 2 at 17:42
BlueMonkMNBlueMonkMN
18.8k656120
Yeah, the date range is the only different parameters. I'm not sure how using a case to get a specific value will help me, can you show a simple exemple (english isnt my main language ) ?
– Thierry Savard Saucier
Jan 2 at 17:49
@ThierrySavardSaucier Does that help?
– BlueMonkMN
Jan 3 at 13:53
wait, scratch that ... I guess I could make a between U and V and not between W and Z, and just add the 2 sum for the cumul ... I'll go try it out.
– Thierry Savard Saucier
Jan 5 at 5:17
Alright, it worked out nicely. seems alot cleaner now too, thanks.
– Thierry Savard Saucier
Jan 5 at 6:57
add a comment |
It looks like the main difference you are dealing with in the different join sub-queries is the date range if I understand correctly. In order to be able to merge results from a single date range even if the specific dates are different, you could try using a CASE expression to return the same values for all rows within the same date range. For example, you could use a CASE expression to return the beginning of the date range for any date within the range. Or if you don't care about the actual date value, but just want to sort it into separate groups, assign any number.
SELECT a.description, invoice_sum
FROM (
SELECT description, sum(a.InvoiceNumber) invoice_sum,
CASE WHEN date between U and V then 1
WHEN date between W and X then 2
WHEN date between U and Y then 3
WHEN date between W and Z then 4
ELSE 5 END dategroup
FROM Invoice
) a
GROUP BY a.dategroup, a.description
answered Jan 2 at 17:42
BlueMonkMNBlueMonkMN
18.8k656120
It looks like the main difference you are dealing with in the different join sub-queries is the date range if I understand correctly. In order to be able to merge results from a single date range even if the specific dates are different, you could try using a CASE expression to return the same values for all rows within the same date range. For example, you could use a CASE expression to return the beginning of the date range for any date within the range. Or if you don't care about the actual date value, but just want to sort it into separate groups, assign any number.
SELECT a.description, invoice_sum
FROM (
SELECT description, sum(a.InvoiceNumber) invoice_sum,
CASE WHEN date between U and V then 1
WHEN date between W and X then 2
WHEN date between U and Y then 3
WHEN date between W and Z then 4
ELSE 5 END dategroup
FROM Invoice
) a
GROUP BY a.dategroup, a.description
answered Jan 2 at 17:42
BlueMonkMNBlueMonkMN
18.8k656120
edited Jan 3 at 13:52
answered Jan 2 at 17:42
BlueMonkMNBlueMonkMN
18.8k656120
answered Jan 2 at 17:42
BlueMonkMNBlueMonkMN
18.8k656120
answered Jan 2 at 17:42
BlueMonkMNBlueMonkMN
18.8k656120
18.8k656120
Yeah, the date range is the only different parameters. I'm not sure how using a case to get a specific value will help me, can you show a simple exemple (english isnt my main language ) ?
– Thierry Savard Saucier
Jan 2 at 17:49
@ThierrySavardSaucier Does that help?
– BlueMonkMN
Jan 3 at 13:53
wait, scratch that ... I guess I could make a between U and V and not between W and Z, and just add the 2 sum for the cumul ... I'll go try it out.
– Thierry Savard Saucier
Jan 5 at 5:17
Alright, it worked out nicely. seems alot cleaner now too, thanks.
– Thierry Savard Saucier
Jan 5 at 6:57
add a comment |
Yeah, the date range is the only different parameters. I'm not sure how using a case to get a specific value will help me, can you show a simple exemple (english isnt my main language ) ?
– Thierry Savard Saucier
Jan 2 at 17:49
@ThierrySavardSaucier Does that help?
– BlueMonkMN
Jan 3 at 13:53
wait, scratch that ... I guess I could make a between U and V and not between W and Z, and just add the 2 sum for the cumul ... I'll go try it out.
– Thierry Savard Saucier
Jan 5 at 5:17
Alright, it worked out nicely. seems alot cleaner now too, thanks.
– Thierry Savard Saucier
Jan 5 at 6:57
Yeah, the date range is the only different parameters. I'm not sure how using a case to get a specific value will help me, can you show a simple exemple (english isnt my main language ) ?
– Thierry Savard Saucier
Jan 2 at 17:49
Yeah, the date range is the only different parameters. I'm not sure how using a case to get a specific value will help me, can you show a simple exemple (english isnt my main language ) ?
– Thierry Savard Saucier
Jan 2 at 17:49
@ThierrySavardSaucier Does that help?
– BlueMonkMN
Jan 3 at 13:53
@ThierrySavardSaucier Does that help?
– BlueMonkMN
Jan 3 at 13:53
wait, scratch that ... I guess I could make a between U and V and not between W and Z, and just add the 2 sum for the cumul ... I'll go try it out.
– Thierry Savard Saucier
Jan 5 at 5:17
wait, scratch that ... I guess I could make a between U and V and not between W and Z, and just add the 2 sum for the cumul ... I'll go try it out.
– Thierry Savard Saucier
Jan 5 at 5:17
Alright, it worked out nicely. seems alot cleaner now too, thanks.
– Thierry Savard Saucier
Jan 5 at 6:57
Alright, it worked out nicely. seems alot cleaner now too, thanks.
– Thierry Savard Saucier
Jan 5 at 6:57
add a comment |
try this...
DECLARE @monthtoview int
SET @monthtoview=12
;WITH acummyear AS (
SELECT invoice.type
, locationID as area
-- , INVOICE.IDNumber as InvoiceNumber
,year( dbo.Invoice.[date]) AS [year]
,month( dbo.Invoice.[date]) AS [month]
, SUM(Invoice.amount) OVER (PARTITION BY dbo.Invoice.type, year( dbo.Invoice.[date]) ) as amountyear
,Invoice.amount AS amountmonth
FROM Invoice
Where Invoice.date> '2016-12-31' and Invoice.date<= '2018-12-31' -- years 2017 and 2018
AND locationID = 1
AND type IN (1,2)
)
SELECT type,a.area,a.[year],a.[month]
,count(a.type) as invoicecount
,max(a.amountyear) AS amountperyear
,sum(a.amountmonth) AS amountpermonth
from acummyear a --join here your type and location tables
WHERE a.[month]=@monthtoview
GROUP BY type,a.area,a.[year],a.[month]
but in your report(ssrs) use a matrix to display the values, your rows will be the type and location and your columns... one for year(20XX-CUMUL)with amountperyear in the value and another one for year/month(20XX-DEC)with invoicecount in the value, remove the "sum" added to the values in the cell.
answered Jan 4 at 1:52
user2391537user2391537
132
If not using this framework of mine and being purely in sql, this would most likely have worked. I need to work more with CTE and learnt them for good. I've given my answer to the guy who answered me 1st though, thanks for the nice example.
– Thierry Savard Saucier
Jan 5 at 5:31
add a comment |
try this...
DECLARE @monthtoview int
SET @monthtoview=12
;WITH acummyear AS (
SELECT invoice.type
, locationID as area
-- , INVOICE.IDNumber as InvoiceNumber
,year( dbo.Invoice.[date]) AS [year]
,month( dbo.Invoice.[date]) AS [month]
, SUM(Invoice.amount) OVER (PARTITION BY dbo.Invoice.type, year( dbo.Invoice.[date]) ) as amountyear
,Invoice.amount AS amountmonth
FROM Invoice
Where Invoice.date> '2016-12-31' and Invoice.date<= '2018-12-31' -- years 2017 and 2018
AND locationID = 1
AND type IN (1,2)
)
SELECT type,a.area,a.[year],a.[month]
,count(a.type) as invoicecount
,max(a.amountyear) AS amountperyear
,sum(a.amountmonth) AS amountpermonth
from acummyear a --join here your type and location tables
WHERE a.[month]=@monthtoview
GROUP BY type,a.area,a.[year],a.[month]
but in your report(ssrs) use a matrix to display the values, your rows will be the type and location and your columns... one for year(20XX-CUMUL)with amountperyear in the value and another one for year/month(20XX-DEC)with invoicecount in the value, remove the "sum" added to the values in the cell.
answered Jan 4 at 1:52
user2391537user2391537
132
If not using this framework of mine and being purely in sql, this would most likely have worked. I need to work more with CTE and learnt them for good. I've given my answer to the guy who answered me 1st though, thanks for the nice example.
– Thierry Savard Saucier
Jan 5 at 5:31
add a comment |
try this...
DECLARE @monthtoview int
SET @monthtoview=12
;WITH acummyear AS (
SELECT invoice.type
, locationID as area
-- , INVOICE.IDNumber as InvoiceNumber
,year( dbo.Invoice.[date]) AS [year]
,month( dbo.Invoice.[date]) AS [month]
, SUM(Invoice.amount) OVER (PARTITION BY dbo.Invoice.type, year( dbo.Invoice.[date]) ) as amountyear
,Invoice.amount AS amountmonth
FROM Invoice
Where Invoice.date> '2016-12-31' and Invoice.date<= '2018-12-31' -- years 2017 and 2018
AND locationID = 1
AND type IN (1,2)
)
SELECT type,a.area,a.[year],a.[month]
,count(a.type) as invoicecount
,max(a.amountyear) AS amountperyear
,sum(a.amountmonth) AS amountpermonth
from acummyear a --join here your type and location tables
WHERE a.[month]=@monthtoview
GROUP BY type,a.area,a.[year],a.[month]
but in your report(ssrs) use a matrix to display the values, your rows will be the type and location and your columns... one for year(20XX-CUMUL)with amountperyear in the value and another one for year/month(20XX-DEC)with invoicecount in the value, remove the "sum" added to the values in the cell.
answered Jan 4 at 1:52
user2391537user2391537
132
try this...
DECLARE @monthtoview int
SET @monthtoview=12
;WITH acummyear AS (
SELECT invoice.type
, locationID as area
-- , INVOICE.IDNumber as InvoiceNumber
,year( dbo.Invoice.[date]) AS [year]
,month( dbo.Invoice.[date]) AS [month]
, SUM(Invoice.amount) OVER (PARTITION BY dbo.Invoice.type, year( dbo.Invoice.[date]) ) as amountyear
,Invoice.amount AS amountmonth
FROM Invoice
Where Invoice.date> '2016-12-31' and Invoice.date<= '2018-12-31' -- years 2017 and 2018
AND locationID = 1
AND type IN (1,2)
)
SELECT type,a.area,a.[year],a.[month]
,count(a.type) as invoicecount
,max(a.amountyear) AS amountperyear
,sum(a.amountmonth) AS amountpermonth
from acummyear a --join here your type and location tables
WHERE a.[month]=@monthtoview
GROUP BY type,a.area,a.[year],a.[month]
but in your report(ssrs) use a matrix to display the values, your rows will be the type and location and your columns... one for year(20XX-CUMUL)with amountperyear in the value and another one for year/month(20XX-DEC)with invoicecount in the value, remove the "sum" added to the values in the cell.
answered Jan 4 at 1:52
user2391537user2391537
132
answered Jan 4 at 1:52
user2391537user2391537
132
answered Jan 4 at 1:52
user2391537user2391537
132
answered Jan 4 at 1:52
user2391537user2391537
132
132
If not using this framework of mine and being purely in sql, this would most likely have worked. I need to work more with CTE and learnt them for good. I've given my answer to the guy who answered me 1st though, thanks for the nice example.
– Thierry Savard Saucier
Jan 5 at 5:31
add a comment |
If not using this framework of mine and being purely in sql, this would most likely have worked. I need to work more with CTE and learnt them for good. I've given my answer to the guy who answered me 1st though, thanks for the nice example.
– Thierry Savard Saucier
Jan 5 at 5:31
If not using this framework of mine and being purely in sql, this would most likely have worked. I need to work more with CTE and learnt them for good. I've given my answer to the guy who answered me 1st though, thanks for the nice example.
– Thierry Savard Saucier
Jan 5 at 5:31
If not using this framework of mine and being purely in sql, this would most likely have worked. I need to work more with CTE and learnt them for good. I've given my answer to the guy who answered me 1st though, thanks for the nice example.
– Thierry Savard Saucier
Jan 5 at 5:31
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54010627%2fhow-can-i-group-by-a-query-result-where-a-table-is-joined-on-itself%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
It might help to show an example of the output you are getting that's wrong too.
– BlueMonkMN
Jan 2 at 17:33
the output isnt bad ... but that query is over simplified ... I have 7 or 8 table joined to get all of the data imput for a single invoice ( imagine you want that report by item on the invoice, and regroup by item part of a specific group(1 row for pencils, 1 for paper, etc )) ... so the whole query takes 100's of more lines ... i just wish I had a clean example to start working with. each added joined table is include at least 4 more times, each with specific conditions, which makes the number of resulting parameters kinda hard to follow up.
– Thierry Savard Saucier
Jan 2 at 17:35