Any unique datetime's in SQL2005?

Once upon a time, some version of SQLServer was supposed to do
timestamps as datetimes, so you could have a guaranteed unique token
that decoded to a real date/time. I believe this was supposed to be
the ANSI standard for SQL as well.
Has this yet been delivered in SQL2005?
(I think not, but am not certain, and would like to have it for
something I'm doing right now! I should have SQL2005 loaded here in a
few days, but thanks in advance for any answer to this. btw, I don't
see the SQL2005 documentation yet online at MSDN.microsoft.com)
J.No.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:quqpo15ejq4biu6cgsg2nkiedr92dinok1@.
4ax.com...
> Once upon a time, some version of SQLServer was supposed to do
> timestamps as datetimes, so you could have a guaranteed unique token
> that decoded to a real date/time. I believe this was supposed to be
> the ANSI standard for SQL as well.
> Has this yet been delivered in SQL2005?
> (I think not, but am not certain, and would like to have it for
> something I'm doing right now! I should have SQL2005 loaded here in a
> few days, but thanks in advance for any answer to this. btw, I don't
> see the SQL2005 documentation yet online at MSDN.microsoft.com)
> J.
>

Any unique datetime's in SQL2005?

Once upon a time, some version of SQLServer was supposed to do
timestamps as datetimes, so you could have a guaranteed unique token
that decoded to a real date/time. I believe this was supposed to be
the ANSI standard for SQL as well.
Has this yet been delivered in SQL2005?
(I think not, but am not certain, and would like to have it for
something I'm doing right now! I should have SQL2005 loaded here in a
few days, but thanks in advance for any answer to this. btw, I don't
see the SQL2005 documentation yet online at MSDN.microsoft.com)
J.No. :(
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:quqpo15ejq4biu6cgsg2nkiedr92dinok1@.4ax.com...
> Once upon a time, some version of SQLServer was supposed to do
> timestamps as datetimes, so you could have a guaranteed unique token
> that decoded to a real date/time. I believe this was supposed to be
> the ANSI standard for SQL as well.
> Has this yet been delivered in SQL2005?
> (I think not, but am not certain, and would like to have it for
> something I'm doing right now! I should have SQL2005 loaded here in a
> few days, but thanks in advance for any answer to this. btw, I don't
> see the SQL2005 documentation yet online at MSDN.microsoft.com)
> J.
>

Any unique datetime's in SQL2005?

Once upon a time, some version of SQLServer was supposed to do
timestamps as datetimes, so you could have a guaranteed unique token
that decoded to a real date/time. I believe this was supposed to be
the ANSI standard for SQL as well.
Has this yet been delivered in SQL2005?
(I think not, but am not certain, and would like to have it for
something I'm doing right now! I should have SQL2005 loaded here in a
few days, but thanks in advance for any answer to this. btw, I don't
see the SQL2005 documentation yet online at MSDN.microsoft.com)
J.
No.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:quqpo15ejq4biu6cgsg2nkiedr92dinok1@.4ax.com...
> Once upon a time, some version of SQLServer was supposed to do
> timestamps as datetimes, so you could have a guaranteed unique token
> that decoded to a real date/time. I believe this was supposed to be
> the ANSI standard for SQL as well.
> Has this yet been delivered in SQL2005?
> (I think not, but am not certain, and would like to have it for
> something I'm doing right now! I should have SQL2005 loaded here in a
> few days, but thanks in advance for any answer to this. btw, I don't
> see the SQL2005 documentation yet online at MSDN.microsoft.com)
> J.
>

Any significant differences between these two date CAST statements

I need to find records that have a date stamp of yesterday. I have created two where clauses that use CAST and DATETIME. Both seem to return the same results. Is either one better?

CAST(FLOOR(CAST(cc.Date AS float)) AS datetime) = CAST(FLOOR(CAST((getdate()-1) AS float)) AS datetime)

-- OR --

cast (round(cast(cc.Date as float),0,1) as datetime) = cast (round(cast((getdate()-1) as float),0,1) as datetime)

BTW: Oracle handles this easily as trunc(mydate)

Thanks in advance

Oh Using SQLServer 2005

Doug

www.cooltimbers.com

It looks to me like they will both work:

declare @.morningDt datetime set @.morningDt = '3/13/7 0:05'
declare @.afternoonDt datetime set @.afternoonDt = '3/13/7 23:59'

select cast(round(cast(@.morningDt as float),0,1) as datetime),
cast(round(cast(@.afternoonDt as float),0,1) as datetime),
round(cast(@.morningDt as float),0,1) ,
round(cast(@.afternoonDt as float),0,1)

-- -- - -
2007-03-13 00:00:00.000 2007-03-13 00:00:00.000 39152.0 39152.0

I am more used to seeing the FLOOR version.

|||

Thanks Kent for the prompt reply... and for your help in general!

I sure do wish Microsoft implements something similar to Oracles "TRUNC" function that nicely truncates to a day.

Any ideas on this one ?

I have a Datetime parameter from a dataset. It also shows the time but I
only need the date, is it possible somehow to change how it displays the
parameter. As what I can see it is only possible to choose datetime format.
I tried to convert it like this convert(datetime,period,105) as dateonly and
then use this field but it shows exactly the same !
JackI ended up writing a sql function to do this.
Basically the function subtracts the hours , minutes and seconds using
the DateAdd statement. There vb equivalents to this to - look up date
functions in BOL.
Chris
Jack Nielsen wrote:
> I have a Datetime parameter from a dataset. It also shows the time
> but I only need the date, is it possible somehow to change how it
> displays the parameter. As what I can see it is only possible to
> choose datetime format. I tried to convert it like this
> convert(datetime,period,105) as dateonly and then use this field but
> it shows exactly the same !
> Jack|||Could this be used somehow, and if how do I show this new field in the
Parameter and still use the datetime field in the sql statement ?
USE Northwind
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id =object_id(N'[dbo].[udf_MyDate]') and xtype = N'FN')
DROP FUNCTION [dbo].[udf_MyDate]
GO
CREATE FUNCTION udf_MyDate (@.indate datetime, @.separator char(1))
RETURNS Nchar(20)
AS
BEGIN
RETURN
CONVERT(Nvarchar(20), datepart(mm,@.indate))
+ @.separator
+ CONVERT(Nvarchar(20), datepart(dd, @.indate))
+ @.separator
+ CONVERT(Nvarchar(20), datepart(yy, @.indate))
END
GO
This is the statement where i include the parameter
HAVING (DEBSTAT.DATASET = @.Regnskab) AND (DEBSTAT.PERIODESTART >
@.periodestartparm) AND (DEBSTAT.PERIODESTART < @.periodeslutparm)
And it now shows like this 12/01/2005 00:00:00 in the report parameter,
would like it only to show 12/01/2005 but still using a real datetime format
so that the statement still works.
Jack
> I ended up writing a sql function to do this.
> Basically the function subtracts the hours , minutes and seconds using
> the DateAdd statement. There vb equivalents to this to - look up date
> functions in BOL.
> Chris
> Jack Nielsen wrote:
> > I have a Datetime parameter from a dataset. It also shows the time
> > but I only need the date, is it possible somehow to change how it
> > displays the parameter. As what I can see it is only possible to
> > choose datetime format. I tried to convert it like this
> > convert(datetime,period,105) as dateonly and then use this field but
> > it shows exactly the same !
> >
> > Jack
>|||Can you not just return the datetime from the dataset and format it in the
layout of the report using an expression (=Format(Fields!dateTimeField.Value,
"MMM dd, yyyy"))? Am I missing something in your requirements?
MKD
"Jack Nielsen" wrote:
> Could this be used somehow, and if how do I show this new field in the
> Parameter and still use the datetime field in the sql statement ?
> USE Northwind
> GO
> IF EXISTS (SELECT * FROM sysobjects WHERE id => object_id(N'[dbo].[udf_MyDate]') and xtype = N'FN')
> DROP FUNCTION [dbo].[udf_MyDate]
> GO
> CREATE FUNCTION udf_MyDate (@.indate datetime, @.separator char(1))
> RETURNS Nchar(20)
> AS
> BEGIN
> RETURN
> CONVERT(Nvarchar(20), datepart(mm,@.indate))
> + @.separator
> + CONVERT(Nvarchar(20), datepart(dd, @.indate))
> + @.separator
> + CONVERT(Nvarchar(20), datepart(yy, @.indate))
> END
> GO
> This is the statement where i include the parameter
> HAVING (DEBSTAT.DATASET = @.Regnskab) AND (DEBSTAT.PERIODESTART >
> @.periodestartparm) AND (DEBSTAT.PERIODESTART < @.periodeslutparm)
> And it now shows like this 12/01/2005 00:00:00 in the report parameter,
> would like it only to show 12/01/2005 but still using a real datetime format
> so that the statement still works.
> Jack
> > I ended up writing a sql function to do this.
> > Basically the function subtracts the hours , minutes and seconds using
> > the DateAdd statement. There vb equivalents to this to - look up date
> > functions in BOL.
> >
> > Chris
> >
> > Jack Nielsen wrote:
> >
> > > I have a Datetime parameter from a dataset. It also shows the time
> > > but I only need the date, is it possible somehow to change how it
> > > displays the parameter. As what I can see it is only possible to
> > > choose datetime format. I tried to convert it like this
> > > convert(datetime,period,105) as dateonly and then use this field but
> > > it shows exactly the same !
> > >
> > > Jack
> >
>
>|||Ducky,
This is a date from a dataset, being used to populate a parameter list.
This is why we have to be a bit more convaluted.
Jack,
In your dataset do something like this;
Select MyDate, dbo.udf_MyDate(MyDate, '/') As Label From etc ...
In your parameter set MyDate as the Value and Label as the Label.
Chris
ducky wrote:
> Can you not just return the datetime from the dataset and format it
> in the layout of the report using an expression
> (=Format(Fields!dateTimeField.Value, "MMM dd, yyyy"))? Am I missing
> something in your requirements?
> MKD
>
> "Jack Nielsen" wrote:
> > Could this be used somehow, and if how do I show this new field in
> > the Parameter and still use the datetime field in the sql statement
> > ?
> >
> > USE Northwind
> > GO
> > IF EXISTS (SELECT * FROM sysobjects WHERE id => > object_id(N'[dbo].[udf_MyDate]') and xtype = N'FN')
> > DROP FUNCTION [dbo].[udf_MyDate]
> > GO
> > CREATE FUNCTION udf_MyDate (@.indate datetime, @.separator char(1))
> > RETURNS Nchar(20)
> > AS
> > BEGIN
> > RETURN
> > CONVERT(Nvarchar(20), datepart(mm,@.indate))
> > + @.separator
> > + CONVERT(Nvarchar(20), datepart(dd, @.indate))
> > + @.separator
> > + CONVERT(Nvarchar(20), datepart(yy, @.indate))
> > END
> > GO
> >
> > This is the statement where i include the parameter
> > HAVING (DEBSTAT.DATASET = @.Regnskab) AND (DEBSTAT.PERIODESTART
> > > @.periodestartparm) AND (DEBSTAT.PERIODESTART < @.periodeslutparm)
> >
> > And it now shows like this 12/01/2005 00:00:00 in the report
> > parameter, would like it only to show 12/01/2005 but still using a
> > real datetime format so that the statement still works.
> >
> > Jack
> >
> > > I ended up writing a sql function to do this.
> > > Basically the function subtracts the hours , minutes and seconds
> > > using the DateAdd statement. There vb equivalents to this to -
> > > look up date functions in BOL.
> > >
> > > Chris
> > >
> > > Jack Nielsen wrote:
> > >
> > > > I have a Datetime parameter from a dataset. It also shows the
> > > > time but I only need the date, is it possible somehow to change
> > > > how it displays the parameter. As what I can see it is only
> > > > possible to choose datetime format. I tried to convert it like
> > > > this convert(datetime,period,105) as dateonly and then use this
> > > > field but it shows exactly the same !
> > > >
> > > > Jack
> > >
> >
> >
> >|||If your RS parameter is set to a string, the original
"convert(datetime,period,105)" should give you the listing you desire. When
this parameter is then passed to SQL it "should" automatically be recognized
as a date, but if not, you could pass the parameter as as string, and then
declare and set a new SQL parameter to the cast(@.param as datetime).
"Chris McGuigan" <chris.mcguigan@.zycko.com> wrote in message
news:eP03NtUiFHA.2180@.TK2MSFTNGP15.phx.gbl...
> Ducky,
> This is a date from a dataset, being used to populate a parameter list.
> This is why we have to be a bit more convaluted.
> Jack,
> In your dataset do something like this;
> Select MyDate, dbo.udf_MyDate(MyDate, '/') As Label From etc ...
> In your parameter set MyDate as the Value and Label as the Label.
> Chris
>
> ducky wrote:
>> Can you not just return the datetime from the dataset and format it
>> in the layout of the report using an expression
>> (=Format(Fields!dateTimeField.Value, "MMM dd, yyyy"))? Am I missing
>> something in your requirements?
>> MKD
>>
>> "Jack Nielsen" wrote:
>> > Could this be used somehow, and if how do I show this new field in
>> > the Parameter and still use the datetime field in the sql statement
>> > ?
>> >
>> > USE Northwind
>> > GO
>> > IF EXISTS (SELECT * FROM sysobjects WHERE id =>> > object_id(N'[dbo].[udf_MyDate]') and xtype = N'FN')
>> > DROP FUNCTION [dbo].[udf_MyDate]
>> > GO
>> > CREATE FUNCTION udf_MyDate (@.indate datetime, @.separator char(1))
>> > RETURNS Nchar(20)
>> > AS
>> > BEGIN
>> > RETURN
>> > CONVERT(Nvarchar(20), datepart(mm,@.indate))
>> > + @.separator
>> > + CONVERT(Nvarchar(20), datepart(dd, @.indate))
>> > + @.separator
>> > + CONVERT(Nvarchar(20), datepart(yy, @.indate))
>> > END
>> > GO
>> >
>> > This is the statement where i include the parameter
>> > HAVING (DEBSTAT.DATASET = @.Regnskab) AND (DEBSTAT.PERIODESTART
>> > > @.periodestartparm) AND (DEBSTAT.PERIODESTART < @.periodeslutparm)
>> >
>> > And it now shows like this 12/01/2005 00:00:00 in the report
>> > parameter, would like it only to show 12/01/2005 but still using a
>> > real datetime format so that the statement still works.
>> >
>> > Jack
>> >
>> > > I ended up writing a sql function to do this.
>> > > Basically the function subtracts the hours , minutes and seconds
>> > > using the DateAdd statement. There vb equivalents to this to -
>> > > look up date functions in BOL.
>> > >
>> > > Chris
>> > >
>> > > Jack Nielsen wrote:
>> > >
>> > > > I have a Datetime parameter from a dataset. It also shows the
>> > > > time but I only need the date, is it possible somehow to change
>> > > > how it displays the parameter. As what I can see it is only
>> > > > possible to choose datetime format. I tried to convert it like
>> > > > this convert(datetime,period,105) as dateonly and then use this
>> > > > field but it shows exactly the same !
>> > > >
>> > > > Jack
>> > >
>> >
>> >
>> >
>|||> If your RS parameter is set to a string, the original
> "convert(datetime,period,105)" should give you the listing you desire.
When
> this parameter is then passed to SQL it "should" automatically be
recognized
> as a date, but if not, you could pass the parameter as as string, and then
> declare and set a new SQL parameter to the cast(@.param as datetime).
This doesn't seem to work, if I set it to string and do the convert it still
shows the time.
I'm trying to do it the other way around but get syntax error, what could be
wrong here:
CREATE FUNCTION udf_MyDate (@.indate datetime, @.separator char(1))
RETURNS Nchar(20)
AS
BEGIN
RETURN
CONVERT(Nvarchar(20), datepart(mm,@.indate))
+ @.separator
+ CONVERT(Nvarchar(20), datepart(dd, @.indate))
+ @.separator
+ CONVERT(Nvarchar(20), datepart(yy, @.indate))
END
GO
SELECT DISTINCT PERIODESTART, DAY(PERIODESTART) AS Expr1, [dbo].[udf_MyDate]
(periodestart,'/') AS pstart
FROM DEBSTAT
WHERE (DAY(PERIODESTART) <> '31')
ORDER BY PERIODESTART
DROP FUNCTION [dbo].[udf_MyDate]
Jack

Any ideas on this one ?

If your RS parameter is set to a string, the original
> "convert(datetime,period,105)" should give you the listing you desire.
When
> this parameter is then passed to SQL it "should" automatically be
recognized
> as a date, but if not, you could pass the parameter as as string, and then
> declare and set a new SQL parameter to the cast(@.param as datetime).
This doesn't seem to work, if I set it to string and do the convert it still
shows the time.
I'm trying to do it the other way around but get syntax error, what could be
wrong here:
CREATE FUNCTION udf_MyDate (@.indate datetime, @.separator char(1))
RETURNS Nchar(20)
AS
BEGIN
RETURN
CONVERT(Nvarchar(20), datepart(mm,@.indate))
+ @.separator
+ CONVERT(Nvarchar(20), datepart(dd, @.indate))
+ @.separator
+ CONVERT(Nvarchar(20), datepart(yy, @.indate))
END
GO
SELECT DISTINCT PERIODESTART, DAY(PERIODESTART) AS Expr1, [dbo].[udf_MyDate]
(periodestart,'/') AS pstart
FROM DEBSTAT
WHERE (DAY(PERIODESTART) <> '31')
ORDER BY PERIODESTART
DROP FUNCTION [dbo].[udf_MyDate]
JackJack, recommend you check Books on Line. Here's what it says:"Using CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )"
Style is very useful here; you may need to experiment with it until you get
the output you want. By using this, you won't need a function. You can use
the convert statement in your query.
I hope this helps you.
Henry Robinette
"Jack Nielsen" wrote:
> If your RS parameter is set to a string, the original
> > "convert(datetime,period,105)" should give you the listing you desire.
> When
> > this parameter is then passed to SQL it "should" automatically be
> recognized
> > as a date, but if not, you could pass the parameter as as string, and then
> > declare and set a new SQL parameter to the cast(@.param as datetime).
> This doesn't seem to work, if I set it to string and do the convert it still
> shows the time.
> I'm trying to do it the other way around but get syntax error, what could be
> wrong here:
> CREATE FUNCTION udf_MyDate (@.indate datetime, @.separator char(1))
> RETURNS Nchar(20)
> AS
> BEGIN
> RETURN
> CONVERT(Nvarchar(20), datepart(mm,@.indate))
> + @.separator
> + CONVERT(Nvarchar(20), datepart(dd, @.indate))
> + @.separator
> + CONVERT(Nvarchar(20), datepart(yy, @.indate))
> END
> GO
> SELECT DISTINCT PERIODESTART, DAY(PERIODESTART) AS Expr1, [dbo].[udf_MyDate]
> (periodestart,'/') AS pstart
> FROM DEBSTAT
> WHERE (DAY(PERIODESTART) <> '31')
> ORDER BY PERIODESTART
> DROP FUNCTION [dbo].[udf_MyDate]
> Jack
>
>

any help with my select ?

Hello All,
I have a simple table with 2 columns.
datetime int
27.05.04 1
29.05.04 2
31.05.04 5
and i need to get a view that looks like this below :
27.05.04 1 1
29.05.04 2 3
31.05.04 5 8
The third column shows a sum of numbers that were display before present one
...
I was trying build stored proc however I'm not so familiar with SQL so I
failed.. :-(
can anybody help ?
thx in advance
Krzysiek
Don't multi-post. See my answer in .programming.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Krzysiek" <krzysiek79@.hotmail.com> wrote in message
news:c9fq0j$25cf$1@.news2.ipartners.pl...
Hello All,
I have a simple table with 2 columns.
datetime int
27.05.04 1
29.05.04 2
31.05.04 5
and i need to get a view that looks like this below :
27.05.04 1 1
29.05.04 2 3
31.05.04 5 8
The third column shows a sum of numbers that were display before present one
...
I was trying build stored proc however I'm not so familiar with SQL so I
failed.. :-(
can anybody help ?
thx in advance
Krzysiek

any help with my select ?

Hello All,
I have a simple table with 2 columns.
datetime int
27.05.04 1
29.05.04 2
31.05.04 5
and i need to get a view that looks like this below :
27.05.04 1 1
29.05.04 2 3
31.05.04 5 8
The third column shows a sum of numbers that were display before present one
..
I was trying build stored proc however I'm not so familiar with SQL so I
failed.. :-(
can anybody help ?
thx in advance
KrzysiekDon't multi-post. See my answer in .programming.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Krzysiek" <krzysiek79@.hotmail.com> wrote in message
news:c9fq0j$25cf$1@.news2.ipartners.pl...
Hello All,
I have a simple table with 2 columns.
datetime int
27.05.04 1
29.05.04 2
31.05.04 5
and i need to get a view that looks like this below :
27.05.04 1 1
29.05.04 2 3
31.05.04 5 8
The third column shows a sum of numbers that were display before present one
..
I was trying build stored proc however I'm not so familiar with SQL so I
failed.. :-(
can anybody help ?
thx in advance
Krzysiek

any help with my select ?

Hello All,
I have a simple table with 2 columns.
datetime int
27.05.04 1
29.05.04 2
31.05.04 5
and i need to get a view that looks like this below :
27.05.04 1 1
29.05.04 2 3
31.05.04 5 8
The third column shows a sum of numbers that were display before present one
..
I was trying build stored proc however I'm not so familiar with SQL so I
failed.. :-(
can anybody help ?
thx in advance
KrzysiekDon't multi-post. See my answer in .programming.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Krzysiek" <krzysiek79@.hotmail.com> wrote in message
news:c9fq0j$25cf$1@.news2.ipartners.pl...
Hello All,
I have a simple table with 2 columns.
datetime int
27.05.04 1
29.05.04 2
31.05.04 5
and i need to get a view that looks like this below :
27.05.04 1 1
29.05.04 2 3
31.05.04 5 8
The third column shows a sum of numbers that were display before present one
..
I was trying build stored proc however I'm not so familiar with SQL so I
failed.. :-(
can anybody help ?
thx in advance
Krzysiek

Any clue to make this query run faster

Any help would be really appreciated...
My stored procedure...

CREATE PROCEDURE business3rd7
@.Fromdate DATETIME,
@.ToDate DATETIME
AS

select distinct CONVERT(VARCHAR(10),Receipts.Companynumber1)+CONVE RT(VARCHAR(10),Receipts.Companynumber2) as co ,
Receipts.Premium1+Receipts.Premium2 as Premium,
"CAN"=case when Receipts.transactiontype='CAN'
then (receipts.premium1+receipts.premium2)
else 0
end,
"NET"=Receipts.Premium1+Receipts.Premium2-case when Receipts.transactiontype='CAN'
then (receipts.premium1+receipts.premium2)
else 0
end,

"#NEW"=case when Receipts.transactiontype='NEW' then count(Receipts.policynumber) else
0 end,

---
"$NEW"=
case when Receipts.transactiontype='NEW' then (Receipts.Premium1+Receipts.premium2)
else 0 end,
"#REN"=case when Receipts.transactiontype='REN' then count(Receipts.policynumber) else
0 end,
"$RENEW"=
case when Receipts.transactiontype='REN' then (Receipts.Premium1+Receipts.premium2)
else 0 end,
"#AP"=case when Receipts.transactiontype='AP' then count(Receipts.policynumber) else 0
end,
"$AP"=
case when Receipts.transactiontype='AP' then (Receipts.Premium1+Receipts.premium2) else
0 end,
"#SENT"=case when policy.Renewalofferdate between @.Fromdate AND @.ToDate then
count(policy.policynumber) end,

"%"=case when case when Receipts.transactiontype='REN' then count(Receipts.policynumber)else 0
end =0 then 0
when case when policy.Renewalofferdate between @.Fromdate AND @.ToDate then
count(policy.policynumber) else 0 end=0 then 0
else
case when Receipts.transactiontype='REN' then count(Receipts.policynumber)else 0
end /case when policy.Renewalofferdate between @.Fromdate AND @.ToDate then
count(policy.policynumber) else 0 end end,

"Current Year"= case when policy.Renewalofferdate between @.Fromdate AND @.ToDate then
count(clubamount) end,
"Previous Year"=case when policy.Renewalofferdate between DateAdd(year, -1, @.Fromdate)
AND DateAdd(year, -1, @.ToDate) then count(clubamount) end,
"#AA"=count(receipts.clubamount),
"$AA"=sum(receipts.clubamount)

FROM Receipts,Policy
where Receipts.Agencyid=Policy.Agentid
group by
Receipts.CompanyNumber1,Receipts.CompanyNumber2,
Receipts.Premium1,Receipts.Premium2,
Receipts.TransactionType,policy.Renewalofferdate,
Receipts.Agencyid

GO

Query plan...

-------Query Plan

|--Sort(DISTINCT ORDER BY:([Expr1008] ASC, [Expr1009] ASC, [Expr1010] ASC, [Expr1011] ASC, [Expr1012] ASC, [Expr1013] ASC, [Expr1014] ASC, [Expr1015] ASC, [Expr1016] ASC, [Expr1017] ASC, [Expr1018] ASC, [Expr1019] ASC, [Expr1020] ASC, [Expr1021] ASC, [Ex
|--Compute Scalar(DEFINE:([Expr1008]=Convert([Receipts].[CompanyNumber1])+Convert([Receipts].[CompanyNumber2]), [Expr1009]=[Receipts].[Premium1]+[Receipts].[Premium2], [Expr1010]=If ([Receipts].[TransactionType]='CAN') then ([Receipts].[Premium1]+[R
|--Compute Scalar(DEFINE:([Expr1004]=Convert([Expr1076]), [Expr1005]=Convert([Expr1077]), [Expr1006]=Convert([Expr1078]), [Expr1007]=If ([Expr1078]=0) then NULL else [Expr1079]))
|--Stream Aggregate(GROUP BY:([Receipts].[CompanyNumber1], [Receipts].[CompanyNumber2], [Receipts].[Premium1], [Receipts].[Premium2], [Receipts].[TransactionType], [Policy].[RenewalOfferDate], [Receipts].[AgencyID]) DEFINE:([Expr1076]=COUN
|--Sort(ORDER BY:([Receipts].[CompanyNumber1] ASC, [Receipts].[CompanyNumber2] ASC, [Receipts].[Premium1] ASC, [Receipts].[Premium2] ASC, [Receipts].[TransactionType] ASC, [Policy].[RenewalOfferDate] ASC, [Receipts].[AgencyID] ASC))
|--Hash Match(Inner Join, HASH:([Policy].[AgentID])=([Receipts].[AgencyID]), RESIDUAL:([Policy].[AgentID]=[Receipts].[AgencyID]))
|--Table Scan(OBJECT:([gasInquiry].[dbo].[Policy]))
|--Table Scan(OBJECT:([gasInquiry].[dbo].[Receipts]))

\

The two tables has number of records as 13349 and 97032.It taking more than 30 mins...
Any way to make it faster...well distinct sucks.

you have 2 table scans. I always use the graphical execution plan, but if these are big query costs, you may want to index the columns involved.|||well distinct sucks.

you have 2 table scans. I always use the graphical execution plan, but if these are big query costs, you may want to index the columns involved.

Thank you Thrasymachus.My guess was right...yea one of the culprit is that two table scan let me create some index on those columns and I have to think over that distinct again.|||Lol - I didn't even know you could use distinct and group by in a single query. In any case - this makes distinct doubly unnecessary.|||Yea,thats my mistake ..I overlooked that...well after creating the index on the columns not a significant change has resulted.
...:S

Any clue...|||A where clause?|||A where clause?
I didnt get you,I can't use where clause with @.fromdate and @.todate ,bcoz I am doing the calculation of previous year also.
well, I think I have to consider the whole query in a different approach...|||where did you create your indices?|||I mean that it is usually pretty rare to need every record from one table and all related records from another. If you need them all then you need them all but point was - do you?|||I mean that it is usually pretty rare to need every record from one table and all related records from another. If you need them all then you need them all but point was - do you?

No,I don't need all records of one table only those records which satisfy the datefrom clause, and then I want some records of other table which is related to this table by agentid.
Clear?|||where did you create your indices?

I have created the clustered and non clustered index on the columns on both tables which are invloved in this query.ok?|||ummm, are they on the columns causing your scans? are your scans a large percentage of your query cost? how big are the tables involved?|||ummm, are they on the columns causing your scans? are your scans a large percentage of your query cost? how big are the tables involved?

well, I checked my estiimated execution plan and found that the sort is taking the 96% cost...I mean the grouping cost 96%.
Any clue?
The tables are having data of 13349 and 97032 respectively,its a testing databse so few data are there...|||Ok, I've taken some liberties trying to get to what I think you want. Some columns are just plain missing because they made no sense to me. The result set is quite differently arranged in that what was once one row has become many rows based on transaction type. This isn't an "apples to apples" comparison, but I think it does what I think you really want, and will actually be easier for you to use!SELECT
Policy.AgentID
, Receipts.CompanyNumber1
, Receipts.CompanyNumber2
, Receipts.transactiontype
, Count(*) AS c
, Sum(Receipts.premium1 + Receipts.premium2) AS dollars
, Count(Receipts.clubamount) AS "AA#"
, Sum(Receipts.clubamount) AS "AA$"
FROM Receipts
JOIN Policy
ON (policy.agentID = Receipts.AgentID)
WHERE policy.renewalofferdate BETWEEN @.Fromdate AND @.ToDate
GROUP BY Policy.AgentID
, Receipts.Company1
, Receipts.Company2
, Receipts.TransactionType-PatP|||This query still not working ...:(
I am trying all things but for some strange reason its not working...
SEE the modified code which contains the crosstab operations...
The two tables are Policy and Receipts tables...

CREATE TABLE [Policy] (

[PolicyNumber] [nvarchar] (10) ,
[PolicySequence] [smallint] NULL ,
[Name] [nvarchar] (40) ,

--there are many columns here and I am are not mentioning those

[AgentID] [int] NULL ,
[RenewalOfferDate] [smalldatetime] NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [Receipts] (
[RecordType] [nchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AgencyID] [int] NULL ,
[ContractNumber] NULL ,
[MonthlyPayment] [money] NULL ,
[PaymentType] [nchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PolicyNumber] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PolicySequence] [smallint] NOT NULL ,
[ReceiptNumber] [bigint] NULL ,
[TransactionType] [nchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InsuredName] [nchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DownPayment] [money] NULL ,
[ClubAmount] [money] NULL ,
[ServiceFee] [money] NULL ,
[MVRFee] [money] NULL ,
[SR22Fee] [money] NULL ,
[AgentInitial] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProcessedInitial] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[QuotedInitial] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Source] [nchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CompanyNumber1] [int] NULL ,
[PolicyNumber1] [nchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Premium1] [money] NULL ,
[CompanyNumber2] [int] NULL ,
[PolicyNumber2] [nchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Premium2] [money] NULL ,
[TotalAmount] [money] NULL ,
[HowPaid] [nchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HowPaidCode] [nchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AmountTendered] [money] NULL ,
[Change] [money] NULL ,
[DateEntered] [datetime] NULL ,
[Void] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VoidDate] [datetime] NULL ,
[VoidBy] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RequestVoidBy] [nchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ReasonVoid] [nchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

Here is the code which ran really fast...

@.Fromdate DATETIME,
@.ToDate DATETIME
set @.fromdate='1/1/2006'
set @.todate='12/1/2006'

SELECT
Policy.AgentID
, Receipts.CompanyNumber1
, Receipts.CompanyNumber2
, Receipts.transactiontype
, Count(*) AS c
, Sum(Receipts.premium1 + Receipts.premium2) AS dollars
, Count(Receipts.clubamount) AS "AA#"
, Sum(Receipts.clubamount) AS "AA$"

FROM Receipts
JOIN Policy
ON (Policy.Agentid=Receipts.Agencyid)
WHERE policy.renewalofferdate BETWEEN @.Fromdate AND @.ToDate
GROUP BY Policy.AgentID
, Receipts.CompanyNumber1
, Receipts.CompanyNumber2
, Receipts.TransactionType

This is the modified code which is very slow one...

declare
@.Fromdate DATETIME,
@.ToDate DATETIME
set @.fromdate='1/1/2006'
set @.todate='12/1/2006'

SELECT
Policy.AgentID
, Receipts.CompanyNumber1
, Receipts.CompanyNumber2
, Receipts.transactiontype
, Count(*) AS c
, Sum(Receipts.premium1 + Receipts.premium2) AS dollars
, Count(Receipts.clubamount) AS "AA#"
, Sum(Receipts.clubamount) AS "AA$",

----- [B]These calculations are needed here

"CAN"=case when Receipts.transactiontype='CAN'
then (receipts.premium1+receipts.premium2)
else 0
end,
"NET"=Receipts.Premium1+Receipts.Premium2-case when Receipts.transactiontype='CAN'
then (receipts.premium1+receipts.premium2)
else 0
end,

"#NEW"=case when Receipts.transactiontype='NEW' then count(Receipts.policynumber) else
0 end,

---
"$NEW"=
case when Receipts.transactiontype='NEW' then (Receipts.Premium1+Receipts.premium2)
else 0 end,
"#REN"=case when Receipts.transactiontype='REN' then count(Receipts.policynumber) else
0 end,
"$RENEW"=
case when Receipts.transactiontype='REN' then (Receipts.Premium1+Receipts.premium2)
else 0 end,
"#AP"=case when Receipts.transactiontype='AP' then count(Receipts.policynumber) else 0
end,
"$AP"=
case when Receipts.transactiontype='AP' then (Receipts.Premium1+Receipts.premium2) else
0 end,
"#SENT"=case when policy.Renewalofferdate between @.Fromdate AND @.ToDate then
count(policy.policynumber) end,

"%"=case when case when Receipts.transactiontype='REN' then count(Receipts.policynumber)else 0
end =0 then 0
when case when policy.Renewalofferdate between @.Fromdate AND @.ToDate then
count(policy.policynumber) else 0 end=0 then 0
else
case when Receipts.transactiontype='REN' then count(Receipts.policynumber)else 0
end /case when policy.Renewalofferdate between @.Fromdate AND @.ToDate then
count(policy.policynumber) else 0 end end,

"Current Year"= case when policy.Renewalofferdate between @.Fromdate AND @.ToDate then
count(clubamount) end,
"Previous Year"=case when policy.Renewalofferdate between DateAdd(year, -1, @.Fromdate)
AND DateAdd(year, -1, @.ToDate) then count(clubamount) end

----

FROM Receipts
JOIN Policy
ON (Policy.Agentid=Receipts.Agencyid)
WHERE policy.renewalofferdate BETWEEN @.Fromdate AND @.ToDate
GROUP BY Policy.AgentID
, Receipts.CompanyNumber1
, Receipts.CompanyNumber2
, Receipts.TransactionType

--- three(3) more group by added

, Receipts.Premium1
, Receipts.Premium2
, Policy.Renewalofferdate

I have created neccessary Indices on the columns which are involed and are grouped.
I have created an index comprises of all the columns involed in the group except Policy.Renewalofferdate.

Any clue?