why are my variables null? - 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

SQL Server 2008 Forums

Transact-SQL (2008)

why are my variables null?

Author

Topic

DeanT
Starting Member

13 Posts

Posted - 2012-03-20 : 15:37:09

Here is all my sql, why are my counter variables null?

declare @startdate datetime , @enddate datetime
select @startdate ='10/01/2011' , @enddate = '12/08/2011'
declare @csqname varchar(20)

declare @MondayCnt int=0,@TuesdayCnt int=0,@WednesdayCnt int=0,@ThursdayCnt int=0,@FridayCnt int=0,@SaturdayCnt int=0,@SundayCnt int=0

select distinct(ccd.startdatetime),cqd.disposition,
case
when datepart(DW, ccd.startdatetime) = 1 then @SundayCnt +1
when datepart(DW, ccd.startdatetime) = 2 then @Mondaycnt +1
when datepart(DW, ccd.startdatetime) = 3 then @TuesdayCnt +1
when datepart(DW, ccd.startdatetime) = 4 then @WednesdayCnt +1
when datepart(DW, ccd.startdatetime) = 5 then @ThursdayCnt +1
when datepart(DW, ccd.startdatetime) = 6 then @FridayCnt +1
when datepart(DW, ccd.startdatetime) = 7 then @SaturdayCnt +1
end
from dbo.contactcalldetail ccd
inner join contactqueuedetail cqd on ccd.sessionid = cqd.sessionid and ccd.sessionseqnum = cqd.sessionseqnum and cqd.profileid = ccd.profileid and cqd.nodeid = ccd.nodeid
where DATEADD("HOUR", -5,ccd.startdatetime) >= @startdate and DATEADD("HOUR", -5,ccd.startdatetime) <= @enddate + '23:59:59.000'

select @MondayCnt as Monday, @TuesdayCnt as Tuesday, @WednesdayCnt as Wednesday, @ThursdayCnt as Thursday, @FridayCnt as Friday, @SaturdayCnt as Saturday, @SundayCnt as Sunday

See me at
http://www.local-developers.com

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-03-20 : 15:38:52

Varaibles are null until you assign them a value. Since you didn't assign them a value adding 1 to a null value is null.

DeanT
Starting Member

13 Posts

Posted - 2012-03-20 : 15:40:29

ok so I've initialized them to zero and now I get zero, why don't my counters increment?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-20 : 15:43:47

quote:
Originally posted by DeanT

ok so I've initialized them to zero and now I get zero, why don't my counters increment?

because you're not assigning new values back. you've just included them in select but not assigned new values back to original variables

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DeanT
Starting Member

13 Posts

Posted - 2012-03-20 : 15:44:39

show me

DeanT
Starting Member

13 Posts

Posted - 2012-03-20 : 15:46:59

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=109309
this is wrong

DeanT
Starting Member

13 Posts

Posted - 2012-03-20 : 15:48:39

if your too lazy to post code then don't post at all

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-20 : 15:51:18

[code]
declare @startdate datetime , @enddate datetime
select @startdate ='10/01/2011' , @enddate = '12/08/2011'
declare @csqname varchar(20)

declare @MondayCnt int=0,@TuesdayCnt int=0,@WednesdayCnt int=0,@ThursdayCnt int=0,@FridayCnt int=0,@SaturdayCnt int=0,@SundayCnt int=0

select @MondayCnt =sum(MondayCnt),@TuesdayCnt =sum(TuesdayCnt),@WednesdayCnt =sum(WednesdayCnt),@ThursdayCnt =sum(ThursdayCnt),@FridayCnt =sum(FridayCnt),@SaturdayCnt =sum(SaturdayCnt),@SundayCnt =sum(SundayCnt)
from
(
select distinct(ccd.startdatetime),cqd.disposition,
case
when datepart(DW, ccd.startdatetime) = 1 then 1 end as SundayCnt,
when datepart(DW, ccd.startdatetime) = 2 then 1 end as MondayCnt,
when datepart(DW, ccd.startdatetime) = 3 then 1 end as TuesdayCnt,
when datepart(DW, ccd.startdatetime) = 4 then 1 end as WednesdayCnt,
when datepart(DW, ccd.startdatetime) = 5 then 1 end as ThursdayCnt,
when datepart(DW, ccd.startdatetime) = 6 then 1 end as FridayCnt,
when datepart(DW, ccd.startdatetime) = 7 then 1 end as SaturdayCnt
from dbo.contactcalldetail ccd
inner join contactqueuedetail cqd on ccd.sessionid = cqd.sessionid and ccd.sessionseqnum = cqd.sessionseqnum and cqd.profileid = ccd.profileid and cqd.nodeid = ccd.nodeid
where DATEADD("HOUR", -5,ccd.startdatetime) >= @startdate and DATEADD("HOUR", -5,ccd.startdatetime) <= @enddate + '23:59:59.000'
)t
select @MondayCnt as Monday, @TuesdayCnt as Tuesday, @WednesdayCnt as Wednesday, @ThursdayCnt as Thursday, @FridayCnt as Friday, @SaturdayCnt as Saturday, @SundayCnt as Sunday

[/code]
i didnt understand why you've select distinct and other columns inside. that didnt make sny sense to me

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Vassago
Starting Member

33 Posts

Posted - 2012-03-20 : 15:51:53

Plus, we can't tell what is in your original tables. I'm guessing something in your join is also wrong if you are getting zeros. I used a generic calendar table on my system and it came up with values of 1 with each date listed, since you are doing a distinct on that. I don't think your joins are right.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-20 : 15:54:47

quote:
Originally posted by DeanT

if your too lazy to post code then don't post at all

you sound as if you're paying the people here for providing you the help

please understand that people here are doing help voluntarily and have other main tasks also. Have the courtesy to appreciate the help got and also have attitude to apply yourselves based on guidelines given rather than waiting for spoonfed answers!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-20 : 15:55:49

quote:
Originally posted by DeanT

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=109309
this is wrong

who told
see how i'm doing it
i'm assigning it back to original variable which is what you missed!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DeanT
Starting Member

13 Posts

Posted - 2012-03-20 : 15:56:26

then voluntarily do it right and stop being such an anuss

DeanT
Starting Member

13 Posts

Posted - 2012-03-20 : 15:58:44

your answers are wrong sorry

DeanT
Starting Member

13 Posts

Posted - 2012-03-20 : 16:00:20

i didnt understand why you've select distinct and other columns inside. that didnt make sny sense to me
because smartass there are multiples startdatetime within the same day and I just want to count the day of weeks

Vassago
Starting Member

33 Posts

Posted - 2012-03-20 : 16:00:42

DeanT is probably a student looking for help with school work or something to slide through school. I would hate to think that a professional would act in this manner.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-20 : 16:01:31

quote:
Originally posted by DeanT

your answers are wrong sorry

Nobody will be able to help you unless you give them full picture. By this attitude you're not helping anyone even yourself!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-20 : 16:02:13

quote:
Originally posted by Vassago

DeanT is probably a student looking for help with school work or something to slide through school. I would hate to think that a professional would act in this manner.

exactly

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DeanT
Starting Member

13 Posts

Posted - 2012-03-20 : 16:09:51

you act the way you want your the jerks

Vassago
Starting Member

33 Posts

Posted - 2012-03-20 : 16:12:41

You're not setting your variables to the count you are trying to reach. You are also not doing anything to select a count in an aggregate function to set the variables to. Without these two steps, your variables will never stop being 0 because that is what you originally set them to without setting them again. Look up the set and count functions. That should give you a starting point.

The only one being rude in this thread is you, as far as I'm concerned.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-03-20 : 16:14:03

Lyle, you should consider not putting your real name in your profile if you are going to act so unprofessionally. I'll leave out your last name here as a simple google search has found you and future potential employers could come across this thread as googling one's name is very common these days before hiring someone.

Locking this thread due to Lyle's conduct here.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources