November 6, 2008 at 9:30 pm
Hi all SQL gurus,
i'm a novice in sql server and hope i can get some help in developing a view. below is a description of what i need.
i have a SalesView which already summarizes TotalSales by InvoiceDate. the results will show all dates for all years.
i'm having trouble trying to setup another view based on the above showing SalesThisYr, SalesLastYr, Month so i can graph this info.
the Year is based on calendar months from July to June, hence Last year would mean July 2007 to Jun 2008 and This Year would be July 2008 to June 2009.
any help is much appreciated.
Jerry
November 7, 2008 at 3:51 am
I havent played with views too much so there may be an easier way of doing this but i would suggest putting a filter statement on the view you already have.
So in your view where you have your WHERE statement make some modyfications there. Or you could write a stored procedure (though if your pulling a lot of data back that mya not be the quickest route.
Can you upload a copy of your existing view?
Adam Zacks-------------------------------------------Be Nice, Or Leave
November 7, 2008 at 4:08 am
Hi thanks for replying.
here's my current View in code:
===
SELECT TOP (100) PERCENT dbo.JobDetails.Weight AS TotalWeight, dbo.JobDetails.ExtPrice AS TotalSales, dbo.Jobs.InvoiceDate
FROM dbo.JobDetails INNER JOIN
dbo.Jobs ON dbo.JobDetails.JobNo = dbo.Jobs.JobNo
WHERE (dbo.Jobs.Status = 'Invoiced')
===
November 7, 2008 at 4:38 am
need some more info. of your query.
November 7, 2008 at 4:40 am
Here I figured that you may have a 'Sales' table which holds details on the sales and a sale is then related to a job or multiple jobs where there maybe one sale with multiple jobs where each job can be invoiced independently. So the idea with the below is to give you all sales that have either been invoiced or have one invoiced job related to it (dependant on your database setup).
SELECT TOP (100) PERCENT dbo.JobDetails.Weight AS TotalWeight, dbo.JobDetails.ExtPrice AS TotalSales, dbo.Jobs.InvoiceDate
FROM dbo.JobDetails INNER JOIN
dbo.Jobs ON dbo.JobDetails.JobNo = dbo.Jobs.JobNo
WHERE dbo.Sales.SaleDate BETWEEN {date1} AND {date2} AND dbo.Jobs.Status = 'Invoiced'
OR
Once again obviously I don’t know your database structure or fields but assuming you have a field that holds the date that a job was invoiced, something like this should return all jobs that were invoiced between {date1} and {date2}...
SELECT TOP (100) PERCENT dbo.JobDetails.Weight AS TotalWeight, dbo.JobDetails.ExtPrice AS TotalSales, dbo.Jobs.InvoiceDate
FROM dbo.JobDetails INNER JOIN
dbo.Jobs ON dbo.JobDetails.JobNo = dbo.Jobs.JobNo
WHERE dbo.Jobs.Status = 'Invoiced' AND dbo.Jobs.Invoioced BETWEEN {date1} AND {date2}
I don’t have you database to test this but it should be close to what you need (obviously you'll need to change column names etc).
Let me know.
Adam Zacks-------------------------------------------Be Nice, Or Leave
November 7, 2008 at 5:05 am
Hi Adam
thanks for the help so far.
the code you provided will not group the TotalSales into SalesThisYr & SalesLastYr and the issue with grouping these by the date parameters i require. i think a sub query is required individually for SalesThisYr & SalesLastYr. e.g.
SalesThisYr SalesLastYr Month
25000 23000 July
32000 33000 Aug
and so on....
November 7, 2008 at 5:44 am
Gotcha!
Sorry misunderstood your requirements. Just in the middle of something but take a look at these.
http://www.odetocode.com/articles/299.aspx
http://www.devx.com/dbzone/Article/28165/1954
PIVOT tables may hold the key 😉
Adam Zacks-------------------------------------------Be Nice, Or Leave
November 8, 2008 at 12:04 am
Sup Adam
thanks for the links. read thru them and the devx article is quite good. been messing around with cross tab queries today without any success.
i can achieve what i need by way of 3 views but im sure its inefficient.
view1 - to find current yr sales info
===
SELECT TOP (100) PERCENT SUM(TotalSales) AS Sales, SUM(TotalWeight) AS Weight, MONTH(InvoiceDate) AS M, DATENAME(month, InvoiceDate) AS Month,
DATEADD(month, DATEDIFF(month, 0, InvoiceDate), 0) AS ChartDate
FROM dbo.SalesView
WHERE (MONTH(CURRENT_TIMESTAMP) >= 7) AND (MONTH(CURRENT_TIMESTAMP) < 7) OR
(MONTH(CURRENT_TIMESTAMP) >= 7) AND (InvoiceDate >= CAST(YEAR(CURRENT_TIMESTAMP) AS CHAR(4)) + '0701') OR
(MONTH(CURRENT_TIMESTAMP) = CAST(YEAR(CURRENT_TIMESTAMP) - 1 AS CHAR(4)) + '0701') OR
(InvoiceDate >= CAST(YEAR(CURRENT_TIMESTAMP) - 1 AS CHAR(4)) + '0701') AND (InvoiceDate >= CAST(YEAR(CURRENT_TIMESTAMP) AS CHAR(4))
+ '0701')
GROUP BY MONTH(InvoiceDate), DATENAME(Month, InvoiceDate), DATEADD(month, DATEDIFF(month, 0, InvoiceDate), 0)
ORDER BY ChartDate
===
View2 - find Last yr sales info
===
SELECT TOP (100) PERCENT SUM(TotalSales) AS Sales, SUM(TotalWeight) AS Weight, MONTH(InvoiceDate) AS M, DATENAME(month, InvoiceDate) AS Month,
DATEADD(month, DATEDIFF(month, 0, InvoiceDate), 0) AS ChartDate
FROM dbo.SalesView
WHERE (MONTH(CURRENT_TIMESTAMP) >= 7) AND (MONTH(CURRENT_TIMESTAMP) < 7) OR
(MONTH(CURRENT_TIMESTAMP) >= 7) AND (InvoiceDate <= CAST(YEAR(CURRENT_TIMESTAMP) - 2 AS CHAR(4)) + '0701') OR
(MONTH(CURRENT_TIMESTAMP) < 7) AND (InvoiceDate <= CAST(YEAR(CURRENT_TIMESTAMP) - 1 AS CHAR(4)) + '0701') OR
(InvoiceDate >= CAST(YEAR(CURRENT_TIMESTAMP) - 1 AS CHAR(4)) + '0701') AND (InvoiceDate <= CAST(YEAR(CURRENT_TIMESTAMP) AS CHAR(4))
+ '0701')
GROUP BY MONTH(InvoiceDate), DATENAME(Month, InvoiceDate), DATEADD(month, DATEDIFF(month, 0, InvoiceDate), 0)
ORDER BY ChartDate
====
view3 - merge view 1 & 2 and do a left outer join
===
SELECT TOP (100) PERCENT dbo.SalesByMonthLastYear.Sales AS SalesLY, dbo.SalesView2.Sales AS SalesCY, dbo.SalesByMonthLastYear.Month
FROM dbo.SalesByMonthLastYear LEFT OUTER JOIN
dbo.SalesView2 ON dbo.SalesByMonthLastYear.Month = dbo.SalesView2.Month
ORDER BY dbo.SalesByMonthLastYear.ChartDate
===
there must be a better way?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply