call for SQL statements sepcialists! :-)
-
(sorry for the very long post ...)
I have three tables (see below, the names are in Dutch, but you will get the idea) ...
One (tblTypeInfo) contains the different types of info
One (tblInfoAanvraag) contains all the requests for a type of info, every servered request makes the stock decrease
one (tblPublicatie) contains all the new prints of the types of info, every new print makes the stock increase.
"aantal" is Dutch for "number of items" (#)
"kostprijs" is Dutch for "cost"
"qry" prefix: these are views on the different tables, containing every fields, but for user management, are not updateable.
If you look at the sql statement, you see that I want a report, containing for each item from tblTypeInfo (eacht different kind of publication people can request) containing:
the name of that publication (and its dimensions if appliceable), the total number of new brochures, the total number of requested items, the remaining stock (the difference between new prints and requested items), the number of ordered prints, the number of request, the total cost of all the prints.
Now the problem:
for eacht "Type of Info" all the "counts" and "sums" are mutiplied by the number of records in the other table. e.g. Instead of only counting the total number of new brochures (sum(aantal) from tblPublicatie) the query counts the sum of these records and mutiplies it by the number of records in tblInfoAanvraag and vica versa...
How can this be solved?
-
Hi visbek !
It took me some time to get the picture 🙂 but after that it was obvious. I think the problem is in the way the type info view is JOINDed to other two views. The right approach would be to make separate summaries from qryPublicate and qryInfoAanVraag and JOIN them to qryInfoType to get the description. I will only scratch the SQL:
SELECT TOP 100 PERCENT
<columns from qryTypeInfo>, <columns from Tmp_Publicate>, <columns from Tmp_InfoAanVraag>
FROM qryTypeInfo
LEFT OUTER JOIN (SELECT type_info_id, SUM(aantal) AS Publicate_aantal, SUM(kostprijs) AS Publicate_kostprijs
FROM qryPublicate GROUP BY type_info_id) AS Tmp_Publicate
ON (Tmp_Publicate.type_info_id = qryTypeInfo.id)
LEFT OUTER JOIN (SELECT type_info_id, SUM(aantal) AS InfoAanVraag_aantal
FROM qryInfoAanVraag GROUP BY type_info_id) AS Tmp_InfoAanVraag
ON (Tmp_InfoAanVraag.type_info_id = qryTypeInfo.id)
Additional columns for COUNT and the difference should be added, too.
I hope this will help,
Regards,
Goce Smilevski.
-
thx!!
Learned something here to dynamically rename fields in a view
complete SQL statement:
SELECT TOP 100 PERCENT dbo.qryTypeInfo.id, dbo.qryTypeInfo.naam_info + ISNULL(' (' + dbo.qryTypeInfo.formaat + ')', '') AS vollnaam, ISNULL(SUM(qryPublicatie.publicatie_aantal), 0) AS tot_in, ISNULL(SUM(qryInfoAanVraag.infoaanvraag_aantal), 0) AS tot_uit, SUM(ISNULL(qryPublicatie.publicatie_aantal, 0) - ISNULL(qryInfoAanVraag.infoaanvraag_aantal, 0)) AS tot_stock, ISNULL(qryPublicatie.publicatie_bewegingen, 0) AS aantal_in, ISNULL(qryInfoAanVraag.infoaanvraag_bewegingen, 0) AS aantal_uit, ISNULL(CONVERT(nvarchar, SUM(qryPublicatie.Publicatie_kostprijs)) + ' EUR', 0) AS kostprijs, dbo.qryTypeInfo.type_oorsprong_id
FROM dbo.qryTypeInfo LEFT OUTER JOIN
(SELECT type_info_id, SUM(aantal) AS publicatie_aantal, SUM(kostprijs) AS Publicatie_kostprijs, COUNT(*) AS publicatie_bewegingen
FROM qryPublicatie
GROUP BY type_info_id) qryPublicatie ON qryPublicatie.type_info_id = dbo.qryTypeInfo.id LEFT OUTER JOIN
(SELECT type_info_id, SUM(aantal) AS infoaanvraag_aantal, COUNT(*) AS infoaanvraag_bewegingen
FROM qryInfoAanVraag
GROUP BY type_info_id) qryInfoAanVraag ON qryInfoAanVraag.type_info_id = dbo.qryTypeInfo.id
GROUP BY dbo.qryTypeInfo.id, dbo.qryTypeInfo.naam_info, dbo.qryTypeInfo.formaat, dbo.qryTypeInfo.type_oorsprong_id, qryPublicatie.publicatie_bewegingen, qryInfoAanVraag.infoaanvraag_bewegingen
ORDER BY dbo.qryTypeInfo.naam_info
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply