romeiovasu
asked on
sql query with hour group by
hi all,
i am trying to write a query where i am trying to get a result like this.
locationid storeid date time totalin totalout
1234 abc 12/01/2011 11 5 2
1234 abc 12/01/2011 16 4 1
i am attaching my data how it is store in.
can someone help me out.
arjaytelecom20101116-1-.csv
i am trying to write a query where i am trying to get a result like this.
locationid storeid date time totalin totalout
1234 abc 12/01/2011 11 5 2
1234 abc 12/01/2011 16 4 1
i am attaching my data how it is store in.
can someone help me out.
arjaytelecom20101116-1-.csv
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
In SQL Server you can use Datepart(<part>, <date>) to obtain the hour part from a time field :
http://msdn.microsoft.com/en-us/library/ms174420.aspx
Your query should be something like :
SELECT locationid, storeid, date, datepart(hh, time), sum(totalin), sum(totalout)
FROM mytable
GROUP BY date, datepart(hh, time), locationid, storeid