sassy168
asked on
Running TOtals query help
I have the following query to give me these results. How can I show 0 in all the dates beyond the current date? currently its at 378537 all the way till end of the month.
SELECT TOP 100 PERCENT CONVERT(varchar(20), datestamp, 101) AS DateStamp, Board_ID, SUM(Actual_Qty) AS actual_qty_total, SUM(Target_Qty) AS target_qty_total,
(SELECT SUM(Actual_Qty) AS Expr1
FROM dbo.TB_Actual AS t2
WHERE (CONVERT(varchar(20), datestamp, 101) <= CONVERT(varchar(20), t1.datestamp, 101)) AND (datestamp >= DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0))
AND (t1.Board_ID = Board_ID)) AS running_actual,
(SELECT SUM(Target_Qty) AS Expr1
FROM dbo.TB_Actual AS t3
WHERE (CONVERT(varchar(20), datestamp, 101) <= CONVERT(varchar(20), t1.datestamp, 101)) AND (datestamp >= DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0))
AND (t1.Board_ID = Board_ID)) AS running_target
FROM dbo.TB_Actual AS t1
WHERE (datestamp >= DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)) AND (datestamp < DATEADD(m, DATEDIFF(m, 0, GETDATE()) + 1, 0)) AND (Board_ID = '1')
GROUP BY CONVERT(varchar(20), datestamp, 101), Board_ID
ORDER BY CONVERT(varchar(20), datestamp, 101), Board_ID
runningtotal3.jpg
Try the query below and why do you have TOP 100 PERCENT. Its meaningless right over in your query as you are going to fetch all set of records.
SELECT case when datestamp > getdate() then CONVERT(varchar(20), datestamp, 101)
else '0' endAS DateStamp, Board_ID, SUM(Actual_Qty) AS actual_qty_total, SUM(Target_Qty) AS target_qty_total,
(SELECT SUM(Actual_Qty) AS Expr1
FROM dbo.TB_Actual AS t2
WHERE (CONVERT(varchar(20), datestamp, 101) <= CONVERT(varchar(20), t1.datestamp, 101)) AND (datestamp >= DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0))
AND (t1.Board_ID = Board_ID)) AS running_actual,
(SELECT SUM(Target_Qty) AS Expr1
FROM dbo.TB_Actual AS t3
WHERE (CONVERT(varchar(20), datestamp, 101) <= CONVERT(varchar(20), t1.datestamp, 101)) AND (datestamp >= DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0))
AND (t1.Board_ID = Board_ID)) AS running_target
FROM dbo.TB_Actual AS t1
WHERE (datestamp >= DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)) AND (datestamp < DATEADD(m, DATEDIFF(m, 0, GETDATE()) + 1, 0)) AND (Board_ID = '1')
GROUP BY CONVERT(varchar(20), datestamp, 101), Board_ID
ORDER BY CONVERT(varchar(20), datestamp, 101), Board_ID
ASKER
oh sql server always puts that TOP 100 percent on default when you put it in a view!
ASKER
i got this error
Which query you ran? Whose query generated this error?
ASKER
sharath yours ran fine , error on rrjegan17's query.
okay last thing i need, and i am done!! the datestamp is displaying as regular date. My old query i was displaying only the 2 digit day and 2 digit month.
SELECT RIGHT ('0' + RTRIM(MONTH(datestamp)), 2) + '/' + RIGHT ('0' + RTRIM(DAY(datestamp))
how can i replace it to show the date like that?
okay last thing i need, and i am done!! the datestamp is displaying as regular date. My old query i was displaying only the 2 digit day and 2 digit month.
SELECT RIGHT ('0' + RTRIM(MONTH(datestamp)), 2) + '/' + RIGHT ('0' + RTRIM(DAY(datestamp))
how can i replace it to show the date like that?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks much appreciated! I can sleep tonite!
Glad that it helped you.
ASKER
okay..maybe one more thing!
i need one extra column for the difference between running total and runing target......is that hard to do?
i need one extra column for the difference between running total and runing target......is that hard to do?
check this.
SELECT DateStamp,Board_ID,actual_qty_total,target_qty_total,running_actual,running_target,running_actual - running_target AS Qty_diff
FROM (
SELECT DateStamp,Board_ID,actual_qty_total,target_qty_total,
CASE WHEN (CONVERT(varchar(20), datestamp, 101) > CONVERT(varchar(20), getdate(), 101)) THEN 0 ELSE running_actual END AS running_actual,
running_target
FROM (
SELECT CONVERT(varchar(20), datestamp, 101) AS DateStamp, Board_ID,
SUM(Actual_Qty) AS actual_qty_total,
SUM(Target_Qty) AS target_qty_total,
(SELECT SUM(Actual_Qty) AS Expr1
FROM dbo.TB_Actual AS t2
WHERE (CONVERT(varchar(20), datestamp, 101) <= CONVERT(varchar(20), t1.datestamp, 101)) AND (datestamp >= DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0))
AND (t1.Board_ID = Board_ID)) AS running_actual,
(SELECT SUM(Target_Qty) AS Expr1
FROM dbo.TB_Actual AS t3
WHERE (CONVERT(varchar(20), datestamp, 101) <= CONVERT(varchar(20), t1.datestamp, 101)) AND (datestamp >= DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0))
AND (t1.Board_ID = Board_ID)) AS running_target
FROM dbo.TB_Actual AS t1
WHERE (datestamp >= DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)) AND (datestamp < DATEADD(m, DATEDIFF(m, 0, GETDATE()) + 1, 0))
AND (Board_ID = '1')
GROUP BY CONVERT(varchar(20), datestamp, 101), Board_ID ) t4 ) t5
ORDER BY CONVERT(varchar(20), datestamp, 101), Board_ID
Because that it the running total. Check this script. If this is not your expected result, let me know.
Open in new window