quote:

---

Originally posted by nicky_river

Hi Everyone,

I need to display the report datewise i.e by "Date_of_Matching"

---

quote:

---

Originally posted by nicky_river

Hi Everyone,

also I need to give the user the option of "from date" and "TO date", with "from date" and "to date" can be the same date. the below query is used to generate a report in SSRS.

---

DECLARE @sql VARCHAR(MAX)
DECLARE @from_date DATETIME
DECLARE @to_date DATETIME
SET @from_date = '2010-08-21'
SET @to_date = '2010-10-23'
SET @sql = '

SELECT  to_char(dt.date_application,''dd-mm-yyyy'') Date_of_Matching,
		ins.underlying_display_name Commodity_Name,
		ins.symbol Contract_Name,
		dtd.clearing_member_code CM_ID_Seller,
		dtd.member_code TM_ID_Seller,
		( SELECT name FROM members m WHERE m.member_code = dtd.clearing_member_code ) CM_Name_Seller,
		( SELECT name FROM members m WHERE m.member_code = dtd.member_code ) TM_Name_Seller,
		dtd.buy_sell Category_Seller,
		la.balance Open_Position_in_Lots_Seller,
		dt.quantity MatchedQty_in_Lots_Seller,
		ih.delivery_settlement_price Rate_S,
		dt.value Delivery_Amount_S,
		to_char(dt.date_cash_pay_out,''dd-mm-yyyy'') Funds_Settlement_Date_S,
		to_char(dt.date_cash_pay_in,''dd-mm-yyyy'') Delivery_Settlement_Date_S,
		BUYER.clearing_member_code CM_ID_Buyer,
		BUYER.member_code TM_ID_Buyer,
		( SELECT name FROM members m WHERE m.member_code = BUYER.clearing_member_code ) CM_Name_Buyer,
		( SELECT name FROM members m WHERE m.member_code = BUYER.member_code ) TM_Name_Buyer,
		BUYER.buy_sell Category_Buyer,
		BUYER.balance Open_Position_in_Lots_Buyer,
		BUYER.quantity MatchedQty_in_Lots_Buyer,
		BUYER.delivery_settlement_price Rate,
		BUYER.value Delivery_Amount,
		to_char(BUYER.date_cash_pay_out,''dd-mm-yyyy'') Funds_Settlement_Date,
		to_char(BUYER.date_cash_pay_in,''dd-mm-yyyy'') Delivery_Settlement_Date
FROM DELIVERY_TRANSACTIONS dt,
	 INSTRUMENTS ins,
	 DELIVERY_TRANSACTION_DETAILS dtd,
	 LEDGER_ACCOUNTS la,
	 INSTRUMENT_HISTORIES IH,
	(	
		SELECT dt1.date_application,
			   dt1.delivery_reference,
			   ins1.instrument_id, ins1.underlying_display_name,
			   ins1.symbol,
			   dtd1.clearing_member_code,
			   dtd1.member_code,
			   dtd1.buy_sell,
               la1.balance,
               dt1.quantity,
               ih1.delivery_settlement_price,
               dt1.value,
               dt1.date_cash_pay_out,
               dt1.date_cash_pay_in
         FROM DELIVERY_TRANSACTIONS dt1,
              INSTRUMENTS ins1,
			  DELIVERY_TRANSACTION_DETAILS dtd1,
			  LEDGER_ACCOUNTS la1,
			  INSTRUMENT_HISTORIES IH1
		 WHERE dt1.instrument_id = ins1.instrument_id 
				and dt1.delivery_reference = dtd1.delivery_reference
				and dt1.date_application = dtd1.date_application
				and dt1.date_application BETWEEN '''+CAST(@from_date AS VARCHAR(15))+''' AND '''+CAST(@to_date AS VARCHAR(15))+'''
				and la1.member_code = dtd1.clearing_member_code
				and la1.member_client_reference = dtd1.member_client_reference
				and la1.instrument_id = ins1.instrument_id
				and IH1.DATE_MARKET = dt1.date_application
				and IH1.INSTRUMENT_ID = ins1.instrument_id
				and dtd1.buy_sell = ''B''
   ) BUYER
WHERE dt.instrument_id = ins.instrument_id 
	 and dt.delivery_reference = dtd.delivery_reference
	 and dt.date_application = dtd.date_application
	 and dt.date_application BETWEEN '''+CAST(@from_date AS VARCHAR(15))+ ''' AND ''' +CAST(@to_date AS VARCHAR(15)) + '''
	 and la.member_code = dtd.clearing_member_code
	 and la.member_client_reference = dtd.member_client_reference
	 and la.instrument_id = ins.instrument_id
	 and IH.DATE_MARKET = dt.date_application
	 and IH.INSTRUMENT_ID = ins.instrument_id
	 and dtd.buy_sell = ''S''
	 and BUYER.date_application = dt.date_application
	 and BUYER.delivery_reference = dt.delivery_reference
	 and BUYER.instrument_id = ins.instrument_id
ORDER BY dt.date_application
'
	 
	 
	 -- PRINT @sql -- uncomment this to check whether the query is formed correctly
EXEC(@sql)