Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Pulling the Data from the table for year wise		  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mahesh.sanka
Starting Member

India
18 Posts
Posted - 03/25/2013 :  08:06:15  Show Profile  Reply with Quote

Hi ,

I need to pull out the data based on the year select.

This is my table

POLICY_NO CALLDATETIME CALLTYPE AGENT_ID VENDER_NAME APPLICATION_NUMBER UNIQUE_ID

005820319 2012-10-01 00:00:00.000 E QUAEND Qualtouch
A47148143 Z00000

005813521 2012-10-01 00:00:00.000 E QUAEND Qualtouch
A47148161 Z00000

005826165 2012-10-01 00:00:00.000 E QUAEND Qualtouch
A47152911 Z00000

005840653 2012-10-01 00:00:00.000 E QUAEND Qualtouch
A47387908 Z00000

005858520 2012-11-20 00:00:00.000 L QUAELG Qualtouch A46852284 QUAEND1

007 2012-10-01 00:00:00.000 L QUAELG Qualtouch
A46852287 QUAEND1

I want the out put like this when user entered 2012

Vender Name Month-Year Count of E Count of L
Qualtouch October-2012 1 4
Qualtouch November-2012 0 1

Below is the my sql Query


Select    VENDER_NAME,datename(month, CALLDATETIME ) + '-' + datename(year, CALLDATETIME) 'call_date',CALL_TYPE,COUNT(*) 'cnt' 
FROM      INSR_CALL_DETAILS 
WHERE     year(CALLDATETIME) = '2012'
GROUP BY  datename(month, CALLDATETIME ) + '-' + datename(year, CALLDATETIME),VENDER_NAME,CALL_TYPE


But i am getting the o/p like this.

Qualtouch November-2012 L 1
Qualtouch October-2012 E 4
Qualtouch October-2012 L 1

Can any one help me where i am doing wrong

Thanks & Regards,
Mahesh Kumar Sanka

ahmeds08
Aged Yak Warrior

India
737 Posts
Posted - 03/25/2013 :  08:42:30  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
Not sure but,try this:


select vendor_name,
convert(varchar(4),MONTH(calldatetime))+'-'+convert(varchar(4),YEAR(calldatetime)) as month_year,
count(case when calltype='E' then 1 else 0)end as count_of_E,
count(case when calltype='L' then 1 else 0) end as count_of_L from INSR_CALL_DETAILS
group by vendor_name,
convert(varchar(4),MONTH(calldatetime))+'-'+convert(varchar(4),YEAR(calldatetime))
Go to Top of Page

mahesh.sanka
Starting Member

India
18 Posts
Posted - 03/25/2013 :  08:53:18  Show Profile  Reply with Quote
Hi ahmeds08 ,

Thanks for reply. But i am getting error while executing your query.






Thanks & Regards,
Mahesh Kumar Sanka
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2242 Posts
Posted - 03/25/2013 :  09:06:20  Show Profile  Reply with Quote
quote:
Originally posted by mahesh.sanka

Hi ahmeds08 ,
Thanks for reply. But i am getting error while executing your query.
Thanks & Regards,
Mahesh Kumar Sanka

Which error you got? Post that error

--
Chandu
Go to Top of Page

mahesh.sanka
Starting Member

India
18 Posts
Posted - 03/25/2013 :  09:10:25  Show Profile  Reply with Quote
quote:
Originally posted by bandi

quote:
Originally posted by mahesh.sanka

Hi ahmeds08 ,
Thanks for reply. But i am getting error while executing your query.
Thanks & Regards,
Mahesh Kumar Sanka

Which error you got? Post that error

--
Chandu



Hi Chandu ,

I am getting the following error.

Incorrect Syntax near )

Thanks & Regards,
Mahesh Kumar Sanka
Go to Top of Page

mahesh.sanka
Starting Member

India
18 Posts
Posted - 03/25/2013 :  09:20:30  Show Profile  Reply with Quote
quote:
Originally posted by ahmeds08

Not sure but,try this:


select vendor_name,
convert(varchar(4),MONTH(calldatetime))+'-'+convert(varchar(4),YEAR(calldatetime)) as month_year,
count(case when calltype='E' then 1 else 0)end as count_of_E,
count(case when calltype='L' then 1 else 0) end as count_of_L from INSR_CALL_DETAILS
group by vendor_name,
convert(varchar(4),MONTH(calldatetime))+'-'+convert(varchar(4),YEAR(calldatetime))


Hi Ahmed,

Thanks for Reply.


select VENDER_NAME,

convert(varchar(4),MONTH(calldatetime))+'-'+convert(varchar(4),YEAR(calldatetime)) as month_year,

count(case CALL_TYPE when 'E' then 1 else 0 end )as count_of_E,

count(case CALL_TYPE when 'L' then 1 else 0 end) as count_of_L from INSR_CALL_DETAILS

group by VENDER_NAME,CALL_TYPE ,

convert(varchar(4),MONTH(calldatetime))+'-'+convert(varchar(4),YEAR(calldatetime))


But i am getting wrong out put. i.e duplication of data is not coming

Thanks & Regards,
Mahesh Kumar Sanka
Go to Top of Page

mahesh.sanka
Starting Member

India
18 Posts
Posted - 03/25/2013 :  09:21:12  Show Profile  Reply with Quote
quote:
Originally posted by bandi

quote:
Originally posted by mahesh.sanka

Hi ahmeds08 ,
Thanks for reply. But i am getting error while executing your query.
Thanks & Regards,
Mahesh Kumar Sanka

Which error you got? Post that error

--
Chandu

Error rectified but out put is not correct


Thanks & Regards,
Mahesh Kumar Sanka
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2242 Posts
Posted - 03/25/2013 :  09:22:30  Show Profile  Reply with Quote
group by VENDER_NAME,CALL_TYPE ,

convert(varchar(4),MONTH(calldatetime))+'-'+convert(varchar(4),YEAR(calldatetime))
Go to Top of Page

mahesh.sanka
Starting Member

India
18 Posts
Posted - 03/26/2013 :  00:12:59  Show Profile  Reply with Quote
quote:
Originally posted by bandi

group by VENDER_NAME,CALL_TYPE ,

convert(varchar(4),MONTH(calldatetime))+'-'+convert(varchar(4),YEAR(calldatetime))




Thanks for your reply. Even though i am getting wrong out put.

GanpatiEnterprises 10-2012 4 4
Qualtouch 10-2012 5 5
GanpatiEnterprises 11-2012 1 1
Qualtouch 11-2012 1 1

Thanks & Regards,
Mahesh Kumar Sanka
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2242 Posts
Posted - 03/26/2013 :  00:38:29  Show Profile  Reply with Quote
If you want E_count and L_Count based on only Month-YYYY, then use the following code

SELECT DISTINCT icd.VENDER_NAME, call_date, E_Count, L_Count
FROM INSR_CALL_DETAILS icd
JOIN ( SELECT datename(month, CALLDATETIME ) + '-' + datename(year, CALLDATETIME) 'call_date',
			COUNT(CASE WHEN CALLTYPE='E' THEN 1 END) 'E_Count',
			COUNT(CASE WHEN CALLTYPE='L' THEN 1 END) 'L_Count' 
		FROM  INSR_CALL_DETAILS 
		WHERE year(CALLDATETIME) = '2012'
		GROUP BY datename(month, CALLDATETIME ) + '-' + datename(year, CALLDATETIME)
	 ) temp
ON temp.call_date = datename(month, icd.CALLDATETIME ) + '-' + datename(year, icd.CALLDATETIME)
Go to Top of Page

mahesh.sanka
Starting Member

India
18 Posts
Posted - 03/26/2013 :  00:50:59  Show Profile  Reply with Quote
quote:
Originally posted by bandi

If you want E_count and L_Count based on only Month-YYYY, then use the following code

SELECT DISTINCT icd.VENDER_NAME, call_date, E_Count, L_Count
FROM INSR_CALL_DETAILS icd
JOIN ( SELECT datename(month, CALLDATETIME ) + '-' + datename(year, CALLDATETIME) 'call_date',
			COUNT(CASE WHEN CALLTYPE='E' THEN 1 END) 'E_Count',
			COUNT(CASE WHEN CALLTYPE='L' THEN 1 END) 'L_Count' 
		FROM  INSR_CALL_DETAILS 
		WHERE year(CALLDATETIME) = '2012'
		GROUP BY datename(month, CALLDATETIME ) + '-' + datename(year, CALLDATETIME)
	 ) temp
ON temp.call_date = datename(month, icd.CALLDATETIME ) + '-' + datename(year, icd.CALLDATETIME)


Thanks for your reply.
But now also i am getting wrong out put.

The above query combining the values of every vendor.

GanpatiEnterprises November-2012 0 2
GanpatiEnterprises October-2012 8 1
Qualtouch November-2012 0 2
Qualtouch October-2012 8 1

Thanks & Regards,
Mahesh Kumar Sanka
Edited by - mahesh.sanka on 03/26/2013 00:53:58
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2242 Posts
Posted - 03/26/2013 :  01:09:11  Show Profile  Reply with Quote
Can you show us the sample data with different VenderNames and also how will you get to know above is incorrect result? Tell me the logic behind it...

Only you need E, L count based on Year or Month-Year? or any thing else..........

Do you need other columns(except E_COUNT, L_COUNT, Call_date) in the SELECT ?
Go to Top of Page

mahesh.sanka
Starting Member

India
18 Posts
Posted - 03/26/2013 :  01:19:06  Show Profile  Reply with Quote
quote:
Originally posted by bandi

Can you show us the sample data with different VenderNames and also how will you get to know above is incorrect result? Tell me the logic behind it...

Only you need E, L count based on Year or Month-Year? or any thing else..........

Do you need other columns(except E_COUNT, L_COUNT, Call_date) in the SELECT ?


This is my table Structure

AND COLUMN IS CALL_TYPE where i am storing either 'E' or 'L'

CREATE TABLE [dbo].[INSR_CALL_DETAILS](
[CUSTOMER_ID] [varchar](40) NULL,
[POLICY_NO] [varchar](40) NULL,
[POLICY_DESC] [varchar](240) NULL,
[CALLDATETIME] [datetime] NULL,
[CALLTYPE] [varchar](1) NULL,
[AGENT_ID] [varchar](240) NOT NULL,
[VENDER_NAME] [varchar](240) NOT NULL,
[EXTENSION_DETAILS] [varchar](50) NULL,
[MOBILE_NO] [varchar](75) NULL,
[MEDIA_PATH] [varchar](1000) NULL,
[CMP_CODE] [varchar](10) NULL,
[CREATED_BY] [varchar](10) NULL,
[CREATED_TIME] [datetime] NULL,
[MODIFIED_BY] [varchar](10) NULL,
[MODIFIED_TIME] [datetime] NULL,
[APPLICATION_NUMBER] [varchar](30) NULL,
[UNIQUE_ID] [varchar](300) NULL,
[PRODUCT_NAME] [varchar](3000) NULL,
[CALL_TYPE] [char](1) NULL,
[CLI_INDV_SUR_NM] [nvarchar](max) NULL,
[CLI_INDV_MID_NM] [nvarchar](max) NULL,
[CLI_INDV_GIV_NM] [nvarchar](max) NULL,
[CVG_MAT_XPRY_DT] [datetime] NULL,
[BSLI_APP_BR_RECV_DT] [datetime] NULL,
[status] [varchar](240) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

Scripts for Insertion

INSERT [dbo].[INSR_CALL_DETAILS] ([CUSTOMER_ID], [POLICY_NO], [POLICY_DESC], [CALLDATETIME], [CALLTYPE], [AGENT_ID], [VENDER_NAME], [EXTENSION_DETAILS], [MOBILE_NO], [MEDIA_PATH], [CMP_CODE], [CREATED_BY], [CREATED_TIME], [MODIFIED_BY], [MODIFIED_TIME], [APPLICATION_NUMBER], [UNIQUE_ID], [PRODUCT_NAME], [CALL_TYPE], [CLI_INDV_SUR_NM], [CLI_INDV_MID_NM], [CLI_INDV_GIV_NM], [CVG_MAT_XPRY_DT], [BSLI_APP_BR_RECV_DT], [status]) VALUES (N'BODHRAJTARGOTRA', N'005820319 ', NULL, CAST(0x0000A0DD00000000 AS DateTime), N'O', N'QUAEND', N'Qualtouch', NULL, N'9848421860', N'//Qualtouch//End To End Process//2012//10//01//A47148143.wav', N'C008', N'Admin', CAST(0x0000A16A00FB5FE0 AS DateTime), N'Admin', CAST(0x0000A17200B5905A AS DateTime), N'A47148143', N'Z00000 ', N'BSLI Vision Plan - GSB Pay 20 ', N'E', N'BODH', N'RAJ', N'TARGOTRA', CAST(0x0000A73D00000000 AS DateTime), CAST(0x0000A0FB00000000 AS DateTime), NULL)
INSERT [dbo].[INSR_CALL_DETAILS] ([CUSTOMER_ID], [POLICY_NO], [POLICY_DESC], [CALLDATETIME], [CALLTYPE], [AGENT_ID], [VENDER_NAME], [EXTENSION_DETAILS], [MOBILE_NO], [MEDIA_PATH], [CMP_CODE], [CREATED_BY], [CREATED_TIME], [MODIFIED_BY], [MODIFIED_TIME], [APPLICATION_NUMBER], [UNIQUE_ID], [PRODUCT_NAME], [CALL_TYPE], [CLI_INDV_SUR_NM], [CLI_INDV_MID_NM], [CLI_INDV_GIV_NM], [CVG_MAT_XPRY_DT], [BSLI_APP_BR_RECV_DT], [status]) VALUES (N'DINESHKUMAR', N'005813521 ', NULL, CAST(0x0000A0DD00000000 AS DateTime), N'O', N'QUAEND', N'Qualtouch', NULL, N'9848022338', N'//Qualtouch//End To End Process//2012//10//01//A47148161.wav', N'C008', N'Admin', CAST(0x0000A16A00FB611A AS DateTime), N'Admin', CAST(0x0000A178011CE0BF AS DateTime), N'A47148161', N'Z00000 ', N'BSLI Vision Plan - GSB Pay 20 ', N'E', N'DINESH', N'KUMAR', NULL, CAST(0x00010ED700000000 AS DateTime), CAST(0x0000A0F700000000 AS DateTime), NULL)
INSERT [dbo].[INSR_CALL_DETAILS] ([CUSTOMER_ID], [POLICY_NO], [POLICY_DESC], [CALLDATETIME], [CALLTYPE], [AGENT_ID], [VENDER_NAME], [EXTENSION_DETAILS], [MOBILE_NO], [MEDIA_PATH], [CMP_CODE], [CREATED_BY], [CREATED_TIME], [MODIFIED_BY], [MODIFIED_TIME], [APPLICATION_NUMBER], [UNIQUE_ID], [PRODUCT_NAME], [CALL_TYPE], [CLI_INDV_SUR_NM], [CLI_INDV_MID_NM], [CLI_INDV_GIV_NM], [CVG_MAT_XPRY_DT], [BSLI_APP_BR_RECV_DT], [status]) VALUES (N'SATYAJITDAS', N'005826165 ', NULL, CAST(0x0000A0DD00000000 AS DateTime), N'O', N'QUAEND', N'Qualtouch', NULL, N'', N'//Qualtouch//End To End Process//2012//10//01//A47152911.wav', N'C008', N'Admin', CAST(0x0000A16A00FB6238 AS DateTime), NULL, NULL, N'A47152911', N'Z00000 ', N'BSLI Vision Plan - GSB Pay 20 ', N'E', N'SATYAJIT', NULL, N'DAS', CAST(0x0000FC5100000000 AS DateTime), CAST(0x0000A10000000000 AS DateTime), NULL)
INSERT [dbo].[INSR_CALL_DETAILS] ([CUSTOMER_ID], [POLICY_NO], [POLICY_DESC], [CALLDATETIME], [CALLTYPE], [AGENT_ID], [VENDER_NAME], [EXTENSION_DETAILS], [MOBILE_NO], [MEDIA_PATH], [CMP_CODE], [CREATED_BY], [CREATED_TIME], [MODIFIED_BY], [MODIFIED_TIME], [APPLICATION_NUMBER], [UNIQUE_ID], [PRODUCT_NAME], [CALL_TYPE], [CLI_INDV_SUR_NM], [CLI_INDV_MID_NM], [CLI_INDV_GIV_NM], [CVG_MAT_XPRY_DT], [BSLI_APP_BR_RECV_DT], [status]) VALUES (N'ANILKUMARSINGH', N'005840653 ', NULL, CAST(0x0000A0DD00000000 AS DateTime), N'O', N'QUAEND', N'Qualtouch', NULL, N'', N'//Qualtouch//End To End Process//2012//10//01//A47387908.wav', N'C008', N'Admin', CAST(0x0000A16A00FB6358 AS DateTime), NULL, NULL, N'A47387908', N'Z00000 ', N'BSLI Vision Plan - GSB Pay 20 ', N'E', N'ANIL', N'KUMAR', N'SINGH', CAST(0x0000FF3800000000 AS DateTime), CAST(0x0000A10C00000000 AS DateTime), NULL)
INSERT [dbo].[INSR_CALL_DETAILS] ([CUSTOMER_ID], [POLICY_NO], [POLICY_DESC], [CALLDATETIME], [CALLTYPE], [AGENT_ID], [VENDER_NAME], [EXTENSION_DETAILS], [MOBILE_NO], [MEDIA_PATH], [CMP_CODE], [CREATED_BY], [CREATED_TIME], [MODIFIED_BY], [MODIFIED_TIME], [APPLICATION_NUMBER], [UNIQUE_ID], [PRODUCT_NAME], [CALL_TYPE], [CLI_INDV_SUR_NM], [CLI_INDV_MID_NM], [CLI_INDV_GIV_NM], [CVG_MAT_XPRY_DT], [BSLI_APP_BR_RECV_DT], [status]) VALUES (N'PRONOTIASHISHLAHIRI', N'005858520 ', NULL, CAST(0x0000A10F00000000 AS DateTime), N'O', N'QUAELG', N'Qualtouch', NULL, N'', N'//Qualtouch//Lead Generation//2012//11//20//QUAEND1_11202012_Sachin.wav', N'C008', N'Admin', CAST(0x0000A16A00FF5F1F AS DateTime), NULL, NULL, N'A46852284 ', N'QUAEND1', N'BSLI Protector Plan - Term 14 Pay 14 ', N'L', N'PRONOTI', N'ASHISH', N'LAHIRI', CAST(0x0000A11A00000000 AS DateTime), CAST(0x0000A11900000000 AS DateTime), NULL)
INSERT [dbo].[INSR_CALL_DETAILS] ([CUSTOMER_ID], [POLICY_NO], [POLICY_DESC], [CALLDATETIME], [CALLTYPE], [AGENT_ID], [VENDER_NAME], [EXTENSION_DETAILS], [MOBILE_NO], [MEDIA_PATH], [CMP_CODE], [CREATED_BY], [CREATED_TIME], [MODIFIED_BY], [MODIFIED_TIME], [APPLICATION_NUMBER], [UNIQUE_ID], [PRODUCT_NAME], [CALL_TYPE], [CLI_INDV_SUR_NM], [CLI_INDV_MID_NM], [CLI_INDV_GIV_NM], [CVG_MAT_XPRY_DT], [BSLI_APP_BR_RECV_DT], [status]) VALUES (N'Prasad', N'007', NULL, CAST(0x0000A0DD00000000 AS DateTime), N'O', N'QUAELG', N'Qualtouch', NULL, N'9820696039', N'fdfd', N'C008', N'Admin', CAST(0x0000A15800000000 AS DateTime), NULL, NULL, N'DNSKLDN', N'FDLN', N'FNDL', N'L', N'FD', N'L', N'L', CAST(0x00009FCB00000000 AS DateTime), CAST(0x00009FCB00000000 AS DateTime), NULL)
INSERT [dbo].[INSR_CALL_DETAILS] ([CUSTOMER_ID], [POLICY_NO], [POLICY_DESC], [CALLDATETIME], [CALLTYPE], [AGENT_ID], [VENDER_NAME], [EXTENSION_DETAILS], [MOBILE_NO], [MEDIA_PATH], [CMP_CODE], [CREATED_BY], [CREATED_TIME], [MODIFIED_BY], [MODIFIED_TIME], [APPLICATION_NUMBER], [UNIQUE_ID], [PRODUCT_NAME], [CALL_TYPE], [CLI_INDV_SUR_NM], [CLI_INDV_MID_NM], [CLI_INDV_GIV_NM], [CVG_MAT_XPRY_DT], [BSLI_APP_BR_RECV_DT], [status]) VALUES (N'BODHRAJTARGOTRA', N'005820319 ', NULL, CAST(0x0000A0DD00000000 AS DateTime), N'O', N'GANEND', N'GanpatiEnterprises', NULL, N'', N'//Qualtouch//End To End Process//2012//10//01//A47148143.wav', N'C008', N'Admin', CAST(0x0000A16A00FB5FE0 AS DateTime), NULL, NULL, N'A47148143', N'Z00000 ', N'BSLI Vision Plan - GSB Pay 20 ', N'E', N'BODH', N'RAJ', N'TARGOTRA', CAST(0x0001104900000000 AS DateTime), CAST(0x0000A0FB00000000 AS DateTime), NULL)
INSERT [dbo].[INSR_CALL_DETAILS] ([CUSTOMER_ID], [POLICY_NO], [POLICY_DESC], [CALLDATETIME], [CALLTYPE], [AGENT_ID], [VENDER_NAME], [EXTENSION_DETAILS], [MOBILE_NO], [MEDIA_PATH], [CMP_CODE], [CREATED_BY], [CREATED_TIME], [MODIFIED_BY], [MODIFIED_TIME], [APPLICATION_NUMBER], [UNIQUE_ID], [PRODUCT_NAME], [CALL_TYPE], [CLI_INDV_SUR_NM], [CLI_INDV_MID_NM], [CLI_INDV_GIV_NM], [CVG_MAT_XPRY_DT], [BSLI_APP_BR_RECV_DT], [status]) VALUES (N'DINESHKUMAR', N'005813521 ', NULL, CAST(0x0000A0DD00000000 AS DateTime), N'O', N'GANEND', N'GanpatiEnterprises', NULL, N'', N'//Qualtouch//End To End Process//2012//10//01//A47148161.wav', N'C008', N'Admin', CAST(0x0000A16A00FB611A AS DateTime), NULL, NULL, N'A47148161', N'Z00000 ', N'BSLI Vision Plan - GSB Pay 20 ', N'E', N'DINESH', N'KUMAR', NULL, CAST(0x00010ED700000000 AS DateTime), CAST(0x0000A0F700000000 AS DateTime), NULL)
INSERT [dbo].[INSR_CALL_DETAILS] ([CUSTOMER_ID], [POLICY_NO], [POLICY_DESC], [CALLDATETIME], [CALLTYPE], [AGENT_ID], [VENDER_NAME], [EXTENSION_DETAILS], [MOBILE_NO], [MEDIA_PATH], [CMP_CODE], [CREATED_BY], [CREATED_TIME], [MODIFIED_BY], [MODIFIED_TIME], [APPLICATION_NUMBER], [UNIQUE_ID], [PRODUCT_NAME], [CALL_TYPE], [CLI_INDV_SUR_NM], [CLI_INDV_MID_NM], [CLI_INDV_GIV_NM], [CVG_MAT_XPRY_DT], [BSLI_APP_BR_RECV_DT], [status]) VALUES (N'SATYAJITDAS', N'005826165 ', NULL, CAST(0x0000A0DD00000000 AS DateTime), N'O', N'GANEND', N'GanpatiEnterprises', NULL, N'', N'//Qualtouch//End To End Process//2012//10//01//A47152911.wav', N'C008', N'Admin', CAST(0x0000A16A00FB6238 AS DateTime), NULL, NULL, N'A47152911', N'Z00000 ', N'BSLI Vision Plan - GSB Pay 20 ', N'E', N'SATYAJIT', NULL, N'DAS', CAST(0x0000FC5100000000 AS DateTime), CAST(0x0000A10000000000 AS DateTime), NULL)
INSERT [dbo].[INSR_CALL_DETAILS] ([CUSTOMER_ID], [POLICY_NO], [POLICY_DESC], [CALLDATETIME], [CALLTYPE], [AGENT_ID], [VENDER_NAME], [EXTENSION_DETAILS], [MOBILE_NO], [MEDIA_PATH], [CMP_CODE], [CREATED_BY], [CREATED_TIME], [MODIFIED_BY], [MODIFIED_TIME], [APPLICATION_NUMBER], [UNIQUE_ID], [PRODUCT_NAME], [CALL_TYPE], [CLI_INDV_SUR_NM], [CLI_INDV_MID_NM], [CLI_INDV_GIV_NM], [CVG_MAT_XPRY_DT], [BSLI_APP_BR_RECV_DT], [status]) VALUES (N'ANILKUMARSINGH', N'005840653 ', NULL, CAST(0x0000A0DD00000000 AS DateTime), N'O', N'QUAEND', N'GanpatiEnterprises', NULL, N'', N'//Qualtouch//End To End Process//2012//10//01//A47387908.wav', N'C008', N'Admin', CAST(0x0000A16A00FB6358 AS DateTime), NULL, NULL, N'A47387908', N'Z00000 ', N'BSLI Vision Plan - GSB Pay 20 ', N'E', N'ANIL', N'KUMAR', N'SINGH', CAST(0x0000FF3800000000 AS DateTime), CAST(0x0000A10C00000000 AS DateTime), NULL)
INSERT [dbo].[INSR_CALL_DETAILS] ([CUSTOMER_ID], [POLICY_NO], [POLICY_DESC], [CALLDATETIME], [CALLTYPE], [AGENT_ID], [VENDER_NAME], [EXTENSION_DETAILS], [MOBILE_NO], [MEDIA_PATH], [CMP_CODE], [CREATED_BY], [CREATED_TIME], [MODIFIED_BY], [MODIFIED_TIME], [APPLICATION_NUMBER], [UNIQUE_ID], [PRODUCT_NAME], [CALL_TYPE], [CLI_INDV_SUR_NM], [CLI_INDV_MID_NM], [CLI_INDV_GIV_NM], [CVG_MAT_XPRY_DT], [BSLI_APP_BR_RECV_DT], [status]) VALUES (N'PRONOTIASHISHLAHIRI', N'005858520 ', NULL, CAST(0x0000A10F00000000 AS DateTime), N'O', N'GANELG', N'GanpatiEnterprises', NULL, N'', N'//Qualtouch//Lead Generation//2012//11//20//QUAEND1_11202012_Sachin.wav', N'C008', N'Admin', CAST(0x0000A16A00FF5F1F AS DateTime), NULL, NULL, N'A46852284 ', N'QUAEND1', N'BSLI Protector Plan - Term 14 Pay 14 ', N'L', N'PRONOTI', N'ASHISH', N'LAHIRI', CAST(0x0000A11A00000000 AS DateTime), CAST(0x0000A11900000000 AS DateTime), NULL)

Thanks & Regards,
Mahesh Kumar Sanka
Edited by - mahesh.sanka on 03/26/2013 01:31:18
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2242 Posts
Posted - 03/26/2013 :  01:36:51  Show Profile  Reply with Quote
Expected output?
Count based on [month-YYYY] or ([month-YYYY] and Vendername)?
Go to Top of Page

mahesh.sanka
Starting Member

India
18 Posts
Posted - 03/26/2013 :  02:23:59  Show Profile  Reply with Quote
quote:
Originally posted by bandi

Expected output?
Count based on [month-YYYY] or ([month-YYYY] and Vendername)?


I want count based on ([month-YYYY] and Vendername)

The problem is solved


SELECT VENDER_NAME,datename(month, CALLDATETIME ) + '-' + datename(year, CALLDATETIME) 'call_date',
			COUNT(CASE  CALL_TYPE WHEN 'E' THEN 1 END ) 'E_Count',
			COUNT(CASE  CALL_TYPE when 'L' THEN 1 END) 'L_Count' 
		FROM  INSR_CALL_DETAILS 
		WHERE year(CALLDATETIME) = '2012'
		GROUP BY VENDER_NAME,datename(month, CALLDATETIME ) + '-' + datename(year, CALLDATETIME)


Thanks all of you guys. I have modified your original query only

Thanks & Regards,
Mahesh Kumar Sanka
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2242 Posts
Posted - 03/26/2013 :  02:33:50  Show Profile  Reply with Quote
Already we have suggested above query at ( bandi Posted - 03/25/2013 : 09:22:30)....

Be specific in the explanation whenever you want query so that you can save lot of time....
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000