CODEWITHSUNDEEP
sqlsql-server-2008t-sql
KTate
KTate

Reputation: 57

How to use a Date Field for Yesterday orders and YTD orders for customers?

My boss wants to have a report that he reviews that would should him every sales order entered from the day before (yesterday). With just seeing the snapshot details of the sales orders he also wants a view of the customer's Year-To-Date sales history at our company.

My issue is that I am using the field T0.[DocDate] in the WHERE clause to only show the day before's sales. How would I also query up the related T0.[CardName] and their YTD history for: YTD Sales Total, YTD Gross Profit, YTD GP%?

Here is the code that I have typed thus far:

SELECT  T0.[DocDate] AS 'Date',
        T0.[DocNum] AS 'SO #',
        T0.[CardName] AS 'Customer',        
        ROUND(T0.[DocTotal],2) AS 'Sales Total',
        ROUND(T0.[GrosProfit],2) AS 'GP',
        ROUND(((T0.[DocTotal]-(t0.[DocTotal]-t0.[GrosProfit]))/NULLIF(t0.[DocTotal],0)*100),2) AS 'GM%'

FROM    dbo.ORDR T0 

WHERE   CAST(GETDATE()-1 AS DATE) = T0.DocDate 

ORDER BY    T0.[DocNum]

FOR BROWSE

In the code you'll notice I haven't added in the SELECT statement any mention of YTD columns. When I added the fields it never would work because I have no clue how to query the YTD info. Is it even possible? Do I need a Sub-Query?

Any advice on this would be very much obliged!!

Thanks.

I am now trying to use the UNION ALL for the code and I feel SOOO CLOSE but the 2 columns I union at the end total everything up for the date range of year to date. How do I get the query to recongize that I want it to find the customer and apply that formula to the related customer. Not as an overall SUM?

Here is the new code:

SELECT  T0.[DocDate] AS 'Date',
        T0.[DocNum] AS 'SO #',
        T0.[CardName] AS 'Customer',        
        ROUND(T0.[DocTotal],2) AS 'Sales Total',
        ROUND(T0.[GrosProfit],2) AS 'GP',
        ROUND(((T0.[DocTotal]-(t0.[DocTotal]-t0.[GrosProfit]))/NULLIF(t0.[DocTotal],0)*100),2) AS 'GM%',
        NULL,
        NULL

FROM    dbo.ORDR T0 

WHERE   CAST(GETDATE()-1 AS DATE) = T0.DocDate 

UNION ALL

SELECT NULL,NULL,NULL,NULL,NULL,NULL,SUM(T0.[DocTotal]),SUM(T0.[GrosProfit])

FROM dbo.ORDR T0

WHERE   CAST(GETDATE()-365 AS DATE) >= T0.[DocDate]

Upvotes: 1

Views: 483

Answers (2)

Sam CD
Sam CD

Reputation: 2097

If you want them side by side, you can use a join:

SELECT  T0.[DocDate] AS 'Date',
        T0.[DocNum] AS 'SO #',
        T0.[CardName] AS 'Customer',        
        ROUND(T0.[DocTotal],2) AS 'Sales Total',
        ROUND(T0.[GrosProfit],2) AS 'GP',
        ROUND(((T0.[DocTotal]-(t0.[DocTotal]-t0.[GrosProfit]))/NULLIF(t0.[DocTotal],0)*100),2) AS 'GM%',
    T1.[Sales Total][Sales Total YTD],
    T1.GP [GP YTD],
    T1.[No. of Orders][Orders YTD]


FROM    dbo.ORDR T0 
INNER JOIN (SELECT
    count(T0.[DocNum]) AS 'No. of Orders',
    T0.[CardName] as 'Customer',        
    sum(ROUND(T0.[DocTotal],2)) AS 'Sales Total',
    sum(ROUND(T0.[GrosProfit],2)) AS 'GP'
    ROUND(((sum(T0.[DocTotal])-(sum(t0.[DocTotal]-t0.[GrosProfit])))/NULLIF(t0.sum([DocTotal]),0)*100),2) AS 'GM%'
FROM dbo.ORDR T0 
WHERE T0.DocDate between CAST(dateadd(yy,-1,GETDATE()) AS DATE) 
and CAST(GETDATE()-1 AS DATE)
group by t0.cardname) T1
on T0.CardName = T1.Customer    
WHERE   CAST(GETDATE()-1 AS DATE) = T0.DocDate 

ORDER BY    T0.[DocNum]

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

SELECT cast(min(T0.[DocDate]) as date) AS 'Start Date',
    cast(max(T0.[DocDate]) as date) AS 'End Date',
    count(T0.[DocNum]) AS 'No.of Orders',
    T0.[CardName] AS 'Customer',        
    sum(ROUND(T0.[DocTotal],2)) AS 'Sales Total',
    sum(ROUND(T0.[GrosProfit],2)) AS 'GP'
    -- ROUND(((T0.[DocTotal]-(t0.[DocTotal]-t0.[GrosProfit]))/NULLIF(t0.[DocTotal],0)*100),2) AS 'GM%'
FROM dbo.ORDR T0 
WHERE T0.DocDate between CAST(dateadd(yy,-1,GETDATE()) AS DATE) 
and CAST(GETDATE()-1 AS DATE)
group by t0.cardname

Try this for YTD sales.

Edit:

SELECT  T0.[DocDate] AS 'Date',
T0.[DocNum] AS 'SO #',
T0.[CardName] AS 'Customer', 
ROUND(T0.[DocTotal],2) AS 'Sales Total',
ROUND(T0.[GrosProfit],2) AS 'GP', 
ROUND(((T0.[DocTotal]-(t0.[DocTotal]-t0.[GrosProfit]))/NULLIF(t0.[DocTotal],0)‌​*100),2) AS 'GM%', 
NULL as Total, NULL as Total_Gross_Profit  -- '', '' 
FROM dbo.ORDR T0
WHERE CAST(GETDATE()-1 AS DATE) = T0.DocDate
UNION ALL 
SELECT NULL, NULL, NULL, NULL,NULL, NULL --'','','','','','',
SUM(T0.[DocTotal]),
SUM(T0.[GrosProfit]) 
FROM dbo.ORDR T0
WHERE CAST(GETDATE()-365 AS DATE) >= T0.[DocDate]

Upvotes: 1

Related Questions