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

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Pulling the Data from the table for year wise

Author  Topic 

mahesh.sanka
Starting Member

18 Posts

Posted - 2013-03-25 : 08:06:15

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

737 Posts

Posted - 2013-03-25 : 08:42:30
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

18 Posts

Posted - 2013-03-25 : 08:53:18
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-25 : 09:06:20
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

18 Posts

Posted - 2013-03-25 : 09:10:25
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

18 Posts

Posted - 2013-03-25 : 09:20:30
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.

[CODE]
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))


[/CODE]

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

18 Posts

Posted - 2013-03-25 : 09:21:12
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-25 : 09:22:30
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

18 Posts

Posted - 2013-03-26 : 00:12:59
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-26 : 00:38:29
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

18 Posts

Posted - 2013-03-26 : 00:50:59
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
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-26 : 01:09:11
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

18 Posts

Posted - 2013-03-26 : 01:19:06
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
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-26 : 01:36:51
Expected output?
Count based on [month-YYYY] or ([month-YYYY] and Vendername)?
Go to Top of Page

mahesh.sanka
Starting Member

18 Posts

Posted - 2013-03-26 : 02:23:59
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-26 : 02:33:50
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
   

- Advertisement -