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?
 
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
0
 
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

0
 
MehramAuthor Commented:
Msg 207, Level 16, State 1, Line 51
Invalid column name 'monthno'.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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

0
 
MehramAuthor Commented:
Open in New Window Select All Accept Multiple Solutions Accept as Solution
could not understand, please help
0
 
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
0
 
MehramAuthor Commented:
Sir it is working but the now problem is year for example 2007 will be up then 2008
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Can u post the sample result you are getting and the expected results
0
 
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
0
 
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 ?
0
 
MehramAuthor Commented:
No, I want to say it show in sequance 2007 & 2008, if see it is showing 2007 in middel
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
did u try changing the ordedr by part

Order by lb, X.MonthNo
0
 
MehramAuthor Commented:
yes, but result same
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.