SQL Server Performance

Struggling with fairly complex statement

Discussion in 'General Developer Questions' started by incandescant, Aug 3, 2004.

  1. incandescant New Member

    Hi all,

    I'm new to this whole SQL lark and struggling a fari bit. I wonder if anyone can help me.
    I want to populate a VB.Net Datagrid with data pulled from a table.
    I am trying to write an SQL statement to filter said data.
    The SQL code looks like this


    DECLARE @month INT
    DECLARE @day INT
    DECLARE @year INT
    SET @day = (SELECT DATEPART (dd, GETDATE()))
    SET @month = (SELECT DATEPART (mm, GETDATE()))
    SET @year = (SELECT DATEPART (yyyy, GETDATE()))
    SELECT heat.CallLog.CallID, heat.CallLog.RecvdDate, heat.CallLog.CallSummary, heat.Profile.EMailID
    FROM heat.CallLog CROSS JOIN heat.Profile
    WHERE
    (
    (heat.CallLog.CallStatus = 'Closed' OR heat.CallLog.CallStatus = 'closed')
    AND (heat.Profile.EMailID LIKE '%@bpb.com')
    AND (heat.Profile.Surname NOT LIKE '%ystem') AND (heat.Profile.Surname <> 'SYSTEM')
    AND (heat.CallLog.Cause NOT LIKE '%eavers')
    AND (heat.CallLog.Cause NOT LIKE '%eletions')
    AND (heat.CallLog.SupportType <> 'RBIT') AND (heat.CallLog.SupportType NOT LIKE '%bit')
    AND (SELECT DATEPART (mm, (SELECT (convert(DATETIME,ClosedDate)) FROM heat.CallLog))) = @month --(SELECT DATEPART (mm, GETDATE()))
    AND (SELECT DATEPART (dd, (SELECT (convert(DATETIME,ClosedDate)) FROM heat.CallLog))) = @day --(SELECT DATEPART (dd, GETDATE()))
    AND (SELECT DATEPART (yyyy, (SELECT (convert(DATETIME, ClosedDate)) FROM heat.CallLog))) = @year
    )

    When I run this I get the error


    quote:Server: Msg 512, Level 16, State 1, Line 13
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    Can anyone please help me?

    I'd really appreciate it!

    Thanks in advance!

    Josh
  2. Fndr70 New Member

    this part

    SELECT (convert(DATETIME,ClosedDate)) FROM heat.CallLog))) = @month

    (and the subsequent parts for day and year), are what is causing the error.

    The select statment returns more than one value (presumably, every closeddate in your call log). You're trying to compare this to one value, @month. That won't work.


    you're already selecting from teh calllog table, so there is no need to do it again. Change the above select statement that was causing an error to

    AND DATEPART(mm, convert(DATETIME, ClosedDate)) = @month


    and then repeat the changes for the day and year part of your where clause.
  3. incandescant New Member

    quote:Originally posted by Fndr70

    this part

    SELECT (convert(DATETIME,ClosedDate)) FROM heat.CallLog))) = @month

    (and the subsequent parts for day and year), are what is causing the error.

    The select statment returns more than one value (presumably, every closeddate in your call log). You're trying to compare this to one value, @month. That won't work.


    you're already selecting from teh calllog table, so there is no need to do it again. Change the above select statement that was causing an error to

    AND DATEPART(mm, convert(DATETIME, ClosedDate)) = @month


    and then repeat the changes for the day and year part of your where clause.
  4. Fndr70 New Member

    did that work for you?
  5. incandescant New Member

    Not really, it is pulling out all of the data in the database and changing all of the call date fields to the current date.<br /><br />E.G: <br /><br /> <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote">000333302004-08-04mark smith rang from transport to say batch processing runningbarbara.little@bpb.com<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />This row of data is over a year old but the filter changes the date to todays date returns the row... <img src='/community/emoticons/emotion-7.gif' alt=':s' /><br /><br />Heres how my SQL code looks now<br /><pre>USE heat<br /><br />DECLARE @month INT<br />DECLARE @day INT<br />DECLARE @year INT<br />SET @day = (SELECT DATEPART (dd, GETDATE()))<br />SET @month = (SELECT DATEPART (mm, GETDATE()))<br />SET @year = (SELECT DATEPART (yyyy, GETDATE()))<br />SELECT heat.CallLog.CallID, heat.CallLog.RecvdDate, heat.CallLog.CallSummary, heat.Profile.EMailID<br />FROM heat.CallLog CROSS JOIN heat.Profile<br />WHERE <br />( <br />(heat.CallLog.CallStatus = 'Closed' OR heat.CallLog.CallStatus = 'closed') <br />AND (heat.Profile.EMailID LIKE '%@bpb.com') <br />AND (heat.Profile.Surname NOT LIKE '%ystem') AND (heat.Profile.Surname &lt;&gt; 'SYSTEM')<br />AND (heat.CallLog.Cause NOT LIKE '%eavers')<br />AND (heat.CallLog.Cause NOT LIKE '%eletions')<br />AND (heat.CallLog.SupportType &lt;&gt; 'RBIT') AND (heat.CallLog.SupportType NOT LIKE '%bit')<br />AND (DATEPART (mm,(convert(DATETIME,ClosedDate)))) = (@month) <br />AND (DATEPART (dd,(convert(DATETIME,ClosedDate)))) = (@day) <br />AND (DATEPART (yyyy,(convert(DATETIME, ClosedDate)))) = (@year)<br />)<br /></pre>
  6. tdong New Member

    your @day @month and @year is from the GetDate and your WHERE condition as the queries to select mm = @month, dd =@day and yyyy =@year that is why you get all the return value of the same as GETDATE give me some rows example because there is no way that I can test the queries for you if I don't have any data.

    Beside the select statement DOESN"T CHANGE ANY VALUE it only return whatever YOU ASKED it to do hehehhe

    May the best cheaters win
  7. tdong New Member

    By the way the best way to test your SQL Statement above is to ELIMINATE the where statement. TEST one CONDITION AT A TIME to see if it return what YOU ASKED. then after that put everything back together? first thing you should test is your DATE hehhe. The statement below will return all the rows which has the same DATE AS GETDATE which is the currently date

    USE heat

    DECLARE @month INT
    DECLARE @day INT
    DECLARE @year INT
    SET @day = (SELECT DATEPART (dd, GETDATE()))
    SET @month = (SELECT DATEPART (mm, GETDATE()))
    SET @year = (SELECT DATEPART (yyyy, GETDATE()))
    SELECT heat.CallLog.CallID, heat.CallLog.RecvdDate, heat.CallLog.CallSummary, heat.Profile.EMailID
    FROM heat.CallLog CROSS JOIN heat.Profile
    WHERE
    (
    (DATEPART (mm,(convert(DATETIME,ClosedDate)))) = (@month)
    AND (DATEPART (dd,(convert(DATETIME,ClosedDate)))) = (@day)
    AND (DATEPART (yyyy,(convert(DATETIME, ClosedDate)))) = (@year)
    )


    May the best cheaters win
  8. incandescant New Member

    tdong

    It has all been tested, the date clauses are the latest addition.

    I would like the statement to compare the dateparts of the current date to the dateparts of the date from the table data.

    At the minute it is running with random test data
  9. tdong New Member

    you need to explain what you want to compare
    example. GETDATE = 2004-08-05 @month = 08 @year = 2004 @day =05

    CloseDate has these dates
    2004-07-10
    2004-07-06
    2004-08-05
    2004-07-08
    2004-07-07
    2004-08-05

    Your statement only return
    2004-08-05
    2004-08-05


    May the best cheaters win
  10. incandescant New Member

    Thats what I want to do but with what ever the current date is on the system so that i can get it to automatically pull data out every day.
    Can you suggest what changes need to be made to the SQL statement?

    Thanks
  11. tdong New Member

    Oh then that is exactly what your statement does however you need to specific where the CLOSEDATE from I assume it from heat.Profile otherwise change it to heat.CallLog

    USE heat

    DECLARE @month INT
    DECLARE @day INT
    DECLARE @year INT
    SET @day = (SELECT DATEPART (dd, GETDATE()))
    SET @month = (SELECT DATEPART (mm, GETDATE()))
    SET @year = (SELECT DATEPART (yyyy, GETDATE()))
    SELECT heat.CallLog.CallID, heat.CallLog.RecvdDate, heat.CallLog.CallSummary, heat.Profile.EMailID
    FROM heat.CallLog CROSS JOIN heat.Profile
    WHERE
    (
    (heat.CallLog.CallStatus = 'Closed' OR heat.CallLog.CallStatus = 'closed')
    AND (heat.Profile.EMailID LIKE '%@bpb.com')
    AND (heat.Profile.Surname NOT LIKE '%ystem') AND (heat.Profile.Surname <> 'SYSTEM')
    AND (heat.CallLog.Cause NOT LIKE '%eavers')
    AND (heat.CallLog.Cause NOT LIKE '%eletions')
    AND (heat.CallLog.SupportType <> 'RBIT') AND (heat.CallLog.SupportType NOT LIKE '%bit')
    AND (DATEPART (mm,(convert(DATETIME,heat.Profile.ClosedDate)))) = (@month)
    AND (DATEPART (dd,(convert(DATETIME,heat.Profile.ClosedDate)))) = (@day)
    AND (DATEPART (yyyy,(convert(DATETIME, heat.Profile.ClosedDate)))) = (@year)
    )



    May the best cheaters win
  12. tdong New Member

    A shorter statment would be why don't you just test the DATE your statement will RETURN only value of CLOSEDATE that is EQUAL to GETDATE if that is what you want. I still don't understand what you want yet

    SELECT heat.CallLog.CallID, heat.CallLog.RecvdDate, heat.CallLog.CallSummary, heat.Profile.EMailID
    FROM heat.CallLog CROSS JOIN heat.Profile
    WHERE DateDiff(day, heat.Profile.ClosedDate, getdate()) = 0


    May the best cheaters win
  13. incandescant New Member

    That shorter statement would be much better!
    However both statements return incorrect results

    I.E: the short stament returned this row in its output

    000330322004-07-30gyptel problems at k.t.no account - fergusons

    Appreciating your help!

    What I actually want is, at the end of every day, to get all of the records that where added to one database and copy select rows across to another database.

    When I get the statement working it will be included in a DTS hat will be scheduled to run nightly.
  14. tdong New Member

    then there must be something wrong with the DATE on your computer try this ADD THE GETDATE() in the SELECT to see the value of the GET DATE add ClosedDate as well

    SELECT heat.CallLog.CallID, heat.CallLog.RecvdDate, heat.CallLog.CallSummary, heat.Profile.EMailID, GETDATE(), ClosedDate
    FROM heat.CallLog CROSS JOIN heat.Profile
    WHERE DateDiff(day, heat.Profile.ClosedDate, getdate()) = 0


    May the best cheaters win
  15. tdong New Member

    assuming something is wrong with GETDATE() try to enter TODAY DATE INSTEAD this will returns everything you need post the RESULT up so I can see

    SELECT heat.CallLog.CallID, heat.CallLog.RecvdDate, heat.CallLog.CallSummary, heat.Profile.EMailID, GETDATE(), ClosedDate
    FROM heat.CallLog CROSS JOIN heat.Profile
    WHERE DateDiff(day, heat.Profile.ClosedDate, '2004-08-05') = 0


    May the best cheaters win
  16. incandescant New Member

    Running this code:

    SELECT heat.CallLog.CallID, heat.CallLog.RecvdDate, heat.CallLog.CallSummary, heat.Profile.EMailID, GETDATE(), ClosedDate
    FROM heat.CallLog CROSS JOIN heat.Profile
    WHERE DateDiff(day, heat.CallLog.ClosedDate, '2004-08-05') = 0
    returns this set of results

    quote:00032917 2004-07-29gyptel problems user@domain.com2004-08-09 10:58:24.8972004-08-05
    00033347 2004-08-04We are attempting to select a site address from the project options on order entryuser@domain.com2004-08-09 10:58:24.8972004-08-05
    00033443 2004-08-05qq password for itntuser@domain.com2004-08-09 10:58:24.8972004-08-05
    00033438 2004-08-05qq reprint f002user@domain.com2004-08-09 10:58:24.8972004-08-05
    00033468 2004-08-05qq outlook can't log onuser@domain.com2004-08-09 10:58:24.8972004-08-05

    The dates in all of these are wrong.
  17. tdong New Member

    look at your GETDATE() it is 2004-08-09 and the close date is obviously 2004-08-05 it return the correct result by comparing the '2004-08-05' to the CLOSEDATE. the only thing wrong is the GETDATE() since there are timezone to consider etc do the setting in sql. What do you mean by "the dates in all of these are wrong ?" you asked the sql to return all the closedate = 2004-08-05 and it did all you have to do now is to set the GETDATE to return the correct date

    May the best cheaters win
  18. incandescant New Member

    Ah yes, I see!

    Thanks for that.

    So how do I go about setting GETDATE to return the correct date/format?
  19. tdong New Member

    oh what I think is that did you run the query today it is AUGUST 09 <img src='/community/emoticons/emotion-1.gif' alt=':)' /> enter August 09 <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br /><br />SELECT heat.CallLog.CallID, heat.CallLog.RecvdDate, heat.CallLog.CallSummary, heat.Profile.EMailID, GETDATE(), ClosedDate<br />FROM heat.CallLog CROSS JOIN heat.Profile<br />WHERE DateDiff(day, heat.CallLog.ClosedDate, <font color="red">'2004-08-09'</font id="red">) = 0<br /><br />SELECT heat.CallLog.CallID, heat.CallLog.RecvdDate, heat.CallLog.CallSummary, heat.Profile.EMailID, GETDATE(), ClosedDate<br />FROM heat.CallLog CROSS JOIN heat.Profile<br />WHERE DateDiff(day, heat.CallLog.ClosedDate, <font color="red">GetDate()</font id="red">) = 0<br /><br />These two should give you the same results<br /><br />May the best cheaters win
  20. incandescant New Member

    tdong

    I have executed both of the above statements and for some strange reason they are returning ALL rows in the database, even where heat.CallLog.ClosedDate is not the current date.
    All of the rows in the output have the ClosedDate printed (output?) as the current date but that is not how the data is in the actual tables of the database.

    Is this in any way similar to the programming assign (=) and equals (==) operators of high level languages such as C++ and Java?

    Any ideas?

    Thanks for all you help

    Incandescant
  21. Adriaan New Member

    You can make a CROSS JOIN behave like an INNER JOIN by matching both tables in the WHERE clause. The WHERE clause in the above queries does not specify anything for the heat.Profile table and so the query is still returning too many rows.

    Not sure why you're not using an INNER JOIN? Why are you using a CROSS JOIN at all?
  22. incandescant New Member

    The join was done drag and drop from query analyzer (I am a serious newb to SQL).

    So you suggest I change CROSS to INNER?

    Is this what you recommend Adriaan

    SELECT heat.CallLog.CallID, heat.CallLog.RecvdDate, heat.CallLog.CallSummary, heat.Profile.EMailID, GETDATE(), ClosedDate
    FROM heat.CallLog INNER JOIN heat.Profile
    WHERE DateDiff(day, heat.CallLog.ClosedDate, GetDate()) = 0
  23. FrankKalis Moderator

    May I jump right in?

    Am I right, that you want this to find out all events that have CallLog.CallStatus ='closed' at the end of the day and then copy them to another database?
    If so, what is your goal?

    --Frank
    http://www.insidesql.de
  24. incandescant New Member

    quote:Originally posted by FrankKalis

    May I jump right in?

    Am I right, that you want this to find out all events that have CallLog.CallStatus ='closed' at the end of the day and then copy them to another database?
    If so, what is your goal?

    --Frank
    http://www.insidesql.de

    Hi Frank,

    Yes that is correct. All of the new records in the heat database; CallLog table that have a CallStatus of 'closed' are to be copied to another database.
    That is because I need to perform operations to the data and add more data to the rows but I am not allowed to modify in any way shape or form the structure of the original (heat) database.
    To this end I wish to copy across the data I need to a new database.

    Hope that clears it up a bit,

    Regards

    Josh
  25. FrankKalis Moderator

    So, you have two very similar tables in two databases, right? <br />Once a day you copy new data from the original table to your other one and manipulate it according to your needs,right?<br />The data in neither table will get deleted, right?<br />So, why don't you just copy the data from table A that has a Status of 'Closed' and does NOT EXISTS in your second table? And then schedule a job which runs once a day at midnight.<br /><br />Hope I understand all this [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><br />--Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />
  26. incandescant New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />So, why don't you just copy the data from table A that has a Status of 'Closed' and does NOT EXISTS in your second table? And then schedule a job which runs once a day at midnight.<br /><br />Hope I understand all this [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />That is exactly what I want to do but have been unable to do it.<br />I tried setting up a DTS and this SQL code was intended to filter the results.<br /><br />Any pointers (or preferably outright HowTo's)?<br /><br />Regards <br /><br />Josh
  27. FrankKalis Moderator

    Could you post your table structure along with some non-confidental sample data?
    I think we're getting closer to success.


    --Frank
    http://www.insidesql.de
  28. tdong New Member

    Hi Adriaan

    He said he already tested everything already just add the DATE in the where clause.
    The thing is I just eliminate some of his where clause to show him the date. When testing he should put his complete queries to get the result.

    I was only helping him with the statement Getting all the Closed Dates equal to GetDate(). didn't look at his actual CrossJoin above

    May the best cheaters win
  29. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />Could you post your table structure along with some non-confidental sample data?<br />I think we're getting closer to success.<br /><br /><br />--Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Just to add, the NOT EXISTS() in my previous post is intentionally in uppercase [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><br />--Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />
  30. incandescant New Member

    quote:Originally posted by FrankKalis

    Could you post your table structure along with some non-confidental sample data?
    I think we're getting closer to success.

    --Frank
    http://www.insidesql.de

    I can taste the success!
    The original tables are HUGE, the table being copied to looks like

    RequestID UNIQUE INT
    UserEmail VARCHAR
    HelpDeskRef VARCHAR
    CallDate VARCHAR
    CallSummary VARCHAR
    Comment VARCHAR
    CompleteFlag VARCHAR
    Rating VARCHAR
    MailFlag VARCHAR

    After copying an example row would be

    13 user@domain.com 000123 2004-03-08 Unable to access Net <NULL> No <NULL> Yes
  31. Adriaan New Member

    Hi Josh,

    Not sure how the query analyzer would come up with a CROSS JOIN - that's a pretty unusual type of JOIN to use, especially if you're just looking up an e-mail address based on a person's ID code (which I guess is what you're trying to do here).

    It looks as if you're having to learn the T-SQL basics at the same time as setting up some complicated procedures - get the basics down first, then build on that.

    Best of luck!
  32. FrankKalis Moderator

    I guess something like this will work


    SET NOCOUNT ON
    CREATE TABLE myoriginaltable
    (
    RequestID INT PRIMARY KEY
    , UserEmail VARCHAR(50)
    , HelpDeskRef VARCHAR(50)
    , CallDate VARCHAR(20)
    , CallSummary VARCHAR(200)
    , Comment VARCHAR(200)
    , CompleteFlag VARCHAR(3)
    , Rating VARCHAR
    , MailFlag VARCHAR(3)
    )

    CREATE TABLE mysecondtable
    (
    RequestID INT PRIMARY KEY
    , UserEmail VARCHAR(50)
    , HelpDeskRef VARCHAR(50)
    , CallDate VARCHAR(20)
    , CallSummary VARCHAR(200)
    , Comment VARCHAR(200)
    , CompleteFlag VARCHAR(3)
    , Rating VARCHAR
    , MailFlag VARCHAR(3)
    )
    INSERT INTO myoriginaltable values(13,'user@domain.com', '000123','2004-03-08','Unable to access Net',NULL,'No', NULL, 'Yes')
    INSERT INTO mysecondtable
    SELECT
    RequestID
    , UserEmail
    , HelpDeskRef
    , CallDate
    , CallSummary
    , Comment
    , CompleteFlag
    , Rating
    , MailFlag
    FROM
    myoriginaltable
    WHERE NOT EXISTS
    (SELECT * FROM mysecondtable WHERE myoriginaltable.RequestID = mysecondtable.RequestID)

    SELECT * FROM mysecondtable
    INSERT INTO myoriginaltable values(14,'1user@domain.com', '000123','2004-03-08','Unable to access Net',NULL,'No', NULL, 'Yes')
    INSERT INTO mysecondtable
    SELECT
    RequestID
    , UserEmail
    , HelpDeskRef
    , CallDate
    , CallSummary
    , Comment
    , CompleteFlag
    , Rating
    , MailFlag
    FROM
    myoriginaltable
    WHERE NOT EXISTS
    (SELECT * FROM mysecondtable WHERE myoriginaltable.RequestID = mysecondtable.RequestID)
    SELECT * FROM mysecondtable
    DROP TABLE myoriginaltable,mysecondtable
    SET NOCOUNT OFF
    May I add that this design isn't well though over?
    You definitely don't want dates to be stored in VARCHAR(xx) columns. Any report, statistics or whatsoever based on time ranges will suffer from this design. That is your query code will get messy when you CAST or CONVERT to datetime in order to avoid incorrect sorting.

    HTH


    --Frank
    http://www.insidesql.de
  33. incandescant New Member

    Thanks Frank,

    I shall have a go with this.
    Unfortunately the database I am not allowed to touch/modify/look at was badly designed with varchar fields for the dates!

    Nuts I tell ya
  34. incandescant New Member

    Right I've had a look at the code you've written Frank and it all makes sense.
    The problem is I want to take data from a database that I can only read from. This database has the information I require (and more) stored over two tables and I would like to pull out the required fields and plug them into my new database.

    Something along the lines of

    INSERT INTO tblFatRequest VALUES (SELECT heat.Profile.EmailID, heat.CallLog.CallID, heat.CallLog.RecvdDate, heat.CallLog.CallSummary FROM heat.CallLog INNER JOIN heat.Profile)

    but I'd also need to input values not taken from the database such as GETDATE() and NULL values

    Best

    Josh
  35. FrankKalis Moderator

    Consider my little script only as a skeleton for your final query. If you've got the idea behind, that good and the rest shouldn't be a big issue anyway.
    I wouldn't pass values as GETDATE() or NULLS, but rather create equivalent DEFAULTS on the target table, respectively leave the column nullability. Although you can pass such values.


    --Frank
    http://www.insidesql.de

Share This Page