Database

Re: How to loop or something a temp distinct table to sum up the totals

6-Jan-14 6:06

The Qty comes from like a shopping cart history table. So all cart items sold on the requested report date may contain 20 orders for the same item, so that day, 7 people ordered the same item of qty 1, so the qty would be 7. 7 was the result I was looking for to make sure the math was right.

My explanation below is crude, but it should represent the thought. I have this bad feeling about this, I think your going to rip me a new one here Poke tongue | ;-P

Shopping Cart History
date ------- PN  ---------- Qty    
08/29/2013 - 8AM  06-CR10   1
08/29/2013 - 9AM  06-CR10   1
08/29/2013 - 10AM 06-CR10   1
08/29/2013 - 11AM 12-ZZZ1   2
So the Qty would be 3

the result set would be

Items requiring fulfillment today
PN --------Qty
06-CR10    3
12-ZZZ1    2

modified 6-Jan-14 12:28pm.

Jörgen Andersson5-Jan-14 22:16

Jörgen Andersson

5-Jan-14 22:16

I believe you can remove also the last temptable:

SQL

DECLARE <a href="/Members/Year">@Year</a>AS int;
DECLARE <a href="/Members/month">@Month</a>AS int;
DECLARE <a href="/Members/Day">@Day</a>AS int;
SET <a href="/Members/Year">@Year</a>= 2013;
SET <a href="/Members/month">@Month</a>= 8;
SET <a href="/Members/Day">@Day</a>= 29;
 
DECLARE @startDate AS DATE;
DECLARE @stopDate AS DATE;
SET @startDate = DATETIMEFROMPARTS(@Year, <a href="/Members/month">@Month</a> <a href="/Members/Day">@Day</a> 0, 0, 0, 0);
SET @stopDate = DATETIMEFROMPARTS(@Year, <a href="/Members/month">@Month</a> <a href="/Members/Day">@Day</a> 23, 59, 59, 999);

SELECT PartNumber, ThumbNail, SDescription, sum(Qty) Qty, Cost, Price, SUM(Qty * Price) SubTotal From CompletedOrdersCart 
WHERE OrderDate > @startDate AND OrderDate < @stopDate
GROUP BY PartNumber, Thumbnail, SDescription, Cost, Price

And just like the thumbnail, the SDescription belongs in a different table, for the same reasons

jkirkerx wrote:
I tried between, but I would of had to deduct a day, and add a day for start and stop.

My bad,

SQL

WHERE OrderDate BETWEEN @StartDate AND @StopDate

is the same as

SQL

WHERE OrderDate >= @startDate AND OrderDate <= @stopDate

not

SQL

WHERE OrderDate > @startDate AND OrderDate < @stopDate

Wrong is evil and must be defeated.
- Jeff Ello[^]

Re: How to loop or something a temp distinct table to sum up the totals

jkirkerx6-Jan-14 6:15

Re: How to loop or something a temp distinct table to sum up the totals

6-Jan-14 6:15

I think I ended up doing that, and was amazed at how it worked.

Let me check,

This is the SQL in the asp.net function I used for testing. I kept the DateTimeFromParts because I stored the order date as a timestamp, and not a date, and had to be more precise with the hours minutes and seconds. I was originally BETWEEN, but I could not get a result, and it took me hours to figure it out, plus I had upgraded to SQL Server 2012 at the time. Then the timestamp dawned on me, and I spent another hour figuring out how to be more precise. I'd rather use between, and not use anything specific to 2012.

I SUM the Qty to get 7, and it worked on the test data set, not sure if I was just lucky to get that value, I need to find more data on different days to fully check it.

"DECLARE @startDate AS DATE; " & _
        "DECLARE @stopDate AS DATE; " & _
        "SET @startDate = DATETIMEFROMPARTS(@Year, @Month, @Day, 0, 0, 0, 0); " & _
        "SET @stopDate = DATETIMEFROMPARTS(@Year, @Month, @Day, 23, 59, 59, 999); " & _
        "DECLARE @SummaryItems TABLE(" & _
            "PartNumber VarChar(80), " & _
            "ThumbNail VarChar(250), " & _
            "SDescription VarChar(250), " & _
            "Qty INT, " & _
            "Cost Decimal, " & _
            "Price Decimal, " & _
            "SubTotal Decimal " & _
        ") " & _
        "INSERT INTO @SummaryItems (PartNumber, ThumbNail, SDescription, Qty, Cost, Price, SubTotal) " & _
        "SELECT PartNumber, ThumbNail, SDescription, SUM(Qty), Cost, Price, SUM(Qty * Price) From CompletedOrdersCart  " & _
        "WHERE OrderDate > @startDate AND OrderDate < @stopDate " & _
        "GROUP BY PartNumber, Thumbnail, SDescription, Qty, Cost, Price " & _
        "SELECT PartNumber, Thumbnail, SDescription, Qty, Cost, Price, SubTotal FROM @SummaryItems;"

modified 6-Jan-14 12:44pm.

Jörgen Andersson6-Jan-14 8:17

Jörgen Andersson

6-Jan-14 8:17

You need to remove Qty from the GROUP BY or you might get "unexpected" results.

And I believe you can also skip @SummaryItems. It doesn't serve any purpose.

Wrong is evil and must be defeated.
- Jeff Ello[^]

Re: How to loop or something a temp distinct table to sum up the totals

jkirkerx6-Jan-14 7:01

Re: How to loop or something a temp distinct table to sum up the totals

6-Jan-14 7:01

Click!

Oh, I see now what your saying. If the Thumbnail image is changed during the day, I can end up with 2 thumbnail image locations in a dataset. So which one would be right. I'm open to error with my SQL, and it will back fire on me soon, because it's history data.

So I should get rid of the thumbnail, and make a different request for that data. That's why I should store the image data in another table, and use a join. I never thought of that. That could take me a week to change in the entire program, but it must be done.

Slap me in the back of the head.

Jörgen Andersson6-Jan-14 8:36

Jörgen Andersson

6-Jan-14 8:36

It's called normalization, and we have all walked that way.

While the explanations for Normalization in Wikipedia[^] is pretty good.
I personally recommend one[^] made by Peso (a bit of a legend on a different forum), because he's explaining by example.

Wrong is evil and must be defeated.
- Jeff Ello[^]

Re: How to loop or something a temp distinct table to sum up the totals

jkirkerx6-Jan-14 9:02

6-Jan-14 9:02

Wow Thanks for the optimization tips!

I do need to expand my knowledge here and use the tools right. I'll play around with the TSQL in SQL Manager to see the various results and read the lessons.

The temp table was cool to know, but if it can be done without it, then better.

Not work correctly

Member 100045982-Jan-14 23:05

Member 10004598

2-Jan-14 23:05

Hi

I try to update a value inside a table (ExTable01) but updated value is not true.Please help.

When I apply update Saved wrong value is 891257536,00 (Column type real False ) but True value is 891257560 ( --> 97352 * 9155 = 891257560 )

Best regards

Shameel2-Jan-14 23:14

2-Jan-14 23:14

Which database system? And how are you updating? If through an update statement, where is it?

Member 100045983-Jan-14 0:07

Member 10004598

3-Jan-14 0:07

MSSQL-TSQL
From Calucaltor TRUE RESULT : 164016 * 9155 = 1501566480

I check form profiler:

Update TestTable Set Con=@Con, Ust=@Ust, En =@En,Tem=@Tem, Where ID=@ID',N'@Con decimal(6,0),@Ust decimal(4,0),@En decimal(10,0),@Tem decimal(3,2), ID bigint',@Con=164016,@Ust=9155,@En=1501566480,@Tem=7.50,@ID=95452

Finally I check database to see value:

select str(En,25,5) as ToSeevalue, En, Con , Ust,* from TestTable where ID = 95452
-- REsult (WRONG OUTCOME):
ToSeevalue: 1501566464.00000 En: 1,501566E+09 Con: 164016 Ust: 9155

Shameel3-Jan-14 0:41

3-Jan-14 0:41

I don't understand how ToSeevalue is updated, is it a calculated field?

Richard MacCutchan3-Jan-14 1:11

Richard MacCutchan

3-Jan-14 1:11

What do you mean by "wrong outcome"? Please explain exactly what, in the above, is considered wrong.

Veni, vidi, abiit domum

Simon_Whale3-Jan-14 1:53

Simon_Whale

3-Jan-14 1:53

Also what types are the following?

Con
Ust
En

Every day, thousands of innocent plants are killed by vegetarians.

Help end the violence EAT BACON

Member 100045983-Jan-14 3:57

Member 10004598

3-Jan-14 3:57

I found a solution .Column data type was real I changed it.Now it's decimal (38,4)

Shameel6-Jan-14 3:40

insert information of filed to another fielads

6-Jan-14 3:40

Update the original question and the title to indicate that your issue has been fixed. Also, please describe your fix.

f,amiri88227-Dec-13 9:11

f,amiri882

27-Dec-13 9:11

hello!
I have a table that it has a field.
the content of this field Containing different of information such as phone number,address and ...
I separated this filed.This means that for each group I have create
a table like main table(number of field and datatype)
now I want add fields to main table that number of added field equal number of table.but I don't know how insert this information to this fields?
please help me.

Re: insert information of filed to another fielads

Andrius Leonavicius27-Dec-13 12:23

Andrius Leonavicius

27-Dec-13 12:23

Hi,

Could you post some example? I am not sure what you are trying to achieve...

Re: insert information of filed to another fielads

Shameel2-Jan-14 4:09