Problem with my Sql Query - SQL Server Forums

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

Problem with my Sql Query

Author

Topic

kathyc2003
Starting Member

15 Posts

Posted - 2008-03-01 : 09:43:56

Hello,

I have to updat this query that was given to me so that it displays only items that were created in a certain month and certain year (which I prompt the user for). I hard-coded a month and year to
test (which isn't working).

But my results are coming out as follows:

2008 1 Incoming 1 0 0 0 02008 1 Both 1 0 0 0 0
2008 2 Outgoing 394 0 0 0 02008 2 Incoming 88 0 0 0 02008

I tried to add a where clause in marked with /* problem 1 */
and /* problem 2 */
that checked to see if date fields were null etc.

I can for the life of me figure out how to fix this up.

query below...

================================

DECLARE @TheYear integer
DECLARE @TheMonth integer

SET @TheYear = 2008
SET @TheMonth = 2

SELECT * FROM (
SELECT
year(startime) as yearstart,
month(startime) as monthstart,
directioncodename

, count(CASE

WHEN new_issuecategoryname is null

THEN activitycountvalue

END ) as nullcall

, count(CASE

WHEN new_issuecategoryname='ACDelco Comment'

THEN activitycountvalue

END ) as acdelcocommentcall

, count(CASE

WHEN

new_issuecategoryname='ACDelco General Inquiry'

THEN activitycountvalue

END ) as acdgeneralinquirycall

, count(CASE

WHEN

new_issuecategoryname='ACDelco PPD'

THEN activitycountvalue

END ) as acdppdcall

, count(CASE

WHEN

new_issuecategoryname='ACDelco Promotion'

THEN activitycountvalue

END ) as acdpromotioncall

, count(CASE

WHEN

new_issuecategoryname='ACDelco Promotion - Calendar'

THEN activitycountvalue

END ) as acdpromotioncalendarcall

, count(CASE

WHEN

new_issuecategoryname='ACDelco TSS Program Enquiry'

THEN activitycountvalue

END ) as tssprogramenquirycall

, count(CASE

WHEN

new_issuecategoryname='ACDelco TSS Promotion'

THEN activitycountvalue

END ) as tsspromotioncall

, count(CASE

WHEN

new_issuecategoryname='ACDelco Training'

THEN activitycountvalue

END ) as acdtrainingcall

, count(CASE

WHEN

new_issuecategoryname='ACDelco TSS Benefits'

THEN activitycountvalue

END ) as tssbenefitscall

, count(CASE

WHEN

new_issuecategoryname='ACDelco Other'

THEN activitycountvalue

END ) as acdothercall

, count(CASE

WHEN

new_issuecategoryname='ACDelco TAC Number'

THEN activitycountvalue

END ) as acdtacnumbercall

, count(CASE

WHEN

new_issuecategoryname='ACDelco PPD Promotion'

THEN activitycountvalue

END ) as acdppdpromotioncall

, count(CASE

WHEN

new_issuecategoryname='RealRewards - ISC'

THEN activitycountvalue

END ) as realrewardsisccall

, count(CASE

WHEN

new_issuecategoryname='RealRewards - CounterPerson'

THEN activitycountvalue

END ) as realrewardscounterpersonscall

, count(CASE

WHEN

new_issuecategoryname='ACDelco Event'

THEN activitycountvalue

END ) as acdelcoeventcall

, count(CASE

WHEN

new_issuecategoryname='ACDelco TSS Lead'

THEN activitycountvalue

END ) as acdtssleadcall

, count(CASE

WHEN

new_issuecategoryname='ACDelco TSS Other'

THEN activitycountvalue

END ) as acdtssothercall

, count(CASE

WHEN new_issuecategoryname is not null

and new_issuecategoryname!='ACDelco Comment'

and new_issuecategoryname!='ACDelco General Inquiry'

and new_issuecategoryname!='ACDelco PPD'

and new_issuecategoryname!='ACDelco Promotion'

and new_issuecategoryname!='ACDelco Promotion - Calendar'

and new_issuecategoryname!='ACDelco TSS Program Enquiry'

and new_issuecategoryname!='ACDelco TSS Promotion'

and new_issuecategoryname!='ACDelco Training'

and new_issuecategoryname!='ACDelco TSS Benefits'

and new_issuecategoryname!='ACDelco Other'

and new_issuecategoryname!='ACDelco TAC Number'

and new_issuecategoryname!='ACDelco PPD Promotion'

and new_issuecategoryname!='RealRewards - ISC'

and new_issuecategoryname!='RealRewards - CounterPerson'

and new_issuecategoryname!='ACDelco Event'

and new_issuecategoryname!='ACDelco TSS Lead'

and new_issuecategoryname!='ACDelco TSS Other'

THEN activitycountvalue

END ) as othercall

,count(activitycountvalue) as totalcall

FROM (

select

startime =

CASE

WHEN filteredphonecall.new_cmgstartdatetime is not null

THEN filteredphonecall.new_cmgstartdatetime

WHEN filteredphonecall.actualstart is not null and filteredphonecall.new_cmgstartdatetime is null

THEN filteredphonecall.actualstart

ELSE

filteredphonecall.createdon

END

, 1 as activitycountvalue

, new_issuecategoryname

, new_issuecategory

, 'phone call' as activitytypecodename

, filteredphonecall.new_languagename

, filteredphonecall.directioncodename

from
/* PROBLEM */
filteredphonecall

WHERE
(
(
filteredphonecall.new_cmgstartdatetime is not null
AND
(Year(filteredphonecall.new_cmgstartdatetime) = @TheYear)
AND
(Month(filteredphonecall.new_cmgstartdatetime) = @TheMonth)
)

OR
(
filteredphonecall.actualstart is not null
AND
(Year(filteredphonecall.new_cmgstartdatetime) = @TheYear)
AND
(Month(filteredphonecall.new_cmgstartdatetime) = @TheMonth)
)

OR
(
filteredphonecall.actualstart is not null
AND
(Year(filteredphonecall.actualstart) = @TheYear)
AND
(Month(filteredphonecall.actualstart) = @TheMonth)
)

OR
(
filteredphonecall.createdon is not null
AND
(Year(filteredphonecall.createdon) = @TheYear)
AND
(Month(filteredphonecall.createdon) = @TheMonth)
)
)

)as phoneactivities

GROUP BY year(startime), month(startime), directioncodename

UNION ALL

/* KATHY1 */

SELECT year(startime) as yearstart

, month(startime) as monthstart

,'Both' as directioncodename

, count(CASE

WHEN new_issuecategoryname is null

THEN activitycountvalue

END ) as nullcall

, count(CASE

WHEN new_issuecategoryname='ACDelco Comment'

THEN activitycountvalue

END ) as acdelcocommentcall

, count(CASE

WHEN

new_issuecategoryname='ACDelco General Inquiry'

THEN activitycountvalue

END ) as acdgeneralinquirycall

, count(CASE

WHEN

new_issuecategoryname='ACDelco PPD'

THEN activitycountvalue

END ) as acdppdcall

, count(CASE

WHEN

new_issuecategoryname='ACDelco Promotion'

THEN activitycountvalue

END ) as acdpromotioncall

, count(CASE

WHEN

new_issuecategoryname='ACDelco Promotion - Calendar'

THEN activitycountvalue

END ) as acdpromotioncalendarcall

, count(CASE

WHEN

new_issuecategoryname='ACDelco TSS Program Enquiry'

THEN activitycountvalue

END ) as tssprogramenquirycall

, count(CASE

WHEN

new_issuecategoryname='ACDelco TSS Promotion'

THEN activitycountvalue

END ) as tsspromotioncall

, count(CASE

WHEN

new_issuecategoryname='ACDelco Training'

THEN activitycountvalue

END ) as acdtrainingcall

, count(CASE

WHEN

new_issuecategoryname='ACDelco TSS Benefits'

THEN activitycountvalue

END ) as tssbenefitscall

, count(CASE

WHEN

new_issuecategoryname='ACDelco Other'

THEN activitycountvalue

END ) as acdothercall

, count(CASE

WHEN

new_issuecategoryname='ACDelco TAC Number'

THEN activitycountvalue

END ) as acdtacnumbercall

, count(CASE

WHEN

new_issuecategoryname='ACDelco PPD Promotion'

THEN activitycountvalue

END ) as acdppdpromotioncall

, count(CASE

WHEN

new_issuecategoryname='RealRewards - ISC'

THEN activitycountvalue

END ) as realrewardsisccall

, count(CASE

WHEN

new_issuecategoryname='RealRewards - CounterPerson'

THEN activitycountvalue

END ) as realrewardscounterpersonscall

, count(CASE

WHEN

new_issuecategoryname='ACDelco Event'

THEN activitycountvalue

END ) as acdelcoeventcall

, count(CASE

WHEN

new_issuecategoryname='ACDelco TSS Lead'

THEN activitycountvalue

END ) as acdtssleadcall

, count(CASE

WHEN

new_issuecategoryname='ACDelco TSS Other'

THEN activitycountvalue

END ) as acdtssothercall

, count(CASE

WHEN new_issuecategoryname is not null

and new_issuecategoryname!='ACDelco Comment'

and new_issuecategoryname!='ACDelco General Inquiry'

and new_issuecategoryname!='ACDelco PPD'

and new_issuecategoryname!='ACDelco Promotion'

and new_issuecategoryname!='ACDelco Promotion - Calendar'

and new_issuecategoryname!='ACDelco TSS Program Enquiry'

and new_issuecategoryname!='ACDelco TSS Promotion'

and new_issuecategoryname!='ACDelco Training'

and new_issuecategoryname!='ACDelco TSS Benefits'

and new_issuecategoryname!='ACDelco Other'

and new_issuecategoryname!='ACDelco TAC Number'

and new_issuecategoryname!='ACDelco PPD Promotion'

and new_issuecategoryname!='RealRewards - ISC'

and new_issuecategoryname!='RealRewards - CounterPerson'

and new_issuecategoryname!='ACDelco Event'

and new_issuecategoryname!='ACDelco TSS Lead'

and new_issuecategoryname!='ACDelco TSS Other'

THEN activitycountvalue

END ) as othercall

,count(activitycountvalue) as totalcall

FROM (

select

startime =

CASE

WHEN filteredphonecall.new_cmgstartdatetime is not null

THEN filteredphonecall.new_cmgstartdatetime

WHEN filteredphonecall.actualstart is not null and filteredphonecall.new_cmgstartdatetime is null

THEN filteredphonecall.actualstart

ELSE

filteredphonecall.createdon

END

, 1 as activitycountvalue

, new_issuecategoryname

, new_issuecategory

, 'phone call' as activitytypecodename

, filteredphonecall.new_languagename

, filteredphonecall.directioncodename

from
filteredphonecall

/* PROBLEM */
filteredphonecall

WHERE
(
(
filteredphonecall.new_cmgstartdatetime is not null
AND
(Year(filteredphonecall.new_cmgstartdatetime) = @TheYear)
AND
(Month(filteredphonecall.new_cmgstartdatetime) = @TheMonth)
)

OR
(
filteredphonecall.actualstart is not null
AND
(Year(filteredphonecall.new_cmgstartdatetime) = @TheYear)
AND
(Month(filteredphonecall.new_cmgstartdatetime) = @TheMonth)
)

OR
(
filteredphonecall.actualstart is not null
AND
(Year(filteredphonecall.actualstart) = @TheYear)
AND
(Month(filteredphonecall.actualstart) = @TheMonth)
)

OR
(
filteredphonecall.createdon is not null
AND
(Year(filteredphonecall.createdon) = @TheYear)
AND
(Month(filteredphonecall.createdon) = @TheMonth)
)
)

)as phoneactivities

GROUP BY year(startime), month(startime)

) as orderedresults

order by yearstart, monthstart, directioncodename DESC;

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-01 : 10:52:39

Yikes...



DECLARE @TheYear integer
DECLARE @TheMonth integer

SET @TheYear = 2008
SET @TheMonth = 2


SELECT * 
FROM ( --open parenthesis for main tables
SELECT	
		 year(startime) as yearstart,month(startime) as monthstart,directioncodename	
		,SUM(isNull(new_issuecategorynamesum,1)) as nullcall
		,sum(CASE WHEN new_issuecategoryname='ACDelco Comment' THEN 1 else 0 end ) as acdelcocommentcall
		,sum(CASE  WHEN new_issuecategoryname='ACDelco General Inquiry' THEN 1 else 0 end  ) as acdgeneralinquirycall
		,sum(CASE WHEN  new_issuecategoryname='ACDelco PPD' THEN 1 else 0 end  ) as acdppdcall
		, sum(CASE WHEN new_issuecategoryname='ACDelco Promotion' THEN 1 else 0 end ) as acdpromotioncall
		, sum(CASE WHEN new_issuecategoryname='ACDelco Promotion - Calendar' THEN 1 else 0 end  ) as acdpromotioncalendarcall
		, sum(CASE WHEN new_issuecategoryname='ACDelco TSS Program Enquiry' THEN 1 else 0 end  ) as tssprogramenquirycall
		, sum(CASE WHEN new_issuecategoryname='ACDelco TSS Promotion' THEN 1 else 0 end  ) as tsspromotioncall
		, sum(CASE WHEN new_issuecategoryname='ACDelco Training' THEN 1 else 0 end  ) as acdtrainingcall
		, sum(CASE WHEN new_issuecategoryname='ACDelco TSS Benefits' THEN 1 else 0 end  ) as tssbenefitscall
		, sum(CASE WHEN new_issuecategoryname='ACDelco Other' THEN 1 else 0 end  ) as acdothercall
		, sum(CASE WHEN new_issuecategoryname='ACDelco TAC Number' THEN 1 else 0 end ) as acdtacnumbercall
		, sum(CASE WHEN new_issuecategoryname='ACDelco PPD Promotion' THEN 1 else 0 end ) as acdppdpromotioncall
		, sum(CASE WHEN new_issuecategoryname='RealRewards - ISC' THEN 1 else 0 end ) as realrewardsisccall
		, sum(CASE WHEN new_issuecategoryname='RealRewards - sumerPerson' THEN 1 else 0 end ) as realrewardssumerpersonscall
		, sum(CASE WHEN new_issuecategoryname='ACDelco Event' THEN 1 else 0 end  ) as acdelcoeventcall
		, sum(CASE WHEN new_issuecategoryname='ACDelco TSS Lead' THEN 1 else 0 end ) as acdtssleadcall
		, sum(CASE WHEN new_issuecategoryname='ACDelco TSS Other' THEN 1 else 0 END ) as acdtssothercall
		, sum(CASE WHEN new_issuecategoryname is not null 
					and new_issuecategoryname not in ('ACDelco Comment','ACDelco General Inquiry',
													'ACDelco PPD','ACDelco Promotion',
													'ACDelco Promotion - Calendar',
													'ACDelco TSS Program Enquiry','ACDelco TSS Promotion','ACDelco Training','ACDelco TSS Benefits',
													'ACDelco Other','ACDelco TAC Number','ACDelco PPD Promotion','RealRewards - ISC',
													'RealRewards - sumerPerson','ACDelco Event','ACDelco TSS Lead','ACDelco TSS Other')
					THEN 1 ELSE 0 END ) as othercall
		,sum(activitysumvalue) as totalcall
		FROM		(select startime = CASE WHEN filteredphonecall.new_cmgstartdatetime is not null	THEN filteredphonecall.new_cmgstartdatetime
											WHEN filteredphonecall.actualstart is not null and 
											 filteredphonecall.new_cmgstartdatetime is null THEN filteredphonecall.actualstart
									    ELSE filteredphonecall.createdon END 
							, new_issuecategoryname
							, new_issuecategory
							, 'phone call' as activitytypecodename
							, filteredphonecall.new_languagename
							, filteredphonecall.directioncodename
						from   filteredphonecall

						WHERE (
						(filteredphonecall.new_cmgstartdatetime is not null AND (Year(filteredphonecall.new_cmgstartdatetime) = @TheYear) AND (Month(filteredphonecall.new_cmgstartdatetime) = @TheMonth))
						OR (filteredphonecall.actualstart is not null AND (Year(filteredphonecall.new_cmgstartdatetime) = @TheYear) AND (Month(filteredphonecall.new_cmgstartdatetime) = @TheMonth) )
						OR (filteredphonecall.actualstart is not null AND (Year(filteredphonecall.actualstart) = @TheYear) AND 	(Month(filteredphonecall.actualstart) = @TheMonth))
						OR (filteredphonecall.createdon is not null AND (Year(filteredphonecall.createdon) = @TheYear) AND (Month(filteredphonecall.createdon) = @TheMonth))
								)) firstresults--this table needed a name
GROUP BY year(startime),month(startime) ,directioncodename	
	
	UNION ALL
/* KATHY1 */

				SELECT year(startime) as yearstart, month(startime) as monthstart,'Both' as directioncodename
				, count(CASE WHEN new_issuecategoryname is null THEN activitycountvalue END ) as nullcall
				, count(CASE WHEN new_issuecategoryname='ACDelco Comment' THEN activitycountvalue END ) as acdelcocommentcall
				, count(CASE WHEN new_issuecategoryname='ACDelco General Inquiry'THEN activitycountvalue END ) as acdgeneralinquirycall
				, count(CASE WHEN new_issuecategoryname='ACDelco PPD' THEN activitycountvalue END ) as acdppdcall 
				, count(CASE WHEN new_issuecategoryname='ACDelco Promotion' THEN activitycountvalue END ) as acdpromotioncall
				, count(CASE WHEN new_issuecategoryname='ACDelco Promotion - Calendar' THEN activitycountvalue END ) as acdpromotioncalendarcall
				, count(CASE WHEN new_issuecategoryname='ACDelco TSS Program Enquiry' THEN activitycountvalue END ) as tssprogramenquirycall
				, count(CASE WHEN new_issuecategoryname='ACDelco TSS Promotion' THEN activitycountvalue  END ) as tsspromotioncall
				, count(CASE WHEN new_issuecategoryname='ACDelco Training' THEN activitycountvalue END ) as acdtrainingcall
				, count(CASE WHEN new_issuecategoryname='ACDelco TSS Benefits' THEN activitycountvalue END ) as tssbenefitscall
				, count(CASE WHEN new_issuecategoryname='ACDelco Other' THEN activitycountvalue END ) as acdothercall
				, count(CASE WHEN new_issuecategoryname='ACDelco TAC Number' THEN activitycountvalue END ) as acdtacnumbercall
				, count(CASE WHEN new_issuecategoryname='ACDelco PPD Promotion' THEN activitycountvalue END ) as acdppdpromotioncall
				, count(CASE WHEN new_issuecategoryname='RealRewards - ISC' THEN activitycountvalue END ) as realrewardsisccall
				, count(CASE WHEN new_issuecategoryname='RealRewards - CounterPerson' THEN activitycountvalue END ) as realrewardscounterpersonscall
				, count(CASE WHEN new_issuecategoryname='ACDelco Event' THEN activitycountvalue END ) as acdelcoeventcall
				, count(CASE WHEN new_issuecategoryname='ACDelco TSS Lead' THEN activitycountvalue END ) as acdtssleadcall
				, count(CASE WHEN new_issuecategoryname='ACDelco TSS Other' THEN activitycountvalue END ) as acdtssothercall
				, count(CASE WHEN new_issuecategoryname is not null
								and new_issuecategoryname not in ('ACDelco Comment','ACDelco General Inquiry',
																	'ACDelco PPD','ACDelco Promotion',
																	'ACDelco Promotion - Calendar',
																	'ACDelco TSS Program Enquiry','ACDelco TSS Promotion','ACDelco Training','ACDelco TSS Benefits',
																	'ACDelco Other','ACDelco TAC Number','ACDelco PPD Promotion','RealRewards - ISC',
																	'RealRewards - CounterPerson','ACDelco Event','ACDelco TSS Lead','ACDelco TSS Other')
													THEN activitycountvalue END ) as othercall
				,count(activitycountvalue) as totalcall

			  FROM				(select startime =CASE WHEN filteredphonecall.new_cmgstartdatetime is not null THEN filteredphonecall.new_cmgstartdatetime
										 WHEN filteredphonecall.actualstart is not null and filteredphonecall.new_cmgstartdatetime is null
											THEN filteredphonecall.actualstart ELSE filteredphonecall.createdon END
								, 1 as activitycountvalue
								, new_issuecategoryname
								, new_issuecategory
								, 'phone call' as activitytypecodename
								, filteredphonecall.new_languagename
								, filteredphonecall.directioncodename

								from filteredphonecall 

								WHERE(
								( filteredphonecall.new_cmgstartdatetime is not null AND (Year(filteredphonecall.new_cmgstartdatetime) = @TheYear) AND (Month(filteredphonecall.new_cmgstartdatetime) = @TheMonth))
								OR (filteredphonecall.actualstart is not null AND (Year(filteredphonecall.new_cmgstartdatetime) = @TheYear) AND (Month(filteredphonecall.new_cmgstartdatetime) = @TheMonth))
								OR (filteredphonecall.actualstart is not null AND (Year(filteredphonecall.actualstart) = @TheYear) 	AND (Month(filteredphonecall.actualstart) = @TheMonth))
								OR (filteredphonecall.createdon is not null AND (Year(filteredphonecall.createdon) = @TheYear) AND (Month(filteredphonecall.createdon) = @TheMonth))) 
								GROUP BY year(startime), month(startime)) as orderedresults 
)  AS maintable --close parenthesis for main tables
order by yearstart, monthstart, directioncodename DESC;

reworked some of this. You can use SUM(Case..when then 1 else 0 end) instead of having a 1 as [ColumnName] and "Counting that"

Problem was in certain parenthetical references and locations, referring to column names that didn't exist in the alias or derived query...this checks out by syntax...but couldn't do a real test without data.

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources