How to encorporate Date and Time comparison to pull a field

I'm trying to pull this data in my first inner join trying to pull the rmsbalance based on the latest date/time combination but not having any luck with my syntax because sql is throwing errors due to my syntax below and I can't figure how how to do the check on the date and time combo:

SELECT rf.RMSTRANCDE,

rm.rmsacctnum,

SUM(rf.rmstranamt) AS [Sum Tran Amt],

rf10.rmsbalance

FROM RMASTER rm

INNER JOIN <-- Problem starts in this inner join

(

SELECT RMSFILENUM,

rmsbalance

FROM RFINANL a

where rmstrandte + rmstrantim = (select max(rmstrandte) + max(rmstrantm) from RFINANL)

) AS rf10 ON rf10.RMSFILENUM = rm.RMSFILENUM

INNER JOIN

(

SELECT RMSFILENUM,

RMSTRANCDE,

SUM(rmstranamt) AS rmstranamt

FROM RFINANL

GROUP BY RMSFILENUM, RMSTRANCDE

) AS rf ON rf.RMSFILENUM = rm.RMSFILENUM

GROUP BY rm.rmsacctnum, rf.RMSTRANCDE, rf10.rmsbalance

HAVING rf10.rmsbalance <> SUM(rf.rmstranamt)

AND rf10.rmsbalance <> 0.00

What is the error message? Syntax of the SELECT statement seems fine.|||

thanks, but I actually figured it out on my own

sql

How to do this without using a cursor?

I am in the last stages of designing a forecasting "engine" for my company,
and I'm stuck on something that seems simple in comparison to everything
else I've done so far.

I have product ABC, and it's total sales forecast is 15 units.
I split the forecast into 2 different locations, based on an established percentage. In this case, I'll say 67% in location 'OH', and 33% in location 'AL' That's 10 units 'OH' and 5 units 'AL'. Then I get my actual orders by location, and compare them to the forecast.

If the orders exceed the forecast, I'll use orders, otherwise, I use forecast. Whenever I do that, I need to reduce the forecast for the other location, in order to keep the total forecast of 15 whole. (It is not possible for total orders to exceed total forecast, I've already dealt with that.)

CREATE PROCEDURE tempSelect
AS

CREATE TABLE #tmpTest (
parent char(2),
proj_ship real,
open_ord real,
)

insert into #tmpTest (parent, proj_ship, open_ord)
select 'OH', 10, 4

insert into #tmpTest (parent, proj_ship, open_ord)
select 'AL', 5, 7

SELECT PARENT, 'UNITS' = ?
FROM #TMPTEST

DROP TABLE #TMPTEST
GO

I need help with '?' in the query.

The result set I am looking for is:
OH 8
AL 7You have no way to differentiate forecasts from actuals in your schema. Seems like that could be a problem...
You will need to store the total forecast at a higher level in the schema. For instance, how are OH and AL related, that they share a forecast? Store 15 at the level, and then you can easily balance out the actuals.
So, it seems to me you have more of a design problem than an SQL problem.

And another thing: If OH exceeds its forecast, then AL automatically falls below its forecast? That doesn't make any sense. What kind of business is this?|||Before I get to this level, I generate a forecast by customer "Bill-To", based on sales input, history, and current orders. One Bill-To can have multiple "Ship-To's", so it can be served by 2 or 3 locations. Based on this analysis, the total forecast will never be exceeded by open orders for one location. I break the location specific forecast out by taking the total forecast, and multiplying it by a factor stored in another table:

BOD_ID|MONTH|PCT
1234|1|.66
1235|1|.34
1234|2|.5
1235|2|.5
...

Where BOD_ID represents a combination of Site and Product.

I agree that it seems odd that I would want to reduce the forecast for one location, because another location exceeds forecast. However Lawn Fertilizer is an unpredictable business, and we've decided that we want to keep the total forecast intact, because of the analysis that goes on prior to getting to that level.

My guess would be that this will require multiple temp tables to do it without a cursor. I personally don't have anything against using a cursor, but it seems like all the pros don't like them, so I'm trying to learn the "right" way, just in case I ever want to go "Pro".|||Ok, I found either a butt-ugly or pure-genuis way of solving this:

CREATE PROCEDURE tempSelect
AS

CREATE TABLE #tmpTest (
parent char(2),
proj_ship real,
open_ord real,
)

insert into #tmpTest (parent, proj_ship, open_ord)
select 'OH', 10, 4

insert into #tmpTest (parent, proj_ship, open_ord)
select 'AL', 5, 7

insert into #tmpTest (parent, proj_ship, open_ord)
select 'PA', 3, 1

SELECT PARENT,
'UNITS' = CASE WHEN OPEN_ORD>PROJ_SHIP
THEN OPEN_ORD ELSE PROJ_SHIP-
(((select sum(case when OPEN_ORD>PROJ_SHIP
THEN OPEN_ORD ELSE PROJ_SHIP END) as total from #tmpTest)-
(select sum(proj_ship) from #tmpTest))*(proj_ship/
(select sum(proj_ship) from #tmpTest where open_ord<proj_ship)))
END
FROM #TMPTEST

DROP TABLE #TMPTEST
GO

This works for 2 or more sites, and if some or none of the sites' orders exceed forecast. It already looks very complex, and I still need to add grouping by product for the "real" query. Is there still a better way?

How to do comparison between current_year_amount vs previous_year_amount

Hi all,

I try to compare this year amount, A (i.e March 2003) with last year amount, B (i.e March 2002) to get a result, C. I need to calculate and store figure for each following month, then later compute variance from the C value i get.

May I know how to write SQL code that allow me to have both last year data n this year data in the furure? :eek: I only have current year figure now.

A thousand thanks!

SQL Junior,
Suh Fui ;)Here is a quick demo that you can apply to your issue.

select sum(case when convert(varchar(6),orderdate,112)='199607' then freight else 0 end) as 'Jul 96',
sum(case when convert(varchar(6),orderdate,112)='199707' then freight else 0 end) as 'Jul 97'
from northwind..orders