Anyone with SQL Knowledge can help me out?

Software & Operating Systems
#1

I’m really not sure if this belongs here, but i figured MS SQL Server is a Software, so yeah, here goes.

I’ve been running into a similar Problem time and time again and have no way to solve this.
Currently i’m trying to aggregate some sales Data in Grafana. The Datasource is a MS SQL Server 2012. I’ll throw the whole query here and try to explain whats going on:

SELECT
IIF (SUM(CLE.I_CLERKTYPE_P) IN ('1', '6', '12', '17'),
	(
	SELECT TOP 1
	emp1.S_NAME2 + ' ' + emp1.S_NAME1 AS VB
	FROM INVOICE_P AS offer1
	LEFT JOIN CLERKINV_P AS clerk1 on offer1.I_INVOICE_P = clerk1.I_INVOICE_P
	JOIN EMPLOYEE_M AS emp1 on clerk1.I_EMPLOYEE_M = emp1.I_EMPLOYEE_M
	WHERE  offer1.DT_DELETED IS NULL AND clerk1.DT_DELETED IS NULL AND clerk1.I_CLERKTYPE_P = '1' AND offer1.S_INVNO = INV.S_INVNO 
	)
	, 'Kein VB') AS VB
,FORMAT(INV.N_INCL, 'C')
FROM [StepsProd].[sao].[INVOICE_P] AS INV
LEFT JOIN CLERKINV_P AS CLE on INV.I_INVOICE_P = CLE.I_INVOICE_P
JOIN EMPLOYEE_M AS emp on CLE.I_EMPLOYEE_M = emp.I_EMPLOYEE_M
WHERE INV.DT_DELETED IS NULL AND CLE.DT_DELETED IS NULL
GROUP BY VB

Ok, The basic idea is, i have one table with sales (INVOICE_P) and another with corresponding salesperson (CLERKINV_P). Problem is, some Sales don’t have a salesperson. Rather than ommiting those, I’m assigning those a Value of “Kein VB”. All of this works great. I can get a list of sales with the correct corresponding sales person.

No the tricky part. I want to Sum those up and Group by the sales person. I have no clue how to do this. Since it is determined with a Subquerry, i have no decent way of grouping by it. I though by using the AS i could reference it later, but SQL is complaining VB is undefined. I also tried storing this in a variable with AS VB into @vb but this doesn’t work either. I’m not sure if this is a mysql thing or such.
Last option was repeating the whole subquery in the Group by statement, but this doesn’t work either as SQL complains about using a Aggregation in a GROUP BY clause.

I’m at a loss here. The last thing i can think of, is using DECLARE to create a table variable and storing all of this in it. I’ve never done this, so no clue if this would be correct.

Any help on this is welcome. And a more general approach on how to GROUP BY fields that where “created” in the SELECT statement would be really helpful, as i have this requirement quite often.

(Please forgive my potentially messy Style of writing SQL Querys. I’m self thought and am basically just copying stuff from Stackoverflow until it works :wink: )

1 Like
#2

I’ve not used grafana, so this may not be applicable, but when I learned SQL in the context of a lamp stack, the general guidance was to make many simple queries and then do any complex manipulation in php (or ruby or whatever context is above the database).

That said, if you need to do the whole operation in sql, I think you might need to make a table, but I’m not an sql wizard by any measure.

1 Like
#3

I’ve not properly read or digested your post (sorry, too busy) but my gut instinct is to ask if you’ve tried to solve it using a Common Table Expression?

This let’s you build a temporary result set that you can query over and even refer to itself. Very useful at times.

https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-2017

1 Like
#4

Thanks. Yeah it’s basically all SQL. With Grafana you add Panels (widgets) that show data. You define the data per panel with an SQL Query in this case. So i need to use SQL only to get this.

I’ll try the variant with defining a table in the Query now. Maybe this works. No clue though. I’ll report back.

Edit: So it actually works. I didn’t think it would but it did. The final “solution”:

DECLARE @vbtable table(vb varchar(50), umsatz int)
INSERT INTO @vbtable
SELECT
IIF (SUM(CLE.I_CLERKTYPE_P) IN ('1', '6', '12', '17'),
	(
	SELECT TOP 1
	emp1.S_NAME2 + ' ' + emp1.S_NAME1 AS VB
	FROM INVOICE_P AS offer1
	LEFT JOIN CLERKINV_P AS clerk1 on offer1.I_INVOICE_P = clerk1.I_INVOICE_P
	JOIN EMPLOYEE_M AS emp1 on clerk1.I_EMPLOYEE_M = emp1.I_EMPLOYEE_M
	WHERE  offer1.DT_DELETED IS NULL AND clerk1.DT_DELETED IS NULL AND clerk1.I_CLERKTYPE_P = '1' AND offer1.S_INVNO = INV.S_INVNO 
	)
	, 'Kein VB') AS VB
,INV.N_INCL
FROM INVOICE_P AS INV
LEFT JOIN CLERKINV_P AS CLE on INV.I_INVOICE_P = CLE.I_INVOICE_P
JOIN EMPLOYEE_M AS emp on CLE.I_EMPLOYEE_M = emp.I_EMPLOYEE_M
WHERE INV.DT_DELETED IS NULL AND CLE.DT_DELETED IS NULL AND INV.D_INVOICEDATE >= $__timeFrom() AND INV.D_INVOICEDATE <= $__timeTo()
GROUP BY INV.S_INVNO, INV.D_INVOICEDATE, INV.S_DESCRIPTION, INV.N_INCL

SELECT vb, FORMAT(SUM(Umsatz), 'C')
FROM @vbtable
GROUP BY vb

Works like a charm. Maybe not Pretty, but i don’t care really. I’m also not sure if performance can be an issue. So far, with around 10.000 Rows it doesn’t seem to be. And since this dashboard will be used by only one person, i don’t care to much.

2 Likes