SQl Query Help - 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)

SQl Query Help

Author

Topic

itnagaraj
Yak Posting Veteran

70 Posts

Posted - 2011-05-03 : 10:00:35

SQl query returns only two rows.For example
Id Name Jan Feb
2 Test2 10 20
5 Test5 100 200

By default,i want 6 rows.For example
Id Name Jan Feb
1 Test1 0 0
2 Test2 10 20
3 Test3 0 0
4 Tset4 0 0
5 Test5 100 200
6 Test6 0 0

how to write sql query above synarieo.

V.NAGARAJAN

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-03 : 10:02:57

Show the query that returns only two rows.

No, you're never too old to Yak'n'Roll if you're too young to die.

itnagaraj
Yak Posting Veteran

70 Posts

Posted - 2011-05-03 : 10:20:39

The below sql does not return any rows.
Select A.SlNo,A.Detail,Sum(A.Jan) Jan,Sum(A.Feb) Feb,Sum(A.Mar) Mar,Sum(A.Apr) Apr,Sum(A.May) May,
Sum(A.Jun) Jun,Sum(A.Jul) Jul,Sum(A.Aug) Aug,Sum(A.Sep) Sep,Sum(A.Oct) Oct,Sum(A.Nov) Nov,Sum(A.[Dec]) [Dec],Sum(A.Total) Total From
(
Select '3' SlNo,
'Equipment Pending' [Detail],
isnull(cast([1]as int),0) As Jan,
isnull(cast([2]as int),0) As Feb,
isnull(cast([3]as int),0) As Mar,
isnull(cast([4]as int),0) As Apr,
isnull(cast([5]as int),0) As May,
isnull(cast([6]as int),0) As Jun,
isnull(cast([7]as int),0) As Jul,
isnull(cast(

as int),0) As Aug,
isnull(cast([9]as int),0) As Sep,
isnull(cast([10]as int),0) As Oct,
isnull(cast([11]as int),0) As Nov,
isnull(cast([12]as int),0) As [Dec]
, isnull(cast([1]as int),0) + isnull(cast([2]as int),0) + isnull(cast([3]as int),0) + isnull(cast([4]as int),0)
+ isnull(cast([5]as int),0) + isnull(cast([6]as int),0) + isnull(cast([7]as int),0) + isnull(cast(

as int),0)
+ isnull(cast([9]as int),0) + isnull(cast([10]as int),0) + isnull(cast([11]as int),0) + isnull(cast([12]as int),0)
As Total
From
(
SELECT Month(a.createdDateTime) Mon,
a.id,
b.description,
CONVERT(VARCHAR(10), a.createdDateTime, 101) as submittedOn,
CONVERT(VARCHAR(10), a.requestDueDate , 101) as requestDueDate,
d.userFirstName ,
d.userLastName,
c.workflowEventDescription,
case when a.studioid is null then I.studioId else a.studioid end as assignedStudio,
CONVERT(VARCHAR(10), a.assignedDatetime, 101) as assignedDate,
a.requestStatus,
case when cast(case when a.requestStatus in (1,2) and a.equipment=0.00 then a.equipment else a.equipment end as varchar) = '0.00' then ''
else cast(case when a.requestStatus in (1,2) and a.equipment=0.00 then a.equipment else a.equipment end as varchar)
end as equipment,
case when cast(case when a.requestStatus in (1,2) and a.labour=0.00 then a.labour else a.labour end as varchar) = '0.00' then ''
else cast(case when a.requestStatus in (1,2) and a.labour=0.00 then a.labour else a.labour end as varchar)
end as labour,
a.soldOrLost,
a.assignedUser ,
a.equipment as equipment1,
a.labour as labour1
FROM tblRequestDetails a
join tbllookupdetails b on a.requestTypeId = b.id
join tblWorkflowEvents c on a.workflowStatus = c.id
left outer join tblUserDetails d on a.requestorid = d.userId
left outer join tblIndustryDetails I on I.id = a.companyIndustry
Where 1 = 1 and a.isactive = 1
and YEAR(a.createdDateTime) = YEAR(getdate()) - 1
--and a.requestStatus in (1,2,3)
and a.requestStatus = 4 AND soldOrLost = 1
and a.equipment is not null
and a.equipment > 0
) up
pivot (
Sum(equipment1) For Mon IN([1], [2], [3], [4], [5], [6],[7],

, [9], [10], [11], [12])
) As P1
) A
Group By A.SlNo,A.Detail

but i want below result
SlNo Detail Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total
3 Equipment Pending 0 0 0 0 10 0 1 0 0 0 0 0 0

quote:
Originally posted by webfred

Show the query that returns only two rows.

No, you're never too old to Yak'n'Roll if you're too young to die.

V.NAGARAJAN

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources