[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

0 for months that don't exist in a query

Posted on 2005-04-28
3
Medium Priority
?
166 Views
Last Modified: 2010-03-19
This is probably a classic question but I cannot figure it out.

I have a sales orders table and a sales detail table.  I want to group from the sales order table by customerID and the month of the order and then calc the total sales for the month.  the kicker is I only need to go back 12 months from the current date (think i have that solved, may not be the most efficient though) and some customers didn't buy during some months, i want to show 0 for those months).  i thought i could create a temp table with the last twelve months and just do a join but it doesn't seem to be working.  here's my query so far, its not the neatest and it may not be the most efficient, comments are welcome on all aspects of it.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


CREATE proc GetSalesByMonth @Start_Date datetime = '1/1/1900'

AS

Declare @End_date datetime

Declare @Start_Date_back_1 datetime
Declare @Start_Date_back_2 datetime
Declare @Start_Date_back_3 datetime
Declare @Start_Date_back_4 datetime
Declare @Start_Date_back_5 datetime
Declare @Start_Date_back_6 datetime
Declare @Start_Date_back_7 datetime
Declare @Start_Date_back_8 datetime
Declare @Start_Date_back_9 datetime
Declare @Start_Date_back_10 datetime
Declare @Start_Date_back_11 datetime

Declare @End_Date_back_1 datetime
Declare @End_Date_back_2 datetime
Declare @End_Date_back_3 datetime
Declare @End_Date_back_4 datetime
Declare @End_Date_back_5 datetime
Declare @End_Date_back_6 datetime
Declare @End_Date_back_7 datetime
Declare @End_Date_back_8 datetime
Declare @End_Date_back_9 datetime
Declare @End_Date_back_10 datetime
Declare @End_Date_back_11 datetime

If @Start_Date = '1/1/1900'
      Begin
            Set @Start_Date = DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
      End
Else
      Begin
            Set @Start_Date = DATEADD(mm, DATEDIFF(mm,0,@Start_Date), 0)
      End

set @Start_Date_back_1 = DATEADD(mm, -1, @Start_Date)
set @Start_Date_back_2 = DATEADD(mm, -2, @Start_Date)
set @Start_Date_back_3 = DATEADD(mm, -3, @Start_Date)
set @Start_Date_back_4 = DATEADD(mm, -4, @Start_Date)
set @Start_Date_back_5 = DATEADD(mm, -5, @Start_Date)
set @Start_Date_back_6 = DATEADD(mm, -6, @Start_Date)
set @Start_Date_back_7 = DATEADD(mm, -7, @Start_Date)
set @Start_Date_back_8 = DATEADD(mm, -8, @Start_Date)
set @Start_Date_back_9 = DATEADD(mm, -9, @Start_Date)
set @Start_Date_back_10 = DATEADD(mm, -10, @Start_Date)
set @Start_Date_back_11 = DATEADD(mm, -11, @Start_Date)

            
Set @End_Date = dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,@start_date  )+1, 0))
Set @End_Date_back_1 = dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,@start_date_back_1  )+1, 0))
Set @End_Date_back_2 = dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,@start_date_back_2  )+1, 0))
Set @End_Date_back_3 = dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,@start_date_back_3  )+1, 0))
Set @End_Date_back_4 = dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,@start_date_back_4  )+1, 0))
Set @End_Date_back_5 = dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,@start_date_back_5  )+1, 0))
Set @End_Date_back_6 = dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,@start_date_back_6  )+1, 0))
Set @End_Date_back_7 = dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,@start_date_back_7  )+1, 0))
Set @End_Date_back_8 = dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,@start_date_back_8  )+1, 0))
Set @End_Date_back_9 = dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,@start_date_back_9  )+1, 0))
Set @End_Date_back_10 = dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,@start_date_back_10  )+1, 0))
Set @End_Date_back_11 = dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,@start_date_back_10  )+1, 0))

CREATE TABLE [#TempDateList] (
      [Monthdate] [datetime] NOT NULL ,
      [NullAmt] money not null
      CONSTRAINT [PK_TempDateList] PRIMARY KEY  CLUSTERED
      (
            [Monthdate]
      )  ON [PRIMARY]
) ON [PRIMARY]


Insert into #TempDateList Values (@Start_date, 0)

Insert into #TempDateList Values (@Start_date_back_1, 0)

Insert into #TempDateList Values (@Start_date_back_2, 0)

Insert into #TempDateList Values (@Start_date_back_3, 0)

Insert into #TempDateList Values (@Start_date_back_4, 0)

Insert into #TempDateList Values (@Start_date_back_5, 0)

Insert into #TempDateList Values (@Start_date_back_6, 0)

Insert into #TempDateList Values (@Start_date_back_7, 0)

Insert into #TempDateList Values (@Start_date_back_8, 0)

Insert into #TempDateList Values (@Start_date_back_9, 0)

Insert into #TempDateList Values (@Start_date_back_10, 0)

Insert into #TempDateList Values (@Start_date_back_11, 0)



SELECT

CM.Company_Name,

Case
       When Order_Date between @Start_Date and @End_Date Then @Start_Date
      When Order_Date between @Start_Date_Back_1 and @End_Date_Back_1 Then @Start_Date_Back_1
      When Order_Date between @Start_Date_Back_2 and @End_Date_Back_2 Then @Start_Date_Back_2
      When Order_Date between @Start_Date_Back_3 and @End_Date_Back_3 Then @Start_Date_Back_3
      When Order_Date between @Start_Date_Back_4 and @End_Date_Back_4 Then @Start_Date_Back_4
      When Order_Date between @Start_Date_Back_5 and @End_Date_Back_5 Then @Start_Date_Back_5
      When Order_Date between @Start_Date_Back_6 and @End_Date_Back_6 Then @Start_Date_Back_6
      When Order_Date between @Start_Date_Back_7 and @End_Date_Back_7 Then @Start_Date_Back_7
      When Order_Date between @Start_Date_Back_8 and @End_Date_Back_8 Then @Start_Date_Back_8
      When Order_Date between @Start_Date_Back_9 and @End_Date_Back_9 Then @Start_Date_Back_9
      When Order_Date between @Start_Date_Back_10 and @End_Date_Back_10 Then @Start_Date_Back_10
      When Order_Date between @Start_Date_Back_11 and @End_Date_Back_11 Then @Start_Date_Back_11
End as month,
Sum(Case
       When Order_Date between @Start_Date and @End_Date Then QTD.Extended_Price_Shipped
      When Order_Date between @Start_Date_Back_1 and @End_Date_Back_1 Then QTD.Extended_Price_Shipped
      When Order_Date between @Start_Date_Back_2 and @End_Date_Back_2 Then QTD.Extended_Price_Shipped
      When Order_Date between @Start_Date_Back_3 and @End_Date_Back_3 Then QTD.Extended_Price_Shipped
      When Order_Date between @Start_Date_Back_4 and @End_Date_Back_4 Then QTD.Extended_Price_Shipped
      When Order_Date between @Start_Date_Back_5 and @End_Date_Back_5 Then QTD.Extended_Price_Shipped
      When Order_Date between @Start_Date_Back_6 and @End_Date_Back_6 Then QTD.Extended_Price_Shipped
      When Order_Date between @Start_Date_Back_7 and @End_Date_Back_7 Then QTD.Extended_Price_Shipped
      When Order_Date between @Start_Date_Back_8 and @End_Date_Back_8 Then QTD.Extended_Price_Shipped
      When Order_Date between @Start_Date_Back_9 and @End_Date_Back_9 Then QTD.Extended_Price_Shipped
      When Order_Date between @Start_Date_Back_10 and @End_Date_Back_10 Then QTD.Extended_Price_Shipped
      When Order_Date between @Start_Date_Back_11 and @End_Date_Back_11 Then QTD.Extended_Price_Shipped
      Else 0
End) as Sales,
Sum(NullAmt) as amt,
monthdate

FROM        
dbo.SalesOrder QT

LEFT OUTER JOIN
        dbo.SalesOrderDetail QTD
            ON QT.Order_ID = QTD.FK_order_ID

Left OUTER JOIN
      dbo.Company CM
      on CM.Company_ID = QT.FK_Company_ID
Full Outer Join
      #TempDateList on
Case
       When Order_Date between @Start_Date and @End_Date Then @Start_Date
      When Order_Date between @Start_Date_Back_1 and @End_Date_Back_1 Then @Start_Date_Back_1
      When Order_Date between @Start_Date_Back_2 and @End_Date_Back_2 Then @Start_Date_Back_2
      When Order_Date between @Start_Date_Back_3 and @End_Date_Back_3 Then @Start_Date_Back_3
      When Order_Date between @Start_Date_Back_4 and @End_Date_Back_4 Then @Start_Date_Back_4
      When Order_Date between @Start_Date_Back_5 and @End_Date_Back_5 Then @Start_Date_Back_5
      When Order_Date between @Start_Date_Back_6 and @End_Date_Back_6 Then @Start_Date_Back_6
      When Order_Date between @Start_Date_Back_7 and @End_Date_Back_7 Then @Start_Date_Back_7
      When Order_Date between @Start_Date_Back_8 and @End_Date_Back_8 Then @Start_Date_Back_8
      When Order_Date between @Start_Date_Back_9 and @End_Date_Back_9 Then @Start_Date_Back_9
      When Order_Date between @Start_Date_Back_10 and @End_Date_Back_10 Then @Start_Date_Back_10
      When Order_Date between @Start_Date_Back_11 and @End_Date_Back_11 Then @Start_Date_Back_11
End = MonthDate
where
QT.Closed = 1
and Case
       When Order_Date between @Start_Date and @End_Date Then @Start_Date
      When Order_Date between @Start_Date_Back_1 and @End_Date_Back_1 Then @Start_Date_Back_1
      When Order_Date between @Start_Date_Back_2 and @End_Date_Back_2 Then @Start_Date_Back_2
      When Order_Date between @Start_Date_Back_3 and @End_Date_Back_3 Then @Start_Date_Back_3
      When Order_Date between @Start_Date_Back_4 and @End_Date_Back_4 Then @Start_Date_Back_4
      When Order_Date between @Start_Date_Back_5 and @End_Date_Back_5 Then @Start_Date_Back_5
      When Order_Date between @Start_Date_Back_6 and @End_Date_Back_6 Then @Start_Date_Back_6
      When Order_Date between @Start_Date_Back_7 and @End_Date_Back_7 Then @Start_Date_Back_7
      When Order_Date between @Start_Date_Back_8 and @End_Date_Back_8 Then @Start_Date_Back_8
      When Order_Date between @Start_Date_Back_9 and @End_Date_Back_9 Then @Start_Date_Back_9
      When Order_Date between @Start_Date_Back_10 and @End_Date_Back_10 Then @Start_Date_Back_10
      When Order_Date between @Start_Date_Back_11 and @End_Date_Back_11 Then @Start_Date_Back_11
End is not null
and cm.company_name is not null
and cm.company_name not like '%VOID%'

group by Cm.Company_Name, monthdate,
Case
       When Order_Date between @Start_Date and @End_Date Then @Start_Date
      When Order_Date between @Start_Date_Back_1 and @End_Date_Back_1 Then @Start_Date_Back_1
      When Order_Date between @Start_Date_Back_2 and @End_Date_Back_2 Then @Start_Date_Back_2
      When Order_Date between @Start_Date_Back_3 and @End_Date_Back_3 Then @Start_Date_Back_3
      When Order_Date between @Start_Date_Back_4 and @End_Date_Back_4 Then @Start_Date_Back_4
      When Order_Date between @Start_Date_Back_5 and @End_Date_Back_5 Then @Start_Date_Back_5
      When Order_Date between @Start_Date_Back_6 and @End_Date_Back_6 Then @Start_Date_Back_6
      When Order_Date between @Start_Date_Back_7 and @End_Date_Back_7 Then @Start_Date_Back_7
      When Order_Date between @Start_Date_Back_8 and @End_Date_Back_8 Then @Start_Date_Back_8
      When Order_Date between @Start_Date_Back_9 and @End_Date_Back_9 Then @Start_Date_Back_9
      When Order_Date between @Start_Date_Back_10 and @End_Date_Back_10 Then @Start_Date_Back_10
      When Order_Date between @Start_Date_Back_11 and @End_Date_Back_11 Then @Start_Date_Back_11
End
order by cm.company_name,
Case
       When Order_Date between @Start_Date and @End_Date Then @Start_Date
      When Order_Date between @Start_Date_Back_1 and @End_Date_Back_1 Then @Start_Date_Back_1
      When Order_Date between @Start_Date_Back_2 and @End_Date_Back_2 Then @Start_Date_Back_2
      When Order_Date between @Start_Date_Back_3 and @End_Date_Back_3 Then @Start_Date_Back_3
      When Order_Date between @Start_Date_Back_4 and @End_Date_Back_4 Then @Start_Date_Back_4
      When Order_Date between @Start_Date_Back_5 and @End_Date_Back_5 Then @Start_Date_Back_5
      When Order_Date between @Start_Date_Back_6 and @End_Date_Back_6 Then @Start_Date_Back_6
      When Order_Date between @Start_Date_Back_7 and @End_Date_Back_7 Then @Start_Date_Back_7
      When Order_Date between @Start_Date_Back_8 and @End_Date_Back_8 Then @Start_Date_Back_8
      When Order_Date between @Start_Date_Back_9 and @End_Date_Back_9 Then @Start_Date_Back_9
      When Order_Date between @Start_Date_Back_10 and @End_Date_Back_10 Then @Start_Date_Back_10
      When Order_Date between @Start_Date_Back_11 and @End_Date_Back_11 Then @Start_Date_Back_11
End DESC

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

0
Comment
Question by:dcaparaso
  • 2
3 Comments
 
LVL 28

Accepted Solution

by:
rafrancisco earned 1050 total points
ID: 13890613
First create a function that will give you a table of months to process:

CREATE FUNCTION [dbo].[ufn_GetMonths] ( @pStartDate    DATETIME)
RETURNS @vMonths TABLE ( MonthStart    DATETIME,
                         MonthEnd      DATETIME)
AS
BEGIN

    DECLARE @vCount        INT
    DECLARE @vMonthStart   DATETIME
    DECLARE @vMonthEnd     DATETIME

    SET @vCount = 0
    WHILE @vCount < 12
    BEGIN
        SET @vMonthStart = CAST(YEAR(@pStartDate) AS VARCHAR(4)) + '/' + CAST(MONTH(@pStartDate) AS VARCHAR(2)) + '/01'
        SET @vMonthEnd   = DATEADD(DD, -1, CAST(YEAR(DATEADD(MM, 1, @pStartDate)) AS VARCHAR(4)) + '/' +
                           CAST(MONTH(DATEADD(MM, 1, @pStartDate)) AS VARCHAR(2)) + '/01')
        INSERT INTO @vMonths ( MonthStart, MonthEnd )
        VALUES ( @vMonthStart, @vMonthEnd )

        SET @vCount = @vCount + 1
        SET @pStartDate = DATEADD(MM, -1, @pStartDate)
    END

    RETURN
END

Then, use this table in your query, like this:

SELECT CM.Company_Name, A.MonthStart, SUM(QTD.Extended_Price_Shipped) AS Sales, Sum(NullAmt) as amt,
FROM dbo.ufn_GetMonths(@Start_Date) A
LEFT JOIN  dbo.SalesOrder QT
ON QT.Order_Date BETWEEN A.MOnthStart AND B.MonthEnd
LEFT OUTER JOIN
        dbo.SalesOrderDetail QTD
          ON QT.Order_ID = QTD.FK_order_ID
Left OUTER JOIN
     dbo.Company CM
     on CM.Company_ID = QT.FK_Company_ID
where
QT.Closed = 1
and cm.company_name is not null
and cm.company_name not like '%VOID%'
GROUP BY CM.Company_Name, A.MonthStart
ORDER BY CM.Company_Name, A.MonthStart
0
 

Author Comment

by:dcaparaso
ID: 13964724
Alright, I altered your function a little bit, I wanted to also have the names of the months.
(here's the function - keep reading after it)
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

ALTER  FUNCTION [dbo].[ufn_GetXMonthsBack]
(
@dtStartDate    DATETIME,
@intMonthsback int = 12
)

RETURNS @vMonths TABLE ( MonthStart    DATETIME,
                         MonthEnd      DATETIME,
                   MonthBackName nvarchar(10),
                   MonthCalendarName nvarchar(20))
AS
BEGIN

    DECLARE @nvMonthBackName nvarchar(10)
    DECLARE @nvMonthCalendarName nvarchar(20)
    DECLARE @vMonthStart   DATETIME
    DECLARE @vMonthEnd     DATETIME
    DECLARE @intCount int
    DECLARE @dtcurMonthStart datetime
    Declare @dtcurMonthEnd datetime

      set @vMonthStart = DATEADD(mm, DATEDIFF(mm,0,@dtStartDate), 0)
      set @vMonthEnd = DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,@vMonthStart  )+1, 0))
      set @nvMonthCalendarName = DATENAME(month, @vMonthStart) + ' ' + cast(Year(@vMonthStart) as char(4))
      set @nvMonthBackName = 'Month_0'

        INSERT INTO @vMonths ( MonthStart, MonthEnd, MonthBackName, MonthCalendarName )
        VALUES ( @vMonthStart, @vMonthEnd, @nvMonthBackName, @nvMonthCalendarName )
      
    SET @intCount = 1
    WHILE @intCount <= @intMonthsback -1
    BEGIN
      Set @nvMonthBackName = 'Month_' + cast(@intcount as nvarchar(4))
      SET @vMonthStart = DATEADD(mm, -1, @vMonthStart)
        SET @vMonthEnd   = DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,@vMonthStart  )+1, 0))
      set @nvMonthCalendarName = DATENAME(month, @vMonthStart) + ' ' + cast(Year(@vMonthStart) as char(4))
      
        INSERT INTO @vMonths ( MonthStart, MonthEnd, MonthBackName, MonthCalendarName )
        VALUES ( @vMonthStart, @vMonthEnd, @nvMonthBackName, @nvMonthCalendarName )

        SET @intCount = @intCount + 1
       
    END

    RETURN
END


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


here's my new select query

select sh.customer_display_name, fn.MonthCalendarName,
sum(sh.extended_price) as Sales
From ufn_GetXMonthsBack('4/1/2005', 3) fn
left outer join vw_sales_history sh
on sh.invoice_date between fn.monthstart and fn.monthend
group by fn.monthcalendarname, sh.customer_display_name

my problem is it doesn't the months where the customer had no sales, i just leaves it out, i'd like to see 0.


0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13986567
>> my problem is it doesn't the months where the customer had no sales, i just leaves it out, i'd like to see 0. <<

I just tried the function and it showed me the desired output.  Can you please post some sample data and the output the you received.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

826 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question