Part 3
vendor: Australia Bike Retailer
order # date employee total
9 14/12/2011 259 Buyer 694.17
88 09/02/2012 252 Buyer 656.59
167 30/05/2012 255 Buyer 710.96
246 21/09/2012 253 Buyer 564.20
325 25/04/2013 251 Buyer 1497.51
404 25/06/2013 261 Purchasing Assistant 278.84
434 05/08/2013 253 Buyer 285.36
513 11/08/2013 251 Buyer 402.73
596 18/08/2013 261 Purchasing Assistant 501.20
683 25/08/2013 257 Buyer 358.63
766 01/09/2013 254 Buyer 234.96
849 08/09/2013 258 Buyer 694.17
932 18/09/2013 260 Purchasing Assistant 272.76
1031 13/10/2013 259 Buyer 501.20
1095 06/11/2013 257 Buyer 593.59
1174 13/11/2013 254 Buyer 278.84
1253 25/11/2013 258 Buyer 415.33
1332 04/12/2013 260 Purchasing Assistant 272.76
1411 12/12/2013 259 Buyer 501.20
1490 20/12/2013 256 Buyer 358.63
1569 30/12/2013 255 Buyer 234.96
1648 06/01/2014 253 Buyer 694.17
1727 16/01/2014 251 Buyer 773.96
1806 24/01/2014 261 Purchasing Assistant 482.52
1885 05/02/2014 257 Buyer 111.07
1964 12/02/2014 254 Buyer 278.84
2043 20/02/2014 258 Buyer 415.33
2122 27/02/2014 250 Purchasing Manager 272.76
2201 05/03/2014 259 Buyer 501.20
2280 12/03/2014 256 Buyer 358.63
2359 20/03/2014 255 Buyer 234.96
2438 28/03/2014 253 Buyer 694.17
2517 07/04/2014 251 Buyer 773.96
2596 14/04/2014 261 Purchasing Assistant 482.52
2675 21/04/2014 257 Buyer 111.07
2754 28/04/2014 254 Buyer 278.84
2833 05/05/2014 250 Purchasing Manager 415.33
2912 13/05/2014 260 Purchasing Assistant 272.76
2991 20/05/2014 259 Buyer 501.20
3070 26/05/2014 256 Buyer 358.63
3149 02/06/2014 255 Buyer 234.96
3228 07/06/2014 253 Buyer 694.17
3307 13/06/2014 251 Buyer 773.96
3386 20/06/2014 261 Purchasing Assistant 482.52
3465 26/06/2014 252 Buyer 111.07
3544 02/07/2014 254 Buyer 278.84
3623 09/07/2014 258 Buyer 415.33
3702 15/07/2014 260 Purchasing Assistant 272.76
3781 21/07/2014 259 Buyer 501.20
3860 26/07/2014 256 Buyer 358.63
3939 31/07/2014 255 Buyer 234.96
sum 22678.91
vendor: Allenson Cycles
order # date employee total
3 16/04/2011 257 Buyer 8847.30
82 09/02/2012 254 Buyer 8847.30
161 30/05/2012 258 Buyer 8847.30
240 05/09/2012 260 Purchasing Assistant 8847.30
319 24/04/2013 259 Buyer 8847.30
398 25/06/2013 256 Buyer 8847.30
428 04/08/2013 260 Purchasing Assistant 8847.30
507 11/08/2013 259 Buyer 8847.30
590 17/08/2013 256 Buyer 8847.30
677 25/08/2013 255 Buyer 8847.30
760 01/09/2013 253 Buyer 8847.30
843 08/09/2013 251 Buyer 8847.30
926 17/09/2013 261 Purchasing Assistant 8847.30
1025 13/10/2013 252 Buyer 8847.30
1089 30/10/2013 252 Buyer 8847.30
1168 13/11/2013 253 Buyer 8847.30
1247 25/11/2013 251 Buyer 8847.30
1326 04/12/2013 261 Purchasing Assistant 8847.30
1405 12/12/2013 257 Buyer 8847.30
1484 19/12/2013 254 Buyer 8847.30
1563 30/12/2013 258 Buyer 8847.30
1642 06/01/2014 260 Purchasing Assistant 8847.30
1721 16/01/2014 259 Buyer 8847.30
1800 23/01/2014 252 Buyer 8847.30
1879 04/02/2014 255 Buyer 8847.30
1958 11/02/2014 253 Buyer 8847.30
2037 19/02/2014 251 Buyer 8847.30
2116 26/02/2014 261 Purchasing Assistant 8847.30
2195 04/03/2014 257 Buyer 8847.30
2274 12/03/2014 254 Buyer 8847.30
2353 20/03/2014 258 Buyer 8847.30
2432 28/03/2014 260 Purchasing Assistant 8847.30
2511 07/04/2014 259 Buyer 8847.30
2590 14/04/2014 256 Buyer 8847.30
2669 21/04/2014 255 Buyer 8847.30
2748 25/04/2014 253 Buyer 8847.30
2827 02/05/2014 251 Buyer 8847.30
2906 13/05/2014 261 Purchasing Assistant 8847.30
2985 20/05/2014 257 Buyer 8847.30
3064 26/05/2014 254 Buyer 8847.30
3143 31/05/2014 258 Buyer 8847.30
3222 06/06/2014 250 Purchasing Manager 8847.30
3301 13/06/2014 259 Buyer 8847.30
3380 20/06/2014 256 Buyer 8847.30
3459 25/06/2014 255 Buyer 8847.30
3538 02/07/2014 253 Buyer 8847.30
3617 09/07/2014 251 Buyer 8847.30
3696 14/07/2014 261 Purchasing Assistant 8847.30
3775 19/07/2014 257 Buyer 8847.30
3854 25/07/2014 254 Buyer 8847.30
3933 30/07/2014 250 Purchasing Manager 8847.30
sum 451212.30
...
...
vendor: Chicago City Saddles
order # date employee total
19 15/12/2011 259 Buyer 79204.13
98 08/03/2012 256 Buyer 73377.15
177 22/06/2012 252 Buyer 101720.85
256 21/09/2012 253 Buyer 50860.43
335 25/04/2013 251 Buyer 152581.28
414 30/07/2013 261 Purchasing Assistant 28343.70
444 05/08/2013 253 Buyer 34118.70
523 12/08/2013 251 Buyer 50860.43
606 18/08/2013 261 Purchasing Assistant 39258.45
693 26/08/2013 252 Buyer 50860.43
776 02/09/2013 254 Buyer 62462.40
859 09/09/2013 258 Buyer 39258.45
942 18/09/2013 260 Purchasing Assistant 28343.70
1041 14/10/2013 259 Buyer 73377.15
1105 07/11/2013 257 Buyer 50860.43
1184 15/11/2013 254 Buyer 28343.70
1263 26/11/2013 258 Buyer 50860.43
1342 05/12/2013 260 Purchasing Assistant 34118.70
1421 13/12/2013 259 Buyer 39258.45
1500 20/12/2013 252 Buyer 50860.43
1579 31/12/2013 255 Buyer 79204.13
1658 08/01/2014 253 Buyer 22516.73
1737 17/01/2014 251 Buyer 101720.85
1816 24/01/2014 261 Purchasing Assistant 50860.43
1895 05/02/2014 257 Buyer 152581.28
1974 12/02/2014 254 Buyer 28343.70
2053 20/02/2014 258 Buyer 50860.43
2132 27/02/2014 260 Purchasing Assistant 34118.70
2211 05/03/2014 259 Buyer 39258.45
2290 13/03/2014 256 Buyer 50860.43
2369 21/03/2014 255 Buyer 79204.13
2448 31/03/2014 253 Buyer 22516.73
2527 08/04/2014 251 Buyer 101720.85
2606 15/04/2014 261 Purchasing Assistant 50860.43
2685 22/04/2014 257 Buyer 152581.28
2764 28/04/2014 254 Buyer 28343.70
2843 05/05/2014 258 Buyer 50860.43
2922 14/05/2014 250 Purchasing Manager 34118.70
3001 21/05/2014 259 Buyer 39258.45
3080 27/05/2014 256 Buyer 50860.43
3159 02/06/2014 255 Buyer 79204.13
3238 09/06/2014 253 Buyer 22516.73
3317 16/06/2014 251 Buyer 101720.85
3396 21/06/2014 261 Purchasing Assistant 50860.43
3475 26/06/2014 257 Buyer 152581.28
3554 03/07/2014 254 Buyer 28343.70
3633 10/07/2014 250 Purchasing Manager 50860.43
3712 15/07/2014 260 Purchasing Assistant 34118.70
3791 21/07/2014 259 Buyer 39258.45
3870 26/07/2014 256 Buyer 50860.43
3949 31/07/2014 255 Buyer 79204.13
sum 3029108.91
vendor: Business Equipment Center
order # date employee total
16 14/12/2011 253 Buyer 150.79
95 27/02/2012 251 Buyer 150.79
174 11/06/2012 261 Purchasing Assistant 150.79
253 21/09/2012 252 Buyer 150.79
332 25/04/2013 254 Buyer 150.79
411 22/07/2013 258 Buyer 150.79
441 05/08/2013 257 Buyer 150.79
520 11/08/2013 250 Purchasing Manager 150.79
603 18/08/2013 258 Buyer 150.79
690 26/08/2013 260 Purchasing Assistant 150.79
773 02/09/2013 259 Buyer 150.79
856 08/09/2013 256 Buyer 150.79
939 18/09/2013 255 Buyer 150.79
1038 13/10/2013 253 Buyer 150.79
1102 07/11/2013 260 Purchasing Assistant 150.79
1181 15/11/2013 259 Buyer 150.79
1260 26/11/2013 256 Buyer 150.79
1339 04/12/2013 255 Buyer 150.79
1418 13/12/2013 253 Buyer 150.79
1497 20/12/2013 251 Buyer 150.79
1576 31/12/2013 261 Purchasing Assistant 150.79
1655 08/01/2014 257 Buyer 150.79
1734 17/01/2014 254 Buyer 150.79
1813 24/01/2014 258 Buyer 150.79
1892 05/02/2014 260 Purchasing Assistant 150.79
1971 12/02/2014 259 Buyer 150.79
2050 20/02/2014 256 Buyer 150.79
2129 27/02/2014 255 Buyer 150.79
2208 05/03/2014 253 Buyer 150.79
2287 13/03/2014 251 Buyer 150.79
2366 21/03/2014 261 Purchasing Assistant 150.79
2445 31/03/2014 257 Buyer 150.79
2524 07/04/2014 254 Buyer 150.79
2603 14/04/2014 258 Buyer 150.79
2682 21/04/2014 260 Purchasing Assistant 150.79
2761 28/04/2014 259 Buyer 150.79
2840 05/05/2014 256 Buyer 150.79
2919 14/05/2014 255 Buyer 150.79
2998 21/05/2014 253 Buyer 150.79
3077 27/05/2014 251 Buyer 150.79
3156 02/06/2014 261 Purchasing Assistant 150.79
3235 09/06/2014 257 Buyer 150.79
3314 16/06/2014 254 Buyer 150.79
3393 21/06/2014 258 Buyer 150.79
3472 26/06/2014 260 Purchasing Assistant 150.79
3551 02/07/2014 252 Buyer 150.79
3630 09/07/2014 256 Buyer 150.79
3709 15/07/2014 255 Buyer 150.79
3788 21/07/2014 253 Buyer 150.79
3867 26/07/2014 251 Buyer 150.79
3946 31/07/2014 261 Purchasing Assistant 150.79
sum 7690.29
final total >>> 63792002.21
---------------------------------------------
SQL query
select * from Purchasing.Vendorselect * from HumanResources.Employee
select * from Purchasing.PurchaseOrderHeader
select * from Purchasing.PurchaseOrderDetail
alter view my_view as
select detail.PurchaseOrderID, convert(varchar, header.OrderDate, 103) as date, e.BusinessEntityID, e.JobTitle, sum(detail.LineTotal) as total from Production.Product as p inner join
Purchasing.PurchaseOrderDetail as detail on p.ProductID=detail.ProductID inner join
Purchasing.PurchaseOrderHeader as header on detail.PurchaseOrderID=header.PurchaseOrderID inner join
HumanResources.Employee as e on header.EmployeeID = e.BusinessEntityID
group by detail.PurchaseOrderID, header.OrderDate, e.BusinessEntityID, e.JobTitle
select * from my_view
select purchaseorderid, count(*) from my_view
group by purchaseorderid
having count(*)>1
alter procedure my_report as
begin
declare @company varchar(40)
declare @vendor_id numeric(8)
declare @order# numeric(10)
declare @date varchar(20)
declare @employee_id numeric(10)
declare @employee_title varchar(20)
declare @total1 numeric(20,2)=0
declare @total2 numeric(20,2)=0
declare @total3 numeric(20,2)=0
declare cursor1 cursor for
select Name, BusinessEntityID from Purchasing.Vendor
open cursor1
fetch next from cursor1 into @company, @vendor_id
while(@@FETCH_STATUS=0)
begin
print 'vendor: ' + convert(varchar, @company)
declare cursor2 cursor for
select detail.PurchaseOrderID, convert(varchar, header.OrderDate, 103), e.BusinessEntityID, e.JobTitle, sum(detail.LineTotal) as total from Production.Product as p inner join
Purchasing.PurchaseOrderDetail as detail on p.ProductID=detail.ProductID inner join
Purchasing.PurchaseOrderHeader as header on detail.PurchaseOrderID=header.PurchaseOrderID inner join
HumanResources.Employee as e on header.EmployeeID = e.BusinessEntityID
where header.VendorID = @vendor_id
group by detail.PurchaseOrderID, header.OrderDate, e.BusinessEntityID, e.JobTitle
order by PurchaseOrderID
open cursor2
fetch next from cursor2 into @order#, @date, @employee_id, @employee_title,@total1
print char(9)+'order #'+char(9)+'date'+char(9)+'employee'+char(9)+'total'
while(@@FETCH_STATUS=0)
begin
print char(9)+convert(varchar, @order#)+char(9)+ @date+char(9)+convert(varchar, @employee_id)+char(9)+ @employee_title+char(9)+ convert(varchar,@total1)
set @total2=@total2+@total1
fetch next from cursor2 into @order#, @date, @employee_id, @employee_title,@total1
end
close cursor2
deallocate cursor2
print char(9) +'sum'+char(9)+convert(varchar,@total2)
set @total3=@total3+@total2
set @total2=0
fetch next from cursor1 into @company, @vendor_id
end
close cursor1
deallocate cursor1
print 'final total >>> '+convert(varchar,@total3)
end
exec my_report
------------------------------
reference:
http://chuanshuoge2.blogspot.com/2018/02/sql-function-procedure-trigger.html
No comments:
Post a Comment