Calculate daily turnover in SQL Server 2008
-
I have the following data:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[mytable](
[id] [int] NOT NULL,
[userID] [nvarchar](50) NULL,
[TimeIn] [datetime] NULL,
[TimeOut] [datetime] NULL,
[Cash] [float] NULL,
[Note] [nvarchar](50) NULL,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO mytable(id,userID,TimeIn,TimeOut,Cash,Note) VALUES (1,'a','11/1/2015 7:30','11/1/2015 7:40',3000,NULL);
INSERT INTO mytable(id,userID,TimeIn,TimeOut,Cash,Note) VALUES (2,'b','11/1/2015 7:01','11/1/2015 8:30',5000,NULL);
INSERT INTO mytable(id,userID,TimeIn,TimeOut,Cash,Note) VALUES (3,'c','11/1/2015 7:20','11/1/2015 17:30',30000,NULL);
INSERT INTO mytable(id,userID,TimeIn,TimeOut,Cash,Note) VALUES (4,'d','11/1/2015 7:10','11/1/2015 21:30',50000,NULL);
INSERT INTO mytable(id,userID,TimeIn,TimeOut,Cash,Note) VALUES (5,'e','11/2/2015 7:03','11/2/2015 7:40',3000,NULL);
INSERT INTO mytable(id,userID,TimeIn,TimeOut,Cash,Note) VALUES (6,'f','11/2/2015 14:30','11/2/2015 22:30',40000,NULL);
INSERT INTO mytable(id,userID,TimeIn,TimeOut,Cash,Note) VALUES (7,'g','11/3/2015 7:30','11/3/2015 10:30',70000,NULL);
I have to count data from date A to Date B (e.g: 11/1/2015 to 11/3/2015) and From Hour to Hour (e.g: From0_12: From 0:00 to 11:59:59; From12_18: from 12:00 to 19:59:59; From18_24: from 18:00 to 23:59:59, And TotalDay: total cash in a day) The following picture illustrates this result:
I don't know how to write SQL statements (Function, View, Store or etc...) to receive the result table above.
Please Help me.
Thank you so much.
-
mrbill.mt (11/14/2015)
I have the following data:
I have to count data from date A to Date B (e.g: 11/1/2015 to 11/3/2015) and From Hour to Hour (e.g: From0_12: From 0:00 to 12:00; From12_18: from 12:00 to 18:00; From18_24: from 18:00 to 23:59:59, And TotalDay: total cash in a day) The following picture illustrates this result:
I don't know how to write SQL statements (Function, View, Store or etc...) to receive the result table above.
Please Help me.
Thank you so much.
ok.....first some clarification please.....
what does the "No" column indicate?
what column do you want your results in if for example the "TimeOut" hapeens at exactly midday (12:00:00) ?
where does the 70000 come from 11/3/2015....or is this just a typo?
are you able to provide some sample table/data for us to work with.....please read this article
http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
this is relatively easy problem to solve ...as a start I suggest you read this article
http://www.sqlservercentral.com/articles/T-SQL/63681/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day -
J Livingston SQL (11/14/2015)
mrbill.mt (11/14/2015)
I have the following data:
I have to count data from date A to Date B (e.g: 11/1/2015 to 11/3/2015) and From Hour to Hour (e.g: From0_12: From 0:00 to 12:00; From12_18: from 12:00 to 18:00; From18_24: from 18:00 to 23:59:59, And TotalDay: total cash in a day) The following picture illustrates this result:
I don't know how to write SQL statements (Function, View, Store or etc...) to receive the result table above.
Please Help me.
Thank you so much.
ok.....first some clarification please.....
what does the "No" column indicate?
what column do you want your results in if for example the "TimeOut" hapeens at exactly midday (12:00:00) ?
where does the 70000 come from 11/3/2015....or is this just a typo?
are you able to provide some sample table/data for us to work with.....please read this article
http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
this is relatively easy problem to solve ...as a start I suggest you read this article
"No." column is ordinal number (first record, second record,...), you can ignore this column.
Column From0_12: From 0:00 to 11:59:59; From12_18: from 12:00 to 17:59:59; From18_24: from 18:00 to 23:59:59, And TotalDay: total cash in a day. I think you can understand easily what i mean. So "TimeOut" happens at exactly midday (12:00:00) is belong to From12_18 column.
Thanks.
-
mrbill.mt (11/14/2015)
J Livingston SQL (11/14/2015)
mrbill.mt (11/14/2015)
I have the following data:
I have to count data from date A to Date B (e.g: 11/1/2015 to 11/3/2015) and From Hour to Hour (e.g: From0_12: From 0:00 to 12:00; From12_18: from 12:00 to 18:00; From18_24: from 18:00 to 23:59:59, And TotalDay: total cash in a day) The following picture illustrates this result:
I don't know how to write SQL statements (Function, View, Store or etc...) to receive the result table above.
Please Help me.
Thank you so much.
ok.....first some clarification please.....
what does the "No" column indicate?
what column do you want your results in if for example the "TimeOut" hapeens at exactly midday (12:00:00) ?
where does the 70000 come from 11/3/2015....or is this just a typo?
are you able to provide some sample table/data for us to work with.....please read this article
http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
this is relatively easy problem to solve ...as a start I suggest you read this article
"No." column is ordinal number (first record, second record,...), you can ignore this column.
Column From0_12: From 0:00 to 11:59:59; From12_18: from 12:00 to 17:59:59; From18_24: from 18:00 to 23:59:59, And TotalDay: total cash in a day. I think you can understand easily what i mean. So "TimeOut" happens at exactly midday (12:00:00) is belong to From12_18 column.
Thanks.
did you get the chance to read the two articles I pointed you to?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day -
J Livingston SQL (11/14/2015)
mrbill.mt (11/14/2015)
J Livingston SQL (11/14/2015)
mrbill.mt (11/14/2015)
I have the following data:
I have to count data from date A to Date B (e.g: 11/1/2015 to 11/3/2015) and From Hour to Hour (e.g: From0_12: From 0:00 to 12:00; From12_18: from 12:00 to 18:00; From18_24: from 18:00 to 23:59:59, And TotalDay: total cash in a day) The following picture illustrates this result:
I don't know how to write SQL statements (Function, View, Store or etc...) to receive the result table above.
Please Help me.
Thank you so much.
ok.....first some clarification please.....
what does the "No" column indicate?
what column do you want your results in if for example the "TimeOut" hapeens at exactly midday (12:00:00) ?
where does the 70000 come from 11/3/2015....or is this just a typo?
are you able to provide some sample table/data for us to work with.....please read this article
http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
this is relatively easy problem to solve ...as a start I suggest you read this article
"No." column is ordinal number (first record, second record,...), you can ignore this column.
Column From0_12: From 0:00 to 11:59:59; From12_18: from 12:00 to 17:59:59; From18_24: from 18:00 to 23:59:59, And TotalDay: total cash in a day. I think you can understand easily what i mean. So "TimeOut" happens at exactly midday (12:00:00) is belong to From12_18 column.
Thanks.
did you get the chance to read the two articles I pointed you to?
OK, thanks. You can read my question again (F5 to refresh). Thanks your link (two articles).
Please focus on my question and answer it.
Thank you so much.
-
here is one way....this should get you started.....
SELECT
CONVERT(varchar(10) , TimeOut , 101) AS yourdate
, SUM(CASE
WHEN DATEPART(hour , timeout) >= 12
AND DATEPART(hour , timeout) < 18 THEN cash
ELSE 0
END) AS from12_18
, SUM(Cash) AS totalday
FROM mytable
GROUP BY
CONVERT(varchar(10) , TimeOut , 101);
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day -
J Livingston SQL (11/14/2015)
here is one way....this should get you started.....SELECT
CONVERT(varchar(10) , TimeOut , 101) AS yourdate
, SUM(CASE
WHEN DATEPART(hour , timeout) >= 12
AND DATEPART(hour , timeout) < 18 THEN cash
ELSE 0
END) AS from12_18
, SUM(Cash) AS totalday
FROM mytable
GROUP BY
CONVERT(varchar(10) , TimeOut , 101);
wow, thank you so much. Great! You are the best.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply