TBSupport
asked on
T-SQL CASE Statement
Hello:
Below is a select statement pulling fields of data from another select statement. The "Month" field is formatted and returned as "Month 01", "Month 02", "Month 03", etc. through "Month 18".
When I run this statement against our data, some months legitimately do not appear. Month 02 is an example. There are no purchase orders for that month and, therefore, it does not show when selected.
But, I would like to show fields of data for Month 02 and any other "missing months".
So, I would like to have a single line of data representing Month 02 but have all other fields for that line be blank or 0.
How can I accomplish this?
Truthfully, I started with the second "big" select statement. When I could not find a way to add missing months in it, I "added" the first select statement that is wrapped around the select case statement. (I hope that makes sense.)
Thanks!
TBSupport
select
ContractNum, VENDORID, StartDate, EndDate, Month, ITEMNMBR, ITEMDESC, AvailQuantity, EXTDCOST
FROM(
select RCG.dbo.POContractHeader.C ontractNum , RCG.dbo.POContractHeader.V ENDORID, RCG.dbo.POContractHeader.S tartDate, RCG.dbo.POContractHeader.E ndDate,
'Month ' +
CASE WHEN DATEDIFF(m,GETDATE(),RCG.d bo.POContr actHeader. EndDate) + 1 < 10
THEN CAST(CAST('0' AS VARCHAR) + CAST(DATEDIFF(m,GETDATE(), RCG.dbo.PO ContractHe ader.EndDa te) + 1 AS VARCHAR(1)) AS VARCHAR(2))
ELSE CAST(DATEDIFF(m,GETDATE(), RCG.dbo.POContractHeader.E ndDate) + 1 AS VARCHAR(2))
END
AS Month,
--RCG.dbo.PODraws.PONUMBER ,
RCG.dbo.POContractDetail.I TEMNMBR, RCG.dbo.POContractDetail.I TEMDESC, RCG.dbo.POContractDetail.A vailQuanti ty, RCG.dbo.POContractDetail.E XTDCOST
from RCG.dbo.POContractHeader
INNER JOIN RCG.dbo.POContractDetail on RCG.dbo.POContractHeader.C ontractNum = RCG.dbo.POContractDetail.C ontractNum
and RCG.dbo.POContractHeader.V ENDORID = RCG.dbo.POContractDetail.V ENDORID
INNER JOIN RCG.dbo.PODraws on RCG.dbo.POContractDetail.C ontractNum = RCG.dbo.PODraws.ContractNu m
and RCG.dbo.POContractDetail.V ENDORID = RCG.dbo.PODraws.VENDORID
where RCG.dbo.POContractHeader.S tartDate IS NOT NULL and RCG.dbo.POContractHeader.E ndDate IS NOT NULL
and RCG.dbo.POContractDetail.A vailQuanti ty <> 0
and (RCG.dbo.POContractHeader. EndDate >= DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)) AND
(RCG.dbo.POContractHeader. EndDate < DATEADD(m, DATEDIFF(m, 0, GETDATE()) + 18, 0))
and RCG.dbo.POContractHeader.V ENDORID = '8000028'
GROUP BY RCG.dbo.POContractHeader.V ENDORID,
RCG.dbo.POContractHeader.C ontractNum ,
RCG.dbo.POContractHeader.S tartDate, RCG.dbo.POContractHeader.E ndDate,
RCG.dbo.POContractDetail.I TEMNMBR, RCG.dbo.POContractDetail.I TEMDESC, RCG.dbo.POContractDetail.A vailQuanti ty, RCG.dbo.POContractDetail.E XTDCOST--,
--RCG.dbo.PODraws.PONUMBER ,
) as test
--ORDER BY RCG.dbo.POContractHeader.V ENDORID, RCG.dbo.POContractHeader.E ndDate
Below is a select statement pulling fields of data from another select statement. The "Month" field is formatted and returned as "Month 01", "Month 02", "Month 03", etc. through "Month 18".
When I run this statement against our data, some months legitimately do not appear. Month 02 is an example. There are no purchase orders for that month and, therefore, it does not show when selected.
But, I would like to show fields of data for Month 02 and any other "missing months".
So, I would like to have a single line of data representing Month 02 but have all other fields for that line be blank or 0.
How can I accomplish this?
Truthfully, I started with the second "big" select statement. When I could not find a way to add missing months in it, I "added" the first select statement that is wrapped around the select case statement. (I hope that makes sense.)
Thanks!
TBSupport
select
ContractNum, VENDORID, StartDate, EndDate, Month, ITEMNMBR, ITEMDESC, AvailQuantity, EXTDCOST
FROM(
select RCG.dbo.POContractHeader.C
'Month ' +
CASE WHEN DATEDIFF(m,GETDATE(),RCG.d
THEN CAST(CAST('0' AS VARCHAR) + CAST(DATEDIFF(m,GETDATE(),
ELSE CAST(DATEDIFF(m,GETDATE(),
END
AS Month,
--RCG.dbo.PODraws.PONUMBER
RCG.dbo.POContractDetail.I
from RCG.dbo.POContractHeader
INNER JOIN RCG.dbo.POContractDetail on RCG.dbo.POContractHeader.C
and RCG.dbo.POContractHeader.V
INNER JOIN RCG.dbo.PODraws on RCG.dbo.POContractDetail.C
and RCG.dbo.POContractDetail.V
where RCG.dbo.POContractHeader.S
and RCG.dbo.POContractDetail.A
and (RCG.dbo.POContractHeader.
(RCG.dbo.POContractHeader.
and RCG.dbo.POContractHeader.V
GROUP BY RCG.dbo.POContractHeader.V
RCG.dbo.POContractHeader.C
RCG.dbo.POContractHeader.S
RCG.dbo.POContractDetail.I
--RCG.dbo.PODraws.PONUMBER
) as test
--ORDER BY RCG.dbo.POContractHeader.V
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, All, for the responses!
Sharath_123>> I think that your "[Month]" is out of place. Can you check, again, and update the case?
Also, where is "t2"?
TBSupport
Sharath_123>> I think that your "[Month]" is out of place. Can you check, again, and update the case?
Also, where is "t2"?
TBSupport
ASKER
chaau>>Your solution works really well! Thank you! Question: Is there a way for me to get the EXTDCOST to return "0" instead of "NULL"?
Sure, use ISNULL(), like this:
.....
select
ContractNum, VENDORID, StartDate, EndDate, m.Month, ITEMNMBR, ITEMDESC, AvailQuantity, ISNULL(EXTDCOST,0) AS EXTDCOST
......
>> I think that your "[Month]" is out of place.
What do you mean?
>> Also, where is "t2"?
replace t2 with test.
What do you mean?
>> Also, where is "t2"?
replace t2 with test.
Open in new window