Click here to Skip to main content
15,065,044 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I am passing a DataTable to a stored procedure to populate my SQL table.
There are thousands of rows. I am trying to summarize 5 of the columns, so that the thousand of rows, turn into 20 rows. Because there are 20 unique "KjøretøyID's" in [KjøretøyID].

I am trying to incorporate the following into my stored procedure:

- GROUP BY [KjøretøyID] column.
- SUM cells in [Betaling (brutto)] [Betaling (netto)] [Bestillingsgebyr (netto)] [Betalingsgebyr (netto)] [Oppdragsgebyr (netto)]
- Somehow use UPDATE? To re-configure the other columns who aren't getting added. So that my end-result are 20 rows, one per unique "KjøretøyID".

What I have tried:

This is my stored procedure.
It's using a User-Defined Table Type as temporary storage.

C#
/****** Object:  StoredProcedure [dbo].[spGetTrips]    Script Date: 17-Sep-21 12:59:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spGetTrips]
	@trips udtTripsPerMonthX readonly

AS
BEGIN
	SELECT 'Not inserted:', *
		FROM @trips AS o
		WHERE EXISTS (
			SELECT 1
				FROM dbo.tblTripsPerMonth AS i
				WHERE i.KjøretøyID = o.KjøretøyID
				  AND i.[Betaling (netto)] = o.[Betaling (netto)]
				  AND i.LøyvehaverFakturaID = o.LøyvehaverFakturaID
		);

	INSERT INTO dbo.tblTripsPerMonth([KjøretøyID], [År], [Måned], [Betaling (brutto)], [Betaling (netto)], [Bestillingsgebyr (netto)], [Betalingsgebyr (netto)], [OppdragsMVAkode], [LøyvehaverFakturaID], [Oppdragsgebyr (netto)], [OppdragsgebyrMVAkode], [RidelRegionFakturaID])

		SELECT	[KjøretøyID], 
				SUBSTRING([År], 7, 4), 
				SUBSTRING([Måned], 4, 2), 
				SUM([Betaling (brutto)]) AS [Betaling (brutto)], 
				SUM([Betaling (netto)]) AS [Betaling (netto)], 
				SUM([Bestillingsgebyr (netto)]) AS [Bestillingsgebyr (netto)], 
				SUM([Betalingsgebyr (netto)]) AS [Betalingsgebyr (netto)], 
				[OppdragsMVAkode], 
				CONCAT(LøyvehaverFakturaID, + 'UF-' + SUBSTRING([År], 9, 2) + SUBSTRING([Måned], 4, 2)), 
				SUM([Oppdragsgebyr (netto)]) AS [Oppdragsgebyr (netto)], 
				[OppdragsgebyrMVAkode], 
				CONCAT([RidelRegionFakturaID], + 'UF-' + SUBSTRING([År], 9, 2) + SUBSTRING([Måned], 4, 2))

			FROM @trips AS o
			WHERE NOT EXISTS ( 
				SELECT 1
					FROM tblTripsPerMonth i
					INNER JOIN @trips o
						ON i.KjøretøyID = o.KjøretøyID
						AND i.[Betaling (netto)] = o.[Betaling (netto)]
						AND i.LøyvehaverFakturaID = o.LøyvehaverFakturaID
			)

			GROUP BY [KjøretøyID], [År], [Måned], [OppdragsMVAkode], [LøyvehaverFakturaID], [OppdragsgebyrMVAkode], [RidelRegionFakturaID]


	UPDATE tblTripsPerMonth
	SET [OppdragsMVAkode] = (
		SELECT [MVAsats]
		FROM [tblMVAkoder]
		WHERE [ID] = 'MVAkode2'
		);

	UPDATE tblTripsPerMonth
	SET [OppdragsgebyrMVAkode] = (
		SELECT [MVAsats]
		FROM [tblMVAkoder]
		WHERE [ID] = 'MVAkode5'
		);
		
	UPDATE tblTripsPerMonth
	SET [Oppdragsgebyr (netto)] = (

		SELECT [Betaling (brutto)] * 8 / 100
	);
END


This is essentially what I need:

chart — ImgBB[^]
Posted
Updated 17-Sep-21 3:04am
v5
Comments
Wendelius 16-Sep-21 14:36pm
   
Can you post an example of the source data and the desired result?
Flidrip 17-Sep-21 3:14am
   
@Wendelius, thank you for taking the time. I have updated my question with a image showing what I need.

1 solution

When summarising data in a SELECT statement you would never use UPDATE. You are doing one or the other not both (although you can update a table based on the results of a SELECT, but the image does not imply that that is what you are trying to do).

From the image provided it looks like a straight forward "GROUP BY"
SQL
SELECT 
   SUM([Betaling (brutto)]) as [Betaling (brutto)]
   ,SUM([Betaling (netto)]) as [Betaling (netto)]
   ,SUM([Bestillingsgebyr (netto)]) as [Bestillingsgebyr (netto)]
   ,SUM([Betalingsgebyr (netto)]) as [Betalingsgebyr (netto)]
   ,SUM([Oppdragsgebyr (netto)]) as [Oppdragsgebyr (netto)]
FROM 
   tblTripsPerMonth
GROUP BY [KjøretøyID], [År], [Måned]
That will give you one row per [KjøretøyID], per [År] and per [Måned]. With the data you provided it will give the results on the right.

A couple of other points.
- Stick to English or Anglicized names for columns and avoid spaces and things like (brutto). Having to put Square Brackets around every column quickly becomes very tiring.
- When giving sample data here in QA don't use images. Just provide the code that will populate the sample data (with the value!) or at least provide that table in text format here so we can copy it easily.
- Same goes for the expected results - I'm at work in my lunch break and do not like following links outside this site just to help someone in my spare time
- Try to stick to just the relevant detail - it doesn't matter that this is a Stored Procedure and it doesn't matter that you are using a user-defined table type - you are essentially just trying to summarise some data. Small steps.

Edit after OP comments:
It seems that you only really want to group by [KjøretøyID] so try this instead
SQL
SELECT [KjøretøyID]
   SUM([Betaling (brutto)]) as [Betaling (brutto)]
   ,SUM([Betaling (netto)]) as [Betaling (netto)]
   ,SUM([Bestillingsgebyr (netto)]) as [Bestillingsgebyr (netto)]
   ,SUM([Betalingsgebyr (netto)]) as [Betalingsgebyr (netto)]
   ,SUM([Oppdragsgebyr (netto)]) as [Oppdragsgebyr (netto)]
FROM 
   tblTripsPerMonth
GROUP BY [KjøretøyID]
That is going to give you the results
AG4203000002     5699     5376.40    147.16    80.62
AG4203000003     1190     1122.64     22.64    19.84
Notice that [År] and [Måned]. If you want to include those extra columns in the summary, you cannot just add them to the Group By clause, you also need to add them to the Select ... and from what you are saying in the comments that is returning too many rows.

Perhaps include some sample data where [År] != 2021 and/or [Måned] != 7 ... work out your expected results from that revised sample and share that.
   
v2
Comments
Flidrip 17-Sep-21 9:01am
   
@CHill60, thanks alot -definitively taking all of those other points to heart. Appreciate that.
When it comes to the main issue, I am experiencing some confusion.
After I added your solution (although extrapolated a little, as my Excel example was abbreviated), I end up with some un-expected results. It returns *almost* all the rows; 7190. My Excel sheet is at 7210 rows. Why did it suddenly remove 20? lol.
I have updated my stored procedure in my original question. I am sure I am doing an obvious mistake here.
CHill60 17-Sep-21 9:19am
   
The updates will have NO effect on the query because they are being done after it has run! I repeat - When summarising data in a SELECT statement you would never use UPDATE.

If your original spreadsheet has 7210 rows and the query returns 7190 then you must have 7190 unique combinations of [KjøretøyID], [År] and [Måned] on your spreadsheet. Group by will aggregate data - you should never get the same number of rows back as are in your original data (if you do then there was no point in the aggregation). Time to do some reading up ... SQL GROUP BY Statement[^]
Flidrip 17-Sep-21 9:57am
   
Ok, I understand - I will work on reproducing the results from my UPDATE clause in another way.
But - I am still confused about the GROUP BY. Because [År], [Måned] (after SUBSTRING), and [LøyvehaverFakturaID], [RidelRegionFakturaID] (after CONCAT) are identical for all 7k+ rows. How can "eliminate" the extra duplicates?
The only one who varies is [KjøretøyID]. How can I exclude the others from the GROUP BY - if I try to remove them, it gives me error: Column '@trips.LøyvehaverFakturaID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I have seen tutorial, and read your link, as well as others, but I can't wrap my head around this.
CHill60 17-Sep-21 10:18am
   
If you just want to group on [KjøretøyID] then that is the only non-aggregating item you can include - you have to miss out [År], [Måned] altogether. I will update my solution to hopefully explain this better
CHill60 17-Sep-21 10:30am
   
You can leave the update statements in there - I've just realised that you are querying the results table outside of the stored procedure, not returning the query from the SP. My Bad!
Flidrip 17-Sep-21 10:35am
   
Thank you very much for your help. It has been invaluable!
Now onto the next hurdle.....
Wendelius 17-Sep-21 13:22pm
   
Nice explanation!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900