One Results Set with multi-date ranges
-
Hello,
I am looking for the right direction for a business request that has been made to me.
I have been asked to develop a report that will display sales data based on ITEM VENDOR. This is to allow users to do comparisons of year and year to date.
The columns are defined as:
ITEM | VENDOR | 2005 TOTAL | 2006 TOTAL | YTD 2006 | YTD 2007 |
123 X $xxx $xxx $xxx $xxx
456 Y $xxx $xxx $xxx $xxx
342 X $xxx $xxx $xxx $xxx
124 Y $xxx $xxx $xxx $xxx
665 Y $xxx $xxx $xxx $xxx
454 X $xxx $xxx $xxx $xxx
957 A $xxx $xxx $xxx $xxx
964 B $xxx $xxx $xxx $xxx
The columns are defined as this:
ITEM - Product
VENDOR - VENDOR (One to many relationship with ITEM)
2005 TOTAL = SUM of Total Sales for 2005
2006 TOTAL = TOtal Sales for 2006
YTD 2006 = TOTAL Sales from 1/1/2006 until 7/3/2006
YTD 2007 = Total Sales from 1/1/2007 until 7/3/2007
I am trying to get all this information into ONE result set from the database. I am not quit sure how accomplish this or if it can be done in one result set.
I have tried multiple select statements with data between ranges, but I at a loss on how ot put this together in one results set.
Any assistance is appreciated, since I have been mulling on this for a few days.
Thanks inadvance.
tk
-
We could use additional info in order to help you. The DDL for the table(s) and some sample data will go a long way to generate some suggestions to help you out.
-
I will provide as much info as possible. This is sample I created for this request to test.
Table1
[id] [int] IDENTITY (1, 1) NOT NULL ,
[entered_date] [datetime] NULL ,
[sales] [int] NULL ,
[item] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vendor] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
Sample data
-
Unfortunately, I can't see your sample data. If you can post it as a series of insert statements that could be cut and pasted like your table DDL, that would help.
Thanks
-
this should be better:
Sample data:
ID DATE SALES VENDOR ITEM
1 1/1/2005 12005 ca 31
2 2/2/2005 22005 ca 32
3 3/3/2005 32005 ca 33
4 4/4/2005 42005 ca 54
5 1/1/2006 12006 ca 43
6 2/2/2006 22006 ca 67
7 3/3/2006 32006 ca 895
8 4/4/2006 42006 ca 58
9 1/1/2007 12007 ca 45
10 2/2/2007 22007 ca 956
11 3/3/2007 32007 ca 345
12 4/4/2007 42007 ca 4
13 5/5/2005 52005 ca 4567
14 6/6/2005 62005 ri ADAS
15 7/7/2005 72005 ri 3add
16 8/8/2005 82005 ri 4asd
17 9/9/2005 92005 ri wer
18 10/10/2005 102005 ri 234
19 5/5/2006 52006 ri as44
20 6/6/2006 62006 ri 456
21 7/7/2006 72006 ri jhd6
22 8/8/2006 82006 ri 4564
23 9/9/2006 92006 ri fd32
24 10/10/2006 102006 ri 2345s
25 5/5/2007 52007 ri 45623
26 6/6/2007 62007 ri 89
27 7/7/2007 72007 ri 567
28 8/8/2007 82007 ri 678
29 9/9/2007 92007 ri xsdf
30 10/10/2007 102007 ri 5es
31 1/1/2005 12005 nc 223
32 2/2/2005 22005 nc 234
33 1/1/2006 12006 nc 422
34 2/2/2006 22006 nc 333
-
Try this against your sample data:
select
item,
vendor,
sum(case when year(entered_date) = 2005 then sales else 0 end) as TotalSales2005,
sum(case when year(entered_date) = 2006 then sales else 0 end) as TotalSales2006,
sum(case when year(entered_date) = 2006 and entered_date < dateadd(yyyy, -1, dateadd(dd,datediff(dd,0,getdate()),0)) then sales else 0 end) as YTD2006,
sum(case when year(entered_date) = 2007 and entered_date < dateadd(dd,datediff(dd,0,getdate()),0) then sales else 0 end) as YTD2007
from
dbo.TestTable
where
entered_date between dateadd(yyyy, -2, dateadd(yyyy, datediff(yyyy, 0, getdate()), 0)) and dateadd(dd,datediff(dd,0,getdate()),0)
group by
item,
vendor
-
Lynn,
Thank you very much for you attention to my issue. The has helped me in more ways then one.
tj
-
select
item.name,
vendor
.name,
sum(case when year(entered_date) = 2005 then sales else 0 end) as TotalSales2005,
sum(case when year(entered_date) = 2006 then sales else 0 end) as TotalSales2006,
sum(case when year(entered_date) = 2006 and datepart(dayofyear, entered_date) <= datepart(dayofyear, current_timestamp) then sales else 0 end) as YTD2006,
sum(case when year(entered_date) = 2007 and datepart(dayofyear, entered_date) <= datepart(dayofyear, current_timestamp) then sales else 0 end) as YTD2007
from
Vendor
INNER
JOIN Item ON Item.VendorID = Vendor.VendorID
where
entered_date >= '2005-01-01'
group
by item.name,
item
.itemid,
vendor
.name,
vendor
.vendorid
N 56°04'39.16"
E 12°55'05.25" -
I see one possible problem using dayofyear; leapyears. February 29 of a leapyear is the same dayofyear as March 1 of a non-leapyear year (confused?).
-
I realized that too, but sincce neither 2006 nor 2007 are leapyears it works.
N 56°04'39.16"
E 12°55'05.25" -
Looking back at my code, its semi-generic. I can see a few more changes that would need to be fixed to make it more generic and useable for any three year period.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply