Unanswered: Complex Query dealing with data in a range
The program deals with issues of cheques, to customers, they then redeem products against these cheques.
I have two tables containing the following fields
.Project_Num (Holds the project number)
.Start_Num (Holds the starting cheque number issued)
.StopNum (Holds the ending cheque number issued)
.ProjectNum (Holds the project number)
.ChqNum (Holds the cheque number redeemed)
1.) To return the total value of cheques issued against each Project
2.) Which cheques have been redeemed against the cheques issued
3.) Show the total value by project unredeemed against each Project
Chq issued 1000 to 1005
Chq issued 1000 to 1003
Chq num 1000
Chq num 1001
Chq num 1002
The problem is there is no direct link back to the cheques issued as they are issued in a range of cheques. The range of cheques can also relate to a number of projects.
Note: The set up of the tables cannot be changed. The program I am working on is linking to this database.
I am having trouble with expression, gives me a message = “The expression you have entered contains invalid syntax” it seems to be on the where clause.
I have also created an expression that identifies the number of cheques issued. eg StartNum = 3001 StopNum 3025. I now have an expression that tells me that 25 where issued. Not sure if this helps but, that where im at, at the moment.
The problem is that you want to display rows for checks that have been issued but not redeemed, but these checks do not exist as entities in your table design. There is a nifty way around this problem.
I've found on many occasions that it is handy to have a table in the database that consists of sequential number valuse starting at zero:
CREATE TABLE [dbo].[sequential_numbers] ([value] [int] NOT NULL ) ON [PRIMARY]
Populate the table with enough value to cover the maximum number of checks that will be issued to any one project. You can use this table in a subquery to simulate the missing data elements (issued checks), and your report query will look like this:
1 as Issued,
sum(case when tblRedeemed.ChqNum is null then 0 else 1 end) as Redeemed,
sum(case when tblRedeemed.ChqNum is null then 1 else 0 end) as Outstanding
(select Project_Num, Start_Num+value as ChqNum
inner join sequential_numbers on sequential_numbers.value <= (StopNum - Start_Num)) Issued
left outer join tblRedeemed on issued.project_num = tblRedeemed.projectnum
and issued.ChqNum = tblRedeemed.ChqNum
group by Issued.Project_Num, Issued.ChqNum
order by Issued.Project_Num, Issued.ChqNum