Help with SQL aggregate function

P00HB33R

Senior Member
Joined
Jul 15, 2010
Messages
680
Hi guys

So here's the deal. I have got 2 tables. "JOBCARD" & "JOBCARD_DETAIL"

JOBCARD contains all detail relevant to a jobcard header eg. Customer, Date, Technician etc
JOBCARD DETAIL contains the lines with "work done", "price", "qty" and "line total".

I want to create a view/table/procedure that will give me a breakdown By Technician, Per Month, Total Sales

The total sales needs to be calulated by Summing "Line total" in JOBCARD_DETAIL for given month per technician.

I need to use the resulting table/view/procedure to populate a Line Graph Report.

Just to be clear the results should look something like:

Piet | Jan 2015 | 5000
Koos | Jan 2015 | 6500
Piet | Feb 2015 | 2455
Koos | Feb 2015 | 3478

And so on...

Any help will be greatly appreciated.

And if you are wondering, this is just a pet project that I started to delve into SQL & WinForms.
So far its going great, but the reporting part has got me a bit stumped.
 

VG008

Senior Member
Joined
Dec 9, 2010
Messages
778
Hi

A join should sort you out.

I don't know what type of SQL database you are using (mysql/MSSQL), or how the two tables link to each other, but this is what I would do.


SELECT JOBCARD.TECHNICIAN,
MONTH_YEAR(JOBCARD.DATE),
SUM(JOBCARD_DETAIL.LINE_TOTAL)
FROM JOBCARD
JOIN JOBCARD_DETAIL
ON JOBCARD_DETAIL.JOBID = JOBCARD.JOBID
GROUP BY JOBCARD.TECHNICIAN,
MONTH_YEAR(JOBCARD.DATE)


The MONTH_YEAR syntax is not correct. There are various ways you can format the date, and it depends on the SQL database you are using.
 

P00HB33R

Senior Member
Joined
Jul 15, 2010
Messages
680
Hi

A join should sort you out.

I don't know what type of SQL database you are using (mysql/MSSQL), or how the two tables link to each other, but this is what I would do.


SELECT JOBCARD.TECHNICIAN,
MONTH_YEAR(JOBCARD.DATE),
SUM(JOBCARD_DETAIL.LINE_TOTAL)
FROM JOBCARD
JOIN JOBCARD_DETAIL
ON JOBCARD_DETAIL.JOBID = JOBCARD.JOBID
GROUP BY JOBCARD.TECHNICIAN,
MONTH_YEAR(JOBCARD.DATE)


The MONTH_YEAR syntax is not correct. There are various ways you can format the date, and it depends on the SQL database you are using.

I will forever be in your debt.

Thank you.

It works flawlessly.

Is there a way to have the Month and year be displayed in the column instead of a number?
 

VG008

Senior Member
Joined
Dec 9, 2010
Messages
778
Is there a way to have the Month and year be displayed in the column instead of a number?

Yes - There are various ways of converting dates in SQL.
I usually use this guide: http://www.sql-server-helper.com/tips/date-formats.aspx

Basically, you will replace MONTH_YEAR with SUBSTRING(CONVERT(VARCHAR(11), JOBCARD.DATE, 113), 4, 8)


Also, how would I group the result first by year then by month?
After the group by:

Order by FORMAT(JOBCARD.DATE, 'yyyyMM')

The order by can have any of the date format functions that meets your needs.
 

P00HB33R

Senior Member
Joined
Jul 15, 2010
Messages
680
Yes - There are various ways of converting dates in SQL.
I usually use this guide: http://www.sql-server-helper.com/tips/date-formats.aspx

Basically, you will replace MONTH_YEAR with SUBSTRING(CONVERT(VARCHAR(11), JOBCARD.DATE, 113), 4, 8)



After the group by:

Order by FORMAT(JOBCARD.DATE, 'yyyyMM')

The order by can have any of the date format functions that meets your needs.

Again thank you so much for your help. I got it working. After a bit of googling the different functions I got it working exactly as I wanted.

Here is a screenshot of the reports just so you can see what you helped me with.

http://i59.tinypic.com/21j7ou9.png

Thanks mate
 
Top