Problem in Calculating Total

Hi Everybody,

I need to generate report for all the days in a current month. I have to display the total in the folg. way.

Day1 Day2 etc.
Group Name sum of amount sum of amount
from day1 to end from day2 to end
of month of month

upto end of month.

I had a problem in setting the columns. And is there any way to calculate the group wise sum with out creating groups.

If you have any idea, pls. help me

Thanks,
harmonycitraHi,
I dont think it is possible to have groupwise total without creating group. You can group,find sum and suppress group header.

Madhivanan|||Thanks,

But I'd done it by Cross tab. The only difference is,

instead of starting from 1st to 31st, I reversed the order from end of month to beg. of month. So I'm calculating running total easily.

Any way, thank you!

Problem converting CHAR to DATETIME

hello all,

I am having a problem calculating the difference, in days, between two dates, STARTDATE and ENDDATE. The data is stored in the database as char(8), formatted YYYYMMDD. "Null" values are stored as '00000000'. When I try to use DATEDIFF an exception is thrown: "the conversion of a char data type to a datetime data type resulted in an out-of-range datetime value"

How can I fix this, and a get a result even if STARTDATE or ENDDATE is '00000000'? Changing the format of the stored data is not an option.

Thanks for any assistance.

Mike



CREATE TABLE dbo.DATETIME1 (
ID1 int,
STARTDATE char(8),
ENDDATE char(8)
)
INSERT into DATETIME1 (ID1, STARTDATE, ENDDATE)
VALUES (
1, '20070105', '20070108'
)
INSERT into DATETIME1 (ID1, STARTDATE, ENDDATE)
VALUES (
2, '20070105', '00000000'
)
Select * from DATETIME1
Select DATEDIFF(d, STARTDATE, Convert(datetime,ENDDATE) ) as Difference
from DATETIME1
WHERE ID1 = 1How about a case statement to check for the '00000000'? Or an isdate check?|||Ahhhh, good idea...can you help with the syntax of the CASE statement in SQA? I've done them in VB.NET, but not directly in SQL.

Thanks|||add this to your test code replacing your datediff select.


select STARTDATE, ENDDATE,
CASE
WHEN isdate(ENDDATE) = 0
THEN 0
ELSE
DATEDIFF(d, STARTDATE, Convert(datetime,ENDDATE) )
END
from DATETIME1 as DaysDifference|||THANKS! I was doing something similar, but couldn't quite get it to work! :D|||You could also try the following:
ISNULL(DATEDIFF(d, CONVERT(DATETIME, STARTDATE, 112), CONVERT(DATETIME, NULLIF(ENDDATE, '00000000'), 112), 0)
Btw: explicit conversions are always better than implicit ones:)