Need Urgent Help in MS SQL Express Query!

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)

Need Urgent Help in MS SQL Express Query!

Author

Topic

ivan603
Starting Member

4 Posts

Posted - 2008-09-07 : 21:41:04

Hi all,
I need a help in MS SQL Express query. I have a table named "tblTotalCust" and the fields are:
[Date] date, [ServedTime] date, [TotalCustomer] int.

Example data:
[Date] [ServedTime] [TotalCustomer]
8/9/2008 12:00:00AM 8/9/2008 9:23:00AM 12
8/9/2008 12:00:00AM 8/9/2008 9:27:00 AM 7
8/9/2008 12:00:00AM 8/9/2008 9:37:00AM 9
8/9/2008 12:00:00AM 8/9/2008 10:45:00AM 25
8/9/2008 12:00:00AM 8/9/2008 10:57:00AM 11
8/9/2008 12:00:00AM 8/9/2008 11:32:00AM 6

In MS Access, my query is :
SELECT Date, TimeSerial(0, ((HOUR(ServedTime) * 60 + MINUTE(ServedTime))\30)*30,0) AS TimeOfDay, SUM(TotalCustomer) AS TotalCustomer GROUP BY Date, TimeSerial(0,((HOUR(ServedTime) * 60 + MINUTE(ServedTime))\30)*30,0)

This will produce a sum TotalCustomer in each 30 minute of ServedTime as below:
[Date] [TimeOfDay] [TotalCustomer]
8/9/2008 12:00:00AM 8/9/2008 9:00:00AM 19
8/9/2008 12:00:00AM 8/9/2008 9:30:00AM 9
8/9/2008 12:00:00AM 8/9/2008 10:30:00AM 36
8/9/2008 12:00:00AM 8/9/2008 11:30:00AM 6

But how do i write this query in MS SQL Express? Please help. Thanks

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-09-07 : 22:14:38

have a look at the DATEPART function

elsasoft.org

ivan603
Starting Member

4 Posts

Posted - 2008-09-07 : 23:29:50

How to use DATEPART in such query?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-08 : 03:10:49

quote:
Originally posted by ivan603

How to use DATEPART in such query?

Read about it in sql server help file

Madhivanan

Failing to plan is Planning to fail

ivan603
Starting Member

4 Posts

Posted - 2008-09-08 : 06:10:41

Actually i'm beginner in MS SQL, i don't know how to change the query from MS Access to MS SQL. Please help. Thanks

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-09-08 : 06:30:17

[code]select datepart(hour, current_timestamp)
select datepart(minute, current_timestamp)
[/code]

ivan603
Starting Member

4 Posts

Posted - 2008-09-08 : 09:58:40

Hi,
I have created a query to solve my problem. Please comment:

SELECT Qry.BranchCode, Qry.Date, Qry.TOD, SUM(Qry.TotalServed)AS Total
FROM(
SELECT BranchCode, Date, ServedTime,
Convert(datetime,
Convert(varchar, ServedTime, 101)
+ ' ' +
Convert(varchar, DATEADD(ss, (SUM(DATEPART(HOUR, ServedTime) * 3600 +
DATEPART(MINUTE, ServedTime) * 60 + DATEPART(SECOND, ServedTime))
/ 1800) * 1800 ,0) ,108) ,120)AS TOD,
SUM(CASE WHEN REmark='N' THEN 1 ELSE 0 END)AS TotalServed
FROM tblTotalCust WHERE Remark='N' GROUP BY BranchCode, Date, ServedTime
)AS Qry GROUP BY Qry.BranchCode, Qry.Date, Qry.TOD

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-09-08 : 10:20:16

This may help you out:

http://weblogs.sqlteam.com/jeffs/archive/2007/03/30/Quick-Access-JET-SQL-to-T-SQL-Cheatsheet.aspx

Also, if you need a TimeSerial() function, see the dbo.Time() function here:

http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources