Grouping and sorting data

[Trench37716]

I have this gigantic query that returns certain data from my MSSQL Database (working perfectly- Courtesy of djjeavons). I want to sort and arrange this data in order. I want if the user selects a range of dates from the form (ex. November 1, 2014 - November 9, 2014), the result should be grouped in order (November 1, 2014, November 2, 2014, November 3, 2014 ..... November 9, 2014). I would like for each of the results to form its own dataset.

# November 1, 2014 #                                                                     
Distributor/Importer       OpeningPMS  Transfer  Sales Closing  
        (ADD DATA)

# November 2, 2014 #                                                                     
Distributor/Importer       OpeningPMS  Transfer  Sales Closing  
        (ADD DATA)

# November 3, 2014 #                                                                     
Distributor/Importer       OpeningPMS  Transfer  Sales Closing  
        (ADD DATA)
                    .
                    .
                    .
# November 9, 2014 #                                                                     
Distributor/Importer       OpeningPMS  Transfer  Sales Closing  
        (ADD DATA)

SELECT        DistributorImporter, max(OpeningPMS) AS OpeningPMS, max(OpeningAGO) AS OpeningAGO, max(OpeningATK) AS OpeningATK, Transaction_Date
FROM            (SELECT        Distributors.DistributorName + '/' + Importers.ImporterName AS DistributorImporter, 
                                                    CASE WHEN Product_Delivery_2. Product = 1 THEN Opening END AS OpeningPMS,
                                                    CASE WHEN Product_Delivery_2. Product = 2 THEN Opening END AS OpeningAGO, 
                                                    CASE WHEN Product_Delivery_2. Product = 3 THEN Opening END AS OpeningATK,
                                                     Transaction_Date
                          FROM            dbo.Product_Delivery AS Product_Delivery_2 INNER JOIN
                                                        (SELECT        DistributorID, DistributorName
                                                          FROM            (SELECT        ImporterDistributor_1.ID AS DistributorID, ImporterDistributor_1.Name AS DistributorName
                                                                                    FROM            dbo.ImporterDistributor AS ImporterDistributor_1 INNER JOIN
                                                                                                              dbo.Product_Delivery AS Product_Delivery_1 ON ImporterDistributor_1.ID = Product_Delivery_1.Distributor
--                                                                                                            where Product_Delivery_1.Transaction_Date='2014-11-01'
                                                                                    GROUP BY ImporterDistributor_1.ID, ImporterDistributor_1.Name) AS derivedtbl_1) AS Distributors ON 
                                                    Product_Delivery_2.Distributor = Distributors.DistributorID INNER JOIN
                                                        (SELECT        dbo.ImporterDistributor.ID AS ImporterID, dbo.ImporterDistributor.Name AS ImporterName
                                                          FROM            dbo.ImporterDistributor INNER JOIN
                                                                                    dbo.Product_Delivery ON dbo.ImporterDistributor.ID = dbo.Product_Delivery.Importer
--                                                                                   where cast(Product_Delivery.Transaction_Date as datetime2)='2014-11-01'
                                                          GROUP BY dbo.ImporterDistributor.ID, dbo.ImporterDistributor.Name) AS Importers ON Product_Delivery_2.Importer = Importers.ImporterID) 
                         AS TransactionData
                          where TransactionData.Transaction_Date>='2014-11-01' and TransactionData.Transaction_Date <= '2014-11-09'
GROUP BY DistributorImporter, TransactionData.CommentPMS, TransactionData.Transaction_Date

[J.C. SolvoTerra]

Without going too far in to this have you tried serializing your data response into an object structure, or using LINQ?

If serializing or LINQ doesn't make any sense then don't worry too much about it, I may be missunderstanding your requirement.