Link to home
Start Free TrialLog in
Avatar of sassy168
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

Open in new window

runningtotal3.jpg
Avatar of Sharath S
Sharath S
Flag of United States of America image


Because that it the running total. Check this script. If this is not your expected result, let me know.
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
ORDER BY CONVERT(varchar(20), datestamp, 101), Board_ID

Open in new window

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

Open in new window

Avatar of sassy168
sassy168

ASKER

oh sql server always puts that TOP 100 percent on default when you put it in a view!
i got this error
Which query you ran? Whose query generated this error?
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?
ASKER CERTIFIED SOLUTION
Avatar of Sharath S
Sharath S
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thanks much appreciated! I can sleep tonite!
Glad that it helped you.
okay..maybe one more thing!

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

Open in new window