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.
| Author |
Topic |
|
pavlos
Yak Posting Veteran
59 Posts |
 Posted - 2011-12-20 : 22:19:54
|
| 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_TARGETFROM [@MCS_BUDGET_HDR] INNER JOIN[@MCS_BUDGET_LINES] ON [@MCS_BUDGET_HDR].Code = [@MCS_BUDGET_LINES].Code INNER JOINOCRD ON [@MCS_BUDGET_HDR].Code = OCRD.CardCodeGROUP BY OCRD.State1, OCRD.CardName, OCRD.U_LOC_NO, U_DateHAVING (OCRD.U_LOC_NO IS NOT NULL)hope that code makes sense guysnow 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'andMTD 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
3608 Posts |
Posted - 2011-12-21 : 03:51:39
|
| 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_TARGETFROM [@MCS_BUDGET_HDR] INNER JOIN[@MCS_BUDGET_LINES] ON [@MCS_BUDGET_HDR].Code = [@MCS_BUDGET_LINES].Code INNER JOINOCRD ON [@MCS_BUDGET_HDR].Code = OCRD.CardCodeWHERE 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_DateHAVING (OCRD.U_LOC_NO IS NOT NULL) |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-21 : 12:34:17
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
virtual studio... various dates