Complicated 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 2005 Forums

Transact-SQL (2005)

Complicated query?

Author

Topic

vadmcse
Starting Member

10 Posts

Posted - 2009-03-11 : 07:48:16

Hello,

I have two tables and want to get a result.

TABLE1 = Invoice
DATE-------------TOTAL------VAT
20060101--------100,00------15,00
20060115--------200,00------30,00
20060220--------150,00------22,50
20070101--------350,00------52,50
20070125--------500,00------75,00
20080516--------800,00------120,00

TABLE2 = Credit
DATE-------------TOTAL------VAT
20060115--------70,00-------10,50
20060810--------200,00------30,00
20080516--------400,00------60,00

RESULT should be:
WEEK______2006__________2007__________2008
1_________85,00_________297,50
3_________110,50
4_______________________425,00
8_________127,50
20____________________________________340,00
32________(170,00)

Where, WEEK_of_YEAR is the number of the week in the DATE year, and the total in each year is equal to (TABLE1.Total - TABLE1.VAT) - (Table2.Total - Table2.VAT)

anyone can help me?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-11 : 07:51:32

Please define what a week means to you.
How do you define a week? What are the rules for calculating week number over New Year?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-11 : 07:57:29

[code]-- Prepare sample data
DECLARE @Invoice TABLE
(
dt DATETIME,
Total MONEY,
VAT MONEY
)

INSERT @Invoice
SELECT '20060101', 100.00, 15.00 UNION ALL
SELECT '20060115', 200.00, 30.00 UNION ALL
SELECT '20060220', 150.00, 22.50 UNION ALL
SELECT '20070101', 350.00, 52.50 UNION ALL
SELECT '20070125', 500.00, 75.00 UNION ALL
SELECT '20080516', 800.00, 120.00

DECLARE @Credit TABLE
(
dt DATETIME,
Total MONEY,
VAT MONEY
)

INSERT @Credit
SELECT '20060115', 70.00, 10.50 UNION ALL
SELECT '20060810', 200.00, 30.00 UNION ALL
SELECT '20080516', 400.00, 60.00

-- Display the final result
SELECT theWeek,
SUM(CASE WHEN theYear = 2006 THEN theValue ELSE 0 END) AS [2006],
SUM(CASE WHEN theYear = 2007 THEN theValue ELSE 0 END) AS [2007],
SUM(CASE WHEN theYear = 2008 THEN theValue ELSE 0 END) AS [2008]
FROM (
SELECT DATEPART(YEAR, dt) AS theYear,
DATEPART(WEEK, dt) AS theWeek,
Total - VAT AS theValue
FROM @Invoice

UNION ALL

SELECT DATEPART(YEAR, dt) AS theYear,
DATEPART(WEEK, dt) AS theWeek,
VAT - Total AS theValue
FROM @Credit
) AS d
GROUP BY theWeek
ORDER BY theWeek[/code]

E 12°55'05.63"
N 56°04'39.26"

vadmcse
Starting Member

10 Posts

Posted - 2009-03-11 : 08:06:54

Hi PESO,

Thanks for answering.

I get the week number with this:

SELECT DATEPART(week, TABLE1.Date)

Thanks

vadmcse
Starting Member

10 Posts

Posted - 2009-03-11 : 08:17:13

Hi PESO,

WOOOOWWWWWW!

It's amazing!!!!
It works great!

Thank you very much!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources