I have data like this. first row of Id 1 from particular time period and second row of id 1 is another time period. so now want to combined id and name which are same in the two time periods reaming are same.if there is no orders from that time period its should be display 0 or null.
Id Name Qty Price
----------------------
1 Rose 4 540
1 Rose 1 640
2 Lilly 5 550
2 Lilly 18 360
3 Grand 2 460
3 Grand 10 360
4 lotus 0 0
4 Lotus 9 580
now I want data like this..
Id Name Qty Price
4 540
1 rose
1 640
5 550
2 Lilly
18 360
2 460
3 Grand
10 360
0 0
4 Lotus
9 580
This is my procedure
create PROCEDURE [dbo].[Sp_Orders]
(
@Startdate varchar(30),
@Enddate varchar(30),
@Startdate1 varchar(30),
@Enddate1 varchar(30)
)
--[Sp_Orders] '03/01/2016','03/15/2016','02/01/2016','02/28/2016'
AS
BEGIN
---First Duration----
SELECT DISTINCT
op.ProductId as id, op.Price as Prc,
sc.SubCategoryName as ScName,
COUNT(op.ProductId) AS Qty,
ROUND(SUM(op.Price * op.Quantity), 0) AS Revenue,
FROM
orderdetails od
INNER JOIN
(SELECT DISTINCT
Orderid, Productid, ProductFeatures, Price, Quantity
FROM
OrderProducts) op ON od.Orderid = op.Orderid
INNER JOIN
products p ON p.productid = op.productid
INNER JOIN
subcategory sc ON sc.subcategoryid = p.subcategoryid
WHERE
CONVERT(datetime, CONVERT(varchar(50), od.DeliveryDate, 101)) BETWEEN @Startdate AND @Enddate
GROUP BY
op.ProductID, op.Price, sc.SubCategoryName
---Second Duration----
SELECT DISTINCT
op.ProductID AS id, op.Price AS Prc,
sc.SubCategoryName AS ScName,
COUNT(op.ProductId) AS Qty,
ROUND(SUM(op.Price * op.Quantity), 0) AS Revenue,
FROM
orderdetails od
INNER JOIN
(SELECT DISTINCT
Orderid, Productid, ProductFeatures, Price, Quantity
FROM
OrderProducts) op ON od.Orderid = op.Orderid
INNER JOIN
products p ON p.productid = op.productid
INNER JOIN
subcategory sc ON sc.subcategoryid = p.subcategoryid
WHERE
CONVERT(datetime, CONVERT(varchar(50),od.DeliveryDate,101)) BETWEEN @Startdate1 AND @Enddate1
GROUP BY
op.ProductID, op.Price, sc.SubCategoryName
END
Aucun commentaire:
Enregistrer un commentaire