July 31, 2009 at 5:29 am
The From segment is the same except for AND I.IT_Code, which is different in both statements.
Is there a way?
Thanks
SET @LOSS_SRIP = (Select Count(*)
From (
Select Count(*) As cnt
FROM dbo.tblContracts C WITH(NOLOCK)
INNER JOIN dbo.tblSoldiers S WITH(NOLOCK) ON C.SSN = S.SSN
INNER JOIN dbo.tblSoldier_Status_Actions SA WITH(NOLOCK) ON S.SSN = SA.SSN And SA.Contract_ID = C.Contract_ID
Inner JOIN dbo.tLookup_Incentive I WITH(NOLOCK) ON I.IT_ID = C.IT_Code
WHERE SA.ACTION_CODE='SOLD_000003' AND C.ContractStatus = 'A' AND SA.ACTION_TAKEN_BY is null AND S.UnitState = 'AZ' AND I.IT_Code = 'SRIP'
GROUP BY
S.SSN,
S.Name,
S.UnitState,
S.UPC,
S.PMOS,
S.DMOS,
S.RCC,
S.MPC,
C.Contract_ID,
C.ControlNumber,
C.IT_CODE,
I.IT_Code) As LOSS_SRIP)
Set @LOSS_TA = (Select Count(*)
From (
Select Count(*) As cnt
FROM dbo.tblContracts C WITH(NOLOCK)
INNER JOIN dbo.tblSoldiers S WITH(NOLOCK) ON C.SSN = S.SSN
INNER JOIN dbo.tblSoldier_Status_Actions SA WITH(NOLOCK) ON S.SSN = SA.SSN And SA.Contract_ID = C.Contract_ID
Inner JOIN dbo.tLookup_Incentive I WITH(NOLOCK) ON I.IT_ID = C.IT_Code
WHERE SA.ACTION_CODE='SOLD_000003' AND C.ContractStatus = 'A' AND SA.ACTION_TAKEN_BY is null AND S.UnitState = 'AZ' AND I.IT_Code = 'TA'
GROUP BY
S.SSN,
S.Name,
S.UnitState,
S.UPC,
S.PMOS,
S.DMOS,
S.RCC,
S.MPC,
C.Contract_ID,
C.ControlNumber,
C.IT_CODE,
I.IT_Code) As LOSS_TA)
July 31, 2009 at 5:54 am
It's early but here is a quick shot.
Select @LOSS_TA = sum(CASE WHEN I.IT_CODE = 'TA' THEN 1 ELSE 0 END),
@LOSS_SRIP = SUM(CASE WHEN I.IT_CODE = 'SRIP' THEN 1 ELSE 0 END)
FROM dbo.tblContracts C WITH(NOLOCK)
INNER JOIN dbo.tblSoldiers S WITH(NOLOCK) ON C.SSN = S.SSN
INNER JOIN dbo.tblSoldier_Status_Actions SA WITH(NOLOCK) ON S.SSN = SA.SSN And SA.Contract_ID = C.Contract_ID
Inner JOIN dbo.tLookup_Incentive I WITH(NOLOCK) ON I.IT_ID = C.IT_Code
WHERE SA.ACTION_CODE='SOLD_000003' AND C.ContractStatus = 'A' AND SA.ACTION_TAKEN_BY is null AND S.UnitState = 'AZ' AND I.IT_Code in ('TA','SRIP')
GROUP BY
S.SSN,
S.Name,
S.UnitState,
S.UPC,
S.PMOS,
S.DMOS,
S.RCC,
S.MPC,
C.Contract_ID,
C.ControlNumber,
C.IT_CODE,
I.IT_Code
If this doesn't work could you post the table create scripts and some sample data?
July 31, 2009 at 6:04 am
This is the result I get from running your query statement:
However, this is not what I want. My query should give:
Loss_TA Loss_SRIP
______________________
3 12
01
02
01
01
02
01
02
01
01
20
20
20
01
02
01
July 31, 2009 at 6:13 am
It could be that the caffine is still not completely into my system yet but I am not sure what results you are expecting. Do you want multiple rows or just counts returned to a variable?
July 31, 2009 at 6:24 am
I just want counts returned to two variables displayed in a single row.
Thanks
July 31, 2009 at 6:45 am
Can you change the script below to reflect the table structures you are have.
CREATE TABLE #tblContracts (Contract_ID INT, ControlNumber INT,
ContractStatus CHAR(1), SSN CHAR(11), IT_Code VARCHAR(5))
INSERT INTO #tblContracts VALUES (1, 1, 'A', '111-11-1111','TA')
INSERT INTO #tblContracts VALUES (2, 1, 'A', '222-22-2222','TA')
INSERT INTO #tblContracts VALUES (3, 1, 'A', '333-33-3333','SRIP')
INSERT INTO #tblContracts VALUES (4, 1, 'A', '444-44-4444','SRIP')
INSERT INTO #tblContracts VALUES (5, 1, 'A', '555-55-5555','TA')
CREATE TABLE #tblSoldiers (SSN CHAR(11), Name VARCHAR(30),
UnitState CHAR(2), UPC INT, PMOS INT,
DMOS INT, RCC INT, MPC INT)
INSERT INTO #tblSoldiers VALUES ('111-11-1111','test guy 1', 'AZ', 1, 1, 1, 1, 1)
INSERT INTO #tblSoldiers VALUES ('222-22-2222','test guy 2', 'AZ', 1, 1, 1, 1, 1)
INSERT INTO #tblSoldiers VALUES ('333-33-3333','test guy 3', 'AZ', 1, 1, 1, 1, 1)
INSERT INTO #tblSoldiers VALUES ('444-44-4444','test guy 4', 'AZ', 1, 1, 1, 1, 1)
INSERT INTO #tblSoldiers VALUES ('555-55-5555','test guy 5', 'AZ', 1, 1, 1, 1, 1)
CREATE TABLE #tblSoldier_Status_Actions (SSN CHAR(11), Contract_ID INT,
ACTION_CODE VARCHAR(50), ACTION_TAKEN_BY INT)
INSERT INTO #tblSoldier_Status_Actions VALUES ('111-11-1111', 1, 'SOLD_000003', NULL)
INSERT INTO #tblSoldier_Status_Actions VALUES ('222-22-2222', 2, 'SOLD_000003', NULL)
INSERT INTO #tblSoldier_Status_Actions VALUES ('333-33-3333', 3, 'SOLD_000003', NULL)
INSERT INTO #tblSoldier_Status_Actions VALUES ('444-44-4444', 4, 'SOLD_000003', NULL)
INSERT INTO #tblSoldier_Status_Actions VALUES ('555-55-5555', 5, 'SOLD_000003', NULL)
CREATE TABLE #tLookup_Incentive (IT_ID VARCHAR(5), IT_Code VARCHAR(5))
INSERT INTO #tLookup_Incentive VALUES ('TA','TA')
INSERT INTO #tLookup_Incentive VALUES ('SRIP','SRIP')
DECLARE @LOSS_TA INT
DECLARE @LOSS_SRIP INT
Select @LOSS_TA = sum(CASE WHEN I.IT_Code = 'TA' THEN 1 ELSE 0 END),
@LOSS_SRIP = SUM(CASE WHEN I.IT_Code = 'SRIP' THEN 1 ELSE 0 END)
FROM #tblContracts C WITH(NOLOCK)
INNER JOIN #tblSoldiers S WITH(NOLOCK) ON C.SSN = S.SSN
INNER JOIN #tblSoldier_Status_Actions SA WITH(NOLOCK) ON S.SSN = SA.SSN And SA.Contract_ID = C.Contract_ID
Inner JOIN #tLookup_Incentive I WITH(NOLOCK) ON I.IT_ID = C.IT_Code
WHERE SA.ACTION_CODE='SOLD_000003' AND C.ContractStatus = 'A' AND SA.ACTION_TAKEN_BY is null AND S.UnitState = 'AZ' AND I.IT_Code in ('TA','SRIP')
GROUP BY
S.SSN,
S.Name,
S.UnitState,
S.UPC,
S.PMOS,
S.DMOS,
S.RCC,
S.MPC,
C.Contract_ID,
C.ControlNumber,
C.IT_Code,
I.IT_Code
SELECT @LOSS_SRIP, @LOSS_TA
This does only return 1 row but I am sure the data isn't correct.
Also I am interested in this line:
Inner JOIN #tLookup_Incentive I WITH(NOLOCK) ON I.IT_ID = C.IT_Code
Both the tLookup_Incentive table and the tblContracts table have an IT_Code but that is not what is used in the join. Just trying to figure this out.
July 31, 2009 at 7:03 am
Thank you so much for your efforts. I just wanted to mke sure that the alternative to having two statements does not yield better performance.
July 31, 2009 at 9:17 am
This is the correct answer:
This gives the result I am looking for:
Select LOSS_SRIP = sum(case I.IT_Code when 'SRIP' then 1 else 0 end),
LOSS_TA = sum(case I.IT_Code when 'TA' then 1 else 0 end),
LOSS_Kicker = sum(case I.IT_Code when 'Kicker' then 1 else 0 end),
LOSS_Loan = sum(case I.IT_Code when 'SLRP' then 1 else 0 end)
FROM
(
SELECT I.IT_Code
FROM dbo.tblContracts C WITH(NOLOCK)
INNER JOIN dbo.tblSoldiers S WITH(NOLOCK) ON C.SSN = S.SSN
INNER JOIN dbo.tblSoldier_Status_Actions SA WITH(NOLOCK) ON S.SSN = SA.SSN And SA.Contract_ID = C.Contract_ID
INNER JOIN dbo.tLookup_Incentive I WITH(NOLOCK) ON I.IT_ID = C.IT_Code
WHERE SA.ACTION_CODE='SOLD_000003'
AND C.ContractStatus = 'A' AND S.UnitState = 'AZ'AND SA.ACTION_TAKEN_BY is null
GROUP BY
S.SSN,
C.Contract_ID,
I.IT_Code
) I
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply