Tricky stuff - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Tricky stuff

Author

Topic

raydenl
Starting Member

16 Posts

Posted - 2005-08-23 : 23:50:14

I have an sql statement like so:

SELECT a.TDate, WeekStart=a.TDate-Cast(a.TDate - '11-Aug-2004' AS int) % 7,
Adjusted=Case WHEN a.TDate <> '11-Aug-2004' THEN
Case WHEN a.Total > 24000 THEN
a.Total/4 ELSE
Case WHEN a.Total > 0 THEN
a.Total ELSE
0 -- here is where the problem needs to be solved!!!
END END
ELSE Case WHEN a.Total > 24000 THEN
a.Total/4 ELSE
a.Total END END
FROM(SELECT TDate, Sum(Scores) AS Total
FROM Test
WHERE (TDate >= '11-Aug-2004' AND TDate <= '24-Aug-2004')
AND Code In ('ABC')
AND Scores>0
GROUP BY TDate
) AS a

It returns a result set like so:

2004-08-11 00:00:00 2004-08-11 00:00:00 13667.180
2004-08-12 00:00:00 2004-08-11 00:00:00 0
2004-08-13 00:00:00 2004-08-11 00:00:00 12620.891
2004-08-14 00:00:00 2004-08-11 00:00:00 7764.918
2004-08-15 00:00:00 2004-08-11 00:00:00 7551.753
2004-08-16 00:00:00 2004-08-11 00:00:00 13562.814
2004-08-17 00:00:00 2004-08-11 00:00:00 15161.205
2004-08-18 00:00:00 2004-08-18 00:00:00 14780.539
2004-08-19 00:00:00 2004-08-18 00:00:00 14295.927
2004-08-20 00:00:00 2004-08-18 00:00:00 12648.412
2004-08-21 00:00:00 2004-08-18 00:00:00 10163.869
2004-08-22 00:00:00 2004-08-18 00:00:00 7691.478
2004-08-23 00:00:00 2004-08-18 00:00:00 13482.235
2004-08-24 00:00:00 2004-08-18 00:00:00 15543.685

Here is the problem, at the moment one of the case statements says if Total is not greater than zero then return zero, what it is supposed to say is, if the Total is not greater than zero then return the value for the previous day.

In other words, looking at the result set above, if 2004-08-12 Total is not greater than zero, then the total should equal that of 2004-08-11, giving a result set like so:

2004-08-11 00:00:00 2004-08-11 00:00:00 13667.180
2004-08-12 00:00:00 2004-08-11 00:00:00 13667.180
2004-08-13 00:00:00 2004-08-11 00:00:00 12620.891
2004-08-14 00:00:00 2004-08-11 00:00:00 7764.918
.
.
.

Can anyone help?

digital3
Starting Member

7 Posts

Posted - 2005-08-24 : 00:39:48

Does the solution need to be in one SQL statement?

I had a thought if you INSERT the output to a @Table variable (assuming you have SQL Server 2000), you might be able to issue a second statement on that table to resolve the Total = 0 rows.

Though of course, this depends on the size of the data set, among other things that might make the use of a table variable and a second iteration unacceptable.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-08-24 : 00:44:44

do you have an identity key on this table?

maybe if you posted your table DDL and some test data we could help you out a little quicker.

-ec

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources