Help with SQL aggregate function

[P00HB33R]

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]

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]

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?

 

[P00HB33R]

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

 

[VG008]

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]

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

 

[VG008]

Cool stuff - The UI is very clean and professional.