26 Sept 2013

Export multiple DataSets to multiple Excel sheets dynamically formatted according to the record's data type

“Export multiple DataSets to multiple Excel sheets dynamically formatted according to the record's data type”



public void DataSetsToExcel(List<DataSet> dataSets, string fileName)
    {
        Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
        Sheets xlSheets = null;
        Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = null;

        foreach (DataSet dataSet in dataSets)
        {
            System.Data.DataTable dataTable = dataSet.Tables[0];
            int rowNo = dataTable.Rows.Count;
            int columnNo = dataTable.Columns.Count;
            int colIndex = 0;

            //Create Excel Sheets
            xlSheets = xlWorkbook.Sheets;
            xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.Add(xlSheets[1],
                           Type.Missing, Type.Missing, Type.Missing);
            xlWorksheet.Name = dataSet.DataSetName;

            //Generate Field Names
            foreach (DataColumn dataColumn in dataTable.Columns)
            {
                colIndex++;
                xlApp.Cells[1, colIndex] = dataColumn.ColumnName;
            }

            object[,] objData = new object[rowNo, columnNo];

            //Convert DataSet to Cell Data
            for (int row = 0; row < rowNo; row++)
            {
                for (int col = 0; col < columnNo; col++)
                {
                    objData[row, col] = dataTable.Rows[row][col];
                }
            }

            //Add the Data
            Range range = xlWorksheet.Range[xlApp.Cells[2, 1], xlApp.Cells[rowNo + 1, columnNo]];
            range.Value2 = objData;

            //Format Data Type of Columns
            colIndex = 0;
            foreach (DataColumn dataColumn in dataTable.Columns)
            {
                colIndex++;
                string format = "@";
                switch (dataColumn.DataType.Name)
                {
                    case "Boolean":
                        break;
                    case "Byte":
                        break;
                    case "Char":
                        break;
                    case "DateTime":
                        format = "dd/mm/yyyy";
                        break;
                    case "Decimal":
                        format = "$* #,##0.00;[Red]-$* #,##0.00";
                        break;
                    case "Double":
                        break;
                    case "Int16":
                        format = "0";
                        break;
                    case "Int32":
                        format = "0";
                        break;
                    case "Int64":
                        format = "0";
                        break;
                    case "SByte":
                        break;
                    case "Single":
                        break;
                    case "TimeSpan":
                        break;
                    case "UInt16":
                        break;
                    case "UInt32":
                        break;
                    case "UInt64":
                        break;
                    default: //String
                        break;
                }
                //Format the Column accodring to Data Type
                xlWorksheet.Range[xlApp.Cells[2, colIndex],
                      xlApp.Cells[rowNo + 1, colIndex]].NumberFormat = format;
            }
        }

        //Remove the Default Worksheet
        ((Microsoft.Office.Interop.Excel.Worksheet)xlApp.ActiveWorkbook.Sheets[xlApp.ActiveWorkbook.Sheets.Count]).Delete();

        //Save
        xlWorkbook.SaveAs(fileName, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, XlSaveAsAccessMode.xlNoChange, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
        xlWorkbook.Close();
        xlApp.Quit();
        GC.Collect();
    }





DataSet dataSet1 = new DataSet("My Data Set 1");
dataAdapter1.Fill(dataSet1);

DataSet dataSet2 = new DataSet("My Data Set 2");
dataAdapter1.Fill(dataSet2);

DataSet dataSet3 = new DataSet("My Data Set 3");
dataAdapter1.Fill(dataSet3);

List<DataSet> dataSets = new List<DataSet>();
dataSets.Add(dataSet1);
dataSets.Add(dataSet2);
dataSets.Add(dataSet3);

DataSetsToExcel(dataSets, "{Your File Name}")

at 24 comments:

25 Sept 2013

HOW TO ENABLE GAME IN WINDOW 7 ?

HOW TO ENABLE GAME IN WINDOW 7 ?


1. CLICK ON START BUTTON.
 


2. GO TO CONTROL PANEL.
 


3. CHANGE THE CATEGORY IN VIEW MODE.



4. SELECT PROGRAM.



5. SELECT PROGRAM AND FEATURES.



6. THEN CLICK ON (LEFT SIDE) TURN WINDOWS FEATURES ON OR OFF.



7. DOUBLE CLICK ON GAMES AND OK.


8. NOW YOUR GAMES ARE READY.




HOW TO ENABLE GAMES IN WINDOW 7 ?
at 6 comments:

2 Aug 2013

Security token Key Generagor in C#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace XSLT_Demo
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            string TokenKey = uniqueTokenKey();
            Response.Write("Unredable : " + TokenKey + "<br />");
            Response.Write("..Redable : " + ReaduniqueTokenKey(TokenKey) + "<br />");
        }


        private string uniqueTokenKey()
        {
            Random rnd = new Random();
            byte[] bytes = new byte[32];
            rnd.NextBytes(bytes);
            string myRndID = BitConverter.ToString(bytes);
            string[] myRndIDarr = myRndID.Split('-');

            string datekey = "";
            datekey = DateTime.Now.ToString("{.1}");
            string rekey = "";
            for (int i = datekey.Length; i > 0; i--)
            {
                rekey += datekey.Substring(i - 1, 1);
            }
            string[] Keyarr = rekey.Split('-');


            string finalkey = "";
            for (int i = 0; i < Keyarr.Length; i++)
            {
                finalkey += myRndIDarr[i] + "-" + myRndIDarr[i + 1] + "-" + Keyarr[i] + "" + "-";
            }

            return finalkey.Replace("-", "");
        }


        private string ReaduniqueTokenKey(string key)
        {
            string rekey = "";
            rekey = key.Substring(42, 3) + ":" + key.Substring(36, 2) + ":" + key.Substring(12, 2) + ":" + key.Substring(18, 2) + ":" + key.Substring(24, 2) + ":" + key.Substring(30, 2) + ":" + key.Substring(4, 4);
            string readablevalue = "";

            for (int i = rekey.Length; i > 0; i--)
            {
                readablevalue += rekey.Substring(i - 1, 1);
            }

            return readablevalue;
        }

    }
}






{.1}                         fff-ss-MM-dd-hh-mm-yyyy
at 20 comments:

27 Jul 2013

Multiple Markers View in Google Map Integration :

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <script type="text/javascript" src="http://maps.googleapis.com/maps/api/js?sensor=false"></script>
        <script type="text/javascript">
            var map; var infowindow;
            function InitializeMap() {
                var latlng = new google.maps.LatLng(40.756, -73.986);
                var myOptions =
        {
            zoom: 8,
            center: latlng,
            mapTypeId: google.maps.MapTypeId.ROADMAP
        };
                map = new google.maps.Map(document.getElementById("map"), myOptions);
            }


            function markicons() {

                InitializeMap();

                var ltlng = [];

                ltlng.push(new google.maps.LatLng(17.22, 78.28));
                ltlng.push(new google.maps.LatLng(13.5, 79.2));
                ltlng.push(new google.maps.LatLng(15.24, 77.16));

                map.setCenter(ltlng[0]);
                for (var i = 0; i <= ltlng.length; i++) {
                    marker = new google.maps.Marker({
                        map: map,
                        position: ltlng[i]
                    });

                    (function (i, marker) {

                        google.maps.event.addListener(marker, 'click', function () {

                            if (!infowindow) {
                                infowindow = new google.maps.InfoWindow();
                            }

                            infowindow.setContent("Message" + i);

                            infowindow.open(map, marker);

                        });

                    })(i, marker);

                }

            }

            window.onload = markicons;

        </script>
        <h2>
            Multiple Markers View in Google Map Integration :</h2>
        <div id="map" style="width: 896px; top: 51px; left: 32px; position: absolute; height: 400px">
        </div>
    </div>
    </form>
</body>
</html>


at 16 comments:

24 Jul 2013

sql server Join Query


BEGIN
      select id, mappedto, '4306' as cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,status,entrydate
      into [update20130723].[dbo].[tab_CitySampleMappings_Paris_test]
      from
      (
      select id,mappedto,'4306' as cityid,'MATCH' as MAPCHECKSTATUS,'FUZZY_SYSTEM' as checkbyuser,getdate() as MAPCHECKTIME,'active' as status,getdate() as entrydate
      from [FuzzyMap2].[dbo].[FuzzyParis2$]
      where not id in (SELECT  [id] FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto)
      union all
      SELECT id, mappedto, cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,'activee' as status,getdate() as entrydate FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto
      ) X where x.id in
      (
      select id from ibehotels.dbo.tab_hotels A
      inner join Ibedb.dbo.tab_masterCities B on a.citycode=b.CityCode and a.source=b.source
      where 
      b.cityname like 'Paris%' and
      not b.source in
      ('AMADEUS',
      'ASIAN')
      )
END



BEGIN
      select id, mappedto, '2902' as cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,status,entrydate
      into [update20130723].[dbo].[tab_CitySampleMappings_Kualalumpur_test]
      from
      (
      select id,mappedto,'2902' as cityid,'MATCH' as MAPCHECKSTATUS,'FUZZY_SYSTEM' as checkbyuser,getdate() as MAPCHECKTIME,'active' as status,getdate() as entrydate
      from [FuzzyMap2].[dbo].[FuzzyKualalumpur2$]
      where not id in (SELECT  [id] FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto)
      union all
      SELECT id, mappedto, cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,'activee' as status,getdate() as entrydate FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto
      ) X where x.id in
      (
      select id from ibehotels.dbo.tab_hotels A
      inner join Ibedb.dbo.tab_masterCities B on a.citycode=b.CityCode and a.source=b.source
      where 
      b.cityname like 'KUALA LUMPUR%' and
      not b.source in
      ('AMADEUS',
      'ASIAN')
      )
END



BEGIN
      select id, mappedto, '9196' as cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,status,entrydate
      into [update20130723].[dbo].[tab_CitySampleMappings_Pattaya_test]
      from
      (
      select id,mappedto,'9196' as cityid,'MATCH' as MAPCHECKSTATUS,'FUZZY_SYSTEM' as checkbyuser,getdate() as MAPCHECKTIME,'active' as status,getdate() as entrydate
      from [FuzzyMap2].[dbo].[FuzzyPattaya2$]
      where not id in (SELECT  [id] FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto)
      union all
      SELECT id, mappedto, cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,'activee' as status,getdate() as entrydate FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto
      ) X where x.id in
      (
      select id from ibehotels.dbo.tab_hotels A
      inner join Ibedb.dbo.tab_masterCities B on a.citycode=b.CityCode and a.source=b.source
      where 
      b.cityname like 'Pattaya%' and
      not b.source in
      ('AMADEUS',
      'ASIAN')
      )
END



BEGIN
      select id, mappedto, '418' as cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,status,entrydate
      into [update20130723].[dbo].[tab_CitySampleMappings_Bangkok_test]
      from
      (
      select id,mappedto,'418' as cityid,'MATCH' as MAPCHECKSTATUS,'FUZZY_SYSTEM' as checkbyuser,getdate() as MAPCHECKTIME,'active' as status,getdate() as entrydate
      from [FuzzyMap2].[dbo].[FuzzyBangkok2$]
      where not id in (SELECT  [id] FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto)
      union all
      SELECT id, mappedto, cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,'activee' as status,getdate() as entrydate FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto
      ) X where x.id in
      (
      select id from ibehotels.dbo.tab_hotels A
      inner join Ibedb.dbo.tab_masterCities B on a.citycode=b.CityCode and a.source=b.source
      where 
      b.cityname like 'Bangkok%' and
      not b.source in
      ('AMADEUS',
      'ASIAN')
      )
END



BEGIN
      select id, mappedto, '3648' as cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,status,entrydate
      into [update20130723].[dbo].[tab_CitySampleMappings_Milan_test]
      from
      (
      select id,mappedto,'3648' as cityid,'MATCH' as MAPCHECKSTATUS,'FUZZY_SYSTEM' as checkbyuser,getdate() as MAPCHECKTIME,'active' as status,getdate() as entrydate
      from [FuzzyMap2].[dbo].[FuzzyMilan2$]
      where not id in (SELECT  [id] FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto)
      union all
      SELECT id, mappedto, cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,'activee' as status,getdate() as entrydate FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto
      ) X where x.id in
      (
      select id from ibehotels.dbo.tab_hotels A
      inner join Ibedb.dbo.tab_masterCities B on a.citycode=b.CityCode and a.source=b.source
      where 
      b.cityname like 'Milan%' and
      not b.source in
      ('AMADEUS',
      'ASIAN')
      )
END



BEGIN
      select id, mappedto, '3216' as cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,status,entrydate
      into [update20130723].[dbo].[tab_CitySampleMappings_London_test]
      from
      (
      select id,mappedto,'3216' as cityid,'MATCH' as MAPCHECKSTATUS,'FUZZY_SYSTEM' as checkbyuser,getdate() as MAPCHECKTIME,'active' as status,getdate() as entrydate
      from [FuzzyMap2].[dbo].[FuzzyLondon2$]
      where not id in (SELECT  [id] FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto)
      union all
      SELECT id, mappedto, cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,'activee' as status,getdate() as entrydate FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto
      ) X where x.id in
      (
      select id from ibehotels.dbo.tab_hotels A
      inner join Ibedb.dbo.tab_masterCities B on a.citycode=b.CityCode and a.source=b.source
      where 
      b.cityname like 'London%' and
      not b.source in
      ('AMADEUS',
      'ASIAN')
      )
END



BEGIN
      select id, mappedto, '6353' as cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,status,entrydate
      into [update20130723].[dbo].[tab_CitySampleMappings_Zurich_test]
      from
      (
      select id,mappedto,'6353' as cityid,'MATCH' as MAPCHECKSTATUS,'FUZZY_SYSTEM' as checkbyuser,getdate() as MAPCHECKTIME,'active' as status,getdate() as entrydate
      from [FuzzyMap2].[dbo].[FuzzyZurich2$]
      where not id in (SELECT  [id] FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto)
      union all
      SELECT id, mappedto, cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,'activee' as status,getdate() as entrydate FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto
      ) X where x.id in
      (
      select id from ibehotels.dbo.tab_hotels A
      inner join Ibedb.dbo.tab_masterCities B on a.citycode=b.CityCode and a.source=b.source
      where 
      b.cityname like 'Zurich%' and
      not b.source in
      ('AMADEUS',
      'ASIAN')
      )
END



BEGIN
      select id, mappedto, '7063' as cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,status,entrydate
      into [update20130723].[dbo].[tab_CitySampleMappings_HongKong_test]
      from
      (
      select id,mappedto,'7063' as cityid,'MATCH' as MAPCHECKSTATUS,'FUZZY_SYSTEM' as checkbyuser,getdate() as MAPCHECKTIME,'active' as status,getdate() as entrydate
      from [FuzzyMap2].[dbo].[FuzzyHongKong2$]
      where not id in (SELECT  [id] FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto)
      union all
      SELECT id, mappedto, cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,'activee' as status,getdate() as entrydate FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto
      ) X where x.id in
      (
      select id from ibehotels.dbo.tab_hotels A
      inner join Ibedb.dbo.tab_masterCities B on a.citycode=b.CityCode and a.source=b.source
      where 
      (b.cityname like 'Hong Kong%' or b.cityname like 'HongKong%') and
      not b.source in
      ('AMADEUS',
      'ASIAN')
      )
END



BEGIN
      select id, mappedto, '7652' as cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,status,entrydate
      into [update20130723].[dbo].[tab_CitySampleMappings_Phuket_test]
      from
      (
      select id,mappedto,'7652' as cityid,'MATCH' as MAPCHECKSTATUS,'FUZZY_SYSTEM' as checkbyuser,getdate() as MAPCHECKTIME,'active' as status,getdate() as entrydate
      from [FuzzyMap2].[dbo].[FuzzyPhuket2$]
      where not id in (SELECT  [id] FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto)
      union all
      SELECT id, mappedto, cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,'activee' as status,getdate() as entrydate FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto
      ) X where x.id in
      (
      select id from ibehotels.dbo.tab_hotels A
      inner join Ibedb.dbo.tab_masterCities B on a.citycode=b.CityCode and a.source=b.source
      where 
      b.cityname like 'Phuket%' and
      not b.source in
      ('AMADEUS',
      'ASIAN')
      )
END


BEGIN
      select id, mappedto, '3271' as cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,status,entrydate
      into [update20130723].[dbo].[tab_CitySampleMappings_Lucerne_test]
      from
      (
      select id,mappedto,'3271' as cityid,'MATCH' as MAPCHECKSTATUS,'FUZZY_SYSTEM' as checkbyuser,getdate() as MAPCHECKTIME,'active' as status,getdate() as entrydate
      from [FuzzyMap2].[dbo].[FuzzyLucerne2$]
      where not id in (SELECT  [id] FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto)
      union all
      SELECT id, mappedto, cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,'activee' as status,getdate() as entrydate FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto
      ) X where x.id in
      (
      select id from ibehotels.dbo.tab_hotels A
      inner join Ibedb.dbo.tab_masterCities B on a.citycode=b.CityCode and a.source=b.source
      where 
      b.cityname like 'Lucerne%' and
      not b.source in
      ('AMADEUS',
      'ASIAN')
      )
END



BEGIN
      select id, mappedto, '9116' as cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,status,entrydate
      into [update20130723].[dbo].[tab_CitySampleMappings_NewYork_test]
      from
      (
      select id,mappedto,'9116' as cityid,'MATCH' as MAPCHECKSTATUS,'FUZZY_SYSTEM' as checkbyuser,getdate() as MAPCHECKTIME,'active' as status,getdate() as entrydate
      from [FuzzyMap2].[dbo].[FuzzyNewYork2$]
      where not id in (SELECT  [id] FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto)
      union all
      SELECT id, mappedto, cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,'activee' as status,getdate() as entrydate FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto
      ) X where x.id in
      (
      select id from ibehotels.dbo.tab_hotels A
      inner join Ibedb.dbo.tab_masterCities B on a.citycode=b.CityCode and a.source=b.source
      where 
      b.cityname like 'New York%' and
      not b.source in
      ('AMADEUS',
      'ASIAN')
      )
END



BEGIN
      select id, mappedto, '2585' as cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,status,entrydate
      into [update20130723].[dbo].[tab_CitySampleMappings_Istanbul_test]
      from
      (
      select id,mappedto,'2585' as cityid,'MATCH' as MAPCHECKSTATUS,'FUZZY_SYSTEM' as checkbyuser,getdate() as MAPCHECKTIME,'active' as status,getdate() as entrydate
      from [FuzzyMap2].[dbo].[FuzzyIstanbul2$]
      where not id in (SELECT  [id] FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto)
      union all
      SELECT id, mappedto, cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,'activee' as status,getdate() as entrydate FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto
      ) X where x.id in
      (
      select id from ibehotels.dbo.tab_hotels A
      inner join Ibedb.dbo.tab_masterCities B on a.citycode=b.CityCode and a.source=b.source
      where 
      b.cityname like 'Istanbul%' and
      not b.source in
      ('AMADEUS',
      'ASIAN')
      )
END



BEGIN
      select id, mappedto, '173' as cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,status,entrydate
      into [update20130723].[dbo].[tab_CitySampleMappings_Amsterdam_test]
      from
      (
      select id,mappedto,'173' as cityid,'MATCH' as MAPCHECKSTATUS,'FUZZY_SYSTEM' as checkbyuser,getdate() as MAPCHECKTIME,'active' as status,getdate() as entrydate
      from [FuzzyMap2].[dbo].[FuzzyAmsterdam2$]
      where not id in (SELECT  [id] FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto)
      union all
      SELECT id, mappedto, cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,'activee' as status,getdate() as entrydate FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto
      ) X where x.id in
      (
      select id from ibehotels.dbo.tab_hotels A
      inner join Ibedb.dbo.tab_masterCities B on a.citycode=b.CityCode and a.source=b.source
      where 
      b.cityname like 'Amsterdam%' and
      not b.source in
      ('AMADEUS',
      'ASIAN')
      )
END
go


-----===============================================================================

[IbeCitySampleMappings].[dbo].[usp_removeDuplicateHotelMapping] 'tab_CitySampleMappings_Paris_test', 'update20130723B'
go
[IbeCitySampleMappings].[dbo].[usp_removeDuplicateHotelMapping] 'tab_CitySampleMappings_Kualalumpur_test', 'update20130723B'
go
[IbeCitySampleMappings].[dbo].[usp_removeDuplicateHotelMapping] 'tab_CitySampleMappings_Pattaya_test', 'update20130723B'
go
[IbeCitySampleMappings].[dbo].[usp_removeDuplicateHotelMapping] 'tab_CitySampleMappings_Bangkok_test', 'update20130723B'
go
[IbeCitySampleMappings].[dbo].[usp_removeDuplicateHotelMapping] 'tab_CitySampleMappings_Milan_test', 'update20130723B'
go
[IbeCitySampleMappings].[dbo].[usp_removeDuplicateHotelMapping] 'tab_CitySampleMappings_London_test', 'update20130723B'
go
[IbeCitySampleMappings].[dbo].[usp_removeDuplicateHotelMapping] 'tab_CitySampleMappings_Zurich_test', 'update20130723B'
go
[IbeCitySampleMappings].[dbo].[usp_removeDuplicateHotelMapping] 'tab_CitySampleMappings_HongKong_test', 'update20130723B'
go




[IbeCitySampleMappings].[dbo].[usp_removeDuplicateHotelMapping] 'tab_CitySampleMappings_Phuket_test', 'update20130723B'
go
[IbeCitySampleMappings].[dbo].[usp_removeDuplicateHotelMapping] 'tab_CitySampleMappings_Lucerne_test', 'update20130723B'
go
[IbeCitySampleMappings].[dbo].[usp_removeDuplicateHotelMapping] 'tab_CitySampleMappings_NewYork_test', 'update20130723B'
go
[IbeCitySampleMappings].[dbo].[usp_removeDuplicateHotelMapping] 'tab_CitySampleMappings_Istanbul_test', 'update20130723B'
go
[IbeCitySampleMappings].[dbo].[usp_removeDuplicateHotelMapping] 'tab_CitySampleMappings_Amsterdam_test', 'update20130723B'
go








----------------------------

/*




CREATE procedure [dbo].[usp_RemoveDuplicateGroups]   
as   
update update20130723A.dbo.tab_CitySampleMappings_Lucerne_test  
set mappedto=b.mappedto   
from update20130723A.dbo.tab_CitySampleMappings_Lucerne_test a inner join    
(select id,mappedto from update20130723A.dbo.tab_CitySampleMappings_Lucerne_test   
where id in   
(   
select mappedto from update20130723A.dbo.tab_CitySampleMappings_Lucerne_test   
 ) and id>mappedto   
 ) b on a.mappedto=b.id and a.id<>b.mappedto   
     
update update20130723A.dbo.tab_CitySampleMappings_Lucerne_test   
set mappedto=id   
where id in   
(    
select mappedto    
from update20130723A.dbo.tab_CitySampleMappings_Lucerne_test   
where id in   
(   
select mappedto from update20130723A.dbo.tab_CitySampleMappings_Lucerne_test   
 ) and id>mappedto)


*/




at 1 comment: