October 5, 2009 at 4:31 am
Dear all,
See the below query, i want to get the results of this query into one variable. u can find it in "select statement". Plz help me out to get the count into one variable. If there is another way then plz suggest.
Declare @cnt int
Declare @sid varchar(20), @qry varchar(1000)
Declare @dtTo varchar(30), @dtFrom varchar(30)
set @sid = '31'
set @dtTo = '2009-09-01'
set @dtTo = '2009-09-30'
Select @qry = 'Select @cnt = Count(Distinct dbo.CustomerMaster.CustId) FROM dbo.CustomerMaster Where
(Cast(floor(Cast(dbo.CustomerMaster.Transdate as float)) as datetime) >= ''' + @dtFrom + ''' and
Cast(floor(Cast(dbo.CustomerMaster.Transdate as float)) as datetime) <= ''' + @dtTo + ''')'
Exec(@qry)
Thanks in advance...
October 5, 2009 at 4:49 am
From the posted query it looks like the dynamic query may not be required. However if it is unavoidable to use a dynamic sql query, you can:
1. Declare a table variable:
Declare @tblVar TABLE (
ResultingCount int
)
2. Insert the results of the dynamic sql into table variable using
Insert Into @tblVar Exec (@qry)
3. Query the table variable
Select @cnt = ResultingCount From @tblVar
October 5, 2009 at 7:31 am
Hi Girish,
First-of-all thanks for reply..
Actually i use SQL Server 2000. so i can not use Table variable.
any ways, i use temporary table using yr solution. 🙂
Again thank u....
October 5, 2009 at 10:37 am
krish-529631 (10/5/2009)
Hi Girish,First-of-all thanks for reply..
Actually i use SQL Server 2000. so i can not use Table variable.
any ways, i use temporary table using yr solution. 🙂
Again thank u....
SQL 2000 supports table variables just fine.
October 5, 2009 at 4:49 pm
As Girish said, there's no need for a table variable, but you don't need a temp table either or any dynamic SQL at all.
Declare @sid varchar(20), @qry varchar(1000)
Declare @dtTo varchar(30), @dtFrom varchar(30)
set @sid = '31'
set @dtFrom = '2009-09-01'
set @dtTo = '2009-09-30'
Select @cnt = Count(Distinct dbo.CustomerMaster.CustId) FROM dbo.CustomerMaster Where
dbo.CustomerMaster.Transdate >= @dtFrom and
dbo.CustomerMaster.Transdate < DATEADD ( dd, 1, @dtTo ) -- Or maybe DateAdd ( mm, 1, @dtFrom ) if you are looking for monthly reports
SELECT @cnt -- To be returned by your statement/storedproc
Note this is untested, since you didn't supply any DDL or test data, and I am therefore assuming your TransDate column is typed as datetime, but I hope you get the idea. And of course assuming you meant to set @dtFrom to 1 September 2009 instead of assigning it to @dtTo. Also note that I changed the "<=" @dtTo to "< DATEADD ( dd, 1, @dtTo )" coz datetimes without the time portion are subject to the 3ms resolution of the datetime data type's resolution, which means that you may end up with 1 October at midnight and two milliseconds before that being counted twice once you run the same kind of query for October. May not be a big deal... Oh, by the way, where is the @sid variable being used?
October 6, 2009 at 12:11 am
Hello Jan Van der Eecken,
Thanks for reply...
Actually already tried your suggested query when i begin with one new module. Actually the flow is like that - the end user select 2 dates : DateFrom and DateTo as u also find it in my SQL Query.
so my query should be like this
Select @cnt = Count(Distinct dbo.CustomerMaster.CustId) FROM dbo.CustomerMaster Where
dbo.CustomerMaster.Transdate >= @dtFrom and
dbo.CustomerMaster.Transdate <= @dtTo
SELECT @cnt
Instead of below you suggest.
Select @cnt = Count(Distinct dbo.CustomerMaster.CustId) FROM dbo.CustomerMaster Where
dbo.CustomerMaster.Transdate >= @dtFrom and
dbo.CustomerMaster.Transdate <= DateAdd ( mm, 1, @dtFrom )
SELECT @cnt
I mean, it does not mean that your suggested query is wrong, but according to my requirement i can't use that.
OK...Now the problem is when i run the below query i get the 2988 Records
Declare @cnt varchar(20), @qry varchar(1000)
Declare @dtTo varchar(30), @dtFrom varchar(30)
set @dtFrom = '2009-09-01'
set @dtTo = '2009-09-30'
Select @cnt = Count(Distinct dbo.CustomerMaster.CustId) FROM dbo.CustomerMaster Where
dbo.CustomerMaster.Transdate >= @dtFrom and
dbo.CustomerMaster.Transdate <= @dtTo
SELECT @cnt
And after that when i run the below query by Casting my table column, I get the 3113 Records.
Select Count(Distinct dbo.CustomerMaster.CustId) FROM dbo.CustomerMaster Where
Cast(floor(Cast(dbo.CustomerMaster.Transdate as float)) as datetime) >= '2009-09-01' and
Cast(floor(Cast(dbo.CustomerMaster.Transdate as float)) as datetime) <= '2009-09-30'
As u told me in your reply that datetimes without the time portion are subject to the 3ms resolution of the datetime data type's resolution..
and may be that is why i get this Count difference..:w00t:
And bcs of this I write my sql query like this -
Select @qry = "Select ........"
Exec(@qry)
If there is another way to overcome this problem then plz suggest...
Thanks again for suggesting...and yes also thanks to share your knowledge with me...
Oh Yes, Actually @sid variable is a part of another table which is Inner join with my Customermaster table but then i remove that table from query but forgot to remove this variable...:-)
October 6, 2009 at 10:57 pm
krish-529631 (10/6/2009)
Instead of below you suggest.
Select @cnt = Count(Distinct dbo.CustomerMaster.CustId) FROM dbo.CustomerMaster Wheredbo.CustomerMaster.Transdate >= @dtFrom and
dbo.CustomerMaster.Transdate <= DateAdd ( mm, 1, @dtFrom )
SELECT @cnt
You did not read the suggestion carefully enough.
It should be:
Select @cnt = Count(Distinct dbo.CustomerMaster.CustId)
FROM dbo.CustomerMaster
Where
dbo.CustomerMaster.Transdate >= @dtFrom and
dbo.CustomerMaster.Transdate < DateAdd ( mm, 1, @dtFrom )
SELECT @cnt
_____________
Code for TallyGenerator
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply