SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 why are my variables null?
 New Topic  Topic Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

DeanT
Starting Member

USA
13 Posts

Posted - 03/20/2012 :  15:37:09  Show Profile
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

Edited by - DeanT on 03/20/2012 15:41:15

Lamprey
Flowing Fount of Yak Knowledge

4354 Posts

Posted - 03/20/2012 :  15:38:52  Show Profile
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.
Go to Top of Page

DeanT
Starting Member

USA
13 Posts

Posted - 03/20/2012 :  15:40:29  Show Profile
ok so I've initialized them to zero and now I get zero, why don't my counters increment?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 03/20/2012 :  15:43:47  Show Profile
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/

Go to Top of Page

DeanT
Starting Member

USA
13 Posts

Posted - 03/20/2012 :  15:44:39  Show Profile
show me
Go to Top of Page

DeanT
Starting Member

USA
13 Posts

Posted - 03/20/2012 :  15:46:59  Show Profile
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=109309
this is wrong
Go to Top of Page

DeanT
Starting Member

USA
13 Posts

Posted - 03/20/2012 :  15:48:39  Show Profile
if your too lazy to post code then don't post at all
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 03/20/2012 :  15:51:18  Show Profile

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



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/

Go to Top of Page

Vassago
Starting Member

33 Posts

Posted - 03/20/2012 :  15:51:53  Show Profile
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 03/20/2012 :  15:54:47  Show Profile
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/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 03/20/2012 :  15:55:49  Show Profile
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/


Edited by - visakh16 on 03/20/2012 15:56:21
Go to Top of Page

DeanT
Starting Member

USA
13 Posts

Posted - 03/20/2012 :  15:56:26  Show Profile
then voluntarily do it right and stop being such an anuss
Go to Top of Page

DeanT
Starting Member

USA
13 Posts

Posted - 03/20/2012 :  15:58:44  Show Profile
your answers are wrong sorry
Go to Top of Page

DeanT
Starting Member

USA
13 Posts

Posted - 03/20/2012 :  16:00:20  Show Profile
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
Go to Top of Page

Vassago
Starting Member

33 Posts

Posted - 03/20/2012 :  16:00:42  Show Profile
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 03/20/2012 :  16:01:31  Show Profile
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/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 03/20/2012 :  16:02:13  Show Profile
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/

Go to Top of Page

DeanT
Starting Member

USA
13 Posts

Posted - 03/20/2012 :  16:09:51  Show Profile
you act the way you want your the jerks
Go to Top of Page

Vassago
Starting Member

33 Posts

Posted - 03/20/2012 :  16:12:41  Show Profile
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 03/20/2012 :  16:14:03  Show Profile  Visit tkizer's Homepage
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Topic Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000