question

prasad.nyalapatla avatar image
prasad.nyalapatla asked

logic depends on table columns.

---Query CREATE TABLE #PlannedCollectionsDetails ( [JanValue] [decimal](30, 10) NULL,[FebValue] [decimal](30, 10) NULL,[MarValue] [decimal](30, 10) NULL,[AprValue] [decimal](30, 10) NULL,[MayValue] [decimal](30, 10) NULL,[JunValue] [decimal](30, 10) NULL,[JulValue] [decimal](30, 10) NULL,[AugValue] [decimal](30, 10) NULL,[SepValue] [decimal](30, 10) NULL,[OctValue] [decimal](30, 10) NULL,[NovValue] [decimal](30, 10) NULL,[DecValue] [decimal](30, 10) NULL, [isJanApproved] [int] NULL,[isFebApproved] [int] NULL,[isMarApproved] [int] NULL,[isAprApproved] [int] NULL,[isMayApproved] [int] NULL,[isJunApproved] [int] NULL,[isJulApproved] [int] NULL,[isAugApproved] [int] NULL,[isSepApproved] [int] NULL,[isOctApproved] [int] NULL,[isNovApproved] [int] NULL,[isDecApproved] [int] NULL ) INSERT INTO #PlannedCollectionsDetails ( [JanValue],[FebValue],[MarValue],[AprValue],[MayValue],[JunValue],[JulValue],[AugValue],[SepValue],[OctValue],[NovValue],[DecValue], [isJanApproved],[isFebApproved],[isMarApproved],[isAprApproved],[isMayApproved],[isJunApproved],[isJulApproved],[isAugApproved],[isSepApproved],[isOctApproved],[isNovApproved],[isDecApproved] ) SELECT 1000,2000,3000,4000,5000,6000,7000,8000,9000,10000,110000,12000,1,1,0,1,0,0,0,0,0,0,0,1 -----explanation examples: if we send any selected months from front end.only we want that month values if that month had isapproved true. for example: we send months like "jan,feb,aug,dec" jan feb aug dec total 1000 2000 0 12000 15000
sqlserver2008
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This site runs off of votes. Please mark all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that answer.
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
Is the design of your tables fixed? You'd have much better luck if you stored the months as rows in a column rather than having 12 cols for the values and 12 for the approved flag. The title of your question gives away the problem - you are using logic based on column names - much better to do logic based on data and not metadata.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hi Kev Riley, Thanks for your quick reply, YES, my table design is fixed. Because of last few years to till now we received data from client like this. Based on client approval, we update isApproved columns accordingly. Please can you give me suggestion accordingly. Title: Sorry your inconvenience for title, my thought my logic depends on columns that’s way I am using.
0 Likes 0 ·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
Like Kev says: Your data should probably be modelled differently for better flexibility in use. But I also know that you don't always "own" the database design. What you are really looking at is being able to UNPIVOT the data over more than one column. That's not possible with UNPIVOT (well it is, but you'd have to use two CTEs and join them on month, causing unnecessary reads in your database). But it can be solved using the APPLY operator and the VALUES-clause to "create a virtual table" for each row. Here's how it could look in your case. SELECT v.MonthNo, v.IsApproved,v.MonthValue FROM #PlannedCollectionsDetails p CROSS APPLY(VALUES(1,p.JanValue,p.isJanApproved), (2,p.FebValue,p.isFebApproved), (3,p.MarValue,p.isMarApproved), (4,p.AprValue,p.isAprApproved), (5,p.MayValue,p.isMayApproved), (6,p.JunValue,p.isJunApproved), (7,p.JulValue,p.isJulApproved), (8,p.AugValue,p.isAugApproved), (9,p.SepValue,p.isSepApproved), (10,p.OctValue,p.isOctApproved), (11,p.NovValue,p.isNovApproved), (12,p.DecValue,p.isDecApproved) ) as V(MonthNo, MonthValue, IsApproved);
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks Magnus Ahlkvist, it is working fine.......
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.