Compare Today And Yesterday Time Range On Sales
Aug 23, 2014
I have to do a report for each hour compare yesterday each hour of sales amount their output is below, how to write a query.
desire output 08:00 am -23:00pm
shop today Time yesterday current Amt Yest Amt diff amount
001 13:00-14:00 13:00-14:00 $10000 $20000 -10000 (down)
002 14:00-15:00 14:00-15:00 $10000 $15000 500 (up)
Time as at HH:MM
Yesterday Total $20000
today Total $35000
View 2 Replies
ADVERTISEMENT
May 21, 2008
This is a cross-post from the Office PPS Planning site: http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=3379691&SiteID=17 I was hoping there may be some additional MDX resources here.
I'm trying to determine if the currentmember of the time dimension is > or < the value of today. I want to use this to change the the value of a member that I display on columns from actual (for prior to today) to forecast (after today). I have this MDX:
with
member [measures].[PYCY] as IIf(VBA!CInt(Format(VBA!Now(),"MM"))<10, VBA!CStr(VBA!CInt(VBA!Year(VBA!Now())-1)), VBA!CStr(VBA!CInt(VBA!Year(VBA!Now())+0)))
member [measures].[CYNY] as IIf(VBA!CInt(Format(VBA!Now(),"MM"))<10, VBA!CStr(VBA!CInt(VBA!Year(VBA!Now())+0)), VBA!CStr(VBA!CInt(VBA!Year(VBA!Now())+1)))
member [measures].[NYNY2] as IIf(VBA!CInt(Format(VBA!Now(),"MM"))<10, VBA!CStr(VBA!CInt(VBA!Year(VBA!Now())+1)), VBA!CStr(VBA!CInt(VBA!Year(VBA!Now())+2)))
member [measures].[test2] as Format(Now(), "yyyyMM")
member [measures].[test4] as [Time].[Day View].CurrentMember.Member_Key
member [measures].[diff] as DateDiff('m', Format(Now(), "yyyyMM"),[Time].[Day View].CurrentMember.Member_Key)
select
StrToSet('{' + Generate(Descendants(
{strtomember('[Time].[Day View].[year].&[' + [measures].[PYCY] + ']'), strtomember('[Time].[Day View].[year].&[' + [measures].[CYNY] + ']'), strtomember('[Time].[Day View].[year].&[' + [measures].[NYNY2] + ']') }
, [Time].[Day View].[Month]), [Time].[Day View].CurrentMember.UNIQUE_NAME, ", ") + '}')
* {[measures].[test2],[Measures].[test4],[measures].[diff]}
on 0,
[Scenario].[All Members].members
on 1
from [SalesForecasting]
Here is the result for 2 months:
October FY2007
October FY2007
October FY2007
November FY2007
November FY2007
November FY2007
test2
test4
diff
test2
test4
diff
All
200805
200610
#Error
200805
200611
#Error
None
200805
200610
#Error
200805
200611
#Error
Actual
200805
200610
#Error
200805
200611
#Error
Forecast
200805
200610
#Error
200805
200611
#Error
Override
200805
200610
#Error
200805
200611
#Error
Plan
200805
200610
#Error
200805
200611
#Error
The error is: #Error Execution of the managed stored procedure DateDiff failed with the following error: Exception has been thrown by the target of an invocation.Argument 'Date1' cannot be converted to type 'Date'..
Why can't DateDiff calculate the difference between 200805 & 200610?
Thanks
View 5 Replies
View Related
Nov 15, 2007
I converted an MS Access db to SQL Server 2005 Express yesterday. I used FullConvert Enterprise for the conversion and it worked great. I ran several queries and saved them, and they ran fine. Today, running the same queries, I get this error message:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'tblTXMup'
I googled the message and found someone who had a similar problem and their answer was they were not the dbo. I checked the new database and it was owned by sa so I logged in as sa and got the same error.
Can anyone set me straight so I can get into this db?
Thanks,
View 14 Replies
View Related
Nov 15, 2007
I converted an Access db to SQL Server 2005 Express using FullConvert Enterprise and the conversion went well. I ran a few queries. When I logged in today and run the same exact query (saved it) I get this error:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'tblTXMup'.
I googled the error and someone in another forum said to check ownership, that it would throw that error if another user who was not dbo tried to run a query. I checked and the owner is sa. I logged in as sa and get the same error.
Anyone have an idea what happened?
View 5 Replies
View Related
Oct 16, 2007
OK, the thread's name is not very self explanatory so here I go:
The fact
I'm working on a simple APP where I store the date of a transaction. I use in my Stored Procedure the getdate() as the default (and untouchable) value for the transaction date
The problem:
These transactions are made all day long and there are transctions made on the morning and afternoon. Now, when I make a report of the transactions for one specific range of days, all the transactions made in the morning of the start date are ignored completely.
Say I have these transactions
TCode TType TDate
45 REQ 03/09/2007 08:20:16 a.m.
46 REQ 03/09/2007 11:59:43 a.m.
47 REQ 03/09/2007 12:01:30 p.m.
48 REQ 03/09/2007 06:01:49 p.m.
and I make this query
SELECT * FROM <Table Name> WHERE TDate BETWEEN <Start Date> AND <End Date>
(where <Start Date> && <End Date> are 03/09/2007 selected from a DateTimePicker control)
I only get these results 47 REQ 03/09/2007 12:01:30 p.m.
48 REQ 03/09/2007 06:01:49 p.m.
if I make a cast as integer I get these results:
TCode TDate TCast
46 2007-09-03 11:59:43.000 39326
47 2007-09-03 12:01:30.000 39327
so my question is
Why are the Morning Transactions from the start date ignored (or interpreted as part of previous day transactions) ?
View 1 Replies
View Related
Jan 13, 2006
I need a where clause like
Where field=getdate()
but I can't get it to work because the field is datetime so there is never an exact match. Do I need to break out each part just to compare today's date to the 'date' of getdate()
View 14 Replies
View Related
Aug 19, 2013
I want to get all records that have a valid date range for todays date(20130819).
All records have a date_f and date_t. I need to check that against todays date. The below code is my version of sql pseudo code.
SELECT DISTINCT
p.id,
p.name,
c.ip_number
FROM
tbl_Person AS p, tbl_current_conn_ipnumber AS c
[Code] .....
View 2 Replies
View Related
Jan 26, 2006
I am trying to get the yesterday's date without the time, eg "1/25/2006 " but I will get "2006-01-25 17:10:21.403" instead. Can somebody tell me the sql for it? currently I am using :
CONVERT(DATETIME, DATEADD(day, -1, GETDATE()), 101))
View 5 Replies
View Related
Aug 16, 2006
I am attempting to write a SQL query that retrieves info processed between two times (ie. 2:00 pm to 6:00 pm) during a date range (ie. 8/1/06 to 8/14/06)... I am new to SQL and am perplexed... I have referenced several texts, but have not found a solution. Even being pointed in the right direction would be greatly appreciated!!
View 6 Replies
View Related
May 15, 2008
My main datasource is a rather poorly written and documented SQL database. I am currently working in SQL Server 2000 but will be upgrading to 2005 in 6 months to a year. There are three sales order tables.
SOMAST Sales Order Master Table
SOITEMS SO Items Table
SORELS SO Releases Table
My employer wants to track how our sales orders change over time. This would be a nightly process. They want to track changes in certain columns such as price and quantity to see if they differed from yesterday and to keep those changes separate in another table or set of tables to track them. This of course would include newly entered sales orders for that day as well. Our current erp system does not support this.
This seems like a huge task to a neophyte like myself, but I am tasked with doing this. Am I correct in assuming the correct method would be a stored procedure that does the following:
1. Check the current tables at end of day today and compare them with a saved version of yesterday's tables.
2. Insert into a 3rd table (or set of them) the differences.
3. Copy today's tables over yesterday's tables so they are available tomorrow.
I realize this task is difficult, but am I at least starting in the right direction?
Experts Only Please. (jk)
View 17 Replies
View Related
Aug 2, 2000
I want to write a query to retrieve all the items that are
order between 2 dates range, 07/30/2000 to 08/02/2000, any suggestions are
greatly appreciated,
View 4 Replies
View Related
Nov 15, 2007
Hi, I really need this help urgently.
I need to send an email when the dueDate(field name in database) is equal to today's date... I have come out with this code with the help of impathan(jimmy i did not use ur code cos i not very sure sry)... below is the code with no error... but it jus wun send email...
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load, Me.Load
con1.Open()
Dim cmd As New SqlCommand
cmd.CommandText = "select * from custTransaction where convert(datetime,dueDate,101) = convert(datetime,GetDate(),101)"
'Set the connect the command object should use
cmd.Connection = con1Dim da As New SqlDataAdapter(cmd)Dim ds As New DataSet
da.Fill(ds)
con1.Close()
If Not ds.Tables(0) Is Nothing ThenIf ds.Tables(0).Rows.Count > 0 Then
Dim objEmail As MailMessage = New MailMessage
objEmail.From = New MailAddress("my@email.com.sg")objEmail.To.Add(New MailAddress("my@email.com.sg"))
objEmail.Subject = "Due Date Reaching"objEmail.Body = Session("dueName")
objEmail.Priority = MailPriority.Normal
Dim SmtpMail As New SmtpClient("servername")
SmtpMail.Send(objEmail)
End If
End If
End Sub
Note: I am veri sure that database has the data field dueDate with the value 11/16/2007 smalltimedate(mm/dd/yyyy)
Realli veri urgent Thanks so much for ur'll help
View 8 Replies
View Related
Apr 16, 2007
I all.
In a talbe I've a datatime field. for example it contain '16-4-2007 10:45'.
I like to write a SQL that return all record with the date field equals '16-4-2007' (it's not important the time). how to?
thank you.
View 2 Replies
View Related
Jul 27, 2006
Hi,
I have built a Sales Forecast model to predict the sales
value.
Along with making historic predictions for previous time
periods I also want to retrieve the actual sales values for those periods.
How can I achieve this in a time series model?
I also would like to know how do mining models store the data.
Do they store the data in the same table/view format
as their respecive data source view or in the Model Content format.
View 3 Replies
View Related
May 19, 2015
i am trying to find a way to link an 'initial' Sale ID of a product to 'future' Sale IDs of products that will trace back to the original Sale ID.For example, if I call the original sale , 'Sale ID #123', how can i link future Sale ID's (child[ren]) and all future sales to the original Sale ID #123? Can I use a Surrogate Key or similar function?
Parent:Sale ID #123
Children:Â Sale ID # 456,
Sale ID #789,
Sale ID #.....
how I can link the original Sales ID (Parent) to Sale ID's (child[ren]) of future purchases currently existing and in the future going forward?
View 4 Replies
View Related
Dec 9, 2014
I have the below data. I need to produce a report that shows customer and total sales who had the max sales by year.
Order ID Cust ID Year Sales
O1 C1 2000 100
O2 C1 2000 150
O1 C2 2000 50
O1 C1 2001 150
O2 C3 2001 200
Report:
Cust ID Year Sales
C1 2000 250
C3 2001 200
View 1 Replies
View Related
Apr 21, 2008
Hi there
I am using SQL2005 and would like to check the whether the time is within the valid range for instance:
Data to check: 2:15AM
Range is between: 01:00AM till 06:59AM, 07:00AM till 02:00 PM
I can use the datepart for hh but that is not what I want. I want to have a full time: HH:MM cause sometimes if you do datepart by hour and if you apply into the above excample will be:
2 BETWEEN 1 AND 6 (this is ok)
2 BETWEEN 7 AND 2 (this is not ok)
Any idea?
View 3 Replies
View Related
Oct 5, 2007
Hello,I have a table that lists a number of available time slots for a party venue. I want a user to select a particular time slot and have the query show results for that time slot plus two time slots before and two time slots after the user's selection. If the user selects a time slot that is the first of the day, I still want that user to be shown 5 results including the one he selected. The same goes for the last time slot of the day. Can anyone help me with the SQL statement?
Thank you very much in advance!
Mark
View 12 Replies
View Related
Jul 5, 2007
Hi there,
With a DateStamp field
Im trying to group by Day Name
and also group it By Time ranges
i.e. Morning 00:00 to 11:59
Afternoon 12:00 to 17:59
Evening 18:00 to 23.59
I can do the group By Day Name ok
Like Below
Code:
SELECT COUNT(ISNULL(createdDate, 1)) AS DayCount, ISNULL(DATENAME(dw, createdDate), 'No Date Set') AS DayOftheWeek
FROM dbo.MyTable
GROUP BY ISNULL(DATENAME(dw, createdDate), 'No Date Set')
its just the grouping by time ranges im having trouble with..
Code:
M | T | W | Th | F | S | Su
Morning ||
Afternoon ||
Evening ||
Thanks for any help ..
View 5 Replies
View Related
May 7, 2014
I have a query to determine the time range like following
SET @StartDate = CAST (DATEDIFF(d, 0, DATEADD(d, 1 - day(getdate()), getdate()))as datetime)
SET @EndDate = GetDate()
What time range that query set ??
View 3 Replies
View Related
Sep 17, 2007
I want to create one complex query with Date time field,  my query is I want a grouping   between the time range
Â
EX:
Â
13/09/2007 1:00:00 PM    to  14/09/2007 4:00:00 AM    will be as Day1
14/09/2007 1:00:00 PM    to  15/09/2007 4:00:00 AM    Will be as Day2
''
''
''
This way the end date for the period will be 13/10/2007 4:00:00Â as Day 30
pavan
View 4 Replies
View Related
Nov 18, 2013
I have to compare two time duration for a resource booking system. for example if a resource is booked for 9.00am to 2.00am ,it must not be booked during that duration and other coinciding times e.g 1.00pm to 2.00 pm should not be booked or even 9.00 am to 5.00pm should not be booked.
how can i write a sql query for this.
View 3 Replies
View Related
Oct 19, 2006
Hello,I need to store the start and end time of each weekly class into adatabase. so that people can easily search for classes based on time.for example, someone might want to search all classes that are offeredbetween 9am and 2pm.How can i implement this time field? i don't have a date to store withthe hour and minutes. is there and way sql server can tell 2pm isbigger than (>) 9am?thank you in advance,eddy
View 2 Replies
View Related
Nov 26, 2003
Hi everyone, this is the exception:
System.Data.SqlClient.SqlException: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value
Here's my code!
... 'datagrid code
cmdSelect.CommandText = "SELECT * FROM [Table] WHERE [Date and Time] > '" & Me.tbFromDate.Text & "' AND [Date and Time] < '" & Me.tbToDate.Text & "'"
...
Me.tbFromDate.Text and Me.tbFromDate.text look like this: 27/11/2003 1:11:43 PM
The SQL Query works fine - just that this code gives a runtime error.
Can someone help?? I'm guessing I have to do somekind of conversion!!
Andrew
View 1 Replies
View Related
Jan 5, 2014
I have a date/time value (dd/MM/yyyy HH:mm:ss) stored in a field as part of each record in a dataset.
I would like to achieve the following via a single T-SQL query if possible (efficiency is not an issue as the dataset is small)
On query run;
Check if any records in the dataset are within 10 minutes + or - of the current system time (using the date/time value referenced above)
if none
Load the first record
View 6 Replies
View Related
Aug 29, 2007
hi guyz i want to compare time from DateTime field i.e. i want to identify if the time is from 1pm to 2pm the do this else do......
select DATEPART(hour, loginTime) ......returns me the hour i can get the Hour part of the time but the prblem is how to identify it
whether it is less than 2:00:00 pm and greater than 1:00:00 pm i can do this task using at application level but i want this to b done at query level
any ideas??????????
View 2 Replies
View Related
Jan 13, 2012
Is it possible to send a sql statement to update column 1 where the current time is 10 mins older than the time in col 2.
Column 1 is holding a true statement that we need to update to false if over 10 mins old...
View 8 Replies
View Related
Sep 30, 2015
I want to calculate the sum of actual sales until a date and forecast sales after a date.I am not sure what the best approach to this problem is, but I have tried my best with the following approach. Any better ways to solve this (using DAX).
I have created a parameter table that offers the last date of each month as possible choices to the user. I have tried to create a measure that sums actual sales up until this date.
SalesQuantityActual:=IF(HASONEVALUE(parLastActualMonth[Date]);CALCULATE(factSalesActual[Quantity];factSalesActual[Date]<=VALUES(parLastActualMonth[Date]));BLANK())
Unfortunately the measure above does not work.
In addition to the parameter table, I also have a normal date table.
View 2 Replies
View Related
Oct 12, 2006
I have reservation database, suppose somebody reserved a resource on 10/12/2006 from 9:00am to 12pm. If anybody else want to reserve the same resource from 10am to 3pm. It will not let them reserver. I would like to check a range in store procedure. Is there has any function to check range in easy way?
Many thanks.
View 13 Replies
View Related
Sep 7, 2006
how to synchronize and compare two database in sql server 2000 without using any others software in real time.
my purpose is to check the two database in two different server if there has any different. And synchronize these two database continusly.
if one database had change, the other will also change
View 4 Replies
View Related
Sep 7, 2006
how to synchronize and compare two database in sql server 2000 without using any others software in real time.
my purpose is to check the two database in two different server if there has any different. And synchronize these two database continusly.
if one database had change, the other will also change
View 3 Replies
View Related
Dec 26, 2013
I come in to work 6.30am, and need to audit what happened from 5pm when I left to 6.30am this morning. I have used code to search 13.5 hours back from any given time:
SELECT * FROM TRANSACTION_HISTORY
WHERE TRANSACTION_HISTORY.ACTIVITY_DATE_TIME > (SELECT DATEADD(hour,-13.5,(SELECT MAX (TRANSACTION_HISTORY.ACTIVITY_DATE_TIME) FROM TRANSACTION_HISTORY)))
Problem is if I run query later, I lose time from the start, eg. If I run query at 7am, I only get results from 5.30pm onwards. Rather than change criteria every day, I wanted to able to search from 6.30am of the current day, back to 5.30pm of the previous day.
View 3 Replies
View Related
Aug 28, 2014
How to edit this query to show the total by shop on time range ?
current result.(Time range from 9:00am-23:00pm)
Shop Time_slot cur Amt, yest Amt, Diff Amt, Sales Direction
Abc 10:59 $100 $50 +50 (+)
Abc 11:59 $100 $50 +150 (+)
Abc 12:59 $100 $50 +50 (+)
BBB 11:59 $100 $50 +150 (+)
BBB 12:59 $100 $50 +50 (+)
------------------------------------------------------------------
Desired Result .
Shop Time_slot cur Amt, yest Amt, Diff Amt, Sales Direction
Abc 10:59 $100 $50 +50 (+)
Abc 11:59 $100 $50 +150 (+)
Abc 12:59 $100 $50 +50 (+)
Total $300 $150 +$200 (+)
BBB 11:59 $10 $50 -40 (-)
BBB 12:59 $10 $50 -40 (-)
Total $20 $100 -80 (-)
-----------------------------------------------------------
select shop
,ltrim(str(datepart(hh,yourdatetimefield)))+':00 - '+ltrim(str(datepart(hh,yourdatetimefield)))+':59' as time_span
,sum(case
when datediff(dd,yourdatetimefield,getdate())=0
[Code] .....
View 1 Replies
View Related
