May 15, 2011 at 11:57 pm
hi all
im fairly new to SQL so struglling a bit here,
i have a table where it has the Daily sales, and a date value
i have to show the Sales value as a MTD fig, and where the date is queried.
i.e i have 5 days worth of sales value, and the date is queried for the 4th day, it should then add the values from the 1st, 2nd, 3rd and the 4th day and show as the 4th day value.
this is what i have so far
SELECT
Sum([DailyValue]) as MTDValue, [Date_in_char]
from
[TEST].[dbo].[test]
Where [Date_in_char] between 'DATEADD(dd,-(DAY(DATEADD(mm,1,[Date_in_char]))-1),DATEADD(mm,0,[Date_in_char]))'
and '2011-02-09'
Group by [Date_in_char]
however this give me a conversion error any help on this would be much apperciated. thanks heaps in advance
May 17, 2011 at 10:01 am
You might want to try removing the single quotes from the following:
WHERE [Date_in_char] BETWEEN 'DATEADD(dd,-(DAY(DATEADD(mm,1,[Date_in_char]))-1),DATEADD(mm,0,[Date_in_char]))'
May 17, 2011 at 5:18 pm
Thanks
that did work, but the final result was not what i wanted.
Does anyone how to do a MTD total, where the date is quired. so when the date is is set to 2011-04-10
then it should add all the values from the first of the 4th 2011 to the 10th of the 4th.
May 17, 2011 at 9:04 pm
what is the data type for the column [Date_in_char]? If it's a DATE, then that should work. If it's DATETIME, or SMALLDATE, then try this:
SELECT
Sum([DailyValue]) as MTDValue, [Date_in_char]
from
[TEST].[dbo].[test]
Where [Date_in_char] >= DATEADD(dd,-(DAY(DATEADD(mm,1,[Date_in_char]))-1),DATEADD(mm,0,[Date_in_char]))
AND [Date_in_char] < DATEADD(DD,1, '2011-02-09')
Group by [Date_in_char]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply