Struggling with fairly complex statement

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

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.

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.

did that work for you?

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>

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

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

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

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

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

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

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

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.

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

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

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.

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

Ah yes, I see!

Thanks for that.

So how do I go about setting GETDATE to return the correct date/format?

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

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

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?

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

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

<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

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

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

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!

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

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