Last active
August 29, 2015 14:10
-
-
Save malkitsingh/630cca49f097fc542635 to your computer and use it in GitHub Desktop.
apna taste testing queries
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
select * from orders | |
select * from orders ORDER BY id DESC |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
USE [sweetsalesdbnopricerule] | |
GO | |
/****** Object: StoredProcedure [sweetsalesdbnopr].[GetOrderSummaryReport] Script Date: 12/3/2014 10:14:15 AM ******/ | |
SET ANSI_NULLS ON | |
GO | |
SET QUOTED_IDENTIFIER ON | |
GO | |
ALTER PROCEDURE [sweetsalesdbnopr].[GetOrderSummaryReport] | |
@vTransactionDate datetime='2014-12-03 00:00:00.000', | |
@vCustomerId int=44 | |
AS | |
BEGIN | |
-- SET NOCOUNT ON added to prevent extra result sets from | |
-- interfering with SELECT statements. | |
SET NOCOUNT ON; | |
Declare @SQLQuery AS NVarchar(4000) | |
Declare @ParamDefinition AS NVarchar(2000) | |
Set @SQLQuery = ' SELECT xx.ItemId,xx.Quantity, i.Name FROM | |
( | |
SELECT od.ItemId, SUM(CONVERT(int, ISNULL(od.Quantity,0))) AS Quantity FROM dbo.OrderDetails od | |
WHERE | |
od.OrderId IN | |
(SELECT id FROM dbo.Orders o WHERE 1=1' | |
IF @vTransactionDate IS NOT NULL | |
Set @SQLQuery = @SQLQuery + ' and o.ShippingDate=@vTransactionDate' | |
IF @vCustomerId IS NOT NULL | |
Set @SQLQuery = @SQLQuery + ' and o.CutomerId=@vCustomerId' | |
Set @SQLQuery = @SQLQuery + ') GROUP BY od.ItemId )xx INNER JOIN dbo.Items i ON i.Id=xx.ItemId' | |
Set @ParamDefinition = ' @vTransactionDate datetime, | |
@vCustomerId int' | |
Execute sp_Executesql @SQLQuery, | |
@ParamDefinition, @vTransactionDate, @vCustomerId | |
--SELECT xx.ItemId,xx.Quantity, i.Name FROM | |
-- ( | |
-- SELECT od.ItemId, SUM(CONVERT(int, ISNULL(od.Quantity,0))) AS Quantity FROM dbo.OrderDetails od | |
-- WHERE | |
-- od.OrderId IN | |
-- (SELECT id FROM dbo.Orders o WHERE o.ShippingDate=@vTransactionDate) | |
-- GROUP BY od.ItemId | |
-- )xx INNER JOIN dbo.Items i | |
-- ON i.Id=xx.ItemId | |
END |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment