Related to Miscrosoft SQL

Question

0.00/5 (No votes)

See more:

A process change that necessitates the insertion of a new
record in the Activity table for each Unique_ID in the SLTax table
having the following characteristics:

JOIN on SLTax.Unique_ID = Activity.Unique_ID

SLTax Criteria:

Covge_Eff_Dt > '12/31/2008'
Payto code IN ('1216AA', '1772AA')
Payb_status = 'O'

Activity Criteria:

Check_ID = 10
For max.check_date, Is_Present = 0

INSERT a new record in Activity for each Unique_ID meeting the above
criteria:
Check_ID = 10
User_ID = 45
Date = current date
Is_Present = 1

The follwoing query is used to fetch the data but i am not able to retrive it and Insert. the Query is as follows

SQL

SELECT     dbo.Activity.*
FROM         dbo.Activity INNER JOIN
                      dbo.SLTax ON dbo.Activity.Unique_ID = dbo.SLTax.Unique_ID
WHERE     ('Check_Date' IN
                          (SELECT     MAX('Check_Date')
                            FROM          Activity)) AND (dbo.Activity.Check_ID = 10) AND (dbo.Activity.Is_Present = 'o') AND (dbo.SLTax.Cvge_Eff_Dt > CONVERT(DATETIME, 
                      '2008-12-31 00:00:00', 102)) AND (dbo.SLTax.Payto_Cd IN ('1216AA', '17772AA')) AND (dbo.SLTax.Payb_Status = 'o')

can I get help regarding this

Posted 15-May-12 22:58pm

mmsgk251804

Updated 16-May-12 1:03am

P.Salini

v3

Add a Solution

Comments

ssd_coolguy 16-May-12 5:16am

hey dude, you can not use aggregate function in where clause.
if you want condition on aggregate function you can use group by clause.

Maciej Los 16-May-12 7:28am

What kind of fields do you want to insert and into which table?

2 solutions

Solution 1

You should apply Group By and Heaving Clause while working with aggregate funcitons.

Hope this link will Help you :

http://blog.sqlauthority.com/2007/07/04/sql-server-definition-comparison-and-difference-between-having-and-where-clause/[^]

Posted 15-May-12 23:08pm

sunandandutt

Comments

mmsgk251804 16-May-12 8:15am

Thank you

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

mmsgk251804 · Accepted Answer · 2012-05-16T02:14:00

SQL

SELECT     dbo.Activity.*
FROM         dbo.Activity INNER JOIN
                      dbo.SLTax ON dbo.Activity.Unique_ID = dbo.SLTax.Unique_ID
WHERE     (dbo.Activity.Check_ID = 10) AND (dbo.Activity.Is_Present = '0') AND (dbo.SLTax.Cvge_Eff_Dt > CONVERT(DATETIME, '2008-12-31 00:00:00', 102)) AND
                      (dbo.SLTax.Payto_Cd IN ('1216AA', '17772AA')) AND (dbo.SLTax.Payb_Status = 'O') AND ('Check_Date' IN
                          (SELECT     MAX('Check_Date')
                            FROM          Activity))