Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL Server
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 19:50pm
Edited 3-Dec-12 20:01pm
__TR__28.9K
v2
Comments
__TR__ at 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: bad
good
Please Sign up or sign in to vote.

Solution 4

  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

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
  Permalink  
Comments
sukhen dass at 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: bad
good
Please Sign up or sign in to vote.

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.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Try this it might help you
 
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 Smile | :) Smile | :) Smile | :) Happy coding Smile | :)
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 275
1 DamithSL 265
2 CPallini 235
3 Maciej Los 185
4 George Jonsson 170
0 OriginalGriff 5,305
1 DamithSL 4,382
2 Maciej Los 3,760
3 Kornfeld Eliyahu Peter 3,470
4 Sergey Alexandrovich Kryukov 2,901


Advertise | Privacy | Mobile
Web01 | 2.8.141216.1 | Last Updated 5 Dec 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100