Crystal report - complicated sql query

Question

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.

Nitin Sontakke · Answer

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!

Tangara G · Answer

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.

Nitin Sontakke · Answer

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.

Tangara G · Answer

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.

Bikesh Srivastava · Answer

You can use dataset or stored procedure in case complicated query..

Nitin Sontakke · Answer

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.

Nitin Sontakke · Answer

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.

Tangara G · Answer

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)

Nitin Sontakke · Answer

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.