SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 virtual studio... various dates		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pavlos
Yak Posting Veteran

Greece
58 Posts
Posted - 12/20/2011 :  22:19:54  Show Profile  Reply with Quote
SELECT
OCRD.State1 AS STATE, OCRD.CardName AS STORE,
OCRD.U_LOC_NO AS STORE_ID,
SUM([@MCS_BUDGET_LINES].U_Amount) AS TARGET,
SUM([@MCS_BUDGET_LINES].U_Amount) AS MTD_TARGET
FROM

[@MCS_BUDGET_HDR] INNER JOIN
[@MCS_BUDGET_LINES] ON
[@MCS_BUDGET_HDR].Code = [@MCS_BUDGET_LINES].Code INNER JOIN
OCRD ON
[@MCS_BUDGET_HDR].Code = OCRD.CardCode

GROUP BY OCRD.State1, OCRD.CardName, OCRD.U_LOC_NO, U_Date
HAVING
(OCRD.U_LOC_NO IS NOT NULL)


hope that code makes sense guys

now for the table this creates.. it will create 5 coloumns.. The Target and MTD_TARGET i want to specify where clauses..
Eg.
Target will be when the date is between '2011-12-01' and '2011-12-31'
and
MTD Target will be when the date is between '2011-12-01' and getdate() being the current date.

I have tried entering it in various filter sections in visula studio with no luck so I just thought I would copy and past the code.

RickD
Slow But Sure Yak Herding Master

United Kingdom
3560 Posts
Posted - 12/21/2011 :  03:51:39  Show Profile  Reply with Quote
SELECT
OCRD.State1 AS STATE, OCRD.CardName AS STORE,
OCRD.U_LOC_NO AS STORE_ID,
SUM([@MCS_BUDGET_LINES].U_Amount) AS TARGET,
SUM([@MCS_BUDGET_LINES].U_Amount) AS MTD_TARGET
FROM

[@MCS_BUDGET_HDR] INNER JOIN
[@MCS_BUDGET_LINES] ON
[@MCS_BUDGET_HDR].Code = [@MCS_BUDGET_LINES].Code INNER JOIN
OCRD ON
[@MCS_BUDGET_HDR].Code = OCRD.CardCode
WHERE TARGET > '2011-12-01' AND TARGET <= '2011-12-31'
AND MTD_TARGET > '2011-12-01' AND MTD_TARGET <= getdate()
GROUP BY OCRD.State1, OCRD.CardName, OCRD.U_LOC_NO, U_Date
HAVING
(OCRD.U_LOC_NO IS NOT NULL)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47023 Posts
Posted - 12/21/2011 :  12:34:17  Show Profile  Reply with Quote
you can add those conditions inside SUM() function

like

...
SUM(CASE WHEN U_Date BETWEEN '2011-12-01' and '2011-12-31' THEN [@MCS_BUDGET_LINES].U_Amount ELSE 0 END) AS TARGET, 
SUM(CASE WHEN U_Date BETWEEN '2011-12-01' and getdate()  THEN [@MCS_BUDGET_LINES].U_Amount ELSE 0 END) AS MTD_TARGET

...


you can even replace hardcoded values with variable/parameters above to add more dynamicity

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000