March 16, 2017 at 4:39 pm
hi,
I have a sql table like below.
UnitID SEQ MarkNo Qty ModelNo MLP Discount Price
1578b210-877d-4301-8c05-847a0148a382 00001 1 TG8S060A12MP 11 602 273.31
4cebbc36-6de4-4174-b259-74acc62d83d1 00001 1 S1-1TVM4G1 141 -75 35.25
6dcce707-fc51-4456-b9c4-b26f7a47782e 00001 1 TG8S060A12MP 1159 -68.6 363.93
93e67d42-1270-4f6e-a9db-2ca84a3c030e 00001 3 S1-1TVM4G1 1547 -58 0.42
1edf2929-6c02-4a5f-99f8-8de3ae2accdb 00001 1 YC090C00A4AAA2 5329 -65.9 0.341
Below is the expected result . Duplicated rows has to be consolidated by modelnumber and price column should be of a min value out of the consolidated model numbers.Other columns QTY ,MLP and discount are to be summed up by model number.
UnitID SEQ MarkNo Qty ModelNo MLP Discount Price
1578b210-877d-4301-8c05-847a0148a382 1 2 TG8S060A12MP 1160 533.4 273.31
4cebbc36-6de4-4174-b259-74acc62d83d1 2 4 S1-1TVM4G1 1688 -133 0.42
1edf2929-6c02-4a5f-99f8-8de3ae2accdb 3 1 YC090C00A4AAA2 5329 -65.9 0.341
For the above task, i have used the below procedure but its not working fully.
I have listed out the duplicate models first and stored it in temp table DuplicateModel. Am not sure how i can get the minimum price like the above.Any help is appreciated.
create procedure GetLineByModel
as
BEGIN
create table #tempstores (SEQ nvarchar(50), ModelNo nvarchar(96),MLP float,QTY int, Discount float,BuyMultiplier float,Price float,UnitPrice float,[Description] nvarchar(4000),family nvarchar(50),Brand nvarchar(3))
create table #DuplicateModel (ModelNo nvarchar(96),duplicatecount float)
SET NOCOUNT ON;
with ct(SEQ, ModelNo,MLP, QTY,Discount,BuyMultiplier,Price,UnitPrice, [Description],Family,Brand ) as(
SELECT ROW_NUMBER() OVER (ORDER BY ModelNo) AS SEQ, ModelNo,MLP, SUM(QTY) as QTY,Discount,BuyMultiplier,Sum(Price) as Price, sum(UnitPrice) as UnitPrice, [Description],Family,Brand
FROM dbo.ustlineitems WHERE JID like '47ff6ee3-a49f-4709-8cd3-090ca65cbcea'
--and modelno ='ZJ090C00B2B1AAA1A1'
GROUP BY ModelNo, MLP,Discount,BuyMultiplier,[Description] ,family,Brand
)
insert into #DuplicateModel(ModelNo,duplicatecount)
SELECT ct.ModelNo, COUNT(ct.ModelNo) FROM ct GROUP BY ct.ModelNo HAVING COUNT(ct.ModelNo)>1
SELECT o.ModelNo, MIN(o.Price)
FROM dbo.ustlineitems AS o
right outer join #DuplicateModel A on o.ModelNo = a.ModelNo
GROUP BY o.ModelNo;
Select tbl.* From dbo.ustlineitems tbl
Inner Join
(
Select ModelNo,MIN(UnitPrice) MinPoint From dbo.ustlineitems Group By ModelNo
)tbl1
On tbl1.ModelNo=tbl.ModelNo
Where JID like '47ff6ee3-a49f-4709-8cd3-090ca65cbcea' and tbl1.MinPoint=tbl.UnitPrice and tbl1.ModelNo in (
select dISTINCT(ModelNo) from #DuplicateModel)
END
Thanks
Vj
March 16, 2017 at 5:14 pm
Could you post the CREATE TABLE and INSERT scripts for this?
If this is true: "Duplicated rows has to be consolidated by modelnumber and price column should be of a min value out of the consolidated model numbers.Other columns QTY ,MLP and discount are to be summed up by model number."
then why are you using ROW_NUMBER()? Sounds like you need a plain GROUP BY with MIN(price). Sounds like homework...
March 16, 2017 at 5:39 pm
Thanks for your reply.Actually i need the sequence number to be displayed in the front end and so needs to build that too in the select query as well.
Please find the scripts below.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[LineItems](
[UnitID] [nvarchar](96) NOT NULL,
[SEQ] [nvarchar](50) NULL,
[MarkNo] [nvarchar](50) NULL,
[Qty] [int] NULL,
[ModelNo] [nvarchar](96) NOT NULL,
[MLP] [float] NULL,
[Discount] [float] NULL,
[Price] [float] NULL
)
GO
INSERT INTO [SFA].[dbo].[USTLineItems1]([UnitID],[SEQ],[MarkNo],[Qty],[ModelNo],[MLP],[Discount],[Price])
VALUES('1578b210-877d-4301-8c05-847a0148a382','00001' ,'',1,'TG8S060A12MP',11,602,273.31)
INSERT INTO [SFA].[dbo].[USTLineItems1]([UnitID],[SEQ],[MarkNo],[Qty],[ModelNo],[MLP],[Discount],[Price])
VALUES('4cebbc36-6de4-4174-b259-74acc62d83d1','00001' ,'',1,'S1-1TVM4G1',141,-75,35.25)
INSERT INTO [SFA].[dbo].[USTLineItems1]([UnitID],[SEQ],[MarkNo],[Qty],[ModelNo],[MLP],[Discount],[Price])
VALUES('6dcce707-fc51-4456-b9c4-b26f7a47782e','00001' ,'',1,'TG8S060A12MP',1159,-68.6,363.93)
INSERT INTO [SFA].[dbo].[USTLineItems1]([UnitID],[SEQ],[MarkNo],[Qty],[ModelNo],[MLP],[Discount],[Price])
VALUES('93e67d42-1270-4f6e-a9db-2ca84a3c030e','00001' ,'',3,'S1-1TVM4G1',1547,-58,0.42)
INSERT INTO [SFA].[dbo].[USTLineItems1]([UnitID],[SEQ],[MarkNo],[Qty],[ModelNo],[MLP],[Discount],[Price])
VALUES('1edf2929-6c02-4a5f-99f8-8de3ae2accdb','00001' ,'',1,'YC090C00A4AAA2',5329,-65.9,0.341)
March 16, 2017 at 5:51 pm
Is this homework? What have you tried? I have a solution... but show me how far you got.
This is the only "twist" at all.
ROW_NUMBER() OVER (ORDER BY ModelNo) AS rn
The rest is pure totals query.
March 16, 2017 at 6:21 pm
Hi
Actually I got stuck in the above script which I shared in my first post .
This is not a homework ??and it's my project and I got stuck here as am not expert in sql server .
Thanks
VJ
March 16, 2017 at 6:29 pm
This worked for me...
SELECT ModelNo
, ROW_NUMBER() OVER (ORDER BY ModelNo) AS RowNum
, SUM(Qty) AS TotalQty
, SUM(MLP) AS TotalMLP
, SUM(Discount) AS TotalDiscount
FROM dbo.LineItems
GROUP BY ModelNo;
The ROW_NUMBER()... part just numbers the records grouping/partitioning by ModelNo... you can call it anything you like. This is my result. (Can I format this using HTML? like using <table> etc?)
ModelNo rn TotalQty TotalMLP TotalDiscount
S1-1TVM4G1 1 4 1688 -133
TG8S060A12MP 2 2 1170 533.4
YC090C00A4AAA2 3 1 5329 -65.9
March 16, 2017 at 7:39 pm
I updated the query slightly to get the min price. I still facing the issue of getting the Unique model numbers as i have some more columns in the table BuyMultiplier(float value) with the different values for same model number. I have to include them in the select but BuyMultiplier should not be in the GROUP BY. Any ways to achieve it, Thanks for your help,
SELECT ModelNo
, ROW_NUMBER() OVER (ORDER BY ModelNo) AS SEQ
, SUM(Qty) AS TotalQty
, SUM(MLP) AS TotalMLP
, SUM(Discount) AS TotalDiscount
,MIN(UnitPrice) as UnitPrice
, BuyMultiplier,Family,Brand
FROM dbo.USTLineItems
WHERE JID like '47ff6ee3-a49f-4709-8cd3-090ca65cbcea' --and ModelNo='ZJ090C00B2B1AAA1A1'
GROUP BY BuyMultiplier,ModelNo,Family,Brand
Thanks
Vijay
March 16, 2017 at 8:02 pm
March 16, 2017 at 9:01 pm
Sorry i missed it in my first post.My Bad.
March 16, 2017 at 9:15 pm
Please find the updated script below,
USE [SFA]
GO
/****** Object: Table [dbo].[USTLineItems] Script Date: 03/16/2017 18:27:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[USTLineItems1](
[UnitID] [nvarchar](96) NOT NULL,
[SEQ] [nvarchar](50) NULL,
[MarkNo] [nvarchar](50) NULL,
[Qty] [int] NULL,
[ModelNo] [nvarchar](96) NOT NULL,
[MLP] [float] NULL,
[Discount] [float] NULL,
[Price] [float] NULL,
[BuyMultiplier] [float] NULL
)
INSERT INTO [SFA].[dbo].[USTLineItems1]([UnitID],[SEQ],[MarkNo],[Qty],[ModelNo],[MLP],[Discount],[Price],[BuyMultiplier])
VALUES('1578b210-877d-4301-8c05-847a0148a382','00001' ,'',1,'TG8S060A12MP',11,602,273.31,1.1)
INSERT INTO [SFA].[dbo].[USTLineItems1]([UnitID],[SEQ],[MarkNo],[Qty],[ModelNo],[MLP],[Discount],[Price],[BuyMultiplier])
VALUES('4cebbc36-6de4-4174-b259-74acc62d83d1','00001' ,'',1,'S1-1TVM4G1',141,-75,35.25,2.1)
INSERT INTO [SFA].[dbo].[USTLineItems1]([UnitID],[SEQ],[MarkNo],[Qty],[ModelNo],[MLP],[Discount],[Price],[BuyMultiplier])
VALUES('6dcce707-fc51-4456-b9c4-b26f7a47782e','00001' ,'',1,'TG8S060A12MP',1159,-68.6,363.93,1.2)
INSERT INTO [SFA].[dbo].[USTLineItems1]([UnitID],[SEQ],[MarkNo],[Qty],[ModelNo],[MLP],[Discount],[Price],[BuyMultiplier])
VALUES('93e67d42-1270-4f6e-a9db-2ca84a3c030e','00001' ,'',3,'S1-1TVM4G1',1547,-58,0.42,2.1)
INSERT INTO [SFA].[dbo].[USTLineItems1]([UnitID],[SEQ],[MarkNo],[Qty],[ModelNo],[MLP],[Discount],[Price],[BuyMultiplier])
VALUES('1edf2929-6c02-4a5f-99f8-8de3ae2accdb','00001' ,'',1,'YC090C00A4AAA2',5329,-65.9,0.341,3.1)
SELECT ModelNo
, ROW_NUMBER() OVER (ORDER BY ModelNo) AS SEQ
, SUM(Qty) AS TotalQty
, SUM(MLP) AS TotalMLP
, SUM(Discount) AS TotalDiscount
,MIN(Price) as UnitPrice
, BuyMultiplier
FROM dbo.USTLineItems1
GROUP BY ModelNo,BuyMultiplier
March 16, 2017 at 9:55 pm
So where does [BuyMultiplier] fit into the answer? How does it change the expected result? If you group by both ModelNo and BuyMultiplier, you will get one record for each unique combination of the two column values. Is that what you want?
March 20, 2017 at 8:31 am
hi,
ya you are correct. Below query fetches the unique records.Thanks for your help
SELECT ModelNo
, ROW_NUMBER() OVER (ORDER BY ModelNo) AS SEQ
, SUM(Qty) AS TotalQty
, SUM(MLP) AS TotalMLP
, SUM(Discount) AS TotalDiscount
,MIN(Price) as UnitPrice
,SUM( BuyMultiplier)
FROM dbo.USTLineItems1
GROUP BY ModelNo
Thanks
vj
March 20, 2017 at 10:32 am
pietlinden - Thursday, March 16, 2017 9:55 PMSo where does [BuyMultiplier] fit into the answer? How does it change the expected result? If you group by both ModelNo and BuyMultiplier, you will get one record for each unique combination of the two column values. Is that what you want?
I have one issue now, Am not getting the correct minimum of price column. for example i have the price values as below on four rows of same model nulmber
UnitPrice Model No
6950 ZT061N08P4B1BAA1A1
6984.75 ZT061N08P4B1BAA1A1
6820.44 ZT061N08P4B1BAA1A1
6820.44 ZT061N08P4B1BAA1A1
SELECT ModelNo
,MIN(UnitPrice) as Price
FROM dbo.USTLineItems
where
ModelNo like 'ZT061N08P4B1BAA1A1'
GROUP BY ModelNo
The above query produce the below result with price 6861.83 instead of gettting the minimum price 6820.44 .Any idea on this
ModelNo Price
ZT061N08P4B1BAA1A1 6861.83
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply