April 19, 2002 at 4:24 am
I wanted to take out a list showing the items which constitute the 70% of the total sales made for the month.
I have columns like, itemcode, name, qty, value. and I have around 45,000 records. There are around 400 different type of items.
How can I retrieve the items which helps us to achieve 70% sales of the total sale made in the month. The Database server is SQL 7.0.
Could any one help me to take out the report from this list? Thanks in advance for suggesting a solution.
Prabin Misra
April 19, 2002 at 5:54 am
Ok I am confused here. Are you saying you want to get 70% of the records and total them or you want what 70% of the totals are? Or is it something else I am missing? (Example would be helpfull)
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 19, 2002 at 6:52 am
Thanks. It is not 70% of the records. if the total value of sale is 100, I require the no of items in descenging order of sale(total value) whose cummulative should come to 70.
item sale cummulative
e.g. item1 30.00 30.00
item2 25.00 55.00
ietm3 20.00 75.00
There may be 20 records in the table whose total sale is 100. But the above mentioned 3 items constitute 75% of the sales. So I require only listing of 3 records not 14 records which is 70% of 20.
Prabin Misra
Prabin Misra
April 19, 2002 at 8:38 am
Without seeing the DDL of the tables and a sample of data to know for sure. Something like this would do the job. If you want to get into more detail let me know and I can help.
SELECT
*
FROM
(
SELECT
item,
sale,
SumOfHoursCharged + (SELECT ISNULL(SUM(sale),0) FROM tblSales iT WHERE iT.sale < oT.sale AND SalesDate between '1/1/2002' AND '2/1/2002') as cummulative
FROM
tblSales oT
WHERE
SalesDate between '1/1/2002' AND '2/1/2002'
) As BaseWork
WHERE
cummulative < (SELECT SUM(sale) FROM tblSales iT WHERE SalesDate between '1/1/2002' AND '2/1/2002' ) * .70
UNION ALL
SELECT TOP 1
*
FROM
(
SELECT
item,
sale,
SumOfHoursCharged + (SELECT ISNULL(SUM(sale),0) FROM tblSales iT WHERE iT.sale < oT.sale AND SalesDate between '1/1/2002' AND '2/1/2002') as cummulative
FROM
tblSales oT
WHERE
SalesDate between '1/1/2002' AND '2/1/2002'
) As BaseWork
WHERE
cummulative < (SELECT SUM(sale) FROM tblSales iT WHERE SalesDate between '1/1/2002' AND '2/1/2002' ) * .70
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 20, 2002 at 2:00 am
Thanks for the solution. but I could not understand why u have "SumOfHoursCharged" in that query. I might be wrong in explaining the problem. I will give u a sample list and the answer of the list. Though I got the result, but not through professional manner.
Assume that this is the total sale for the month. we don't require any data range that can be incorporated afterwards.
The sample list is
This is the sample table
Sl.No BRANCH ITEMCODE QTY AMT
1 BAN GBTPN040 10 0.06
2 BAN KSSW0008 50 0.09
3 BAN KSSW0004 10 0.01
4 BAN KSSW0012 10 0.02
5 BAN KSSW0004 40 0.06
6 BAN KSSW0008 40 0.07
7 BAN KSSW0004 16 0.02
8 BAN KSSW0008 50 0.09
9 BAN KSSW0004 59 0.08
10 BAN KSSW0008 100 0.18
11 BAN KSSW0012 46 0.11
12 BAN KSSW0008 40 0.07
13 BAN KSSW0008 34 0.06
14 BAN KSSW0012 147 0.34
15 BAN KSSW0012 53 0.12
.
.
90 BAN GBTPN032 75 0.36
91 BAN GBTPN032 55 0.26
92 LUC SBFPI063 102 0.25
93 LUC SBDPI040 80 0.08
94 LUC SBSP0010 175 0.10
95 LUC SBSP0016 800 0.44
96 LUC SBSP0006 114 0.06
This is the transaction of each itemcode. I have taken 96 transactions. and the total sale (sum of amt column) comes to 13.96 lacs.
I require the itemcodes which constitute approx 70% of the total sale 13.96 lacs
The answer of the sample table should be
Sl.No ITEMCODE AMT
1 SBSP0032 1.85
2 KSPSAW20 1.61
3 SBSP0020 1.39
4 SBSP0010 1.14
5 SBSP0016 0.95
6 SBSP0025 0.87
7 SBSP0006 0.81
8 GBTPN032 0.74
9 KSSW0012 0.71
This upto 9th item comes to be 72.13% of the total sale. This may be the simple one for u but I could not get get thru it. I think this example is clear. I require 70%, This could be 60% or 80% also depending on requirement.
Prabin Misra
Prabin Misra
April 22, 2002 at 5:16 am
Sorry about the sumofhourscharged that was from my test item. Anyway considering what yout gave try this.
SELECT
*
FROM
(
SELECT
[No],
ITEMCODE,
AMT,
AMT + (SELECT ISNULL(SUM(AMT),0) FROM Sl iT WHERE iT.AMT > oT.AMT) as cummulative
FROM
Sl oT
) As BaseWork
WHERE
cummulative < (SELECT SUM(AMT) FROM Sl iT) * .70
UNION ALL
SELECT TOP 1
*
FROM
(
SELECT
[No],
ITEMCODE,
AMT,
AMT + (SELECT ISNULL(SUM(AMT),0) FROM Sl iT WHERE iT.AMT > oT.AMT) as cummulative
FROM
Sl oT
) As BaseWork
WHERE
cummulative < (SELECT SUM(AMT) FROM Sl iT) * .70
ORDER BY
cummulative
To change the percentage just change where you see * .70 to * .80 or whatever percentage you are after. Note this looks at the whole database and outputs the largest values that make up roughly 70% or more to get the answer.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 22, 2002 at 6:06 am
Excellent solution Antares..however the i think the calculation for cumulative should be slightly different...here's my take on your query
SELECT
*
FROM
(
SELECT
SL_No,
ITEMCODE,
AMT,
AMT + (SELECT ISNULL(SUM(AMT),0) FROM SL iT WHERE iT.SL_NO < oT.SL_NO) as cummulative
FROM
SL oT
) As BaseWork
WHERE
cummulative < (SELECT SUM(AMT) FROM SL iT) * .70
April 22, 2002 at 10:50 am
Hey GRN that is where I was originally headed if you look at my previous example, but based on his example expected output he is wanting the data based on the largest value down to 70% (rough) as opposed to least value. That is the effect changing the > (greatest to least) to < (least to greatest) will have.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 23, 2002 at 12:54 am
aahhh...missed that ..however Antares i've not just changed the > operator but this subquery SELECT ISNULL(SUM(AMT),0) FROM SL iT WHERE iT.SL_NO < oT.SL_NO
notice i'm using the SL_NO rather than the AMT column ...using the amount column the query would return all records other than the record with the lowest AMT?
i think we are both missing something .. i think he wants the records grouped on ITEMCODE ? is that right Prabin .. in which case the SL_NO column in your sample output is misleading!
April 23, 2002 at 4:34 am
Missed that one too. I see your difference now and agree that I may be missing something else as well in regards to itemcode since you point that out.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 29, 2002 at 3:55 am
Sorry for being late in replying to this Solution. Thanks Antares686 and GRN for delving into the matter meticulously.
I think slno won't work out becoz the itemcodes are not in a particular sequence. But I have to order it by itemcodes and secondly the amount field, e.g. if two or more rows are having the same amount then how will it calculate the cummulative. would there be any error If I could see ?.Your help is required.
Prabin Misra
Prabin Misra
April 29, 2002 at 5:18 am
The a combo change of both of our codes should help.
SELECT
*
FROM
(
SELECT
[No],
ITEMCODE,
AMT,
AMT + (SELECT ISNULL(SUM(AMT),0) FROM Sl iT WHERE iT.AMT > oT.AMT AND iT.SL_NO > oT.SL_NO) as cummulative
FROM
Sl oT
) As BaseWork
WHERE
cummulative < (SELECT SUM(AMT) FROM Sl iT) * .70
UNION ALL
SELECT TOP 1
*
FROM
(
SELECT
[No],
ITEMCODE,
AMT,
AMT + (SELECT ISNULL(SUM(AMT),0) FROM Sl iT WHERE iT.AMT > oT.AMT AND iT.SL_NO > oT.SL_NO) as cummulative
FROM
Sl oT
) As BaseWork
WHERE
cummulative < (SELECT SUM(AMT) FROM Sl iT) * .70
ORDER BY
cummulative
You should not get an error unless the code itself is an issue. The only problem I see is if two values itemcode and amount are the same. If that is possible then consider doing a temp table to collect the data with an identity field and use the same basic code here with a few minor tweaks. If you need an exampl let me know. I am still a bit asleep.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply