12,999,417 members (84,377 online)
Rate this:
See more:
My Table
```Month	Year	Dealer	Dealer_Code	City	 Active	Points
9	2012	Jaswant Motors	10000	Jalandhar  1	500
9	2012	Speedways	10001	Amritsar	   1	1300
9	2012	Jaswant Motors	10000	Jalandhar  1	279
9	2012	Speedways	10001	Amritsar	   1	88```

Desired Output

```Month	Dealer	                             Service_Points	manth	year
Sep-12	Jaswant Motors - 10000 - Jalandhar 	779	          9	2012
Sep-12	Speedways - 10001 - Amritsar	        1388	          9	2012
Sep-12	Total	                                  2167	         [123]	[1234]
Total	Jaswant Motors - 10000 - Jalandhar	         779	         [234]	[2345]
Total	Speedways - 10001 - Amritsar	        1388	         [234]	[2345]
Total	Total	                                  2167	         [234]	[345]```

Note:-Values in Square Brackets([]) are Imaginary value
Posted 3-Dec-12 18:50pm
Updated 3-Dec-12 19:01pm
__TR__29K
v2
__TR__ 4-Dec-12 1:09am

Can you please explain what do you mean by Imaginary values ? What data should be populated in the last 2 columns of your result?

Rate this:

Rate this:

## Solution 1

Try below sql

`select convert( varchar(3) , DATENAME(MM,DATEADD(mm,Month,-1)) ) + '-' + right (convert(varchar,Year ),2) as Month ,Dealer ,Service_Points, manth, year from TableName`
sukhen dass 4-Dec-12 3:19am

Thanks Kasim...but your query will not give the desired output because there is no group by included..please,refer the desired output given
Rate this:

## Solution 2

You are going to have to break this up into multiple queries.

First get the totals into a temp table matching your output, all fields will have to be varchar as you are mixing data types, a really crappy idea.

Then you select your detail records and use a `UNION `to the temp table, you may want to add an ID column to insure you can sort the data correctly.

This is a particularly silly query as the totalling should be done in your consuming application.
Rate this:

## Solution 3

`Select left(DateName( month , DateAdd(MONTH , [Month]* , 0 ) - 1 ),3)+ '-' + substring(CAST([year]* as CHAR(4)), 3, 2) AS Month,Dealer + '-' + Dealer_Code + '-' + City as Dealer,Points as Service point ,manth ,Month from MyTable`

[Month]* and [year]* is the column name of the table MyTable

Keep smiling :):):) Happy coding :)

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

Top Experts
Last 24hrsThis month
 RickZeeland 185 OriginalGriff 50 Raghu112 50 Bharat Leel 35 Jochen Arndt 30
 OriginalGriff 4,547 ppolymorphe 2,272 RickZeeland 2,107 Kornfeld Eliyahu Peter 1,973 Richard Deeming 1,593

Advertise | Privacy | Mobile
Web01 | 2.8.170624.1 | Last Updated 5 Dec 2012