Porblem with dates 2

[Cpreston]

Hi

I need to make 2 querys which give me todays totals and also a Weeks totals

Both will be separate. My date field is a datetime format. I have tried many ways and currently my query looks like this

select sum(TotalSellPrice), sum(TotalCostPrice), sum(TotalVolume), DateTimeCreated DEFAULT GETDATE() from OrderHeader
where OrderStatus <> 0 AND OrderStatus <> 1 AND OrderStatus <>2

It does not recognise the Default. Any ideas please on this query and also how I can get it to look at the current week using another query.

Thanks

 

[jbenson001]

I have never seen syntax like that in SQL Server except when creating a column in a table.
What do expect the DEFAULT keyword to do for you?

 

[bborissov]

You can't have DEFAULT in query,

select sum(TotalSellPrice), 
       sum(TotalCostPrice), 
       sum(TotalVolume), 
       GETDATE() AS DateTimeCreated 
from OrderHeader
where OrderStatus <> 0 AND OrderStatus <> 1 AND OrderStatus <>2

Borislav Borissov
VFP9 SP2, SQL Server

 

[Cpreston]

Hi

Jbenson001, I want to get a sum of todays sales figures and it always look at todays date The following query gives me the correct sums but of course it is just looking at today as a fixed date

select sum(TotalSellPrice), sum(TotalCostPrice), sum(TotalVolume) from OrderHeader where DateTimeCreated > '2015-06-10 00:00:00'
AND OrderStatus <> 0 AND OrderStatus <> 1 AND OrderStatus <>2 AND OrderStatus <> 7 AND OrderStatus <>8

BBborissov - your query appears to be summing much more than todays figures (any ideas)

Thanks

 

[Cpreston]

Also the DateTimeCreated is the name of a field in the orderheader table and is a datetime format

 

[jbenson001]

If you only want to sum for today's date, then you have to add the condition to your WHERE clause

WHERE   DateTimeCreated >= DATEADD(day, datediff(day,0,getdate()),0) AND  DateTimeCreated <= DATEADD(day, datediff(day,0,getdate()),0) + 1

There may be some more efficient ways to do this, but I figured this is a good place to start.

 

[Cpreston]

Hi

That works great thanks, so my next question is can it be done for a sum of the current week, lets say first day is always Monday (if that is possible)

Thanks for your posts

 

[jbenson001]

Sure it can be done. You want dates from the Previous Monday to the current date?

 

[Cpreston]

err yes so it would for example look at Monday 8th and sum up until Sunday 14th
Then start fresh with Monday 15th next week. Hope that makes sense, so it is always adding up the current week.

 

[jbenson001]

You can try something like this:

WHERE (DateTimeCreated >= DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0) AND DateTimeCreated < SELECT DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 8))

 

[Cpreston]

Hi

I am getting this error

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ')'.


The query looks like this

select sum(TotalSellPrice), sum(TotalCostPrice), sum(TotalVolume) from OrderHeader
WHERE (DateTimeCreated >= DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0)AND DateTimeCreated < SELECT DATEADD(wk, DATEDIFF
(wk, 0, GETDATE()), 8))AND OrderStatus <> 0 AND OrderStatus <> 1 AND OrderStatus <>2 AND OrderStatus <> 7 AND OrderStatus
<>8

 

[jbenson001]

Remove the "SELECT"... it was a copy and paste error

 

[Cpreston]

Sorry did not see your post, yes I took the Select out and it works perfect. Many thanks for your posts you have been very helpful

 

[jbenson001]

Glad to help. There is also plenty of info out there if you Google as well.