Help need in Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help need in Query

Author

Topic

learntsql

524 Posts

Posted - 2009-09-02 : 06:46:33

Hi All,

I have input dropdown where it contains all my datecolumn names of my table and year as another input and Areas as another input;
If i select any year,Area and any date column i have to show sales as per month wise of that year and Area and sum of sales

as like
Area--sales--Month
abc --1000--2
abc --2000--3
cde --233-- 5
def --333-- 9
...
...
...

here MonthNo is extracted from the column of my table which i selected as date input field(which contains all datetime datatype columns).
how can we do this?
any sugestions please.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-02 : 06:54:47

table structure
sample data
wanted output
would help to help!

No, you're never too old to Yak'n'Roll if you're too young to die.

learntsql

524 Posts

Posted - 2009-09-02 : 07:39:33

assume my sample data as
ID Area sales(Rs) date1 date2 date3....
1 abc 10000 d1 d2 d3
2 abc 20000 d2 d3 d3
3 cde 30000 d1 d1 d1
.....
.....
as i told my input dropdown contains date1,date2,date3 ...(datetime) columns as values
when i select any value let say date2 and year input as some year then i have to get all the details from columns considering Area,Sales and Date2 columns
i.e i need to ignore all other datetime columns.

my output should like as
Area-SumofSales-MonthName
abc-10000-month(d2)
abc-20000-month(d3)
abc-30000-month(d1)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-02 : 08:30:51

does date field contain full date values or just month and day?

learntsql

524 Posts

Posted - 2009-09-02 : 08:33:29

it contains full datetime value.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-02 : 08:36:07

then what's significance of year input?

learntsql

524 Posts

Posted - 2009-09-02 : 08:41:17

i have to show sales to selected year.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-02 : 08:49:28

sorry didnt get that. can you illustrate?

learntsql

524 Posts

Posted - 2009-09-02 : 09:20:13

sorry i think i am not clear.
i have year dropdown.
i have to show sales for selected year as
i need to filter it in where clause like
where year(date2)=@iYear --date2 is the value which i selected from datevalues dropdown.
if i select date1 then my where condition should change as
where year(date1)=@iYear....

and in my select clause i have to display
Area,Sales,MonthNo.

sorry if i am not clear.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-02 : 09:30:12

ok for that do like this


SELECT Area,Sales,MonthNo
FROM YourTABLE
where CASE @DateField
      WHEN 'Date1' THEN DATEADD(dd,DATEDIFF(dd,0,date1),0)
      WHEN 'Date2' THEN DATEADD(dd,DATEDIFF(dd,0,date2),0)
      ...
      END >=DATEADD(yy,@iYear-1900,0)
      AND  CASE @DateField
      WHEN 'Date1' THEN DATEADD(dd,DATEDIFF(dd,0,date1),0)
      WHEN 'Date2' THEN DATEADD(dd,DATEDIFF(dd,0,date2),0)
      ...
      END< DATEADD(yy,@iYear-1899,0)

learntsql

524 Posts

Posted - 2009-09-02 : 09:34:21

Thanks visakh i will try.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-02 : 09:46:01

good luck

learntsql

524 Posts

Posted - 2009-09-04 : 01:09:08

sorry for delay reply visakh,
MonthNo i have to pick from the column of my table which i pass as date parameter and i need to sum the sales for coresponding months.
for eg; if i pass date2 as parameter then the monthno should pick from the column date2 of my table similerly if i pass any other parameter then same as above.
at a time i pass only single date parameter.

learntsql

524 Posts

Posted - 2009-09-04 : 04:25:32

PLEASE COULD ANYONE REPLY.
OR ANY SUGESTIONS OR ANY ALTERNATIVE WAY.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-04 : 04:48:45

Like this (using Visakh's approach):

select
Area,
sum(Sales) as Sales,
MonthNo
from
(
  SELECT 
  Area,
  Sales,
  case @Datefield
    when 'Date1' then month(Date1)
    when 'Date2' then month(Date2)
    ...
  end as MonthNo
  FROM YourTABLE
  where CASE @DateField
      WHEN 'Date1' THEN DATEADD(dd,DATEDIFF(dd,0,date1),0)
      WHEN 'Date2' THEN DATEADD(dd,DATEDIFF(dd,0,date2),0)
      ...
      END >=DATEADD(yy,@iYear-1900,0)
      AND  CASE @DateField
      WHEN 'Date1' THEN DATEADD(dd,DATEDIFF(dd,0,date1),0)
      WHEN 'Date2' THEN DATEADD(dd,DATEDIFF(dd,0,date2),0)
      ...
      END< DATEADD(yy,@iYear-1899,0)
)dt
group by Area,MonthNo