MS SQL 2005 Show data in month Sequence

The below mentioned query is showing data according to below mentioned result, is it possible it is show in month order

Query:
SELECT      Months
            ,LB
            ,Qty=sum(qty)
            ,Qty15c=sum(Qty15c)
            ,Tankers=Sum(Tankers)
--Documents Status
            ,DocsRcvd=Sum(DocsRcvd)
            ,DocsBOPOT=sum(DocsBOPOT)
            ,DocsSent=sum(DocsSent)
            ,DocsBIH=sum(DocsBIH)
            ,DocsBOA=sum(DocsBOA)
--Permit Status
            ,PR=sum(pr)
            ,PNRFT=sum(PNRFT)
            ,PS=sum(PS)
            ,PSTV=sum(PSTV)
            ,PRFV=sum(PRFV)
            ,PBIV=sum(PBIV)
--Accident Tankers
            ,ADT=Sum(ADT)
            ,APOPOT=Sum(APOPOT)
            ,APC=sum(APC)
From
(SELECT      Months=RIGHT(CONVERT(varchar, loadon,6),6)
            ,LB
            ,Qty=sum(qty)
            ,Qty15c=sum(Qty15c)
            ,Tankers=Count(TrailerNo)
--Documents Status
            ,DocsRcvd=Count(PODRcvdOn)
            ,DocsBOPOT=Count(TrailerNo)-Count(PODRcvdOn)
            ,DocsSent=COUNT(DOCSSENTON)
            ,DocsBIH=Count(PODRcvdOn)-COUNT(DOCSSENTON)
            ,DocsBOA=Count(TrailerNo)-COUNT(DOCSSENTON)
--Permit Status
            ,PR=Case When loadon >='2/1/2008' and PermitRcvd='1'  then Count(PermitRcvd) else 0 end
            ,PNRFT=Case When loadon >='2/1/2008' and PermitRcvd='0' then Count(PermitRcvd) else 0 end
            ,PS=count(PermitSentOn)
            ,PSTV=count(PermitVenusSentOn)
            ,PRFV=count(PermitVenusRcvdOn)
            ,PBIV=count(PermitVenusSentOn)-count(PermitVenusRcvdOn)
--Accident Tankers
            ,ADT=Count(AccidentDt)
            ,APOPOT=Count(AccidentDt)-Count(AccidentPC)
            ,APC=Count(AccidentPC)
from SupremeFuelDet
Where Del='0'
group by Loadon,PermitRcvd,lb)X
group by months,lb
Order by lb,months

Result:

8-Apr      ARL-Rawalpindi      5912000                       5830269
8-Aug      ARL-Rawalpindi      11169000      11120684
7-Dec      ARL-Rawalpindi      9407000                        9290888
8-Feb      ARL-Rawalpindi      8465000                        8320878
8-Jan      ARL-Rawalpindi      8473000                        8388693
8-Jul      ARL-Rawalpindi      10792000      10751330
8-Jun      ARL-Rawalpindi      9442000                        9391963
8-Mar      ARL-Rawalpindi      10025000         9824312
8-May      ARL-Rawalpindi      8462000                         8413887
7-Nov      ARL-Rawalpindi      5171000                         5074056
8-Nov      ARL-Rawalpindi      1199000                         1177072
8-Oct      ARL-Rawalpindi      6711000                         6614246



MehramAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:

SELECT      Months
            ,LB
            ,Qty=sum(qty)
            ,Qty15c=sum(Qty15c)
            ,Tankers=Sum(Tankers)
--Documents Status
            ,DocsRcvd=Sum(DocsRcvd)
            ,DocsBOPOT=sum(DocsBOPOT)
            ,DocsSent=sum(DocsSent)
            ,DocsBIH=sum(DocsBIH)
            ,DocsBOA=sum(DocsBOA)
--Permit Status
            ,PR=sum(pr)
            ,PNRFT=sum(PNRFT)
            ,PS=sum(PS)
            ,PSTV=sum(PSTV)
            ,PRFV=sum(PRFV)
            ,PBIV=sum(PBIV)
--Accident Tankers
            ,ADT=Sum(ADT)
            ,APOPOT=Sum(APOPOT)
            ,APC=sum(APC)
From
(SELECT      Months=RIGHT(CONVERT(varchar, loadon,6),6)
            ,MonthNo = month(loadon)
            ,LB
            ,Qty=sum(qty)
            ,Qty15c=sum(Qty15c)
            ,Tankers=Count(TrailerNo)
--Documents Status
            ,DocsRcvd=Count(PODRcvdOn)
            ,DocsBOPOT=Count(TrailerNo)-Count(PODRcvdOn)
            ,DocsSent=COUNT(DOCSSENTON)
            ,DocsBIH=Count(PODRcvdOn)-COUNT(DOCSSENTON)
            ,DocsBOA=Count(TrailerNo)-COUNT(DOCSSENTON)
--Permit Status
            ,PR=Case When loadon >='2/1/2008' and PermitRcvd='1'  then Count(PermitRcvd) else 0 end
            ,PNRFT=Case When loadon >='2/1/2008' and PermitRcvd='0' then Count(PermitRcvd) else 0 end
            ,PS=count(PermitSentOn)
            ,PSTV=count(PermitVenusSentOn)
            ,PRFV=count(PermitVenusRcvdOn)
            ,PBIV=count(PermitVenusSentOn)-count(PermitVenusRcvdOn)
--Accident Tankers
            ,ADT=Count(AccidentDt)
            ,APOPOT=Count(AccidentDt)-Count(AccidentPC)
            ,APC=Count(AccidentPC)
from SupremeFuelDet
Where Del='0'
group by Loadon,PermitRcvd,lb)X
group by months,lb
Order by lb,MonthNo

MehramAuthor Commented:
Msg 207, Level 16, State 1, Line 51
Invalid column name 'monthno'.
Aneesh RetnakaranDatabase AdministratorCommented:
are u sure u tried the exact query i have sent




SELECT      Months
            ,LB
            ,Qty=sum(qty)
            ,Qty15c=sum(Qty15c)
            ,Tankers=Sum(Tankers)
--Documents Status
            ,DocsRcvd=Sum(DocsRcvd)
            ,DocsBOPOT=sum(DocsBOPOT)
            ,DocsSent=sum(DocsSent)
            ,DocsBIH=sum(DocsBIH)
            ,DocsBOA=sum(DocsBOA)
--Permit Status
            ,PR=sum(pr)
            ,PNRFT=sum(PNRFT)
            ,PS=sum(PS)
            ,PSTV=sum(PSTV)
            ,PRFV=sum(PRFV)
            ,PBIV=sum(PBIV) 
--Accident Tankers
            ,ADT=Sum(ADT)
            ,APOPOT=Sum(APOPOT)
            ,APC=sum(APC)
From
(SELECT      Months=RIGHT(CONVERT(varchar, loadon,6),6)
            ,MonthNo = month(loadon)
            ,LB
            ,Qty=sum(qty)
            ,Qty15c=sum(Qty15c)
            ,Tankers=Count(TrailerNo)
--Documents Status
            ,DocsRcvd=Count(PODRcvdOn)
            ,DocsBOPOT=Count(TrailerNo)-Count(PODRcvdOn)
            ,DocsSent=COUNT(DOCSSENTON)
            ,DocsBIH=Count(PODRcvdOn)-COUNT(DOCSSENTON)
            ,DocsBOA=Count(TrailerNo)-COUNT(DOCSSENTON)
--Permit Status
            ,PR=Case When loadon >='2/1/2008' and PermitRcvd='1'  then Count(PermitRcvd) else 0 end
            ,PNRFT=Case When loadon >='2/1/2008' and PermitRcvd='0' then Count(PermitRcvd) else 0 end
            ,PS=count(PermitSentOn)
            ,PSTV=count(PermitVenusSentOn)
            ,PRFV=count(PermitVenusRcvdOn)
            ,PBIV=count(PermitVenusSentOn)-count(PermitVenusRcvdOn) 
--Accident Tankers
            ,ADT=Count(AccidentDt)
            ,APOPOT=Count(AccidentDt)-Count(AccidentPC)
            ,APC=Count(AccidentPC)
from SupremeFuelDet
Where Del='0'
group by Loadon,PermitRcvd,lb)X
group by months,lb
Order by lb, x.MonthNo

Open in new window

Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

MehramAuthor Commented:
Open in New Window Select All Accept Multiple Solutions Accept as Solution
could not understand, please help
Aneesh RetnakaranDatabase AdministratorCommented:
if you check the inner query, i have added a new column there "MonthNo" and in the outer query i am ordering by that column


SELECT      Months
            ,MonthNo
            ,LB
            ,Qty=sum(qty)
            ,Qty15c=sum(Qty15c)
            ,Tankers=Sum(Tankers)
--Documents Status
            ,DocsRcvd=Sum(DocsRcvd)
            ,DocsBOPOT=sum(DocsBOPOT)
            ,DocsSent=sum(DocsSent)
            ,DocsBIH=sum(DocsBIH)
            ,DocsBOA=sum(DocsBOA)
--Permit Status
            ,PR=sum(pr)
            ,PNRFT=sum(PNRFT)
            ,PS=sum(PS)
            ,PSTV=sum(PSTV)
            ,PRFV=sum(PRFV)
            ,PBIV=sum(PBIV)
--Accident Tankers
            ,ADT=Sum(ADT)
            ,APOPOT=Sum(APOPOT)
            ,APC=sum(APC)
From
(SELECT      Months=RIGHT(CONVERT(varchar, loadon,6),6)
            ,MonthNo = month(loadon)
            ,LB
            ,Qty=sum(qty)
            ,Qty15c=sum(Qty15c)
            ,Tankers=Count(TrailerNo)
--Documents Status
            ,DocsRcvd=Count(PODRcvdOn)
            ,DocsBOPOT=Count(TrailerNo)-Count(PODRcvdOn)
            ,DocsSent=COUNT(DOCSSENTON)
            ,DocsBIH=Count(PODRcvdOn)-COUNT(DOCSSENTON)
            ,DocsBOA=Count(TrailerNo)-COUNT(DOCSSENTON)
--Permit Status
            ,PR=Case When loadon >='2/1/2008' and PermitRcvd='1'  then Count(PermitRcvd) else 0 end
            ,PNRFT=Case When loadon >='2/1/2008' and PermitRcvd='0' then Count(PermitRcvd) else 0 end
            ,PS=count(PermitSentOn)
            ,PSTV=count(PermitVenusSentOn)
            ,PRFV=count(PermitVenusRcvdOn)
            ,PBIV=count(PermitVenusSentOn)-count(PermitVenusRcvdOn)
--Accident Tankers
            ,ADT=Count(AccidentDt)
            ,APOPOT=Count(AccidentDt)-Count(AccidentPC)
            ,APC=Count(AccidentPC)
from SupremeFuelDet
Where Del='0'
group by Loadon,PermitRcvd,lb)X
group by months,lb
Order by lb,Months, X.MonthNo
MehramAuthor Commented:
Sir it is working but the now problem is year for example 2007 will be up then 2008
Aneesh RetnakaranDatabase AdministratorCommented:
Can u post the sample result you are getting and the expected results
MehramAuthor Commented:
Result
Apr 08      4      ARL-Rawalpindi      5912000.00      5830269.00
Aug 08      8      ARL-Rawalpindi      11169000.00      11120684.00
Dec 07      12      ARL-Rawalpindi      9407000.00      9290888.00
Feb 08      2      ARL-Rawalpindi      8465000.00      8320878.00
Jan 08      1      ARL-Rawalpindi      8473000.00      8388693.00
Jul 08      7      ARL-Rawalpindi      10792000.00      10751330.00
Jun 08      6      ARL-Rawalpindi      9442000.00      9391963.00
Aneesh RetnakaranDatabase AdministratorCommented:
U meant to say that you dont really need the results for year 2008 ?
in that case why can;t you put a where condition in the inner query ?
MehramAuthor Commented:
No, I want to say it show in sequance 2007 & 2008, if see it is showing 2007 in middel
Aneesh RetnakaranDatabase AdministratorCommented:
did u try changing the ordedr by part

Order by lb, X.MonthNo
MehramAuthor Commented:
yes, but result same
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT      Months
            ,MaxDate
            ,LB
            ,Qty=sum(qty)
            ,Qty15c=sum(Qty15c)
            ,Tankers=Sum(Tankers)
--Documents Status
            ,DocsRcvd=Sum(DocsRcvd)
            ,DocsBOPOT=sum(DocsBOPOT)
            ,DocsSent=sum(DocsSent)
            ,DocsBIH=sum(DocsBIH)
            ,DocsBOA=sum(DocsBOA)
--Permit Status
            ,PR=sum(pr)
            ,PNRFT=sum(PNRFT)
            ,PS=sum(PS)
            ,PSTV=sum(PSTV)
            ,PRFV=sum(PRFV)
            ,PBIV=sum(PBIV)
--Accident Tankers
            ,ADT=Sum(ADT)
            ,APOPOT=Sum(APOPOT)
            ,APC=sum(APC)
From
(SELECT      Months=RIGHT(CONVERT(varchar, loadon,6),6)
            ,MaxDate = Max(loadon)
            ,LB
            ,Qty=sum(qty)
            ,Qty15c=sum(Qty15c)
            ,Tankers=Count(TrailerNo)
--Documents Status
            ,DocsRcvd=Count(PODRcvdOn)
            ,DocsBOPOT=Count(TrailerNo)-Count(PODRcvdOn)
            ,DocsSent=COUNT(DOCSSENTON)
            ,DocsBIH=Count(PODRcvdOn)-COUNT(DOCSSENTON)
            ,DocsBOA=Count(TrailerNo)-COUNT(DOCSSENTON)
--Permit Status
            ,PR=Case When loadon >='2/1/2008' and PermitRcvd='1'  then Count(PermitRcvd) else 0 end
            ,PNRFT=Case When loadon >='2/1/2008' and PermitRcvd='0' then Count(PermitRcvd) else 0 end
            ,PS=count(PermitSentOn)
            ,PSTV=count(PermitVenusSentOn)
            ,PRFV=count(PermitVenusRcvdOn)
            ,PBIV=count(PermitVenusSentOn)-count(PermitVenusRcvdOn)
--Accident Tankers
            ,ADT=Count(AccidentDt)
            ,APOPOT=Count(AccidentDt)-Count(AccidentPC)
            ,APC=Count(AccidentPC)
from SupremeFuelDet
Where Del='0'
group by Loadon,PermitRcvd,lb)X
group by months,lb
Order by lb,X.MaxDate

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.