Need help on this 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

SQL Server 2000 Forums

SQL Server Development (2000)

Need help on this query

Author

Topic

nicky_river
Yak Posting Veteran

55 Posts

Posted - 2010-07-18 : 10:09:09

Hi Everyone,

I am facing a peculiar problem on how to go about building this query. The query is as given below. I would be grateful is you could provide me with the resultant query.

The O/P is as given below :-

O/P
---------------------------------------
Date Client_code salary Cumulative_sal
----------------------------------------
1/4 A 4000 4000
2/4 A 0 4000
3/4 A 0 4000
4/4 A 6000 10000
. . .
. . .
. . .
1/4 B 6000 6000
2/4 B 6000 12000
3/4 B 0 12000
4/4 B 0 12000
. . .
. . .
. . .

Original table
----------------------------------------
date Client_code salary
----------------------------------------
1/4 A 4000
1/4 B 6000
2/4 B 6000
4/4 A 6000
6/4 B 8000
6/4 A 2000

Basically, I need to generate the above o/p for each employee and for all dates in april. If there is no employee from the original table, associated with that date then both the employees salary must be 0 for that date and cumulative salary gets added with the below salary.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-18 : 10:22:49

Do you have a calendar table ?


select c.date, e.employee, salary = isnull(o.salary, 0)
from   calendar c
       cross join employee e
       left  join original o  on  c.date = o.date
                              and e.employee = o.client_code
where  c.date >= '2010-04-01' 
and    c.date <= '2010-04-30'

KH
[spoiler]Time is always against us[/spoiler]

nicky_river
Yak Posting Veteran

55 Posts

Posted - 2010-07-19 : 04:44:50

Hi khtan,

I don't have a calender table, but the date for a particular month must be stored in the #temp table.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-19 : 05:18:36

Please don't post duplicates.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=147429

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-19 : 07:50:11

quote:
Originally posted by nicky_river

Hi khtan,

I don't have a calender table, but the date for a particular month must be stored in the #temp table.

You can use F_TABLE_DATE to replace the calendar table

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources