User Profile

Collapse

Profile Sidebar

Collapse
HeavenCore
HeavenCore
Last Activity: Jul 11 '08, 09:52 AM
Joined: Oct 11 '07
Location:
  •  
  • Time
  • Show
  • Source
Clear All
new posts
  • HeavenCore
    started a topic SubQuery Question
    in SQL Server

    SubQuery Question

    Hello Everyone, i have a rather chunky Query:

    Code:
    SELECT top 100 percent EMDET.DET_NUMBERA, EMDET.DET_G1_NAME1A, EMDET.DET_SURNAMEA, EMDET.DET_BIR_DATED, EMDET.DET_SFT_DATED, EMPOS.POS_PDT_GRDA, EMPOS.POS_ENDD, EMPOS.POS_AV_HR_WKN, EMPOS.POS_L3_CDA, Max(EMSAL.SMN_DATEC) AS SMN_DATEC, Max(EMSAL.SMN_HOURLYN) as SMN_HOURLYN, EMPOS.POS_COST_GRPA, EMDET.DET_TER_DATED
FROM EMDET, EMPOS, EMSAL
WHERE EMDET.DET_NUMBERA = EMPOS.DET_NUMBERA
    ...
    See more | Go to post
    • HeavenCore
      replied to SQL Servers equivilient for Oracles Lead and Lag
      in SQL Server
      ive come up with a solution via implementing a simple subselect, works, but not sure what the performance implications are going to be.

      Query if anyone is interested:
      Code:
      select * from SELECT     DailyMileageID, MileageAtStartOfShift, MileageAtEndOfShift, MileageAtEndOfShift - MileageAtStartOfShift AS DayMileage, MileageAtEndOfShift -
                          (SELECT     MileageAtStartOfShift
      ...
      See more | Go to post

        Leave a comment:

      • HeavenCore
        replied to SQL Servers equivilient for Oracles Lead and Lag
        in SQL Server
        The Primary Hey is a field called DailyFuel_ID and it is an Identity Field Also.

        one other thing that might help you answer my question is i am only using sql server 2000 (i'm guessing thats why the MDX function lead/lag is not working).

        i was thinking of doing this with some kind of subselect with ID+1 or something, will have a play when i get in work.

        but we are going to be dealing with large tables...
        See more | Go to post

          Leave a comment:

        • HeavenCore
          started a topic SQL Servers equivilient for Oracles Lead and Lag
          in SQL Server

          SQL Servers equivilient for Oracles Lead and Lag

          Hello everyone, i have some data:



          i am trying to create a 4th column called 'Nightly Mileage' which would essentially be DailyMileage(ro w1)-MileageAtStartO fShift(Row2)

          not sure how to do an aggregate function over data from a different row, i tried this:

          Code:
          SELECT MileageAtStartOfShift, MileageAtEndOfShift, MileageAtEndOfShift - MileageAtStartOfShift AS DayMileage, 
MileageAtEndOfShift
          ...
          See more | Go to post
          • HeavenCore
            replied to IS NULL being ignored
            in SQL Server
            ok i think i have come up with a work around by putting the above query into a subselect then filtering out the nulls as follows:

            Code:
            SELECT * FROM (
SELECT A.*, E.PIPCode
FROM AnalystBuffer A
LEFT JOIN EnterpriseBuffer E
ON A.BuyingCode = E.PIPCode
WHERE E.PIPCode IS NULL and A.DailySupplier = 'Enterprise Single' or A.DailySupplier = 'Enterprise Outer')as tb WHERE PIPCODE is NULL
            and although...
            See more | Go to post

              Leave a comment:

            • HeavenCore
              started a topic IS NULL being ignored
              in SQL Server

              IS NULL being ignored

              Hello, i have a problem selecting rows from one table where they dont exist in another.

              These tables are as follows:
              Table A - AnalystBuffer (key Column is 'BuyingCode')
              Table E - EnterpriseBuffe r (key column is 'PIPCode')

              Table A includes different columns to Table B, but the data in the key columns should match up, at least partially. I need to select all records from Table E where the PIPCode does not...
              See more | Go to post
              • HeavenCore
                replied to UPDATE from Sub-select problem
                in SQL Server
                Spot on!

                Thanks alot, really appreciate that :D
                See more | Go to post

                  Leave a comment:

                • HeavenCore
                  started a topic UPDATE from Sub-select problem
                  in SQL Server

                  UPDATE from Sub-select problem

                  Hello all,

                  i have a process which reads 2 csv files into two different tables (thus there is no real primary key etc)

                  i need to update 1 column in one table (EnterpriseBuff er) with data from another table (EnterpriseProd GroupToAnalystD eptMap.AnalystD epartment). This is done where EnterpriseBuffe r.PG = EnterpriseProdG roupToAnalystDe ptMap.Enterpris ePG.

                  I attempted the following:

                  Code:
                  Update EnterpriseBuffer
                  ...
                  See more | Go to post
                  • HeavenCore
                    replied to max() throwing up group by errors, please help :D
                    in SQL Server
                    UPDATE: whatever idiot wrote the view i am selecting from has declared the datetime field in the view as a varchar, so max() is maxing the string, not as data, which explains why is select the higher date based on the 2 at the beinging of the dd/mm/yyyy

                    So i'm guessing i will have to do max(cast(date as satetime) although the varchar only has dd/mm/yyyy so i may need to use convert().

                    best get reading up on my date convert...
                    See more | Go to post

                      Leave a comment:

                    • HeavenCore
                      replied to max() throwing up group by errors, please help :D
                      in SQL Server
                      thankyou for your reply. However that has two issues,
                      1. The order by clause throws up the error 'Column name 'vw_Requests.Re quest_Created' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.'
                      2. if i remove the order by clause the query executes, however the data returned is incorrect, as i will now explain:


                      note in the image below the data we are selecting from...
                      See more | Go to post

                        Leave a comment:

                      • HeavenCore
                        started a topic max() throwing up group by errors, please help :D
                        in SQL Server

                        max() throwing up group by errors, please help :D

                        hello, i have rows of data with duplicates EXCEPT the id which is unique, i am try to select a row for each max date of each duplicate set: for example with the follwoing rows:

                        1----------08/10/2007----------Delivery----------Dorothy Haynes
                        2----------28/09/2007----------Delivery----------Dorothy Haynes
                        3----------08/10/2007----------Delivery----------Dorothy Kittrick
                        4----------28/09/2007----------Delivery----------Dorothy...
                        See more | Go to post
                        No activity results to display
                        Show More
                        Working...