Create a view based on variables?

Hi All,

I would like to create a view based on a variable (a date).

CREATE VIEW testView (@.myDate)
AS

select * from testTable
WHERE testVar = @.myDate

--
This is far from what the final view would look like, but if anyone knows if something similiar to this can be done, I would greatly appreciate it.

Thanks.I would say "No, it can't." Why can't you just create the view without the where clause and select from it where the date = @.myDate when you need it? You could create a function that returns a table and takes the date as an argument.

Create a table with a field with only Time datatype

Hello experts,
I want to create a table to store only time in a fileld. There is "DateTime" for my purpose but i dont want to save the Date part only the time part as "12:30:44 AM". I know i can select only time part from Datetime field but i want to save only time.Can anybody help me how can i create that kinda table ?Hello experts,
I want to create a table to store only time in a fileld. There is "DateTime" for my purpose but i dont want to save the Date part only the time part as "12:30:44 AM". I know i can select only time part from Datetime field but i want to save only time.Can anybody help me how can i create that kinda table ?

See this enigma's post link (http://sqljunkies.com/Article/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk)
Its a good one ...|||i dont understand why you are not thinking in terms of a datetime/smalldatetime field by ignoring the date part and whats wrong in it. however, if you are rigid you can store it either as char field or as numeric field (float/decimal). remember you do not actually save anything by doing so.

storing as char field is straight forward. if you want to store as number (remember that each date+time has a numeric representation) can take hints from the following queries

--1 minute as number
select cast(cast('20060101 11:01 AM' as smalldatetime) as float)- cast(cast('20060101 11:00 AM' as smalldatetime) as float)
--it returns approx 0.0006944

--use that to get a date time 20060101 11:00 AM back
select cast( cast(cast('20060101' as smalldatetime) as float) + (0.0006944*60*11) as smalldatetime)

quite unnecessary...;-)sql

Create a table of temperature

Hello,

I don't know how to make this :

1 table containing list of cities
1 table containing date and temperature

both linked by IdCity

how can i create a table to display cities in rows, date in colums and temperature at intersections ?

Thanks !Hi,

as I get to know you, you want to join two tables. To do this you can use JOIN, RIGHT JOIN, LEFT JOIN in your SQL Statement.

it woul look like this ".... JOIN Cities ON Cieties.IdCity=Temperatures.IdCity
....."

look here for more about joins:
http://www.w3schools.com/sql/sql_join.asp|||Please take a look at the pivot operator in Books Online. You can generate the cross-tab type of report using that. This however requires knowing the values of date since that needs to be used in the pivot clause. ex:

select crp.CityName, crp.[20050101], crp.[20050102]
from Cities as c
join CityReadings as cr
on c.IdCity = cr.IdCity
pivot (max(cr.temperature) for cr.date in ([20050101], [20050102])) as crp
order by crp.CityName;
|||Ok but Pivot only works with SQL2K5 not with SQL2K ?sql

Create a new date

Hi,

I want to write a query that returns me the first date of the month...

I wrote this query

SELECT DateAdd(day,- Day(GetDate()) + 1,GetDate())

THis works fine for me, is there any function that build a new date, without using the DateAdd function?

here is another way

select dateadd(mm, datediff(mm, 0, getdate())+0, 0)

and another

select convert(datetime,(convert(varchar(6),getdate(),112) + '01'))

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Thanks

I used the second query :

select convert(datetime,(convert(varchar(6),getdate(),112) + '01'))

create a new column

HI,
I have a table ONE have the following two columns
[DATE] [datetime]
[NUM] [float]
I want to create a new column DATE1=DATE+NUM, date type is datetime.
Could anyone give me a help?
Thanks,
Mike
Hi,
I've tried the following code. But it did not work. Please let me help
me out. Thanks!
create table test
(
value float
)
insert into test
values(17188)
exec sp_help test
declare @.startdate datetime
set @.startdate='1/1/1960'
alter table test
add value1 as value+@.startdate
Mike
Michael wrote:
> HI,
> I have a table ONE have the following two columns
> [DATE] [datetime]
> [NUM] [float]
> I want to create a new column DATE1=DATE+NUM, date type is datetime.
> Could anyone give me a help?
> Thanks,
> Mike
|||Thanks a lot!
mladjo wrote:
> Hi.
> What do you want exactly with adding NUM to DATE? Can you get some example?
> Do you want to add day, month, year, or something else?
> I suppose you want something like this:
> create table ONE
> (
> [DATE] datetime,
> [NUM] float
> )
> INSERT INTO ONE values (getdate(), 2)
> INSERT INTO ONE values (getdate(), 3)
> SELECT [DATE],dateadd(dd, NUM, [DATE]) FROM ONE
>
> ALTER TABLE ONE
> ADD DATE1 AS dateadd(dd, NUM, [DATE])
> SELECT * FROM ONE
>
> If you want that your data in DATE1 column must be stored in your table (not
> just computed every time you working something with that column) use
> PERSISTED column as follows:
> ALTER TABLE ONE
> ADD DATE1 AS dateadd(dd, NUM, [DATE]) PERSISTED
> Mladen.

create a new column

HI,
I have a table ONE have the following two columns
[DATE] [datetime]
[NUM] [float]
I want to create a new column DATE1=DATE+NUM, date type is datetime.
Could anyone give me a help?
Thanks,
MikeHi.
What do you want exactly with adding NUM to DATE? Can you get some example?
Do you want to add day, month, year, or something else?
I suppose you want something like this:
create table ONE
(
[DATE] datetime,
[NUM] float
)
INSERT INTO ONE values (getdate(), 2)
INSERT INTO ONE values (getdate(), 3)
SELECT [DATE],dateadd(dd, NUM, [DATE]) FROM ONE
ALTER TABLE ONE
ADD DATE1 AS dateadd(dd, NUM, [DATE])
SELECT * FROM ONE
If you want that your data in DATE1 column must be stored in your table (not
just computed every time you working something with that column) use
PERSISTED column as follows:
ALTER TABLE ONE
ADD DATE1 AS dateadd(dd, NUM, [DATE]) PERSISTED
Mladen.|||Hi,
I've tried the following code. But it did not work. Please let me help
me out. Thanks!
create table test
(
value float
)
insert into test
values(17188)
exec sp_help test
declare @.startdate datetime
set @.startdate='1/1/1960'
alter table test
add value1 as value+@.startdate
Mike
Michael wrote:
> HI,
> I have a table ONE have the following two columns
> [DATE] [datetime]
> [NUM] [float]
> I want to create a new column DATE1=DATE+NUM, date type is datetime.
> Could anyone give me a help?
> Thanks,
> Mike|||Thanks a lot!
mladjo wrote:
> Hi.
> What do you want exactly with adding NUM to DATE? Can you get some example?
> Do you want to add day, month, year, or something else?
> I suppose you want something like this:
> create table ONE
> (
> [DATE] datetime,
> [NUM] float
> )
> INSERT INTO ONE values (getdate(), 2)
> INSERT INTO ONE values (getdate(), 3)
> SELECT [DATE],dateadd(dd, NUM, [DATE]) FROM ONE
>
> ALTER TABLE ONE
> ADD DATE1 AS dateadd(dd, NUM, [DATE])
> SELECT * FROM ONE
>
> If you want that your data in DATE1 column must be stored in your table (not
> just computed every time you working something with that column) use
> PERSISTED column as follows:
> ALTER TABLE ONE
> ADD DATE1 AS dateadd(dd, NUM, [DATE]) PERSISTED
> Mladen.

create a new column

HI,
I have a table ONE have the following two columns
[DATE] [datetime]
[NUM] [float]
I want to create a new column DATE1=DATE+NUM, date type is datetime.
Could anyone give me a help?
Thanks,
MikeHi.
What do you want exactly with adding NUM to DATE? Can you get some example?
Do you want to add day, month, year, or something else?
I suppose you want something like this:
create table ONE
(
[DATE] datetime,
[NUM] float
)
INSERT INTO ONE values (getdate(), 2)
INSERT INTO ONE values (getdate(), 3)
SELECT [DATE],dateadd(dd, NUM, [DATE]) FROM ONE
ALTER TABLE ONE
ADD DATE1 AS dateadd(dd, NUM, [DATE])
SELECT * FROM ONE
If you want that your data in DATE1 column must be stored in your table (not
just computed every time you working something with that column) use
PERSISTED column as follows:
ALTER TABLE ONE
ADD DATE1 AS dateadd(dd, NUM, [DATE]) PERSISTED
Mladen.|||Hi,
I've tried the following code. But it did not work. Please let me help
me out. Thanks!
create table test
(
value float
)
insert into test
values(17188)
exec sp_help test
declare @.startdate datetime
set @.startdate='1/1/1960'
alter table test
add value1 as value+@.startdate
Mike
Michael wrote:
> HI,
> I have a table ONE have the following two columns
> [DATE] [datetime]
> [NUM] [float]
> I want to create a new column DATE1=DATE+NUM, date type is datetime.
> Could anyone give me a help?
> Thanks,
> Mike|||Thanks a lot!
mladjo wrote:
> Hi.
> What do you want exactly with adding NUM to DATE? Can you get some example
?
> Do you want to add day, month, year, or something else?
> I suppose you want something like this:
> create table ONE
> (
> [DATE] datetime,
> [NUM] float
> )
> INSERT INTO ONE values (getdate(), 2)
> INSERT INTO ONE values (getdate(), 3)
> SELECT [DATE],dateadd(dd, NUM, [DATE]) FROM ONE
>
> ALTER TABLE ONE
> ADD DATE1 AS dateadd(dd, NUM, [DATE])
> SELECT * FROM ONE
>
> If you want that your data in DATE1 column must be stored in your table (n
ot
> just computed every time you working something with that column) use
> PERSISTED column as follows:
> ALTER TABLE ONE
> ADD DATE1 AS dateadd(dd, NUM, [DATE]) PERSISTED
> Mladen.

Create a datetime dimension table with start and end dates

Hi

I want to create a table that has a datetime column, data type=datetime and I want the date to start form 01/01/2004 00:00:00 to 01/01/2008 00:00:00 the increment will be by minute like

01/01/2004 00:01:00

01/01/2004 00:02:00

01/01/2004 00:03:00 and so on up to 01/01/2008 00:00:00

I will use this time dimension in bussiness objects.

please provide me with the SQL sript to do this.

CREATE TABLE DimensionTable

(

IdCol INT IDENTITY(1,1),

Timecol DATETIME

)

GO

DECLARE @.Startdate DATETIME

DECLARE @.Enddate DATETIME

SET @.Startdate = '20040101'

SET @.Enddate = '20080101'

WHILE @.Startdate < @.Enddate

BEGIN

INSERT INTO DimensionTable

(

TimeCol

)

SELECT @.Startdate

SET @.Startdate = DATEADD(mi,1,@.Startdate)

END

That could take some time :-)

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||Thanks a lot

Create a comma delimited *field*

Help! (again...)
I have data that looks like this:
date snum
5/9/2007 3064
5/9/2007 3072
5/16/2007 4031
5/16/2007 3856
5/16/2007 4252
(Can recreate with
declare @.temptable table (date smalldatetime, snum int)
insert into @.temptable values('09 May 2007', 3064)
insert into @.temptable values('09 May 2007', 3072)
insert into @.temptable values('16 May 2007', 4031)
insert into @.temptable values('16 May 2007', 3856)
insert into @.temptable values('16 May 2007', 4252)
)
I need to make it look like this:
date snumlist
5/9/2007 3064, 3072
5/16/2007 4031, 3856, 4252
The "snumlist" is being plugged into a javascript function in a
vbscript.asp page. I can easily create the snumlist using while/wend
in vbscript but it seems quite slow! I'm wondering if it wouldn't be
faster to create the comma delimited list in SQL rather than try to
build it with VBScript.
(I can do anything to the temptable that I need to, structure-wise -
it's created on the fly.)
Thanks for any help, a pointer to a web page, a pointer to a previous
post, ANYthing.
Julie
(P.S. This email address forwards to my regular email address. Either
Google or my ISP often marks replies from this newsgroup as spam, so I
never see them. I still haven't gotten it sorted out. If possible,
please reply on list. Thanks, jcls)Sorry - posted this in wrong group. Moving to .programming
Thx, Julie
On May 5, 4:42 pm, Julie <julie.sie...@.gmail.com> wrote:
> Help! (again...)
> I have data that looks like this:
> date snum
> 5/9/2007 3064
> 5/9/2007 3072
> 5/16/2007 4031
> 5/16/2007 3856
> 5/16/2007 4252
> (Can recreate with
> declare @.temptable table (date smalldatetime, snum int)
> insert into @.temptable values('09 May 2007', 3064)
> insert into @.temptable values('09 May 2007', 3072)
> insert into @.temptable values('16 May 2007', 4031)
> insert into @.temptable values('16 May 2007', 3856)
> insert into @.temptable values('16 May 2007', 4252)
> )
> I need to make it look like this:
> date snumlist
> 5/9/2007 3064, 3072
> 5/16/2007 4031, 3856, 4252
> The "snumlist" is being plugged into a javascript function in a
> vbscript.asp page. I can easily create the snumlist using while/wend
> in vbscript but it seems quite slow! I'm wondering if it wouldn't be
> faster to create the comma delimited list in SQL rather than try to
> build it with VBScript.
> (I can do anything to the temptable that I need to, structure-wise -
> it's created on the fly.)
> Thanks for any help, a pointer to a web page, a pointer to a previous
> post, ANYthing.
> Julie
> (P.S. This email address forwards to my regular email address. Either
> Google or my ISP often marks replies from this newsgroup as spam, so I
> never see them. I still haven't gotten it sorted out. If possible,
> please reply on list. Thanks, jcls)|||I'll tell you what they'll tell you over there - what you're trying to do
should be done in the front end. That aside, there are several T-SQL
scripts (some cleverer than others) posted in the .programming newsgroup to
do exactly what you're asking. When you ask over there, be sure to mention
whether or not the ordering of the items in the comma-separated list is
important. From your example it doesn't appear so, but you will probably
want to clarify that.
"Julie" <julie.siebel@.gmail.com> wrote in message
news:1178409829.540816.60210@.p77g2000hsh.googlegroups.com...
> Sorry - posted this in wrong group. Moving to .programming
> Thx, Julie
> On May 5, 4:42 pm, Julie <julie.sie...@.gmail.com> wrote:
>> Help! (again...)
>> I have data that looks like this:
>> date snum
>> 5/9/2007 3064
>> 5/9/2007 3072
>> 5/16/2007 4031
>> 5/16/2007 3856
>> 5/16/2007 4252
>> (Can recreate with
>> declare @.temptable table (date smalldatetime, snum int)
>> insert into @.temptable values('09 May 2007', 3064)
>> insert into @.temptable values('09 May 2007', 3072)
>> insert into @.temptable values('16 May 2007', 4031)
>> insert into @.temptable values('16 May 2007', 3856)
>> insert into @.temptable values('16 May 2007', 4252)
>> )
>> I need to make it look like this:
>> date snumlist
>> 5/9/2007 3064, 3072
>> 5/16/2007 4031, 3856, 4252
>> The "snumlist" is being plugged into a javascript function in a
>> vbscript.asp page. I can easily create the snumlist using while/wend
>> in vbscript but it seems quite slow! I'm wondering if it wouldn't be
>> faster to create the comma delimited list in SQL rather than try to
>> build it with VBScript.
>> (I can do anything to the temptable that I need to, structure-wise -
>> it's created on the fly.)
>> Thanks for any help, a pointer to a web page, a pointer to a previous
>> post, ANYthing.
>> Julie
>> (P.S. This email address forwards to my regular email address. Either
>> Google or my ISP often marks replies from this newsgroup as spam, so I
>> never see them. I still haven't gotten it sorted out. If possible,
>> please reply on list. Thanks, jcls)
>

CR Chart add 2 or more series ...

I am trying to graph fuel miles per gallon by vehicle by date.

My data looks like this:
Vehicle MilesPerGallon Date
1 25.6 9-1-04
1 27 9-7-04
2 22 9-2-04
2 25 9-8-04

I would like a line by vehicle, so like vehicle 1 would be a blue line and the y axis would have Miles Per Gallon, the x axis would have date and it would have a dot at 9-1-04 and 25.6, 9-7-04 and 27. Then vehicle 2 would be a red line with dots at 9-2-04 and 22, 9-8-04 and 25.

I am using CR with .NET (basically CR v10 ... I think) and don't understand how to accomplish this. Do you have any advice?

Thanks ... ChadDid you ever find the solution to your problem?? I am having the same problem and would appreciate any help you could give me.

Thanks|||Nope, I gave up and only showed Miles Per Gallon for one vehicle at at time.

Sorry ...

coverting to date problem

SELECT DISTINCT CAST(YEAR(classdate) AS varchar(4)) + '/' + CAST(MONTH(classdate) AS varchar(2)) as ok ,{fn MONTH(dbo.classgiven.classdate)} as monthcode FROM dbo.classT INNER JOIN dbo.classgiven ON dbo.classT.classcode = dbo.classgiven.classcode WHERE (dbo.classT.discount = '-1') AND (dbo.classT.coned IS NOT NULL) order by 1", conNorthwind)

hello how do convert "CAST(YEAR(classdate) AS varchar(4)) + '/1/' + CAST(MONTH(classdate) AS varchar(2)) as ok" to a date in my sql statement?

so that it comes out as MM/DD/YYYY

thank you all

agian "OK" has to come out as a date..thanks

you can use

convert(varchar(20),classdate,101)

in you query, this will converts the date in the MM/dd/yyyy format.

|||

It looks as thoughclassdate is a date type anyway so, rather than create unnecessary function calls in your SQL statement, simply return the date and format it in whatever control you are using to display the data.

Counts in Date Ranges

Hi,
I have table with the following structure
User Name, Name of Product, Date of Purchase
I want to find out the number of times a particular product was purchased by
anybody in the last 30, 60 and 90 days.
Is there any function in SQL that will give this type of distribution? What
will be the best way of writing the query?
Thanks in anticipation,
Nitin MNitim
SELECT COUNT(ProdunctName) FROM
TableName WHERE [Date of Purchase] >=DATEADD(DAY,-30,GETDATE()) AND
<GETDATE()
AND ProductName='Something'
"Nitin M" <nitin@.nowhere.com> wrote in message
news:%23Zbsk3yPFHA.2932@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have table with the following structure
> User Name, Name of Product, Date of Purchase
> I want to find out the number of times a particular product was purchased
by
> anybody in the last 30, 60 and 90 days.
> Is there any function in SQL that will give this type of distribution?
What
> will be the best way of writing the query?
> Thanks in anticipation,
> Nitin M
>|||Thanks Uri,
This is the solution to get the purchase count for a given time period.
However I was looking for a solution that gives me the purchase counts for
three time periods in one query. I am looking for a result set that looks
like
Product PurchaseInLast30Days PurchaseInLast60Days
PurchaseInLast90Days
----
--
A 10 13
31
B 0 5
5
and so on ...
I have a query that looks like... but it amounts to scanning the table 4
times. Is there a better way out?
select
P.ProductName,
Last30Days = (select count(P1.ProductName) from Products P1 where
P1.ProductName = P.ProductName and
datediff(day,P1.PurchaseDate,getutcdate()) <= 30 group by P1.ProductName),
Last60Days = (select count(P1.ProductName) from Products P1 where
P1.ProductName = P.ProductName and
datediff(day,P1.PurchaseDate,getutcdate()) <= 60 group by P1.ProductName),
Last90Days = (select count(P1.ProductName) from Products P1 where
P1.ProductName = P.ProductName and
datediff(day,P1.PurchaseDate,getutcdate()) <= 90 group by P1.ProductName),
from
Products P
group by
P.ProductName
Thanks,
Nitin M
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eJjWlBzPFHA.2876@.TK2MSFTNGP10.phx.gbl...
> Nitim
> SELECT COUNT(ProdunctName) FROM
> TableName WHERE [Date of Purchase] >=DATEADD(DAY,-30,GETDATE()) AND
> <GETDATE()
> AND ProductName='Something'
>
> "Nitin M" <nitin@.nowhere.com> wrote in message
> news:%23Zbsk3yPFHA.2932@.TK2MSFTNGP09.phx.gbl...
> by
> What
>|||I see what you mean
select
ProductName,
COUNT(CASE WHEN datediff(day,PurchaseDate,getutcdate()) <= 30 THEN 1 END)
Last30Days ,
COUNT(CASE WHEN datediff(day,PurchaseDate,getutcdate()) <=60 THEN 1 END)
Last60Days ,
COUNT(CASE WHEN datediff(day,PurchaseDate,getutcdate()) <= 90 THEN 1 END)
Last90Days
from
Products
group by
ProductName
"Nitin M" <nitin@.nowhere.com> wrote in message
news:OVtGoHzPFHA.532@.TK2MSFTNGP09.phx.gbl...
> Thanks Uri,
> This is the solution to get the purchase count for a given time period.
> However I was looking for a solution that gives me the purchase counts for
> three time periods in one query. I am looking for a result set that looks
> like
> Product PurchaseInLast30Days PurchaseInLast60Days
> PurchaseInLast90Days
> ----
--
> A 10 13
> 31
> B 0 5
> 5
> and so on ...
> I have a query that looks like... but it amounts to scanning the table 4
> times. Is there a better way out?
> select
> P.ProductName,
> Last30Days = (select count(P1.ProductName) from Products P1 where
> P1.ProductName = P.ProductName and
> datediff(day,P1.PurchaseDate,getutcdate()) <= 30 group by P1.ProductName),
> Last60Days = (select count(P1.ProductName) from Products P1 where
> P1.ProductName = P.ProductName and
> datediff(day,P1.PurchaseDate,getutcdate()) <= 60 group by P1.ProductName),
> Last90Days = (select count(P1.ProductName) from Products P1 where
> P1.ProductName = P.ProductName and
> datediff(day,P1.PurchaseDate,getutcdate()) <= 90 group by P1.ProductName),
> from
> Products P
> group by
> P.ProductName
> Thanks,
> Nitin M
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eJjWlBzPFHA.2876@.TK2MSFTNGP10.phx.gbl...
purchased
>|||Thanks a lot Uri ... I got it ... It helped me.
Thank You
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23dOdsRzPFHA.1096@.TK2MSFTNGP12.phx.gbl...
>I see what you mean
> select
> ProductName,
> COUNT(CASE WHEN datediff(day,PurchaseDate,getutcdate()) <= 30 THEN 1 END)
> Last30Days ,
> COUNT(CASE WHEN datediff(day,PurchaseDate,getutcdate()) <=60 THEN 1 END)
> Last60Days ,
> COUNT(CASE WHEN datediff(day,PurchaseDate,getutcdate()) <= 90 THEN 1 END)
> Last90Days
> from
> Products
> group by
> ProductName
> "Nitin M" <nitin@.nowhere.com> wrote in message
> news:OVtGoHzPFHA.532@.TK2MSFTNGP09.phx.gbl...
> --
> purchased
>

CountRows

I have a query structured to return products sold by date. I'm then grouping them by the year they were sold, and totalling up sales..etc. The problem is when I run my sql query that retrieves the data in the SQL designer, the query retrieves 97 rows. When I run the report, and check the number of rows returned (with CountRows()) I get 67. However the remaining 30 rows still show up and are grouped on the next page! Why is this happening? Thanks!It would appear that it's splitting the data into two queries... one with 67 rows and one with 30 rows. I do not want this behavior to occur. What may I do?|||

Try moving the counter out of the group row and put CountRows() in the table row.

Tableheader CountRows()

Group1header "Not here"

data

group1footer

Tablefooter "or here" CountRows()

|||

Thanks for the speedy reply! With that in the table header it's still returning 67/30. I think I've narrowed the problem down to the List grouping expression, but I don't understand why it considers the same value to belong to a different group?

|||It had to do with the list... Removing it and throwing in just a table fixed the problem. When would you ever need to use a list? Thanks

counting the years in multiple rows question

am making a CV program and i need a way to count the experience the user has:
i have his begin date and end Date as datetime in an sql server.
i can do it programicly but i prefer to do it at the sql side
the question:
how can i get how much exp he has aka :

Code Snippet

SUM(DATEDIFF(year , JobApExp.BeginDate , JobApExp.EndDate ))

but for all the datarow
(he has more than one BeginDate and EndDate (for each job he has one))

P.S i want to be able to use it in a where clause :

Code Snippet

select * from jobap
where -- or HAVING
JobAp.ind = JobApExp.JobAp AND
SUM(DATEDIFF(year , JobApExp.BeginDate , JobApExp.EndDate )) > CONVERT(int,@.Exp)

thanks in advance

You have to use the grouping together with the having clause:

declare @.Exp as int

select max(A) as A, max(B) as B from jobap

where JobAp.ind = JobApExp.JobAp

HAVING SUM(DATEDIFF(year , JobApExp.BeginDate , JobApExp.EndDate )) > @.Exp

|||

create table #Personnel(id int,name varchar(20))
insert #Personnel select 1,'madhu'
insert #Personnel select 2 ,'zyx'

create table #PersonnelExp (ID int,fmdt datetime,todt datetime)
delete #PersonnelExp
insert #PersonnelExp select 1,'1990-1-1','1999-1-1'
insert #PersonnelExp select 1,'1999-1-2','2002-1-2'
insert #PersonnelExp select 1,'2003-1-2','2007-1-2'
insert #PersonnelExp select 2,'1995-1-1','1999-1-1'
insert #PersonnelExp select 2,'1999-1-2','2004-1-2'
insert #PersonnelExp select 2,'2005-1-2','2007-1-2'

select a.ID,a.Name,b.TotExp From #Personnel a,
(select id,SUM(DATEDIFF(year , fmdt, todt )) As TotExp from #PersonnelExp group by id) b
where a.ID=b.ID
and totexp>12

Check this script

Madhu

|||

You may want to consider performing the DATEDIFF on the day or month level, as the year shows the year difference between the two dates.

IE

select datediff(yy, '01/01/2006', '12/31/2006')

select datediff(yy, '12/31/2006', '01/01/2007')

The first returns 0, even though it's a full year. And the second returns 1 even though it's only been 1 day.

counting rows by date (was "Help on Query")

I hate to ask such silly helps..but I'm missing something here..need help.
I have a table having columns for createddate and deleteddate. The data gets created and deleted periodically and I need to find out the number of created,deleted and remaining number of records on each day. This query works, but takes a lot of time...not sure if there is a more better way to do this.. Please help
SELECT
CAST(createddate AS DATETIME) AS createdDate,
Created,
Deleted,
Remaining
FROM(
SELECT
CONVERT(VARCHAR,createdon,102) AS CreatedDate,
COUNT(1) created,
(SELECT COUNT(1) FROM table ta2
WHERE CONVERT(VARCHAR,ta2.deletedon,102) =
CONVERT(VARCHAR,ta.createdon,102)) Deleted,
((SELECT COUNT(1) FROM table ta1
WHERE CONVERT(VARCHAR,ta1.createdon,102) <=
CONVERT(VARCHAR,ta.createdon,102)) -
(SELECT COUNT(1) FROM table ta1
WHERE CONVERT(VARCHAR,ta1.deletedon,102) <=
CONVERT(VARCHAR,ta.createdon,102))) Remaining
FROM table ta
WHERE CONVERT(VARCHAR,createdon,102) >= (GETDATE() - 90)
GROUP BY CONVERT(VARCHAR,createdon,102)
ORDER BY CONVERT(VARCHAR,createdon,102) DESC)
AS tmpFirst, your outer select is completely unnecessary. All it does is display the data exactly as it is returned from the inner query. Drop it.
Second, please use count(*) instead of count(1). Why? Just to make the rest of us happy. That's why.
Third, use the DATEDIFF function instead of subtracting an integer from GETDATE().
Fourth, lets hope to God your table has a primary key, which in keeping with your naming convention we will name "primarykey".

Then see if this doesn't run faster:

select Convert(char(10), ta1.createdon, 120) CreateDate,
count(distinct ta1.primarykey) Created,
count(distinct ta2.primarykey) Deleted,
count(distinct ta3.primarykey) - count(distinct ta4.primarykey) Remaining
from table ta1
left outer join table ta2 on datediff(day, ta2.deletedon, ta1.createdon) = 1
left outer join table ta3 on datediff(day, ta3.createdon, ta1.createdon) >= 0
left outer join table ta4 on datediff(day, ta4.deletedon, ta1.createdon) >= 0
where datediff(day, ta1.createdon, getdate()) <= 90
group by Convert(char(10), ta1.createdon, 120)
order by Convert(char(10), ta1.createdon, 120)|||My primary key is a uniqueid, so this query gives an error: count unique aggregate operation cannot take uniqueidentifier datatype as argument.|||Try count(Distinct cast([YourPrimaryKey] as char(36))|||Well. I tried that. It worked, but it takes a lot more time than before, could be because of outer joins. I am trying to replace the date comparision with datediff, but getting 'not contained in aggregate function or group by' error. still working out|||This is not going to be a fast query, whatever you do. If you post the table structure I or somebody else here may be able to find a more efficient execution. Perhaps a multi-step procedure using a table variable, for instance.|||Originally posted by blindman
This is not going to be a fast query, whatever you do. If you post the table structure I or somebody else here may be able to find a more efficient execution. Perhaps a multi-step procedure using a table variable, for instance.

Really? You give us too much credit...

I'd like to see the DDL AND an explanation of what you're trying to do...

IN Business terms...nothing technical

Sample data would be helpful as well

Oh and what the final result should be...

Do that and THEN I'll agree with the Blind dude...

(amazing how he can find the keys...no?)

SELECT
CAST(createddate AS DATETIME) AS createdDate
, Created
, Deleted
, Remaining
FROM ( SELECT
, CONVERT(VARCHAR,createdon,102) AS CreatedDate
, COUNT(1) created
, ( SELECT COUNT(1) FROM table ta2
WHERE CONVERT(VARCHAR,ta2.deletedon,102) =
CONVERT(VARCHAR,ta.createdon,102)
) Deleted
, (( SELECT COUNT(1) FROM table ta1
WHERE CONVERT(VARCHAR,ta1.createdon,102) <=
CONVERT(VARCHAR,ta.createdon,102)) -
( SELECT COUNT(1) FROM table ta1
WHERE CONVERT(VARCHAR,ta1.deletedon,102) <=
CONVERT(VARCHAR,ta.createdon,102))) Remaining
FROM table ta
WHERE CONVERT(VARCHAR,createdon,102) >= (GETDATE() - 90)
GROUP BY CONVERT(VARCHAR,createdon,102)
ORDER BY CONVERT(VARCHAR,createdon,102) DESC
) AS tmp|||Ok. Basically, the table has order entries created by the sales men and it gets reviewed by the manager. Once they review it, it becomes invalid the table(logically deleted, not physically). So, I am storing the created date and deleted date in this table. For audit purposes, I need a query to give how many orders were created during a day, how many were reviewed(or deleted) and how many were remaining at the end of day. The primary key is the order id which has an index on it. I also have a column that stores 1 or 0 depending on whether the order is deleted or not.|||Do you know how to script the DDL for the table?

Post it here...|||CREATE TABLE [dbo].[Order] (
[OrderId] [uniqueidentifier] NOT NULL ,
[CustomerNo] [varchar] (100) NULL ,
[Address] [varchar] (255) NULL ,
[DocNo] [varchar] (100) NULL ,
[DocDate] [datetime] NULL ,
[OrderType] [uniqueidentifier] NULL ,
[SalesRep] [uniqueidentifier] NULL ,
[Value] [money] NULL ,
[Comments] [varchar] (2048) NULL ,
[CreatedBy] [uniqueidentifier] NULL ,
[CreatedOn] [datetime] NULL ,
[UpdatedBy] [uniqueidentifier] NULL ,
[UpdatedOn] [datetime] NULL ,
[Deleted] [bit] NOT NULL ,
[DeletedBy] [uniqueidentifier] NULL ,
[DeletedOn] [datetime] NULL ,
[Status] [char] (1) NULL,
CONSTRAINT [I_ORDER] PRIMARY KEY CLUSTERED
(
[OrderId]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]|||Originally posted by nanadmin
Ok. Basically, the table has order entries created by the sales men and it gets reviewed by the manager. Once they review it, it becomes invalid the table(logically deleted, not physically). So, I am storing the created date and deleted date in this table. For audit purposes, I need a query to give how many orders were created during a day, how many were reviewed(or deleted) and how many were remaining at the end of day. The primary key is the order id which has an index on it. I also have a column that stores 1 or 0 depending on whether the order is deleted or not.

Well...is that the difference of the 2?

Also, you mention you have a column that stores 0 or 1 to show that it's logically deleted...

But doesn't the existence of a date in the DeletedOn column infer that?

Same thing with the CreatedOn Column for New Orders?

Yes?|||It is not the difference of 2. Because, there may be 10 orders created yesterday and 5 today; and if 6 were deleted yesterday and 4 today, then my end of day remaining count will be 4 for yesterday and 5 for today. The result would be 10,6,4 and 5,4,5

'Deleted' column has the value 0 or 1. Deleted date is enough, but I needed a flag for other querying purposes, since it is programatically good to check (deleted =1) instead of (deleteddate <> null)|||1. From DDL statement there is only index on the table - PK by order ID. So your query are processed like 4 full table scans.
2. Do not compare DateTime fields this way: CONVERT(VARCHAR,ta2.deletedon,102) = CONVERT(VARCHAR,ta.createdon,102). This way query engine cannot use indexes and it lowering comparision performance.
3. If create indexes on createdon and deletedon fields and remove CONVERT on comparision operations your select would be much faster.|||Palex, welcome to the forum. But if you are going to start responding to year-old posts then you are going to have a lot of catching up to do.

Hope your calendar is clear for the next month or two...

Counting Date Query

I have a table called sv_call_log
The table contains columns row_id, card_num, start_date, end_date.
This table logs calls.
The start_date is when the call begins and end_date is when the call ends.
They are both fromatted as follows EX. 2004-09-20 15:55:29.247
We currently have 4 lines avliable for people to call in out.
Does anyone know how to write a query to show when more than 3 lines are
used at the same time? The query would have to see if 3 or more start_dates
occured between a start_date and end_date. I'm on how I would
write this query or if there is even a way. Any help would be greatly
appreciated. Thank you.Having the entire table DDL would be very useful for us to provide
meaningful help. Otherwise we are just guessing...
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"tarheels4025" <tarheels4025@.discussions.microsoft.com> wrote in message
news:2EBC3CD5-95D8-49CF-B59B-49EBB78E508C@.microsoft.com...
>I have a table called sv_call_log
> The table contains columns row_id, card_num, start_date, end_date.
> This table logs calls.
> The start_date is when the call begins and end_date is when the call ends.
> They are both fromatted as follows EX. 2004-09-20 15:55:29.247
> We currently have 4 lines avliable for people to call in out.
> Does anyone know how to write a query to show when more than 3 lines are
> used at the same time? The query would have to see if 3 or more
> start_dates
> occured between a start_date and end_date. I'm on how I
> would
> write this query or if there is even a way. Any help would be greatly
> appreciated. Thank you.|||Hmm. Interesting.
Hopefully start_date and end_date are actually of datetime datatype,
and the formatting you describe is simply how they are displayed.
I think this will show you any call that starts while at least 2 other
calls are already in progress. If a fourth call starts there will be
rows for both the third and fourth.
--Each call matches itself, possibly others
SELECT X.row_id,
count(Y.row_id) as OverlapLevel,
start_date as start_overlap,
min(end_date) as end_overlap
FROM CallLog as X
JOIN CallLog as Y
ON X.start_date BETWEEN Y.start_date AND Y.end_date
GROUP BY X.row_id
HAVING count(Y.row_id) > 3
Roy Harvey
Beacon Falls, CT
On Thu, 22 Jun 2006 12:03:02 -0700, tarheels4025
<tarheels4025@.discussions.microsoft.com> wrote:

>I have a table called sv_call_log
>The table contains columns row_id, card_num, start_date, end_date.
>This table logs calls.
>The start_date is when the call begins and end_date is when the call ends.
>They are both fromatted as follows EX. 2004-09-20 15:55:29.247
>We currently have 4 lines avliable for people to call in out.
>Does anyone know how to write a query to show when more than 3 lines are
>used at the same time? The query would have to see if 3 or more start_date
s
>occured between a start_date and end_date. I'm on how I would
>write this query or if there is even a way. Any help would be greatly
>appreciated. Thank you.|||Ron,
When I run the query it throws back
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Start_Date'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'End_Date'.
"Roy Harvey" wrote:

> Hmm. Interesting.
> Hopefully start_date and end_date are actually of datetime datatype,
> and the formatting you describe is simply how they are displayed.
> I think this will show you any call that starts while at least 2 other
> calls are already in progress. If a fourth call starts there will be
> rows for both the third and fourth.
> --Each call matches itself, possibly others
> SELECT X.row_id,
> count(Y.row_id) as OverlapLevel,
> start_date as start_overlap,
> min(end_date) as end_overlap
> FROM CallLog as X
> JOIN CallLog as Y
> ON X.start_date BETWEEN Y.start_date AND Y.end_date
> GROUP BY X.row_id
> HAVING count(Y.row_id) > 3
> Roy Harvey
> Beacon Falls, CT
> On Thu, 22 Jun 2006 12:03:02 -0700, tarheels4025
> <tarheels4025@.discussions.microsoft.com> wrote:
>
>|||Sorry about that.
SELECT X.row_id,
count(Y.row_id) as OverlapLevel,
X.start_date as start_overlap,
min(Y.end_date) as end_overlap
FROM CallLog as X
JOIN CallLog as Y
ON X.start_date BETWEEN Y.start_date AND Y.end_date
GROUP BY X.row_id
HAVING count(Y.row_id) > 3
Roy
On Thu, 22 Jun 2006 12:55:02 -0700, tarheels4025
<tarheels4025@.discussions.microsoft.com> wrote:

>Ron,
>When I run the query it throws back
>Server: Msg 209, Level 16, State 1, Line 1
>Ambiguous column name 'Start_Date'.
>Server: Msg 209, Level 16, State 1, Line 1
>Ambiguous column name 'End_Date'.