March 26, 2013 at 6:08 am
Hi Every Body,
i am strucked up in creating stored procedure....
My scenario is:
I Have a table Sales_Month with values like this :
StateDistrict Division Month_Number Total
353151692.1666
353152692.1666
353153692.1666
353154692.1666
353155692.1666
353156692.1666
353157692.1666
353158692.1666
353159692.1666
3531510692.1666
3531511692.1666
3531512692.1666
354151786.9166
354152786.9166
354153786.9166
354154786.9166
354155786.9166
354156786.9166
354157786.9166
354158786.9166
354159786.9166
3541510786.9166
3541511786.9166
3541512786.9166
3581511318.75
3581521318.75
3581531318.75
3581541318.75
3581551318.75
3581561318.75
3581571318.75
3581581318.75
3581591318.75
35815101318.75
35815111318.75
35815121318.75
359151623.6666
359152623.6666
359153623.6666
359154623.6666
359155623.6666
I want to create a store procedure to get output as
StateDistrict Division Month_Number TotalBusiness DaysSales per day Date
353151692.1666220.00001/1/2013Holiday
353151692.16662231.46211/2/2013
353151692.16662231.46211/3/2013
353151692.16662231.46211/4/2013
353151692.1666220.00001/5/2013Sat'Day
353151692.1666220.00001/6/2013Sun'Day
353151692.16662231.46211/7/2013
353151692.16662231.46211/8/2013
353151692.16662231.46211/9/2013
353151692.16662231.46211/10/2013
353151692.16662231.46211/11/2013
353151692.1666220.00001/12/2013Sat'Day
353151692.1666220.00001/13/2013Sun'Day
353151692.16662231.46211/14/2013
353151692.16662231.46211/15/2013
353151692.16662231.46211/16/2013
353151692.16662231.46211/17/2013
353151692.16662231.46211/18/2013
353151692.1666220.00001/19/2013Sat'Day
353151692.1666220.00001/20/2013Sun'Day
353151692.16662231.46211/21/2013
353151692.16662231.46211/22/2013
353151692.16662231.46211/23/2013
353151692.16662231.46211/24/2013
353151692.16662231.46211/25/2013
353151692.1666220.00001/26/2013Sat'Day
353151692.1666220.00001/27/2013Sun'Day
353151692.16662231.46211/28/2013
353151692.16662231.46211/29/2013
353151692.16662231.46211/30/2013
353151692.16662231.46211/31/2013
Like that i want to display for all State-Ditrict-Divison Combination.
If Date is either Holiday or weekend(Sat'Day and Sun'Day) then "salesperday" should be zero.
I have created following view for Dates
CREATE VIEW [dbo].[view1]
AS SELECT Date_id ,
DateKey
,DayKey
,DateMonthId
,Year_Month_Id
,ClosingDateMonthId
,Day_Ind = 1 ,WeekEnd_Ind = CASE WHEN DATENAME(DW,DATEKEY) IN ('Saturday','Sunday') THEN 1 ELSE 0 END
,CASE WHEN Holiday_Ind = 1 AND DATENAME(DW,DATEKEY) NOT IN ('Saturday','Sunday') THEN 1 ELSE 0
END AS Holiday_Ind FROM DimDate
Note: from the above view we have to get business days and Holidays (including weekends). we have already another table which contains all the columns ([StateId] [int] NULL,
[District]
[Division]
[Month_Number]
[Total]
[BuisDays]
[SalesPerDay]
[Date]
All columns has values except for (BusiDays,SalesPerday,Dates: these are nulls). I want update that table with "Busidays,salesperday,Dates" values from this query.
Please! Please! Please! Kindly help me to create stored procedure. i am trying from morning onwards.
March 26, 2013 at 7:27 am
Table sales_month doesn't contain year. Start with something like this:
SELECT s.*, c.*
FROM Sales_Month s
INNER JOIN view1 c ON c.? = s.Month_Number
WHERE c.[year] = ?
Replace the question marks with something sensible and check the results. The calculations in your required output are trivial - what you want to see first is that you are getting the correct number of rows with the correct data in them from both tables.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 26, 2013 at 7:33 am
Its my mistake. Year column is present in Table as well as view.
Can you help how to take business days and how to populate each day for month.
Help Appreciated.
March 26, 2013 at 7:36 am
Experiment with the simple query I posted. Replace the ? with appropriate column names.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 26, 2013 at 8:07 am
I Have tried. I didn't get businessdays and holidays (weekends also).
if possible would you dont mind please write query for me. i am beginner in SQL and i am tryinng from morning onwards.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply