question

Gogolo avatar image
Gogolo asked

Difficult Selection

I Have a table where I have to calculate 2 fields for each consumer, for each month. Can I do this query without Cursor? If yes HOW? create table #Consumer ( ConsId nvarchar (20), Debit MONEY, Credit Money, BillDate datetime, ches1 decimal (18,4), Ches2 decimal (18,4) ) Insert into #Consumer (ConsID, Debit, Credit,BillDate,ches1,Ches2)( SELECT '1', '20.55', '10.55','20100301', '110.20','54.10' UNION ALL SELECT '1', '10.55', '5.55','20100401', '50.20','25.10' UNION ALL SELECT '2', '10.55', '5.55','20100601', '50.20','25.10' UNION ALL SELECT '2', '12.55', '11.55','20100314', '110.20','54.10' UNION ALL SELECT '3', '20.55', '10.55','20100401', '110.20','54.10' UNION ALL SELECT '3', '10.55', '5.55','20100105', '50.20','25.10' UNION ALL SELECT '1', '20.55', '10.55','20100313', '110.20','54.10' UNION ALL SELECT '1', '10.55', '5.55','20100401', '50.20','25.10' UNION ALL SELECT '2', '10.55', '5.55','20100612', '50.20','25.10' UNION ALL SELECT '2', '11.05', '11.55','20100315', '110.20','54.10' UNION ALL SELECT '3', '12.55', '10.55','20100421', '110.20','54.10' UNION ALL SELECT '3', '10.55', '5.55','20100116', '50.20','25.10') SELECT * FROM #Consumer AS C DROP TABLE #Consumer
calculations
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

agron - please supply us with a bit more detail. Table creation scripts, sample data, expected output and what you have as a query at present. It is more than likely this can be done without a cursor.
3 Likes 3 ·
@agron - we like a challenge here, so yes we can do it without a cursor, just give us a little more info though.....:)
2 Likes 2 ·
I just pasted a script with some data given as example, thank you in advanced!!
0 Likes 0 ·
Any sample output you want to see?
0 Likes 0 ·

1 Answer

· Write an Answer
WilliamD avatar image
WilliamD answered
I am not sure what you want here. Here you have the monthly Debits-Credits per Customer: SELECT ConsId, (DATEADD(MONTH, DATEDIFF(MONTH, 0, Billdate), 0)), -- Cast all Billdates to the month level SUM(Debit - Credit) FROM #Consumer AS C GROUP BY ConsId, (DATEADD(MONTH, DATEDIFF(MONTH, 0, Billdate), 0)) The other option would be grouping sets, to give you the Monthly totals and the per customer totals: SELECT ConsId, (DATEADD(MONTH, DATEDIFF(MONTH, 0, Billdate), 0)), SUM(debit - Credit) FROM #Consumer C GROUP BY GROUPING SETS((consid), ((DATEADD(MONTH, DATEDIFF(MONTH, 0, Billdate), 0)))) If this is not what you want, please provide an expected output for your sample data. **** **EDIT** **** Now that agron has explained what he wanted as the final output, this should do the trick: Edit CET 15:11 - added temp Tally Table, because agron didn't have one - sorry! /* This is based on the BRILLIANT article from Jeff Moden on SQLServerCentral.com http://www.sqlservercentral.com/articles/Crosstab/65048/ */ /* Find the start and end of the dates in the Consumer table to create the dynamic cross tab casting dates to start of each month */ DECLARE @StartDate date, @EndDate date, @SQL1 nvarchar(4000), @SQL2 nvarchar(4000), @SQL3 nvarchar(4000) SELECT @StartDate = MIN((DATEADD(mm, DATEDIFF(mm, 0, BillDate), 0))), @EndDate = MAX((DATEADD(mm, DATEDIFF(mm, 0, BillDate), 0))) FROM #Consumer /* Build the dynamic sql*/ /* @SQL1 is static */ SELECT @SQL1 = 'SELECT * FROM ( SELECT CASE WHEN GROUPING(ConsId) = 1 THEN ''Total'' ELSE ConsId END AS ConsId,'+CHAR(10) /* @SQL2 is dynamic creating all columns per each month between @StartDate and @EndDate */ ;WITH Tally (N) AS ( SELECT N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns AC ) SELECT @SQL2 = COALESCE(@SQL2, '') + ' SUM(CASE WHEN MonthDate = ' + QUOTENAME(d.MonthName, '''') + ' THEN Total ELSE 0 END) AS [' + d.MonthName + '],' + CHAR(10) FROM (SELECT N, STUFF(CONVERT(char(11), DATEADD(mm, N - 1, @StartDate), 100), 4, 3, '') AS MonthName FROM Tally WHERE N <= (DATEDIFF(mm, @StartDate, @EndDate)+1)) d ORDER BY d.N /* @SQL3 is static too. Note the final WHERE clause, which removes the Totals row from the results */ SELECT @SQL3 = ' SUM(Total) AS Total FROM ( SELECT ConsId, MonthDate = (DATEADD(mm, DATEDIFF(mm, 0, BillDate), 0)), -- Cast all Billdates to the month level, Total = SUM(Debit - Credit) FROM #Consumer AS C GROUP BY ConsId, (DATEADD(mm, DATEDIFF(mm, 0, BillDate), 0)) ) d GROUP BY ConsId WITH ROLLUP ) CrossTab WHERE ConsId <>''Total'' '; /* Run the query */ EXEC(@SQL1+@SQL2+@SQL3)
9 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This query looks OK, but I need results split ed in different column per each month...? example: ConsID, Debt, January, February etc....
0 Likes 0 ·
@williamd, The query should provide data like BRILLIANT article from Jeff Moden, but something I don't understand and I cannot build this... I tried your query but I got errors on @SQL2...!?
0 Likes 0 ·
@Agron - I missed the Tally table that I was using. I now added it to the solution. Look it up on SqlServerCentral (it is from Jeff Moden too).
0 Likes 0 ·
@WilliamD - is this line right? SELECT @SQL2 = COALESCE(@SQL2, '') ...
0 Likes 0 ·
It is right, query works but I'm not getting proper results, I mean I have record from month 6 (June) and query doesn't create Month 6?
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.