DATE FUNCTION - 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

General SQL Server Forums

New to SQL Server Programming

DATE FUNCTION

Author

Topic

rahulmalhotra26
Starting Member

23 Posts

Posted - 2008-01-23 : 20:08:48

i have just started working on SQL and i am trying to solve this puzzle maybe smoe one could do it.. i have to make a query such that
it will take all the transactions throughout the day sort them with the accnt number and then give total amount in the transaction, also the total amount should be > 10000 if some one can try and locate the problem with hte logic i would appreciate it..

just for instance what i did was i tried using the convert in group by and then sum(deposit) this doesnt work.. i am really confused please help.

i have written this query:

declare @datelastweek as datetime
declare @yesterday as datetime
declare @date as datetime
set @Date = Convert(varchar,GetDate() - 1,1)
SET @datelastweek = DATEADD(Day, 1, (DATEADD(Week, -1, @Date)))
SET @yesterday = dateadd(day,1,(dateadd(day,-1,@date)))

select accountno, sum(amount), convert (varchar, TransactionTime -1,1)
from deposit tb join transaction t on tb.id=t.id
where TxnTime between @datelastweek and @date
group by DATEADD(d,DATEDIFF(d,0,TxnTime),0),t.Accountno, txntime
having sum(tb.amount)>10000

Rahul

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-23 : 20:13:04

try this


select accountno, sum(amount), convert (varchar, TransactionTime -1,1), DATEADD(d,DATEDIFF(d,0,TxnTime),0)
from   deposit tb join transaction t on tb.id = t.id
where  TxnTime between @datelastweek and @date
group by DATEADD(d,DATEDIFF(d,0,TxnTime),0), t.Accountno , txntime
having sum(tb.amount) > 10000

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

rahulmalhotra26
Starting Member

23 Posts

Posted - 2008-01-23 : 20:22:24

Still its not giving the output,. sorry man

Rahul

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-23 : 20:31:08

any error ? what is the expected output ? Also post your sample data for the expected output

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

rahulmalhotra26
Starting Member

23 Posts

Posted - 2008-01-23 : 20:37:50

hang on let me check if i can get the report format in which it should be printed after the final result,, i wil check some thinga nd post back thanks man.. thats kindda format i want:
This report runs weekly and gives me output for wed-- thursday thats why i have used the between clause to get the time..
Account 11047124 for 18/01/2008:
17:02:47 at off NO Semaphore, DEP CASH xxx
17:02:55 at off No Semaphore, DEP CASH xxx
Total: >10000

Rahul

rahulmalhotra26
Starting Member

23 Posts

Posted - 2008-01-23 : 20:39:08

and the report goes so on after that giving everyday number of transactions and then the total figure.. and ghave to make sure that total figure is above 10000 no error just the output doesnt match to what i want

Rahul

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-23 : 20:42:10

please post your table DDL, sample data and the corresponding required result

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

rahulmalhotra26
Starting Member

23 Posts

Posted - 2008-01-23 : 21:02:53

It goes like this the structure of the table

account number amount transtime
101 5000 2008-01-18 11:58:39.523
102 10000 2008-01-21 12:00:39:365
103 12000 2008-01-21 17:30:39:265
102 12500 2008-01-21 17:00:39:896

Account 102 for 21/01/2008:
12:00:39:365 at off NO Semaphore, DEP CASH 10000
17:30:39:265 at off No Semaphore, DEP CASH 12000
17:00:39:896 at off no semsphore, Dep cash 12500
Total deposit: 34500

So what i am trying to do is sort out the results with deposits more then 10000 and then getting the total at the end of the day.

Rahul

rahulmalhotra26
Starting Member

23 Posts

Posted - 2008-01-23 : 21:04:24

account number amount transtime
101 5000 2008-01-18 11:58:39.523
102 10000 2008-01-21 12:00:39:365
103 12000 2008-01-21 17:30:39:265
102 12500 2008-01-21 17:00:39:896

i guess is more readable now

Rahul

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-23 : 21:09:35

this ?

DECLARE @acc         int,
        @transdate   datetime

SELECT 	@acc = 102,
       	@transdate = '20080121'
SELECT 	account_number, amount, transtime
FROM   	yourtable
WHERE  	account_number = @acc
AND   	transtime      >= @transdate
AND	transtime      <  DATEADD(DAY, 1, @transdate)
AND	amount 		> 10000

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-23 : 21:10:13

you can get the total deposit with simple sum() on a separate query

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

rahulmalhotra26
Starting Member

23 Posts

Posted - 2008-01-23 : 22:27:19

hey buddy i have got 10000 of records i cant specify accnt number as you did, as it is what i wanna retrieve ,, i just know the dates with which i have to sort this data, please see if any one can help

Rahul

rahulmalhotra26
Starting Member

23 Posts

Posted - 2008-01-23 : 22:51:24

I CAN SORT THE RECORDS OUT NOW.. THANKS MAN

Rahul

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources