Date Code Problem - 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

General SQL Server Forums

New to SQL Server Programming

Date Code Problem

Author

Topic

dibblejon
Starting Member

36 Posts

Posted - 2010-02-16 : 11:34:40

Hi

I am building an SSRS report and need to create a dataset that returns the sales data for months -7 to -12 on a rolling basis. Can someone please help me with my broken code below?


SELECT        ACCOUNT, [Qty x Price], MONTH(DATE) AS Expr1, FAMILY_NAME

FROM            SalesHistoryView

WHERE         DATEADD(MONTH, - 12, GETDATE()) AND (MONTH(DATE) BETWEEN - 7 AND - 12)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-16 : 12:02:36

sounds like this

SELECT        ACCOUNT, [Qty x Price], MONTH(DATE) AS Expr1, FAMILY_NAME

FROM            SalesHistoryView

WHERE         [DATE] BETWEEN DATEADD(month,DATEDIFF(month,0,[DATE])-12,0) AND DATEADD(month,DATEDIFF(month,0,[DATE])-7,0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dibblejon
Starting Member

36 Posts

Posted - 2010-02-17 : 05:11:26

Thanks - that's brilliant.

Is it now possible to build the query to follow the logic :

If the date is in the months 0 to -6 m then return a sales total, Else if the date is in months
-12 to -7 then return the sales total? And display as two sepearate expressions? For example Period 1 Sales vs Period 2 Sales.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-17 : 05:17:40

quote:
Originally posted by dibblejon

Thanks - that's brilliant.

Is it now possible to build the query to follow the logic :

If the date is in the months 0 to -6 m then return a sales total, Else if the date is in months
-12 to -7 then return the sales total? And display as two sepearate expressions? For example Period 1 Sales vs Period 2 Sales.

do you mean this?


SELECT       SUM(CASE WHEN        [DATE] BETWEEN DATEADD(month,DATEDIFF(month,0,GETDATE())-6,0) AND DATEADD(month,DATEDIFF(month,0,GETDATE()),0) THEN 
THEN SalesAmt END) AS SalesTotal1,
 SUM(CASE WHEN        [DATE] BETWEEN DATEADD(month,DATEDIFF(month,0,GETDATE())-12,0) AND DATEADD(month,DATEDIFF(month,0,GETDATE())-7,0) THEN 
THEN SalesAmt END) AS SalesTotal2
FROM            SalesHistoryView

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dibblejon
Starting Member

36 Posts

Posted - 2010-02-17 : 05:24:04

Hi
Yes I think that's what I need. I need to insert into this query though, is it possible?


SELECT     dbo.SalesHistory.ACCOUNT, dbo.SalesHistory.PART, dbo.Stock.DESCRIPTION, dbo.Stock.FAMILY, dbo.SalesHistory.QTY * dbo.SalesHistory.PRICE AS [Qty x Price],                       dbo.SalesHistory.DISC1, dbo.CUSTOMER.POST, dbo.SalesHistory.DATE, dbo.Family.FAMILY_NAME
FROM         dbo.SalesHistory INNER JOIN                      dbo.Stock ON dbo.SalesHistory.PART = dbo.Stock.PART INNER JOIN                      dbo.Family ON dbo.Stock.FAMILY = dbo.Family.FAMILY LEFT OUTER JOIN                      dbo.CUSTOMER ON dbo.SalesHistory.ACCOUNT = dbo.CUSTOMER.ACCOUNT
WHERE     (dbo.Stock.FAMILY IN ('800', '600', '1400', '2200', '2000', '5600', '200', '3000', '3600', '5200')) AND (dbo.SalesHistory.DATE >= DATEADD(YEAR, - 2, GETDATE()))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-17 : 05:25:58

so you want it for each SalesHistory.PART value?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dibblejon
Starting Member

36 Posts

Posted - 2010-02-17 : 05:33:22

Hi

No it needs to total the fields below. I really appreciate your help, thanks.

dbo.SalesHistory.QTY * dbo.SalesHistory.PRICE AS [Qty x Price]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-17 : 05:37:31

quote:
Originally posted by dibblejon

Hi

No it needs to total the fields below. I really appreciate your help, thanks.

dbo.SalesHistory.QTY * dbo.SalesHistory.PRICE AS [Qty x Price]

thats ok. but what all fields you want to group on?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dibblejon
Starting Member

36 Posts

Posted - 2010-02-17 : 05:43:31

I was going to run this as a VIEW in SQL and then add all the grouping in BIDS for SSRS. If it needs to be grouped here then ideally I would group by ACCOUNT, FAMILY_NAME so that I had a total for Period 1 for each ACCOUNT by FAMILY_NAME and the same for Period 2.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-17 : 05:50:30

quote:
Originally posted by dibblejon

I was going to run this as a VIEW in SQL and then add all the grouping in BIDS for SSRS. If it needs to be grouped here then ideally I would group by ACCOUNT, FAMILY_NAME so that I had a total for Period 1 for each ACCOUNT by FAMILY_NAME and the same for Period 2.

sorry i may better rephrase my question
what all fields you want to display.
can you post a sample output that you expect out of view?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dibblejon
Starting Member

36 Posts

Posted - 2010-02-17 : 05:51:51

Have put your code into query but as you suggested need to add grouping otherwise it falls over!

SELECT     dbo.SalesHistory.ACCOUNT, dbo.SalesHistory.PART, dbo.Stock.DESCRIPTION, dbo.Stock.FAMILY,
           dbo.SalesHistory.DISC1, dbo.CUSTOMER.POST, dbo.SalesHistory.DATE, dbo.Family.FAMILY_NAME,
                    
         SUM(CASE WHEN[DATE] BETWEEN DATEADD(month,DATEDIFF(month,0,GETDATE())-6,0) AND DATEADD(month,DATEDIFF(month,0,GETDATE()),0) THEN 
 dbo.SalesHistory.QTY * dbo.SalesHistory.PRICE END)AS SalesTotal1,
 SUM(CASE WHEN        [DATE] BETWEEN DATEADD(month,DATEDIFF(month,0,GETDATE())-12,0) AND DATEADD(month,DATEDIFF(month,0,GETDATE())-7,0) THEN 
 dbo.SalesHistory.QTY * dbo.SalesHistory.PRICE END) AS SalesTotal2


FROM         dbo.SalesHistory INNER JOIN
                      dbo.Stock ON dbo.SalesHistory.PART = dbo.Stock.PART INNER JOIN
                      dbo.Family ON dbo.Stock.FAMILY = dbo.Family.FAMILY LEFT OUTER JOIN
                      dbo.CUSTOMER ON dbo.SalesHistory.ACCOUNT = dbo.CUSTOMER.ACCOUNT
WHERE     (dbo.Stock.FAMILY IN ('800', '600', '1400', '2200', '2000', '5600', '200', '3000', '3600', '5200'))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-17 : 06:23:56

quote:

Originally posted by dibblejon

Have put your code into query but as you suggested need to add grouping otherwise it falls over!

SELECT     dbo.SalesHistory.ACCOUNT, dbo.SalesHistory.PART, dbo.Stock.DESCRIPTION, dbo.Stock.FAMILY,
           dbo.SalesHistory.DISC1, dbo.CUSTOMER.POST, dbo.SalesHistory.DATE, dbo.Family.FAMILY_NAME,
                    
         SUM(CASE WHEN[DATE] BETWEEN DATEADD(month,DATEDIFF(month,0,GETDATE())-6,0) AND DATEADD(month,DATEDIFF(month,0,GETDATE()),0) THEN 
 dbo.SalesHistory.QTY * dbo.SalesHistory.PRICE END)AS SalesTotal1,
 SUM(CASE WHEN        [DATE] BETWEEN DATEADD(month,DATEDIFF(month,0,GETDATE())-12,0) AND DATEADD(month,DATEDIFF(month,0,GETDATE())-7,0) THEN 
 dbo.SalesHistory.QTY * dbo.SalesHistory.PRICE END) AS SalesTotal2


FROM         dbo.SalesHistory INNER JOIN
                      dbo.Stock ON dbo.SalesHistory.PART = dbo.Stock.PART INNER JOIN
                      dbo.Family ON dbo.Stock.FAMILY = dbo.Family.FAMILY LEFT OUTER JOIN
                      dbo.CUSTOMER ON dbo.SalesHistory.ACCOUNT = dbo.CUSTOMER.ACCOUNT
WHERE     (dbo.Stock.FAMILY IN ('800', '600', '1400', '2200', '2000', '5600', '200', '3000', '3600', '5200'))

thats why i asked you to post your expected output along with sample data out of which you get it

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dibblejon
Starting Member

36 Posts

Posted - 2010-02-17 : 06:38:31

Your several steps ahead of me!

Here is my final working code : Thanks for all your help.

SELECT     TOP (100) PERCENT dbo.SalesHistory.ACCOUNT, dbo.Family.FAMILY, dbo.SalesHistory.DATE, dbo.Family.FAMILY_NAME, SUM(CASE WHEN [DATE] BETWEEN 
                      DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 6, 0) AND DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) 
                      THEN dbo.SalesHistory.QTY * dbo.SalesHistory.PRICE END) AS SalesTotal1, SUM(CASE WHEN [DATE] BETWEEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) 
                      - 12, 0) AND DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 7, 0) THEN dbo.SalesHistory.QTY * dbo.SalesHistory.PRICE END) AS SalesTotal2
FROM         dbo.SalesHistory INNER JOIN
                      dbo.Stock ON dbo.SalesHistory.PART = dbo.Stock.PART INNER JOIN
                      dbo.Family ON dbo.Stock.FAMILY = dbo.Family.FAMILY LEFT OUTER JOIN
                      dbo.CUSTOMER ON dbo.SalesHistory.ACCOUNT = dbo.CUSTOMER.ACCOUNT
WHERE     (dbo.Stock.FAMILY IN ('800', '600', '1400', '2200', '2000', '5600', '200', '3000', '3600', '5200')) AND (dbo.SalesHistory.DATE >= DATEADD(MONTH, - 12, GETDATE()))
GROUP BY dbo.SalesHistory.ACCOUNT, dbo.Family.FAMILY, dbo.Family.FAMILY_NAME, dbo.SalesHistory.DATE
ORDER BY dbo.SalesHistory.ACCOUNT

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-17 : 06:42:48

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources