Click here to Skip to main content
15,868,016 members
Please Sign up or sign in to vote.
2.50/5 (2 votes)
See more:
My Table
SQL
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

SQL
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
Updated 3-Dec-12 19:01pm
v2
Comments
__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?

 
Share this answer
 
Try below sql

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
 
Share this answer
 
Comments
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
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.
 
Share this answer
 
Try this it might help you

SQL
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 :)
 
Share this answer
 

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