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

Query regarding deleting duplicates in table....

Expand Messages
  • arun aarun
    Dear Friends,                                I have an employee table in which there is no primary key constraint  and there i
    Message 1 of 20 , Mar 11, 2010
    • 0 Attachment
      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]
    • 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 2 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 3 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 4 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 5 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 6 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 7 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 8 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 9 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 10 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 11 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 12 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 13 of 20 , Apr 12, 2010
                            • 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 14 of 20 , Apr 15, 2010
                              • 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 15 of 20 , Apr 15, 2010
                                • 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 16 of 20 , Apr 15, 2010
                                  • 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 17 of 20 , Apr 15, 2010
                                    • 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 18 of 20 , Apr 15, 2010
                                      • 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 19 of 20 , Apr 15, 2010
                                        • 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 20 of 20 , Apr 15, 2010
                                          • 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.