September 16, 2008 at 11:36 pm
Hi
ALTER proc dbo.getMaxval
(
@AllocId bigint,
@Cnt int output
)
as
set nocount on
select @cnt = max(cnt)
from AllocationVisit
where AllocId = @AllocId
set @cnt = coalesce(@cnt, 0) + 1
Thank you for helping me with the above query.
In extention to this i require a query which makes the count as 0 for the next day, even if there is an entry for the same AllocId today and then increments the count for today. And for the next day again the count should again become '0' and if there is no AllocId entry at all, the count should be '0'.
Please do the help for me.
Thanks in advance.
Regards
Mahathi.
September 17, 2008 at 12:44 am
Uhmmmmmm... Hi there,
I'm a bit confused about what you want. Could you or anyone explain a bit more. It would be better if you can give us some sample data and some sample results. Thank you ^__^
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
September 17, 2008 at 12:54 am
Hi,
The data in the table for yesterday i.e., 16th sept is
AllocId VisitTime Cnt LastUpdate
1 09/16/2008 12:19:00 PM 4 09/16/2008 12:19:00 PM
4 09/16/2008 12:19:00 PM 3 09/16/2008 12:19:00 PM
So now my requirement is the count column for the AllocId 1 and 4 or any other AllocId should be '0' for today i.e., 17th Sept for the first time.
Later when there is an entry for the AllocId for today, the count should increment.
Hope you understood.
Regards
Mahathi.
September 17, 2008 at 3:27 am
😀 Hi there,
Sorry this came late, I was a bit busy with my work...
By the wa thanks for thwe new sample... Now we're talking...
Saw your problem and I think I got what your looking for...
Try this one...
;)I hope it helps... ^__^
-- Sample Table
CREATE TABLE #Table
(
IDINT,--NO IDENTITY
ItemVARCHAR(MAX),
DateDATETIME
)
GO
-- Test Proc for Today
CREATE PROC Today
@Item Varchar(MAX)
AS
DECLARE @NewID INT
SELECT @NewID=ISNULL(MAX(ID),-1)+1
FROM #TABLE
WHERE CONVERT(VARCHAR(MAX),GETDATE(),103)=CONVERT(VARCHAR(MAX),Date,103)
INSERT INTO #Table
VALUES (@NewID,@Item,GETDATE())
RETURN
GO
-- Test Proc for Yesterday
CREATE PROC Yesterday
@Item Varchar(MAX)
AS
DECLARE @NewID INT
SELECT @NewID=ISNULL(MAX(ID),-1)+1 FROM #TABLE
WHERE CONVERT(VARCHAR(MAX),DATEADD(d,-1,GETDATE()),103)=CONVERT(VARCHAR(MAX),Date,103)
INSERT INTO #Table
VALUES (@NewID,@Item,DATEADD(d,-1,GETDATE()))
RETURN
GO
-- Test Proc for Tommorow
CREATE PROC Tomorrow
@Item Varchar(MAX)
AS
DECLARE @NewID INT
SELECT @NewID=ISNULL(MAX(ID),-1)+1 FROM #TABLE
WHERE CONVERT(VARCHAR(MAX),DATEADD(d,1,GETDATE()),103)=CONVERT(VARCHAR(MAX),Date,103)
INSERT INTO #Table
VALUES (@NewID,@Item,DATEADD(d,1,GETDATE()))
RETURN
GO
-- Let's get some data
EXEC Yesterday'Test1'
EXEC Today'Test2'
EXEC Tomorrow'Test3'
EXEC Yesterday'Test4'
EXEC Today'Test5'
EXEC Tomorrow'Test6'
EXEC Yesterday'Test7'
EXEC Today'Test8'
EXEC Tomorrow'Test9'
-- All rows
SELECT * FROM #Table
-- Yesterday
SELECT * FROM #Table
WHERE CONVERT(VARCHAR(MAX),DATEADD(d,-1,GETDATE()),103)=CONVERT(VARCHAR(MAX),Date,103)
-- Today
SELECT * FROM #Table
WHERE CONVERT(VARCHAR(MAX),DATEADD(d,0,GETDATE()),103)=CONVERT(VARCHAR(MAX),Date,103)
--Tomorrow
SELECT * FROM #Table
WHERE CONVERT(VARCHAR(MAX),DATEADD(d,1,GETDATE()),103)=CONVERT(VARCHAR(MAX),Date,103)
-- Drop the temp table
DROP TABLE #Table
Tell me if this was helpful or if it needs some more modifications... ^__^
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
September 17, 2008 at 9:54 pm
Hi
Thank you for the reply. Should I create three different procedures for today,tomorrow and yesterday. And what does the field @Item and @NewId mean?
Please clarify the above doubts.
Regards,
Mahathi.
September 18, 2008 at 6:53 pm
Hi there,
Lets take it one by one. the 3 procs are just samples, focus your sample on the TODAY proc. the three are the same only the other two stores in different dates to show the sample of everyday incrementation
@Item is just a parameter which asks wht you want to store in the table.
@ID is our incrementor or what ever you call it. everyday, it starts to increment from zero and so on. First we get the maximum value of this day's (or any day's) ID and then add it by one. if theres no existing ID, we add the one to -1 making the incrementation start from zero.
Focus on the TODAY Sproc specially on how the value of the @ID is managed.
By the way, sorry for my bad english ^__^
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
September 18, 2008 at 10:17 pm
Hi
Thank you for the help.
It works fine. If there are any doubts I'll again place it in the forum.
Thank you once again.
Regards,
Mahathi.
September 18, 2008 at 10:52 pm
Your Welcome,
Glad I could help...
^__^
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
September 18, 2008 at 10:56 pm
Thank you
September 18, 2008 at 11:06 pm
Hi
Everything works fine. One doubt is that please tell me why did you took convert(max)?
I am asking this question because, i used the same conevrt(max) to a text field. It worked fine. When I removed the max it didn't give the required output. Please let me know.
Regards
Mahathi.
September 21, 2008 at 8:09 pm
:DHi there,
:PSorry this came late, I have no network connection last friday and I'm ofline during saturdays and sundays...
:hehe:Anyway, regarding your question... Are you talking about this one?
WHERE CONVERT(VARCHAR(MAX),GETDATE(),103)=CONVERT(VARCHAR(MAX),Date,103)
You see If you input the dates to be recorded you can just do something like this
WHERE GETDATE()=Date
But if you store it using methods or functions such as GETDATE, you should stick with this one
WHERE CONVERT(VARCHAR(MAX),GETDATE(),103)=CONVERT(VARCHAR(MAX),Date,103)
Why? Because using functions such as GETDATE() stores also the time.
So the first thing we do is format them into something like this
12/25/2008
This can be done by using CONVERT(VARCHAR(MAX),GETDATE(),103)
Notice the 103, that's the date format I used which only returns the date without the time. But I forgot what format it looks like...
Anyway for the Varchar(MAX)... (You can use Varchar(20))... You need to convert int to varchar because if you convert it to date, it will just store its original format...
Oh yeah, the reason why I removed the time is because it is also involve in the comparison (=) and you won't get any other time in a particular date
You can also use LEFT()
by the way, about some of the functions and conversions mentioned, you can read them at http://www.msdn.com
^__^ Tell me if you still have some questions, we're always here.
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
September 21, 2008 at 9:33 pm
Hi Quatreix
Thank you clarifying my doubt. No problem, even if it is late.
Regards
Mahathi.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply