June 18, 2009 at 1:46 pm
If I have procedures which pull a set of data for a report, how would I set a condition to run it on certain days.
For example, SP_ClassA produces a data set with the Item, Description, Location, and Last Count Date. The shop has 5 days to count everything in ClassA. I need to count these items 6 times a year. SP_ClassB needs to be counted 3 times a year. The shop has 12 days to count ClassB.
We have 4 classes in all.
How do I say in a query
(I know this is incorrect)
Case when ShopDay between 1 and 5 then exec sp_ClassA
Case when ShopDay between 6 and 18 then exec sp_ClassB
??
Am I going about this the wrong with the procedures?
Maybe I should use some kind of CTE or temp tables?
Any ideas?
June 18, 2009 at 1:48 pm
can u try to re-write this query using if-else....
Check BOL for syntax...
June 18, 2009 at 1:54 pm
I'm not sure what you are asking for. This?
If ShopDay between 1 and 5 then exec sp_ClassA
If ShopDay between 6 and 18 then exec sp_ClassB
If ShopDay between 19 and 40 then exec sp_ClassC
If ShopDay between 41 and 60 then exec sp_ClassD
If ShopDay between 61 and 65 then exec sp_ClassA
If ShopDay between 66 and 78 then exec sp_ClassB
If ShopDay between 79 and 100 then exec sp_ClassC
If ShopDay between 101 and 120 then exec sp_ClassD
June 18, 2009 at 2:00 pm
adams.squared (6/18/2009)
I'm not sure what you are asking for. This?If ShopDay between 1 and 5 then exec sp_ClassA
If ShopDay between 6 and 18 then exec sp_ClassB
If ShopDay between 19 and 40 then exec sp_ClassC
If ShopDay between 41 and 60 then exec sp_ClassD
If ShopDay between 61 and 65 then exec sp_ClassA
If ShopDay between 66 and 78 then exec sp_ClassB
If ShopDay between 79 and 100 then exec sp_ClassC
If ShopDay between 101 and 120 then exec sp_ClassD
Declare @ShopDay int
select @ShopDay=your_Column from your_table
If @shopday<=5 exec sp_classA
else if @shopday<=18 exec sp_classB
else if @ShopDay<=40 exec sp_classC
...
...
...
Can u try this out?
June 18, 2009 at 2:15 pm
I think that is going to work.
Thanks
June 18, 2009 at 2:18 pm
Glad i could help you 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply