Help with query!!! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help with query!!!

Author

Topic

mlawre10
Starting Member

3 Posts

Posted - 2009-11-18 : 15:24:43

Hi i'm new to the SQL Server forum. Could someone help me with combining this query.... and i want the 1st query to drive the 2nd query with the state field...

INSERT INTO ATT_Main(ban, advoc_id, unc, business_name, units, state, status, logdate)
SELECT a.BAN,
c.ADID AS advoc_id,
a.[STCARE CORPID] AS unc,
a.[STCARE COMPANY NAME] AS business_name,
a.[UNIT COUNT] AS units,
a.STATE,
a.[STCARE STATUS] AS STATUS,
GETDATE() AS logdate
FROM SCT_Master a WITH (NOLOCK)
LEFT JOIN SCT_Rep_To_Mgr_List b WITH (NOLOCK) ON a.[SCS PR ID]=b.[Rep ID]
LEFT JOIN CMT_ADID_SCT_UserRelationship c WITH (NOLOCK) ON a.[SCS PR ID]=c.SCT_UserName
WHERE a.[STCARE STATUS] = 'Active' AND a.[UNIT COUNT] > 0 AND c.ADID = @ADID

INSERT INTO [ATT_Notify]([ADID], [email], [logdate])
SELECT DISTINCT CASE
WHEN b.[Manager ID] IS NULL THEN b.[SR Manager ID]
WHEN b.[Manager ID] = '' THEN b.[SR Manager ID]
ELSE b.[Manager ID]
END AS ADID,
CASE
WHEN d.[Email] IS NULL THEN e.[Email]
WHEN d.[Email] = '' THEN e.[Email]
ELSE d.[Email]
END AS Email,
GETDATE() AS logdate
FROM SCT_Master a WITH (NOLOCK)
LEFT JOIN SCT_Rep_To_Mgr_List b WITH (NOLOCK) ON a.[SCS PR ID]=b.[Rep ID]
LEFT JOIN CMT_ADID_SCT_UserRelationship c WITH (NOLOCK) ON a.[SCS PR ID]=c.[SCT_UserName]
LEFT JOIN SCT_Users d WITH (NOLOCK) ON d.[Username]=b.[Manager ID]
LEFT JOIN SCT_Users e WITH (NOLOCK) ON e.[Username]=b.[SR Manager ID]
WHERE a.[STCARE STATUS] = 'Active'
AND a.[UNIT COUNT] > 0
AND a.[STATE] = @STATE AND
(b.[SR Manager ID] IS NOT NULL AND b.[Manager ID] IS NOT NULL)
Order by ADID

Tomji
Starting Member

19 Posts

Posted - 2009-11-22 : 20:49:07

Usually a good idea is to create the tables that you use as @VariableTables and fill them with sample data that will show the issue you have.
You also don't go into any details about what is failing with your query, however if you created the VariableTables with sample data then you could tell us what you want the result to be. And we can test it. It's also advisable to have the following two statements.
Expected Result:
Actual Result:

Maybe you can do that still if you're still fighting with the query and I and other people would be much more willing to help :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources