| Author | Message | ||
Driverramudu Hero Username: Driverramudu Post Number: 16905 Registered: 02-2009 Posted From: 166.170.14.26 Rating: |
Banam Chk ur mail Driving is my PASSION. Ball or Bimmer does not matter. | ||
Banam Hero Username: Banam Post Number: 12323 Registered: 12-2011 Posted From: 209.203.79.108 Rating: N/A |
Database is Oracle.. Green Bay PACKERS ||San Antonio SPURS ||Sachin Ramesh TENDULKAR | ||
Banam Hero Username: Banam Post Number: 12322 Registered: 12-2011 Posted From: 209.203.79.108 Rating: N/A |
Sent bro Sample data inserts. Green Bay PACKERS ||San Antonio SPURS ||Sachin Ramesh TENDULKAR | ||
Banam Hero Username: Banam Post Number: 12321 Registered: 12-2011 Posted From: 209.203.79.108 Rating: N/A |
Sure bro.. Green Bay PACKERS ||San Antonio SPURS ||Sachin Ramesh TENDULKAR | ||
Driverramudu Hero Username: Driverramudu Post Number: 16903 Registered: 02-2009 Posted From: 166.170.14.26 Rating: N/A |
Banam If u need query then Send the sample data to my email To driver.ramudu79 at gmail I will reply to that Driving is my PASSION. Ball or Bimmer does not matter. | ||
Driverramudu Hero Username: Driverramudu Post Number: 16902 Registered: 02-2009 Posted From: 166.170.14.26 Rating: N/A |
Banan Use CTE and outer apply operator Then u will get what you want  Driving is my PASSION. Ball or Bimmer does not matter. | ||
Banam Hero Username: Banam Post Number: 12320 Registered: 12-2011 Posted From: 209.203.79.108 Rating: N/A |
Tried this bro, but kudaraledhu because of EXP records existing in middle. There can be multiple of them some times... Green Bay PACKERS ||San Antonio SPURS ||Sachin Ramesh TENDULKAR | ||
Abcdefghij Hero Username: Abcdefghij Post Number: 18998 Registered: 02-2007 Posted From: 68.42.113.64 Rating: N/A |
sorry ignore my post i did not read about renewal_cnt and thought it was pivot Post by Knf: bittter annai, welcome RS prakasam varaku madi, Prakasm nunchi UA vaaraku meeedi http://www.chalanachithram.com/cgi-bin/discus/show.cgi?tpc=115&post=5144373#POST5144373 | ||
Symbol_of_king Junior Artist Username: Symbol_of_king Post Number: 266 Registered: 12-2010 Posted From: 72.219.244.175 Rating: N/A |
you need to use lead or lag analytic functions to achieve this. | ||
Abcdefghij Hero Username: Abcdefghij Post Number: 18997 Registered: 02-2007 Posted From: 68.42.113.64 Rating: N/A |
its multi step process if i understood your requirement 1. u need to create dynamic pivot, store below columns in temp table REG_ID||Pkg_DES||PRD_DESC||EVENT_DATE||event_type_cd||renewa 2. then you need to apply your business rules for is_conversion & is_renewal etc on above temp table https://www.mssqltips.com/sqlservertip/2783/script-to-create -dynamic-pivot-queries-in-sql-server/ Post by Knf: bittter annai, welcome RS prakasam varaku madi, Prakasm nunchi UA vaaraku meeedi http://www.chalanachithram.com/cgi-bin/discus/show.cgi?tpc=115&post=5144373#POST5144373 | ||
Banam Hero Username: Banam Post Number: 12319 Registered: 12-2011 Posted From: 209.203.79.108 Rating: N/A |
Hi Guys, I have the requirement like this below REG_ID || PACKAGE_DESC || PRODUCT_TYPE_DESC || EVENT_DATE || event_type_cd 1 || CC || trail || 12-12-2012 || new sub 1 || CC || trail || 12-13-2012 || exp 1 || CC || PAID || 12-14-2012 || upsell 1 || CC || PAID || 12-15-2012 || exp 1 || CC || PAID || 12-16-2012 || renewal 1 || CC || PAID || 12-17-2012 || renewal 1 || aa || trail || 12-12-2012 || new sub 1 || aa || trail || 12-13-2012 || exp 1 || aa || PAID || 12-14-2012 || renewal 1 || aa || PAID || 12-15-2012 || renewal 1 || aa || PAID || 12-16-2012 || upsell 1 || aa || PAID || 12-17-2012 || renewal The OutPut i need is like below REG_ID||Pkg_DES||PRD_DESC||EVENT_DATE||event_type_cd||renewa l_cnt||is_ren|| is_conv 1 || CC || trail || 12-12-2012 || new sub || 0 || 0 ||0 1 || CC || trail || 12-13-2012 || exp || 0 || 0 ||0 1 || CC || PAID || 12-14-2012 || upsell || 0 || 0 || 1 1 || CC || PAID || 12-15-2012 || exp || 0 || 0 || 1 1 || CC || PAID || 12-16-2012 || renewal || 1 || 1 || 0 1 || CC || PAID || 12-17-2012 || renewal || 2 || 1 || 0 1 || aa || trail || 12-12-2012 || new sub || 0 || 0 || 0 1 || aa || trail || 12-13-2012 || exp || 0 || 0 || 0 1 || aa || PAID || 12-14-2012 || renewal || 0 || 0 || 1 1 || aa || PAID || 12-15-2012 || renewal || 1 || 1 || 0 1 || aa || PAID || 12-16-2012 || upsell || 2 || 1 || 0 1 || aa || PAID || 12-17-2012 || renewal || 3 || 1 || 0 - Conversion Logic :- If a PRD_DESC is changed from Trail to Paid for the 1st time, then it is called CONVERSION product - Renewal Logic :- If a Paid Product is renewed from Paid to Paid, then it is called RENEWAL product - renewal_cnt should start only at the PAID to PAID product. If the PAID to PAID is having event_type_cd as exp then the counter shouldn't increment. If the trail to paid is having event_type_cd as renewal then also it should be zero only. the event's are ordered by event_date - is_conv should be set to 1 for the 1st Trail to Paid product. - is ren should be set to 1 for the paid to paid product. Does someone have the idea how to achieve this?} Green Bay PACKERS ||San Antonio SPURS ||Sachin Ramesh TENDULKAR |
