query help in stored procedure - best way to do i

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)

query help in stored procedure - best way to do i

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-01-19 : 08:04:41

i'm not sure the best way to do this.
what i want to do is in comments
can someone help me or guide me to the best way to do this in sql

select from sitetariff where site=@site
-- if time is null for all then
select @charge=charge,@minutes=endminutes from sitetariff where @minutes between startminutes and endminutes and site=@site
-- but if sitetarrif has at type then
--see what time it is now and look in sitetimes table to verify if peak or offpeak - then pull the charge from the sitetariff table for that peak - --
--BUT if say peak is 10-17:00 and it's now 16:00 we have to look at increment and if it's increment of 60 (increment goes by minutes) for example then charge them
-- 1 hour at peak 16-17 and tehn 17- rest of minutes at offpeak.

tables are sitetariff which has id, site,increment,charge,starttime,endtime,time(null,peak, offpeak)

and sitetimes - id,starttime,endtime,type (peak,offpeak)

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-01-19 : 09:10:53

IF ...
ELSE IF..

Or a case statement.

Depends on your data really, you post more of the question, with sample data, you'll get more of an answer, you should know that by now.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-01-19 : 14:52:26

ok but my question is how do I say
if there are records that have type of not null then to check what it is and what time it fits under

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-01-20 : 04:21:00

A nested case statement will do it.


SELECT
CASE WHEN time IS NULL THEN
     CASE WHEN type = 'whatever' THEN <do oter thing> END
....
END
FROM sitetariff

All the rest is just your business logic and can be put within the above construct.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources