Loading ...
Sorry, an error occurred while loading the content.

Re:Query regarding deleting duplicates in table....

Expand Messages
  • Damhuis Anton
    Dear Arun Since the employees table does not seem to have something unique to tell one row apart from another, you cannot uniquely delete one row and not the
    Message 1 of 20 , Mar 16, 2010
    • 0 Attachment
      Dear Arun

      Since the employees table does not seem to have something unique to tell one row apart from another, you cannot uniquely delete one row and not the other.
      So you will need to add one additional column, and enter unique values in it. This could either be a int.
      And then you can write some delete statement, which can delete the data as required.

      Assuming you want to keep the last entry made, as it should be more up to date, you could have a query as follows:

      Delete from emplyee where DeleteID in (
      Select DeleteID from employee where Name = "John" and Surname="Doe" and DeleteID <> (Select Max(DeleteID) from employee where Name = "John" and Surname="Doe"));

      DeleteID is the column added to uniquely identify each row.
      Make sure the incremental value of DeleteID is as expected. (Higher ID = later entries)
      The inner, inner select gets the ID which muct be kept.
      The inner select returns a list of DeleteIDs excluding the one we want to keep, from the criteria specified.
      The outer statements deletes the data.

      Obviously test this first on a temp database, making sure the results are what you expected.

      Hope this helps.

      Regards
      Anton


      ________________________________
      Please note: This email and its contents are subject to the disclaimer as displayed at the following link: http://www.investmentsolutions.co.za/EmailLegalNotice.htm. Should you not have web access, send an email to legalnotice@... and a copy of this disclaimer will be sent to you.


      [Non-text portions of this message have been removed]
    • Noman Aftab
      Hi, I have a table MARKET_PRICES with columns: SYMBOL, PRICE_DATE and PRICE. There may be days on which the prices don t get upload, simply because they havn t
      Message 2 of 20 , Mar 24, 2010
      • 0 Attachment
        Hi,
        I have a table MARKET_PRICES with columns: SYMBOL, PRICE_DATE and PRICE.
        There may be days on which the prices don't get upload, simply because they havn't changed for the day.
        Therefore data in the table can be like this:
        01/Mar/2010, 0.45
        02/Mar/2010, 0.44
        05/Mar/2010, 0.43
        08/Mar/2010, 0.40
        09/Mar/2010, 0.41
        13/Mar/2010, 0.46
        17/Mar/2010, 0.44

        I need a query which when given from and to dates, shows the prices for each day, if price of a particular day is not uploaded then the price of the previous day should be shown for that day, for e.g. giving dates 01/Mar/2010 to 20/mar/2010 should yield the following results:

        01/Mar/2010, 0.45
        02/Mar/2010, 0.44
        03/Mar/2010, 0.44
        04/Mar/2010, 0.44
        05/Mar/2010, 0.43
        06/Mar/2010, 0.43
        07/Mar/2010, 0.43
        08/Mar/2010, 0.40
        09/Mar/2010, 0.41
        10/Mar/2010, 0.41
        11/Mar/2010, 0.41
        12/Mar/2010, 0.41
        13/Mar/2010, 0.46
        14/Mar/2010, 0.46
        15/Mar/2010, 0.46
        16/Mar/2010, 0.46
        17/Mar/2010, 0.44
        18/Mar/2010, 0.44
        19/Mar/2010, 0.44
        20/Mar/2010, 0.44


        Best Wishes,
        Noman Aftab


        http://www.corpus.quran.com/wordbyword.jsp
        http://www.jalandhari.qsh.eu


        Your Mail works best with the New Yahoo Optimized IE8. Get it NOW! http://downloads.yahoo.com/in/internetexplorer/

        [Non-text portions of this message have been removed]
      • John Warner
        You will at least need a table with all dates in one of the columns otherwise there is no way to even show zero for the missing dates. Then you do an outer
        Message 3 of 20 , Mar 24, 2010
        • 0 Attachment
          You will at least need a table with all dates in one of the columns
          otherwise there is no way to even show zero for the missing dates. Then
          you do an outer join to the date table. Not sure how you get data into the
          other two columns, I guess a case/else structure.

          John Warner


          > -----Original Message-----
          > From: SQLQueriesNoCode@yahoogroups.com
          > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Noman Aftab
          > Sent: Wednesday, March 24, 2010 4:04 PM
          > To: SQLQueriesNoCode@yahoogroups.com
          > Cc: faraz.faheem@...
          > Subject: [SQLQueriesNoCode] Missing record query
          >
          > Hi,
          > I have a table MARKET_PRICES with columns: SYMBOL, PRICE_DATE and
          > PRICE.
          > There may be days on which the prices don't get upload, simply because
          they
          > havn't changed for the day.
          > Therefore data in the table can be like this:
          > 01/Mar/2010, 0.45
          > 02/Mar/2010, 0.44
          > 05/Mar/2010, 0.43
          > 08/Mar/2010, 0.40
          > 09/Mar/2010, 0.41
          > 13/Mar/2010, 0.46
          > 17/Mar/2010, 0.44
          >
          > I need a query which when given from and to dates, shows the prices for
          each
          > day, if price of a particular day is not uploaded then the price of the
          previous
          > day should be shown for that day, for e.g. giving dates 01/Mar/2010 to
          > 20/mar/2010 should yield the following results:
          >
          > 01/Mar/2010, 0.45
          > 02/Mar/2010, 0.44
          > 03/Mar/2010, 0.44
          > 04/Mar/2010, 0.44
          > 05/Mar/2010, 0.43
          > 06/Mar/2010, 0.43
          > 07/Mar/2010, 0.43
          > 08/Mar/2010, 0.40
          > 09/Mar/2010, 0.41
          > 10/Mar/2010, 0.41
          > 11/Mar/2010, 0.41
          > 12/Mar/2010, 0.41
          > 13/Mar/2010, 0.46
          > 14/Mar/2010, 0.46
          > 15/Mar/2010, 0.46
          > 16/Mar/2010, 0.46
          > 17/Mar/2010, 0.44
          > 18/Mar/2010, 0.44
          > 19/Mar/2010, 0.44
          > 20/Mar/2010, 0.44
          >
          >
          > Best Wishes,
          > Noman Aftab
          >
          >
          > http://www.corpus.quran.com/wordbyword.jsp
          > http://www.jalandhari.qsh.eu
          >
          >
          > Your Mail works best with the New Yahoo Optimized IE8. Get it NOW!
          > http://downloads.yahoo.com/in/internetexplorer/
          >
          > [Non-text portions of this message have been removed]
          >
          >
          >
          > ------------------------------------
          >
          > Yahoo! Groups Links
          >
          >
          >
        • Paul Livengood
          There are many different ways to do this and the correct would to use is based on your business requirements (speed, how often used, how much data to return,
          Message 4 of 20 , Mar 24, 2010
          • 0 Attachment
            There are many different ways to do this and the correct would to use is based on your business requirements (speed, how often used, how much data to return, how called, etc).
            I picked the following method because it uses no temp tables and is written so it can be in a table function if needed.  I know it uses many sub-queries, but if your select list is low (a year of less would be my guess) then this will run without issue. 

            HTH
            Paul

            -------------CODE
            --this is to recreate your tableDECLARE@MARKET_PRICES TABLE(PRICE_DATE DATETIME,PRICE NUMERIC(18,2));INSERTINTO@MARKET_PRICES SELECT'01/Mar/2010',0.45;INSERTINTO@MARKET_PRICES SELECT'02/Mar/2010',0.44;INSERTINTO@MARKET_PRICES SELECT'05/Mar/2010',0.43;INSERTINTO@MARKET_PRICES SELECT'08/Mar/2010',0.40;INSERTINTO@MARKET_PRICES SELECT'09/Mar/2010',0.41;INSERTINTO@MARKET_PRICES SELECT'13/Mar/2010',0.46;INSERTINTO@MARKET_PRICES SELECT'17/Mar/2010',0.44;WITH--the with statement creates a table with a list of numbers...that is all (taken from an old book i have)G0 AS(SELECT1 ASC UNION ALLSELECT1),--2 rowsG1 AS(SELECT1 ASC FROMG0 ASA,G0 ASB),--4 rowsG2 AS(SELECT1 ASC FROMG1 ASA,G1 ASB),--16 rowsG3 AS(SELECT1 ASC FROMG2 ASA,G2 ASB),--256 rowsG4 AS(SELECT1 ASC FROMG3 ASA,G3 ASB),--65536 rowsnum AS(SELECTROW_NUMBER()OVER(ORDERBYC)ASN FROMG4)SELECTa.TempDate DisplayDate,(SELECTMAX(aa.PRICE_DATE)FROM@MARKET_PRICES aa WHEREaa.PRICE_DATE
            <=a.TempDate)PRICEDATE,(SELECTzz.PRICE FROM@MARKET_PRICES zz WHEREzz.PRICE_DATE =(SELECTMAX(aa.PRICE_DATE)FROM@MARKET_PRICES aa WHEREaa.PRICE_DATE <=a.TempDate))PRICEFROM(SELECTCAST('2010-03-01'ASDATETIME)+N-1 ASTempDate --start dateFROMnumWHEREN BETWEEN1 AND31)a --number of days you want returnedLEFTJOIN@MARKET_PRICES bONa.TempDate =b.PRICE_DATE




            ________________________________
            From: Noman Aftab <noman17pk@...>
            To: SQLQueriesNoCode@yahoogroups.com
            Cc: faraz.faheem@...
            Sent: Wed, March 24, 2010 2:04:15 PM
            Subject: [SQLQueriesNoCode] Missing record query

             
            Hi,
            I have a table MARKET_PRICES with columns: SYMBOL, PRICE_DATE and PRICE.
            There may be days on which the prices don't get upload, simply because they havn't changed for the day.
            Therefore data in the table can be like this:
            01/Mar/2010, 0.45
            02/Mar/2010, 0.44
            05/Mar/2010, 0.43
            08/Mar/2010, 0.40
            09/Mar/2010, 0.41
            13/Mar/2010, 0.46
            17/Mar/2010, 0.44

            I need a query which when given from and to dates, shows the prices for each day, if price of a particular day is not uploaded then the price of the previous day should be shown for that day, for e.g. giving dates 01/Mar/2010 to 20/mar/2010 should yield the following results:

            01/Mar/2010, 0.45
            02/Mar/2010, 0.44
            03/Mar/2010, 0.44
            04/Mar/2010, 0.44
            05/Mar/2010, 0.43
            06/Mar/2010, 0.43
            07/Mar/2010, 0.43
            08/Mar/2010, 0.40
            09/Mar/2010, 0.41
            10/Mar/2010, 0.41
            11/Mar/2010, 0.41
            12/Mar/2010, 0.41
            13/Mar/2010, 0.46
            14/Mar/2010, 0.46
            15/Mar/2010, 0.46
            16/Mar/2010, 0.46
            17/Mar/2010, 0.44
            18/Mar/2010, 0.44
            19/Mar/2010, 0.44
            20/Mar/2010, 0.44

            Best Wishes,
            Noman Aftab

            http://www.corpus. quran.com/ wordbyword. jsp
            http://www.jalandhari.qsh.eu

            Your Mail works best with the New Yahoo Optimized IE8. Get it NOW! http://downloads. yahoo.com/ in/internetexplo rer/

            [Non-text portions of this message have been removed]




            [Non-text portions of this message have been removed]
          • John Warner
            Paul the list software screwed up your post so that it is extremely difficult to read. If it isn t too much trouble (and you still have the original in your
            Message 5 of 20 , Mar 25, 2010
            • 0 Attachment
              Paul the list software screwed up your post so that it is extremely difficult to read. If it isn't too much trouble (and you still have the original in your sent items box) could you repost but before you do uncheck html?

              Thanks and if it is gone don't worry about it.

              John Warner


              > -----Original Message-----
              > From: SQLQueriesNoCode@yahoogroups.com
              > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Paul Livengood
              > Sent: Wednesday, March 24, 2010 9:32 PM
              > To: SQLQueriesNoCode@yahoogroups.com
              > Subject: Re: [SQLQueriesNoCode] Missing record query
              >
              > There are many different ways to do this and the correct would to use is based
              > on your business requirements (speed, how often used, how much data to
              > return, how called, etc).
              > I picked the following method because it uses no temp tables and is written so
              > it can be in a table function if needed. I know it uses many sub-queries, but if
              > your select list is low (a year of less would be my guess) then this will run
              > without issue.
              >
              >
              > HTH
              > Paul
              >
              > -------------CODE
              > --this is to recreate your tableDECLARE@MARKET_PRICES
              > TABLE(PRICE_DATE DATETIME,PRICE
              > NUMERIC(18,2));INSERTINTO@MARKET_PRICES
              > SELECT'01/Mar/2010',0.45;INSERTINTO@MARKET_PRICES
              > SELECT'02/Mar/2010',0.44;INSERTINTO@MARKET_PRICES
              > SELECT'05/Mar/2010',0.43;INSERTINTO@MARKET_PRICES
              > SELECT'08/Mar/2010',0.40;INSERTINTO@MARKET_PRICES
              > SELECT'09/Mar/2010',0.41;INSERTINTO@MARKET_PRICES
              > SELECT'13/Mar/2010',0.46;INSERTINTO@MARKET_PRICES
              > SELECT'17/Mar/2010',0.44;WITH--the with statement creates a table with a list
              > of numbers...that is all (taken from an old book i have)G0 AS(SELECT1 ASC
              > UNION ALLSELECT1),--2 rowsG1 AS(SELECT1 ASC FROMG0 ASA,G0
              > ASB),--4 rowsG2 AS(SELECT1 ASC FROMG1 ASA,G1 ASB),--16 rowsG3
              > AS(SELECT1 ASC FROMG2 ASA,G2 ASB),--256 rowsG4 AS(SELECT1 ASC
              > FROMG3 ASA,G3 ASB),--65536 rowsnum
              > AS(SELECTROW_NUMBER()OVER(ORDERBYC)ASN
              > FROMG4)SELECTa.TempDate
              > DisplayDate,(SELECTMAX(aa.PRICE_DATE)FROM@MARKET_PRICES aa
              > WHEREaa.PRICE_DATE
              > <=a.TempDate)PRICEDATE,(SELECTzz.PRICE FROM@MARKET_PRICES
              > zz WHEREzz.PRICE_DATE
              > =(SELECTMAX(aa.PRICE_DATE)FROM@MARKET_PRICES aa
              > WHEREaa.PRICE_DATE <=a.TempDate))PRICEFROM(SELECTCAST('2010-
              > 03-01'ASDATETIME)+N-1 ASTempDate --start dateFROMnumWHEREN
              > BETWEEN1 AND31)a --number of days you want
              > returnedLEFTJOIN@MARKET_PRICES bONa.TempDate =b.PRICE_DATE
              >
              >
              >
              >
              > ________________________________
              > From: Noman Aftab <noman17pk@...>
              > To: SQLQueriesNoCode@yahoogroups.com
              > Cc: faraz.faheem@...
              > Sent: Wed, March 24, 2010 2:04:15 PM
              > Subject: [SQLQueriesNoCode] Missing record query
              >
              >
              > Hi,
              > I have a table MARKET_PRICES with columns: SYMBOL, PRICE_DATE and
              > PRICE.
              > There may be days on which the prices don't get upload, simply because they
              > havn't changed for the day.
              > Therefore data in the table can be like this:
              > 01/Mar/2010, 0.45
              > 02/Mar/2010, 0.44
              > 05/Mar/2010, 0.43
              > 08/Mar/2010, 0.40
              > 09/Mar/2010, 0.41
              > 13/Mar/2010, 0.46
              > 17/Mar/2010, 0.44
              >
              > I need a query which when given from and to dates, shows the prices for each
              > day, if price of a particular day is not uploaded then the price of the previous
              > day should be shown for that day, for e.g. giving dates 01/Mar/2010 to
              > 20/mar/2010 should yield the following results:
              >
              > 01/Mar/2010, 0.45
              > 02/Mar/2010, 0.44
              > 03/Mar/2010, 0.44
              > 04/Mar/2010, 0.44
              > 05/Mar/2010, 0.43
              > 06/Mar/2010, 0.43
              > 07/Mar/2010, 0.43
              > 08/Mar/2010, 0.40
              > 09/Mar/2010, 0.41
              > 10/Mar/2010, 0.41
              > 11/Mar/2010, 0.41
              > 12/Mar/2010, 0.41
              > 13/Mar/2010, 0.46
              > 14/Mar/2010, 0.46
              > 15/Mar/2010, 0.46
              > 16/Mar/2010, 0.46
              > 17/Mar/2010, 0.44
              > 18/Mar/2010, 0.44
              > 19/Mar/2010, 0.44
              > 20/Mar/2010, 0.44
              >
              > Best Wishes,
              > Noman Aftab
              >
              > http://www.corpus. quran.com/ wordbyword. jsp
              > http://www.jalandhari.qsh.eu
              >
              > Your Mail works best with the New Yahoo Optimized IE8. Get it NOW!
              > http://downloads. yahoo.com/ in/internetexplo rer/
              >
              > [Non-text portions of this message have been removed]
              >
              >
              >
              >
              > [Non-text portions of this message have been removed]
              >
              >
              >
              > ------------------------------------
              >
              > Yahoo! Groups Links
              >
              >
              >
            • Noman Aftab
              Yeah, I also tried to make it run-able but was unable to do so. Best Wishes, Noman Aftab ________________________________ From: John Warner
              Message 6 of 20 , Mar 25, 2010
              • 0 Attachment
                Yeah, I also tried to make it run-able but was unable to do so.
                Best Wishes,
                Noman Aftab







                ________________________________
                From: John Warner <john@...>
                To: SQLQueriesNoCode@yahoogroups.com
                Sent: Thu, 25 March, 2010 1:40:51 PM
                Subject: RE: [SQLQueriesNoCode] Missing record query


                Paul the list software screwed up your post so that it is extremely difficult to read. If it isn't too much trouble (and you still have the original in your sent items box) could you repost but before you do uncheck html?

                Thanks and if it is gone don't worry about it.

                John Warner

                > -----Original Message-----
                > From: SQLQueriesNoCode@ yahoogroups. com
                > [mailto:SQLQueriesNoCode@ yahoogroups. com] On Behalf Of Paul Livengood
                > Sent: Wednesday, March 24, 2010 9:32 PM
                > To: SQLQueriesNoCode@ yahoogroups. com
                > Subject: Re: [SQLQueriesNoCode] Missing record query
                >
                > There are many different ways to do this and the correct would to use is based
                > on your business requirements (speed, how often used, how much data to
                > return, how called, etc).
                > I picked the following method because it uses no temp tables and is written so
                > it can be in a table function if needed. I know it uses many sub-queries, but if
                > your select list is low (a year of less would be my guess) then this will run
                > without issue.
                >
                >
                > HTH
                > Paul
                >
                > ------------ -CODE
                > --this is to recreate your tableDECLARE@ MARKET_PRICES
                > TABLE(PRICE_ DATE DATETIME,PRICE
                > NUMERIC(18,2) );INSERTINTO@ MARKET_PRICES
                > SELECT'01/Mar/ 2010',0.45; INSERTINTO@ MARKET_PRICES
                > SELECT'02/Mar/ 2010',0.44; INSERTINTO@ MARKET_PRICES
                > SELECT'05/Mar/ 2010',0.43; INSERTINTO@ MARKET_PRICES
                > SELECT'08/Mar/ 2010',0.40; INSERTINTO@ MARKET_PRICES
                > SELECT'09/Mar/ 2010',0.41; INSERTINTO@ MARKET_PRICES
                > SELECT'13/Mar/ 2010',0.46; INSERTINTO@ MARKET_PRICES
                > SELECT'17/Mar/ 2010',0.44; WITH--the with statement creates a table with a list
                > of numbers...that is all (taken from an old book i have)G0 AS(SELECT1 ASC
                > UNION ALLSELECT1), --2 rowsG1 AS(SELECT1 ASC FROMG0 ASA,G0
                > ASB),--4 rowsG2 AS(SELECT1 ASC FROMG1 ASA,G1 ASB),--16 rowsG3
                > AS(SELECT1 ASC FROMG2 ASA,G2 ASB),--256 rowsG4 AS(SELECT1 ASC
                > FROMG3 ASA,G3 ASB),--65536 rowsnum
                > AS(SELECTROW_ NUMBER()OVER( ORDERBYC) ASN
                > FROMG4)SELECTa. TempDate
                > DisplayDate, (SELECTMAX( aa.PRICE_ DATE)FROM@ MARKET_PRICES aa
                > WHEREaa.PRICE_ DATE
                > <=a.TempDate) PRICEDATE, (SELECTzz. PRICE FROM@MARKET_ PRICES
                > zz WHEREzz.PRICE_ DATE
                > =(SELECTMAX( aa.PRICE_ DATE)FROM@ MARKET_PRICES aa
                > WHEREaa.PRICE_ DATE <=a.TempDate) )PRICEFROM( SELECTCAST( '2010-
                > 03-01'ASDATETIME) +N-1 ASTempDate --start dateFROMnumWHEREN
                > BETWEEN1 AND31)a --number of days you want
                > returnedLEFTJOIN@ MARKET_PRICES bONa.TempDate =b.PRICE_DATE
                >
                >
                >
                >
                > ____________ _________ _________ __
                > From: Noman Aftab <noman17pk@yahoo. com>
                > To: SQLQueriesNoCode@ yahoogroups. com
                > Cc: faraz.faheem@ hotmail.com
                > Sent: Wed, March 24, 2010 2:04:15 PM
                > Subject: [SQLQueriesNoCode] Missing record query
                >
                >
                > Hi,
                > I have a table MARKET_PRICES with columns: SYMBOL, PRICE_DATE and
                > PRICE.
                > There may be days on which the prices don't get upload, simply because they
                > havn't changed for the day.
                > Therefore data in the table can be like this:
                > 01/Mar/2010, 0.45
                > 02/Mar/2010, 0.44
                > 05/Mar/2010, 0.43
                > 08/Mar/2010, 0.40
                > 09/Mar/2010, 0.41
                > 13/Mar/2010, 0.46
                > 17/Mar/2010, 0.44
                >
                > I need a query which when given from and to dates, shows the prices for each
                > day, if price of a particular day is not uploaded then the price of the previous
                > day should be shown for that day, for e.g. giving dates 01/Mar/2010 to
                > 20/mar/2010 should yield the following results:
                >
                > 01/Mar/2010, 0.45
                > 02/Mar/2010, 0.44
                > 03/Mar/2010, 0.44
                > 04/Mar/2010, 0.44
                > 05/Mar/2010, 0.43
                > 06/Mar/2010, 0.43
                > 07/Mar/2010, 0.43
                > 08/Mar/2010, 0.40
                > 09/Mar/2010, 0.41
                > 10/Mar/2010, 0.41
                > 11/Mar/2010, 0.41
                > 12/Mar/2010, 0.41
                > 13/Mar/2010, 0.46
                > 14/Mar/2010, 0.46
                > 15/Mar/2010, 0.46
                > 16/Mar/2010, 0.46
                > 17/Mar/2010, 0.44
                > 18/Mar/2010, 0.44
                > 19/Mar/2010, 0.44
                > 20/Mar/2010, 0.44
                >
                > Best Wishes,
                > Noman Aftab
                >
                > http://www.corpus. quran.com/ wordbyword. jsp
                > http://www.jalandha ri.qsh.eu
                >
                > Your Mail works best with the New Yahoo Optimized IE8. Get it NOW!
                > http://downloads. yahoo.com/ in/internetexplo rer/
                >
                > [Non-text portions of this message have been removed]
                >
                >
                >
                >
                > [Non-text portions of this message have been removed]
                >
                >
                >
                > ------------ --------- --------- ------
                >
                > Yahoo! Groups Links
                >
                >
                >





                The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/

                [Non-text portions of this message have been removed]
              • p_livengood
                sorry about that....is this better? ... --this is to recreate your table DECLARE @MARKET_PRICES TABLE ( PRICE_DATE DATETIME, PRICE NUMERIC(18,2)); INSERT
                Message 7 of 20 , Mar 25, 2010
                • 0 Attachment
                  sorry about that....is this better?


                  ------CODE
                  --this is to recreate your table
                  DECLARE @MARKET_PRICES TABLE (
                  PRICE_DATE DATETIME,
                  PRICE NUMERIC(18,2));

                  INSERT INTO @MARKET_PRICES SELECT '01/Mar/2010', 0.45;
                  INSERT INTO @MARKET_PRICES SELECT '02/Mar/2010', 0.44;
                  INSERT INTO @MARKET_PRICES SELECT '05/Mar/2010', 0.43;
                  INSERT INTO @MARKET_PRICES SELECT '08/Mar/2010', 0.40;
                  INSERT INTO @MARKET_PRICES SELECT '09/Mar/2010', 0.41;
                  INSERT INTO @MARKET_PRICES SELECT '13/Mar/2010', 0.46;
                  INSERT INTO @MARKET_PRICES SELECT '17/Mar/2010', 0.44;

                  WITH --the with statement creates a table with a list of numbers...that is all (taken from an old book i have)
                  G0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows
                  G1 AS (SELECT 1 AS C FROM G0 AS A, G0 AS B),--4 rows
                  G2 AS (SELECT 1 AS C FROM G1 AS A, G1 AS B),--16 rows
                  G3 AS (SELECT 1 AS C FROM G2 AS A, G2 AS B),--256 rows
                  G4 AS (SELECT 1 AS C FROM G3 AS A, G3 AS B),--65536 rows
                  num AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS N FROM G4)

                  SELECT a.TempDate DisplayDate,
                  (SELECT MAX(aa.PRICE_DATE) FROM @MARKET_PRICES aa WHERE aa.PRICE_DATE <= a.TempDate) PRICEDATE,
                  (SELECT zz.PRICE FROM @MARKET_PRICES zz WHERE zz.PRICE_DATE = (SELECT MAX(aa.PRICE_DATE) FROM @MARKET_PRICES aa WHERE aa.PRICE_DATE <= a.TempDate)) PRICE
                  FROM (SELECT CAST('2010-03-01' AS DATETIME) + N-1 AS TempDate --start date
                  FROM num
                  WHERE N BETWEEN 1 AND 31) a --number of days you want returned
                  LEFT JOIN @MARKET_PRICES b
                  ON a.TempDate = b.PRICE_DATE



                  Paul

                  --- In SQLQueriesNoCode@yahoogroups.com, Noman Aftab <noman17pk@...> wrote:
                  >
                  > Yeah, I also tried to make it run-able but was unable to do so.
                  > Best Wishes,
                  > Noman Aftab
                  >
                  >
                  >
                  >
                  >
                  >
                  >
                  > ________________________________
                  > From: John Warner <john@...>
                  > To: SQLQueriesNoCode@yahoogroups.com
                  > Sent: Thu, 25 March, 2010 1:40:51 PM
                  > Subject: RE: [SQLQueriesNoCode] Missing record query
                  >
                  >
                  > Paul the list software screwed up your post so that it is extremely difficult to read. If it isn't too much trouble (and you still have the original in your sent items box) could you repost but before you do uncheck html?
                  >
                  > Thanks and if it is gone don't worry about it.
                  >
                  > John Warner
                  >
                  > > -----Original Message-----
                  > > From: SQLQueriesNoCode@ yahoogroups. com
                  > > [mailto:SQLQueriesNoCode@ yahoogroups. com] On Behalf Of Paul Livengood
                  > > Sent: Wednesday, March 24, 2010 9:32 PM
                  > > To: SQLQueriesNoCode@ yahoogroups. com
                  > > Subject: Re: [SQLQueriesNoCode] Missing record query
                  > >
                  > > There are many different ways to do this and the correct would to use is based
                  > > on your business requirements (speed, how often used, how much data to
                  > > return, how called, etc).
                  > > I picked the following method because it uses no temp tables and is written so
                  > > it can be in a table function if needed. I know it uses many sub-queries, but if
                  > > your select list is low (a year of less would be my guess) then this will run
                  > > without issue.
                  > >
                  > >
                  > > HTH
                  > > Paul
                  > >
                  > > ------------ -CODE
                  > > --this is to recreate your tableDECLARE@ MARKET_PRICES
                  > > TABLE(PRICE_ DATE DATETIME,PRICE
                  > > NUMERIC(18,2) );INSERTINTO@ MARKET_PRICES
                  > > SELECT'01/Mar/ 2010',0.45; INSERTINTO@ MARKET_PRICES
                  > > SELECT'02/Mar/ 2010',0.44; INSERTINTO@ MARKET_PRICES
                  > > SELECT'05/Mar/ 2010',0.43; INSERTINTO@ MARKET_PRICES
                  > > SELECT'08/Mar/ 2010',0.40; INSERTINTO@ MARKET_PRICES
                  > > SELECT'09/Mar/ 2010',0.41; INSERTINTO@ MARKET_PRICES
                  > > SELECT'13/Mar/ 2010',0.46; INSERTINTO@ MARKET_PRICES
                  > > SELECT'17/Mar/ 2010',0.44; WITH--the with statement creates a table with a list
                  > > of numbers...that is all (taken from an old book i have)G0 AS(SELECT1 ASC
                  > > UNION ALLSELECT1), --2 rowsG1 AS(SELECT1 ASC FROMG0 ASA,G0
                  > > ASB),--4 rowsG2 AS(SELECT1 ASC FROMG1 ASA,G1 ASB),--16 rowsG3
                  > > AS(SELECT1 ASC FROMG2 ASA,G2 ASB),--256 rowsG4 AS(SELECT1 ASC
                  > > FROMG3 ASA,G3 ASB),--65536 rowsnum
                  > > AS(SELECTROW_ NUMBER()OVER( ORDERBYC) ASN
                  > > FROMG4)SELECTa. TempDate
                  > > DisplayDate, (SELECTMAX( aa.PRICE_ DATE)FROM@ MARKET_PRICES aa
                  > > WHEREaa.PRICE_ DATE
                  > > <=a.TempDate) PRICEDATE, (SELECTzz. PRICE FROM@MARKET_ PRICES
                  > > zz WHEREzz.PRICE_ DATE
                  > > =(SELECTMAX( aa.PRICE_ DATE)FROM@ MARKET_PRICES aa
                  > > WHEREaa.PRICE_ DATE <=a.TempDate) )PRICEFROM( SELECTCAST( '2010-
                  > > 03-01'ASDATETIME) +N-1 ASTempDate --start dateFROMnumWHEREN
                  > > BETWEEN1 AND31)a --number of days you want
                  > > returnedLEFTJOIN@ MARKET_PRICES bONa.TempDate =b.PRICE_DATE
                  > >
                  > >
                  > >
                  > >
                  > > ____________ _________ _________ __
                  > > From: Noman Aftab <noman17pk@yahoo. com>
                  > > To: SQLQueriesNoCode@ yahoogroups. com
                  > > Cc: faraz.faheem@ hotmail.com
                  > > Sent: Wed, March 24, 2010 2:04:15 PM
                  > > Subject: [SQLQueriesNoCode] Missing record query
                  > >
                  > >
                  > > Hi,
                  > > I have a table MARKET_PRICES with columns: SYMBOL, PRICE_DATE and
                  > > PRICE.
                  > > There may be days on which the prices don't get upload, simply because they
                  > > havn't changed for the day.
                  > > Therefore data in the table can be like this:
                  > > 01/Mar/2010, 0.45
                  > > 02/Mar/2010, 0.44
                  > > 05/Mar/2010, 0.43
                  > > 08/Mar/2010, 0.40
                  > > 09/Mar/2010, 0.41
                  > > 13/Mar/2010, 0.46
                  > > 17/Mar/2010, 0.44
                  > >
                  > > I need a query which when given from and to dates, shows the prices for each
                  > > day, if price of a particular day is not uploaded then the price of the previous
                  > > day should be shown for that day, for e.g. giving dates 01/Mar/2010 to
                  > > 20/mar/2010 should yield the following results:
                  > >
                  > > 01/Mar/2010, 0.45
                  > > 02/Mar/2010, 0.44
                  > > 03/Mar/2010, 0.44
                  > > 04/Mar/2010, 0.44
                  > > 05/Mar/2010, 0.43
                  > > 06/Mar/2010, 0.43
                  > > 07/Mar/2010, 0.43
                  > > 08/Mar/2010, 0.40
                  > > 09/Mar/2010, 0.41
                  > > 10/Mar/2010, 0.41
                  > > 11/Mar/2010, 0.41
                  > > 12/Mar/2010, 0.41
                  > > 13/Mar/2010, 0.46
                  > > 14/Mar/2010, 0.46
                  > > 15/Mar/2010, 0.46
                  > > 16/Mar/2010, 0.46
                  > > 17/Mar/2010, 0.44
                  > > 18/Mar/2010, 0.44
                  > > 19/Mar/2010, 0.44
                  > > 20/Mar/2010, 0.44
                  > >
                  > > Best Wishes,
                  > > Noman Aftab
                  > >
                  > > http://www.corpus. quran.com/ wordbyword. jsp
                  > > http://www.jalandha ri.qsh.eu
                  > >
                  > > Your Mail works best with the New Yahoo Optimized IE8. Get it NOW!
                  > > http://downloads. yahoo.com/ in/internetexplo rer/
                  > >
                  > > [Non-text portions of this message have been removed]
                  > >
                  > >
                  > >
                  > >
                  > > [Non-text portions of this message have been removed]
                  > >
                  > >
                  > >
                  > > ------------ --------- --------- ------
                  > >
                  > > Yahoo! Groups Links
                  > >
                  > >
                  > >
                  >
                  >
                  >
                  >
                  >
                  > The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/
                  >
                  > [Non-text portions of this message have been removed]
                  >
                • John Warner
                  Much, THANK YOU! John Warner ... is all ... difficult to ... your sent ... use is ... to ... written ... sub-queries, ... will run ... with ... AS(SELECT1 ...
                  Message 8 of 20 , Mar 25, 2010
                  • 0 Attachment
                    Much, THANK YOU!

                    John Warner


                    > -----Original Message-----
                    > From: SQLQueriesNoCode@yahoogroups.com
                    > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of p_livengood
                    > Sent: Thursday, March 25, 2010 10:25 AM
                    > To: SQLQueriesNoCode@yahoogroups.com
                    > Subject: [SQLQueriesNoCode] Re: Missing record query
                    >
                    >
                    >
                    > sorry about that....is this better?
                    >
                    >
                    > ------CODE
                    > --this is to recreate your table
                    > DECLARE @MARKET_PRICES TABLE (
                    > PRICE_DATE DATETIME,
                    > PRICE NUMERIC(18,2));
                    >
                    > INSERT INTO @MARKET_PRICES SELECT '01/Mar/2010', 0.45;
                    > INSERT INTO @MARKET_PRICES SELECT '02/Mar/2010', 0.44;
                    > INSERT INTO @MARKET_PRICES SELECT '05/Mar/2010', 0.43;
                    > INSERT INTO @MARKET_PRICES SELECT '08/Mar/2010', 0.40;
                    > INSERT INTO @MARKET_PRICES SELECT '09/Mar/2010', 0.41;
                    > INSERT INTO @MARKET_PRICES SELECT '13/Mar/2010', 0.46;
                    > INSERT INTO @MARKET_PRICES SELECT '17/Mar/2010', 0.44;
                    >
                    > WITH --the with statement creates a table with a list of numbers...that
                    is all
                    > (taken from an old book i have)
                    > G0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows
                    > G1 AS (SELECT 1 AS C FROM G0 AS A, G0 AS B),--4 rows
                    > G2 AS (SELECT 1 AS C FROM G1 AS A, G1 AS B),--16 rows
                    > G3 AS (SELECT 1 AS C FROM G2 AS A, G2 AS B),--256 rows
                    > G4 AS (SELECT 1 AS C FROM G3 AS A, G3 AS B),--65536 rows
                    > num AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS N
                    > FROM G4)
                    >
                    > SELECT a.TempDate DisplayDate,
                    > (SELECT MAX(aa.PRICE_DATE) FROM @MARKET_PRICES aa
                    > WHERE aa.PRICE_DATE <= a.TempDate) PRICEDATE,
                    > (SELECT zz.PRICE FROM @MARKET_PRICES zz WHERE
                    > zz.PRICE_DATE = (SELECT MAX(aa.PRICE_DATE) FROM
                    > @MARKET_PRICES aa WHERE aa.PRICE_DATE <= a.TempDate)) PRICE
                    > FROM (SELECT CAST('2010-03-01' AS DATETIME) + N-1 AS
                    > TempDate --start date
                    > FROM num
                    > WHERE N BETWEEN 1 AND 31) a --number of days you want
                    > returned
                    > LEFT JOIN @MARKET_PRICES b
                    > ON a.TempDate = b.PRICE_DATE
                    >
                    >
                    >
                    > Paul
                    >
                    > --- In SQLQueriesNoCode@yahoogroups.com, Noman Aftab
                    > <noman17pk@...> wrote:
                    > >
                    > > Yeah, I also tried to make it run-able but was unable to do so.
                    > > Best Wishes,
                    > > Noman Aftab
                    > >
                    > >
                    > >
                    > >
                    > >
                    > >
                    > >
                    > > ________________________________
                    > > From: John Warner <john@...>
                    > > To: SQLQueriesNoCode@yahoogroups.com
                    > > Sent: Thu, 25 March, 2010 1:40:51 PM
                    > > Subject: RE: [SQLQueriesNoCode] Missing record query
                    > >
                    > >
                    > > Paul the list software screwed up your post so that it is extremely
                    difficult to
                    > read. If it isn't too much trouble (and you still have the original in
                    your sent
                    > items box) could you repost but before you do uncheck html?
                    > >
                    > > Thanks and if it is gone don't worry about it.
                    > >
                    > > John Warner
                    > >
                    > > > -----Original Message-----
                    > > > From: SQLQueriesNoCode@ yahoogroups. com
                    > > > [mailto:SQLQueriesNoCode@ yahoogroups. com] On Behalf Of Paul
                    > Livengood
                    > > > Sent: Wednesday, March 24, 2010 9:32 PM
                    > > > To: SQLQueriesNoCode@ yahoogroups. com
                    > > > Subject: Re: [SQLQueriesNoCode] Missing record query
                    > > >
                    > > > There are many different ways to do this and the correct would to
                    use is
                    > based
                    > > > on your business requirements (speed, how often used, how much data
                    to
                    > > > return, how called, etc).
                    > > > I picked the following method because it uses no temp tables and is
                    written
                    > so
                    > > > it can be in a table function if needed. I know it uses many
                    sub-queries,
                    > but if
                    > > > your select list is low (a year of less would be my guess) then this
                    will run
                    > > > without issue.
                    > > >
                    > > >
                    > > > HTH
                    > > > Paul
                    > > >
                    > > > ------------ -CODE
                    > > > --this is to recreate your tableDECLARE@ MARKET_PRICES
                    > > > TABLE(PRICE_ DATE DATETIME,PRICE
                    > > > NUMERIC(18,2) );INSERTINTO@ MARKET_PRICES
                    > > > SELECT'01/Mar/ 2010',0.45; INSERTINTO@ MARKET_PRICES
                    > > > SELECT'02/Mar/ 2010',0.44; INSERTINTO@ MARKET_PRICES
                    > > > SELECT'05/Mar/ 2010',0.43; INSERTINTO@ MARKET_PRICES
                    > > > SELECT'08/Mar/ 2010',0.40; INSERTINTO@ MARKET_PRICES
                    > > > SELECT'09/Mar/ 2010',0.41; INSERTINTO@ MARKET_PRICES
                    > > > SELECT'13/Mar/ 2010',0.46; INSERTINTO@ MARKET_PRICES
                    > > > SELECT'17/Mar/ 2010',0.44; WITH--the with statement creates a table
                    with
                    > a list
                    > > > of numbers...that is all (taken from an old book i have)G0
                    AS(SELECT1
                    > ASC
                    > > > UNION ALLSELECT1), --2 rowsG1 AS(SELECT1 ASC FROMG0 ASA,G0
                    > > > ASB),--4 rowsG2 AS(SELECT1 ASC FROMG1 ASA,G1 ASB),--16 rowsG3
                    > > > AS(SELECT1 ASC FROMG2 ASA,G2 ASB),--256 rowsG4 AS(SELECT1
                    > ASC
                    > > > FROMG3 ASA,G3 ASB),--65536 rowsnum
                    > > > AS(SELECTROW_ NUMBER()OVER( ORDERBYC) ASN
                    > > > FROMG4)SELECTa. TempDate
                    > > > DisplayDate, (SELECTMAX( aa.PRICE_ DATE)FROM@
                    > MARKET_PRICES aa
                    > > > WHEREaa.PRICE_ DATE
                    > > > <=a.TempDate) PRICEDATE, (SELECTzz. PRICE FROM@MARKET_
                    > PRICES
                    > > > zz WHEREzz.PRICE_ DATE
                    > > > =(SELECTMAX( aa.PRICE_ DATE)FROM@ MARKET_PRICES aa
                    > > > WHEREaa.PRICE_ DATE <=a.TempDate) )PRICEFROM( SELECTCAST(
                    > '2010-
                    > > > 03-01'ASDATETIME) +N-1 ASTempDate --start dateFROMnumWHEREN
                    > > > BETWEEN1 AND31)a --number of days you want
                    > > > returnedLEFTJOIN@ MARKET_PRICES bONa.TempDate
                    > =b.PRICE_DATE
                    > > >
                    > > >
                    > > >
                    > > >
                    > > > ____________ _________ _________ __
                    > > > From: Noman Aftab <noman17pk@yahoo. com>
                    > > > To: SQLQueriesNoCode@ yahoogroups. com
                    > > > Cc: faraz.faheem@ hotmail.com
                    > > > Sent: Wed, March 24, 2010 2:04:15 PM
                    > > > Subject: [SQLQueriesNoCode] Missing record query
                    > > >
                    > > >
                    > > > Hi,
                    > > > I have a table MARKET_PRICES with columns: SYMBOL, PRICE_DATE
                    > and
                    > > > PRICE.
                    > > > There may be days on which the prices don't get upload, simply
                    because
                    > they
                    > > > havn't changed for the day.
                    > > > Therefore data in the table can be like this:
                    > > > 01/Mar/2010, 0.45
                    > > > 02/Mar/2010, 0.44
                    > > > 05/Mar/2010, 0.43
                    > > > 08/Mar/2010, 0.40
                    > > > 09/Mar/2010, 0.41
                    > > > 13/Mar/2010, 0.46
                    > > > 17/Mar/2010, 0.44
                    > > >
                    > > > I need a query which when given from and to dates, shows the prices
                    for
                    > each
                    > > > day, if price of a particular day is not uploaded then the price of
                    the
                    > previous
                    > > > day should be shown for that day, for e.g. giving dates 01/Mar/2010
                    to
                    > > > 20/mar/2010 should yield the following results:
                    > > >
                    > > > 01/Mar/2010, 0.45
                    > > > 02/Mar/2010, 0.44
                    > > > 03/Mar/2010, 0.44
                    > > > 04/Mar/2010, 0.44
                    > > > 05/Mar/2010, 0.43
                    > > > 06/Mar/2010, 0.43
                    > > > 07/Mar/2010, 0.43
                    > > > 08/Mar/2010, 0.40
                    > > > 09/Mar/2010, 0.41
                    > > > 10/Mar/2010, 0.41
                    > > > 11/Mar/2010, 0.41
                    > > > 12/Mar/2010, 0.41
                    > > > 13/Mar/2010, 0.46
                    > > > 14/Mar/2010, 0.46
                    > > > 15/Mar/2010, 0.46
                    > > > 16/Mar/2010, 0.46
                    > > > 17/Mar/2010, 0.44
                    > > > 18/Mar/2010, 0.44
                    > > > 19/Mar/2010, 0.44
                    > > > 20/Mar/2010, 0.44
                    > > >
                    > > > Best Wishes,
                    > > > Noman Aftab
                    > > >
                    > > > http://www.corpus. quran.com/ wordbyword. jsp
                    > > > http://www.jalandha ri.qsh.eu
                    > > >
                    > > > Your Mail works best with the New Yahoo Optimized IE8. Get it NOW!
                    > > > http://downloads. yahoo.com/ in/internetexplo rer/
                    > > >
                    > > > [Non-text portions of this message have been removed]
                    > > >
                    > > >
                    > > >
                    > > >
                    > > > [Non-text portions of this message have been removed]
                    > > >
                    > > >
                    > > >
                    > > > ------------ --------- --------- ------
                    > > >
                    > > > Yahoo! Groups Links
                    > > >
                    > > >
                    > > >
                    > >
                    > >
                    > >
                    > >
                    > >
                    > > The INTERNET now has a personality. YOURS! See your Yahoo!
                    > Homepage. http://in.yahoo.com/
                    > >
                    > > [Non-text portions of this message have been removed]
                    > >
                    >
                    >
                    >
                    >
                    > ------------------------------------
                    >
                    > Yahoo! Groups Links
                    >
                    >
                    >
                  • Noman Aftab
                    Thanks matey! Best Wishes, Noman Aftab http://www.corpus.quran.com/wordbyword.jsp http://www.jalandhari.qsh.eu ________________________________ From:
                    Message 9 of 20 , Mar 25, 2010
                    • 0 Attachment
                      Thanks matey!
                      Best Wishes,
                      Noman Aftab


                      http://www.corpus.quran.com/wordbyword.jsp
                      http://www.jalandhari.qsh.eu





                      ________________________________
                      From: p_livengood <p_livengood@...>
                      To: SQLQueriesNoCode@yahoogroups.com
                      Sent: Thu, 25 March, 2010 6:25:15 PM
                      Subject: [SQLQueriesNoCode] Re: Missing record query




                      sorry about that....is this better?

                      ------CODE
                      --this is to recreate your table
                      DECLARE @MARKET_PRICES TABLE (
                      PRICE_DATE DATETIME,
                      PRICE NUMERIC(18,2) );

                      INSERT INTO @MARKET_PRICES SELECT '01/Mar/2010' , 0.45;
                      INSERT INTO @MARKET_PRICES SELECT '02/Mar/2010' , 0.44;
                      INSERT INTO @MARKET_PRICES SELECT '05/Mar/2010' , 0.43;
                      INSERT INTO @MARKET_PRICES SELECT '08/Mar/2010' , 0.40;
                      INSERT INTO @MARKET_PRICES SELECT '09/Mar/2010' , 0.41;
                      INSERT INTO @MARKET_PRICES SELECT '13/Mar/2010' , 0.46;
                      INSERT INTO @MARKET_PRICES SELECT '17/Mar/2010' , 0.44;

                      WITH --the with statement creates a table with a list of numbers...that is all (taken from an old book i have)
                      G0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows
                      G1 AS (SELECT 1 AS C FROM G0 AS A, G0 AS B),--4 rows
                      G2 AS (SELECT 1 AS C FROM G1 AS A, G1 AS B),--16 rows
                      G3 AS (SELECT 1 AS C FROM G2 AS A, G2 AS B),--256 rows
                      G4 AS (SELECT 1 AS C FROM G3 AS A, G3 AS B),--65536 rows
                      num AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS N FROM G4)

                      SELECT a.TempDate DisplayDate,
                      (SELECT MAX(aa.PRICE_ DATE) FROM @MARKET_PRICES aa WHERE aa.PRICE_DATE <= a.TempDate) PRICEDATE,
                      (SELECT zz.PRICE FROM @MARKET_PRICES zz WHERE zz.PRICE_DATE = (SELECT MAX(aa.PRICE_ DATE) FROM @MARKET_PRICES aa WHERE aa.PRICE_DATE <= a.TempDate)) PRICE
                      FROM (SELECT CAST('2010-03- 01' AS DATETIME) + N-1 AS TempDate --start date
                      FROM num
                      WHERE N BETWEEN 1 AND 31) a --number of days you want returned
                      LEFT JOIN @MARKET_PRICES b
                      ON a.TempDate = b.PRICE_DATE

                      Paul

                      --- In SQLQueriesNoCode@ yahoogroups. com, Noman Aftab <noman17pk@. ..> wrote:
                      >
                      > Yeah, I also tried to make it run-able but was unable to do so.
                      > Best Wishes,
                      > Noman Aftab
                      >
                      >
                      >
                      >
                      >
                      >
                      >
                      > ____________ _________ _________ __
                      > From: John Warner <john@...>
                      > To: SQLQueriesNoCode@ yahoogroups. com
                      > Sent: Thu, 25 March, 2010 1:40:51 PM
                      > Subject: RE: [SQLQueriesNoCode] Missing record query
                      >
                      >
                      > Paul the list software screwed up your post so that it is extremely difficult to read. If it isn't too much trouble (and you still have the original in your sent items box) could you repost but before you do uncheck html?
                      >
                      > Thanks and if it is gone don't worry about it.
                      >
                      > John Warner
                      >
                      > > -----Original Message-----
                      > > From: SQLQueriesNoCode@ yahoogroups. com
                      > > [mailto:SQLQueriesN oCode@ yahoogroups. com] On Behalf Of Paul Livengood
                      > > Sent: Wednesday, March 24, 2010 9:32 PM
                      > > To: SQLQueriesNoCode@ yahoogroups. com
                      > > Subject: Re: [SQLQueriesNoCode] Missing record query
                      > >
                      > > There are many different ways to do this and the correct would to use is based
                      > > on your business requirements (speed, how often used, how much data to
                      > > return, how called, etc).
                      > > I picked the following method because it uses no temp tables and is written so
                      > > it can be in a table function if needed. I know it uses many sub-queries, but if
                      > > your select list is low (a year of less would be my guess) then this will run
                      > > without issue.
                      > >
                      > >
                      > > HTH
                      > > Paul
                      > >
                      > > ------------ -CODE
                      > > --this is to recreate your tableDECLARE@ MARKET_PRICES
                      > > TABLE(PRICE_ DATE DATETIME,PRICE
                      > > NUMERIC(18,2) );INSERTINTO@ MARKET_PRICES
                      > > SELECT'01/Mar/ 2010',0.45; INSERTINTO@ MARKET_PRICES
                      > > SELECT'02/Mar/ 2010',0.44; INSERTINTO@ MARKET_PRICES
                      > > SELECT'05/Mar/ 2010',0.43; INSERTINTO@ MARKET_PRICES
                      > > SELECT'08/Mar/ 2010',0.40; INSERTINTO@ MARKET_PRICES
                      > > SELECT'09/Mar/ 2010',0.41; INSERTINTO@ MARKET_PRICES
                      > > SELECT'13/Mar/ 2010',0.46; INSERTINTO@ MARKET_PRICES
                      > > SELECT'17/Mar/ 2010',0.44; WITH--the with statement creates a table with a list
                      > > of numbers...that is all (taken from an old book i have)G0 AS(SELECT1 ASC
                      > > UNION ALLSELECT1), --2 rowsG1 AS(SELECT1 ASC FROMG0 ASA,G0
                      > > ASB),--4 rowsG2 AS(SELECT1 ASC FROMG1 ASA,G1 ASB),--16 rowsG3
                      > > AS(SELECT1 ASC FROMG2 ASA,G2 ASB),--256 rowsG4 AS(SELECT1 ASC
                      > > FROMG3 ASA,G3 ASB),--65536 rowsnum
                      > > AS(SELECTROW_ NUMBER()OVER( ORDERBYC) ASN
                      > > FROMG4)SELECTa. TempDate
                      > > DisplayDate, (SELECTMAX( aa.PRICE_ DATE)FROM@ MARKET_PRICES aa
                      > > WHEREaa.PRICE_ DATE
                      > > <=a.TempDate) PRICEDATE, (SELECTzz. PRICE FROM@MARKET_ PRICES
                      > > zz WHEREzz.PRICE_ DATE
                      > > =(SELECTMAX( aa.PRICE_ DATE)FROM@ MARKET_PRICES aa
                      > > WHEREaa.PRICE_ DATE <=a.TempDate) )PRICEFROM( SELECTCAST( '2010-
                      > > 03-01'ASDATETIME) +N-1 ASTempDate --start dateFROMnumWHEREN
                      > > BETWEEN1 AND31)a --number of days you want
                      > > returnedLEFTJOIN@ MARKET_PRICES bONa.TempDate =b.PRICE_DATE
                      > >
                      > >
                      > >
                      > >
                      > > ____________ _________ _________ __
                      > > From: Noman Aftab <noman17pk@yahoo. com>
                      > > To: SQLQueriesNoCode@ yahoogroups. com
                      > > Cc: faraz.faheem@ hotmail.com
                      > > Sent: Wed, March 24, 2010 2:04:15 PM
                      > > Subject: [SQLQueriesNoCode] Missing record query
                      > >
                      > >
                      > > Hi,
                      > > I have a table MARKET_PRICES with columns: SYMBOL, PRICE_DATE and
                      > > PRICE.
                      > > There may be days on which the prices don't get upload, simply because they
                      > > havn't changed for the day.
                      > > Therefore data in the table can be like this:
                      > > 01/Mar/2010, 0.45
                      > > 02/Mar/2010, 0.44
                      > > 05/Mar/2010, 0.43
                      > > 08/Mar/2010, 0.40
                      > > 09/Mar/2010, 0.41
                      > > 13/Mar/2010, 0.46
                      > > 17/Mar/2010, 0.44
                      > >
                      > > I need a query which when given from and to dates, shows the prices for each
                      > > day, if price of a particular day is not uploaded then the price of the previous
                      > > day should be shown for that day, for e.g. giving dates 01/Mar/2010 to
                      > > 20/mar/2010 should yield the following results:
                      > >
                      > > 01/Mar/2010, 0.45
                      > > 02/Mar/2010, 0.44
                      > > 03/Mar/2010, 0.44
                      > > 04/Mar/2010, 0.44
                      > > 05/Mar/2010, 0.43
                      > > 06/Mar/2010, 0.43
                      > > 07/Mar/2010, 0.43
                      > > 08/Mar/2010, 0.40
                      > > 09/Mar/2010, 0.41
                      > > 10/Mar/2010, 0.41
                      > > 11/Mar/2010, 0.41
                      > > 12/Mar/2010, 0.41
                      > > 13/Mar/2010, 0.46
                      > > 14/Mar/2010, 0.46
                      > > 15/Mar/2010, 0.46
                      > > 16/Mar/2010, 0.46
                      > > 17/Mar/2010, 0.44
                      > > 18/Mar/2010, 0.44
                      > > 19/Mar/2010, 0.44
                      > > 20/Mar/2010, 0.44
                      > >
                      > > Best Wishes,
                      > > Noman Aftab
                      > >
                      > > http://www.corpus. quran.com/ wordbyword. jsp
                      > > http://www.jalandha ri.qsh.eu
                      > >
                      > > Your Mail works best with the New Yahoo Optimized IE8. Get it NOW!
                      > > http://downloads. yahoo.com/ in/internetexplo rer/
                      > >
                      > > [Non-text portions of this message have been removed]
                      > >
                      > >
                      > >
                      > >
                      > > [Non-text portions of this message have been removed]
                      > >
                      > >
                      > >
                      > > ------------ --------- --------- ------
                      > >
                      > > Yahoo! Groups Links
                      > >
                      > >
                      > >
                      >
                      >
                      >
                      >
                      >
                      > The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo. com/
                      >
                      > [Non-text portions of this message have been removed]
                      >





                      Your Mail works best with the New Yahoo Optimized IE8. Get it NOW! http://downloads.yahoo.com/in/internetexplorer/

                      [Non-text portions of this message have been removed]
                    • Noman Aftab
                      Hi Paul, Here is one solution which I got from another source; the difference being the CTE structure. DECLARE @MARKET_PRICES TABLE ( PRICE_DATE DATETIME,
                      Message 10 of 20 , Mar 27, 2010
                      • 0 Attachment
                        Hi Paul,
                        Here is one solution which I got from another source; the difference being the CTE structure.

                        DECLARE @MARKET_PRICES TABLE (
                        PRICE_DATE DATETIME,
                        PRICE NUMERIC(18,2) );

                        INSERT INTO @MARKET_PRICES SELECT '01/Mar/2010' , 0.45;
                        INSERT INTO @MARKET_PRICES SELECT '02/Mar/2010' , 0.44;
                        INSERT INTO @MARKET_PRICES SELECT '05/Mar/2010' , 0.43;
                        INSERT INTO @MARKET_PRICES SELECT '08/Mar/2010' , 0.40;
                        INSERT INTO @MARKET_PRICES SELECT '09/Mar/2010' , 0.41;
                        INSERT INTO @MARKET_PRICES SELECT '13/Mar/2010' , 0.46;
                        INSERT INTO @MARKET_PRICES SELECT '17/Mar/2010' , 0.44;

                        WITH CTE(Date)AS(SELECT Date = CONVERT(DATETIME,'20100301')
                        UNION ALL SELECT Date = Date + 1 FROM CTE WHERE Date < '20100331'
                        )

                        SELECT CTE.Date DisplayDate,
                        (SELECT MAX(aa.PRICE_DATE) FROM @MARKET_PRICES aa WHERE aa.PRICE_DATE <= CTE.Date) PRICEDATE,
                        (SELECT MP.PRICE FROM @MARKET_PRICES MP WHERE MP.PRICE_DATE = (SELECT MAX(aa.PRICE_DATE) FROM @MARKET_PRICES aaWHERE aa.PRICE_DATE <= CTE.Date)) PRICE
                        FROM CTE
                        LEFT JOIN @MARKET_PRICES b
                        ON CTE.Date = b.PRICE_DATE
                        Best Wishes,
                        Noman Aftab


                        http://www.corpus.quran.com/wordbyword.jsp
                        http://www.jalandhari.qsh.eu





                        ________________________________
                        From: p_livengood <p_livengood@...>
                        To: SQLQueriesNoCode@yahoogroups.com
                        Sent: Thu, 25 March, 2010 6:25:15 PM
                        Subject: [SQLQueriesNoCode] Re: Missing record query




                        sorry about that....is this better?

                        ------CODE
                        --this is to recreate your table
                        DECLARE @MARKET_PRICES TABLE (
                        PRICE_DATE DATETIME,
                        PRICE NUMERIC(18,2) );

                        INSERT INTO @MARKET_PRICES SELECT '01/Mar/2010' , 0.45;
                        INSERT INTO @MARKET_PRICES SELECT '02/Mar/2010' , 0.44;
                        INSERT INTO @MARKET_PRICES SELECT '05/Mar/2010' , 0.43;
                        INSERT INTO @MARKET_PRICES SELECT '08/Mar/2010' , 0.40;
                        INSERT INTO @MARKET_PRICES SELECT '09/Mar/2010' , 0.41;
                        INSERT INTO @MARKET_PRICES SELECT '13/Mar/2010' , 0.46;
                        INSERT INTO @MARKET_PRICES SELECT '17/Mar/2010' , 0.44;

                        WITH --the with statement creates a table with a list of numbers...that is all (taken from an old book i have)
                        G0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows
                        G1 AS (SELECT 1 AS C FROM G0 AS A, G0 AS B),--4 rows
                        G2 AS (SELECT 1 AS C FROM G1 AS A, G1 AS B),--16 rows
                        G3 AS (SELECT 1 AS C FROM G2 AS A, G2 AS B),--256 rows
                        G4 AS (SELECT 1 AS C FROM G3 AS A, G3 AS B),--65536 rows
                        num AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS N FROM G4)

                        SELECT a.TempDate DisplayDate,
                        (SELECT MAX(aa.PRICE_ DATE) FROM @MARKET_PRICES aa WHERE aa.PRICE_DATE <= a.TempDate) PRICEDATE,
                        (SELECT zz.PRICE FROM @MARKET_PRICES zz WHERE zz.PRICE_DATE = (SELECT MAX(aa.PRICE_ DATE) FROM @MARKET_PRICES aa WHERE aa.PRICE_DATE <= a.TempDate)) PRICE
                        FROM (SELECT CAST('2010-03- 01' AS DATETIME) + N-1 AS TempDate --start date
                        FROM num
                        WHERE N BETWEEN 1 AND 31) a --number of days you want returned
                        LEFT JOIN @MARKET_PRICES b
                        ON a.TempDate = b.PRICE_DATE

                        Paul

                        --- In SQLQueriesNoCode@ yahoogroups. com, Noman Aftab <noman17pk@. ..> wrote:
                        >
                        > Yeah, I also tried to make it run-able but was unable to do so.
                        > Best Wishes,
                        > Noman Aftab
                        >
                        >
                        >
                        >
                        >
                        >
                        >
                        > ____________ _________ _________ __
                        > From: John Warner <john@...>
                        > To: SQLQueriesNoCode@ yahoogroups. com
                        > Sent: Thu, 25 March, 2010 1:40:51 PM
                        > Subject: RE: [SQLQueriesNoCode] Missing record query
                        >
                        >
                        > Paul the list software screwed up your post so that it is extremely difficult to read. If it isn't too much trouble (and you still have the original in your sent items box) could you repost but before you do uncheck html?
                        >
                        > Thanks and if it is gone don't worry about it.
                        >
                        > John Warner
                        >
                        > > -----Original Message-----
                        > > From: SQLQueriesNoCode@ yahoogroups. com
                        > > [mailto:SQLQueriesN oCode@ yahoogroups. com] On Behalf Of Paul Livengood
                        > > Sent: Wednesday, March 24, 2010 9:32 PM
                        > > To: SQLQueriesNoCode@ yahoogroups. com
                        > > Subject: Re: [SQLQueriesNoCode] Missing record query
                        > >
                        > > There are many different ways to do this and the correct would to use is based
                        > > on your business requirements (speed, how often used, how much data to
                        > > return, how called, etc).
                        > > I picked the following method because it uses no temp tables and is written so
                        > > it can be in a table function if needed. I know it uses many sub-queries, but if
                        > > your select list is low (a year of less would be my guess) then this will run
                        > > without issue.
                        > >
                        > >
                        > > HTH
                        > > Paul
                        > >
                        > > ------------ -CODE
                        > > --this is to recreate your tableDECLARE@ MARKET_PRICES
                        > > TABLE(PRICE_ DATE DATETIME,PRICE
                        > > NUMERIC(18,2) );INSERTINTO@ MARKET_PRICES
                        > > SELECT'01/Mar/ 2010',0.45; INSERTINTO@ MARKET_PRICES
                        > > SELECT'02/Mar/ 2010',0.44; INSERTINTO@ MARKET_PRICES
                        > > SELECT'05/Mar/ 2010',0.43; INSERTINTO@ MARKET_PRICES
                        > > SELECT'08/Mar/ 2010',0.40; INSERTINTO@ MARKET_PRICES
                        > > SELECT'09/Mar/ 2010',0.41; INSERTINTO@ MARKET_PRICES
                        > > SELECT'13/Mar/ 2010',0.46; INSERTINTO@ MARKET_PRICES
                        > > SELECT'17/Mar/ 2010',0.44; WITH--the with statement creates a table with a list
                        > > of numbers...that is all (taken from an old book i have)G0 AS(SELECT1 ASC
                        > > UNION ALLSELECT1), --2 rowsG1 AS(SELECT1 ASC FROMG0 ASA,G0
                        > > ASB),--4 rowsG2 AS(SELECT1 ASC FROMG1 ASA,G1 ASB),--16 rowsG3
                        > > AS(SELECT1 ASC FROMG2 ASA,G2 ASB),--256 rowsG4 AS(SELECT1 ASC
                        > > FROMG3 ASA,G3 ASB),--65536 rowsnum
                        > > AS(SELECTROW_ NUMBER()OVER( ORDERBYC) ASN
                        > > FROMG4)SELECTa. TempDate
                        > > DisplayDate, (SELECTMAX( aa.PRICE_ DATE)FROM@ MARKET_PRICES aa
                        > > WHEREaa.PRICE_ DATE
                        > > <=a.TempDate) PRICEDATE, (SELECTzz. PRICE FROM@MARKET_ PRICES
                        > > zz WHEREzz.PRICE_ DATE
                        > > =(SELECTMAX( aa.PRICE_ DATE)FROM@ MARKET_PRICES aa
                        > > WHEREaa.PRICE_ DATE <=a.TempDate) )PRICEFROM( SELECTCAST( '2010-
                        > > 03-01'ASDATETIME) +N-1 ASTempDate --start dateFROMnumWHEREN
                        > > BETWEEN1 AND31)a --number of days you want
                        > > returnedLEFTJOIN@ MARKET_PRICES bONa.TempDate =b.PRICE_DATE
                        > >
                        > >
                        > >
                        > >
                        > > ____________ _________ _________ __
                        > > From: Noman Aftab <noman17pk@yahoo. com>
                        > > To: SQLQueriesNoCode@ yahoogroups. com
                        > > Cc: faraz.faheem@ hotmail.com
                        > > Sent: Wed, March 24, 2010 2:04:15 PM
                        > > Subject: [SQLQueriesNoCode] Missing record query
                        > >
                        > >
                        > > Hi,
                        > > I have a table MARKET_PRICES with columns: SYMBOL, PRICE_DATE and
                        > > PRICE.
                        > > There may be days on which the prices don't get upload, simply because they
                        > > havn't changed for the day.
                        > > Therefore data in the table can be like this:
                        > > 01/Mar/2010, 0.45
                        > > 02/Mar/2010, 0.44
                        > > 05/Mar/2010, 0.43
                        > > 08/Mar/2010, 0.40
                        > > 09/Mar/2010, 0.41
                        > > 13/Mar/2010, 0.46
                        > > 17/Mar/2010, 0.44
                        > >
                        > > I need a query which when given from and to dates, shows the prices for each
                        > > day, if price of a particular day is not uploaded then the price of the previous
                        > > day should be shown for that day, for e.g. giving dates 01/Mar/2010 to
                        > > 20/mar/2010 should yield the following results:
                        > >
                        > > 01/Mar/2010, 0.45
                        > > 02/Mar/2010, 0.44
                        > > 03/Mar/2010, 0.44
                        > > 04/Mar/2010, 0.44
                        > > 05/Mar/2010, 0.43
                        > > 06/Mar/2010, 0.43
                        > > 07/Mar/2010, 0.43
                        > > 08/Mar/2010, 0.40
                        > > 09/Mar/2010, 0.41
                        > > 10/Mar/2010, 0.41
                        > > 11/Mar/2010, 0.41
                        > > 12/Mar/2010, 0.41
                        > > 13/Mar/2010, 0.46
                        > > 14/Mar/2010, 0.46
                        > > 15/Mar/2010, 0.46
                        > > 16/Mar/2010, 0.46
                        > > 17/Mar/2010, 0.44
                        > > 18/Mar/2010, 0.44
                        > > 19/Mar/2010, 0.44
                        > > 20/Mar/2010, 0.44
                        > >
                        > > Best Wishes,
                        > > Noman Aftab
                        > >
                        > > http://www.corpus. quran.com/ wordbyword. jsp
                        > > http://www.jalandha ri.qsh.eu
                        > >
                        > > Your Mail works best with the New Yahoo Optimized IE8. Get it NOW!
                        > > http://downloads. yahoo.com/ in/internetexplo rer/
                        > >
                        > > [Non-text portions of this message have been removed]
                        > >
                        > >
                        > >
                        > >
                        > > [Non-text portions of this message have been removed]
                        > >
                        > >
                        > >
                        > > ------------ --------- --------- ------
                        > >
                        > > Yahoo! Groups Links
                        > >
                        > >
                        > >
                        >
                        >
                        >
                        >
                        >
                        > The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo. com/
                        >
                        > [Non-text portions of this message have been removed]
                        >





                        Your Mail works best with the New Yahoo Optimized IE8. Get it NOW! http://downloads.yahoo.com/in/internetexplorer/
                      • John Warner
                        Cool! Of course Paul s book was about SQL Server 2000 and yours 2005 +. I think I like the CTE better. John Warner
                        Message 11 of 20 , Mar 27, 2010
                        • 0 Attachment
                          Cool! Of course Paul's 'book was about SQL Server 2000 and yours 2005 +.
                          I think I like the CTE better.

                          John Warner


                          > -----Original Message-----
                          > From: SQLQueriesNoCode@yahoogroups.com
                          > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Noman Aftab
                          > Sent: Saturday, March 27, 2010 5:41 AM
                          > To: SQLQueriesNoCode@yahoogroups.com
                          > Subject: Re: [SQLQueriesNoCode] Re: Missing record query
                          >
                          > Hi Paul,
                          > Here is one solution which I got from another source; the difference being the
                          > CTE structure.
                          >
                          > DECLARE @MARKET_PRICES TABLE (
                          > PRICE_DATE DATETIME,
                          > PRICE NUMERIC(18,2) );
                          >
                          > INSERT INTO @MARKET_PRICES SELECT '01/Mar/2010' , 0.45;
                          > INSERT INTO @MARKET_PRICES SELECT '02/Mar/2010' , 0.44;
                          > INSERT INTO @MARKET_PRICES SELECT '05/Mar/2010' , 0.43;
                          > INSERT INTO @MARKET_PRICES SELECT '08/Mar/2010' , 0.40;
                          > INSERT INTO @MARKET_PRICES SELECT '09/Mar/2010' , 0.41;
                          > INSERT INTO @MARKET_PRICES SELECT '13/Mar/2010' , 0.46;
                          > INSERT INTO @MARKET_PRICES SELECT '17/Mar/2010' , 0.44;
                          >
                          > WITH CTE(Date)AS(SELECT Date = CONVERT(DATETIME,'20100301')
                          > UNION ALL SELECT Date = Date + 1 FROM CTE WHERE Date < '20100331'
                          > )
                          >
                          > SELECT CTE.Date DisplayDate,
                          > (SELECT MAX(aa.PRICE_DATE) FROM @MARKET_PRICES aa WHERE
                          > aa.PRICE_DATE <= CTE.Date) PRICEDATE,
                          > (SELECT MP.PRICE FROM @MARKET_PRICES MP WHERE
                          > MP.PRICE_DATE = (SELECT MAX(aa.PRICE_DATE) FROM
                          > @MARKET_PRICES aaWHERE aa.PRICE_DATE <= CTE.Date)) PRICE
                          > FROM CTE
                          > LEFT JOIN @MARKET_PRICES b
                          > ON CTE.Date = b.PRICE_DATE
                          > Best Wishes,
                          > Noman Aftab
                          >
                          >
                          > http://www.corpus.quran.com/wordbyword.jsp
                          > http://www.jalandhari.qsh.eu
                          >
                          >
                          >
                          >
                          >
                          > ________________________________
                          > From: p_livengood <p_livengood@...>
                          > To: SQLQueriesNoCode@yahoogroups.com
                          > Sent: Thu, 25 March, 2010 6:25:15 PM
                          > Subject: [SQLQueriesNoCode] Re: Missing record query
                          >
                          >
                          >
                          >
                          > sorry about that....is this better?
                          >
                          > ------CODE
                          > --this is to recreate your table
                          > DECLARE @MARKET_PRICES TABLE (
                          > PRICE_DATE DATETIME,
                          > PRICE NUMERIC(18,2) );
                          >
                          > INSERT INTO @MARKET_PRICES SELECT '01/Mar/2010' , 0.45;
                          > INSERT INTO @MARKET_PRICES SELECT '02/Mar/2010' , 0.44;
                          > INSERT INTO @MARKET_PRICES SELECT '05/Mar/2010' , 0.43;
                          > INSERT INTO @MARKET_PRICES SELECT '08/Mar/2010' , 0.40;
                          > INSERT INTO @MARKET_PRICES SELECT '09/Mar/2010' , 0.41;
                          > INSERT INTO @MARKET_PRICES SELECT '13/Mar/2010' , 0.46;
                          > INSERT INTO @MARKET_PRICES SELECT '17/Mar/2010' , 0.44;
                          >
                          > WITH --the with statement creates a table with a list of numbers...that is all
                          > (taken from an old book i have)
                          > G0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows
                          > G1 AS (SELECT 1 AS C FROM G0 AS A, G0 AS B),--4 rows
                          > G2 AS (SELECT 1 AS C FROM G1 AS A, G1 AS B),--16 rows
                          > G3 AS (SELECT 1 AS C FROM G2 AS A, G2 AS B),--256 rows
                          > G4 AS (SELECT 1 AS C FROM G3 AS A, G3 AS B),--65536 rows
                          > num AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS N FROM G4)
                          >
                          > SELECT a.TempDate DisplayDate,
                          > (SELECT MAX(aa.PRICE_ DATE) FROM @MARKET_PRICES aa WHERE
                          > aa.PRICE_DATE <= a.TempDate) PRICEDATE,
                          > (SELECT zz.PRICE FROM @MARKET_PRICES zz WHERE zz.PRICE_DATE
                          > = (SELECT MAX(aa.PRICE_ DATE) FROM @MARKET_PRICES aa WHERE
                          > aa.PRICE_DATE <= a.TempDate)) PRICE
                          > FROM (SELECT CAST('2010-03- 01' AS DATETIME) + N-1 AS TempDate
                          > --start date
                          > FROM num
                          > WHERE N BETWEEN 1 AND 31) a --number of days you want returned
                          > LEFT JOIN @MARKET_PRICES b
                          > ON a.TempDate = b.PRICE_DATE
                          >
                          > Paul
                          >
                          > --- In SQLQueriesNoCode@ yahoogroups. com, Noman Aftab <noman17pk@.
                          > ..> wrote:
                          > >
                          > > Yeah, I also tried to make it run-able but was unable to do so.
                          > > Best Wishes,
                          > > Noman Aftab
                          > >
                          > >
                          > >
                          > >
                          > >
                          > >
                          > >
                          > > ____________ _________ _________ __
                          > > From: John Warner <john@...>
                          > > To: SQLQueriesNoCode@ yahoogroups. com
                          > > Sent: Thu, 25 March, 2010 1:40:51 PM
                          > > Subject: RE: [SQLQueriesNoCode] Missing record query
                          > >
                          > >
                          > > Paul the list software screwed up your post so that it is extremely difficult to
                          > read. If it isn't too much trouble (and you still have the original in your sent
                          > items box) could you repost but before you do uncheck html?
                          > >
                          > > Thanks and if it is gone don't worry about it.
                          > >
                          > > John Warner
                          > >
                          > > > -----Original Message-----
                          > > > From: SQLQueriesNoCode@ yahoogroups. com
                          > > > [mailto:SQLQueriesN oCode@ yahoogroups. com] On Behalf Of Paul
                          > Livengood
                          > > > Sent: Wednesday, March 24, 2010 9:32 PM
                          > > > To: SQLQueriesNoCode@ yahoogroups. com
                          > > > Subject: Re: [SQLQueriesNoCode] Missing record query
                          > > >
                          > > > There are many different ways to do this and the correct would to use is
                          > based
                          > > > on your business requirements (speed, how often used, how much data to
                          > > > return, how called, etc).
                          > > > I picked the following method because it uses no temp tables and is written
                          > so
                          > > > it can be in a table function if needed. I know it uses many sub-queries,
                          > but if
                          > > > your select list is low (a year of less would be my guess) then this will run
                          > > > without issue.
                          > > >
                          > > >
                          > > > HTH
                          > > > Paul
                          > > >
                          > > > ------------ -CODE
                          > > > --this is to recreate your tableDECLARE@ MARKET_PRICES
                          > > > TABLE(PRICE_ DATE DATETIME,PRICE
                          > > > NUMERIC(18,2) );INSERTINTO@ MARKET_PRICES
                          > > > SELECT'01/Mar/ 2010',0.45; INSERTINTO@ MARKET_PRICES
                          > > > SELECT'02/Mar/ 2010',0.44; INSERTINTO@ MARKET_PRICES
                          > > > SELECT'05/Mar/ 2010',0.43; INSERTINTO@ MARKET_PRICES
                          > > > SELECT'08/Mar/ 2010',0.40; INSERTINTO@ MARKET_PRICES
                          > > > SELECT'09/Mar/ 2010',0.41; INSERTINTO@ MARKET_PRICES
                          > > > SELECT'13/Mar/ 2010',0.46; INSERTINTO@ MARKET_PRICES
                          > > > SELECT'17/Mar/ 2010',0.44; WITH--the with statement creates a table with
                          > a list
                          > > > of numbers...that is all (taken from an old book i have)G0 AS(SELECT1
                          > ASC
                          > > > UNION ALLSELECT1), --2 rowsG1 AS(SELECT1 ASC FROMG0 ASA,G0
                          > > > ASB),--4 rowsG2 AS(SELECT1 ASC FROMG1 ASA,G1 ASB),--16 rowsG3
                          > > > AS(SELECT1 ASC FROMG2 ASA,G2 ASB),--256 rowsG4 AS(SELECT1
                          > ASC
                          > > > FROMG3 ASA,G3 ASB),--65536 rowsnum
                          > > > AS(SELECTROW_ NUMBER()OVER( ORDERBYC) ASN
                          > > > FROMG4)SELECTa. TempDate
                          > > > DisplayDate, (SELECTMAX( aa.PRICE_ DATE)FROM@
                          > MARKET_PRICES aa
                          > > > WHEREaa.PRICE_ DATE
                          > > > <=a.TempDate) PRICEDATE, (SELECTzz. PRICE FROM@MARKET_
                          > PRICES
                          > > > zz WHEREzz.PRICE_ DATE
                          > > > =(SELECTMAX( aa.PRICE_ DATE)FROM@ MARKET_PRICES aa
                          > > > WHEREaa.PRICE_ DATE <=a.TempDate) )PRICEFROM( SELECTCAST(
                          > '2010-
                          > > > 03-01'ASDATETIME) +N-1 ASTempDate --start dateFROMnumWHEREN
                          > > > BETWEEN1 AND31)a --number of days you want
                          > > > returnedLEFTJOIN@ MARKET_PRICES bONa.TempDate
                          > =b.PRICE_DATE
                          > > >
                          > > >
                          > > >
                          > > >
                          > > > ____________ _________ _________ __
                          > > > From: Noman Aftab <noman17pk@yahoo. com>
                          > > > To: SQLQueriesNoCode@ yahoogroups. com
                          > > > Cc: faraz.faheem@ hotmail.com
                          > > > Sent: Wed, March 24, 2010 2:04:15 PM
                          > > > Subject: [SQLQueriesNoCode] Missing record query
                          > > >
                          > > >
                          > > > Hi,
                          > > > I have a table MARKET_PRICES with columns: SYMBOL, PRICE_DATE
                          > and
                          > > > PRICE.
                          > > > There may be days on which the prices don't get upload, simply because
                          > they
                          > > > havn't changed for the day.
                          > > > Therefore data in the table can be like this:
                          > > > 01/Mar/2010, 0.45
                          > > > 02/Mar/2010, 0.44
                          > > > 05/Mar/2010, 0.43
                          > > > 08/Mar/2010, 0.40
                          > > > 09/Mar/2010, 0.41
                          > > > 13/Mar/2010, 0.46
                          > > > 17/Mar/2010, 0.44
                          > > >
                          > > > I need a query which when given from and to dates, shows the prices for
                          > each
                          > > > day, if price of a particular day is not uploaded then the price of the
                          > previous
                          > > > day should be shown for that day, for e.g. giving dates 01/Mar/2010 to
                          > > > 20/mar/2010 should yield the following results:
                          > > >
                          > > > 01/Mar/2010, 0.45
                          > > > 02/Mar/2010, 0.44
                          > > > 03/Mar/2010, 0.44
                          > > > 04/Mar/2010, 0.44
                          > > > 05/Mar/2010, 0.43
                          > > > 06/Mar/2010, 0.43
                          > > > 07/Mar/2010, 0.43
                          > > > 08/Mar/2010, 0.40
                          > > > 09/Mar/2010, 0.41
                          > > > 10/Mar/2010, 0.41
                          > > > 11/Mar/2010, 0.41
                          > > > 12/Mar/2010, 0.41
                          > > > 13/Mar/2010, 0.46
                          > > > 14/Mar/2010, 0.46
                          > > > 15/Mar/2010, 0.46
                          > > > 16/Mar/2010, 0.46
                          > > > 17/Mar/2010, 0.44
                          > > > 18/Mar/2010, 0.44
                          > > > 19/Mar/2010, 0.44
                          > > > 20/Mar/2010, 0.44
                          > > >
                          > > > Best Wishes,
                          > > > Noman Aftab
                          > > >
                          > > > http://www.corpus. quran.com/ wordbyword. jsp
                          > > > http://www.jalandha ri.qsh.eu
                          > > >
                          > > > Your Mail works best with the New Yahoo Optimized IE8. Get it NOW!
                          > > > http://downloads. yahoo.com/ in/internetexplo rer/
                          > > >
                          > > > [Non-text portions of this message have been removed]
                          > > >
                          > > >
                          > > >
                          > > >
                          > > > [Non-text portions of this message have been removed]
                          > > >
                          > > >
                          > > >
                          > > > ------------ --------- --------- ------
                          > > >
                          > > > Yahoo! Groups Links
                          > > >
                          > > >
                          > > >
                          > >
                          > >
                          > >
                          > >
                          > >
                          > > The INTERNET now has a personality. YOURS! See your Yahoo!
                          > Homepage. http://in.yahoo. com/
                          > >
                          > > [Non-text portions of this message have been removed]
                          > >
                          >
                          >
                          >
                          >
                          >
                          > Your Mail works best with the New Yahoo Optimized IE8. Get it NOW!
                          > http://downloads.yahoo.com/in/internetexplorer/
                          >
                          >
                          > ------------------------------------
                          >
                          > Yahoo! Groups Links
                          >
                          >
                          >
                        • hansve2000
                          If using SQL Server 2005 or later: WITH cte AS (SELECT ROW_NUMBER OVER(PARTITION BY Col1, Col2, Col3 ORDER BY Col1) AS rw FROM dbo.Employees) DELETE FROM cte
                          Message 12 of 20 , Apr 12 7:56 AM
                          • 0 Attachment
                            If using SQL Server 2005 or later:

                            WITH cte AS
                            (SELECT ROW_NUMBER OVER(PARTITION BY Col1, Col2, Col3 ORDER BY Col1) AS rw
                            FROM dbo.Employees)
                            DELETE FROM cte
                            WHERE rw > 1;

                            Include all columns in the PARTITION BY list but any column can be used with the ORDER BY clause.

                            Hans.

                            --- In SQLQueriesNoCode@yahoogroups.com, arun aarun <saarun_emf@...> wrote:
                            >
                            > Dear Friends,
                            >                                I have an employee table in which there is no primary key constraint  and there i have 600000 employees details . In that i have to delete duplicate records..
                            >  
                            >  
                            > seeking for reply as early.....
                            >  
                            > Thanks&Regards,
                            > Arun
                            >
                            >
                            > The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/
                            >
                            > [Non-text portions of this message have been removed]
                            >
                          • J P
                            I have a table with three columns Month (values JAN,FEB,MAR,APR) etc Monthno (values 1-12) Year 2009,2010, etc   How do I take the above fields and create a
                            Message 13 of 20 , Apr 15 9:37 AM
                            • 0 Attachment
                              I have a table with three columns
                              Month (values JAN,FEB,MAR,APR) etc
                              Monthno (values 1-12)
                              Year 2009,2010, etc
                               
                              How do I take the above fields and create a date that represents the first day of the month?

                              i.e. Convert Jan 2009 to 1/1/2009
                               
                              I don't need hours:minutes:seconds
                               
                               
                              Thanks for any suggestions.




                              [Non-text portions of this message have been removed]
                            • Arnie Rowland
                              Something like this could work: cast(( cast( [MonthNo] as varchar(2)) + /01/ + cast( [Year] as varchar(4))) as date ) the date datatype is new with SQL
                              Message 14 of 20 , Apr 15 9:57 AM
                              • 0 Attachment
                                Something like this could work:

                                cast(( cast( [MonthNo] as varchar(2)) + '/01/' + cast( [Year] as varchar(4))) as date )

                                the 'date' datatype is new with SQL Server 2008. If you are using SQL Server 2005, cast as a datetime datatype instead.

                                Regards,

                                Arnie Rowland, MVP (SQL Server)

                                "You cannot do a kindness too soon because you never know how soon it will be too late."
                                -Ralph Waldo Emerson




                                -----Original Message-----
                                From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of J P
                                Sent: Thursday, April 15, 2010 9:40 AM
                                To: Arnie
                                Subject: [SQLQueriesNoCode] converting to date time

                                I have a table with three columns
                                Month (values JAN,FEB,MAR,APR) etc
                                Monthno (values 1-12)
                                Year 2009,2010, etc

                                How do I take the above fields and create a date that represents the first day of the month?

                                i.e. Convert Jan 2009 to 1/1/2009

                                I don't need hours:minutes:seconds


                                Thanks for any suggestions.




                                [Non-text portions of this message have been removed]



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

                                Yahoo! Groups Links







                                Disclaimer - April 15, 2010
                                This email and any files transmitted with it are confidential and intended solely for SQLQueriesNoCode@yahoogroups.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Westwood Consulting, Inc. Warning: Although Westwood Consulting, Inc has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.
                                This disclaimer was added by Policy Patrol: http://www.policypatrol.com/
                              • abhijit kakade
                                HI , JHON     select convert(varchar, 1 + space(2) + [MONTH] + space(2) + [year], 102) from datetable Try this , this will be usefull. and let me know in
                                Message 15 of 20 , Apr 15 9:57 AM
                                • 0 Attachment
                                  HI , JHON
                                   
                                   
                                  select convert(varchar, '1' + space(2) + [MONTH] + space(2) + [year], 102) from datetable

                                  Try this , this will be usefull. and let me know in case of any concern.
                                  I would like to help you.
                                   
                                  [month] , [year] its your column name of datetable table





                                  Thanks and Regards
                                  Abhijit B. Kakade
                                   
                                  M - +91 9226752508
                                  E  - abhijitkakade123@...




                                  Winning doesn't always mean being first ,winning means you're doing better than you've done before - Bonniie Blair

                                  --- On Thu, 15/4/10, J P <pulverizers73@...> wrote:


                                  From: J P <pulverizers73@...>
                                  Subject: [SQLQueriesNoCode] converting to date time
                                  To: SQLQueriesNoCode@yahoogroups.com
                                  Cc: john@...
                                  Date: Thursday, 15 April, 2010, 10:07 PM


                                   



                                  I have a table with three columns
                                  Month (values JAN,FEB,MAR, APR) etc
                                  Monthno (values 1-12)
                                  Year 2009,2010, etc
                                   
                                  How do I take the above fields and create a date that represents the first day of the month?

                                  i.e. Convert Jan 2009 to 1/1/2009
                                   
                                  I don't need hours:minutes: seconds
                                   
                                   
                                  Thanks for any suggestions.

                                  [Non-text portions of this message have been removed]










                                  [Non-text portions of this message have been removed]
                                • J P
                                  that got it, thanks Arnie. 2005 version here, so I used datetime. ________________________________ From: Arnie Rowland To:
                                  Message 16 of 20 , Apr 15 10:02 AM
                                  • 0 Attachment
                                    that got it, thanks Arnie.

                                    2005 version here, so I used datetime.




                                    ________________________________
                                    From: Arnie Rowland <arnie@...>
                                    To: SQLQueriesNoCode@yahoogroups.com
                                    Sent: Thu, April 15, 2010 11:57:24 AM
                                    Subject: RE: [SQLQueriesNoCode] converting to date time

                                     
                                    Something like this could work:

                                    cast(( cast( [MonthNo] as varchar(2)) + '/01/' + cast( [Year] as varchar(4))) as date )

                                    the 'date' datatype is new with SQL Server 2008. If you are using SQL Server 2005, cast as a datetime datatype instead.

                                    Regards,

                                    Arnie Rowland, MVP (SQL Server)

                                    "You cannot do a kindness too soon because you never know how soon it will be too late."
                                    -Ralph Waldo Emerson

                                    -----Original Message-----
                                    From: SQLQueriesNoCode@ yahoogroups. com [mailto:SQLQueriesNoCode@ yahoogroups. com] On Behalf Of J P
                                    Sent: Thursday, April 15, 2010 9:40 AM
                                    To: Arnie
                                    Subject: [SQLQueriesNoCode] converting to date time

                                    I have a table with three columns
                                    Month (values JAN,FEB,MAR, APR) etc
                                    Monthno (values 1-12)
                                    Year 2009,2010, etc

                                    How do I take the above fields and create a date that represents the first day of the month?

                                    i.e. Convert Jan 2009 to 1/1/2009

                                    I don't need hours:minutes: seconds


                                    Thanks for any suggestions.

                                    [Non-text portions of this message have been removed]

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

                                    Yahoo! Groups Links

                                    Disclaimer - April 15, 2010
                                    This email and any files transmitted with it are confidential and intended solely for SQLQueriesNoCode@ yahoogroups. com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Westwood Consulting, Inc. Warning: Although Westwood Consulting, Inc has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.
                                    This disclaimer was added by Policy Patrol: http://www.policypatrol.com/






                                    [Non-text portions of this message have been removed]
                                  • John Warner
                                    Arnie as I look at this I wonder if DATEADD(mm,DATEDIFF(mm,0,theDate),0 Wouldn t work to get the 01 for the day of any passed in date in place of the CASTING?
                                    Message 17 of 20 , Apr 15 10:07 AM
                                    • 0 Attachment
                                      Arnie as I look at this I wonder if

                                      DATEADD(mm,DATEDIFF(mm,0,theDate),0

                                      Wouldn't work to get the 01 for the day of any passed in date in place of
                                      the CASTING? I guess really which would be more efficient to the server
                                      cpu?

                                      John Warner


                                      > -----Original Message-----
                                      > From: SQLQueriesNoCode@yahoogroups.com
                                      > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
                                      > Sent: Thursday, April 15, 2010 12:57 PM
                                      > To: SQLQueriesNoCode@yahoogroups.com
                                      > Subject: RE: [SQLQueriesNoCode] converting to date time
                                      >
                                      > Something like this could work:
                                      >
                                      > cast(( cast( [MonthNo] as varchar(2)) + '/01/' + cast( [Year] as
                                      varchar(4))) as
                                      > date )
                                      >
                                      > the 'date' datatype is new with SQL Server 2008. If you are using SQL
                                      Server
                                      > 2005, cast as a datetime datatype instead.
                                      >
                                      > Regards,
                                      >
                                      > Arnie Rowland, MVP (SQL Server)
                                      >
                                      > "You cannot do a kindness too soon because you never know how soon it
                                      will
                                      > be too late."
                                      > -Ralph Waldo Emerson
                                      >
                                      >
                                      >
                                      >
                                      > -----Original Message-----
                                      > From: SQLQueriesNoCode@yahoogroups.com
                                      > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of J P
                                      > Sent: Thursday, April 15, 2010 9:40 AM
                                      > To: Arnie
                                      > Subject: [SQLQueriesNoCode] converting to date time
                                      >
                                      > I have a table with three columns
                                      > Month (values JAN,FEB,MAR,APR) etc
                                      > Monthno (values 1-12)
                                      > Year 2009,2010, etc
                                      >
                                      > How do I take the above fields and create a date that represents the
                                      first day of
                                      > the month?
                                      >
                                      > i.e. Convert Jan 2009 to 1/1/2009
                                      >
                                      > I don't need hours:minutes:seconds
                                      >
                                      >
                                      > Thanks for any suggestions.
                                      >
                                      >
                                      >
                                      >
                                      > [Non-text portions of this message have been removed]
                                      >
                                      >
                                      >
                                      > ------------------------------------
                                      >
                                      > Yahoo! Groups Links
                                      >
                                      >
                                      >
                                      >
                                      >
                                      >
                                      >
                                      > Disclaimer - April 15, 2010
                                      > This email and any files transmitted with it are confidential and
                                      intended solely
                                      > for SQLQueriesNoCode@yahoogroups.com. If you are not the named
                                      > addressee you should not disseminate, distribute, copy or alter this
                                      email. Any
                                      > views or opinions presented in this email are solely those of the author
                                      and
                                      > might not represent those of Westwood Consulting, Inc. Warning: Although
                                      > Westwood Consulting, Inc has taken reasonable precautions to ensure no
                                      > viruses are present in this email, the company cannot accept
                                      responsibility for
                                      > any loss or damage arising from the use of this email or attachments.
                                      > This disclaimer was added by Policy Patrol: http://www.policypatrol.com/
                                      >
                                      >
                                      > ------------------------------------
                                      >
                                      > Yahoo! Groups Links
                                      >
                                      >
                                      >
                                    • Arnie Rowland
                                      It would be a good alternative -IF theDate was in a date format. In this situation, you have to build the date string first. So, it could be used for the
                                      Message 18 of 20 , Apr 15 10:18 AM
                                      • 0 Attachment
                                        It would be a good alternative -IF 'theDate' was in a date format.

                                        In this situation, you have to build the date string first. So, it could
                                        be used for the outer CAST().

                                        It's questionable if involving two functions (dateadd() and datediff())
                                        would be more efficient than a single cast(). And there still is an
                                        implicit cast() from the built up string to a datetime datatype in order
                                        to use the date functions. I'm not convinced it would be more efficient.

                                        I guess it depends upon the usage. AS with everything SQL Server, test,
                                        test, test.

                                        Regards,

                                        Arnie Rowland, MVP (SQL Server)

                                        "You cannot do a kindness too soon because you never know how soon it
                                        will be too late."
                                        -Ralph Waldo Emerson




                                        -----Original Message-----
                                        From: SQLQueriesNoCode@yahoogroups.com
                                        [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                                        Sent: Thursday, April 15, 2010 10:10 AM
                                        To: Arnie
                                        Subject: RE: [SQLQueriesNoCode] converting to date time

                                        Arnie as I look at this I wonder if

                                        DATEADD(mm,DATEDIFF(mm,0,theDate),0

                                        Wouldn't work to get the 01 for the day of any passed in date in place
                                        of the CASTING? I guess really which would be more efficient to the
                                        server cpu?

                                        John Warner


                                        > -----Original Message-----
                                        > From: SQLQueriesNoCode@yahoogroups.com
                                        > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
                                        > Sent: Thursday, April 15, 2010 12:57 PM
                                        > To: SQLQueriesNoCode@yahoogroups.com
                                        > Subject: RE: [SQLQueriesNoCode] converting to date time
                                        >
                                        > Something like this could work:
                                        >
                                        > cast(( cast( [MonthNo] as varchar(2)) + '/01/' + cast( [Year] as
                                        varchar(4))) as
                                        > date )
                                        >
                                        > the 'date' datatype is new with SQL Server 2008. If you are using SQL
                                        Server
                                        > 2005, cast as a datetime datatype instead.
                                        >
                                        > Regards,
                                        >
                                        > Arnie Rowland, MVP (SQL Server)
                                        >
                                        > "You cannot do a kindness too soon because you never know how soon it
                                        will
                                        > be too late."
                                        > -Ralph Waldo Emerson
                                        >
                                        >
                                        >
                                        >
                                        > -----Original Message-----
                                        > From: SQLQueriesNoCode@yahoogroups.com
                                        > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of J P
                                        > Sent: Thursday, April 15, 2010 9:40 AM
                                        > To: Arnie
                                        > Subject: [SQLQueriesNoCode] converting to date time
                                        >
                                        > I have a table with three columns
                                        > Month (values JAN,FEB,MAR,APR) etc
                                        > Monthno (values 1-12)
                                        > Year 2009,2010, etc
                                        >
                                        > How do I take the above fields and create a date that represents the
                                        first day of
                                        > the month?
                                        >
                                        > i.e. Convert Jan 2009 to 1/1/2009
                                        >
                                        > I don't need hours:minutes:seconds
                                        >
                                        >
                                        > Thanks for any suggestions.
                                        >
                                        >
                                        >
                                        >
                                        > [Non-text portions of this message have been removed]
                                        >
                                        >
                                        >
                                        > ------------------------------------
                                        >
                                        > Yahoo! Groups Links
                                        >
                                        >
                                        >
                                        >
                                        >
                                        >
                                        >
                                        > Disclaimer - April 15, 2010
                                        > This email and any files transmitted with it are confidential and
                                        intended solely
                                        > for SQLQueriesNoCode@yahoogroups.com. If you are not the named
                                        > addressee you should not disseminate, distribute, copy or alter this
                                        email. Any
                                        > views or opinions presented in this email are solely those of the
                                        > author
                                        and
                                        > might not represent those of Westwood Consulting, Inc. Warning:
                                        > Although Westwood Consulting, Inc has taken reasonable precautions to
                                        > ensure no viruses are present in this email, the company cannot accept
                                        responsibility for
                                        > any loss or damage arising from the use of this email or attachments.
                                        > This disclaimer was added by Policy Patrol:
                                        > http://www.policypatrol.com/
                                        >
                                        >
                                        > ------------------------------------
                                        >
                                        > Yahoo! Groups Links
                                        >
                                        >
                                        >



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

                                        Yahoo! Groups Links
                                      • John Warner
                                        Good point. Thanks for the explanation, I always learn something when I ask you or some of the others here. John Warner
                                        Message 19 of 20 , Apr 15 10:44 AM
                                        • 0 Attachment
                                          Good point. Thanks for the explanation, I always learn something when I
                                          ask you or some of the others here.

                                          John Warner


                                          > -----Original Message-----
                                          > From: SQLQueriesNoCode@yahoogroups.com
                                          > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
                                          > Sent: Thursday, April 15, 2010 1:18 PM
                                          > To: SQLQueriesNoCode@yahoogroups.com
                                          > Subject: RE: [SQLQueriesNoCode] converting to date time
                                          >
                                          > It would be a good alternative -IF 'theDate' was in a date format.
                                          >
                                          > In this situation, you have to build the date string first. So, it could
                                          > be used for the outer CAST().
                                          >
                                          > It's questionable if involving two functions (dateadd() and datediff())
                                          > would be more efficient than a single cast(). And there still is an
                                          > implicit cast() from the built up string to a datetime datatype in order
                                          > to use the date functions. I'm not convinced it would be more efficient.
                                          >
                                          > I guess it depends upon the usage. AS with everything SQL Server, test,
                                          > test, test.
                                          >
                                          > Regards,
                                          >
                                          > Arnie Rowland, MVP (SQL Server)
                                          >
                                          > "You cannot do a kindness too soon because you never know how soon it
                                          > will be too late."
                                          > -Ralph Waldo Emerson
                                          >
                                          >
                                          >
                                          >
                                          > -----Original Message-----
                                          > From: SQLQueriesNoCode@yahoogroups.com
                                          > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                                          > Sent: Thursday, April 15, 2010 10:10 AM
                                          > To: Arnie
                                          > Subject: RE: [SQLQueriesNoCode] converting to date time
                                          >
                                          > Arnie as I look at this I wonder if
                                          >
                                          > DATEADD(mm,DATEDIFF(mm,0,theDate),0
                                          >
                                          > Wouldn't work to get the 01 for the day of any passed in date in place
                                          > of the CASTING? I guess really which would be more efficient to the
                                          > server cpu?
                                          >
                                          > John Warner
                                          >
                                          >
                                          > > -----Original Message-----
                                          > > From: SQLQueriesNoCode@yahoogroups.com
                                          > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie
                                          > Rowland
                                          > > Sent: Thursday, April 15, 2010 12:57 PM
                                          > > To: SQLQueriesNoCode@yahoogroups.com
                                          > > Subject: RE: [SQLQueriesNoCode] converting to date time
                                          > >
                                          > > Something like this could work:
                                          > >
                                          > > cast(( cast( [MonthNo] as varchar(2)) + '/01/' + cast( [Year] as
                                          > varchar(4))) as
                                          > > date )
                                          > >
                                          > > the 'date' datatype is new with SQL Server 2008. If you are using SQL
                                          > Server
                                          > > 2005, cast as a datetime datatype instead.
                                          > >
                                          > > Regards,
                                          > >
                                          > > Arnie Rowland, MVP (SQL Server)
                                          > >
                                          > > "You cannot do a kindness too soon because you never know how soon it
                                          > will
                                          > > be too late."
                                          > > -Ralph Waldo Emerson
                                          > >
                                          > >
                                          > >
                                          > >
                                          > > -----Original Message-----
                                          > > From: SQLQueriesNoCode@yahoogroups.com
                                          > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of J P
                                          > > Sent: Thursday, April 15, 2010 9:40 AM
                                          > > To: Arnie
                                          > > Subject: [SQLQueriesNoCode] converting to date time
                                          > >
                                          > > I have a table with three columns
                                          > > Month (values JAN,FEB,MAR,APR) etc
                                          > > Monthno (values 1-12)
                                          > > Year 2009,2010, etc
                                          > >
                                          > > How do I take the above fields and create a date that represents the
                                          > first day of
                                          > > the month?
                                          > >
                                          > > i.e. Convert Jan 2009 to 1/1/2009
                                          > >
                                          > > I don't need hours:minutes:seconds
                                          > >
                                          > >
                                          > > Thanks for any suggestions.
                                          > >
                                          > >
                                          > >
                                          > >
                                          > > [Non-text portions of this message have been removed]
                                          > >
                                          > >
                                          > >
                                          > > ------------------------------------
                                          > >
                                          > > Yahoo! Groups Links
                                          > >
                                          > >
                                          > >
                                          > >
                                          > >
                                          > >
                                          > >
                                          > > Disclaimer - April 15, 2010
                                          > > This email and any files transmitted with it are confidential and
                                          > intended solely
                                          > > for SQLQueriesNoCode@yahoogroups.com. If you are not the named
                                          > > addressee you should not disseminate, distribute, copy or alter this
                                          > email. Any
                                          > > views or opinions presented in this email are solely those of the
                                          > > author
                                          > and
                                          > > might not represent those of Westwood Consulting, Inc. Warning:
                                          > > Although Westwood Consulting, Inc has taken reasonable precautions to
                                          > > ensure no viruses are present in this email, the company cannot accept
                                          > responsibility for
                                          > > any loss or damage arising from the use of this email or attachments.
                                          > > This disclaimer was added by Policy Patrol:
                                          > > http://www.policypatrol.com/
                                          > >
                                          > >
                                          > > ------------------------------------
                                          > >
                                          > > Yahoo! Groups Links
                                          > >
                                          > >
                                          > >
                                          >
                                          >
                                          >
                                          > ------------------------------------
                                          >
                                          > Yahoo! Groups Links
                                          >
                                          >
                                          >
                                          >
                                          >
                                          >
                                          >
                                          > ------------------------------------
                                          >
                                          > Yahoo! Groups Links
                                          >
                                          >
                                          >
                                        Your message has been successfully submitted and would be delivered to recipients shortly.