Viewing 15 posts - 5,401 through 5,415 (of 7,614 total)
The separate table is really the only acceptable method for that type of data, since it could see many modifications in the future. For example, you could add types,...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 3, 2015 at 3:53 pm
I think a "standard tally table" (table of seq numbers from 0 to some big number) is all you really need.
--add up the days
SELECT account, loc, CONVERT(varchar(7), month, 111) AS...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 3, 2015 at 2:41 pm
Lynn Pettis (2/3/2015)
ScottPletcher (2/3/2015)
SELECT DOC_NO
,ENTER_DATE
FROM SALES_ORDER
WHERE ENTER_DATE >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 12, 0)
AND...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 3, 2015 at 1:26 pm
Best is to always use >= and < on date/datetimes, not between:
SELECT DOC_NO
,ENTER_DATE
FROM SALES_ORDER
WHERE ENTER_DATE >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 12, 0)
AND ENTER_DATE < DATEADD(DAY,...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 3, 2015 at 1:14 pm
Again, you need to explain "lev5" to us. We do not know your data.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 3, 2015 at 10:25 am
You have to remember that I do not know what your data is.
GROUP BY a.bf_fund_cd, a.bf_bdob_cd, b.lev5,b.bf_orgn_cd
...
SS_21111_1 -Lev5 (dont need to show) ---> less than 5,000000
SS_21112_2 -$1,500,000(dont need to show)
SS_21113_2...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 2, 2015 at 4:53 pm
If you want to see all values, remove the "having" condition:
having sum(a.data)>5000000
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 2, 2015 at 3:41 pm
Rather than use a specific ending value, most people prefer to use < the next day. That method always works, even if the date/datetime format changes later, something like...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 2, 2015 at 3:32 pm
You'll have to use varchar output rather than a numeric format:
SELECT REPLACE(CAST(pay AS varchar(30)), '.000', '') AS pay,
...
FROM table_name
WHERE ...
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 2, 2015 at 3:28 pm
The internet site needs to be able to function stand-alone. You could periodically push data from the intranet to the internet db(s), but you don't want the internet db...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 2, 2015 at 11:20 am
You can use sp_executesql, something like this:
DECLARE @WIDTH decimal(19, 2)
DECLARE @LONG decimal(19, 2)
DECLARE @RESULT decimal(19, 2)
DECLARE @sql nvarchar(4000)
SET @sql = N'( @WIDTH / 3 ) + ( @LONG / 100)'
SET...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 2, 2015 at 11:17 am
Since the page count is vastly smaller, I would think it's the highest level of the index rather than the lowest (leaf) level.
But you should definitely change the query to...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 2, 2015 at 11:05 am
Yeah, not bad, although it does have to do a full index scan for trans date on the TD table.
You can do minor tweaks on the list itself, changing it...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 30, 2015 at 2:19 pm
Ken Davis (1/30/2015)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 30, 2015 at 1:19 pm
Is the TabletAuditLog.dbo.TransactionDetails table clustered on ID first?
Just to confirm, the "ID" in the TD table is the same value as the ID in the T table, right?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 30, 2015 at 1:03 pm
Viewing 15 posts - 5,401 through 5,415 (of 7,614 total)