October 10, 2007 at 1:45 am
hi this is ajay rathi,
i am facing a very big problem,
select month(Start_date) as start_Month, year(Start_date) as start_Year,
sum(case when Status = 'Active' then 1
else 0 end) as Active,
sum(case when Status = 'Inactive' then 1
else 0 end) as Billed
from tbl_Post_Job where Company_Id_Fk = 60 and Start_date between Start_date and Exp_date group by year(Start_date), month(Start_date)order by year(Start_date), month(Start_date)
in this query i want many things,first of all i want to say this is not right query i want query from you..
(1)month(Start_date) as start_Month, year(Start_date) as start_Year
from this line i want month and year from Start_date to Exp_date
(2)in active condition,if any company's status is active from january to april so in this condition the entry of active should be come in january to april in every month
(3)group by year(Start_date), month(Start_date)
in group by section same problem,i want data from Start_date to exp_date
actually the main problem i m facing is this that when i need month and year together and active postion and billed position saperatlly..
actually i need this data in following form...
Date | Active | Billed
------------------------------------
january,2007 | 4 | 2
feburary,2007 | 7 | 5
march,2007 | 4 | 2
april,2007 | 6 | 4
------------------------------------
january,2007 is the start_date and april,2007 is the exp_date
actualy above data january,2007 is coming in form of 1,2007 but i need this data in form of january,2007 so plz tell me how can i convert this data...
plz if any one needs any clarification then plz ask me about this because i need this query immediatly..
plz try to help me as soon as posible
October 10, 2007 at 2:07 am
Ajay
It's not easy to see what your requirement is. Please will you provide table DDL in the form of a CREATE TABLE statement and some sample data (preferably that will lead to the required results you have already given) in the form of INSERT statements.
A few observations:
(a) to convert from 1 to January you can: (i) use a CASE statement, (ii) create a table of months and numbers and join to that or (iii) have your front end do the formatting.
(b) what do you hope to achieve with the condition "Start_date between Start_date and Exp_date"? This will always evaluate as true (at least it will if Start_date isn't NULL).
(c) there are quite a few ways of listing all the dates (or months in your case) between two given dates. One of them is to create a "numbers table". There are lots of posts about that on this site.
Looking forward to seeing your table structure and sample data.
John
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply