August 11, 2006 at 3:03 am
Why wait until Monday? Just put in Monday's date instead of GETDATE()!
John
August 11, 2006 at 3:07 am
Yah done the same thing but out put is not correct
August 11, 2006 at 3:12 am
It look like it's not adding only friday and saturday data because total sum is bigger then original sum what i have for friday to sunday
August 11, 2006 at 3:48 am
I have tested the expression from John's solution, and it evaluates to 4 if date is Monday and 2 if day is other than Monday. This is precisely what you have in your query. Since the rest of the query is unchanged, it should give the same results as your original query... Are you sure you didn't introduce any typos? And does your originally posted query work well? /EDIT - See my next post/
You can simplify the date concatenation:
CREATE VIEW dbo.YS AS
SELECT Salesperson, SUM(InvoicedValueBase) AS YesterdaySales
FROM dbo.SalesF
WHERE CAST(CAST(19000000 + DateInvoiced AS CHAR(8)) AS DATETIME) > GETDATE() + 2 * SIGN((@@datefirst + DATEPART(dw, GETDATE()) - 2) % 7 ) - 4
GROUP BY ALL Salesperson
August 11, 2006 at 4:25 am
I had a good laugh now when I realized what's the problem... maybe if you try it on Monday, it will work fine :-))
You have no upper date limit in your query, so if you try it on you table full of data with last Monday date, the sum includes everything from Friday to today.
Nevertheless, John's solution works well, and the simplification from my previous post works, too.
August 11, 2006 at 6:10 am
Thanks Vladan
I changed system date from 11th to 7th and double checked that which date getdate is giving me and it was monday means 7th and after that i posted my reply and also double checked my total...
John's query is working well on yesterday data anly on monday showing me total sales value more then original means sum of friday and saturday sales...
I am not a old user of SQL so i again check everything and try your query also might be doing something wrong again
August 11, 2006 at 6:37 am
Many Thanks Vladan & John
After reading your post i made one view which was having data upto friday and again by changing system date to monday i tried it and it's working.
You were right it was taking data from friday to today.........
Thanks you very much, it was not possible to do without your help.
Kind Regards,
Vandy
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply