Solved

MS SQL 2005 Show data in month Sequence

Posted on 2008-11-11
431 Views
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
,APOPOT=Sum(APOPOT)
,APC=sum(APC)
From
,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
,APOPOT=Count(AccidentDt)-Count(AccidentPC)
,APC=Count(AccidentPC)
from SupremeFuelDet
Where Del='0'
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
Question by:Mehram

LVL 75

Expert Comment

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
,APOPOT=Sum(APOPOT)
,APC=sum(APC)
From
,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
,APOPOT=Count(AccidentDt)-Count(AccidentPC)
,APC=Count(AccidentPC)
from SupremeFuelDet
Where Del='0'
group by months,lb
Order by lb,MonthNo

0

Author Comment

Msg 207, Level 16, State 1, Line 51
Invalid column name 'monthno'.
0

LVL 75

Expert Comment

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

,APOPOT=Sum(APOPOT)

,APC=sum(APC)

From

,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

,APOPOT=Count(AccidentDt)-Count(AccidentPC)

,APC=Count(AccidentPC)

from SupremeFuelDet

Where Del='0'

group by months,lb

Order by lb, x.MonthNo

0

Author Comment

Open in New Window Select All Accept Multiple Solutions Accept as Solution
0

LVL 75

Expert Comment

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
,APOPOT=Sum(APOPOT)
,APC=sum(APC)
From
,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
,APOPOT=Count(AccidentDt)-Count(AccidentPC)
,APC=Count(AccidentPC)
from SupremeFuelDet
Where Del='0'
group by months,lb
Order by lb,Months, X.MonthNo
0

Author Comment

Sir it is working but the now problem is year for example 2007 will be up then 2008
0

LVL 75

Expert Comment

Can u post the sample result you are getting and the expected results
0

Author Comment

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

LVL 75

Expert Comment

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

Author Comment

No, I want to say it show in sequance 2007 & 2008, if see it is showing 2007 in middel
0

LVL 75

Expert Comment

did u try changing the ordedr by part

Order by lb, X.MonthNo
0

Author Comment

yes, but result same
0

LVL 75

Accepted Solution

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
,APOPOT=Sum(APOPOT)
,APC=sum(APC)
From
,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
,APOPOT=Count(AccidentDt)-Count(AccidentPC)
,APC=Count(AccidentPC)
from SupremeFuelDet
Where Del='0'
group by months,lb
Order by lb,X.MaxDate
0

Featured Post

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…