Help getting one row from db - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help getting one row from db

Author

Topic

somenoob
Posting Yak Master

112 Posts

Posted - 2011-09-16 : 04:44:08

Hi everyone. i have a problem

SELECT COUNT(*) AS Cnt FROM V_OPS_DLY_AGING_DTL WHERE BATCH_ID=GETDATE()

from this codes, it take all the record from my db and checks the batch_id for the date. if all the date is within the current date, it will display the record.

but now i want it to be looking for all the record for the current date. if ONE record is within the current date, i will display all the record.

How do i code it that way?

Heinz23
Yak Posting Veteran

84 Posts

Posted - 2011-09-16 : 05:52:35

quote:
i want it to be looking for all the record for the current date. if ONE record is within the current date, i will display all the record.

I'm not a pro but this one should work:

If (SELECT COUNT(BATCH_ID) FROM V_OPS_DLY_AGING_DTL Where Batch_ID = GetDate()) > 0
select * from V_OPS_DLY_AGING_DTL

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-09-16 : 06:24:20

It'll work, but it's not really efficient to count all rows to see if there is one. (The parser/algebriser/optimiser can usually optimise that out, but not always)

IF EXISTS (SELECT 1 FROM V_OPS_DLY_AGING_DTL Where Batch_ID = GetDate())
  SELECT <column list here> FROM V_OPS_DLY_AGING_DTL

--
Gail Shaw
SQL Server MVP

somenoob
Posting Yak Master

112 Posts

Posted - 2011-09-18 : 21:27:17

does getdate also return the time??? if it does how do i edit the codes so that it only get the day,month and year?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-09-18 : 21:29:20

quote:
Originally posted by somenoob

does getdate also return the time??? if it does how do i edit the codes so that it only get the day,month and year?

Yes. It does

use
dateadd(day, datediff(day, 0, getdate()), 0)

KH
[spoiler]Time is always against us[/spoiler]

somenoob
Posting Yak Master

112 Posts

Posted - 2011-09-18 : 21:51:38

so i use..

IF EXISTS (SELECT 1 FROM V_OPS_DLY_AGING_DTL Where Batch_ID = dateadd(day, datediff(day, 0, getdate()), 0))
SELECT * FROM V_OPS_DLY_AGING_DTL

but when i run the ssis, it went on to my send mail task. as i have an record that is in the current day, i want it to go to my data dump task.

my ssis works like this, if there is no record in the current date, it will send an email. but if there is at least one record in the current date, it will help me do a data dump.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-09-18 : 22:04:15

what is the data type for the Batch_ID ?
string ? What is the format ?
datetime ? What is the time stored ? always midnight ?

KH
[spoiler]Time is always against us[/spoiler]

somenoob
Posting Yak Master

112 Posts

Posted - 2011-09-18 : 22:14:53

i looked into my sql management studio, batch_id in my db is store as datetime. 8/16/2011 12:00:00 AM. in all the record, it is all stored on the same time.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-09-18 : 22:18:49

run that query in Query Window. Any record return ?

KH
[spoiler]Time is always against us[/spoiler]

somenoob
Posting Yak Master

112 Posts

Posted - 2011-09-18 : 22:24:34

i run this code
If (SELECT COUNT(BATCH_ID) FROM [eodw-cntr].[dbo].[V_OPS_DLY_AGING_DTL] Where Batch_ID = dateadd(day, datediff(day, 0, getdate()), 0)) > 0
select * from [eodw-cntr].[dbo].[V_OPS_DLY_AGING_DTL]

in the management studio and it shows

Command(s) completed successfully.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-09-18 : 22:29:58

looks like you don't have a record with batch id that is dated today. Check your data

KH
[spoiler]Time is always against us[/spoiler]

somenoob
Posting Yak Master

112 Posts

Posted - 2011-09-18 : 22:36:16

i have edited the dates now. but i have a problem. whether is it the current date or not, it have now gone to my data dump task. if it is not current date, it should be in the send email task. is it because of my precedence constraint? i don't know how to set the precedence constraint

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-09-18 : 22:44:23

I am not familiar with SSIS. Try posting it under SSIS

KH
[spoiler]Time is always against us[/spoiler]

somenoob
Posting Yak Master

112 Posts

Posted - 2011-09-18 : 22:48:15

ok thanks for your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources