|
Now I just need to add up the total Qty for each item. The SubTotal is correct, but the Qty is still just 1, should be like 7 for the first line.
Let me see if I can solve this part myself. I may have a question
Thanks!
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);
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, 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;
This is what I got returned, the Qty just needs to be 7, so 7 * 44.00 = 308.00
PN, Thumbnail, Description, Qty, Cost, Price, SubTotal
06-10, /Images/thumbnail/06-10.jpg, 10 inch Continuious Blade, 1, 22.00, 44.00, 308.00
FYI:
I tried between, but I would of had to deduct a day, and add a day for start and stop.
|
|
|
|
|
Where does the QTY come from, and why should it be 7 ? if it's the result of a different group by, you can get it with a CTE[^], and then join it back in to your query.
CTEs are useful when you need to return two calculated values based on different group by statements.
Christian Graus
My new article series is all about SQL !!!
|
|
|
|
|
The partnumber and thumbnail has changed since the original post, I believe you can safely assume the database contents or some other circumstances has also changed.
|
|
|
|
|
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 
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.
|
|
|
|
|
I believe you can remove also the last temptable:
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,
WHERE OrderDate BETWEEN @StartDate AND @StopDate is the same as
WHERE OrderDate >= @startDate AND OrderDate <= @stopDate
not
WHERE OrderDate > @startDate AND OrderDate < @stopDate
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
Which database system? And how are you updating? If through an update statement, where is it?
|
|
|
|
|
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
|
|
|
|
|
I don't understand how ToSeevalue is updated, is it a calculated field?
|
|
|
|
|
What do you mean by "wrong outcome"? Please explain exactly what, in the above, is considered wrong.
Veni, vidi, abiit domum
|
|
|
|
|
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
|
|
|
|
|
I found a solution .Column data type was real I changed it.Now it's decimal (38,4)
|
|
|
|
|
Update the original question and the title to indicate that your issue has been fixed. Also, please describe your fix.
|
|
|
|
|
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.
|
|
|
|
|
Hi,
Could you post some example? I am not sure what you are trying to achieve...
|
|
|
|
|
You've already asked a similar question in the same forum, please do not litter the forum with duplicate questions. If you wish to add information to your question, edit your original question and add the information there. Posting duplicate questions will not get you any helpful answers unless you explain your problem well. If English is not your primary language, you can explain your problem with examples.
|
|
|
|
|
I have a database application I was designing that I haven't worked on in a while. Recently I started it up to start working on it again and I was getting a connection error when trying to connect to the database. The error message is:
"A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond"
and the connection string is "Server=localhost;Database=medicehr_provider;Uid=root;Pwd=********;"
However if I replace 'localhost' with '127.0.0.1' then my application can connect to the MySql server. I can't figure out why all of a sudden it needs the actual IP address. I checked the hosts file in C:\Windows\System32\Drivers\etc and it does contain an entry for localhost.
Any ideas or suggestions why 'localhost' isn't being recognized and '127.0.0.1' is would be greatly appreciated.
|
|
|
|
|
Can you ping localhost? If yes, then it's probably the driver - did you recently update it?
DisIsHoody wrote: it does contain an entry for localhost.
In which form?
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Eddy Vluggen wrote: Can you ping localhost?
That isn't really going to tell you anything. If it succeeds it tells you nothing about the database. If it fails, if that is even possible, it just means localhost is redirected. But still says nothing about the database.
|
|
|
|
|
jschell wrote: That isn't really going to tell you anything.
It's going to tell me whether the DNS and the host-file know what IP I'm talking about.
jschell wrote: But still says nothing about the database.
I'm interested in reaching the PC before I reach for a specific database on that PC.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
