• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 440
  • Last Modified:

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



0
Mehram
Asked:
Mehram
  • 7
  • 6
1 Solution
 
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
 
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now