April 12, 2004 at 9:42 am
I have a query such as the following
Select Year(ActionDate) Year, Month(ActionDate) Month, Case When PFTypeID = 151.0011 Then 'P' When PFTypeID = 150.0011 Then 'F' END Physical, Count(s.ScheduleID) Schedules
From Schedule s
Join Schedule_Audit saud On s.ScheduleID = saud.ScheduleID
Join Schedule_Action sact On saud.ActionID = sact.ActionID
Where ActionDate Between '1/1/2003' And '3/31/2004' And VersionNo = 1
Group By Year(ActionDate), Month(ActionDate), Case When PFTypeID = 151.0011 Then 'P' When PFTypeID = 150.0011 Then 'F' END
Order By Year(ActionDate), Month(ActionDate), Case When PFTypeID = 151.0011 Then 'P' When PFTypeID = 150.0011 Then 'F' END
and get results
| Year | Month | Physical | Schedules |
| 2003 | 1 | P | 1177 |
| 2003 | 2 | F | 6 |
| 2003 | 2 | P | 1519 |
| 2003 | 3 | F | 3 |
| 2003 | 3 | P | 1631 |
Is there any easy way to get the SQL Statement to return the missing line for month 1 of year 2003?
There is no data for F on that month/year but I would like the query to come back with a line and a 0 for the schedules column.
Is there an easy way to do this or not? I understand the SQL statement is doing what I am asking it to do, but I want a full result set where there might not be data like month 1 of year 2003.
Any suggestions.
April 13, 2004 at 12:53 am
Pls try this query
(Select Year(ActionDate) Year, Month(ActionDate) Month, Case When PFTypeID = 151.0011 Then 'P' When PFTypeID = 150.0011 Then 'F' END Physical, Count(s.ScheduleID) Schedules
From Schedule s
Join Schedule_Audit saud On s.ScheduleID = saud.ScheduleID
Join Schedule_Action sact On saud.ActionID = sact.ActionID
Where ActionDate Between '1/1/2003' And '3/31/2004' And VersionNo = 1
Group By Year(ActionDate), Month(ActionDate), Case When PFTypeID = 151.0011 Then 'P' When PFTypeID = 150.0011 Then 'F' END)
union
(Select Year,Month,
case when Physical=’P’ then ‘F’
case when Physical=’F’ then ‘P’ End ,0,count(*) from
(Select Year(ActionDate) Year, Month(ActionDate) Month, Case When PFTypeID = 151.0011 Then 'P' When PFTypeID = 150.0011 Then 'F' END Physical, Count(s.ScheduleID) Schedules
From Schedule s
Join Schedule_Audit saud On s.ScheduleID = saud.ScheduleID
Join Schedule_Action sact On saud.ActionID = sact.ActionID
Where ActionDate Between '1/1/2003' And '3/31/2004' And VersionNo = 1
Group By Year(ActionDate), Month(ActionDate), Case When PFTypeID = 151.0011 Then 'P' When PFTypeID = 150.0011 Then 'F' END
Order By Year(ActionDate), Month(ActionDate), Case When PFTypeID = 151.0011 Then 'P' When PFTypeID = 150.0011 Then 'F' END)
group by Year,Month,
case when Physical=’P’ then ‘F’
case when Physical=’F’ then ‘P’,0,count(*) having count(*)<2)
Order By Year, Month,Physical
April 13, 2004 at 8:14 am
UNION probably works have not tested it, but other option would be to build a temp table with dates to join against or perform sub-query within.
April 14, 2004 at 1:56 pm
Create a temp table with the year, month and Physical Values and make an OUTER JOIN right after the group by.
Unluckily there is no ROWNUM in sql server which can let you do all kinds of tricks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply