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
@agron - we like a challenge here, so yes we can do it without a cursor, just give us a little more info though.....:)
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)
@WilliamD - is this line right? SELECT @SQL2 = COALESCE(@SQL2, '') ...
