Crystal report - complicated sql query

Tangara G

I have the above table namely departmentRequisition and departmentRequisitionDetail respectively.

From the departmentRequisitionDetail, there is a column - departmentRequisitionId - 112 and many rows of acutalQuanity that I need to add up as a total quantity to display as a pie chart.

Now, in order to know the department that ties to this total qauntity, I need to refer to another table - departmentRequisition which shows the deparmentId - 2 which ties to this departmentRequisitionId.

Can someone advise how should I write the query so that I can generate the pie chart ?

The above is just for one departmentId and I need to have different departmentId showing the acutalquanity which is the quantity they ordered.

Or is there a better way to generate my crystal report based on the scenario above ? Like create another table to make things easier.

9 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke10y ago
Excellent! Good to know that.
I often expect that the developer struggles a bit to arrive at the desired solution. However, may on such forums expect that fully functional solution should be provided, which I believe is wrong.
It is such struggle that would make you a better and experienced developer tomorrow, otherwise, what is the fun in just copy and paste.
Anyway, I am glad I could help and avoid one more unnecessary table.
Please accept any of the appropriate replies as answer. Thank you!
+1
Tangara G10y ago
Hi Nitin,
Tks for your kindness to help but I finally got it.
Here's the sql :
select sum(actualQuantity) as TotalOrdered, dr.departmentRequisitionId, drd.departmentId
from departmentRequisitiondetail dr inner join departmentRequisition drd on dr.departmentRequisitionId = drd.departmentRequisitionId
group by dr.departmentRequisitionId, drd.departmentId order BY departmentRequisitionId DESC
Thanks for your help cos your sql did help me in someway. Thank you.
0
Nitin Sontakke10y ago
You will need to share bit more information than that.
Give me some sample rows and an expected output. That would be really helpful to write a query. Please refer to my earlier post requesting it.
Please make sure you give sample close to reality.
0
Tangara G10y ago
Hi Nitin,
Just to add, the PK fordepartmentrequisition shd be departmentrequisitionId.
Anyway, I tried out your sql query using view but it doesn't add up the total actualQuantity from a department Requisition from the different departmentId.
0
Bikesh Srivastava10y ago
You can use dataset or stored procedure in case complicated query..
0
Nitin Sontakke10y ago
Just check if following query satisfies your requirement:
select dr.departmentId, sum(actualQuantity)
from departmentRequisitionDetail drd
left outer join departmentRequisition dr on dr.departmentRequisition = drd.departmentRequisitionId
group by dr.departmentId
If not, please post sample of expected output.
0
Nitin Sontakke10y ago
Please also give an idea of expected output. Please be descriptive, if required. Just to give you an EXAMPLE:
Column a - this should be coming from this table
Column b - this is the total of column x from table y, etc.
0
Tangara G10y ago
Hello Nitin,
Here's the infor :
the columns for departmentrequisition are :

departmentRequisition(PK, int, not null)
departmentRequestCode(nvarchar, not null)
disbursementCode(nvarchar, not null)
departmentId(int, not null)
status(nvarchar, not null)
createdDate(datetime, not null)

the columns for departmentrequisitiondetail are :

departmentRequisitionDetailId(PK, int, not null)
departmentRequisitionId(FK, int, not null)
itemId(FK, int, not null)
neededQuantity(int, not null)
actualQuantity(int, not null)
0
Nitin Sontakke10y ago
I would strongly discourage you from creating another table. Keep it right at the bottom of the list of options.
Somehow, the images you posted are not quite in readable format.
From the description of the problem you have given, I am not been able to imagine number of tables and their association with each other.
If possible, please give following information, preferably in text format:
1/ Table structure (with relevant columns only) with table name and pk and fk.
2/ The expected output.
From whatever I have understood, looks like a simple aggregate query to me.
0