Tricky SQL stuff!

[raydenl]

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?

 

[amrita418]

Try this and let us know your findings:

SELECT     a.TDate, 
           WeekStart = a.TDate - Cast(a.TDate - '11-Aug-2004' AS int) % 7,
           Case WHEN a.TDate <> '11-Aug-2004' and a.Total > 24000 THEN a.Total/4 
                WHEN a.TDate <> '11-Aug-2004' and a.Total > 0 THEN a.Total 
                WHEN a.TDate <> '11-Aug-2004' and a.total <= 0 
                     THEN (select outer.total 
                           from   a outer
                           where  outer.TDate = (select max(inner.TDate)
                                                 from   a inner
                                                 where inner.TDate < a.TDate))
                WHEN a.TDate = '11-Aug-2004'  AND a.total > 24000 THEN a.Total / 4
                ELSE a.Total END Adjusted
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) a

PS: Code not Tested.

Regards,
AA

 

[raydenl]

Keeps saying invalid object name 'a', and it's refering to these two sections:

from a outert
from a innert

 

[amrita418]

I am sorry, I don't know what I was thinking.

Two questions for you:
1) In the select stmt with group by TDate you have a filter on scores > 0 so why you should not have a case where TDate <> '11-Aug-2004' and total < 0 so why is that code needed?

2) Are you ok with loading the data into a temp table?
You can drop the table later. If you load the select with group by into a temp table then the previous query should be good with minor changes.

Else
Try this code:

SELECT     a.TDate, 
           WeekStart = a.TDate - Cast(a.TDate - '11-Aug-2004' AS int) % 7,
           Case WHEN a.TDate <> '11-Aug-2004' and a.Total > 24000 THEN a.Total/4 
                WHEN a.TDate <> '11-Aug-2004' and a.Total > 0 THEN a.Total 
                WHEN a.TDate <> '11-Aug-2004' and a.total <= 0 THEN 
                                 (select   sum(scores) 
                                  from     Test 
                                  where    TDate = (select   max(TDate) 
                                                    from     Test 
                                                    where    TDate < a.TDate)
                                  group by TDate)
                WHEN a.TDate = '11-Aug-2004'  AND a.total > 24000 THEN a.Total / 4
                ELSE a.Total END Adjusted
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) a

Regards,
AA

 

[raydenl]

yeah this would work most of the time, but, what if the previous value was greater than 24000 or <= 0, etc.

so it really needs to get the actual value from the previous selected row, needs some sort of recursive solution?

 

[amrita418]

Add that condition to the having clause:

select   max(TDate) 
from     Test 
where    TDate < a.TDate
group by TDate
having   sum(sales) > 0 and sum(sales) < 24000

You can get rid of the outer group by clause. (group by TDate)

Let us know if that does the trick.

Regards,
AA