Database

16-Jul-07 20:31

GRANT create tablespace TO #user id#;

-- modified at 4:22 Wednesday 18th July, 2007

If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".

Doubt in query.........,

Member 387988116-Jul-07 19:12

Re: Doubt in query........., [modified]

16-Jul-07 19:12

Hi i have table like this format:
Id sMonth sYear value1
608 6 2007 100
608 7 2007 200
608 8 2007 150
608 6 2008 652
608 7 2008 120
608 8 2008 234
Suppose i need the records between the 6th month 2007 to 6th month 2008

O/P:

Id sMonth sYear value1
608 6 2007 100
608 7 2007 200
608 8 2007 150
608 6 2008 652

How to write the query???,

If its in date format we can use between condition...,
But in this case how u can compare the months and years ???,

Regards,
Magi

andyharman16-Jul-07 20:24

16-Jul-07 20:24

How about

select * from MyTable
   where (sYear > 2007 or (sYear = 2007 and sMonth >= 6))
   and (sYear < 2008 or (sYear = 2008 and sMonth <= 6))

select * from MyTable
   where convert(varchar(6), (Year*100)+sMonth) between '200706' and '200806'

Regards
Andy

-- modified at 14:19 Wednesday 18th July, 2007

If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".

Member 387988116-Jul-07 20:36

16-Jul-07 20:36

Hi andy,
Thank u for ur reply...,
Ya i getting o/p for the first query...,
But second query is not giving proper o/p...,
Anyway one solution is enough...,
Thank's lot...,
keep in touch,

Regards,
Magi

andyharman16-Jul-07 21:17

16-Jul-07 21:17

You're welcome.

The second query should have used (sYear*100). I hadn't woken-up properly Smile | :)

RepliCrux16-Jul-07 20:37

RepliCrux

16-Jul-07 20:37

5 votes to you man. I was about to say bad database design Poke tongue | ;-P

RepliCrux16-Jul-07 20:53

RepliCrux

16-Jul-07 20:53

Sorry Magi,

Did not wanted to hurt your feelings about the design, But it is a bad practice, you should always use DateTime format. If you want to look at month and year only, you can still do that using datetime column. As you proceed further you will find data Access more and more difficult. That is why they say that make sure your design is right. Consult your senior developers about the design.

And finally, as you said, it might be right in your case, so thats good Smile | :)

Cheers

Member 387988116-Jul-07 21:01

16-Jul-07 21:01

S repli,
I know what ur trying to say...,
But i cant get date from my end user...,
Becoz a month full records i am storing in one row itself...,
So how i can tell the date in this?...,
So in this we can use table deisgn like that only...,
Sorry yar i think my words also make u to feel..,
Once again sorry for that,
Keep in touch,

Regards,
Magi

ChandraRam17-Jul-07 4:01

ChandraRam

17-Jul-07 4:01

andyharman wrote:
select * from MyTable where convert(varchar(6), (Year*12)+sMonth) between '200706' and '200806'

Actually, (Year*12)+sMonth wont get you those values at all Wink | ;)

ChandraRam17-Jul-07 4:11

ChandraRam

17-Jul-07 4:11

And looks like I wasn't reading properly either, before posting my message... pl ignore - I saw your clarification after I had hit the submit.

N a v a n e e t h16-Jul-07 20:37

N a v a n e e t h

16-Jul-07 20:37

writing query for your requirement will be bit tough. Why don't you use date fields rather than storing date like this. In date fields you can use > or < operators to get the dates in between two dates.

My Website | Ask smart questions

Member 387988116-Jul-07 20:57

16-Jul-07 20:57

Thanks for ur reply navaneeth...,
s navaneeth, i amn't getting date format from my end user...,
Just i am getting month and year only...,
So cant say in dateformat in my table...,
But any way thanks for ur suggestion...,
See andy's first query is giving solution for this probs...,
Keep in touch,
Friendly,

Regards,
Magi

Krish - KP16-Jul-07 21:20

Krish - KP

16-Jul-07 21:20

SELECT * FROM TblA
WHERE Convert(Datetime, '01/'+sMonth+'/'+sYear, 103) BETWEEN '01-Jun-2007' AND '30-Jun-2008'

Regards
KP

RepliCrux17-Jul-07 12:40

RepliCrux

17-Jul-07 12:40

You are right, the only thing is not all months have last day as 30, it can be 30, 28, 31... but well this is the right way to do anyway, there is a store procedure which gets the last day of the month in a particular year, can always use that.

Krish - KP17-Jul-07 20:34

Krish - KP

17-Jul-07 20:34

given Month & Year are constant (june, 2007, 2008)
Also the date which is derived is always 1st.

Regards
KP

Query

samerh16-Jul-07 18:42

samerh

16-Jul-07 18:42

Hi all
i have a case where i have 3 tables

table 1
blId blDesc
1 test1
----------
table 2
bl2Id bl2Desc blId
1 test2 1
-------------------
table 3
id name description
1 bilal test1.test2

the description of table3 (test1.test2) containing description from table1 and its child from table2

how can I query table3 and have instead of test1.test2, 1.1 (The Ids)

Please Help

Re: Query

andyharman16-Jul-07 19:44

16-Jul-07 19:44

For SQL-Server, you could try:

select table3.description,
    convert(varchar, table1.blId) + '.' + convert(varchar, table2.bl2Id) as TheIds
from table3
inner join table1
  on table3.description like table1.blDesc + '.%'
inner join table2
  on table3.description like '%.' + table2.bl2Desc
order by table1.blId, table2.bl2Id

Regards
Andy

If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".

Triggers

seemamltn16-Jul-07 11:32

seemamltn

16-Jul-07 11:32

Hi All,

I have a table with some columns. My project is when I upade any of the
column then I want a trigger to execute in a column named DATE_UPDATED. i
have the following trigger but when I update any column it doesn't allow me
to update the table manually. Where is the problem. e.g if I try to update
column name ADDRESS1 it gives the error message
ERROR MESSAGE = The transaction ended in the trigger. The batch has been
aborted.
Here is my trigger.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER TRIGGER [trgUddated]

ON [dbo].[DELTA_CARRIER]

FOR UPDATE

AS

If UPDATE(CARRIER_NAME)

BEGIN

PRINT('AFTER Trigger [trgUddate] - Executed - Carrier Name has
been updated')

ROLLBACK TRANSACTION

END

else If UPDATE(ADDRESS1)

BEGIN

PRINT('AFTER Trigger [trgUddate] - Executed - Address1 has been
updated')

ROLLBACK TRANSACTION

END

else If UPDATE(ADDRESS2)

BEGIN

PRINT('AFTER Trigger [trgUddate] - Executed - Address2 Name has
been updated')

ROLLBACK TRANSACTION

END

else If UPDATE(CITY)

BEGIN

PRINT('AFTER Trigger [trgUddate] - Executed - City has been
updated')

ROLLBACK TRANSACTION

END
else If UPDATE(STATE)

BEGIN

PRINT('AFTER Trigger [trgUddate] - Executed - State Name has
been updated')

ROLLBACK TRANSACTION

END

else If UPDATE(ZIP_CODE)

BEGIN

PRINT('AFTER Trigger [trgUddate] - Executed - Zip code has been
updated')

ROLLBACK TRANSACTION

END
else If UPDATE(PHONE)

BEGIN

PRINT('AFTER Trigger [trgUddate] - Executed - Phone Name has
been updated')

ROLLBACK TRANSACTION

END
UPDATE DELTA_CARRIER

SET DATE_UPDATED = GETDATE()

WHERE CARRIER_ID = 7551

seema

Re: Triggers

andyharman16-Jul-07 20:27