samedi 18 juin 2016

How to merge two particular rows into single row and reaming rows are same using stored procedure SQL Server 2012

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