x

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
more ▼

asked Oct 21, 2010 at 07:08 AM in Default

Gogolo gravatar image

Gogolo
323 23 26 27

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.
Oct 21, 2010 at 07:14 AM WilliamD
@agron - we like a challenge here, so yes we can do it without a cursor, just give us a little more info though.....:)
Oct 21, 2010 at 07:34 AM Kev Riley ♦♦
I just pasted a script with some data given as example, thank you in advanced!!
Oct 21, 2010 at 08:00 AM Gogolo
Any sample output you want to see?
Oct 21, 2010 at 10:57 AM ozamora
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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)
more ▼

answered Oct 21, 2010 at 08:09 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

This query looks OK, but I need results split ed in different column per each month...? example: ConsID, Debt, January, February etc....
Oct 21, 2010 at 11:13 PM Gogolo

@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...!?
Oct 22, 2010 at 06:04 AM Gogolo
@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).
Oct 22, 2010 at 06:13 AM WilliamD

@WilliamD - is this line right?

SELECT @SQL2 = COALESCE(@SQL2, '') ...
Oct 22, 2010 at 06:48 AM ThomasRushton ♦
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?
Oct 22, 2010 at 07:18 AM Gogolo
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x25

asked: Oct 21, 2010 at 07:08 AM

Seen: 1285 times

Last Updated: Oct 21, 2010 at 10:50 AM